Hi,

You can only have correct numbers with multi-fact analysis if ALL dimensions that are used in your analysis are; -

1. Connected directly to all facts

2. Not cross connected to other tables

3. Have appropriate content level settings fact to dimension (both sides) with data existing at the level of granularity set (say detail as you have no pre-aggs as you have said)

Note, sum / count with dimensions will not come out correctly typically as OBIEE uses a distinct on dimensions before any aggregation, if you are using it like a measure it needs to be in your fact (Note looking at your screenshot I am not wholly clear which is fact and which is dimension)

Hi Alex,

I could help you more if you could confirm if your model adheres to my 1,2,3 above - but...

example of what would work, note showing 3 facts but could as easily be 53.

Physical; -

Account_F - fields; Calendar_FK, Account_Id, Account_Value

Payment_F - fields; Calendar_FK, SomeOtherDimension_FK, Payment_Id, Payment_Value

Calendar_D - fields, Calendar_PK, Date, Month, Month_Key, Quarter, Quarter_Key, Year, Year_Key

Physical Joins; -

Payment_F -> Calendar_D

Account_F -> Calendar_D

Business Model; -

logical joins to mirror the above

Account Fact ; fields; No of Accounts - count distinct Account_Id, Account Value: Sum Account_Value

Payment Fact: fields; No of Payments - count distinct Payment_Id, No of Payment Lines - count Payment_Detail_Id, Payment Value: Sum Payment_Value

Time: fields; Date, Month, Quarter, Year   - time hierarchy; detail - date, month - month_key (display month), quarter - quarter_key (display quarter) etc...

Set Content Level: Account Fact - Time to detail, Payment Fact - Time to detail, Time - to detail

Okay, so here pull in any field from Time and any of the measures and you get the correct answers.

This is because all dimensions are conformed to all facts, so far so good.

However, model in a new dimension table (lets call it 'Payment Detail')

Physical

Payment Detail - fields - payment_detail_id, Payment Accounting Detail etc etc

Joins

Payment -> Payment Detail

Business Model

logical joins to reflect the above

Dimension Hierarchy; Total Level, Detail Level: Key - payment_detail_id Display Payment Accounting Detail

Content Level on Payment_Fact to Detail, Content Level on Payment Detail to Detail

Okay, so now, any analysis with payment fact will work with payment detail and time will work fine, the numbers will be all correct.

But if you bring in the account fact measure(s) then they will not appear as OBIEE has no way to get from Payment Detail to Account - there is no join or content at that level of detail

So either (drastic step) you need to remodel your account fact to include the payment_detail_id (in practise this may not even be possible if the two tables are just not related) OR (trivial step) you can set the content level of the account fact to Total for the payment_detail

Hope this makes sense Alex -  if you are still struggling to relate it to your example then show me the content levels you currently have set, and the dimension hierarchies with keys displayed

OBIEE is star-schema-centric.

If your data sources are not star schemas then typically you are into (at least to some extent) modelling 3NF territory, which is more complex and invariably less performant.

If you are modelling 3NF then typically you start by making facts out of the most detailed level of table and joining into that from there.

Your business model in 3NF modelling will not remotely resemble your physical layer, it will look like a star schema. It will be labelled in business-centric terms. It will be separated into subject areas that represent logical divisions of the business. With conformed dimensions it will permit multiple subject area / multiple fact based reporting.