Find missing members in hierarchy

#1

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
#2

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.

3 Likes
#3

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