I had a request to allocate a measure down to a lower level based on a percentage. A former colleague of mine called this prorating. I am not sure the proper name, but this is common in the media/advertising industry. In the past I have done this in the database by joining the original row to the lower level percentages and exploding the fact rows. The problem with doing this in the database is that the number of fact table grows quickly and becomes unmanageable. I wanted to use Analysis Service do the allocation down to the lower level and avoid the explosion of fact rows in the database. I found documentation from Microsoft here. It was a good starting point and gave me the confidence that this task could be accomplished by Analysis Services, but it did not include the custom rollup script that is needed in order to aggregate the totals properly. The purpose of this article is to build on the Microsoft documentation by going a bit further in explaining the solution.
The example below shows the basic idea of what I wanted to achieve. I wanted to allocate a single row (shown below as 2000 impressions) by a given percentage of an age demographic. I want to determine the allocated value by multiplying the percentage by the impressions.
Impressions | Age Demographic | Age Demographic Percentage | Allocated Value | ||
2000 | 2-11 | 5% | 100 | ||
12-17 | 10% | 200 | |||
18-24 | 25% | 500 | |||
25-34 | 25% | 500 | |||
35-49 | 10% | 200 | |||
50-64 | 10% | 200 | |||
65+ | 15% | 300 |
To start I created a main fact table called Fact Advertiser and a related detail table called Fact Advertiser Detail in order to handle the one to many relationship.
CREATE TABLE [dbo].[FactAdvertiser](
[DisplayScreenID] [int] NULL,
[DateId] [int] NULL,
[DeliveredImpressions] [bigint] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[FactAdvertiserDetail](
[DisplayScreenID] [int] NOT NULL,
[DateId] [int] NOT NULL,
[AgeId] [int] NULL,
[AdjustedPct] [numeric](4, 0) NULL
) ON [PRIMARY]
GO
The primary key on the Fact Advertiser table is Display Screen ID and Date ID. The primary key on the Fact Advertiser Detail was DisplayScreenId, DateId, and AgeID.
A many to many relationship was defined in the cube. In the diagram below, the FactAdvertiserDetail serves as the intermediate measure group and DimDisplayScreenID is the intermediate dimension. The intermediate dimension serves as a bridge between the two measure groups involved in the many to many relationship.
Once the many to many relationship was defined the relationships looks as follows.
The two scripts were added to the cube in BIDS under Calculations then Script View. The first one creates the calculated measure.
CREATE MEMBER CURRENTCUBE.[Measures].AdjustedDeliveredImpressions
AS [Measures].[Delivered Impressions] * ([Measures].[Adjusted Pct] ),
FORMAT_STRING = "Standard",
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Fact Advertiser Detail' ;
The second script is a custom rollup formula and was added just after the script above. The purpose of the script is to override the normal aggregation of our calculated measure (AdjustedDeliveredImpressions) and perform the calculation on the lowest level BEFORE the aggregation occurs. Without the custom rollup formula the calculated member would happen AFTER the aggregation and give us erroneous results, especially when querying on Age Demographic without using the intermediate dimension (Dim Display Screen).
SCOPE([Measures].[AdjustedDeliveredImpressions]);
THIS=IIF(
IsLeaf([Dim Display Screen].[Display Screen Id].CurrentMember)
,[Measures].[AdjustedDeliveredImpressions]
,SUM
([Dim Display Screen].[Display Screen Id].CurrentMember.Children
,[Measures].[AdjustedDeliveredImpressions])
);
END SCOPE;
After reprocessing the cube, I was able to verify the results by connecting to the cube with Excel and create this pivot table.
