2 Replies Latest reply on Nov 6, 2012 7:07 AM by Maqs-Oracle

    making fact table


      im having a relationship in the physical layer:

      T1--< T2 --< T3 --< FactTable >-- T4

      The fact table also has many to 1 relationship to another table which has its own relationships to other tables but the problematic area is here.

      I wanted to change this relationship in the business model to be fact table will have many to one relationship to all the table (including T1 and T2) to resemblance a star schema, removing the relationship between T1 to T2 and T2 to T3 (the complete design is much more complicated so im trying to do some muscling to reduce the complexity).

      In the business model and mapping layer, i do:

      1. Created 2 new column in factTable T1key and T2 key. (obviously no T3 key column needed since fact table already contains that)
      2. In the logical source table of the factTable, i added T2 and T3, making the relationship there such as:

      factTable - T3: inner join

      T3 - T2 : inner join

      3. Navigate to the column mapping tab:
      - map T1key to t1key (foreign key of T1 table that T2 table contains) in T2 table
      - map T2key to t2key (foreign key of T2 table that T3 table contains) in T3 table

      4. Navigate to the foreign key tab:
      - adding foreign key from factTable to T2 on factTable.T2Key = T2.t2Key
      - adding foreign key from factTable to T3 on factTable.T3Key = T3.t3Key

      I already has the "flag allow logical foreign key join creation" checked

      => when i try to create new analysis with a column from t1,t2,t3,t4 => error: No fact table exists at the requested level of detail.

      This has happened to me before when i dont create the foreign key in the physical layer and doing it on the business model layer..... Im thinking the foreign key i created at step 4 also somehow ignored by BI thus the problem... What am i missing??
        • 1. Re: making fact table
          can anyone tell me if im in the right direction at least??
          • 2. Re: making fact table
            I found it very difficult to understand your problem.

            Based on what i understood

            T1 < T2 = 1:M

            T2 < T3 = 1:M

            T3 < Fact = 1:M

            T4< Fact = 1:M

            simplest way to represent this as Star in BMM is

            Create a Logical Table T3 with T3, T1 and T2 in the LTS
            Create logicla table T4 and Fact

            So you will have a perfect star in BMM T3< Fact> T4.

            In physical layer it is still going to snowflake T1< T2< T3<Fact>T4 whatever you do.

            So I think you are complicating things too much by adding T2 and T3 in Fact logical table.

            Hope it helps.