Find missing members in hierarchy

i have a hierarchy called Product. it is connected to a datacube and working well. However i miss a few members. is there an easy way to find out which members in the facttable are not in the dim-table? in a table the missing members are grouped under unknow in the product hierarchy but i can find which specific members i am missing in the dim.

Can you give me a tip?

1 Like

I’ve had to do the same before, and found the simplest solution from within DBI** is to have a reference table (facttable in your case) and:

  1. Create a new cube that pulls in both the dim-table and facttable
  2. Join them on the variable that you’re looking at (Product in your case)
  3. Change it to a full outer join so that it shows null where there’s no match
  4. Display the null cases however you want (in a dash or whatnot)

Hope that helps!

** = I think it’s easier at the SQL level, but often you can’t back out to the sql server to do this because it’s already in DBI cubes and such.


Thx for the tip Ken.

The problem was the case sensitive join in Dundas and the non case sensitive join in SQL. I have implemented a check report in SQL with SELECT … FROM xxx LEFT OUTER JOIN xxx ON Fact.criterium = Dim.Criterium COLLATE SQL_Latin1_General_Cp1_CS_AS

With the collate I found the members that had a difference in the upper and lowercase. Then I implemented an UPPER() around the keys on facttable level and dimtable level.

1 Like