This week I’ll take a look at how to customize a dimension in an SSAS cube. Several weeks ago, I mentioned my preference for creating my dimensions first, prior to creating the cube in SSAS. However, is not your only option as I mentioned at that time. You could create your cube directly after defining the data source view you want to use. If you remember, I mentioned that while the cube wizard will identify the potential dimensions and add them to the cube, it only adds the key values from each dimension, ignoring all the other attributes. I may have mentioned that you could go back later and ‘fix’ that omission. So today, I’ll show you how.
I’m still using the ContosoRetailDW database. Suppose I had the following cube design in which I created the Contoso Retail DW cube using the wizard and let the wizard create my dimensions. When I open any of the dimensions in the design surface, you can see that the only attribute included in the dimension is the key value. On the other hand, you can see in the Data Source View pane that this table has many other potential attributes that a user may want to use in the cube.
Before adding the other attributes, let’s make a few changes to the properties of Datekey. First, let’s change the name of this attribute to simply Date. You can do this by right clicking on the attribute Datekey and selecting Rename from the popup menu. Then using your mouse to position the cursor edit the name by selecting and deleting the characters ‘key’.
When you use this dimension in your cube, if you were to add Date (previously Datekey) you would see a value that included not only the date, but also a time value. It may be better to display the date in another format. There are several options from the table including FullDateLabel and DateDescription. You can tell the cube to display one of these fields in place of the attribute Date when displayed in the cube by going to the properties pane and selecting the NameColumn property as shown in the following figure. Click in the far right side of the value portion of this property row to open the dialog box shown. Then simply select the column you want to display in place of Date when the user add the Date attribute to either the cube’s rows or columns.
Next, to display addition attributes in the cube, select the fields from the list of fields in the table DimDate as shown in the Data Source View panel. You do not have to select all of the fields, only the ones you think the user may want to include in the cube or which you may want to use when building a hierarchy (I will cover this next time). Note, it is generally considered to be poor practice to include the column you selected in the previous figure as an additional attribute. Effectively, that would make the same field available using two different names which would probably be confusing.
After selecting the fields you want, simply drag them with the mouse to the Attributes panel so they appear as shown in the following figure.
After making these changes, you should save your solution before processing it. You can do this either from the Save All option in the File dropdown in the main 
Before you can display your cube and use the new date attributes, you must redeploy the cube. You can do this either through the Build menu or you can right click on the project in the Solution Explorer panel and select Deploy
When the deploy completes successfully, you may be anxious to immediately click on the Browser tab to see you changes. However, when you attempt to open the Dim Date dimension, you will see instead an error message as shown in the following figure. This error message occurs because while you deployed a new cube definition (which has been saved in SSAS with the same name as the project). You need to update the data used by the browser.
An easy way to ‘fix’ this problem to get the updated data is to click the Reconnect button in the tool bar within the Browser Tab. (Click the Browser Tab to display its custom toolbar.)
Now when you expand the DimDate dimension, you see all the new attributes recently added. You can then define your cube using any of these attributes such as the Date attribute itself which now displays the FullDateLabel rather than the DateKey value as shown in the following figure.
However, notice that the Sales Amount values are not formatted as currency. I mentioned a way to format the measure data in the cube previously, but here is a better way. Click back on the Cube Structure tab (after opening the Cube definition again if you do not have open). Expand the FactSales section to display all the measures in this measure group (table) as shown in the following figure. While you could select each field individually, a faster way to format multiple fields with the same format string is to first display the measures in a grid rather than a list by clicking the Show Measures Grid as shown below.
Then click on Unit Cost as the first of the attributes that should be formatted as currency. When while holding the CTRL key down, click on the other attributes that you want to formatted as currency such as: Unit Price, Return Amount, Discount Amount, Total Cost, and Sales Amount. Then go to the Properties pane and locate the FormatString property and click the dropdown menu button on the right side of the field value. This action displays a list of possible pre-defined formats that you can define. For example, we can simple select ‘Currency’ or we could select one of the other currency type formats such as ‘$#,##0.00;-$#,##0.00’ or you can create your own format such as ‘$#,##0;-$#,##0’ which shows the values with no cents.
In a similar fashion, select the measures Sales Quantity, Return Quantity, Discount Quantity, and Fact Sales Count and then select FormatString in the Properties pane and set the value to ‘#,#;-#,#’ which essentially tells the cube to display these values as integers.
Of course you will need to Deploy your cube again (right-click the project name in the Solution Explorer and select Deploy). After your deploy completes successfully, click the cube’s Browser tab. This time, your cube will still look like it did before without any of the formatting changes you just set. However, at the bottom of the design area, you should see the information box shown below which tells you that the cube has been updated and that you must reconnect to the data.
You can either click the Reconnect button as I mentioned earlier or you can simply click the hyperlink ‘Reconnect’. After a few seconds, the cube updates the values displayed to show dollar values like Sales Amount as currency and count values like Sales Quantity as integers as shown in the following figure.
That’s all for this week. However, one other piece of unabashed self-promotion that I would like to mention is that I have been selected as a speaker at this year’s SQL PASS conference in Redmond. If any of you will be attending, stop by to say hello.
C’ya next time.
SharePointMike's Blog 