Dimension health check SQL fails

Topics: Resolved
Dec 17, 2009 at 12:34 AM

Hello,

Possibly this is not really a BIDSHelper issue. When we do a Dimension Health Check on a time dimension we get the message reproduced below in blue.

The SQL generated is valid, but behaves strangely in SQL Management Studio. If you copy it, then press the "Verify SQL Syntax" button, you get the "could not be bound" errors as in the BIDSHelper message. However if you simply run the query "Execute SQL" it works. Is this a bug in SQL Management studio?

NOTE: If you strip out the redundant alias [CMN_DVL_Calendar_2Y] in the nested SELECT statement, it validates and executes OK.

Les

Attempt to validate key and name relationship for attribute [CMN DVL Calendar 2Y] failed:The multi-part identifier "dbo.CMN_DVL_Calendar_2Y.DayDate" could not be bound. The multi-part identifier "dbo.CMN_DVL_Calendar_2Y.DayDate" could not be bound. The multi-part identifier "dbo.CMN_DVL_Calendar_2Y.DayDate" could not be bound. The multi-part identifier "dbo.CMN_DVL_Calendar_2Y.DayDate" could not be bound. The multi-part identifier "dbo.CMN_DVL_Calendar_2Y.DayDate" could not be bound. The multi-part identifier "dbo.CMN_DVL_Calendar_2Y.DayDate" could not be bound. 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.[564e4ad6e4cf434f901b05cf38eceb56] as [DayDate] ,y.[3f9d68d2f28244eea763ecde46ab021a] as [DayLabel] from ( select distinct [CMN_DVL_Calendar_2Y].[DayDate] as [564e4ad6e4cf434f901b05cf38eceb56] ,CAST(DATEPART(dd, dbo.CMN_DVL_Calendar_2Y.DayDate) AS varchar) + '/' + CAST(DATEPART(mm, dbo.CMN_DVL_Calendar_2Y.DayDate) AS varchar) + '/' + CAST(DATEPART(yyyy, dbo.CMN_DVL_Calendar_2Y.DayDate) as varchar) as [3f9d68d2f28244eea763ecde46ab021a] from [dbo].[CMN_DVL_Calendar_2Y] [CMN_DVL_Calendar_2Y] ) y join ( select [564e4ad6e4cf434f901b05cf38eceb56] from ( select distinct [CMN_DVL_Calendar_2Y].[DayDate] as [564e4ad6e4cf434f901b05cf38eceb56] ,CAST(DATEPART(dd, dbo.CMN_DVL_Calendar_2Y.DayDate) AS varchar) + '/' + CAST(DATEPART(mm, dbo.CMN_DVL_Calendar_2Y.DayDate) AS varchar) + '/' + CAST(DATEPART(yyyy, dbo.CMN_DVL_Calendar_2Y.DayDate) as varchar) as [3f9d68d2f28244eea763ecde46ab021a] from [dbo].[CMN_DVL_Calendar_2Y] [CMN_DVL_Calendar_2Y] ) x group by [564e4ad6e4cf434f901b05cf38eceb56] having count(*)>1 ) z on coalesce(y.[564e4ad6e4cf434f901b05cf38eceb56],'12/30/1899') = coalesce(z.[564e4ad6e4cf434f901b05cf38eceb56],'12/30/1899') order by y.[564e4ad6e4cf434f901b05cf38eceb56] ,y.[3f9d68d2f28244eea763ecde46ab021a]

Coordinator
Dec 17, 2009 at 1:27 PM

Are you using calculated columns or a named query in the DSV? I think that dbo.CMN_DVL_Calendar_2Y.DayDate part it's complaining about is your code, not BIDS Helper. I searched for "yyyy" in the BIDS Helper code and didn't find it, so I'm thinking that it's yours.

Regardless, if it works when you run it, why worry about it?

Dec 17, 2009 at 9:06 PM

Thanks for the response, appreciate your diligence. Yes it is my code and it is in a calculated column.  My concern was just that the SQL generated by BIDSHelper (or maybe BIDSHelper is simply reproducing SQL generated in SSAS - I don't know the details) would not parse in MS SQL Studio.  I am fairly sure that if I embed the calculated column in the dimension table, BIDSHelper would not complain.

I am not worried about my dimension, but I like BIDSHelper and was just trying to help improve it.

Coordinator
Dec 22, 2009 at 4:33 AM

Thanks for your feedback, we really do appreciate it, but in this case I think you will find that if you take the "dbo" prefixes out of your expression in your calculated column, then your parsing errors may go away.

The only way for us to "fix" this sort of issue would be to attempt to reach inside the expression for your calculated column and to try and parse and re-write it. Which is not something that SSAS itself does, and we really need to execute the same sort of statements that SSAS does in order to be consistent with it. I think this is a real edge case where SQL Server can run the code, but not all of the clients can parse/bind it. 

Dec 22, 2009 at 4:42 AM

Darren,

Thanks for your feedback. You are correct – when I removed the “dbo” prefixes it returns no errors (and no problems with the dimension!).