Stack data tables from different sources with PowerPivot

[bauer24]

Has anyone come across a way to stack historical data with 'live' data?

So imagine if I have live data pulling from a database (Analysis Services) that shows 2012 sales data to date. In a separate Excel file I have 2011's sales data, in the same structure.

I've seen online that a "union all" statement can be used to join two excel files in PowerPivot but has anyone stacked database data with Excel data using a similar method?

I can post more info if it helps.

Thanks!!

 

[JavierGuillen]

The short answer is no, you can't do stack them using a similar method. You can, however, use both sources in one PowerPivot report. Is your intention to stack them or can this requirement be ignored as long as the final report properly represents data from both sources?

 

[bauer24]

I'm hoping to make a pivot chart which shows sales over time. So I imagined that I would need to stack both datasets on top of one another. It's a real shame (and a massive drawback) if this isn't possible with PowerPivot

 

[JavierGuillen]

Stacking tables is not always needed to create the kind of report you need. By generating appropriate lookup tables, you can create a consolidated pivot chart with data from your relational database (or OLAP cube) as well as your excel file.

The following blog entry explains how to use two tables in a consolidated report, without the need for stacking them:

Merging data in PowerPivot « Javier Guillén

 

[Fazza]

I don't know about Power Pivot but in earlier versions of Excel joining data from different sources should be do-able. Please try something like

SELECT fields
FROM `fully qualified reference to database`.[table]
UNION ALL
SELECT fields
FROM `qualified reference to Excel file`.[table]

 

[bauer24]

I found the trick is to transfer the cube data into an Excel sheet by using a "flattened pivot table"