Default Dimension Members Generator (T-SQL)

• Download source code - 2.6 KB

Introduction

Someone deleted those default dimension members from Data Warehouse (again)? Nothing to worry about - using T-SQL script provided (needs user input), you can generate those members in no time.

Background

Properly designed dimensions in data warehouses always have one or more default members, such as 'Unknown', 'Not Available' and 'Not Applicable':

• Unknown member is used when we receive a valid piece of information from the source system (not null) which cannot be matched with any of the existing business key attributes in a dimension.
• Not Available member is used when we receive "empty" information from the source system (NULL)
• Not Applicable member is used when certain attribute does not apply to certain record in Fact table.

More about default members can be found here.

Using the Code

The code provided in the attachment can be used in both Microsoft SQL Server and Azure SQL Data Warehouse BI solutions. The code should be pretty straightforward with only several places where adequate textual values specific to your needs should be entered. Those places in code are marked with 'USER_INPUT'.

For the convenience, the first variable in the script @IsDebug can be set to 'Y' during the test phase. Setting it to 'N' will actually execute generated code.

History

• 2016-08-23: Initial code version