Kasper de Jonge PowerPivot Blog

yes, the word that any BI or IT pro dreads: Kerberos .. Imagine you want to run Power View in a SharePoint farm on top of a SSAS database running in Tabular mode. You probably want to use security so you can secure the data by the user that is actually running the report using the AS security features.

But most likely you won’t run your AS instance on a machine that is inside of the SharePoint farm. When all of your machines are inside the same SharePoint farm, SharePoint will take care of passing the credentials between the machines. But as soon as one of the machines lives outside of the farm you have to setup Kerberos to make sure the security is correctly passed on between machines. Configuring Kerberos is a lot of (hard) work if Kerberos is not already set up correctly in your organisation.

But there is some good news on this with SQL server 2012. In SQL Server 2012 we introduce the BISM file that allows us to start a Power View report based on the connection information in this BISM file. Whenever a connection is made from Power View to the Tabular database it tries to connect using the credentials of the user that is executing the Power View report. If Kerberos is not configured this connection will fail regardless of him having access.

But there is a fall back scenario, when using the BISM connection to connect to a tabular database ADOMD will give it another try using the execution account the Reporting Services app server is running under and then switch to the actual user who initiated the connection. All you have to do is add the execution account the Reporting Services app server is running under to the administrators of the AS instance and this connection will succeed even without Kerberos is set up. Admin connections are always allowed cross machines, under the covers ADODM uses the effectiveusername connection string parameter to switch to the actual user initiating the connection after connection is being made.

This post is dedicated to Marc Valk my esteemed former colleague with whom we battle the Kerberos beast several times at my previous job .

One of the question that regularly comes up is: “He I have created a tabular model in BIDS and deployed it to my test server, now I have changed a measure or a calculated column I deploy those changes and all of my tables are again loaded. I don’t want that ! I just want to deploy some metadata”.

Well actually that is exactly what we do, but the UI is a little deceiving.

When you deploy for the first time and any time after you get the same UI:

But under the covers what we do is:

• Update the metadata of the database on the server
• Send a Process Default command to the database

I want to point to a blog post created by a colleague Pej Javaheri. This blog post will describehow to set up a complete machine with Crescent, Tabular project and PowerPivot for SharePoint:

check it out here.

A few weeks ago I got this interesting question from someone. He wants to compare the sales growth of his products in the months since the introduction of the product. Lets say “Product One” first selling date is 1/2/2010 and “Product Two’s” first selling date is 2/1/2010. If we want to compare the sales since introduction we want to compare month 0 between the two products. Month 0 for Product One is 1/2/2010, for Product Two it is 2/1/2010.

I created a table with some data to simulate this:

As you can see different products have different start dates. The first thing we need to do is enrich our table with a new column that will determine the amount of months between the date from the current row and the date of the first sale for this product. Is we have that month we can use this in our report to compare months, first month of sales for each product will be 0, the next month 1 and so forth. This makes comparisons very easy.

Let’s get started with getting the first date for each product. So what do we have to here ? For each sales transaction in the table we need to check all the other sales transaction from the same product to determine the first salesdate. We can do this with a Calculate column using the MINX function:

=MINX(
FILTER(Sales,Sales[Productname]=EARLIER(Sales[Productname]))
,Sales[SalesDate])

So what does this formula do ? Give me the MIN value of Sales[SalesDate] for all the rows in the Sales table where the Sales[Productname] is the same value as the Sales[Productname] from the current row.

The function to note here is EARLIER(). What does this do ? well FILTER(Sales) gives us a filter over the entire Sales table, we no longer are aware of the current row of the table we are in, the earlier goes to the previous row context, being the current row of the table we are running the formula of the calculate column in.

This results in the following value for each row:

As you can see we now have the first date for each product available. Now all we have to do is calculate the difference between the dates in months. Unfortunately DAX doesn’t have a datediff function so we have to do it the Excel way, I am using a technique described in a blog post from Vidas Matelis for it.

= (YEAR([SalesDate]) - YEAR([First sales date]) ) *12
+ (MONTH([SalesDate])- MONTH([First sales date]))

This results in the column that gives us the months for each sales transaction compared to the date of introduction

Now we can compare the sales from these products in a pivottable:

But this of course is not the end of it, we now only see the sales for each month. We would like to see the sales since product introduction to the current date.

This is pretty simple formula:

=if(HASONEVALUE(Sales[MonthSinceIntroduction]),  CALCULATE([Sum of Sales], Sales[MonthSinceIntroduction] <= VALUES(Sales[MonthSinceIntroduction])) , BLANK())

This formula will check if only one month is selected in the current context, and for this single month calculate the [Sum of Sales] where the MonthSinceIntroduction is smaller or equal to the current MonthSinceIntroduction value we are in on the column.

This will result in this pivottable:

But hey.. hold on..why don’t we see values for the months we don’t have sales ? The dax formula should work. I had to dig deep to find an answer and had to call in the troops (called Jeffrey Wang in this case).

This behavior is a caused by the MDX query that is being executed. Remember the data of the Pivottable is being queries by MDX. MDX will take care of retrieving the rows, column and filters, after it has retrieved the entire. Each DAX measure is being evaluated for each cell (or the cross section between rows, column and filters). One of the behaviors that MDX has  is that when there is no value in the table for a row / column combinations from the same table MDX Autoexists kicks in. Autoexists will make sure that if the combination between rows, columns and filters does not exists within the same table it will NOT execute the formula for that spot. Since MDX is designed for a Multidimensional model where for each fact table you have a separate dimension this made sense. For PowerPivot this makes a little less sense but still pretty simple to work around.

1 Use Crescent that will use DAX to create the report and does not have MDX autoexist.

2 create a second table with values 0 to 25 in it and link it to the original table and use those values in the report. That will solve the problem:

Observe I did not change the formula at all.

Now I can create a chart for it as well:

Often users want to import multiple identical Excel files into PowerPivot as one table. Unfortunately PowerPivot does not have native support for this.

The good thing is that there is a nice work around which someone else already blogged about. You can find out how to do it here: PowerPivot from Identical Structure Excel Files

Last week I head the pleasure of attending TechEd North America in Atlanta, both presenting and meeting a lot of our customers. Teched was our great coming out party for the Analysis services Denali release. In this post you can find a lot of references to TechEd sessions and relevant blog posts.

The most important thing to come out of Atlanta is the updated vision and roadmap for the Analysis services team:
Watch the session here, this also includes a general overview of what is new for SSAS in Denali. The corresponding SSAS team blog post here and another interesting one from Chris Webb.

Other interesting sessions are:

• Introducing the Next Generation Design Tools for Microsoft SQL Server Analysis Services

• Enriching Your BI Semantic Models Using Data Analysis Expressions (DAX) (very interesting of course )

• Killer Real-World PowerPivot Examples

And of course a awesome Crescent session:

• Abundantly “Crescent”: Demos Galore

Also make sure to check out the BI Power Hour: BI Power Hour which is always fun with demos of the latest technology. More detailed blog post will become available when Denali CTP3 becomes available.

If you want to know when CTP3 for SQL Server Denali (including PowerPivot and SSAS) will become available you can subscribe to a notification here.

I had an interesting question on my ask a questions page, one that I have (or something similar) heard a few times before. So I decided to do make a blog post on how to solve it

Our company tracks changes in the resource planning using a data warehouse. An example would be:

Assignment 1: Ian 8 hours planned for 3-Jun; valid from 1-jan-2011 until 1-feb-2011.
Assignment 1: Ian 6 hours planned for 3-Jun; valid from 2-feb-2011 until 15-feb-2011.
Assignment 1: Ian 7 hours planned for 3-jun; valid from 16-feb-2011 until infinity.

I what to enable our end-user to select any date level (Date, Week, Month, year) and get the current planning for that planned date (3-jun).

First of all I created a table in Excel to represent the data:

Next I create a date table that I can use to select dates at different levels using a slicer:

I did not create a relationship between the two tables because I want to use the values of the date table to search for values in the planning table. So far i have prepared the tables, next is creating the report.

First I created a workbook and a new pivottable with the slicers from the date table and put the employee name on rows:

now for the interesting part, on to the DAX formula. We want to use the values from the slicers to determine the date we want to search the planning table on. We can use values(columnname) to get the current value of the slicer and create a date from that on the fly. For example values(‘Date’[year]) will get the current value of years from the slicer. IF you join all 3 together you can create a new Date: Date(values(‘Date’[year]),values(‘Date’[Month]),values(‘Date’[Day]))

Slicers can have multiple values selected at the same time so we need to make sure that only one is selected at the same time, otherwhise we cannot create this date. We can do this with the following formula’s:

=if( countrows(values(‘Date’[year])) = 1 &&countrows(values(‘Date’[Month])) = 1 &&countrows(values(‘Date’[Day])) = 1,

Now on to creating the DAX formula to finding the right values in the planning table:

=if( countrows(values('Date'[year])) = 1
&&
countrows(values('Date'[Month])) = 1
&&
countrows(values('Date'[Day])) = 1,
CALCULATE(sum(Rates[Hours]),
	FILTER(Rates,
	Rates[Startdate] <= Date(values('Date'[year]),values('Date'[Month]),values('Date'[Day]))
	&&
	(Rates[Enddate] >= Date(values('Date'[year]),values('Date'[Month]),values('Date'[Day]))
	|| ISBLANK(Rates[Enddate]))
	)
	)
,Blank()
)

Let’s look at this step by step:

First we want to calculate the sum of Hours.

CALCULATE (sum(Rates[Hours]),

for the rows in the selected date range:

FILTER(Rates,
Rates[Startdate] <= Date(values(‘Date’[year]),values(‘Date’[Month]),values(‘Date’[Day]))
&&
(Rates[Enddate] >= Date(values(‘Date’[year]),values(‘Date’[Month]),values(‘Date’[Day])) || ISBLANK(Rates[Enddate])) )

Filter the Rates table to return only the rows where
the Rates[Startdate] <= the selected date
AND
Rates[Enddate] >=the selected date OR the Rates[Enddate] is empty.

When this formula is used we get the value we wanted based on the slicer we selected:

Make sure you turn the following “Visually indicate items with no data”  for each slicer (right mouse click, slicer settings):

Another blog post with a similar question and answer you can find here: http://www.powerpivotblog.nl/get-values-within-a-start-and-end-date-using-powerpivot-dax

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.