Can you advise with it?

Robert Angel

Sorry, think this is a communication issue - I was asking what error message you see when you run the analysis, or does it not error, just not give you the correct / expected number - if the latter how is it incorrect?

thanks,

Robert.

Alex Sharkov

I create analyse with using

Client and Account

it's calculated fine and give me correct result

When i add Payment measure - it's null

When i add formula with like:  count distinct payments by client.id it work

so i show it on pic

Alex Sharkov

So first step target :

For each client i need calc count account , and count payment. in the same analiz

Robert Angel

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)

Alex Sharkov

Ok

Is any way to solve this task?

Without publisher!

No any action from business with: formula, filter definition and so on?

Could you show me project OBIEE with  multifact tables ?

Robert Angel

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

Alex Sharkov

Ok i will go on step by step.

Did youn mean i should to divide(artificaly) my datamart's on phisical layer?

Robert Angel

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.

Robert Angel

Looking back at your original diagram; -

I would expect provided you have your content level set properly and the right granularity table to table that any analysis with calendar, account (F), payment (F), payment (D) should come out with the right results.

If you introduce Account (D) or Client (D) into the previous analysis then you will get no results, or static results, depending on how you have set the content level.

As OBIEE has no way of getting the right numbers per row as no link exists.

Ways around this could be to use aggregate views to generate the numbers you need and outer join them into your fact table (assuming you have a mechanism to join meaningfully via sql).

Alex Sharkov

So i recreate phisical level diagram

Is'it good for obiee ?