THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
 in Alberto Ferrari (Entire Site) Search

Alberto Ferrari

• PowerPivot: Counting active days

 Browsing on the web I have seen an interesting question that is worth a post, since I think it is a very common pattern and a good example of “thinking in DAX”. Starting from a table which contains a people and two dates (ActiveFrom, ActiveTo), we want to compute how many days an individual has been active over some period of time, let us say at the month level. As always, a picture is worth a thousand words, here is the input and the desired result: The solution is very easy by means of using a calendar table. To create one, it is enough to create an Excel table like the following one and use “Create Linked Table” from the PowerPivot tab of the Ribbon. Once linked in PowerPivot, you end up with the two tables, that I called Activities and Calendar. Beware that there is no need at all to create relationships between the two tables, we will solve the scenario using DAX only. Moreover, there is no means to create these relationships, since the dates represent a time period, not singular dates. To reach the desired result, it is enough to note that, for each record in the Activity table, the number of rows in the calendar table that are between ActiveFrom and ActiveTo, represent the total number of activity days. Thus, the formula is straightforward: =SUMX ( Activities, CALCULATE ( COUNTROWS (Calendar), Calendar[Date] >= EARLIER (Activities[ActiveFrom]), Calendar[Date] <= EARLIER (Activities[ActiveTo]) ) ) This formula seems to always compute, for each row in the activities table, the total number of active days for the individual, since it does not take into account years and months. Surprisingly, if you write this code in a measure and put it on a PivotTable, you will get the result shown at the beginning. The reason for which the formula works is that the inner CALCULATE creates a filter on the Calendar[Date] but does not change the current filter context on year and month. Thus, for each cell, the filter on year and month is still active and produces the correct result. Moreover, the same formula work for any calendar period without any change. I guess for PowerPivot newbies this behavior seems like magic, at least it was so for me a few months ago… in reality, understanding PowerPivot is just a matter of understanding row and filter contexts, once you get them formulas are really simple to write. An interesting exercise, left to the reader, is to count the number of working days of activity. Easier than you might think and surely worth spending some time if you want to learn some DAX data modeling.
• PowerPivot: improve the performances by sorting tables

While it is well know that in UDM data should be loaded sorted, to get better file compression and map structures. The world of PowerPivot (and BISM) is yet not explored. Working with a 50 millions rows table, I tested various scenarios to check if sorting affects PowerPivot data structures too.

The fact table structure is very simple:

CREATE TABLE Fact_Transaction (
ID_Account INT NOT NULL ,
ID_Type INT NOT NULL ,
ID_Date INT NOT NULL ,
Amount MONEY NOT NULL
)

There are 240,000 accounts, 1,000 dates and 4 types. Thus, the ID_Account is the most selective, followed by the date and the type. I tried loading data inside PowerPivot using different sorting and the final workbook size is in the next table:

 Order File Size (K) HEAP (unosrted) 498,905 Type, Date, Account 372,505 Date, Type, Account 372,209 Account, Date, Type 374,001

Thus, the compression algorithm in PowerPivot seems to prefer sorted data (which is somehow expected, even if not obvious). The big difference is between sorted and unsorted data while changing the sort order does not affect the final size in an evident way. This is definitely expected since the ratio between transactions (50 millions) and accounts (240.000) is very high, resulting in a good sorting even with the most selective column. Different distributions in real world scenarios might lead to different results and they are definitely worth trying.

What is interesting is that the query speed of the final workbook is much better with the smaller ones, when compared with the biggest, i.e. unsorted. This might indicate that it is not just a matter of workbook size but even of internal data structures that result in a better optimized format when data is fed sorted to PowerPivot. I don’t have clear number here, but the difference is evident at first glance by pivotting over the data model.

The final hint is straightforward: if you use a heap or a table with an INT IDENTITY column for your fact table, then it might be useful to check different sorting and (as always) verify the final result. It might be the case that you are wasting memory and CPU due to a poorly designed data structure or, in other words, that there is space for improvement.

• PowerPivot: remove useless columns!

 I teach to my students always to remove all useless columns from PowerPivot data models, because they simply waste precious memory. While I was working on a test model with 50 millions of rows, I did the naive mistake of loading the complete table in PowerPivot. Then I saved the file to check the compression of Vertipaq and widened my eyes when I saw a SQL table of 1.6Gb resulting in a workbook of 1.3Gb. It seemed to me that data compression was simply not working. Checking better, I discovered that the fact table contained a PK INT IDENTITY, which I stupidly loaded inside the PowerPivot data model. Simply removing that column (which has 50 millions distinct values) resulted in a 490Mb workbook, i.e. 1/3 of the original size. Much faster to open, save and work with. The lesson to remember is: SELECT * is the evil in the world of SQL and is still the evil in the world of PowerPivot. Always check for the presence of useless columns since, when present in a big fact table, they can really change the user experience with the workbook. Needless to say, publishing such a workbook on SharePoint and wasting 1Gb of RAM of the server is a crime that should be properly punished!
• A new SQL Server MVP from Italy

 Yep… it’s me. :)
• Great post from Jeffrey Wang about DAX

 In case you missed it, this post from Jeffrey about DAX and the way filter contexts work is simply awesome! The way he explains the filter contexts internals is great, deep technical and yet easy to understand. Very very strongly suggested as a good reading for anybody serious about DAX, which simply means anybody working with the MS BI stack.

• Microsoft BI Conference 2011 in Lisbon

 Anyone interested in BI from Portugal or Spain should not miss the Microsoft BI Conference 2011 in Lisbon: one full day (March, 25, 2011) with three tracks on Business Intelligence: Decision Makers BI pros Intro to BI. I am going to present two sessions on PowerPivot: one is a nice deep dive into DAX for BI pros, the other is about self service BI for decision makers. Titles and the complete agenda will be published in the next days, but I suggest to save the date. The full event is free and it seems to me a great opportunity to meet by face readers of my blog and have some good time speaking about our common passion!

• PowerPivot course: Copenhagen, Dublin and Zurich new dates available for booking

 Wow, this time I have been able to make an announcement before Marco Russo! The PowerPivot course we are bringing in tour all over Europe has three new dates available for booking: Copenhagen: 21-22, March 2011 at the Radisson Blu Royal Hotel. Dublin: 28-29, March 2011 at the Microsoft Ireland Building 3. Zurich: 4-5, April 2011 at Digicom Academy AG. The registrations are now open on the www.powerpivotworkshop.com web site., where you can find all the relevant information about the course. As always, registering during the early bird period will let you save some money over the full price. Thus, hurry up! I really look forward to see you during the workshop, since both the Amsterdam and London events have been a GREAT experience and I’d like to repeat them in other cities.