What happens when RDBMS structure and SSAS DB structure are not same?

Not all training session are easy with simple error. There are times when typical issue are seen, here we will discuss one such issue recently seen in Mumbai MSBI premise training while doing SSAS lab.

Issue:

The error says that the duplicate attribute key has been found in Country & Product table. This error has encountered because the Country table and Product table does not have any primary keys in RDBMS structure while in SSAS project we have designed our database where we have given primary key to both Country & Product Table.

Here we do not see the primary key in the RDBMS.

Solution:

For solving this error first of all we will delete the Country table and Product table from our Data Source view which we have created.

After deleting the tables this is how our Data Source View will look like,

Now go to Country table in our RDBMS and write this query which will give the desired output without any duplicate attribute.

Now copy the query and do the following steps,

Paste the query and the table will be created. In the same way do for Product table,

Then set the primary key to both the table and map the relation,

After that we will delete the Cube & dimensions and recreate it again (not advised in the production mode).

Now process this Cube and then do the modifications in all the dimensions by inserting the Name field as we want the analysis to be done by using names and not Id’s.

After doing changes in our Dimensions, again process the Cube and the error are solved.