Now that we have some idea of what a dimension is let’s create one in our DemoCube project.
First let’s have a look at all the tables we have as part of our data source view, right click on the data source view pane and selected show all tables.
We have the tables listed below now, for the first example lets create a very simple dimension on the credit card table. A business case for this would be something like this. “We need to know which cards are being used by our customers frequently so that we can give them better offers by partnering with the card issuer for discounts”. In this case it becomes clear that we need to map each sale with a particular card type in order to be able to provide the business with a breakup of which card types were used and in what distribution.
Now we can explore the credit card table
Immediately it is clear that this table has only 4 columns or attributes or properties of the credit card that we are interested in. More specifically there are currently only two columns which we absolutely require in order to answer the previous question. However at this point the developer is left with a dilemma, should he include the ExpMonth and ExpYear to the cube. There are different schools of thought here, I generally suggest that if the dimension table is not huge (which it isn’t in this case) then add the additional column now itself. The reason I say this is because, I see the potential for the cube to be able to answer another key question very soon. “Show me the list of order purchased with credit cards that are about to expire” At this point rather than revisit the cube design it would make sense to leverage the small sized dimension that was already created with these attributes.
Now let’s create the dimension, open the solution explorer within the BIDS IDE and right click and choose new Dimension as shown below.
The below screen will appear , press Next to continue.
The below screen will appear , the screen provides a number of radio button to create a time based dimensions as well as the very first option which is to create a dimension using an existing table. Time or date dimensions are very important in a cube, in fact almost every cube has one since data is usually analysed over a period of time and trends are pulled out from them.
We will spend a lot of time exploring the Date and time dimensions but for now, select the option “Use and existing table” and press Next
In the above screen we will now need to identify the table and the key column from which we need to derive the dimension. In this case we have selected the only Datasource view we have which is AdworksSalesDSV and then selected the CreditCard table within it. In the credit card table we now identify the key column In this case it is the CreditCardid column. The key column helps identify the rows uniquely within the table and allows the developer to implement the required level of granularity within the cube. Choosing the right level of granularity is critical in ensuring the report can answer the question accurately. If my date dimension has a granularity of months I will never be able to accurately display information at the week or day level because SSAS stops aggregating data after the month level.
Press Next
In this screen we now selected the additional columns that we would like to include as part of the dimension. As mentioned earlier we will include the Cardtype , ExpMonth and Expyear as well.
As you can see from the above screenshot we now have the ability to enabled browsing and well as select the attribute Type, in this case the Exp Month and Exp year and of type Date > Calendar > Month and Year, defining the attribute type allows the cube to understand the context in which the value of the column should be understood, If I had chosen Regular for example then the value 2012 is treated just like a normal number with no inherent meaning in the date context.
Press next
Finally we assign a name to the dimension and press Finish.
We will now be able to see the dimension under the solution explorer.
Please Consider Subscribing
