Tuesday, 19 June 2012

SSAS, Property not accessible because 'Parent Columns and Child Columns don't have type-matching columns'

Hi friends, if you are on this page then I can imagine how much you are frustrated with this silly error and more frustrated because cannot find the cause. And even google cannot help you much.  

I am sharing my experience on this hope this will be helpful to somebody.

I got this error when i refreshed the Data Source View (DSV) in SQL Server Analysis Service (SSAS) project open in visual studio. I was really clueless what was it talking about, which realtion have been changed? Even the change report generated was no showing any problem. 

Stucked!! right? Even I was :) !! After spending couple of days I decided to review changed done in last week. After reviewing I got the hint of the problem.

I was having 'Employee' table with 'EmployeeID' column of type INTEGER (primary key), which was linked to their 4 tables as foreign key. And one of developer has changed the datatype of foreign keys to VARCHAR. 

This should have broken the relationship with other tables but it did not and error was all about that. In DSV I deleted all the faulty relationship manually and refreshed the DSV. Strange enough the report was showing the relationship as CHANGED and not DELETED and then resulting in same error.

I reloaded the project, since it was not allowing me to save. Deleted the relations again, but this time I did one extra step. I refreshed  the all the other 4 tables as well. 

Refreshing the DSV tables. 
1. My table was not a query, DSV was directly referring to table.

2. I replaced the tables with named query

3. Saved the DSV and the again bought it to normal and saved. 
4. Repeated this for all the related table.
5. The problem was solved.

I could have deleted the tables and re-added but that will delete all other relations as well and I have to manually do everything again.

I Hope VS could have handled this mess !! 

Happy Coding!!!
-Ravi Kumar V.

No comments:

Post a Comment