Dimension Health Check issue

Topics: Resolved
Jun 28, 2007 at 6:58 PM
Hi,

I installed this recently and was trying out Dimension Health Check , I get following error for all dimensions and for all attributes of a dimension. Can someone point me in the right direction? BTW, this is installed on Win 2003 machine with both AS and SQL on the same box.

"Attempt to validate key and name relationship for attribute Product Category failed:Unexpected query definition for table binding. at BIDSHelper.DimensionHealthCheckPlugin.GetFromClauseForTable(DataTable oTable) at BIDSHelper.DimensionHealthCheckPlugin.GetQueryToValidateUniqueness(DataSourceView dsv, DataItem[] child, DataItem[] parent) at BIDSHelper.DimensionHealthCheckPlugin.GetQueryToValidateKeyUniqueness(DimensionAttribute da) at BIDSHelper.DimensionHealthCheckPlugin.Check(Dimension d)"

Thanks a lot

Sanjay
Coordinator
Jun 28, 2007 at 9:49 PM
Sanjay-

What database are you using? Only SQL2005 is supported for Dimension Health Check at the moment.

That error is one we generated purposefully when we encounter a particular table setup in the DSV which we weren't expecting. I'm including the code snippet below. Basically what is says is that the table in question (whatever table the Product Category attribute is pointing to) isn't what we expected. It needs to match either:

1. Does not have a QueryDefinition attribute, does have a DbTableName attribute, and does have a DbSchemaName attribute.
OR
2. Does have a QueryDefinition attribute.

Otherwise, it throws that error you see. Can you please right click on the DSV and View Code then find the XML snippet for the table in question and see what's up with it? Maybe you could reply back with the XML definition for your table so we could troubleshoot?

Here's the code where you're getting the error.

        private static string GetFromClauseForTable(DataTable oTable)
        {
            if (!oTable.ExtendedProperties.ContainsKey("QueryDefinition") && oTable.ExtendedProperties.ContainsKey("DbTableName") && oTable.ExtendedProperties.ContainsKey("DbSchemaName"))
            {
                return "[" + oTable.ExtendedProperties["DbSchemaName"].ToString() + "].[" + oTable.ExtendedProperties["DbTableName"].ToString() + "] as [" + oTable.ExtendedProperties["FriendlyName"].ToString() + "]";
            }
            else if (oTable.ExtendedProperties.ContainsKey("QueryDefinition"))
            {
                return "(\r\n " + oTable.ExtendedProperties["QueryDefinition"].ToString() + "\r\n) as [" + oTable.ExtendedProperties["FriendlyName"].ToString() + "]\r\n";
            }
            else
            {
                throw new Exception("Unexpected query definition for table binding.");
            }
        }
Jun 28, 2007 at 11:37 PM
Hi,

I don't see any option "view code" when I right click on the DSV. Is it some setting that needs to be turned on to see the xml code? Also , my source is SQL 2005 database. I use tool to gen up the AS database maybe it is not setting some of these attributes correctly. I will take a look.

Thanks,

Sanjay
Jun 28, 2007 at 11:56 PM
Never mind, I was working directly on a AS database from BIDS. Once I created a project from AS database I see the "View code".

Thanks,

Sanjay