I noticed a few people have tried to do similar things that I am asking below. I may have a suggestion in my answer as to how I fix it so i'll ask the question first.
I have historical data that I source from a datawarehouse connection and load into Powerpivot:
Date Store Sales NumTransactions Tag
2010-01-01 A 20000 50 Historical
2010-01-01 B 15000 10 Historical
2010-01-01 C 5000 3 Historical
2010-01-01 D 300000 500 Historical
That connection to Powerpivot is a direct connection to the database server.
And from this data, i calculate a forecast, within excel that spits out a table that is then brought into PowerPivot using a linked table.
Date Store Sales NumTransactions Tag
2010-01-02 A 20010 51 Forecast
2010-01-02 B 16000 12 Forecast
2010-01-02 C 6000 5 Forecast
2010-01-02 D 400000 650 Forecast
From this I want to be able to drive a number of pivot tables and charts which show both sets of data together:
Date Store Sales NumTransactions Tag
2010-01-01 A 20000 50 Historical
2010-01-01 B 15000 10 Historical
2010-01-01 C 5000 3 Historical
2010-01-01 D 300000 500 Historical
2010-01-02 A 20010 51 Forecast
2010-01-02 B 16000 12 Forecast
2010-01-02 C 6000 5 Forecast
2010-01-02 D 400000 650 Forecast
How do i make this happen? I cant seem to make a relationship work as there is multiple dates and multiple store records.
I have tried to join the two together at within Powerpivot in Table Properties -> Query Editor:
SELECT [daily].* FROM [daily]
UNION ALL
SELECT [fcst].* from [fcst]
(Where [daily] is the historical data from the datawarehouse and [fcst] is the linked table within the excel workbook that has been linked into powerpivot)
But the problem is that Powerpivot is looking for [fcst] from the Connection that goes back to the datawarehouse where my historicals are coming from.
I can make a work around of driving two pivot tables out of the two data sources and then link them together into one table in excel by directly referencing the cells of the pivot tables (and then drive charts off that). But its messy, and Im sure this can be done. I just cant figure out how.
Does the answer lie in a Set defined by MDX?
Any ideas would be greatly appreciated.
Thanks!
When PowerPivot creates a relationship, it needs one table to have a column with distinct values that you use as a lookup column for another table. I would suggest that you create a date table. You can get one from https://datamarket.azure.com/dataset/1542c52d-d466-4094-a801-9ef8fd7c6485 if you don't have an easy way to create one. Then you can create a relationship from daily to date and from fcst to date. I would also add a calculated column to daily with just a string "Historical" and add a calculated column to fcst for a string "Forecast". Add another linked table with two rows in it: Historical, Forecast. Then create a relationship from daily to this linked table. If you create a flattened PivotTable with all this in place - you can get close to what you're looking for, but you're probably going to have one column for sales for historical and another column for sales for forecast. You can create a measure in one of the tables (doesn't matter which one) to use conditional logic to use the applicable value based on the current tag and just use that in your PivotTable instead. Do the same for the number of transactions.