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:
The information 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 my 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 in 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.