Lets examine what the options are for calculating Quartile / Percentiles in PowerPivot.  On my last post, I explained one way to calculate Median using pure DAX.   Though the calculation works, don’t expect it to be speedy when dealing with large datasets;  The reason for this is that it relies on row context iterations to rank values prior to selecting the median.  

As similar technique can be used to calculate Quartile or Percentile, following this blog entry.  However, the result is not always optimal:  not only the calculation can be slow, be the results do not match Excel’s native functions.    The reason for this is that Excel uses interpolation to calculate both Quartile and Percentile, whereas the DAX expression above picks one value out of the set (in other words, its just an approximation).

Can we use a technique to match Excel’s results within a PowerPivot model?   The answer is yes, though the technique discussed here does not use DAX.   What I want to share is another way of using the inherent capabilities of PowerPivot: being itself a version of SQL Server Analysis Services, it is actually able to understand MDX.  It is quite interesting to notice that PowerPivot can behave in ways that reveal its SSAS nature:  from the ability to respond to Excel cube functions to generating a trace file which can be read in SQL Profiler.

So what does this have to do with calculating Percentile?   Well, even though DAX does not include any of those functions you can leverage PowerPivot’s multidimensional interface to create an MDX calculated member that will encapsulate each of those functions.  Lets see how.

In order to see PowerPivot queries to the Vertipaq engine in MDX format, we can use a free codeplex add-in called ‘OLAP PivotTable Extensions’ (download it here, and be sure to select the correct bitness version).  This add-in was created with SSAS in mind, but – without any further effort from the add-in itself – PowerPivot responds to it. Once installed, create a PowerPivot table and then use the add-in to view the MDX query (select the ‘MDX’ tab on the window that appears when you click on the OLAP Pivot Table Extensions menu item)

Sample PowerPivot table using AdventureWorks data

The MDX query we get is:

SELECT
    NON EMPTY Hierarchize(
        {DrilldownLevel({[DimDate].[EnglishMonthName].[All]},,,INCLUDE_CALC_MEMBERS)})
        DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS 
FROM [Sandbox]
    WHERE (
        [DimDate].[CalendarYear].&[2004],
        [Measures].[Sum of SalesAmount]
        )

So lets create a calculated member to compute Median.

Select the ‘Calculations’ tab on the add-in menu.  This is used to submit calculated members along with the rest of the pivot table query.  Type the following query:

Median(                                  
        (
         [DimDate].[CalendarYear].CurrentMember *
         [DimDate].[EnglishMonthName].[All].Children
        ) 
        , CoalesceEmpty ( [Measures].[Sum of SalesAmount] , 0 )                          
      )

This should look in the add-in window as follows:

The result matches Excel regular ‘Median’ function:

Lets examine the actual query.   MDX has a built-in ‘Median’ function ( see definition here ).   As you can see, the expression uses parameters that look like regular SSAS dimensions.  This is the PowerPivot data model though, and we didn’t have to use Visual Studio to create it: the PowerPivot engine automatically creates dimensions based on the tabular model loaded into Vertipaq.

The measure [Sum of SalesAmount] is the aggregate PowerPivot created based on the Sales Amount column of the in-memory fact table.  Once it became an actual measure in the PowerPivot model, we can use it as the measure expression of the MDX function.

Lets try now creating Quartile and Percentiles.  It gets more interesting here:  Unlike the ‘Median’ function, MDX has no built-in Percentile or Quartile function.   So here we have it:  neither DAX nor MDX will help us here.  However, we can leverage a another feature of SSAS: the ability to perform COM interop calls through the ExcelMDX assembly.   You can see this library on a regular SSAS installation by browsing the assemblies folder:

It is quite interesting when you think about it.   We are asking an Excel pivot table to communicate with the PowerPivot engine through a calculated member which in turn invokes Excel functionality not present in the MDX language. Pretty cool indeed. So lets try it:  Using the following expression we can compute Quartiles:

Excel!Quartile(
                                SetToArray ( 
                                                
                                                                ( [DimDate].[CalendarYear].CurrentMember * [DimDate].[EnglishMonthName].[All].Children 
                                                ) 
                                , CoalesceEmpty ( [Measures].[Sum of SalesAmount]  , 0 )
                                ) 
                ,1)

The output is the following:

As you can see, the output matches Excel’s native quartile function. The calculation is now built into the PowerPivot model, and hence is dynamic and can respond to slicers and filters, as well as cell context by using MDX properties like ‘CurrentMember’.  And though the PowerPivot is performing a COM interop call to Excel (marshalling calls from one environment to the other), it should still perform better than a DAX calculation based heavily on row context.

If you examine the MDX that is sent to the Vertipaq engine, you will notice the calculated member is declared as a query scoped expression:

WITH
MEMBER [Measures].[Quartile] as Excel!Quartile(
                                SetToArray ( 
                                                
                                                                ( [DimDate].[CalendarYear].CurrentMember * [DimDate].[EnglishMonthName].[All].Children 
                                                ) 
                                , CoalesceEmpty ( [Measures].[Sum of SalesAmount]  , 0 )
                                ) 
                ,1)

SELECT
    {
    [Measures].[Sum of SalesAmount],
    [Measures].[Quartile]
    }
    DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS ,
NON EMPTY
    Hierarchize(
        DrilldownMember(
            CrossJoin(
                {
                [DimDate].[CalendarYear].[All],
                [DimDate].[CalendarYear].[CalendarYear].AllMembers
                },
                {([DimDate].[EnglishMonthName].[All])}
                ),
                [DimDate].[CalendarYear].[CalendarYear].AllMembers,
                [DimDate].[EnglishMonthName]))
    DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON ROWS 
FROM [Sandbox] CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

The dimension used (DimDate) as well as the measure (Sum of SalesAmount) were automatically created by the PowerPivot engine.  This is what provides compatibility with OLAP interfaces:  for each table in the PowerPivot model, a default ‘count’ measure has been created as well as a dimension, in which each column is an attribute.  In other words, dimensions and measures are somewhat interchangeable in PowerPivot, following a more flexible model than with the traditional SSAS engine.  Also, notice the query is executed against a cube called ‘Sandbox’.  This is the default cube-interface name for Vertipaq PowerPivot databases.  It is the same name you will see in the cubes generated in the dedicated SSAS instance used on PowerPivot for SharePoint (This is an instance of SQL Server Analysis Services 2008 R2 in Vertipaq mode).

One downside of this method is that the PowerPivot field list is not aware of the calculation.  However, if you open the standard pivot table field list you will see it there. In other words, and as I said before, this calculation is now part of the data model.  Which means you can access it outside of the constraints of the pivot table: through Excel cube functions.   As an example, using the following formula expression on a worksheet cell we can dynamically invoke the MDX calculated member against PowerPivot, with the ability to specify a query context to it:

=CUBEVALUE("PowerPivot Data",CUBESET("PowerPivot Data","Head([DimDate].[CalendarYear].[All].Children)"),"[Measures].[Quartile]")

The result will be 925,015 again.  In other words, we were able to dynamically invoke the first member of the CalendarYear attribute on the DimDate dimension and leverage the MDX calculated member, all in one cell – no need of pivot tables.  Pretty powerful right?

You can verify that our Quartile calculation is now part of the intrinstic model by browsing through measures members when constructing the cube formula:

What I find pretty interesting is that the list of available measures on the PowerPivot model includes now regular DAX measures ([Measures].[Sum of Sales]) as well as MDX measures ([Measures].[Quartile]).

How about Percentile?  Using the same method, it is pretty simple to calculate it.  Just replace Quartile in the MDX expression… that’s it!

Excel!Percentile(
                                SetToArray ( 
                                                
                                                                ( [DimDate].[CalendarYear].CurrentMember * [DimDate].[EnglishMonthName].[All].Children 
                                                ) 
                                , CoalesceEmpty ( [Measures].[Sum of SalesAmount]  , 0 )
                                ) 
                ,0.30)

Be careful to not define your underlying measure as ‘currency’ type.  Though I always thought data types in the PowerPivot window were purely decorative (as they don’t carry over the pivot table), the MDX calculated member will not work on currency types.  Instead, try decimal type and you will be fine.

And one last thing… as you may have noticed, this technique opens the possiblity to a huge range of calculated expressions.  An though it is not the standard PowerPivot/DAX implementation, it still leverages PowerPivot technology by making use of its inherent data model. In other words, PowerPivot applications can not only benefit from the amazing power of DAX , but also that of MDX (which in turn opens the door to further functionality through interfaces with Excel and VBA).  Things like Internal Rate of Return (IRR) are also possible using this method

Excel!IRR( SetToArray (  [Table3].[b].[All].Children, [Measures].[Sum of a]  ) )

PowerPivot is generally marketed as a ‘Self-Service BI’ tool.  However, it can alternatively be used as a way to enhance the design phases of traditional Business Intelligence projects, even if the final implementation does not use PowerPivot technology at all.

Why is that?  Because it more closely aligns business experts with BI developers during architectural discussions. In order to explain this better, I think it’s relevant to first describe the process by which data analysts can provide relevant input to BI designers:

A lot of intermediate Excel users will probably never use VBA macros and will barely feel comfortable with Pivot Tables.  For advanced Excel users, however, there is a thrill associated with getting a cool formula to work, or knowing the most interesting formatting shortcuts or even knowing how to add dropdowns to a spreadsheet to achieve dynamic interactivity.

These folks do not normally belong to the technology team; however, in addition to being savvy Excel users they are in the front lines of the decision making process which many times makes them subject matter experts (SMEs). It is likely they will eventually be motivated to start playing with the data import facility in PowerPivot and start developing DAX calculations.

Fast forward a little into the future, and imagine these users developing meaningful and insightful PowerPivot reports.  These contain connections to a multitude of data sources and aggregate data in a way that provides enlightening information to decision makers.

Though not always aware of it, these users will have accomplished three things of tremendous value for Business Intelligence professionals:

·         Validated one or more sources of quality data

·         Validated a useful data model

·         Validated one or more business calculations

In other words, as they benefit and make use of ‘Self-Service BI’ technology they will unknowingly serve as ‘data explorers’ to the Business Intelligence and Data Warehousing team that benefits from their discoveries.

In the past, these ‘spreadsheet-based discoveries’ went unnoticed by members of the technology team (and became spreadmarts).  However, the Business Intelligence team members can now gain insight into popular spreadsheet reports across the enterprise (PowerPivot for SharePoint). In addition to that, they will be able to take these PowerPivot applications and open them in Visual Studio as a SQL Server Analysis Services Business Intelligence Semantic Models (BISM) in SQL Server 2011.  This is the server equivalent of a PowerPivot for Excel workbook: it is built using the same technology (the columnar based storage engine "Vertipaq") but with a number of robust features proper of an enterprise solution.

One of the great things of this new approach to BI that Microsoft is proposing is that it will most likely help close the gap between Excel power users and Business Intelligence professionals by creating an analytical environment built on the same technical foundation on both ends: DAX.  This will most certainly help avoid communication challenges that normally occur when both sides speak in totally different “languages”:  Excel formulas \ business calculations on one side, and T-SQL/MDX and OLAP on the other.

So what about BI prototyping? And how can we benefit from it even before SQL Server 2011 is released? Well, imagine having the ability to engage subject matter experts in their most familiar environment (Excel) and with their help be able to rapidly test assumptions about BI project deliverables, before starting development.  Rather than purely describing a calculation in words or spreadsheets disconnected from a database, we can instead quickly and iteratively build a sample of it tied to a data source and observe the calculation in action as its being sliced by multiple dimensions.  This can generate the necessary early insights that can help correct potential misunderstandings quickly and generate a more complete and accurate data model that can be used to guide actual development. And these insights have been already validated by the SMEs (in collaboration with the BI team).

As an example, imagine a dimension called “Orders”.  During the initial design phase, it becomes clear it must be added to the cube in order to generate the expected reports. Fast forward a few months (and many hours of development).  While building the final reports, the BI team becomes aware something that is missing:  Even though DimOrders is used in many reports (as business users started at the beginning of the project) it was built at a different grain that what reports require;  conditional logic is discovered – yes, at the last stages of the project – that treats some Order Details differently from others.  What to do? Different approaches are possible, from redesigning parts of the ETL to creating new measure groups based on named queries in order include the data that is missing. Architectural re-arrangements are going to have to be made in order to complete it on time; and any ‘quick’ solution will risk compromising the ad hoc nature of the final product (which will in turn limit the ability to use it for analysis).

How would this change if PowerPivot is used as a prototyping tool?  Using an Agile approach, during  early scrum meetings it will be possible to ‘sample’ the Order dimension against a copy of all relevant reports.  By loading a relatively small set of data into PowerPivot and designing ad hoc reports to mirror actual views across any functional existing reports, one is able to quickly uncover the fact that different grain is required for the dimension in question.  This work would probably be done in very small amount of time compared to what it will require to re-desing and re-develop parts of the ETL and OLAP solution.  Time – and money – can be saved while increase accuracy of the design.

How would it be possible to ‘mimic’ the dimension ‘Orders’ without building into a real SSAS cube? This is the most interesting part:  PowerPivot was built on SQL Server Analysis Services code. In other words, it is a version of SSAS.  All rules expected by SSAS when building cubes apply to PowerPivot as well; for example, dimensional tables in a star schema translate to ‘lookup’ tables in the PowerPivot window.  PowerPivot data is consumed by Excel using ‘cube’ interfaces  (MS OLAP provider, Excel CUBE functions, etc); However, PowerPivot was built for fast development which makes it an ideal tool for ‘mimicking’ an SSAS cube without the complexity of advanced server features.

I want to emphasize, we are not trying to build the entire BI project or corporate data warehouse in PowerPivot.  We are just ‘sampling’ the data model and ‘testing’ the different calculations in order to allow subject matter experts to help us gain a much clarity regarding the quality of the data, the relationships among the different data entities and the validity and relevance of the business calculations.   It is often the case that business users will start ‘refining’ their idea of what they want once they see a working model. 

This final result of a BI project that used PowerPivot for prototyping can very well be a traditional UDM/OLAP environment using MDX calculations. The discoveries gained during the prototyping phase have no direct relationship to the specific technology that will be implemented; they only offer pointers to a better architecture that will increase the chances of a successful implementation, one that will have a profound impact on the business.

A lot of Business Intelligence projects fail for two major reasons: lack of Business Sponsorship and poor Data Quality.  There is a good chance that using PowerPivot as a prototyping tool can help tackle these issues as subject matter experts use their familiar Excel environment while collaborating with BI professionals under the iterative Agile framework. Rapid dimension/measure sampling can quickly uncover data quality issues while establishing a solid data model that analysts can understand which gradually builds support for the BI vision (in contrast to solutions they perceive as overly complex and many times inaccurate, which is the mean reason why ‘spreadmarts’ exist in the first place).

This is the final installment on the series of blog entires regarding how to leverage PowerPivot data using Excel cube functions. The main purpose of the series has been to explain how to use Excel in a way that allows the report author to create appealing reports and dashboards using PowerPivot in-memory data but beyond the constraints and limitations imposed by Pivot tables.

As you can see,   the final report (screenshot above) uses a combination Pivot table references, MDX filtering functionality, DAX measures and Excel Cube functions.  There is a reason why each of these was used, which is what we discuss next.

To recap, is worth mentioning a few things that you can do when working with PowerPivot data using Excel cube functions:

-          Cube functions access the in-memory tables using the workbook connection named “PowerPivot Data”, which was automatically created when importing data to PowerPivot.

-          Even though the storage is SSAS in-memory columnar mode (Vertipaq), Cube functions can access this data as if it was SSAS in OLAP mode.

-          Cube functions allow using a limited amount of MDX functionality which is very useful when constructing custom sets to be used across the report

-          DAX calculated columns and measures can be called via Excel Cube functions. Even though they are essentially relational, when using these DAX calculations over Excel Cube functions you can think about them in terms or traditional UDM/OLAP measures and dimensions.

Cube functions: not a vehicle for free form MDX

Even though MDX is allowed inside Cube functions, do not think they will just simply pass the MDX expression to the storage engine (SSAS in OLAP or Vertipaq mode), as if you were in a free form query window like SSMS.  There are limitations that are important to keep in mind:

-          CUBESET function only allows one expression when building the set.  What this means is that you cannot combine two filters in the functions to create a set across 2 or more dimensions (in othere words, a single-tuple set).  You can construct this kind of set however, if you hardcode the set definition manually.



Only one set expression is allowed. Two filters cannot create a tuple set, unless hardcoded manually, as in the expression below:

=CUBESET("PowerPivot Data","({[DimDate].[CalendarYear].[All].[2005],[DimDate].[CalendarYear].[All].[2006]},[DimGeography].[ContinentName].[All].[Asia])","MySet")

Remember: [DimGeography] and [DimDate] are not real OLAP dimensions when using PowerPivot data in this report.  They are in-memory tables.  PowerPivot converts them to ‘dimension equivalents’ after loading the data.  But, you may ask, how does PowerPivot know what is supposed to be a dimension and what a measure?  It doesn’t.  In Vertipaq, any field can be either a dimension or a measure. Try dragging [DimGeography].[RegionCountryName] to the values section of the Pivot table. Vertipaq automatically turns it into a measure, which is by default a count of values.

-          Even though you can define tuples that include more than one dimension following the technique above, the result will be equivalent to a resultset over a column or row axis with crossjoined dimensions. In other words, there is no way to generate the equivalent to a ‘slicer’ axis using the CUBESET function.

There is no equivalent slicer axis in the CUBESET function.  Displaying members of a tuple set will yield duplicate values. In this case the first Asia value is associated with Year 2005, and the second one with Year 2006.

-          CUBEVALUE function only allows calling a measure which is then filtered using member expressions. What this means is that you cannot create MDX calculated members on the fly using the CUBEVALUE function.

In order to compensate for the first two limitations above, if there is a need to apply a slicer to a set it is best to reference existing pivot tables, as the default behavior on them is to filter the dimension members on the column or rows axis in a way that is equivalent to the ‘WHERE’ clause in T-SQL or MDX (slicer axis).

In a pivot table, members are filtered following the behavior of a WHERE slicer clause. When building custom reports, there may be a need to reference pivot tables in other spreadsheets if slicing the data in this particular way is necessary.

Regarding the CUBEVALUE limitation (not being able to construct on the fly calculated members), the way to circumvent it is by way of DAX calculations.  These can be called as if they were MDX members, even to the point of being able to change the context of a calculation in the exact same way once you would when invoking a regular SSAS measure.  This is due to the fact that both languages assign context as a function of the coordinate pointers over stored data’s memory space.  MDX does it over a cube space, wheras DAX does it over a relational system of tables and relationships. However, they are in essence very similar, and to Cube functions they look exactly the same.  An example will explain this better:

In order to calculate the Accumulated Sales Growth Since Inception of the product, we use the following DAX measures:

            (

                                    CALCULATE(

                                                            [Sales (Running Total)],

                                                                                    LASTNONBLANK(

                                                                                                            ALL(DimDate[CalendarMonth]), [Sales (Running Total)])) -

                                    CALCULATE(

                                                            [Sales (Running Total)],

                                                                                    FIRSTNONBLANK(

                                                                                                            ALL(DimDate[CalendarMonth]), [Sales (Running Total)]))

            )                       /

                                    CALCULATE(

                                                            [Sales (Running Total)],

                                                            FIRSTNONBLANK(

                                                                                    ALL(DimDate[CalendarMonth]), [Sales (Running Total)]))

            ,BLANK())

This function removes any current filter context on [CalendarMonth] when calculating growth.  It won’t affect, however, the [CalendarYear] attribute.  When applying this DAX measure to a specific calendar Year, it will reflect the accumulated sales growth over the year by using a simple growth formula:

(Final Accumulated Sales – Initial Sales) / (Initial Sales)

If what we want is to display the growth for ALL years, we must remove any existing filter context for the [CalendarYear] attribute when evaluating the DAX measure.  In the sample report, we also want to display this total rate in one cell, outside of a pivot table.  We can accomplish this using the following Cube formula:

We can call the DAX measure within the Cube formula, and by specifying the [All] member of the [CalendarYear] attribute we change the filter context of evaluation, which gives us the total accumulated sales growth across all years.

Finally, despite the limitations exposed above regarding MDX functionality in Cube formulas, it can be used when filtering set values or constructing custom sets.  In the sample report the X axis values applied to the chart where filtered to only the years that have associated fact data, which was accomplished using the MDX ‘Exists’ function. In that way, the chart won't show empty labels for those years that have no data.

The MDX ‘Exists’ function keeps only those ones that have [Sales] data. MDX can be used even though Vertipaq is fundamentally relational, not multidimensional.

In summary, Excel cube functions using PowerPivot data is a great combination that gives I.T. or data analysts a way to build custom Excel reports and dashboards that break out the constraints of pivot tables.  DAX calculation and MDX set manipulation functions can be used in a single Excel report in order to generate highly customized views of data that a user can still interact with. 

PowerPivot Slicers

Slicers are a new addition to Excel 2010.  They allow filtering in pivot tables, but with a lot of added advantages compared to traditional filters.  First, they look a lot better – they are much more inviting!  Second, when multiple filters are selected, you can actually SEE the selected options as opposed to the [multiple values] that you get with traditional filters.  And last, when using multiple slicers against a pivot table, they can actually autoselect the members with available data based on the selections on the other slicers (in effect, generating a cascading filter)

 It is possible to add a slicer to an Excel worksheet without any pivot table at all.  However, in order to benefit from the cascading mechanism, we must tie it to Pivot table (in that way, members of the slicer will know if they have or not numeric data available for them).

Load the PowerPivot window with the prepopulated Contoso BI data, and select PivotTable under the PowerPivot home tab.  When asked where it should be placed, select “new worksheet”.

Add the RegionCountryName to the rows field, the Sales Amount to the Values field, and the Calendar year and ContinentName as vertical slicers.

If we move the pivot table to another page (PivotTable Tools tab->Move PivotTable), the slicers will stay on the current spreadsheet.  In that way, we can use them when constructing our cube formula based report while still having the PowerPivot field list available to us in case we want to review available dimension/measure attributes or create new DAX calculations.

There are 7 cube functions in Excel.  They can be used to simulate MDX query functionality, from set creation to retrieval of measure values based on filter expressions.  In our current report, we will use 3 of them -

CUBESET:  used to define a set of members.  You can leverage this function to create tuples that span multiple dimensions as well; its usage is not restricted to members of the same dimension as in regular named sets.

CUBERANKEDMEMBER: used to access each of the members of a CUBESET.

CUBEVALUE: used to retrieve measure values in the context of specific slicers (expressions).

If we wanted to know - for example - the total sales for Greece in our sample Contoso BI data, we could use the following formula:

=CUBEVALUE("PowerPivot Data","[DimGeography].[RegionCountryName].[All].[Greece]","[Measures].[Sales]")

The formula queries Vertipaq and retrieves the measure from the equivalent MDX expression of

Aggregate([DimGeography].[RegionCountryName].[All].[Greece], [Measures].[Sales])

In order to tie our current slicers with the cube functions, we can leverage the fact that Excel makes available a name range as a pointer to the slicer member set.

Query against Vertipaq

When building cube formulas against the Excel vertipaq database, we leverage the automatically generated “PowerPivot Data” connection.  There is intellisense in the formulas, which helps guide us see the options we have available.

Note how Vertipaq generated a dimension for each of the queries loaded in-memory.  There is also an automatically generated ‘Measures’ dimension.

This dimension holds a not very intuitive ‘count’ aggregate for each of the queries in the vertipaq data store as well. This was done in order to force Analysis Service to create a measure group placeholder, in which to build on when creating DAX calculations.  In our example using Contoso BI, some DAX calculated members were already created for us, and Vertipaq correctly placed them as Measure members ([Measures].[BaseQty] below)

You can also see and edit these DAX measures in the PowerPivot field list. They have a calculator icon next to them.

In our specific report example, we will be using the [Measures].[Sales] DAX calculation, which is simple a SUM of all SalesAmount values in the FactSales query.  This DAX measure will be evaluated in the context of the slicers used as expressions within the cube function. The picture below shows the DAX measure editor for this calculation.

Add both slicers to the formula -

In the next entry, I will continue building the report in the attempt to answer the questions:

which are the top 5 selling countries per continent and year?

Which were the best selling brands for one or more of the countries above?

Which was the top product out of the best selling brand group above, and what were it sales performance for the last year?