Aggregates in Periodic Snapshot Fact Table

View previous topic View next topic Go down

Aggregates in Periodic Snapshot Fact Table

Post  ohmycamote on Mon Aug 15, 2011 5:30 pm

I have a periodic snapshot fact table that captures the number of customers who joined the membership club in a particular reporting period.

In essence, it looks like:

FactKey CustKey JoinDT_Key ReportingDT_Key Count NewMemberCountExistingMemberCount
21 1 88 88 110
222 88 88 110
23 3 88 88 110
24 1 88 89 101
25 2 88 89 101
26 3 88 89 101

Questions:
1.) I made this to a cube in Analysis Services, and when users pull up the cube in Excel (pivot), I don't want it to SUM incorrectly.
E.g.,
For ReportingDT_Key = 88, the count is 3
For ReportingDT_Key = 89, the count is again 3
But it shouldn't sum up 3+3 in the pivot table.

This is similar to inventory as of a certain period in time, but how do you present this to users so it doesn't get incorrectly summed?

2.) Is it right to put NewMemberCount and ExistingMemberCount like that?
The problem I am having with the above is that the number is always going to be separate for both of those and I could not have a single graph that shows total is 3 members and breakdown of which are that: X are new and Y are existing members.

Any help you can provide would be much appreciated.

ohmycamote

Posts : 14
Join date : 2011-07-05

View user profile

Back to top Go down

Re: Aggregates in Periodic Snapshot Fact Table

Post  ngalemmo on Mon Aug 15, 2011 6:45 pm

What you have are semi-additive measures. That is to say measures that are not additive across all dimensions. In this case, they are not additive across time.

This is fairly typical for periodic snapshot tables. Some BI tools allow you to control how a user can aggregate such measures, others do not. In the latter case, it becomes a matter of educating the users.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Aggregates in Periodic Snapshot Fact Table

Post  hang on Mon Aug 15, 2011 7:50 pm

SSAS allows you to specify AggregateFunction (eg. LastNonEmpty) for semi-additive measures so that additive aggregation happens on all others but date dimension. Make sure you set the date dimension type to “Time”

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Aggregates in Periodic Snapshot Fact Table

Post  ohmycamote on Tue Aug 16, 2011 5:39 pm

I looked at SSAS' AggregateFunction (LastNonEmpty), but I am not sure if that would work for me though.

Notice that I need it summed across the Date Dimension but only for each ReportDT_Key:
E.g.,
For ReportingDT_Key = 88, the count is 3 (SUM of COUNT field)
For ReportingDT_Key = 89, the count is again 3 (SUM of COUNT field)

But I don't want it to have a Grand Total/Sum to show that I have 6 because these are snapshots.
So in Excel pivot table, it by default would try to sum it up and show a "Grand Total" of 6.
I can right-click the column/row and click "Remove Grand Total" but I just thought if there's a way to actually suppress it so none of the users would have to see it.

So in summary, it is doing a SUM across the time dimension but only for same attribute values (e.g., same ReportingDT_Key) but should NOT SUM for all (Grand Total).

Let me know if this is at all possible. Thanks so much for your help.

ohmycamote

Posts : 14
Join date : 2011-07-05

View user profile

Back to top Go down

Re: Aggregates in Periodic Snapshot Fact Table

Post  VHF on Tue Aug 16, 2011 6:29 pm

If you took JoinDT_Key out of your fact table so you are not role playing with the date dimenson I think you would get correct results in SSAS using the LastNonEmpty or LastChild aggregate function for your measures. (I use LastChild with my Inventory daily snapshot fact table.)

A periodic snapshot doesn't typically have an additional date/time dimension reference in it because the time dimension is represented by the frequency of the snapshot. JoinDT_Key would make more sense in a separate transactional fact table (or possibly as an attribute in the Customer dimension.)

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: Aggregates in Periodic Snapshot Fact Table

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum