Determinants: Null Date Issue in Stitch Query

[andy_mason_84]

We have a Fact Table which is at Invoice Split level which is the lowest level in the Invoice Hierarchy:
Invoice Number
Invoice Line
Invoice Split

However the Fact table contains certain measures which are at a different grain - Invoice Line level.

Invoice Number	Invoice Line	Invoice Split Number	Invoice Line Count	Invoice Value
A1	0001	0001	1	100
B1	0001	0001	1	50
B1	0001	0002	1	25
C1	0001	0001	1	30
C1	0002	0001	1	10
TOTAL			4	215

            

In the example above
-   Invoice Line Count is at Invoice Line Level
-   Invoice Value is at Split level.


FOR Statements
In order to get the rollups to be correct we have used the following expression in the Framework Model.

Invoice Line Count = max(Invoice Line Count for Invoice Number+Invoice Line Number)

This works however the performance is not very good and the generated SQL that Cognos creates for this is overly complex.


Using Determinants

We now have two copies of the fact query in the DB layer of FWM.

Split Query – no determinants set

Line Query – Which has determinants set at Invoice Line Level

These two fact tables have several conformed dimensions.

Some of these conformed dimensions are dates which can be null.

My issue is that when the query engine stiches the queries together it full outer joins the two queries on all conformed dimension attributes present on the report. When any of these conformed dimension attributes are null, the full outer join equality comparison fails, and the stich query returns 2 rows when only 1 is expected for any piece of data.

Invoice Number	Invoice Line	Invoice Split Number	Invoice Line Count	Invoice Value
A1	0001	0001	1	100

Becomes

Invoice Number	Invoice Line	Invoice Split Number	Entry Date	Invoice Line Count	Invoice Value
A1	0001	0001		1
A1	0001	0001			100

Does anyone have any suggestions/workarounds for handling null data type conformed dimension?

If we set an aggregate to Entry Date in the Report such as Min/Max then it takes it out of the JOIN criteria and works however this isn't really a viable solution more of a fudge.  So any other ideas would be ideal.

[navissar]

Hi,
Determinants are a very weird creature. For the specific scenarios they were designed to solve, they are a delightful solution. For any other case, they add unnecessary pain. Your case is the latter.
Here's a rule that should never be broken: One must never apply determinants to a fact table. I know, that sounds harsh. Never is such a big word. But true nonetheless, one must never apply determinants to a fact table.
If you want a good grasp of why, and how determinants work, you can start by reading here: https://cognote.wordpress.com/2012/04/16/on-understanding-cognos-determinants/.
As for your case, here's the approach I'd try (In as much as possible, I'd try to do this as part of the ETL process, otherwise, using SQL):
1. Create the consolidated fact table: just use a select statement with summaries and group-bys. A very good, useful way to do this is to use materialized/indexed views.
2. Perform the join manually in framework level or better yet, on database level.
3. Create one big consolidated fact table and connect this to your dimensions.

Having said all that, it is really advisable, in a dwh environment, to follow the Kimball method's referential integrity guidelines, and never to allow a foreign key to be null. This can be easily avoided by replacing null values with an RI row of a fabricated key which corresponds with an "N/A" record in dim table. http://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/fact-table-null/

[andy_mason_84]

Hey Nimrod,

Thanks for your response.

So we have taken on board your advice regarding NOT putting determinants on the fact tables however we still face the issue of the null dates.

Kimball's recommendation has indeed been followed as you suggest. The fact record has a foreign key pointing to a "N/A" record in the dimension table. There are no nulls in any of our foreign keys.

In the "N/A" dimension record all text fields are "N/A". However, you can't put the text "N/A" into a database date field - it must be a valid date or null. These date fields are the only place we ever have nulls.

At run-time Cognos joins the two queries (one for each grain) using ONLY the dimension fields the user has included in the report. The user has included the date field (containing a null) and NOT the foreign key (not visible in the presentation layer anyway).

This stops the join from working.

Have you had experience with this before?

[bdbits]

For what it is worth, all of our dates normally join to a date table defined with a surrogate key. The date table has one row with an obviously-out-of-range date, e.g. 01-Jan-1900. This becomes our "unknown" row for dates, eliminating nulls in dates. Because it is so far out-of-range, it is usually easily recognized in a report or query. If aggregating over time, this also keeps the numbers for these "unknown" dates from aggregating in with normal date totals and can be easily excluded if desired.

[navissar]

The set up bdbits offers is pretty standard, and very good.