[et_pb_section][et_pb_row][et_pb_column type=”1_4″][et_pb_sidebar admin_label=”Sidebar” orientation=”left” area=”et_pb_widget_area_1″ background_layout=”light” /][/et_pb_column][et_pb_column type=”3_4″][et_pb_text admin_label=”Text” background_layout=”light” text_orientation=”left”]

Creating a Dimension

[/et_pb_text][et_pb_text admin_label=”Text” background_layout=”light” text_orientation=”left”]

To create a dimension, right click the ‘Dimensions’ folder (or one of its sub folders) and select Create New.

There are two ways to create a dimension. By far the quickest way is to import the Meta Data from a Source System, and then edit as required. If the Source Meta data is not available, then you can manually enter the information.

Import

Use the Import button on the Dimension tab to import the Meta Data for the Dimension from a Source System.

You must first create a connection to the Source System through a Connection Manager prior to import. To Import the Meta Data.

  1. On the Dimension Attributes tab, click the Import Schema button.
  2. Select the Connection where the source table resides.
  3. Select the Table and press OK.

The dimension attributes will be populate with the column names and data types from the source table. Some connection types don’t return column data types. In this case the data types are defaulted to varchar(50).

Dimension Tab

  1. Dimension Name. 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. Dimension Friendly Name. This is the friendly name the user sees in the Solution explorer. In the future, if DA was to generate a cube, it would be the name of the Dimension in the Cube. Spaces are not replaced in this name.
  3. Dimension Description. A description of the contents of the dimension. optional.
  4. Reference. 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.

To add an Attribute

Click in the empty row at the end of the list.

  • Attribute Name. 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 a name is specified that violates SQL Server naming restrictions, a red ! will be displayed on the row.
  • Data Type. 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.
  • Column Type. Choose the column type for the attribute. 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. It is the primary key of the source table.
  • SCD Type. SCD Type is an acronym for Slowly Changing Dimension Type. The list of types correspond 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). Type 3 and 6 SCD Type attribute will have additional <attribute name>_Prev columns created for them. If you don’t understand SCD types then read our ‘Data Warehouse and Business Intelligence Concepts – Guide’.
  • Description. Add a description of the attribute. Generally the description should make sense to an end user. Descriptions are used in the generated Data Dictionary.
  • Parent. 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. 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.

To Delete an Attribute

Select the attribute row and click the delete key, or click the Delete Attribute(s) button. You can select multiple rows.

To Change an Attribute

Simply find the attribute in the list, click in the field you want to change, and change as desired. Attribute name changes are generated in the DDL as non-destructive rename script. Changes are only made permanent after the document is saved.

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 Conformed Dimensions 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 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.  Meta data 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 Meta Data 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 meta data required by each pattern.

Custom Management Columns

What are 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.

[/et_pb_text][/et_pb_column][/et_pb_row][/et_pb_section]