Detailed operational reporting in Excel 2013 and Power View

A scenario that is quiet common is the following, as part of my analysis I want a flat list of all Customers and their OrderNumbers filtered by a parameter, like year or who purchased particular products etc etc.

Lets take a scenario where I want to see the customer and their orders that I can filter by year and month.

We have the following schema:

sshot-79

Lets start and look at what happens in an Excel PivotTable. I drag in LastName and SalesOrderNumber as rows into a PivotTable. Excel will take a long time to return results even though there are only 60.000 rows in FactInternetSales and 18.000 in DimCustomer. Excel will eventually give you an message that will ask if you want to see a subset of the results. If you click OK Excel will give you results.  But it is probably not what you expect, you get a Cartesian product where the Pivot Table will show you ALL the SalesOrderNumbers for ALL the Customer Lastnames. Not the Ordernumbers for each person. This can result in potentially huge results that will take a long time to return. Since a huge number of rows will be returned performance usually will be quiet bad.

sshot-81

You will need to add a measure to PivotTable for the filtering to take place. This is often not very understandable for the users who just want to see the values that are joined together. If you add the measure you get the results you expect: 

sshot-82

This result set will also give you much better performance since its no longer a crossjoin.

The reason behind needing a measure this is that SSAS doesn’t have the concept of inner joins like SQL has. PivotTables will only show values on rows and columns where for the combination where the value the measure does not return blank. This is the explicit design by Excel for PivotTables.  Most important is to realize that the measure is used to determine if the values for Lastname and SalesOrderNumber are going to show up for each combination of values. Lets say you were to use “Sum of Cost” as measure and the cost column is empty in the FactInternetSales table for some customers, you will NOT see the SalesOrderNumber for that Lastname that we would see if we were to use “Sum of SalesAmount”.

Power View behaves differently, when you put the same the fields into a table in Power View you do get results for each SalesOrderNumber for each LastName, not a crossjoin but use some sort of join. This gives much better performance than doing the same in Excel.

sshot-83

But how does this works and why? Analysis services does not have a way to get relationships without measures. Well what Power View automatically (magically) does is look at the relationships between the tables of the fields used in the report and in the DAX query that it sends it automatically creates a measure in the table on the many side of the relationship to force the values to show up as we expect. The measure is not persisted in the model and just used for this visualization.

This is the query it generates: 

EVALUATE

  FILTER(

    KEEPFILTERS(

      GENERATE(

        KEEPFILTERS(VALUES(‘DimCustomer'[LastName])),

        CALCULATETABLE(

          FILTER(

            KEEPFILTERS(VALUES(‘FactInternetSales'[SalesOrderNumber])),

            NOT(

              ISBLANK(CALCULATE(COUNTROWS(‘FactInternetSales’)))

            )

          )

        )

      )

    ),

    OR(

      NOT(ISBLANK(‘DimCustomer'[LastName])),

      NOT(ISBLANK(‘FactInternetSales'[SalesOrderNumber]))

    )

  )

ORDER BY

  ‘DimCustomer'[LastName], ‘FactInternetSales'[SalesOrderNumber]

So the Power View behavior looks like what makes more sense and what most customers expect, but it is using a fake measure. When you are to put a real measure in the combination of rows could change so this could be strange to some users. It does provide an out of the box performant experience that I personally like.