PowerPivot Security Architecture Diagram

This morning (thanks to my colleague Bas) I came across a nice diagram of an end-to-end view of all the related components of PowerPivot (for Excel and for SharePoint) for SQL Server 2008 R2.

Included in this diagram are:

• Service Accounts
• SharePoint Databases
• Security Hardening
• Automatic Data Refresh
• User Identity Flow
• PowerPivot Permissions Levels

The original document (in Microsoft Visio, PDF, or XPS format) can be downloaded from the MS Technet site (link).

Microsoft TechEd Europe 2012

I'm proud to announce I am one of the already 2.5k+ attendees at the TechEd Europe in Amsterdam next month!

I allready put up my agenda at europe.msteched.com, to attend at least some of the sessions of the following speakers:

- Kasper de Jonge

- Marco Russo

- Alberto Ferrari

- Jen Stirrup

See you all at the TechEd!

Performance difference between DISTINCT and GROUP BY

I was wondering if the following two queries differ in performance when used in SQL Server.

select DISTINCT persNrDV, volgnrDienstverband from medewerker

select persNrDV, volgnrDienstverband from medewerker 
GROUP BY persNrDV, volgnrDienstverband

The goal of both of the above queries is to produce a list of distinct employee numbers and employment numbers from the employee table. The first query uses SELECT DISTINCT to accomplish this task, and the second query uses GROUP BY.

I ran these queries with the Execution Plan option turned on, I found that not only the results are identical, but the Execution Plans are also identical. The SQL Server Query Optimizer has the ability to decipher each query, determining what the ultimate results are, and to produce the most efficient Execution Plan possible.

So my conclusion is that there is no performance advantage using one form of the query over the other.

Children and the alphabet

I thought this would be a nice start of the monday morning...

CALCULATE - The Queen of all DAX functions: From Row context to Filter context

This post is an update on AVERAGEX with SUM vs. SUMX with CALCULATE(AVERAGE). With special thanks to Marco and Alberto explaining the topic of Evaluation Contexts so well! :)

A little while ago I wrote about the different outcome of 2 measures, when trying to find a solution for a problem. Back then I didn't know why they had a different outcome. Since I have been attending the SSAS BISM Tabular workshop by Marco and Alberto this week, I couldn't resist to look at the problem again.

Let's start with the 2 measures:

(1)
AVERAGEX(
        FILTER(
              Organisatie,
              Organisatie[Type] = "Woonlocatie"
        ),
        SUM(Productie_OrganisatieGroei[AantalClienten])
)

(2) 

SUMX(

    Productie_OrganisatieGroei,

    CALCULATE(

             AVERAGE( Productie_OrganisatieGroei[AantalClienten]),               

             Organisatie[Type] = "Woonlocatie"

    )

)

To understand the relationships between the tables, there is a 1-to-Many relation between Organisatie and Productie_OrganisatieGroei:

Datamodel

Starting with measure 1, AVERAGEX is an iterator, so it creates a Row context on Productie_OrganisatieGroei. The FILTER on Organisatie however, because it is in a Row context, is not propagated to the many-side. So calculating the SUM of [AantalClienten] doesn't take the filter into account and takes the sum of the whole Organisatie table.

Measure 2 also starts with an iterator (SUMX) which creates a Row context, but then uses CALCULATE inside, which creates a Filter context on that one row. Remember that Filter context does propagate over relationships. Now that we have created a Filter context by using CALCULATE, the filter on Organisatie[Type] does propagate through the relationship and filters the Productie_OrganisatieGroei table. That is why this formula does produce a correct answer on the problem I had.

Once more the pivot table which has measure 1 and 2 in it:

Note: although the project was done with PowerPivot v1, I just imported the xlsx-file in SQL Server Data Tools (Visual Studio / former BIDS) to extract the datamodel for my workbook:

SSAS BISM Tabular Workshop

On april 16 and 17 I attended the BISM Tabular workshop from Marco Russo and Alberto Ferrari (from SQLBI.com with my colleague Bas Kroes. We were guests at Microsoft BV @ Schiphol, the facilities were excellent, although the temperature in the room was a bit too high these days.

BISM Tabular Workshop

It were 2 long days, also because on monday evening we attended the PASS Community event, co-hosted by ADA ICT, where Marco and Alberto talked about: Tabular or Multidimensional?

PASS Community event

I've been working with PowerPivot for Excel for almost a year now, but haven't done very much on SSAS, and nothing with Tabular (in regards to customers) yet, so I learned a lot in the workshop.

Specially the filter and row context in DAX was very good explained by them, it's still a hard topic, especially with 1-to-many and many-to-many relationships.
I can recommend the workshop to everyone who wants to know more on Tabular and SSAS 2012!

PowerPivot bug?

I ran into an interested thing this afternoon when working in the PowerPivot window.

When the PowerPivot window is maximized I see the following:

So it seems all the tables (which are there) are visible.

But when I restore the window, so it is not longer maximized, the blue triangle reappears and the rest of the tables are accessible again.

I believe it is a bug, but has anybody noticed this before?

I'll try and check this with MS later when I get the chance.

Hub'n'Spoke in PowerPivot - The Core and Thin Workbooks

I'm still working on the Management Dashboard I blogged earlier on when I was Implementing Parent / Child Hierarchy and dealing with the issue with AVERAGEX with SUM vs. SUMX with CALCULATE(AVERAGE).

I am now dealing with some performance issues, specially when opening and refreshing the Excel file. Another point occured when setting the auto refresh option in SharePoint. The refreshing itself is pretty fast, but when you open the file afterwards, it takes ages to load.. This has to do with the first (PP) and second (Excel) refresh of the file as Rob (PowerPivotPro) explains in his post.
When searching for solutions I came across some posts of Rob, the Core and Thin workbooks posts (1 and 2). The implementation of this hub'n'spoke model for PowerPivot is quite straight-forward with the help of Rob's posts.

In short, you have to get a copy of your workbook, change the connection from embedded to the location of the core workbook on Sharepoint. Then you can rename your thin workbook to .zip and copy an empty item1.data in xl\customData. Rename back to xlsx and your thin workbook is created.

Another performance issue I have is when opening an auto-refresh file in Sharepoint, it takes ages for the file to load, due to the second refresh where Excel has to refresh all the formulas and pivot tables. Everytime the file is opened this refresh takes place. With the Core-Thin concept this is not totally solved, because the thin workbook still has to be refreshed..
Still, I can now auto-refresh the core workbook in Sharepoint (this sets the autorefresh on open mark in the file). But the core never gets opened by the user, the thin only consults the data inside the core, so that's not a problem.
The only issue I now have is keeping the thin workbook up to date. Rob (or PivotStream) seems to have a solution for this problem, so I think patiently waiting is the only option.. :)

Backup failed for Server..

When trying to make a backup of a SQL-database I received the following error:

A nonrecoverable I/O error occured on file "" ... Reason: 15105

The error itself is not very descriptive and it didn't lead me in the right direction: 

finally I noticed the disk space on the (C-)drive was the problem for the backup to fail.

Learn DAX Basics in 30 minutes!

Now is the time to start with DAX: a few days ago a tutorial has been released to help you learn DAX basics quickly.
The QuickStart: Learn DAX Basics in 30 Minutes is available as part of the MS Technet Wiki, so we can all contribute to it also!

This QuickStart is for users new to PowerPivot or tabular model projects authored in SQL Server Data Tools. It is meant to give you a quick and easy introduction on how you can use Data Analysis Expressions (DAX) to solve a number of basic data modeling and analytical problems.