Dimension Health Check - Datasources

Jul 3, 2007 at 1:13 PM
Hi, and thanks for your work in this great tool - I'd been using the aggregation manager alone, but having it built into VS along with the other tools is very useful. I have run into a problem, and thought I should let you know for future releases. I'm trying to use the dimension health check on my AS DB, and it errors. The cube has two SQL 2005 datasources, and brings table data in from two SQL DBs on different clustered servers. The dimensions based on the tables from the primary datasource check out OK (Or not, depending on my design ;)) but the dimensions based on the secondary datasource error when I try & health-check them. The error report is as follows:

Attempt to validate attribute relationship Player -> Post Code failed:Invalid object name 'dbo.Player'. 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.a57d090e-1b0f-4dc2-bcde-a01ecaddd866 as PlayerId ,y.82e00ffd-ff2e-44b6-bbb7-4abeea17e1dd as PostCode from ( select distinct a57d090e-1b0f-4dc2-bcde-a01ecaddd866 = Player.PlayerId ,82e00ffd-ff2e-44b6-bbb7-4abeea17e1dd = Player.PostCode from dbo.Player as Player ) as y join ( select a57d090e-1b0f-4dc2-bcde-a01ecaddd866 from ( select distinct a57d090e-1b0f-4dc2-bcde-a01ecaddd866 = Player.PlayerId ,82e00ffd-ff2e-44b6-bbb7-4abeea17e1dd = Player.PostCode from dbo.Player as Player ) x group by a57d090e-1b0f-4dc2-bcde-a01ecaddd866 having count(*)>1 ) z on coalesce(y.a57d090e-1b0f-4dc2-bcde-a01ecaddd866,0) = coalesce(z.a57d090e-1b0f-4dc2-bcde-a01ecaddd866,0)

Note that this is repeated for each attribute in the dimension. The query generated above runs fine in SQL Management Studio, and gives expected results, allowing me to check the dimension manually at least :) A similar AS DB that only uses a single datasource, and includes the Player table, works fine.

Thanks for the tools, and best wishes with your development - Leo
Coordinator
Jul 4, 2007 at 6:12 AM
Leo, thanks for reporting this issue. As you discovered, multiple data sources aren't supported by our Dimension Health Check currently. We'll look into supporting that in a future release.