This discussion is archived
12 Replies Latest reply: Aug 1, 2013 10:07 PM by 987994 RSS

Modelling 2 Fact Tables with Non-Conforming Dimension in OBIEE 11g

649804 Newbie
Currently Being Moderated
Hi all,

I have two fact tables (Fact 1 and Fact 2) and two dimension tables (Product and Rule). The Product dimension table is a conforming dimension and is used in both fact tables, but the Rule dimension is a non-conforming dimension which is used only one fact table. I'm using OBIEE 11g (11.1.1.6.0).


====
Fact 1
====
Sales ID | Product ID | Quantity | Sales Description | Sales Status
-----------------------------------------------------------------------------------------
S001 | P001 | 100 | bla bla bla bla bla | N
S001 | P002 | 200 | bla bla bla bla bla | N
S002 | P001 | 200 | lab lab lab lab lab | Y
S002 | P003 | 250 | lab lab lab lab lab | Y

Notes for Fact 1:
- One Sales ID can have multiple Product IDs
- Sales Description and Sales Status are the same for one Sales ID (repeating Sales Description and Sales Status for the same Sales ID)


====
Fact 2
====
Sales ID | Product ID | Rule ID | Score
-----------------------------------------------------
S001 | P001 | R001 | 2
S001 | P001 | R002 | 3
S001 | P002 | R003 | 1
S002 | P001 | R003 | 1
S002 | P003 | R002 | 2
S002 | P003 | R004 | 5

Notes for Fact 2:
- One combination of Sales ID and Product ID can have multiple Rule ID


I'm wondering how best to model these tables so that I can create this report (number of the dimension and fact tables created in the business model, level mapping, aggregation rule, etc)? Any suggestion/advice on how to achieve this?

Sales ID | Product ID | Quantity | Sales Description | Sales Status | Rule ID | Score
------------------------------------------------------------------------------------------------------------------
S001 | P001 | 100 | bla bla bla bla bla | N | R001 | 2
S001 | P001 | 100 | bla bla bla bla bla | N | R002 | 3
S001 | P002 | 200 | bla bla bla bla bla | N | R003 | 1
S002 | P001 | 200 | lab lab lab lab lab | Y | R003 | 1
S002 | P003 | 250 | lab lab lab lab lab | Y | R002 | 2
S002 | P003 | 250 | lab lab lab lab lab | Y | R004 | 5

Thank you very much!
  • 1. Re: Modelling 2 Fact Tables with Non-Conforming Dimension in OBIEE 11g
    Srini VEERAVALLI Guru
    Currently Being Moderated
    In Physical layer joins are like
    Fact 2->Product and Rule
    Fact 1 ->Product

    Considering you are using a subject area with these 2 fact tables:
    In BMM layer Create Logical Fact table with 2 logical sources Fact1 and Fact 2.

    Create hierarchies for Product and Rule and set
    Fact 2->Product and Rule
    Fact 1 ->Product

    Based on the columns in pull Answers, BI server decide the best source

    Hope this helps, pls mark if it does
  • 2. Re: Modelling 2 Fact Tables with Non-Conforming Dimension in OBIEE 11g
    MK Pro
    Currently Being Moderated
    How is rule is related to Quantity? I see a cartisian join and Quantity value will be wrong. Now we need to know the relation

    For one combination of Sales ID and Product ID I have 2 rules in Fact 2 but I have only one row with Quantity in Fact1. Now What would be the Quantity value in your final report against Rule1 and Rule2.

    If quantity is not used along with others in report you can modle it in a normal way.
  • 3. Re: Modelling 2 Fact Tables with Non-Conforming Dimension in OBIEE 11g
    649804 Newbie
    Currently Being Moderated
    Hi MK, the quantity of a Permit & Prodict combination should be repeated as many times as their satisfied rules, when the rules information is shown (that is when combining the 2 fact tables in one report).

    When the rules information is not shown (that is if no rule information is retrieved, therefore only retrieving from Fact 1), the quantity should be summed in normal way, grouped by Permit ID and Product ID in the Fact 1.

    Appreciate any suggestion. Thanks!
  • 4. Re: Modelling 2 Fact Tables with Non-Conforming Dimension in OBIEE 11g
    649804 Newbie
    Currently Being Moderated
    Hi all, any suggestion regarding this? Thanks! :)
  • 5. Re: Modelling 2 Fact Tables with Non-Conforming Dimension in OBIEE 11g
    Dhar Expert
    Currently Being Moderated
    Hi,

    I see these facts as

    Fact 1
    --------
    Grain : Sales (x) Product (x)

    Fact 2
    --------
    Grain : Sales (x) Product (x) Rule

    As per the above understanding, they are two different facts with two different grains ( I am inclined to think that the first fact is kind of aggregated for fact2 where all rules are rolled up to sales*product).

    Anyway, since they are different facts the following steps might help you

    1. Create physical relationships as is, with Product as the conforming dimension.

    2. Create two different logical sources in your logical Fact with content level set appropriately for each source (One source for fact1, another for fact2).

    3. Make sure the column mappings are appropriate ex: RuleID has to be picked up only for logical source 2 (one pointing to fact with grain Sales (x) Product (x) * Rule )

    4. Now, create your needed report.

    5. What BI Server does now is, since there are two different facts with different grains and different sources, it will fire individual queries to each fact and does a full outer join of these queries based on ProductID since this is the conforming dimension.

    6. Ultimately, you will get all the Sales ID, their Product ID and the corresponding RoleID and the other measures with repetetions.

    Hope I was clear and this helps.

    Thank you,
    Dhar
  • 6. Re: Modelling 2 Fact Tables with Non-Conforming Dimension in OBIEE 11g
    649804 Newbie
    Currently Being Moderated
    Hi Dhar, thanks for the suggestions.

    I tested what you suggested, but the result is not as per my expectation mentioned above. Here's what I did:
    1. In physical layer:
    - I joined Fact 1 table with Product dimension table only
    - I joined Fact 2 table with Product and Rule dimension tables
    2. In business model layer:
    - I created 3 logical tables: Fact, Product, and Rule
    - The Product table contains the Product ID and Product Name from the Product dimension table in the physical layer
    - I created the hierarchy (logical dimension) for Product with only ProductTotal level (as the grand total level) and ProductDetail level that contains Product ID and Product Name
    - The Rule table contains the Rule ID and Rule Name from the Rule dimension table in the physical layer
    - I created the hierarchy (logical dimension) for Rule with only RuleTotal level (as the grand total level) and RuleDetail level that contains Rule ID and Rule Name
    - The Fact table contains 2 logical tables sources: Fact 1 (which logical level in the Content tab is mapped to ProductDetail and RuleTotal) and Fact 2 (which logical level in the Content tab is mapped to ProductDetail and RuleDetail)
    - The Fact table contains Sales ID logical column (mapped to both Fact 1 and Fact 2 logical table sources)
    - The Fact table also contains Sales Description and Sales Status logical columns (mapped to only Fact 1), which aggregation rule is the default to None
    - The Fact table also contains Quantity logical column (mapped to only Fact 1), which aggregation rule is set to Sum
    - The Fact table also contains Score logical column (mapped to only Fact 2), which aggregation rule is set to Sum

    OBIEE returns the expected result when I retrieve the report:
    Sales ID | Product ID | Quantity | Sales Description | Sales Status

    However, OBIEE returns an error when I retrieve the reports:
    Sales ID | Product ID | Quantity | Sales Description | Sales Status | Rule ID
    or
    Sales ID | Product ID | Quantity | Sales Description | Sales Status | Rule ID | Score

    The error is:
    Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
    State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 43119] Query Failed: [nQSError: 14020] None of the fact tables are compatible with the query request FACT.SALES_STATUS. (HY000)

    And the Score column is blank when I retrieved this report:
    Sales ID | Product ID | Quantity | Sales Description | Sales Status | Score

    Any suggestion anyone? Please help. Thanks a lot!

    Edited by: stewartlife on Nov 29, 2012 4:01 PM
  • 7. Re: Modelling 2 Fact Tables with Non-Conforming Dimension in OBIEE 11g
    Dhar Expert
    Currently Being Moderated
    Hi,

    I am sorry, as I misinterpreted Sales to be a separate dimension looking at your dummy data. However, this seems to be a degenerated dimension to me, so do you think you could pluck it out of the fact and make it another dimension in your BMM?

    Also, as per your last update I do not think "Sales_Status" and "Sales Description" could be mapped to fact2.

    Hope this helps.

    -Dhar
  • 8. Re: Modelling 2 Fact Tables with Non-Conforming Dimension in OBIEE 11g
    649804 Newbie
    Currently Being Moderated
    Sorry Dhar, my mistake. I've edited my last post about the Sales Description and Sales Status mapping to only Fact 1.

    I don't think it's possible to make the Fact 1 as a dimension table. It seems very strange to me to map it that way.

    I can't remember whether what I want here is possible in 10g, but I'm really wondering whether or not this kind of modelling is possible in OBIEE 11g.

    Any suggestion is really appreciated. Thanks!
  • 9. Re: Modelling 2 Fact Tables with Non-Conforming Dimension in OBIEE 11g
    Dhar Expert
    Currently Being Moderated
    Hi,

    Yes, its certainly possible and practised design. You could check an example by Rittman at http://www.rittmanmead.com/2010/01/oracle-bi-ee-10-1-3-4-1-modeling-degenerate-dimensions-fact-attributes/

    Hope this helps.

    Thank you,
    Dhar
  • 10. Re: Modelling 2 Fact Tables with Non-Conforming Dimension in OBIEE 11g
    961405 Newbie
    Currently Being Moderated
    Stewartlife -

    Put in the 'Totals' at the measure too along with the table. My testing shows that the 'Totals' that is put on the fact for non-conformed dimensions does not matter. The one that is on the measure matters.

    In your scenario, you would have 2 non-conforming dimension, one for each fact.

    My issues are:
    1. if there is an OR clause in the filter criteria from the 2 non-conforming dimensions, the report breaks, it gives error
    2. if in the display attribute from both non-conforming dimensions are pulled, it gives report error

    I have raised this with oracle but have not heard from them. Maybe this is a bug.

    Anyone has faced this?
  • 11. Re: Modelling 2 Fact Tables with Non-Conforming Dimension in OBIEE 11g
    927621 Newbie
    Currently Being Moderated
    I have faced both issues you discussed:
    1. if there is an OR clause in the filter criteria from the 2 non-conforming dimensions, the report breaks, it gives error
    2. if in the display attribute from both non-conforming dimensions are pulled, it gives report error

    Have you heard any more from oracle support or found your own solution for this?

    Thanks
    Adam
  • 12. Re: Modelling 2 Fact Tables with Non-Conforming Dimension in OBIEE 11g
    987994 Newbie
    Currently Being Moderated

    Hi Adam,

     

    Were you able to fix the issue?

    Which version of Obiee solves the above issue other than 10g?

     

     

    Regards

    NN

Legend

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