Start preparing for your Next Exam | Use coupon – TOGETHER | Avail 30% discount

Overview of Dimension Granularity

  1. Home
  3. Overview of Dimension Granularity

Since users will require to dimension fact data at different dimension granularity or specificity for different purposes. Moreover, in these scenarios, users will require a time dimension with a different grain or level of detail for each of these different fact tables. Also, you could define a new database dimension as a time dimension with this different grain, there is an easier way with Analysis Services. In general, in analysis services, when a dimension is used within a measure group, the grain of the data within that dimension is based on the key attribute of the dimension.

In order to specify a grain for a cube dimension other than the default grain, follow the below steps

  • Firstly, you must modify the granularity attribute for a cube dimension as used within a particular measure group on the Dimension Usage tab of Cube Designer.
  • Secondly, when you change the grain of a dimension within a specific measure group to an attribute other than the key attribute for that dimension, then you must guarantee that all other attributes in the measure group are directly or indirectly related to new granularity attribute.
  • In the next step, you must specify attribute relationships between all other attributes and the attribute that is specified as the granularity attribute in the measure group.
  • Now, you should define additional attribute relationships rather than move attribute relationships.
  • Further, the attribute that is specified as the granularity attribute effectively becomes the key attribute within the measure group for the remaining attributes in the dimension.

How to add tables and defining the Sales Quotas Measure Group?

  • Firstly, switch to the Adventure Works DW 2012 data source view.
  • Then, right-click anywhere in the Diagram Organizer pane, click New Diagram, and then name the diagram Sales Quotas.
  • Thirdly, grag the Employee, Sales Territory, and Date tables from the Tables pane to the Diagram pane.
  • Then, add the FactSalesQuota table to the Diagram pane by right-clicking anywhere in the Diagram pane
  • Next, select Add/Remove Tables. Notice that the SalesTerritory table is linked to the FactSalesQuota table through the Employee table.
  • Review the columns in the FactSalesQuota table and then explore the data in this table.Notice that the grain of the data within this table is the calendar quarter, which is the lowest level of detail in the FactSalesQuota table.
  • Now, in Data Source View Designer, change the FriendlyName property of the FactSalesQuota table to SalesQuotas.
  • Further, switch to the Analysis Services Tutorial cube, and then click the Cube Structure tab.
  • Then, right-click anywhere in the Measures pane, click New Measure Group, click SalesQuotas in the New Measure Group dialog box, and then click OK.The Sales Quotas measure group appears in the Measures pane.
  • Also, expand the Sales Quotas measure group.
  • Here, in the Measures pane, select Sales Amount Quota, and then set the value for the FormatString property to Currency in the Properties window.
  • Select the Sales Quotas Count measure, and then type #,# as the value for the FormatString property in the Properties window.
  • Delete the Calendar Quarter measure from the Sales Quotas measure group. Analysis Services detected the column that underlies the Calendar Quarter measure as a column that contains measures. However, this column and the CalendarYear column contain the values that you will use to link the Sales Quotas measure group to the Date dimension later in this topic.
  • In the Measures pane, right-click the Sales Quotas measure group, and then click New Measure. The New Measure dialog box opens, containing the available source columns for a measure with a usage type of Sum.
  • In the New Measure dialog box, select Distinct count in the Usage list, verify that SalesQuotas is selected in the Source table list, select EmployeeKey in the Source column list, and then click OK. Notice that the measure is created in a new measure group named Sales Quotas 1. Distinct count measures in SQL Server are created in their own measure groups to maximize processing performance.
  • Lastly, change the value for the Name property for the Employee Key Distinct Count measure to Sales Person Count, and then type #,# as the value for the FormatString property.
Microsoft Exam DA-100 Free Practice Test
Menu