1 2 Previous Next 16 Replies Latest reply on Nov 18, 2016 8:52 AM by Christian Berg

    Level based Hierarchy

    ark86

      Dear Fellows,

       

      I've seen so many posts related to it but somehow i'm finding it difficult to nail this concept. So kindly help me here

       

      I'm trying to implement following in BMM of OBIEE 11g (made this diagram only for this post)

      First I create an analysis involving following

      1. Claim_ID

      2. Workshop_Name

      3. Workshop_Amount

       

      Everything works fine since Claim_Workshop_Details_Fact is connected to both dimensions (i.e. Claim_Dim and Workshop_Dim).

       

      Now i add Amount from Claim_Payment_Details_Fact which disappears since it can't be grouped to Workshop_Name.

       

      To make this work, I created level based hierarchy of Workshop_Dim and Claim_Dim (although they don't contain any hierarchy here) by simply clicking Create Logical Dimension > Dim with Level-Based Hierarchy. Then i set content level of fact tables as following

       

      Claim_Workshop_Details_Fact:

           - Claim_Dim -> Detail Level

           - Workshop_Dim -> Detail Level

      Claim_Payment_Details_Fact:

           - Claim_Dim -> Detail Level

           - Workshop_dim -> Total Level

       

      As per my limited knowledge, now it should work fine but it doesn't.

       

      Can anyone help me with this?

       

      Thank you

       

      Regards,

      ARK

        • 1. Re: Level based Hierarchy
          Felipe_Idalgo

          Hi,

           

          Did you refresh your metadata and re-run your analisys after RPD change?

           

          Felipe Idalgo

          • 2. Re: Level based Hierarchy
            ark86

            Hi,

             

            Yes i did. A number of times in fact.

             

            Let me explain what my level based hierarchy contains

             


                 - Claim_Dim -> Detail Total -> no key

                 - Claim_Dim -> Detail Level -> key = Claim_ID

                 - Workshop_Dim -> Detail Level -> no key

                 - Workshop_Dim -> Detail Level -> key = Workshop_ID

             

            Thanks.

             

            Regards,

            ARK

            • 3. Re: Level based Hierarchy
              ark86

              Dear Christian Berg,

               

              I asked a similar question before where you helped me. Can you please look into it and comment.

               

              I'll be grateful. Thank you.

               

              Regards,

              ARK

              • 4. Re: Level based Hierarchy
                Christian Berg

                If that diagram is your actual model then it will not work since your Workship dimension must be tied to your Claim Payment Fact as well in the logical model.

                • 5. Re: Level based Hierarchy
                  ark86

                  Thank you for your input. But Workshop is not related to Claim payments at all.

                   

                  I'm trying to follow below approach as it addresses the exact problem i'm facing

                  https://blog.daanalytics.nl/2010/05/31/multiple-fact-reporting-on-non-conforming-dimensions/

                   

                  I don't want to join multiple tables in logical tables as i'm making an information model and it gets very tricky when more business areas are added to RPD.

                   

                  Please share your thoughts over it.

                   

                  Regards,

                  ARK

                  • 6. Re: Level based Hierarchy
                    Thomas Dodds

                    If this diagram is your logical diagram -- then Christian Berg is correct ... it won't work.  Go back through the link you are trying to follow and note that your diagram looks like the physical diagram ... the logical one needs to look like a star, else OBI server is going to choke and give you results like you are seeing.  You are close, but you are missing the key step where you force the logical star

                    • 7. Re: Level based Hierarchy
                      ark86

                      Hi Thomas,

                       

                      Actually I've already implemented this by forcing logical stars. Now i was trying to learn best practices and thought there might be a better approach.

                       

                      I think i'm finally getting how this will work. In my semantic layer, I've tables with first level facts joined to second level facts and then joined to third level facts. So, i guess, the right approach is to make one logical table (by adding multiple LTS) joined with all dimensions and then set content level of each LTS correctly.

                       

                      do I make sense?

                       

                      Thank you for bearing me and I really appreciate it. You guys have been a great help.

                       

                      Regards,

                      ARK

                      • 8. Re: Level based Hierarchy
                        Thomas Dodds

                        "I've tables with first level facts joined to second level facts and then joined to third level facts" -- not sure I follow you ... you should never have a fact joined to a fact ...

                         

                        2 basic rules:

                        1. every measure has a dimensional context

                        2. all dimensional attributes are related through a fact (for dimensional browsing 'factless' facts are often set up so as to not break this rule)

                        • 9. Re: Level based Hierarchy
                          Thomas Dodds

                          "Actually I've already implemented this by forcing logical stars. Now i was trying to learn best practices and thought there might be a better approach."

                           

                          the only practice is to conform to logical stars ... there is no other way.

                          • 10. Re: Level based Hierarchy
                            ark86

                            I've all the required info and will update this post tomorrow.

                             

                            A big thanks again

                             

                            Regards,

                            ARK

                            • 11. Re: Level based Hierarchy
                              Christian Berg

                              Kinda happy I was offline for some hours and Thomas took over because....logical star conforming is the way to do it.

                              • 12. Re: Level based Hierarchy
                                ark86

                                Hello everyone,

                                 

                                Sorry couldn't get a chance to reply to this post.

                                 

                                Bad news is that i'm still suffering to implement 'Level Based Measures'. So here's what I've done. I've made four separate and simple tables to understand the concept and I'll list my steps in detail

                                 

                                I made following 4 tables (2 dimensions and 2 facts)

                                Then i imported these tables in OBIEE. So my physical diagram looks like this

                                so D2 is a conformed dimension while D1 is not. Now my target is to set content level of F2 to 'Total' for D1 dimension. So following is my BMM diagram

                                If you'll say that F2 needs to link to D1 in BMM then my concern is that this is just one simple example. You cannot make single big ideal star schema in a big project where different facts and dimensions don't relate to each other. Level based measures seems to be the answer to me that'll simply display unrelated amount of fact instead of omitting it.

                                 

                                Anyhow, let me show you the level based hierarchical dimensions I made of D1 and D2

                                Then i set the content level of both facts

                                 

                                For F1

                                For F2:

                                I think F2 amounts shouldn't disappear after selecting Total Level of D1 inside F2 LTS. But F2 measure disappeared as soon as i included D1

                                Sorry for the long post but I just want to be sure that I'm not missing some simple point to nail it.

                                 

                                Thanks.

                                 

                                Regards,

                                ARK

                                • 13. Re: Level based Hierarchy
                                  Thomas Dodds

                                  Your logical model diagram looking like your physical diagram is the problem ...  You should one logical fact table with both physical facts as LTS and set levels appropriately.

                                   

                                   

                                  "You cannot make single big ideal star schema in a big project where different facts and dimensions don't relate to each other. " .... Oracle BI Apps has ONE BMM (Core) with hundreds of fact (with multiple LTS) and dimension logical tables in addition to hundreds of logical hierarchies. Conformity and non-conformity existing in harmony.  So, yes, it can be done.

                                  • 14. Re: Level based Hierarchy
                                    Christian Berg

                                    Why is 1A_F2 not tied to 1A_D1 in the logical model? How should the system ever relate these entities then if you don't tie them together?!

                                    1 2 Previous Next