I'm new to data warehousing so I may be approaching this the wrong way and if so please let me know a better alternative. The following is an example using the same conceptual relationships but different names.
I have a dimension of faculty and I have a bridge (many to many) connecting those faculty to their specialties. A faculty member can have more than one specialty but they may have none. When I perform MDX queries and pull the specialty and facualty member the results are showing perfectly fine but I can't seem to figure out the best way to find faculty members that have no specialty and combine them together with the ones that do. Here is a quick snapshot of the results of a mdx query I want:
name specialty Salary (fact)
James Biology 300
James Bio-diversity 300
Henry Mathmatics 350
George NULL 100
Louis Linguistics 240
etc...
This is what I'm getting from my current query:
name specialty Salary (fact)
James Biology 300
James Bio-diversity 300
Henry Mathmatics 350
Louis Linguistics 240
If I take out the bridge relationship specialty then George shows up fine. Any help or suggestions?
I would add a member named e. g. "none" to the specialty dimension. Then I would add entries to the bridge table for all faculty members which have no specialty that reference this dimension entry.
To technically implement this in detail, there are several ways:
WHERE NOT EXITSTS
logic.That last two implementations would have the advantage that the ETL process need not be changed.