Aggregate Dimension Based on Fact?
2 posters
Page 1 of 1
Aggregate Dimension Based on Fact?
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)?
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
Re: Aggregate Dimension Based on Fact?
Hi ... mrkb72,
I'm thinking you need a new dimension e.g. Number of Times Shopped, looking something like this:
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
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
Re: Aggregate Dimension Based on Fact?
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
Similar topics
» Calculated dimension attributes based on fact
» Location and population dim/fact
» Why not snowflake if the aggregate dimension is needed for a different fact table?
» Generating a Junk dimension - Cross join or based on actual values in fact?
» Data model for Reporting needs - Event based or fact based
» Location and population dim/fact
» Why not snowflake if the aggregate dimension is needed for a different fact table?
» Generating a Junk dimension - Cross join or based on actual values in fact?
» Data model for Reporting needs - Event based or fact based
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|