Develop an Azure Synapse Analytics Data Warehouse with Dimodelo Data Warehouse Studio

Previous Lesson
Next Topic

Create a Task Dimension

Develop an Azure Synapse Analytics Data Warehouse with Dimodelo Data Warehouse Studio Developing Dimensions Create a Task Dimension

Create a Task Dimension

  1. Right-click the Data Warehouse>Data Warehouse>Dimensions folder (or one of its subfolders) and select Create New.

Dimension Tab

  1. In the Dimension Name field enter “Task“. The Dimension Name is the name of your table in your data warehouse relational database. Spaces in the name will be converted to _ automatically when you save. The prefix for the Dimension table name can be set in the config file.
  2. In the Dimension Friendly Name field enter “Task”. This is the friendly name the user sees in the Solution Explorer. In the future, if Dimodelo Data Warehouse Studio was to generate a cube, it would be the name of the Dimension in the Cube. Spaces are not replaced in this name.
  3. Enter “This is the Task Dimension” in the Dimension Description. Optional. A description of the contents of the dimension.
  4. Leave Reference blank. This is a free form text you can use to reference the dimension design back to another design document, perhaps a business requirements document.

Dimension Attributes Tab

You can add, delete and modify dimension attributes through the dimension’s Attribute tab.

Import

Use the Import Schema button on the Dimension Attributes tab to import the metadata for the Dimension from a Source System table.

To Import the metadata.

  1. On the Dimension Attributes tab, click the Import Schema button.
  2. In the Source field select the Service Source System.
  3. In the ‘Import Table‘ field select the “dbo.Task” table

The dimension attributes will be populated with the column names and data types from the source table. If the source table has primary key columns, the columns are imported as a “Business Key” column type. Otherwise, they are imported as an Attribute. It may be necessary to select your own business keys.

Some connection types don’t return column data types (e.g. File sources). In this case, the data types default to varchar(50).

Delete a Dimension Attribute

  1. Highlight the “ScheduledDate” Attribute by clicking the icon at the start of the row.

  1. Click the Delete attribute(s) button.
  2. Click Delete to confirm.

Add an Attribute

  1. Click in the empty row at the end of the list.
  2. In the Attribute Name enter “Category“. Enter the attribute name. Spaces are replaced with _ when the dimension is saved. The Name must be unique within the dimension. Dimodelo Data Warehouse Studio will validate the name. If the name specified violates SQL Server naming restrictions, a red ! will be displayed on the row.
  3. In the Data Type field enter varchar(50). Select a data type from the dropdown list, or just type the data type. The data type will be validated to ensure it is a valid SQL Server data type when the data type field loses focus. If the data type is invalid, a dialog box will display, and the field cleared.
  4. Select Attribute in the Column Type field. If the attribute is part of the Natural/Business key of the dimension, then select ‘Business Key’. The business key can be a composite key. That is, more than one attribute can have a type of Business Key. Normally the business key is what your ETL process will use to look up the dimension to identify if source rows already exist in the dimension.
  5. Select “Type 1 SCD” in the SCD Type field. SCD Type is an acronym for Slowly Changing Dimension Type. The list of types corresponds to the SCD types set out by Ralph Kimball. When the Attribute type is ‘Attribute’ this field is enabled and is required. Select the appropriate SCD type for the attribute. The SCD type selected will affect how the DDL is generated for the attribute (using the supplied templates).  If you don’t understand SCD types then look at our Kimball data warehouse course ‘Kimball Data Warehouse course’.
  6. Enter “Task Category” in the Description field. Add a description of the attribute. Generally, the description should make sense to an end-user. Descriptions are used in the generated Data Dictionary.

Further Information

  • The parent indicator is used to indicate that the attribute is the natural key of the corresponding parent row in a parent-child hierarchy. That is, each row keeps its parent identifier in this attribute column. Parent identifiers can be composite keys, so more than 1 attribute can be marked as a Parent. The presence of a Parent indicator on any attribute will cause a Parent Surrogate Key to be generated in the DDL using the supplied DDL templates. Only a single Parent surrogate key is produced regardless of how many attributes are marked as Parent. Parent logic is added to the ETL Transformation if Parent is ticked.
  • Reference field. This is a free form text you can use to reference the attribute back to another design document, or perhaps a report the attribute appears on.

Change an Attribute

  1. Click on the Attribute Name field with the value “TaskEstimateMinutes”.
  2. Change the value to TaskMinutes.
  3. Click Save (Disk icon) in the Visual Studio tool menu to save the Dimension. Notice the Visual Studio tab label changes to Task.dt.

Further Information

Advanced Tab

  • Custom Surrogate Key Name. This field allows you to specify a custom name for the surrogate key of this dimension. This is useful for in a conforming dimension scenario. See section Error! Reference source not found. for more details. The standard DDL generation templates use the indicator and name to generate the correct surrogate key column name in the DDL.
  • Custom Surrogate Data Type. This field allows you to specify a custom data type for the surrogate key of this dimension. This is useful for in a conforming dimension scenario. See section Conformed Dimensions section for more details. The standard DDL generation templates use the indicator and data type to generate the correct surrogate key column data type in the DDL.
  • Surrogate Key is Auto Generated. Un-tick this option if the ETL manages surrogate keys, instead of the database auto-generating surrogate keys. The standard DDL templates use the indicator to set IDENTITY on dimension keys. This should be used in conjunction with the Auto Generate Key toggle on the advanced tab of the ETL dialog.
  • Generate DDL. Un-tick this option if you don’t want Dimodelo Data Warehouse Studio to generate the DDL for this table.
  • Conformed. Metadata indicating the Dimension is a conformed Dimension from another Warehouse. This can be used in the Generation step to generate Conforming code.  Currently, this property is not used by the standard ETL generation templates.
  • Dimension Tags. Custom MetaData for the Dimension. Tag the dimension with a tag Name and Tag Value. Some Dimension patterns require custom metadata. See the Patterns guide for more information about the custom metadata required by each pattern.

Custom Management Columns

What are the Management Columns? Management columns are columns that are added to your Fact and Dimension tables to help manage the data, during ETL. Management Columns include things like:

  • Batch Identifier.
  • Row Status (Current – Not Current).
  • Row Effective Dates.
  • Checksum (if you use checksums in your ETL)

Dimodelo Data Warehouse Studio allows you to specify Custom management columns on an individual Fact/Dimension basis. A set of Standard Management Columns is specified for all Fact/Dimension tables. See section Standard Management Columns for an explanation of Standard Management Columns.

Custom Management columns can be used for Fact and Dimensions that have custom ETL that requires additional management columns for its operation.

Custom Management Columns can be added, changed and deleted through the Management Columns tab > Custom Management Columns table in the Fact and Dimension editors.

Previous Lesson
Back to Lesson
Next Topic