Click here to Skip to main content
14,927,366 members
Articles / Hosted Services / Azure
Tip/Trick
Posted 22 Aug 2016

Tagged as

Stats

8.4K views
50 downloads
2 bookmarked

Default Dimension Members Generator (T-SQL)

Rate me:
Please Sign up or sign in to vote.
5.00/5 (1 vote)
22 Aug 2016CPOL1 min read
T-SQL script which generates and executes INSERT statements for missing default dimension members (Unknown, Not Available, Not Applicable)

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Miljan Radovic
Australia Australia
Business Intelligence Professional passionate about turning data into meaningful information and helping business grow and make better "data-driven" decisions.

Comments and Discussions

 
-- There are no messages in this forum --