My fact table contains a field which is sometimes null and this is not an error. But this field is related to a dimension and so I must use the "Include unmatched members" option. I also use a lookup to insert the surrogate key of this dimension. I get a message during the build: "unable to insert null in table ...".I red that in order to resolve this you must use a dimension template to access the source data but I do not see how. Could someone describe the procedure?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Decision Stream, including unmatched members
- Thread starter Xantix
- Start date
Hi Xantix,
If you really want to do this, you can turn on template access by going to the Dimensions in the Library, select the Lookup you are useing in your fact build, open the property sheet, and select the Data Access tab. There is a checkbox that lets you use template access.
However, I suspect that Include Unmatched Members is not really the solution you are looking for. That will essentially elimnate all checking on your lookup. "Anything that comes through is OK with me!"
From your description, I'm guessing the field that is sometimes null is the "business key" of the dimension is that correct? The reason I suspect that is that the business key cannot be null. Generally, any kind of lookup will fail when it attempts to find a null value business key.
Your problem is similar to a missing date where that is legal. For example, you may always require an OrderDate, but ShippedDate may be null until the order is shipped.
So a lookup on a null ShipDate will fail but you still need to indicate that it is missing and you still want to be able to analyze on that information.
In such cases, you usually want to replace the null value with some known, fixed value like zero or "?". You can do this in your source data by placing a calculated column in the SQL or by using a derivation in a pre-pass fact build (since you can't use derivations as a dimension element in a DS 6.5).
You will also need to include your null-substitution value (e.g. "0"
in your dimension. You can do that by adding a static member at the lowest member of your hierarchy that you use to load the dimension table.
Hope I'm on the right track and that it helps.
Matt
If you really want to do this, you can turn on template access by going to the Dimensions in the Library, select the Lookup you are useing in your fact build, open the property sheet, and select the Data Access tab. There is a checkbox that lets you use template access.
However, I suspect that Include Unmatched Members is not really the solution you are looking for. That will essentially elimnate all checking on your lookup. "Anything that comes through is OK with me!"
From your description, I'm guessing the field that is sometimes null is the "business key" of the dimension is that correct? The reason I suspect that is that the business key cannot be null. Generally, any kind of lookup will fail when it attempts to find a null value business key.
Your problem is similar to a missing date where that is legal. For example, you may always require an OrderDate, but ShippedDate may be null until the order is shipped.
So a lookup on a null ShipDate will fail but you still need to indicate that it is missing and you still want to be able to analyze on that information.
In such cases, you usually want to replace the null value with some known, fixed value like zero or "?". You can do this in your source data by placing a calculated column in the SQL or by using a derivation in a pre-pass fact build (since you can't use derivations as a dimension element in a DS 6.5).
You will also need to include your null-substitution value (e.g. "0"
in your dimension. You can do that by adding a static member at the lowest member of your hierarchy that you use to load the dimension table.Hope I'm on the right track and that it helps.
Matt
- Thread starter
- #3
Thanks, it is exactly my problem and your solution does work.
I wonder why the option "value if null" in the "element properties" of the "table delivery properties" can not replace null values. I must do it in the SQL statement or else my data is rejected.
I wonder why the option "value if null" in the "element properties" of the "table delivery properties" can not replace null values. I must do it in the SQL statement or else my data is rejected.
The "value if null" option on the element properties is only applied on output so you could use it to replace null business keys, but only in a pre-pass build. It happens too late to help in the build where you are checking your lookup.
In DS 7.0 you should be able to create a derivation that is then used as a dimension element, so this step should be easier. (Especially if you, like me, don't know SQL that well.)
Good luck,
Matt
In DS 7.0 you should be able to create a derivation that is then used as a dimension element, so this step should be easier. (Especially if you, like me, don't know SQL that well.)
Good luck,
Matt
- Status
Similar threads
- Locked
- Question
- Locked
- Question