Error:
The error occurred while defining an intermediate relationship between Fact and Dimension.
The error says that it cannot find the attribute key “Area Id”. Now in a referenced relationship a dimension table is not directly connected to the Fact table but it is connected via an intermediate table. So for getting the data with the usage of that referenced dimension table and Fact table we go to our Cube where we have “Dimension Usage” tab where we can define relationships between Fact & Dimension.
Now there are many kinds of relationships like 1 to many, many to 1, many to many, referenced, etc. So here in the below image we see that we have taken referenced relationship and the respected fields like “Reference Dimension”, “Intermediate Dimension Relationship”. Also there are two other fields which defines that through which attribute the reference and intermediate dimension are connected, in this case we want it through “AreaId”. But in the below image we see that for Intermediate dimension attribute it is showing only CountryId.
And due to this reason the error has occurred. This error is faced by every student during MSBI training at Mumbai when intermediate relationship is established in SSAS.
Solution:
The solution to this error is when we create a Dimension for Area and also changed the Dimension Country table in our Data Source View. So now we only have to add the AreaId attribute to our Country Dimension.
This is our Data Source View.
This is our Country Dimension “DimCountry”.
After doing this Save the dimension and then go check the Cube and see whether our dimension has changed or not.
Then go to Dimension Usage tab and define the relationship.
Here as in above image we will select “AreaId” for Intermediate dimension attribute. As soon as we do this, do Ctrl S and then process the cube.
And finally at the end you will see CUBE process status at bottom which is executed successfully.
