{[['
']]}
']]}
Dimensions Manipulate
At the time of cube development in the SSAS, there might be many times when you would need to make changes to the dimensions and measures. For example, you might need to change the display format of a measure or change the aggregation function of it. For dimensions, you might also need to add/remove an attribute or change the relationship between the attributes. In this section, we will add some attributes to the Sales Territory dimension and also make changes in the display format of the measures.
The Dimension Usage tab is a specific designer for dimensions in the SSDT.
Using the Dimension Designer
- In the Solution Explorer, right under dimensions, double-click on Sales Territory.
- A new designer window will be opened; this designer is called a Dimension Designer.
- In the Dimension Structure tab, drag-and-drop Sales Territory Region from the Data Source View pane to the Attributes pane.
- Do this for Sales Territory Group and Sales Territory Country as well.
- Select Sales Territory Key in the Attributes pane, and then in the Properties window, change the AttributeHierarchyVisible property of this attribute to false.
- Save the changes and process the dimension. You can process the dimension with the Process button, which is the second button from the top left-hand side of the dimension structure tab.
- Go to the Browser tab in the Dimension Designer and then reconnect to a process (you can see how to reconnect to a process in the next screenshot). Select Sales Territory Country in the hierarchy drop-down list and you will then see the dimension member values listed in the browser:
Date Manipulate
- Double-click on the Date dimension in Solution Explorer.
- In the Dimension designer, go to the Browser tab; you can see all the members of the Date Key attribute there. Members of the Date Key attribute are integer values with the YYYYMMDD format. This format is not well formed from the business user's perspective. In this example, we will change this format and also add two other attributes for the year and month.
- Go to the Dimension Structure tab, select Date Key in the Attributes pane, then in the Properties window, click on the ellipsis button of the NameColumn property. In the Name Column dialog box, select FullDateAlternateKey and click on OK.
- Then rename the Date Key attribute to Date.
- Drag-and-drop CalendarYear from the Data Source View pane to the Attributes pane.
- Drag-and-drop MonthNumberOfYear from the Data Source View pane to the Attributes pane.
- As the Month Number of Year view only shows values such as 1, 2,… 12, we need to change it to show the month names. So, change the NameColumn property of this attribute to EnglishMonthName. Also, rename this attribute to Month.
- Process the dimension and go to the Browser tab; if you choose Date in the hierarchy drop-down list, you will see values such as 2005-01-01, which is what we want.
- If you choose Month in the hierarchy drop-down list, you will see that the month names are visible, but they are not in the correct order.
- Go back to the Dimension Structure tab. Select the Month attribute, and then in the Properties window, change the OrderBy property to Key.
- Process the dimension and go to Browser, and you will see that the month's values are shown in the correct order this time.
Excel Browser
- In SSDT, go to the Cube designer, and in the Cube structure tab, select the Sales Amount measure. Change the FormatString property of this measure to Currency.
- Select the Order Quantity measure and change FormatString of this measure to #,##0.00;-#,##0.00
- Process the Solution then go to Browser, there is an option to view the cube in Excel, and you need to click on Analyze in the Excel icon in the browser.
- In the Excel browser, choose Sales Amount and Order Quantity, and from Order Date, choose Calendar Year as shown in the following screenshot:
Post a Comment