Fact table with dimension attributes

Robert Angel

Hi,

Not unusually I have a fact table that is not 'purely' fact, i.e. as well as keys and measures it also has attributes.

Not liking attributes in fact folders I created a separate logical dimension table and put the offending fields in there.

Both fact and dimension both use the same physical alias source.

As I had modelled a dimension I created the associated logical dimension, using the prime key of the fact table as the key for the detail level of the dimension, with no other levels than total. I then set the fact / dimension LTS content levels both to the lowest level of detail. I was certain this was the approach I had used before on this kind of fact content.

When I try to save the rpd generates the error =>

nQSError: 43113

However, if I remove the logical dimension then when I include any of the fields from the 'dimension' table in any answers query then OBIEE generates an error saying that some fields must be removed.

My version is 12c, which I am new to, is the way to model fact attributes different now, or am I missing something else?

Any suggest how I should do this within the bounds of best practise, I really hate having attributes in logical fact tables!!

Gianni Ceresa

Hi,

The way to model degenerated dimensions didn't change between 11g and 12c, so it's supposed to work just fine.

As I don't know the nQS error code by heart (and I hope to continue like that ), what is the text coming with "nQSError: 43113"?

If you just try without adding the hierarchy same issues or it works? (not saying you must not create hierarchies, but a test before to add them just to see what happen...)

Robert Angel

Hi,

thanks for taking time, I know this should be easy, it just isn't....

Scenario 1:

Fact table - joins to various dimensions, source 1 physical table (aliased).

Dim Table - based on same source as fact table, logically joined. No hierarchy created, no content level as a consequence can be set.

Result: No error in repository, but errors at runtime with; -

14020 None of the fact tables are compatible with the query request "Dim Table as above"."Field from Dim Table"  (they come from the same physical source!)

Scenario 2:

as above except create dimension hierarchy on Dim table with detail key based on tables prime key.

Result: Missing navigation space

Any pointers, I have double checked mappings / LTS etc and it is very simple and correct.

Christian Berg-0racle

This is something that nornally means your config is incomplete/messed up.

Degen dims with fact based attribs work as long as

- the degen dim has a key

- has a correct lts content

- its fact has the equivalent lts content set for the logical dim

- the alias both the favt and dim are based on isnt used in a role-olaying fashion, so multiple times, as that wozld throw off the navigator

But your case sounds like one of the first ones. Not the last.

Robert Angel

Thanks Christian,

your last comment may be my problem, I had used the SAME alias for the source of both the FACT and its degenerate dimension.

I had it in my head that as they were the same physical source then the logical join between fact and degenerate fact would resolve to; -

select *

from fact

But what it was trying to do was the equivalent of; -

select *

from fact

,       fact

where 1=1

Does this mean that when aliased it resolves to; -

select *

from  fact as fact

,        fact as dimension

where fact.pk = dimension.pk

thanks again,

Robert.

Christian Berg-0racle

Wait wait wait wait

Dont misinterpret me. Logical fact and dim SHOULD share the same phyisical layer object as otherwise you will need two aliases with a physical join which means two physical instances of the same table in the sql combined by a join and that's extremel inefficient

Robert Angel

Ok.

I understand, but I am still left with my problem, and having checked fact / degen fact I don't see anything wrong, and I am puzzled as to why I get the error when I introduce the dimension hierarchy.

I have tried deleting and starting over to double check everything and I still get the same issue.

Any reason not to leave the attributes on the fact in the logical layer but present it as if it is a dimension in the presentation layer, can this cause problems or is it just a best practise that is cosmetic rather than absolute?

Colour me puzzled...

Robert Angel

Also meant to add, I have constructed a query with ALL other fact / dimension combinations and this is all 100% okay, the only point at which my error manifests is when I add the degenerate dimension in.