topic Re: Relationship between these two fact tables? in Report Server

Relationship between these two fact tables?

Anonymous — Mon, 31 May 2021 14:01:34 GMT

Hi,

ich have on one side a wager. That is my first fact_wagers. It has attributes like:

wager_id
first_product_id
second_product_id

One the other hand I have a fact for the winnings: fact_wins. This occurs later than the wager got created.

It has attributes like:

wager_id
win_amount
product_id

The relationship between them is 1:n , because one wager could have multiple winnings, because of his first and second product. One win in the first_product is related to one row in the winnings. A win in the second_product creates another row.

How would you join those facts?

Make a dim_wager_id as a degenerated dimension, because I would need that for filtering?

Cheers

Re: Relationship between these two fact tables?

HashamNiaz — Tue, 01 Jun 2021 21:04:02 GMT

Hi Anonymous !

I would model this slightly different;

Dimesnion:
DimWager (Unique WagerID)

DimProduct (Unique ProductID)

Fact: (Bridging Table)

FactWagerProductWins (WagerID, ProductID, WinAmount) - Wager can have multiple ProductID, you don't need to create separate column for each ProductID

Relatiionships:

1 - Many between DimWager - FactWagerProductWins (based on WagerID)
1 - Many between DimProduct - FactWagerProductWins (based on ProductID)

This will remove the of separate FactWager (which has only WagerID & ProductID mappings)

Regards,

Hasham

Re: Relationship between these two fact tables?

Anonymous — Wed, 02 Jun 2021 04:05:51 GMT

That is interessting. In which cases you are able to design a fact_table as a dimension_table?

I mean a wager is in this context a business event, which a customer produces. I never thought about to design this a dimension. Is it allowed to insert a lot of entries to a dim_table?

Re: Relationship between these two fact tables?

Icey — Wed, 02 Jun 2021 09:57:09 GMT

Hi Anonymous ,

Please check if the attachment could meet your requirements:

There are some transformations in Power Query Editor.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.