Archive for the ‘PowerPivot’ Category

FW: Days Since Last Event: TSQL vs. MDX vs. DAX

January 19, 2011 Leave a comment

There is a nice blog discussing “Days Since Last Event”, you can find it here.

It compares the same logic implemented by TSQL, MDX and DAX. In my perspective, the TSQL is the most easy to understand. The MDX (2nd solution) is elegant. The DAX one is the hardest to understand. (Tips: don’t read the DAX code in the text, read from the screenshot and the code is formatted better).

PowerPivot’s limitation to SSAS

August 25, 2010 Leave a comment
Marco Russo just published “Using SSAS 2005/2008 as PowerPivot Data Source: Query Designer” on his blog. It is an excellent article solving various issues in PowerPivot.
It is obvious that the version 1 PowerPivot has limitations to use SSAS as data source. It requires some workarounds to make it fully work.
In my opinion, I would really prefer to feed data into PowerPivot from a data mart (relational database) instead of SSAS. And normally, the SSAS cube is get the data from the underlying data mart anyway. Compared to the SSAS cube, the data mart is flat and doesn’t have any problem e.g. hidden columns. SSAS is excellent for building corporate wide BI report while PowerPivot is great for individual to get data from the data mart to do data analysis.
Categories: PowerPivot

PowerPivot for SharePoint Architecture video’s

August 17, 2010 Leave a comment

The below page has several links to some videos taling about PowerPivot and SharePoint integration. Quite interesting and deep dive into the technology.

Categories: PowerPivot

Avoid using FILTER on entire table in PowerPivot DAX


Kasper de Jonge has a nice blog item ( showing that using FILTER function on an entite table has performance issue. As FILTER will create a copy of the entire table in the memory, it is a very expensive function in DAX.

Using VALUEs(<Column>) if you are going to filter based on an indivudual column. Rather than FILTER, VALUES creates a one-column table which doesn’t contain duplicated value (think it as Distinct in T-SQL).

Categories: PowerPivot