5 Replies Latest reply on Jan 26, 2015 2:02 PM by Christian Berg

    OBIEE 2 Fact Table Question

    dileraco

      Hello again everybody,

       

      I want to join another fact table to my model in OBIEE. However, when I join this fact table with my existing fact table, it gives the following error;

       

      [nQSError: 15001] Could not load navigation space for subject area my_sa.

      [nQSError: 15033] Logical table my_fact does not have a properly defined primary key.


      Here is, how I want to join these two facts;


      select *

      from dm_infoman.portfoy_rapor first_fact

      left join dm_infoman.ikts_dm tah second_fact second_fact .cust_no= first_fact.cust_no and second_fact .date= first_fact.date

       

      Why I can not join them? What is the logic behind this situation?

       

      Can you help me please.

       

      Regards,

      Dilek




        • 1. Re: OBIEE 2 Fact Table Question
          Christian Berg

          WHY do you want to do a fact-to-fact join?!

          • 2. Re: OBIEE 2 Fact Table Question
            dileraco

            Hi Christian,

             

            Because our customers xxx value is stored in another table (which is my second fact table), and I need to join both values stored in fact1 and fact2. Is this attitude wrong? I am not the developer of these tables. Somebody creates and say take x value of customer from 'another fact' table. :S

             

            Regards

            • 3. Re: OBIEE 2 Fact Table Question
              Sudipta Chowdhury

              You are not supposed to join two fact table directly. Is has be joined through another dimension table.

               

              Here, in your case you have a customer dimension table and I hope customer key is available in both of the fact tables. Just join the this fact tables with customer dimension table. It should work properly.

              • 4. Re: OBIEE 2 Fact Table Question
                Gianni Ceresa

                Not going into the right or not right to do fact-to-fact joins ... how did you joined them?

                If you add your second table into the LTS of the first one (setting the outer join there) it's supposed to work without problems without caring about primary key as it's a FACT.

                 

                The only issue is that the join will always happen, even when you don't need any data from the second fact table (and if the join duplicate rows of the first fact ... you have wrong figures from that table).

                • 5. Re: OBIEE 2 Fact Table Question
                  Christian Berg

                  Well normally you use conformed dimensionalities to achieve wehat you're looking for. Of course that assumes that the two fact tables contain sufficient FKeys to establish the relationships.

                   

                  Gianni's answer is correct from a technical point of view and can be the cause of some issues, but in terms of OBIEE modelling it's still a more proper approach to use a second LTS rather than an extension of the primary LTS.