Level based Hierarchy - Page 2 — Oracle Analytics

Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Level based Hierarchy

Received Response
92
Views
16
Comments
2»

Answers

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

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

  • ark86
    ark86 Rank 3 - Community Apprentice

    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)

    pastedImage_0.png

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

    pastedImage_1.png

    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

    pastedImage_2.png

    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

    pastedImage_3.png

    Then i set the content level of both facts

    For F1

    pastedImage_4.png

    For F2:

    pastedImage_5.png

    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

    pastedImage_6.png

    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

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    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.

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    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?!

  • ark86
    ark86 Rank 3 - Community Apprentice

    Hi, Thank you for your reply.

    Now i made a perfect star by combining both facts

    pastedImage_0.png

    now both facts are connected to both dimensions logically.

    pastedImage_1.png

    pastedImage_2.png

    pastedImage_3.png

    But still F2 amounts disappear when D1 attribute is added.

    Thanks.

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    If you combine Logical Table Sources rather than leaving it as separate logical facts then you must set the content level for fact columns as well.