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

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.

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