This discussion is archived
3 Replies Latest reply: Feb 21, 2013 9:34 AM by Robert Angel RSS

Business Model - Logical Table Source

Robert Angel Pro
Currently Being Moderated
Can someone give me clarification on exactly when you would follow scenario 1 when modelling a logical table and exactly when you would follow scenario 2, and what the key difference in behaviour between the two is. It would help if someone could illustrate with sql table joins equivalent.


Scenario 1


You drag a second physical table's column onto the logical table. This causes an additional field to appear in that logical tables list of columns sourced from a different table to the original and causes a second table to appear under the original table in the source folder.


Scenario 2

You drag a second physical table onto the existing logical table source of a logical table. The physical table source appears on the surface the same as before, but when you examine its properties you see that the second table has been joined to it.


Thanks for your input,


Robert.
  • 1. Re: Business Model - Logical Table Source
    Dhar Expert
    Currently Being Moderated
    Hi Robert,

    This is quite an interesting question, I would say :). As per me, here is why

    As you know already, BI Server generates intelligent queries based on our logical mappings in the BMM layer and there is only one primary goal for our BI Server Engine, generating an optimistic physical query.

    Scenario 1 : Two logical sources created.

    Two logical sources mean, there are two different ways the logical table(or same logical column) could be arrived at.

    Ex 1:

    You have a model, where you have Sales numbers and 4 levels of product hierarchy (lowest P4 -> P3-> P2-> P1) with sales at productlevel4.

    And, You have created aggregated tables for sales numbers at level2 of product dimension also.

    So, in this case the same product dimension would have two logical table sources, just because Level 1 Product can be derived from Aggregated product dimension source at level2 or detailed product dimension source at level4. And, it is the BI Server which would determine from where it has to pick (Of course there are some other criteria to control it).

    Ex2:

    Here even sales numbers are available at different granularities viz product level2, product level4. So for sales logical fact, we end up creating two sources just because there are two completely different sources with different granularity.


    Scenario 2 : Two columns created, but only one logical source with a join in it.

    Say, in the above example you have a snowflake product manager dimension to the product dimension. So, when you pull Product Manager into your product dimension (collapsing snowflakes), you end up creating just one logical table source with an inner join of ProductManager dimension and product dimension because product manager could be arrived only by going through products.

    So, if you have report on sales by product manager, you would see query with an inner join of sales fact to product and inner join(this is based on the join type you set in this logical source) of product to product manager.

    Hope I was clear enough and this helps.

    Thank you,
    Dhar
  • 2. Re: Business Model - Logical Table Source
    Srini VEERAVALLI Guru
    Currently Being Moderated
    Scenario 1
    ---> This would be more economical and BI server is free to use its Intelligence to pic the sources based on columns pull in criteria tab provide you let know the sources using content tab.
    In general we go for this is when:
    Dimension extensions
    Fragmentations
    Aggregate table


    Scenario 2
    ---> In this case we are forcing BI server to go as we said (forcing to use joins) and we may not utilising BI's Intelligence
    In general we go for this is when:
    Fact extensions
    From 10g; When metrics are based on some conditions based on dimensions so we might have to add/map them to fact and do
    in Siebel Analytics version we used to go aggregations based on logical columns, the same is no more in 10g and 11g.

    Hope this helps

    Edited by: Srini VEERAVALLI on Feb 21, 2013 9:17 AM
  • 3. Re: Business Model - Logical Table Source
    Robert Angel Pro
    Currently Being Moderated
    Many thanks, I had understood correctly, but I use scenario 2 so infrequently that I had doubts, as a previous developer had used this method in a way there I would not.


    Robert.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points