Canonical Time set up for a fact. error OBIEE 12c

Khalid Ibrahim

I have a fact table with more than one time column (say Date ordered and Date shipped)

After reading about canonical time, I created one date dimension and two aliases to the fact table say (Fact Date Ordered and Fact Date shipped). Joined them to the date dimension in the physical layer and used them as the logical sources to the one Logical Fact table (say Sales Fact) in the BMM Layer.

When I try to use the second date (the first date works fine) in  my report I get an error. I did set the content level to the same for the date dimension in both logical table sources.

The errors are:

nQSError: 43119  Query Failed

NQSError14025: No fact table exists at the requested level of detail.

NQs Error 14091, you may be able to evaluate this query if you remove one of the column references.

What am I doing wrong?

This is in OBIEE 12.2.1.2 (non cloud) on Linux.

Thomas Dodds

"when I try to use the second date" ... the whole idea of canonical time is to use a generic date ...

Example:

User picks 3/17/2017 and thus

Ship Count is count of rows from Fact where the join was fact.shipdate = calendar.date

Order Count is count of rows from Fact where the join was fact.orderdate = calendar.date

Make sure you haven't dual-sourced Count (or any other measure) from both LTS ... need a separate 'version' of count from each LTS.

Khalid Ibrahim

Thanks Thomas,

My error seems to be occurring when I want to display the dates in my report.

i.e. I get the errors listed above when I select shipped date. If I do not select shipped date my report works fine.

Thomas Dodds

Indicating that your model isn't set up correctly ...

perhaps posting a few images of your model and LTS setup would allow us to help you out a bit more.

Khalid Ibrahim

Here you go

Thomas Dodds

"Indicating that your model isn't set up correctly ..."  as I suspected...

What is your fact table?  Which is your dimension?

Fact table holds relationships between dimensions (keys) and additive elements ONLY ...

From the picture you have dimensional attributes in your fact table ... so your Logical diagram has a date dimension and a malformed fact.

You asked about canonical time -- your image shows no evidence of aliased physical tables indicating the approach to canonical time.  Such an approach would look like the following where the same physical table is aliased for each perspective desired:

You need at least an Item dimension as well (and perhaps others) ... your model should look like for each perspective:

Khalid Ibrahim

The Item Master Update Date Fact  is an alias of Item master Fact and joins to the  Item Master Date Dim table on the Item Master Update Date column Both the Item master fact tables are aliases of an actual physical table.
I just reduced this part to these three tables, to test canonical time before implementing it. I will have my Item Dimension in there (once I get canonical time worked out)

Thomas Dodds

ok ... you had me concerned there for a second ... still you need to take the attributes out of the fact - simplify it so you can get it working.

So now ...

1. make a count(item id) measure from the ITEM MASTER FACT and call it Item Count - set the aggr level appropriately
2. make a count(item id) measure from the ITEM MASTER UPDATED FACT and call it Item Updated Count - set the aggr level appropriately

You now should be able to select a date (1/1/2017) and see the count from BOTH/EACH LTS.

Khalid Ibrahim

Thomas,

You know, you may actually be helping me solve this issue but not in the manner you are expecting. :-)

I think what I am saying next may be showing where I am messing up.

For my column mapping for the Item Master Update table.. I have  mapped only the column Item Update date (the remaining columns are mapped in the  Item master fact table). Should I map all the columns?

Thanks

Thomas Dodds

yes - to get the perspective you will need at least:

ITEM NUMBER - business key mapped to BOTH LTS

ITEM COUNT - mapped to one LTS

ITEM UPDATED COUNT - mapped to second LTS

Both LTS set at same aggregation content level

Khalid Ibrahim

Thanks Thomas, it is finally working. One more final step. I had to explicitly declare that FAct Item Master Update date also mapped to the Item Master Fact (with an inner join) for the Item Master Create date.