locked
Combining Data from Different Sources RRS feed

  • Question

  • Variations of this question have definitely been asked before, but I haven't found anything specifically like this one... I am looking to load up a PowerPivot spreadsheet with multiple data sources and then combine/merge that data into one table.

    Here's the issue... All data sources are SQL Azure database tables (with identical schema), but one table has historical data (about 80,000,000 rows) and never changes. Then about a year ago, we began "partitioning" our data by month. So we now have January2011, February2011, ..., February 2012 tables. The data in the current/recent months can change daily - obviously the current February 2012 is updated each day with new transations.

    That said, I need to allow our business users to update/refresh the PowerPivot data for say January2012 and February2012 table everday, but they can't be expected to update a full 80,000,000 rows each day, as it takes several hours to download this historical data. And, of course, we need to merge/combine all of this data into one PowerPivot pivot table so the analysts can see the full history of all transactions when they are slicing and dicing the data.

    Any thoughts...?

    Thanks in advance!


    Chris

    Tuesday, February 14, 2012 9:07 PM

All replies

  • Hi,

    To achieve the required functionality you can use BISM tabular models. Create a tabular model by importing your power pivot workbook into SQL server data tools. Create partitions as required. Then deploy it to a SQL Server tabular instance. Then in SSMS you can right click on a table and click on partitions ..

    Now you can select the required partition and process only that partition. Also there are several process options available

    In your case you only needs to add incremental data, so you can use "process add" for incremental processing. You just need to write a query that will add new data to the partition. Then in reporting tools you can get the updated data.

    For more information on process add and script check this link.

    http://www.sqlbi.com/articles/incremental-processing-in-tabular-using-process-add/

    Hope this helps.

    ====================================

    Please mark as answer if helpful.



    Friday, March 16, 2012 7:44 AM
  • Dhanumjay,

    Thanks for that info. Unfortunately, you're speaking a language (BISM, tabular models, etc.) that I'm quite unfamiliar with... One of the biggest reasons we're so keen on PowerPivot is that it takes all of the SSAS stuff out of the equation, as our team is very weak on using Analysis Services alltogether. Right now, we're simply using PowerPivot connected to SQL Azure - something much more feasible for our team (no SSAS whatsoever).

    Anyway, I'll do some research on this stuff to see if it's worthwhile to embark upon an actual project to figure things out.

    Again, thanks for the info.


    Chris

    Friday, March 16, 2012 3:53 PM