# Modelling question - Multiple fact tables with one to many relationships and querying from many fact tables

3 posters

Page

**1**of**1**## Modelling question - Multiple fact tables with one to many relationships and querying from many fact tables

Its a new capital markets data warehouse in design stage.

I have

deal_deatils_fact - 1 row per deal with all measures at deal level - dealID (Degenerative DIemsnion - DD)

deal_tranche_fact - 1 row per deal per tranche with all measures at the tranche level (TrancheID - DD, the dealID from above - the parent key)

deal_product_fact - 1 row per deal per product with all measures at product level (productID)

There is a one to many relationship between deal_deatils_fact and deal_tranche_fact and deal_deatils_fact and deal_product_fact

many to many relationship between deal_tranche_fact and deal_product_fact . There are identifiers to connect between dealdetails, deal product and deal tranche.

The problem I am encounetring is - In a single report - measures from all the above fact tables are required as separate columns (excel report). with dealID - we can get the measures by joining between the three fact tables - but I read every where that it is not a good practice.

What would be the alternate solution for - joining between the fact tables to extract the data for the report ?

should I using multiple sql queries - 1 sql to extract the list of dealIDs for the search criteria entered by user and separate sqls to extract the tranche and product level measures passing list of dealIDs as filter criteria.

Is this the correct design approach ?

Thanks so much in advance.

Bandi

I have

deal_deatils_fact - 1 row per deal with all measures at deal level - dealID (Degenerative DIemsnion - DD)

deal_tranche_fact - 1 row per deal per tranche with all measures at the tranche level (TrancheID - DD, the dealID from above - the parent key)

deal_product_fact - 1 row per deal per product with all measures at product level (productID)

There is a one to many relationship between deal_deatils_fact and deal_tranche_fact and deal_deatils_fact and deal_product_fact

many to many relationship between deal_tranche_fact and deal_product_fact . There are identifiers to connect between dealdetails, deal product and deal tranche.

The problem I am encounetring is - In a single report - measures from all the above fact tables are required as separate columns (excel report). with dealID - we can get the measures by joining between the three fact tables - but I read every where that it is not a good practice.

What would be the alternate solution for - joining between the fact tables to extract the data for the report ?

should I using multiple sql queries - 1 sql to extract the list of dealIDs for the search criteria entered by user and separate sqls to extract the tranche and product level measures passing list of dealIDs as filter criteria.

Is this the correct design approach ?

Thanks so much in advance.

Bandi

**bandik**- Posts : 4

Join date : 2010-06-24

## Re: Modelling question - Multiple fact tables with one to many relationships and querying from many fact tables

The basic technique is to query each fact separately, summarizing on conforming dimensions, then combine the results using either joins of the summarized result sets or a summarized UNION ALL of the same.

## Re: Modelling question - Multiple fact tables with one to many relationships and querying from many fact tables

If query performance is not a big issue then joing all tables is an easy and acceptable solution. One query is easy to maintain and number of possible errors is less with less code. If query performance is an issue of course depends on many things such as your users expectations, number of rows in the tables, hardware, indexes, overall load and more.

**kap**- Posts : 4

Join date : 2010-07-05

## Re: Modelling question - Multiple fact tables with one to many relationships and querying from many fact tables

kap wrote:If query performance is not a big issue then joing all tables is an easy and acceptable solution. One query is easy to maintain and number of possible errors is less with less code. If query performance is an issue of course depends on many things such as your users expectations, number of rows in the tables, hardware, indexes, overall load and more.

Note that since relationships between fact tables are many-to-many, a direct join will not work. Different facts must be summarized to the same grain before they can be joined.

Similar topics

» Multiple measures in a fact table- modelling question

» Design Question - Multiple Fact Tables at the same Grain

» Modelling Fact Tables That Change

» Modeling Fact Tables that have direct relationships, but at a detail and not a dimension layer

» Single fact table vs multiple fact tables - what is the right way in a dimensional model?

» Design Question - Multiple Fact Tables at the same Grain

» Modelling Fact Tables That Change

» Modeling Fact Tables that have direct relationships, but at a detail and not a dimension layer

» Single fact table vs multiple fact tables - what is the right way in a dimensional model?

Page

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

**cannot**reply to topics in this forum