Dimension Health Check error

Topics: Resolved
May 15, 2007 at 6:10 PM
When on dimension I select "Dimension Health Check" I am getting message:
Keyword not supported: 'datatypecompatibility'.

Same message on all dimensions.

Anything I can do to help you find where problem is?

This is from latest installation (May 15th, 2007)

Vidas Matelis

May 15, 2007 at 10:16 PM

Thanks so much for trying this stuff out and finding some of the bugs we haven't run across yet.

Can you run a trace on your SQL database and see if it's actually firing off SQL queries which are invalid? Can you run those queries manually from Management Studio and see (a) whether they blow up and (b) whether they return any rows?

Can you also tell us the connection string you're using in your data source in the cube?
May 16, 2007 at 12:08 AM
I run Profiler on SQL Server and on Analysis Services. No activity detected. It happens allmost instantly.
In my data connection to source database I had dataTypeCompatibility set to 80.

My connection string in data source:
Provider=SQLNCLI.1;Data Source=MyServerName;Integrated Security=SSPI;Initial Catalog=MyDBName;DataTypeCompatibility=80

I tried to change DataTypeCompatibility=90, restarted BIDS, same error
I tried to change DataTypeCompatibility=0, restarted BIDS, same error
I tried to remove DataTypeCompatibility=80 from ds file. When I open project, it was back. Same error.

I opened anther 2 projects that had DataTypeCompatibility=0 in it. Dimension Health Check worked for these projects!

So this is definatelly problem with DataTypeCompatibility=? parameter. Problem is that for other projects this parameter is set to 0 and in data source editor it shows that 0 is default value (non default values are in bold font). In first project if I change DataTypeCompatibility to any value (0, 80,90,-1) all these values are shown as non default (bold font) and they are used in connection string.

I'll keep investigating as I know where to look. I'll post here results.
If you will find anything, please let me know.

May 16, 2007 at 4:26 AM

We've got this issue fixed, I believe. We just checked in an update which uses an internal BIDS method for connecting to the database, and your connection string appears to work now.

We'll get this fix in the next release. For the time being, you'll have to compile the DLL yourself.

Thanks again for reporting this issue.
May 22, 2007 at 3:06 PM
With todays release I tested Dimension Health check and it works now.
I did found few data problems in my dimensions, so this tool is very helpful. It is very nice that you actually show rows causing problem. Very convenient.

Also, I have one parent-child dimension that is connected to itself using composite key (2 fields). Health check on this dimension produced following error:


Attempt to validate key and name relationship for attribute Employe Hierarchy failed:Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints. at System.Data.DataTable.EnableConstraints() at System.Data.DataTable.set_EnforceConstraints(Boolean value) at System.Data.DataTable.EndLoadData() at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue) at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) at System.Data.Common.LoadAdapter.FillFromReader(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) at System.Data.DataTable.Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler errorHandler) at System.Data.DataTable.Load(IDataReader reader) 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.aa85a365-c261-4073-b19a-b64ff3197801 as ManagerKey ,y.bb1d3c05-d0b4-45e9-b47e-07fd915b055c as ManagerHierarchyVersionNo ,y.fb1242fc-1922-4f90-8693-79467b3b81f1 as EmployeID from ( select distinct aa85a365-c261-4073-b19a-b64ff3197801 = vwEmployeHierarchy.ManagerKey ,bb1d3c05-d0b4-45e9-b47e-07fd915b055c = vwEmployeHierarchy.ManagerHierarchyVersionNo ,fb1242fc-1922-4f90-8693-79467b3b81f1 = vwEmployeHierarchy.EmployeID from dbo.vwEmployeHierarchy as vwEmployeHierarchy ) as y join ( select aa85a365-c261-4073-b19a-b64ff3197801 ,bb1d3c05-d0b4-45e9-b47e-07fd915b055c from ( select distinct aa85a365-c261-4073-b19a-b64ff3197801 = vwEmployeHierarchy.ManagerKey ,bb1d3c05-d0b4-45e9-b47e-07fd915b055c = vwEmployeHierarchy.ManagerHierarchyVersionNo ,fb1242fc-1922-4f90-8693-79467b3b81f1 = vwEmployeHierarchy.EmployeID from dbo.vwEmployeHierarchy as vwEmployeHierarchy ) x group by aa85a365-c261-4073-b19a-b64ff3197801 ,bb1d3c05-d0b4-45e9-b47e-07fd915b055c having count(*)>1 ) z on coalesce(y.aa85a365-c261-4073-b19a-b64ff3197801,0) = coalesce(z.aa85a365-c261-4073-b19a-b64ff3197801,0) and coalesce(y.bb1d3c05-d0b4-45e9-b47e-07fd915b055c,0) = coalesce(z.bb1d3c05-d0b4-45e9-b47e-07fd915b055c,0)

There could be very much that there is problem with data in that dimension as I have fresh data load last night. But question first - should this health checker work with parent/child dimensions?

May 23, 2007 at 5:29 AM

Can you copy that select query it mentions into Management Studio and troubleshoot it? Does it succeed? Does it return any rows?

I'll look into what's supported regarding parent/child dimensions.
May 23, 2007 at 3:58 PM

Yes query works no problem. It returns values like:
ManagerKey ManagerVersionNo EmployeeID


I am guessing from the message that it does not like the fact that Employee does not have manager. But that is normal for parent child top level.

May 25, 2007 at 4:37 AM

Upon further reflection, I changed the code to prevent it from validating the key/name pair on any Usage=Parent attributes as it appears to ignore the NameColumn setting. That should fix your problem.

More troubling is why you were getting the error as it retrieved the SQL rows. If you refresh your DSV, does it see any changes? What I'm guessing is that some of the metadata in your DSV is out of data thus making the "constraints" it is talking about overly strict? For instance, maybe it didn't think the ManagerKey column was nullable because the DSV says it's not nullable. (You get the idea.) Anyway, please let me know if you see any ugly stack trace error messages again in Dimension Health Check.

Thanks for taking the time to report the problem.
May 25, 2007 at 12:45 PM

Todays release fix that problem and now dimension is reported as healthy.

To be honest, it is almost impossible that dimension metadata in DSV was not in sync with database - I am 99.999% sure it was correct. So my guess that something else was involved.
This dimension is based on view. I have seen multiple cases when Data Source View reported incorrect primary keys for views, but setup for that dimension is correct.

Anyway, problem is fixed now. Thank you again for this utility.