Thoughts on source abstraction and common functions

mrtoby

New Member
Joined
Feb 9, 2012
Messages
1
Hi everyone

I am new to the world of PowerPivot, and I am struggling with some basic architecture problems.

  1. How do I get an abstraction layer between my operational sources and PowerPivot, so that if I have many PowerPivot reports, and my source changes, I need to make adjustments in only one place, not in every report?
  2. Where to I perfom common functions that are needed for all my PowerPivot reports in one specific use-case? I don't want to define relationships and calculate measures that are common to all reports over and over in every report.
Now, the nice (and timeconsuming) way to solve this is to use an ETL process to get the data from the source into a new database, preferably in a star schema. Then use PowerPivot to build reports on this.

But if I do it like this, I do a lot of work in ETL that would be much simpler in PowerPivot. So, my next thought was to create a master PowerPivot where I do all of the work I would have done in ETL, and then individual reports would use this master PowerPivot workbook as (only) source. This however seems only possible if I use SharePoint :(

Are there other solutions to my two problems? Am I not getting the whole picture here? Or am I getting it and the only problem is that I don't run SharePoint?

Thanks for your help,
Toby
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Hello, Mr Toby :)

First of all yes - SharePoint is required for the hub/spoke workbook approach, which we call core/thin at Pivotstream.

Secondly, I really don't see PowerPivot as a competitor to ETL, but as a complement to it. I can't "reshape" data in PowerPivot.

But I think we agree on that, and all you are really saying is "I don't want to write the same 50 calculated columns over and over again, AND it's easier to write calculated columns in PowerPivot than in the database."

If that's the crux of what you are saying, then I very much still think that doing them in the db is the right approach when it's an option. We even do that at Pivotstream, where we have SharePoint AND hub/spoke.

I wonder if perhaps you are under-using measures. Are you writing lots of numerical calc columns, or are they "property" type columns (TRUE/FALSE, grouping, etc.)
 
Upvote 0
Toby,

Whilst PowerPivot is a great tool for 'trying things out', running scenarios or simulations on the fly by mixing up your enterprise data with some other data that you have, I would have thought that the best approach once that you have determined that this calculated column, or this data element has become part of the enterprise data set, then that is the time to put it back in the main database. I am certainly of the view that DWs are great places to enrich data from the ERP or transaction system(s), and that this is a relatively simple and cost-effective solution.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top