1 Reply Latest reply on Dec 4, 2016 2:19 PM by Maciek La

    How to create a data model when we have more than one fact table

    sandesh ag

      Hi

       

      I have a data with two dimension and 3 fact tables.

       

      Dimension table

      1. Order

      2. BU

       

      Fact table

      1. Orders

      2. Cost

      3.Revenue

       

      All the tables has a order_id and Bu so that I can join based on that.

       

      I want to create a data model in such a way that I can see total aggregated value of Orders, cost and revenue both on project and Bu wise in a single table.

       

      How best I can join Project & BU with 3 fact tables.

        • 1. Re: How to create a data model when we have more than one fact table
          Maciek La

          From the description I assume that your dimension tables are project and BU not order and BU.

          Not seeing the data makes it bit hard but as order seems to be the lowest level, then duplicating this fact table would cause the most problems..I would do inner joins like:

           

          Project DIM--- \              /------Revenue

                                             Orders

          BU DIM ---------/          \-------Cost