Kasper de Jonge PowerPivot Blog

Got an interesting question today. The question was about creating sparklines for all the country’s that shows a running total of sales for the last 12 months (Rolling 12 months) based on the year that I select from a slicer. I use the trick I blogged about before to work with sparklines and PowerPivot, I would recommend reading it for some background.

First I want to start by creating the sheet that I want to use as a report. On this sheet I start by creating the slicer that I want to use, In this case Calendaryear:

Next I create a second sheet where I want to collect my data for the sparklines.

Because we want to show the running 12 months per year I would like to use only the months of one year that is the baseline of the running month. So we hook up the slicer from our report to the pivottable in the other sheet. Select the slicer, go to options and click on pivottable connections, here I can select the pivottable from the other sheet to be filtered as well:

Next we want to create a measure that creates our running total:

=if(COUNTROWS(values(DimDate[CalendarYear])) = 1,
			CALCULATE(SUM(FactSales[SalesAmount]),
				DATESBETWEEN(DimDate[Datekey],
					DATEADD(FIRSTDATE(DimDate[Datekey]),-11, MONTH)
					, LASTDATE(DimDate[Datekey])))
			,blank())

What this DAX measure does is first of all check if only one year is selected using:

COUNTROWS(values(DimDate[CalendarYear])) = 1,

If more than one year is selected we return blank(). If only one year is selected we calculate the running total by doing a sum of FactSales[SalesAmount] by creating a date range using DatesBetween.

The start date of the range is determined by getting the first day of the current month (we know this because of the current row context) and then go back 11 months before that:

DATEADD(FIRSTDATE(DimDate[Datekey]),-11, MONTH)

to the last date of the current month:

LASTDATE(DimDate[Datekey])

Using this in combination of calculate the sum will now calculate over the entire date range.

Now we have the data set up we can create the sparklines. First we need to copy the rows and paste them to the report and create the sparklines on the report page, check the previous blog post on how to create the sparklines.

I have uploaded the workbook for download here.

I just installed a great BI app on my Windows phone 7 called BlueGranite Nitro.  It’s a free app you can use on top of an XML file (SSRS report’s can generate one for your !). And the best thing is it is for free !

Here is how it looks:

Awesome, right ?!?

All you have to do is create a Reporting services report that has the data you want to analyze on your phone:

All you have to do is: point to the report in the BI app, add the XML renderformat parameters to transform the report into XML and you are done ! This gives us a lot possibilities, you can base your report on top of SQL, SSAS, PowerPivot or whatever you like. You can have your data cached or refreshed every few minutes.

Unfortunately I don’t have a outfacing RS myself to try my own reports on my phone but this app seems very promising.

update:

Today I found a walk through on what you need to do to create a SSRS report that renders specific charts, you really need this when you want to build a report on your own:

http://www.blue-granite.com/site/Solutions/BlueGraniteNitro/CreatingaSSRSdatasourceforBlueGraniteNitro/tabid/175/Default.aspx

http://www.blue-granite.com/site/Solutions/BlueGraniteNitro/CreatingaSSRSdatasourceforBlueGraniteNitro/tabid/175/Default.aspx

I wanted to share this cool program that will start in the next month: The SSAS Maestro program. There are more and more enterprises are using SSAS for large implementation, like Yahoo’s 12 TB SSAS cube as you could have seen on SQLPass. That means also that these large servers and cubes have to be maintained and developed as well. This asks for a more in depth knowledge only a few people in the world currently posses.

The new SSAS Maestro Program is a three-day, deep-dive course on Analysis Services 2008 R2 gives architects and consultants the education and hands-on experience needed to deliver highest scalable OLAP solutions.

Prepared and presented by top industry experts and the SQL Server Analysis Server team, this intensive course gives top SSAS professionals the education and hands-on experience needed to deliver highly complex and highly scalable OLAP solutions using Analysis Services 2008 R2. Today, there is increasing need for expertise to architect deliver and maintain mission critical OLAP deployments and the primary objective of this course is equip you with the latest SSAS best practices and case studies.

SSAS Maestro Program Benefits

Upon successful completion of this course, assessment and case study evaluation by industry experts, attendees will:

• Be a part of the elite group of SSAS Maestros
• Help Microsoft accounts teams deploy highly complex/highly large SSAS projects
• Have access to webcasts and Q&A sessions on the learnings some of the most complex SSAS implementations directly from SQLCAT and the Analysis Services team
• Be showcased on a special section within the Microsoft SQL Server Web page for the SSAS Maestros

As you can see this is not for the faint of hearts You have to live and breathe SSAS for a few years to be able to pass this. So most candidates are known by Microsoft (local subsidiaries, SQLCat or the product team) and invited directly. This will help us spread the in depth knowledge of SSAS to our top partners who can share it further along.

Isn’t this very cool…

One of the most important things you need to know about PowerPivot and DAX is row and column context. Creating measures become a lot easier once you understand it.

Let  me give you an example of a problem that is not so hard once you understand row and column context. I had this question today on my blog:

I am trying to calculate Months Exposure for each model, i.e. how many months since the first Distributed Units
So if a product was first distributed on January 2009, MonthsExposure would be 24, etc.

and then he explained to me how he did this using excel:

This is how I calculate MonthsExposure in Excel:

MinDate ={MIN(IF(Model=”Nokia N8″,DateKey)}

MonthsExposure =DATEDIF(MinDate,TODAY(),”M”)

In PowerPivot this scenario is really easy to solve using the row and column context. I have created a previous blog post about what the Context is with PowerPivot you can check it out here: http://www.powerpivotblog.nl/what-is-that-powerpivot-pivottable-context-they-keep-talking-about

In short: when you work with a Pivottable and you put values on the x and or y ax this will mean the value that you put in the Values part automatically uses the value on that row and column to filter by. An example is this:

I put the sum of Onorderquantity in the Values list, it shows me the total sum, row and column context here is that there is no filter on each row, everything from the table is summed. Now let’s put Country on the Y ax:

Now what happens ? Each row is automatically filtered by the country that you see in the Row. Each row contains the sum of all the values of the field onorderquantity in the fact table for that specific country. Now what happens if you put year on the x ax:

Let’s take a look a what this means for the value in the red circled cell. This cell contains the sum of all the values of the field onorderquantity in the fact table for that specific country (China) for the year (2008). This means this is not just one value but is a set of values that is summed up to get one value. The measure makes sure we summarize it.

Back to the example of the question from my blog. He wanted to have the first date a specific product was sold. Remember that by putting the Products on rows we create a filter of all the rows by that product for the measure that we put in the values. The measure must be an aggregation, the filter returns a lot of rows.

In this case we want the first date a product ever was sold, This fact is captured in the field: FactInventory[DateKey].

Creating a measure with this field and putting it as a value in a pivottable that has Products on y ax (rows) will give a list of all the FactInventory[DateKey]‘s for this Product. We now have to make sure we use an function that returns only one value. In this case we want the first date. We can use this measure: FIRSTDATE(FactInventory[DateKey])

This returns the first date that a product was sold.

Expanding this measure to determine how many days this was exposed would be this: =ROUND(1. * NOW() – FIRSTDATE(FactInventory[DateKey]),0)

I used the 1. * to calculate the difference in days read this blog post for more info: “How can I calculate difference between two dates in DAX (seconds, minutes, hours, days and months)”

I hope this blog post got you thinking about Row and filter context in PowerPivot. It really simplifies building measures if you understand it.

For more info about row and column context (and general DAX) you can watch this great webcast from Ashvini Sharma from the SSAS PM team :

Enriching Microsoft PowerPivot for Microsoft Excel Applications Using DAX (Data Analysis Expressions)

DAX (Data Analysis Expressions) is a new expression language for end users to add business logic to their PowerPivot applications. DAX offers a lot of power and flexibility while retaining the the simplicity and familiarity of Excel like formulas. This session explains the fundamentals and concepts behind DAX and demonstrates how to add business logic in PowerPivot using calculated columns and measures. This session also introduces you to various categories of DAX functions — scalar functions, table functions, aggregates, time intelligence — but is not intended to be a comprehensive overview.

View it here

I have had a lot of question about time intelligent functions lately. Most folks ask me whether they really need to follow all the PowerPivot Time Intelligent Golden rules and what happens when they don’t.

In this blog I will show you what happens when you do not use all the rules, these are the rules you should take to hart:

1. Never use the datetime column from the fact table in time functions.
2. Always create a separate Time table.
3. Make sure your datetime table has a continues date range
4. Create relationships between fact tables and the Time table.
5. Make sure that relationships are based on a datetime column (and NOT based on another artificial key column).
6. The datetime column in the Time table should be at day granularity (without fractions of a day).

So here is what happens when you:

• Do not use a separate time table, this will give you unexpected behavior and results of time intel functions.
  For example:
  http://www.powerpivotblog.nl/ok-why-should-i-want-to-load-an-entire-time-table-in-powerpivot
• Make sure that relationships are based on a datetime column
  When you don’t use a relationship based on a datetime column you need to work around it using the All function
  and this will happen:
  
  read more: http://www.powerpivotblog.nl/powerpivot-time-intelligent-functions-revisited-why-use-all-and-how-to-work-around-it
• You want to use a date column that is based on the day granularity or relationships won’t be recognized (date with hours won’t relate to a date with no hours)
  http://www.powerpivotblog.nl/powerpivot-creating-a-clean-date-column-to-create-your-relationship-upon
• Do not use a continues date range or PowerPivot functions will behave not as expected.
  http://cwebbbi.wordpress.com/2010/06/03/the-%E2%80%98function-xxxx-only-works-with-contiguous-date-selections%E2%80%99-error/

As you can see making sure all Time Intel. golden rules are met is a very good idea, otherwise you will get surprising results or DAX functions that will return no values.

I’m very proud and pleased to announce that I’ll be joining Microsoft as Program manager for the SSAS team as of November 1st. This means some big changes for me, first of all  I’ll be moving from the Netherlands to Redmond to work at MS HQ.

In Redmond i’ll be joining the SSAS team to work on the next versions of PowerPivot and SSAS. I really love the concept of Self service BI and of course PowerPivot, this will give me the opportunity to help shape the future of Self Service BI. The funny thing is that I actually will fill the job opening that was created by Rob leaving the SSAS team.

What will this mean for this blog
Fortunately, nothing. This blog is a personal blog, paid for and maintained by me. I will try to stay very active not only on the current version but also in beta’s. We’ll see where I can find the time to blog. I love the sql/bi community so I want to keep that up while working for Microsoft, at what capacity we’ll have to find out.

What is a Program manager
A program manager is a lot of functions combined in one, gathering information from partners, MVP’s and you guys, write functional specs,  a project manager, working with the dev team, validating the product after the design and evangelize the product, and a bunch of other tasks, for more info see this blog post: How to be a program manager.

I’m looking forward to the exciting time ahead and be your voice on the product team!

Yesterday my article on PowerPivot was published in the dutch .NET magazine. If your interested you can download the article in dutch here:

http://www.dotnetmag.nl/Artikel/1312/PowerPivot-doe-het-zelf-oplossing-voor-de-BI

You have followed the time intelligence golden rules to perfection and you think you can handle all time intelligence DAX functions. But unlike the contoso dataset you are working with actual live data from your own production environment and here you have data until the current month not the end of the year. Like this:

We want to compare the YTD from the current year to the YTD of the previous year to the current period last year. First we select the YTD of the current year by selecting the current year in the slicer and using the normal sum. Now to get the YTD of previous year we do a:

=TOTALYTD(sum(Table1[sales]),
                 DATEADD(datum[Date],-12,MONTH))

What happens here? We do the sum of table1[sales] where we use the value of datum[Date] to calculate the sum of sales one year back. What is the result:

This is not what we expected this is the sales of the entire previous year. It is much more. Why? Well one of the golden rules is that you need to have a complete contiguous time table, that means that in my seperate time table we have loaded dates until the end of the current year. And what does TOTALYTD do? According to BOL : “Evaluates the year-to-date value of the expression in the current context.” The current context is “2010″ and the lastdate of our time table is 12/31/2010. Not the 6/1/2010 that is the actual last date we had data.

So here we are in a deadlock between the time intellegence golden rules and what we want. When we would use the date from the fact table it would give us the correct last date but the time intellegence functions wouldn’t return results because we wouldn’t use our seperate related time table  (read here why).

So what can we do? Well we could make the formula ourself. In our case we want the sum of sales from the same period last year, that means from 1/1/2009 to  6/1/2009. We can use the DAX function DATESBETWEEN to  create a custom date range. We want to get the range of datum[Date] values from the first day of the previous year:

FIRSTDATE(DATEADD(datum[Date],-12,MONTH))

We use the datum[Date] column here because we know this is a coninues data range and it would always contain 1/1. Using dateadd we go back 12 months and firstdate makes sure we get the first value from this range. To select the date we want the calculation to end we use:

LASTDATE(DATEADD(Table1[Date],-12,MONTH))

Here we use the lastdate of the fact table to determine the same date one year back, in our case 6/1/2009.

The total formula would look like:

=CALCULATE(sum(Table1[sales]),
                  DATESBETWEEN(datum[Date],
                                  FIRSTDATE(DATEADD(datum[Date],-12,MONTH)),
                                  LASTDATE(DATEADD(Table1[Date],-12,MONTH))
                  ))

PowerPivot DAX will calculate the sum of  Table1[sales] from a date range between 1/1/2009 and 6/1/2009.

Which gives us the result we want: