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

Question

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?

asim cholas · Answer

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

Gianni Ceresa · Answer

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).

Jerry S. · Answer

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.