I am building a reporting system of production profiles for a set of assets. The key data structure is as follows:

Version  Field               Action    Profile          Date          Production     RiskCategory

v1          Field1         Action00    00_Low      31/1/2014        5000         0

v1           Field1         Action00    00_Low     28/2/2014         4980        0

v1           Field1         Action00    00_Low     31/3/2014          4950       0

v1           Field1        Action00      00_Base     31/1/2014        5300       1

..

v1           Field1        Action00      00_High     31/1/2014         5600       2

..

v1           Field1        Action01      01_Low     31/1/2014        8000         0

...

 v2           Field1         Action00    00_Low    31/1/2014          2000         0

 ...

Ie. the data consists of production profiles. Each [Version] has many [Field]s who in turn have many [Action]s who in turn have a High, Base, Low [Profile]s who in turn have a [Production] for each [Date].

Data size:

Each profile has a monthly entry from 2014 to 2034. There are typically 10 Actions per field, and maybe 4 fields per version. So number of rows per version is: 12months*30years*3profilesperaction*10actions*4fields = 43200. Number of versions per data set is not decided yet, however as time goes by there will be a steady increase in nr of versions... it all depends on the speed and practicality.

Related tables:A related table (on Version and Profile) adds [Risk Category] to the [Profile] (and other attributes not relevant here. There is also another related table on [Version] [Field] [Date] not relevant here.

Problem Description: I use PowerPivot to tie 3 tables togetether via composite keys (calculated column = [Version]&[Profile] etc). So I dont have to repeat [RiskCategory] for each date in the profile table and makes QCing inputs far easier. The purpose of the database is to take the production profiles and slice them in different ways to satisfy different input systems.

The most challenging is that I need to calculate the incremental contribution from the Base and High  [Profiles] in each action.

Generate a calculated column that for each Date in each Action calculates:

if [RiskCategory] (thisrow) = 1, then [Production])

If [RiskCategory] (thisrow) = 2, then [Production] (where [RiskCategory]=2)  - 

                                           [Production] (where[RiskCategory]=1)

If [RiskCategory] (thisrow) = 3, then [Production] (where [RiskCategory]=3)  - 

                                           [Production] (where[RiskCategory]=2)

My solution below is a Calculated Column that uses Calculate Allexcept and Earlier to remember the row context. However, the calculation is a little slow: 5 sec maybe - which will get worse as I add more versions and possibly more fields with more actions. and I would like to see if I can speed it up:

=if( Profile[RiskCategory]>1, 

Calculate(SUM(Profile[Production]), Allexcept(Profile,Profile[Date],Profile[Version],Profile[Action]), Profile[RiskCategory] = Earlier(Profile[RiskCategory]))-
Calculate(SUM(Profile[Production]), Allexcept(Profile,Profile[Date],Profile[Version],Profile[Action]), Profile[RiskCategory] = Earlier(Profile[RiskCategory])-1)

,Profile[Production])

Any suggestions?

Hope this is clear.

Thanks

Lars

• Edited by larsito22 Thursday, November 20, 2014 10:37 AM

5 seconds of process time is not that big of a deal. If your concern is refreshing the data every day, I would recommend setting it up on SharePoint and enabling automatic refresh, so the processing is completed overnight and you need not be concerned with it.

Hi Greg,

Thanks, problem was that when working with the Pivot tables its slow and sluggish. I wondered if what I had done (ie the Calculate) statement was not "best practice" and if there are ways of improving the speed. I have only been fiddling with PowerPivot for a week or so.

Thanks

Lars