Can you advise with it?

Joel

Yes. It’s standard practice to create logical dimensions and hierarchies for every dimension even if you only have a total and detail level.

client and account dimensions should have content level of total in the payment fact.

Alex Sharkov

Ok i did 2 new hierarchies:

H-Client (Total level and just detail with PK attribute)

H-Account  (Total level and just detail  with PK attribute)

Also i have to make Fact table for Client (cause connection with Calendar not working properly)

And total i have 3 fact tables (Client(F), Payment(F),Account(F))

On a LTS Fact table Payments i set Total for Account  . And i try to set Total for Client and Total for Account

Everything is useless

Now logical schema

Robert Angel

If you can give us more detail on your physical joins, keys and content level settings and your exact error message(s) then we can probably help.

Alex Sharkov

That's phisical schema

Robert Angel

Yes it is, but I need more to be able to help you!?

Errors?

Joins?

Logical equivalent of what you posted?

Content levels?

Robert Angel

Btw - I would never expect to see a direct join between 2 fact tables - payment / account above - if your previous diagram is correct?

Alex Sharkov

In logical query i see Payments:

...

CountPay:[DAggr(Payment(F).Count by [ Calendar(Month), Client.ИИН, Client.Активен - 1 мес, Client.Активен - 3 мес, Client.Наименование, Client.ТОО ИП, Client.Тип Clientа] )] as c9 ,

--

But next

CountPay  converted to NULL because [nQSError: 14020] None of the fact tables are compatible with the query request

Alex Sharkov

Btw - I would never expect to see a direct join between 2 fact tables - payment / account above - if your previous diagram is correct?

previous diagram is Phisical !  Not BMM

Sorry Robert i have to translate/and redraw so much for show your.

I cant do it quick

Robert Angel

Ok, you need to provide what I ask for or I am left in the dark.

But in terms of general advice; -

1. Ideally each fact should join to each dimension via a foreign key (fact) to prime key (dimension) join, or sub-optimally via a complex join equivalent

2. Each logical dimension should as minimum have a dimension hierarchy with a key set that as minimum identifies the detail level and as a total level. The key should be the prime key, or sub-optimally a compound key equivalent

3. Each dimension should have its content level, typically this is detail, and the fact should have the corresponding level set for all dimensions it joins to. For non-joined dimensions content level on the fact should be set to total

4. Fact should not directly join to fact

5. time dimension should have time keys set

Logical joins are always Fact -> Dimension or with conformed Fact -> Dimension <- Fact

If you need to snowflake then flatten your snowflake in the business model layer by modelling the outer attributes into the dimension, setting the join appropriately

Keys are crucial, they must be unique at the level of detail they are used for, so Jan would not suffice, but Jan2018 or prime key equivalent would.

Robert Angel

This is worth a look ->

https://www.peakindicators.com/blog/20-golden-rules-for-the-obiee-11g-rpd