Aggregate Dimension Based on Fact?

View previous topic View next topic Go down

Aggregate Dimension Based on Fact?

Post  mrkb72 on Mon Mar 21, 2011 10:32 pm

I have a business requirement to report the distinct accounts from a fact transaction table by the aggregate number of transactions by Merchant.

Let me explain in more detail:
The fact table has the following fields (simplified for this example):
AccountID, MerchantID, TxnAmount

I have 3 measures in my cube: TxnAmount, Count, Distinct Count By AccountID (In a separate measure group)

AccountID and MerchantID are surrogate keys to Dimensions.

What I would like to be able to report is: for a specific Merchant, by the number of times shopped (1,<=2,<=3...) the number of distinct accounts.

I am very confused how I would be able to accomplish this. It seems like the aggregation of the fact becomes a dimension itself. In addition, the cumulative nature of the number of times shopped (not just 2, but 1 and 2) confuses me.

Is this possible? Can it be written in MDX(not something I understand with great depth)?





mrkb72

Posts : 2
Join date : 2011-03-21

View user profile

Back to top Go down

Re: Aggregate Dimension Based on Fact?

Post  Mike Honey on Tue Mar 22, 2011 11:43 pm

Hi ... mrkb72,

I'm thinking you need a new dimension e.g. Number of Times Shopped, looking something like this:

Number of Times Shopped Key Number of Times Shopped Label
1 1
2 <=2
3 <=3
... ...

Then I would try a deliberate cross-join to your fact table, something like this:

select fact_table.*, Dim_Number_of_Times_Shopped.Number_of_Times_Key
from fact_table
INNER JOIN
(
select AccountID, MerchantID, count(*) as Number_of_Times_Shopped_Count
from fact_table
group by AccountID, MerchantID ) AS fact_table_Number_of_Times_Shopped
ON fact_table.AccountID = fact_table_Number_of_Times_Shopped.AccountID
AND fact_table.MerchantID = fact_table_Number_of_Times_Shopped.MerchantID
INNER JOIN Dim_Number_of_Times_Shopped
on fact_table_Number_of_Times_Shopped.Number_of_Times_Shopped_Count
<= Dim_Number_of_Times_Shopped.Number_of_Times_Key

This will become a "bridge" table (or view) in your cube, deliberately "double-counting" each transaction by the Number of Times Shopped. For example a fact row with a count of 3 will appear as 3 records, with Dim_Number_of_Times_Shopped.Number_of_Times_Key values of 1, 2 and 3. The "bridge" table has no measures (or a hidden Count if you have to have one e.g. in SSAS) so the duplication doesn't matter. The "bridge" table is then joined to the Number of Times Shopped dimension.

Then you can filter on the Number of Times Shopped dimension and only see the relevant transactions.

Sorry that got a bit complicated - hope it makes sense.

Good luck!
Mike
avatar
Mike Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

View user profile http://www.mangasolutions.com

Back to top Go down

Re: Aggregate Dimension Based on Fact?

Post  mrkb72 on Wed Mar 23, 2011 12:09 am

Ah! That is exactly the answer. It always seems so simple when someone explains it. So an account that shops at merchant x gets 2 records in the bridge table, 1 for number of times shopped=1 and 1 for number of times shopped=2. Then when I select number of times shopped either 1 or 2, this account will be counted in either label.


mrkb72

Posts : 2
Join date : 2011-03-21

View user profile

Back to top Go down

Re: Aggregate Dimension Based on Fact?

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