PowerPivotPro

I received this Halloween card today, thought it was hilarious, and decided to scan it in:

Why exactly is that hilarious, you ask?  It’s a fair question.

Well, it’s funny precisely because that IS my father.  And yes, he owns that suit.  Not making this up.

It’s even funnier because my dad is actually quite a bit “cooler” than me.  Example: a fight broke out during a YMCA basketball game once (he and I were on the same team) and he waded into it to rescue one of our teammates, while I was getting the hell away.  Then about 10 guys piled on my dad…  and I decided to confuse the enemy by running away more.  Darth Vader can fend for himself.

Even today, when we go places together, women who would never even acknowledge me go all giggly around him.  He has that “cool guy” charisma.  And a full replica Darth Vader suit.  Yeah, even the expensive light saber:

Six feet, four inches of total goofball.

This does NOT count as Tuesday’s post, so stand by for something more meaningful.

Woops I'm sorry, did I change your Filter Context ?

By Kasper de Jonge, original post at PowerPivotblog.nl

I got an excellent question last week on the ask my question page that brought me new understanding of DAX. So finally a new interesting (I hope) blog post on DAX.

Let’s say I have a set of sales per week of a specific brand:

Now I want to have the average of sales per week (slicable by brand) and compare it to the average of the last 3 weekly totals. I prepare  the pivottable :

Read the rest of this entry »

The 5-Point palm technique is a beauty to watch, but o so lethal!

By Kasper de Jonge, original post at PowerPivotblog.nl

I recently had a problem that needed The 5-point palm, exploding fxn technique as described by Rob in his blog post.  I have used SUMX and COUNTX with success a few times before but this time I had a hard time getting my head around the AVERAGEX solution to my current problem.

While talking with Rob about it I realized I was thinking way to difficult. In this blog post I will try to make more sense of the X functions and give you a little help in building you own X function.

First let’s set a scenario. I want to compare the salesamount of each year to the average salesamount over the years. Sound easy huh ..

We start by creating a pivottable based on Contoso with CalendarYear on Y and the sum of salesamount as measure

Next we create a measure to get the average using =AVERAGE(FactSales[SalesAmount])

hmm what happens here?  The description of the function Average is: “Returns the average (arithmetic mean) of all the numbers in a column.”  So what is the function will do is use the lowest grain of data and determine the average of these values, in our case this means he uses all the sales amount values from the FactSales table that are in the current context (year) to calculate the average.

So how do we calculate the average over each year? In Excel this is easy:

And in PowerPivot this is essentially the same, we can use the AVERAGEX function to calculate average over a specific table. The description from MSDN is:

Calculates the average (arithmetic mean) of a set of expressions evaluated over a table.

This means we can change the scope of the average, the normal average automatically will take the lowest grain, but now we can supply our own table it has t o calculate the average. We want the function to do the same as the Excel calculation above. Our function should do average over:

And this is exactly how the AVERAGEX should be used:

=AVERAGEX(all(DimDate[CalendarYear]), FactSales[Sum of SalesAmount])

The all(DimDate[CalendarYear]) would return a table of all years available (it even includes years that don’t have sales but this won’t alter the calculation). For each year in this table it returns the sum of salesamount, we need to use a measure here because it automatically calculates the sum no matter what context were are in. Then it uses this values of these sums to calculate an Average over.

The parameters are just like you say it, we want to calculate the average for each CalendarYear of Sum of SalesAmount.

This measure results in the following values:

The key here is the use of the first parameter supplied to the AverageX function, this is the table it will iterate over. Some other samples:
I want to calculate the Average sales amount of the “Contoso Bangkok No.1 Store”, we create a table by using the FILTER function where filter all storenames by one single store.

=AVERAGEX(FILTER(ALL(DimStore[StoreName]),
	DimStore[StoreName] = "Contoso Bangkok No.1 Store"),
	FactSales[Sum of SalesAmount])

I want to calculate the Average sales amount of the “Contoso Bangkok No.1 Store” and “Contoso Seoul Store”:

=AVERAGEX(FILTER(ALL(DimStore[StoreName]),
	DimStore[StoreName] = "Contoso Bangkok No.1 Store"
	|| DimStore[StoreName] = "Contoso Seoul Store"),
	FactSales[Sum of SalesAmount])

I want to calculate the average salesamount of all years leading up to the current year. First it checks if the current year has values, 2011 doesn’t have values but is available in the dimdate table. As table we want to return all years from the dimdate table before the current year. The current year is selected by values(DimDate[CalendarYear]) because we have set CalendarYear on the y-ax.  We need to check if values returns more than one result because when the subtotal is calculated values will return all years.

=IF(FactSales[Sum of SalesAmount] > 0
	,AVERAGEX(
		FILTER(ALL(DimDate[CalendarYear]),
					DimDate[CalendarYear]
					<= IF(countrows(VALUES(DimDate[CalendarYear])) > 1
						,Blank()
						,VALUES(DimDate[CalendarYear]))
				)
		, FactSales[Sum of SalesAmount])
	, Blank())

The AverageX, CountX and SumX functions are very powerful but difficult question, try to visualize what you want to do or use Excel to get the result you want and work from there.

By Kasper de Jonge, cross post from PowerPivotblog.nl

Since this week the public beta of Predixion Software’s Data mining in the cloud for Excel is available. Those of you who are familiar with the the Microsoft SSAS Data mining Add-ins should be very comfortable with what is inside Predixion Data mining for Excel.  I have done a previous blog post on doing data mining using PowerPivot with the MS data mining add-in where you can see how it currently works .

Predixion Insight for Excel is like a new version of the current SSAS add-in, the Predixion insight team consists of the folks that previously build the Add-in for MS and now started on their own.

The biggest change is that you no longer need an SSAS server installed. All action happens on the Predixion servers in the cloud. Second biggest (for me) is that you can use PowerPivot data as a datasource for you Data mining. Using it in combination with PowerPivot requires nothing more then Excel and a Predixion subscription for data mining. Furthermore the overal UI had been improved to make data mining a more user friendly experience. And it support 64 bits.

From the Predixion site:

Predixion’s intuitive and easy-to-use solution allows users to run predictive analytics in the familiar environments of Microsoft Excel^® and PowerPivot. Whether you are an existing SQL Server^® Data Mining user, a BI specialist or a newcomer to the arena of Predictive Analytics, Predixion Insight™ will enable you to easily create, manage and run powerful and accurate predictive models without extensive training or specific knowledge of the methodologies currently required to create successful predictive projects.

In this blog post we are going to see what are the key influencer are of the number of items on stock from the Contoso sample database. First we need to install the Predixion Insight for Excel, just run setup and the client will be installed within Excel. Next time you open Excel the client will be there. We have two tabs “Insight analytics”:

and “Insight Now”:

The “Insight analytics” tab is mainly for the advanced data mining,the insight now enables you to get started immediately. Before we can do anything we need to connect to the predixion servers with the account we created on the website:

After logging into the Predixion cloud service we can start data mining. I have loaded information from my datawarehouse in PowerPivot for Excel, i have information about my stock. I have loaded the fact table FactInventory that contains the actual nr of stock, this contains 8 million rows . The fact table is related to a lot of descriptive tables that surround the fact table, called the dimension tables. I have loaded a few of these descriptive tables into PowerPivot as well. What do we know about an item that is on stock:

• When was it on stock? Year/month/day
• What Product?
• What Productcost
• Aging of a product in inventory
• The Country of the store it is in.

Of all these properties we want to know what influences the nr of days in stock the most. For this i want to use the “Analyze key influencers”  function. So i click on it.

This gives us a screen where i can select what my source is, Excel or PowerPivot, I select PowerPivot. Now i can select what table i want to analyze, i select the fact table. We could place filters here but i decided to plague the Predixion server all out with my full 8 million rows .

Next we can select the column we want to we want to determine the key influencers for:

Of course we don’t need all the columns to be analyzed, we can select the columns we want to include in our analysis:

And this is where we notice something not right. As you can see we can select DateKey, StoreKey, ProductKey. But when we analyze this it would analyze this as a Key value, instead of Year 2009 it would test for the value 1-1-2009 and Store “Amsterdam” it would check as Integer 12. So we need to do something first, we need to prepare our PowerPivot table so that it contains descriptive values.  Luckily for us this is not that hard, just add a column in the PowerPivot field window using the =RELATED function:

Now we can select these columns in the data mining add in:

Now we are good to go, just click Run and the data mining will be started.

The great thing here is that everything happens on the server, i can start multiple operations at the same time. And of course it being in the cloud i can open this up on another machine and immediately access the results.

One thing i noted is that the information is send to the cloud through an encrypted tunnel so no worry your data can be read while sniffing your network.

When i click on Minimize to Task pane you will see a new Predixion pane will show up where you can see all your tasks:

As you can see i ran this demo before so i can use these results to show the result of the mining Predixion did, just click “Results” and the report below appears:

As you can see it is pretty easy to combine the information you have in PowerPivot with the enormous powers of data mining. The new user interface and the availability of the Predixion servers in the cloud really make data mining available for anyone. Just as PowerPivot makes data analytics available for everyone. The Predixion Insight for Excel works with Excel 2007 and Excel 2010 32 AND 64 bit, of course PowerPivot won’t be available with Excel 2007.

Predixion Insight is also working on a on-premise and dedicated off-site cloud solution which leverages SQL Server, SSAS and SharePoint which they call Enterprise Insight.

By Kasper de Jonge, crosspost from PowerPivotblog.nl

As part of our PowerPivot for the regular Information worker today a blog post on how to use PowerPivot to combine information from two sources into a single information product.

In our case the information is stored inside two analysis services cubes that the BI department had made accessible to us.

We want to analyze the order amount per product, year, month and country we have in one cube with the actual order count we have in another cube.

We start by importing the information from both cubes into PowerPivot, make sure both tables contain the same columns you want to analyze against.

We use the “From database”, “From analysis services” to select the analysis service and cube we want to import from. After that we can select the data to import:

Same for the second cube:

The data from both cubes are loaded into two seperate tables. We want to combine the two tables. To do that we can create a relationship between the tables, for more information read a previous blogpost on relationships. First thing we need to do is make sure that all the columns are identical. As you can see in the screenshots above the month and year columns from both tables do not contain the same values.

We start by creating a new calculated column in which we use DAX to get only the last 4 numbers and create a new year column

The next one is a little harder, the monthscolumn from one cube contain only the names of the months instead of the numbers. To translate the monthname to a monthnumber i created a translation table in excel and loaded it into PowerPivot:

next i created a relationship between the table and the translation table:

Now we are able to use the translated values in our table to create a new column using DAX, using the function RELATED we can get the value from the translation table in each row:

To make sure we can create a relationship we need a unique key in each table to connect a row from one table to a row from the other table. Again for more information check out my previous blogpost on relationships.

A row is uniquely identified by the values of all the columns from a row. SSAS will make sure a row is returned once for each measure because it automatically aggregates rows. So all we need to do now is combine the values from all the columns into a new column and connect the two tables by creating a relationship.

First we create a new column we call Key in both tables using the &-sign or use the Concat function:

Then we create a relationship between the two:

Now we have prepared the the data we can use it into the PowerPivot pivottable, you can see we can use the measure from both cubes in one table:

What is important to notice is that I use columns from the table I indicated as lookup table as filter / slicer / row label. When i would have used the other table we only would have had measure from the orderamount table.

To make it a little easier to use i would recommend to hide the columns from the orderamount table in our pivottable. Go to the table, click design, click hide and unhide.

Here you can hide columns from the pivottable, in our case we only want to keep the measure value in the pivottable:

This will give us only the cleaned up colums in our PowerPivot Field list:

As you can see combining data from two cubes is not that hard, important is identifying  a key column between the two tables. You need to keep this in mind when importing data from cubes to combine them.