Fact Table FK Null and Dimension not Returning

Question

Fact Table FK Null and Dimension not Returning

dbernett

SSC Journeyman

Points: 98
More actions
August 31, 2017 at 3:03 pm

#150590
I have a situation in which a dimension is not returning data as wanted. I sort of know why but don’t know the proper fix. Here is the scenario.
- Notice Screen shot. Fact Table with FK (CTP_ID and SS_ID). Dim Tables with PK (CTP_ID and SS_ID)
- Some Clinical Training Programs (CTPs) contain sub specialties and others do not. If a CTP does not contain a sub specialty, there is no corresponding row in Dim_TSS_SubSpecialty of course.
- Notice relationships between 3 tables. This is necessary because the SS_ID can sometimes be NULL in FACT table and I need a Parent Child relationship between Dim_TSS_TrainingProgramClinical and Dim_TSS_SubSpecialty.
- This cube is built from the results of a survey. The Clinical Training Program and Sub specialty are TWO questions.
- The FACT table FK (SS_ID) may be NULL for 2 reasons. There is NO sub specialty for that CTP OR they did not answer that question and dropped from the survey early.
When running this dimension, results will NEVER appear if the SS_ID is null in the Fact Table. Even if there is a valid CTP FK in the Fact table.
Are there any options other than putting fake Sub Specialties for Clinical Training Programs that don’t have Sub specialties in the Dim_TSS_SubSpecialty table? Example Registered Nurse does not have a sub specialty so I would just put “No Sub Specialties for this Program” in the Dim_TSS_SubSpecialty table and on and on. This would NOT fix the issue of them not answering that sub specialty question. And I can’t put unknown(s) in the Dim_TSS_SubSpecialty table because it is a child of the Dim_TSS_TrainingProgramClinical table.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Brian Carlson SSCarpal Tunnel Points: 4170 More actions · Answer 1

Hi David,

I'm not sure I follow all the relationships, but SSAS MD does inner joins on everything even if it's not in the query. That's why the rows with null are not showing. Can you flatten those two dimensions into one dimension? Otherwise, the unknown row or no values row, whatever you want to call it, would be the solution.

-Brian

dbernett SSC Journeyman Points: 98 More actions · Answer 2

Thanks Brian:

I think your statement “SSAS MD does inner joins oneverything” answered my question. That’s exactly the problem. I was hoping there was some way to make that dimension work like an outer join on those 2 tables.

Brian Carlson SSCarpal Tunnel Points: 4170 More actions · Answer 3

If there is a way to do that, I think it would be in coding the measures. Unfortunately, that's beyond my skill level in MD at the moment.

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 4

david.bernett - Thursday, August 31, 2017 3:03 PM
Are there any options other than putting fake Sub Specialties for Clinical Training Programs that donâ€™t have Sub specialties in the Dim_TSS_SubSpecialty table? Example Registered Nurse does not have a sub specialty so I would just put â€œNo Sub Specialties for this Programâ€ in the Dim_TSS_SubSpecialty table and on and on. This would NOT fix the issue of them not answering that sub specialty question. And I canâ€™t put unknown(s) in the Dim_TSS_SubSpecialty table because it is a child of the Dim_TSS_TrainingProgramClinical table.

It's actually a common practice to have rows in dimensions to represent the unknown, not applicable, or no-match cases:
http://www.sqlchick.com/entries/2011/5/16/usage-of-unknown-member-rows-in-a-data-warehouse.html
Is there a reason you were trying to avoid that? You typically don't want a fact record that has a NULL value for a dimension key.

dbernett SSC Journeyman Points: 98 More actions · Answer 5

Thanks Chris:
I did end up putting rows in the Sub Specialties table for each Training Program. For Registered Nurse for Example, I just put Registered Nurse and so on for those who don't have a Sub-Specialty. I read the article link you sent me and this all makes sense.

For those who Answered the Clinical Training Program but did NOT answer the Sub-Specialty (if there was one), I'll probably just delete completely from the Fact table as I don't consider that a valid survey.

Just didn't know if there was a better way.

Thanks again.