Fact tables with this non-calculation index will show unexpected result

Question

Hi Experts,

I have two fact tables and they are sharing the same dimensions, but one fact table contains one non-calculation index(such as "ChangeDate"),which is not used aggregate function(such as "Sum").

When I create one report for using both of fact tables without this non-calculation index, and it will show correctly.

However,  I create one report for using both of fact tables with this non-calculation index for displaying , and it will show unexpected result.( one of the facts does not return values.)

What happen and how to solve this problem?

PS:

Since this non-calculation index should be changed on Month(not fixed data),so I add it into the fact table.

For example(Maybe the sample is not very good):

F1->Month/MenuCode/PricePerPage/ChangeDate

F1->Month/MenuCode/Quantity/

D1>Year/Month

D2->MenuCode/etc.(and so on)

Gianni Ceresa · Answer

It's not important what it is or why it is, but a column without aggregation rule is not supposed to be into a (logical) fact table.

That's why it becomes a degenerated dimension.

So you add a new logical table to your business model, you add the columns of your logical fact without aggregation (so using the same physical source) and model it as a normal dimension in OBIEE.

When done you end up having 2 fact table, some conformed dimensions shared by the 2 fact tables and 1 non conformed dimension, the one you just created, your degenerated dimension.

For this one you must set the content level of the fact without links to it depending on how you want it to work (that's a normal non conformed dimension model).

In case the concept of content level is a bit unfamiliar have a look at this: https://greatobi.wordpress.com/2013/09/10/the-single-most-important-thing-to-know-about-the-obi-rpd/

User472204-OC · Answer

Hi Gianni Ceresa,

Because this one non-calculation index must be changed based on date,you can consider it as the slowing changed dimension.

So if it is the requirment, please help me to how to design the model or architecture. Thanks.

Gianni Ceresa · Answer

Hi,

You model it as a degenerated dimensions and then you set the content level to tell OBIEE how to manage it with the 2nd fact not having it as a dimension (the standard way of configuring conformed and not conformed dimensions in a model).

Your fact tables must contain only aggregated columns.