1

Closed

Dimension Health Check Fails with GUID Keys

description

Hi - first post. Great tool! :)
 
For reasons good and bad I have some dimension attributes in SSAS 2005 that use GUIDs from the underlying SQL database.
When running a dimension health check on such a dimension the SQL code executed errors. I've tied the error to the code at the very bottom of the SQL that looks like
 
on coalesce(y.[da93d4d71e754d1f8d58c4d4fe32861f],0) = coalesce(z.[da93d4d71e754d1f8d58c4d4fe32861f],0) order by y.[da93d4d71e754d1f8d58c4d4fe32861f] ,y.[938381f1ab7b406cba48b362214758cd]
 
The y.[da93d4d71e754d1f8d58c4d4fe32861f] and z.[da93d4d71e754d1f8d58c4d4fe32861f] columns are GUIDs, not integers. Thus the coalesce function fails with the error (from SQL Server) "uniqueidentifier is incompatible with int".
 
Changing the code to execute SQL such as
on coalesce(y.[da93d4d71e754d1f8d58c4d4fe32861f], '00000000-0000-0000-0000-000000000000') = coalesce(z.[da93d4d71e754d1f8d58c4d4fe32861f], '00000000-0000-0000-0000-000000000000') order by y.[da93d4d71e754d1f8d58c4d4fe32861f] ,y.[938381f1ab7b406cba48b362214758cd]
would fix the issue.
 
The full error message from BIDS is
Attempt to validate key and name relationship for attribute [Doctor] failed:Operand type clash: uniqueidentifier is incompatible with int 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.[da93d4d71e754d1f8d58c4d4fe32861f] as [DoctorID] ,y.[938381f1ab7b406cba48b362214758cd] as [FormalName] from ( select distinct [DimDoctors].[DoctorID] as [da93d4d71e754d1f8d58c4d4fe32861f] ,[DimDoctors].[FormalName] as [938381f1ab7b406cba48b362214758cd] from ( SELECT DoctorID, FirstName, Surname, Email, Title, IsUnknown, Qualifications, PrescriberNumber, FirstName + ', ' + Surname + ' ' + Title AS FormalName, Title + ' ' + FirstName + ' ' + Surname AS FullName FROM dbo.Doctors WHERE (DoctorType = 0) AND (IsUnknown = 0) UNION ALL SELECT dbo.DoxLib_NullGUID() AS Expr1, 'Unknown' AS Expr2, 'Unknown' AS Expr3, '' AS Expr4, '' AS Expr5, 1 AS Expr6, '' AS Expr7, '' AS Expr8, 'Unknown' AS Expr9, 'Unknown' AS Expr10 ) [DimDoctors] ) y join ( select [da93d4d71e754d1f8d58c4d4fe32861f] from ( select distinct [DimDoctors].[DoctorID] as [da93d4d71e754d1f8d58c4d4fe32861f] ,[DimDoctors].[FormalName] as [938381f1ab7b406cba48b362214758cd] from ( SELECT DoctorID, FirstName, Surname, Email, Title, IsUnknown, Qualifications, PrescriberNumber, FirstName + ', ' + Surname + ' ' + Title AS FormalName, Title + ' ' + FirstName + ' ' + Surname AS FullName FROM dbo.Doctors WHERE (DoctorType = 0) AND (IsUnknown = 0) UNION ALL SELECT dbo.DoxLib_NullGUID() AS Expr1, 'Unknown' AS Expr2, 'Unknown' AS Expr3, '' AS Expr4, '' AS Expr5, 1 AS Expr6, '' AS Expr7, '' AS Expr8, 'Unknown' AS Expr9, 'Unknown' AS Expr10 ) [DimDoctors] ) x group by [da93d4d71e754d1f8d58c4d4fe32861f] having count(*)>1 ) z on coalesce(y.[da93d4d71e754d1f8d58c4d4fe32861f],0) = coalesce(z.[da93d4d71e754d1f8d58c4d4fe32861f],0) order by y.[da93d4d71e754d1f8d58c4d4fe32861f] ,y.[938381f1ab7b406cba48b362214758cd]
Closed Jul 28, 2009 at 3:06 AM by dgosbell
Fixed in release 1.4.2

comments

dgosbell wrote Jun 12, 2009 at 3:54 AM

Thanks for logging this. I can see where this issue is coming from and I appear to have a fix working at least for SQL Server. I will need to do a bit more testing and then I can check in this change. I will close this issue off once I have checked in the change.

If you are interested in helping me test an interim build, contact me via the contact link on my blog at http://geekswithblogs.net/darrengosbell

MrIanYates wrote Jun 12, 2009 at 5:08 AM

Splendid news. I'll get in touch with you via your blog. Thanks for the quick reply! :)

MrIanYates wrote Jul 28, 2009 at 5:50 AM

I'll get the new release and see how it goes - much appreciated!