olf
olf

Reputation: 872

Can I create a many-to-many relationship in my data warehouse / tabular model on different fields?

Imagine the following situation:

I am designing a data warehouse / Tabular cube/model for a broker company. This means that when the broker creates a Contract, there is a Buyer and a Seller.

So when I create my Fact_Contracts it looks something like this:

CREATE TABLE [dbo].[Fact_Contracts](
    [FactContractId] [INT] IDENTITY(1,1) NOT NULL,
    [ContractBuyer_Client_Key] [INT] NOT NULL,
    [ContractSeller_Client_Key] [INT] NOT NULL,
    [ContractDate_Key] [INT] NOT NULL,
    [ContractAmount] [DECIMAL](18, 2) NOT NULL,
)

Now I have my Clients Dimension:

CREATE TABLE [dbo].[Dim_Clients](
    [ClientKey] [int] IDENTITY(1,1) NOT NULL,
    [ClientBK] [int] NOT NULL,
    [ClientName] [nvarchar](20) NOT NULL,
)

Let's say I have 1 contract where ContractBuyer_Client_Key = 1 and ContractSeller_Client_Key = 2 and the ContractAmount = 1000.

Now when I look in my Tabular Cube, I would like to create a report, showing me details about my customers and my ContractAmount. The output should be like this:

FactContractId  Client_Key   ClientName   ContractAmount
10              1            Abraham      1000
10              2            Björk        1000
--------------------------------------------------------
Total                                     1000
--------------------------------------------------------

Is it possible to create such a reciprocity report where I combine two different columns from my Fact-table?

Please note: A solution where I divide the Total by 2 is not an option. There are other issues which makes this undoable: Sometimes I have a third party as part of the contract which has not been shown on this simplistic outline of my tables. So my total needs to be the sum of all unique Contracts.

Upvotes: 0

Views: 38

Answers (2)

olf
olf

Reputation: 872

Marmite Bomber did put me in the right direction, but the answer was not 100% correct.

Creating a contract_role view as mentioned is correct. But instead of just adding data from one role, I have instead done it in dax, using a SUMMARIZE:

SumOfAmount := 
CALCULATE(
     SUM(Fact_Contracts[ContractAmount]),
     SUMMARIZE(
         contract_role,
         Fact_Contract[FactContractId]
    )
)

This seems to work even if I have more roles than just a Buyer and a Seller.

Upvotes: 0

Marmite Bomber
Marmite Bomber

Reputation: 21053

This is your fact table

select * from contract;

  BUYER_ID  SELLER_ID     AMOUNT
---------- ---------- ----------
         1          2       1000

Create a role view on a top of it

create view contract_role as         
select 'Buyer' role_id, BUYER_ID client_id, amount from contract
union all
select 'Seller' role_id, SELLER_ID client_id, amount from contract;

select * from contract_role;

ROLE_I  CLIENT_ID     AMOUNT
------ ---------- ----------
Buyer           1       1000
Seller          2       1000

Now you may report all roles, but consider the total only from one of the roles

select CLIENT_ID, AMOUNT,
sum(case when ROLE_ID = 'Buyer' then AMOUNT end) over ( ) as total_amount
from contract_role

 CLIENT_ID     AMOUNT TOTAL_AMOUNT
---------- ---------- ------------
         1       1000         1000
         2       1000         1000

Upvotes: 1

Related Questions