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

Need to add a more granular table to existing business layer and having problems.

Received Response
11
Views
4
Comments
Jerry S.
Jerry S. Rank 4 - Community Specialist
edited Aug 13, 2024 6:09PM in Oracle Analytics Cloud and Server

We have an established subject area with its own BMM, and I have a requirement to add a new table that is more

granular then the existing fact table.

Here is a simplified scenario based on our Budgeting subject area:

Dimension for Budget category: example: Office supplies

Dimension for Budget Item: Examples: Pencils, Pens, Paper

Fact table: Budget amount, which is summed, Examples: Pencils=$100, Pens=$50, Paper=$200

So, if I analyze Category and Budget Amount, here are the results:

(Report 1)

Office Supplies     $350

If I include the Item, here are the results.

(Report 2)

Office Supplies         Pencils      $100

                                 Pens           $50

                                 Paper        $200

This is currently the highest granularity for the budget subject area, and you cant go more granular than the Budget Item.

We have a requirement to bring in the Expenditure file, which is more granular than the Budget file.

There can be anywhere from 0 to n expenditure records for every budget record

So, for example, lets look at the budget item =Pencils.

The expenditure file has two records: Red Pencils = $20, and black pencils = $80.

The budget file is joined to the expenditure file.

Now when run the same two reports, the amounts are not correct:

(Report 1)

Office Supplies      $450

(Report 2)

Office Supplies      Pencils         $200

                              Pens              $50

                              Paper           $200

It's summing the Budget amount for Pencils ($100) two times, because there are two expenditure records.

Trying to tear apart and rebuild the business layer in order to try and make the Expenditure table the basis for the Fact table

would be a nightmare due to the complexity of the whole thing. Plus, I'm not sure if I can do that because Expenditure

records do not exist for every Budget item.

How can I add in the Expenditure file, while still having my budget amounts sum correctly?

Answers

  • mac2
    mac2 Rank 4 - Community Specialist

    My initial thoughts would be to pursue one of these options:

    1) Combine your Budget and Expenditure fact tables into a single fact table at the physical source layer (in the database) and thus model a single fact table in the RPD.

    2) Model aggregate/detail tables properly in the RPD, where your Expenditure table is the detail table and the Budget table is the aggregate table. If you do this properly, OBIEE will query budget table in your example Report 1 and Report 2 and would query the Expenditure table when you bring in "Color" field.

  • Jerry S.
    Jerry S. Rank 4 - Community Specialist

    I'm not sure how to do option #2, but it looks like an interesting idea so I am going to pursue it.

    Thank you for the suggestion.

  • Gianni Ceresa
    Gianni Ceresa Mod

    Hi,

    #2 from @mac2 sounds good but ... your date make it not really fully usable ...

    Jerry S. wrote:... because Expenditure records do not exist for every Budget item.

    This one will make your life complicated.

    What do I mean? Well ... as soon as you bring in the "Color" column you will lose the data relative to pens and paper because they aren't in the Expenditure file.

    If you want to keep data for pens and paper when using the "Color" column you must fix your data in the source (so #1 from mac2 above).

    If you are fine by losing these data as when you add "Color" you only want to see what is into the Expenditure file and do not care in losing data not there from the resultset then do #2.

    So, are you fine with losing pens and paper or not when looking at data from Expenditure?

    #2 is the normal way to implement an aggregate table, so you model the 3 dimensions "Budget Category", "Budget Item" and "Expenditure attribute", you model the fact table based on Expenditure as main LTS and set the content level. Then you add a second LTS to the logical fact table and you point it to Budget and set the content level to tell OBIEE this one doesn't contain the dimension "Expenditure attribute".

    Doing that the magic mac2 described happen: OBIEE will use the table returning "less data" when possible to give you better performance, so as long as you don't use "Expenditure attribute" OBIEE will hit Budget, as soon as you add in your analysis something related to "Expenditure attribute" OBIEE will hit Expenditure as source (but only Expenditure, that's why you are going to lose any data available only in Budget).

  • asim cholas
    asim cholas Rank 6 - Analytics Lead

    Dear Jerry,

    You have Category, Item , Expense and Budget tables.

    why cant you join like below

    Category to budget only

    category and item to  expense table

    ideally you will have two facts with one non conformed dimension, which you can set total as its level for expense table

    OR

    Make it under one source in LTS and left outer join to expenditure table and in answers take the min for the amount in budget and sum for the amount in expense.

    Thanks

    Asim