Data type mismatch?

[cbaqir]

I'm not sure how this is possible but to address an issue of data not displaying correctly, I have created a sample QVW and also stripped down my actual application to two tables - a Fact table and a Dimension table. They both join on a field called REQ_ID. In the Fact table, this field is alphanumeric and is aliased to join to the Dimension table. In the Dimension table, it is also alpha-numeric.

The issue in my real QVW file is that for the REQ_ID values that are alphanumeric (as opposed to integers), some of the other field values are not coming through (REQ_NAME = TBD , for example). There are also several other joins in the actual application between the FACT table and other Dimension tables on a field called REQUEST_ID but this was stripped out to identify the issue.

When I tried to replicate this in my sample, the TBD names came through just fine. I've even tried loading the real fields as text() but it didn't work. I've been at this for days and cannot figure it out.

Here's a data model view of the stripped down real file (APP_ELD = FACT table):

Attached is my sample that I could NOT get to replicate the issue (TBD is what I would want to see):

Note  REQ_ID is displayed as REQUEST_ID

I am completely out of ideas and steam.

Help, please!

[rwunderlich]

When you hover over the REQ_ID field in the Dimension table, what is the subset ratio?

-Rob

[cbaqir]

97%

[rwunderlich]

So what that means is that 3% of the REQ_ID values in your fact table don't have a match in the Dim table. So the trick is to identify which values and why. I think you were already on that track but here's a suggestion to clarify the problem.

Load the REQ_ID and extra time in each table, in the DIm table a REQ_ID_DIM, and the fact as REQ_ID_FACT. Then you can do a combination of Select All and Select Excluded in each of the listboxes to see what doesn't match, and what the associate REQ_ID values look like.

-Rob

[cbaqir]

How does this address the fact that I'm not seeing TBD for the REQ_ID's that start with FCAST?

[rwunderlich]

I'm assuming that you are not seeing REQ_NAME values for those keys because there is no FCAST* value in the Dim table, right? There is a mismatch between the key values. My suggestion was just a method to help identify what the key mismatch is. It doesn't correct the problem, but hopefully identifies where the problem is. Am I misunderstanding?

-Rob

[cbaqir]

The values are in both the DIM and FACT tables.

[cbaqir · Accepted solution]

Turns out that the issue was related to this piece of Load Script Code because my FCAST* rows had a REQ_STATUS of NULL:

where REQ_STATUS not in ('Not Submitted') 

fixed by:

where isnull(REQ_STATUS, 'x') not in ('Not Submitted') 

Thanks for the help, though!