# Aggregate Dimension Based on Fact?

## 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

**Mike Honey**- Posts : 185

Join date : 2010-08-04

Location : Melbourne, Australia

## 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

» Linux based VoIP Switch with Integrated Billing only $2/port

» The HSP Dimension is 4 years old today!

» Brain Based Therapy

» Linux Based Stable Solution for VoIP Business!!

» Cov Kab Mob Atypical Mycobacteria

» The HSP Dimension is 4 years old today!

» Brain Based Therapy

» Linux Based Stable Solution for VoIP Business!!

» Cov Kab Mob Atypical Mycobacteria

Page

**1**of**1****Permissions in this forum:**

**cannot**reply to topics in this forum