In SQL Server Analysis Services, Dimensions are the group of attributes (nothing but columns) based on the dimension tables in the data Source View. All these attributes are shown as attribute Hierarchies and Analysis services provides us an option to create User Defined Hierarchies as well.
In SSAS dimensions are of two types such as Database Dimension and Cube Dimension. Please refer Difference between Database Dimension and Cube Dimension in SSAS article to know the Difference between Database Dimension and Cube Dimension in SQL Server Analysis Services.
Creating Dimensions in SSAS Example
Within the Solution Explorer, Right-click the Dimensions folder and select New Dimension from the Context Menu to create new one.
By clicking on the New Dimension option will open the Dimension Wizard.
First page of the Dimension wizard is a Welcome page. If you don’t want to see this page again then Don’t show this page again option as shown in the below screenshot and click on the next
Next page is Select Creation Method and this page gives 4 options to create the Dimension.
- Use an Existing Table: It will use the existing tables present in the Data Source View.
- Generate a Time Table in the Data Source: This option will generate the time-table and saves it in the data source.
- Generate a Time Table on the Server: This option will generate the time-table and saves the table directly in the Server.
- Generate a Non-Time Table in the Data Source: This option will generate the normal tables and saves it in the data source. It has the template to select whether you want to create customer, Employee, Department, Geography etc.
In general, we use the first option only (Use an existing table) because it will use the required table from the Data Source View. However, if we don’t know how to create particular table then use the templates provided here to design it.
Click on the Next button will open the Specify Source Information page to configure the Data Source View, Table and Key columns.
For this Example, Select the Data Source View we created earlier in the SSAS Data Source View article. We intend to create a dimension from the DimCustomer table, so select the same table from the Main table drop down list. Every dimension table needs to have a key attribute and CustomerKey is the primary key column for this table.
Click on the next button will take the Dimension Wizard to Select Related Tables page. Here it will display the dependent tables (tables that are connected with Dim Customers using foreign Key relationship). For now we are selecting them.
Click on the Next Button will open the Select Dimension Attributes page. In this page we have 3 sections to understand.
- Available Attributes: This section will display all the available columns (attributes) of the Dim Customer table present in the data Source View. We can select all the columns or else we can select required columns. It is always advisable to select the required columns.
- Enable Browsing: If you check mark this option then that column will be available for browsing in cube. If you uncheck this option then they won’t be visible to client applications when they browse the dimension. Sometimes we may need id column for reference purpose but not required to slice the data then you can uncheck them from browsing.
- Attribute Types: SQL Server Analysis Services provides many Attribute Types while creating Dimensions. Select the appropriate one and if you don’t know which attribute type to choose then select regular type. For example, For Calendar year column select the calendar year attribute type.
For the time being we selected few columns from the Dim Customer and also we haven’t changed the Attribute Types from regular to appropriate one.
NOTE: It is always a good practice to select the specific attribute type rather than the regular type. Especially for the time and account dimensions we must specify attribute type because it will help when working with the MDX.
Now the next step is to give specific or anything appropriate name to the dimension. In this example we are naming it as Dim Customer.
Click on the finish button to finish creating Dimension in SSAS.
From the below screenshot you can observe that,
- In solution explorer we have Dim Customer dimension under the Dimensions folder.
- Attributes pane have only the selected attributes (Columns). These are the ones we selected while creating Dimension using Dimension Wizard.
- Hierarchies pane is empty because we haven’t created any User Defined Hierarchies yet.
- Data Source View will show all the available columns in Dim Customer table.
Well we successfully created our First Dimension in SQL Server Analysis Services
Thank you for Visiting Our Blog