12 Replies Latest reply on Jan 4, 2019 4:09 PM by Robert Angel

    Many fact tables in one BusinessModel

    Alex Sharkov

      Hi all I have now many fact tables in one BusinessModel. There are: Distribution, Payments, Action (and many common entity joined with facts) I understand that best way to do 3 BusinessModels - one model with one fact table with all other entity, to do 3 star schemas but what to do if business user whant to join all Distribution, Payments, Action with a common attribute (so for ex.:contract_no) ? I cannot say .."Hey gays you can do it with Excel"

        • 1. Re: Many fact tables in one BusinessModel
          Gianni Ceresa

          Hi,

          The theory is a star schema per fact table, but nothing forbid you of having these 3 star schemas into the same business model. This is actually how you do those things. A business model isn't a single star schema, it's more all the pieces representing a business need, knowing that each fact table need to be modelled as a star schema.

           

          In your case inside the same business model you will have your 3 fact tables, and all the dimensions will be linked to these fact tables based on your needs. All the dimensions shared by 2 or more fact tables will be conformed dimensions and are those which allows you to analyse data from multiple fact tables in the same analysis.

          All the dimensions which aren't conformed it's up to you to decide the behaviour : no links at all with the other fact tables or define a join in the business model and use the content level to tell OBIEE how it needs to behave.

          1 person found this helpful
          • 2. Re: Many fact tables in one BusinessModel
            Thomas Dodds

            In extension to Gianni Ceresa ... use the Subject areas to present different perspectives of the same model ... if there is a possibility of relating factual data across dimensions then keep the facts and dimensions in the same model.

            1 person found this helpful
            • 3. Re: Many fact tables in one BusinessModel
              Christian Berg

              Alex Sharkov just to add my 10 cents: Did you ever get a training or detailes workshop on how RPD modelling works and why to choose which approach?

               

              OBI is a very powerful platform but in order to make the best out of it there are certain things which need to be understood. So you can take the right decision and comprehend from the beginning where that decision will lead, what it wil allow and what it will prevent.

              • 4. Re: Many fact tables in one BusinessModel
                Gianni Ceresa

                In addition to all the previous: it's a good things to also don't take the opposite approach and fit everything into a single business model forcing things to stay together when it doesn't make sense.

                As an example a screenshot of the "core" business model of OBIA : tons of objects with a crazy amount of fact tables and even more dimensions.

                So group into the same business models things which make sense from a business point of view (because they share things and could be analyzed together). And as Thomas said you can than show them as "separated" (if you want, don't have to), using subject areas (1 subject area = 1 business model, 1 business model = 1-n subject areas).

                Capture.PNG

                • 5. Re: Many fact tables in one BusinessModel
                  Thomas Dodds

                  Admin tool draws the picture then hangs (not responding)!  Love it!  Yes, there is a balance to be driven at.  BMM = Business Model and Mapping.  Enterprises can legitimately have multiple Business Models -- the hard thinking needs to be done to arrive at the true picture.

                  • 6. Re: Many fact tables in one BusinessModel
                    Alex Sharkov

                    There is no any good master of obiee around :) When i ask question about analitical using obiee my coworkers.. I get something like this : "hmm i never did this ..but... you can use publisher!" So ofcourse i may hardcode any report in publisher. But i whant business solve problem with analytic. Can you advise about good documentation?

                    • 7. Re: Many fact tables in one BusinessModel
                      Robert Angel

                      Others may disagree but my take on a subject area is; -

                       

                      a. It should relate to a single business area and make sense to group what is there in pure business terms

                      b. It should not be possible for the end user to construct an analysis from the subject area that will not work - i.e. the end should need no specialist knowledge of the data architecture

                       

                      In the case of multiple facts in a single subject area this then implies that all facts should be conformed, or where not conformed the non-conformed dimensions should be set to total level for the fact that they do not conform to.

                       

                      The ability to have multi-subject area analysis via conformed dimensions also extends what can be done without cramming everything into a single subject area.

                       

                       

                      And have to agree with Gianni Ceresa on OBIA, that is truly horrible and I would love to hear the design justification!

                      • 8. Re: Many fact tables in one BusinessModel
                        Christian Berg

                        Robert Angel wrote:

                         

                        And have to agree with Gianni Ceresa on OBIA, that is truly horrible and I would love to hear the design justification!

                         

                        Justification: "We did precisely what was written on that sheet of paper there!" where "sheet of paper" = requirements document. Always the problem when you separate design from execution and the "do-ers" have no idea of what they're doing.

                        • 9. Re: Many fact tables in one BusinessModel
                          Robert Angel

                          Am I alone in wondering how much of an asset OBIA could have been if they had developed it in partnership with a decent OBIEE / EBS partner?

                          • 11. Re: Many fact tables in one BusinessModel
                            Christian Berg

                            It started bad when Siebel had IBM write the first ever ETL in Informatica. That stuff was SO rubbish.

                            • 12. Re: Many fact tables in one BusinessModel
                              Robert Angel

                              My first big job with OBIEE was writing ETL and associated rpd development for e-Business GL, AP and PO.

                               

                              When I first saw OBIA I thought, "I wonder how the professionals did it?"

                               

                              The answer, sadly, was whenever there was a difficult bit they invariably ducked the question, and even though I had been mid-way on the OBIEE learning curve when I tackled it, my solution in comparison was objectively massively better than the solution that is still sold as solving 75% of BI needs for users out of the box (sic).

                               

                              The latest versions do not even include the original templates so unless you have history with it or access to someone who does, they have left you very much in the dark with the configuration.

                               

                               

                              The final solution is just plain bad - it keeps bringing to mind the final stanza of a Yeat's poem (pretentious - moi?!) - "And what rough beast, its hour come round at last, Slouches towards Bethlehem to be born"