Thoughts on source abstraction and common functions

[mrtoby]

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

 

[powerpivotpro]

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.)

 

[theBardd]

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.