In the last post we had finished creating our measure and measure group and in the post before that we created a dimension. However our cube is still not ready to answer the question posed by the Business. In order to show the count of order against each card type we now need to establish a relationship between the dimension and the measure we created earlier.
This is done by navigating to the Dimension Usage Tab on the cube designer. In this case SSAS automatically identifies the relationship between the two tables on account of the primary key and foreign key relationship between the two tables. As shown below
On clicking the button next to Credit Card ID (which is the key column of the dimension and the column within the measure on which the data is being joined) we see the type of relationship that has been established.
In simple terms the above screen explains that the CreditcardId Column from the dimension table is mapped to the CreditcardId column in the measure Group table in a regular relationship. This is the most common type of relationship and a well-designed cube will usually have a majority of the relationships in this type. There are other types of relationships which we will explore later.
If you click the advanced button on the above screen you will see something like this.
This screen summarizes the information about what dimension columns are participating in this relationship. The most important option in this screen is the use of the Null Processing drop down. Frequently in an OLAP system there will be a number of data quality issues that need to be addressed, these could be because of human error, machine error or just a delay in getting the data synced. In such cases there is always a possibility that a measure may contain a value which doesn’t exist in the dimension. Normally in a traditional database this would result in a Primary Key foreign key violation and would not be possible. However when it comes to data warehousing we need to still retain this information in order to make sure the results are accurate. We can do this by setting the Null processing option, usually we would default to Unknown since the value is not defined (the traditional meaning of NULL). Else we have other options such as Preserve in which case the value is stored as it is but won’t map the dimension. Error means we get errors similar to the database. Zero or Blank means the value gets default to 0 or Blank value and Automatic is the default options as specified by the user. Let us leave it at Unknown member and press OK.
Please Consider Subscribing