Powerpivot data in editorial excel sheets / custom reports based on database data

[peter_72]

I have a general question about Powerpivot usage.

A friend of me asked to support him in a small Powerpivot project:

1. He has to create a report every few months. This report is created in Excel but with many editorial stuff: Summaries, formatting, and additional categories which are not stored in the database. The additional categories are based on actual database data but reordered and recalculed with additional formulas.
2. In the moment he has to create this report every 3-6 months. Every time he has to work for 3-5 days to finish the report.
3. The database contains about ~2 million data rows in several tables.
4. He thinks Powerpivot is a strong tool to access and aggregate/calculate this database data.
5. In the end he wants his report to be updated in a few minutes after refreshing the database data.

All the Powerpivot projects I previously did were pretty easy (create SQL Views, connect them, build relationships, create nice charts/tables and some extra measures in Excel).

In this project I'm not very sure about the best approach:

1. The data in the database has to be recalculated/aggregated with many different and complex formulas. In total I expect about 30-40 formulas. They usually look like: If payergroup=x and productgroup=y then summarize all results except subproductgroup y1. Every group has it's own formulas. This rules can be stored in the database (as an extra view) or applied via measures. The artificial categories are company units, sections or projects which are not stored in the database. Unfortunatly many projects intersect so the data has to be restructured with complex rules.
   1. My current idea is to create several Powerpivot tables based on the original database tables and use measures for the complex calculations.
2. Unfortunatly the resulting Powerpivot tables don't match his requirements. He wants to summerize them in an nice report which is based on artificial categories which are not stored in the database.
   1. The easiest solution seems to keep his current report structure and use Excel formulas to reference the Powerpivot tables stored in an addition Excel sheet. He might use the vlookup function in Excel to regroup existing categories in his report specific categories, so his Excel formulas won't rely on the actual column index in the pivottable.

In the moment I really don't like the current approach for the following reasons:

• It will result in many Powerpivot tables, measures and individual formulas
• It doesn't look very solid in general

Better ideas?

 

[barnettjacob]

Unfortunatly the resulting Powerpivot tables don't match his requirements. He wants to summerize them in an nice report which is based on artificial categories which are not stored in the database.

1. The easiest solution seems to keep his current report structure and use Excel formulas to reference the Powerpivot tables stored in an addition Excel sheet. He might use the vlookup function in Excel to regroup existing categories in his report specific categories, so his Excel formulas won't rely on the actual column index in the pivottable.

To me this is exactly the reason why you SHOULD use PowerPivot!!

One of its biggest advantages is the ability to combine two sets of data seamlessly - there is not reason at all why all of the tables in your PowerPivot window have to come from the same source, in fact if they do you are missing something crucial.

In the company I work for we have a sales database that provides 99% of the data we analyse - the problem is that there is nothing in that database at all to reflect our complex retail trading calendar. By creating a calendar in Excel, importing it into PowerPivot and then linking the date column to the date column in the sales table I can then slice the sales data by any of the multiple categories that exist in my calendar table. The same applies for stores when far more detailed information exists in external systems that in the sales database however I use a PowerPivot relationship to seamlessly analyse my sales data by the enhanced store information in that table.

There is absolutely nothing to stop the outputs being a 'nice report' - by modifying the layouts on the 'design' tab its perfectly possible to create Pivots that don't look like Pivots and a lot of people use an additional dummy header row so they can hide the rows that aren't so pretty. The other trick I use regularly is to keep the Pivot in the backgound and just paste links onto a standard formatted layout (vba or conditional formatting is useful if the size changes).

All in all PowerPivot sounds like the perfect solution to your project - you just need to spend some time understanding it's capabilities.

Jacob