Dimension Health Check Error - Bug or Sick Dimension?

Topics: Resolved
Dec 1, 2008 at 9:12 PM
I think this is a great feature. I initially thought I had quite a few sick dimensions, but then realized that it may or may not be a bug in the software.I think the Health Check is improperly reporting failed attributes based on the way I've written my Named Calculation.The Named Calculation: Display Name with Title and SuffixExpression:CASEWHEN HotelBKCustomerID IS NULL THEN LTRIM(RTRIM(Title + ' ' + DisplayName + ' ' + Suffix))ELSE DisplayNameENDResults during Health Check:Attempt to validate key and name relationship for attribute [Customer] failed:Invalid column name 'HotelBKCustomerID'. Invalid column name 'HotelBKCustomerID'. at System.Data.OleDb.OleDbDataReader.ProcessResults(OleDbHResult hr) at System.Data.OleDb.OleDbDataReader.NextResult() at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.OleDb.OleDbCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.DataWarehouse.Design.DataSourceConnection.Fill(DataSet dataSet, String sql, CommandBehavior commandBehavior) at Microsoft.DataWarehouse.Design.DataSourceConnection.Fill(DataSet dataSet, String sql) at BIDSHelper.DimensionHealthCheckPlugin.Check(Dimension d) SQL query was: select y.[388e3cda8e0f4fe989fccc92af5cb6ff] as [CustomerKey] ,y.[a8db0a6d93c4430e8945996eb43eb137] as [Display Name with Title and Suffix] from ( select distinct [DimCustomer].[CustomerKey] as [388e3cda8e0f4fe989fccc92af5cb6ff] ,CASE WHEN HotelBKCustomerID IS NULL THEN LTRIM(RTRIM(Title + ' ' + DisplayName + ' ' + Suffix)) ELSE DisplayName END as [a8db0a6d93c4430e8945996eb43eb137] from [dbo].[DimCustomer] [DimCustomer] ) y join ( select [388e3cda8e0f4fe989fccc92af5cb6ff] from ( select distinct [DimCustomer].[CustomerKey] as [388e3cda8e0f4fe989fccc92af5cb6ff] ,CASE WHEN HotelBKCustomerID IS NULL THEN LTRIM(RTRIM(Title + ' ' + DisplayName + ' ' + Suffix)) ELSE DisplayName END as [a8db0a6d93c4430e8945996eb43eb137] from [dbo].[DimCustomer] [DimCustomer] ) x group by [388e3cda8e0f4fe989fccc92af5cb6ff] having count(*)>1 ) z on coalesce(y.[388e3cda8e0f4fe989fccc92af5cb6ff],0) = coalesce(z.[388e3cda8e0f4fe989fccc92af5cb6ff],0) order by y.[388e3cda8e0f4fe989fccc92af5cb6ff] ,y.[a8db0a6d93c4430e8945996eb43eb137]

Basically it's the same thing for all of the problem attributes: Attempt to validate attribute relationship [Customer] -> [Changed To CustomerKey] failed:Invalid column name 'ChangedToCustomerKey'. Invalid column name'ChangedToCustomerKey'. atSystem.Data.OleDb.OleDbDataReader.ProcessResults(OleDbHResult hr) 
where just the Column Name changes for several attributes.  The one thing they have in common is that when I run the query that BIDS Helper makes, I don't get any data back.
Here's the query for ChangedToCustomerKey:
select y.[fbbf1b62270049db9475e034800077b4] as [CustomerKey] 
,y.[b9563c3e84414f0e8fcc583b0dd51041] as [ChangedToCustomerKey] 
from ( select distinct [DimCustomer].[CustomerKey] as [fbbf1b62270049db9475e034800077b4] 
,[DimCustomer].[ChangedToCustomerKey] as [b9563c3e84414f0e8fcc583b0dd51041] 
from [dbo].[DimCustomer] [DimCustomer] 
) y 
join ( select [fbbf1b62270049db9475e034800077b4] 
from ( select distinct [DimCustomer].[CustomerKey] as [fbbf1b62270049db9475e034800077b4] 
,[DimCustomer].[ChangedToCustomerKey] as [b9563c3e84414f0e8fcc583b0dd51041] 
from [dbo].[DimCustomer] [DimCustomer] 
) x 
group by [fbbf1b62270049db9475e034800077b4] 
having count(*)>1 ) z 
on coalesce(y.[fbbf1b62270049db9475e034800077b4],0) = coalesce(z.[fbbf1b62270049db9475e034800077b4],0) 
order by y.[fbbf1b62270049db9475e034800077b4] ,y.[b9563c3e84414f0e8fcc583b0dd51041] 
Is it that I don't understand attribute relationships, and the error message isn't verbose enough, or something else?

Thanks for your replies,
Rick
Coordinator
Dec 1, 2008 at 9:27 PM
Thanks for reporting this. What happens when you run that SQL query it mentions? Where are the missing columns defined? In the SQL database, in a named query, or in a calculated column?
Dec 1, 2008 at 9:38 PM
Of course, as soon as I submit the post, I figure out what it is:
All of the attributes that have errors are for columns that come from tables that aren't in the "primary" database in the .dsv.  So if the .dsv is made to connect to AdventureWorksDW, then you add a new .ds for AdventureWorksDW2, and add tables from AdventureWorksDW2 to the original .dsv, all of the attributes will appear as broken.
Is this a mistake on my part, or is it a bug in the software?

I still think the software is great; I found some tweaks I can make with some of my attributes in the 20 minutes I've spent that wasn't spent tracking down this bug.  Keep up the great work!  Also, feel free to let me know if you want me to test any fix you might come up with for this, assuming it's a bug, and not a glaring mistake in my DSV design!

Cheers,
Rick
Dec 1, 2008 at 9:41 PM
Whoops, I was posting my reply at the same time you were.  If I run the query in the correct DB, no problems, I just don't get any rows back.  If I run the query in the DB that is the "primary" DB in the DSV, I get the errors reported by the Dimension Health Check: Invalid Column Name (with the appropriate column name here).
Coordinator
Dec 1, 2008 at 9:53 PM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.
Coordinator
Dec 1, 2008 at 9:54 PM
Thanks for the heads up. I understand your issue now. We'll look into it.