Dimension Tables are secondary tables that store records in a primary database table. They’re useful for filtering and grouping data when performing database queries.

Designing dimension tables to optimize performance and user-friendliness is a best practice that Pragmatic Works Senior Business Intelligence Consultant Dustin Ryan examines in this blog.

Pick Meaningful and Consistent Names

Selecting meaningful and consistent names for your dimension tables will increase the quality of your data, particularly high-value, volatile information. With the correct naming, a row or column can make all the difference between an unreliable aggregation and a stunning new addition to your analytics arsenal. Furthermore, inconsistent naming makes it harder to audit for anomalies or redundancies within your dataset.

Finding the ideal naming scheme for your data may involve some trial and error, but it’s worth the effort. Consider starting a competition among teams to see who can come up with the most creative tablenames. In the end, you should be able to enjoy the fruits of your data science efforts for years to come; however, be mindful that there is always the risk of an outage or data loss so make sure all pertinent information is available prior to beginning any further analysis.

Assign a Primary Key on the Table Surrogate Key

The ideal practice for a table is to select a primary key that uniquely identifies each row, also known as a natural key. The primary key of a table can be used in SQL JOIN clauses, making it simpler to implement relationships between tables.

However, natural keys cannot always be used. For instance, if an employee’s network user name is chosen as the key for an Employee table, businesses must adjust it upon merging with another company or hiring new personnel who do not share the same network user name.

This process can be time-consuming and tedious, so many programmers and architects opt for a surrogate key instead. A surrogate key is an ID created in the database which may be an integer or GUID (Globally Unique Identifier).

Surrogate keys are typically short, numeric values generated automatically by the database management system (DBMS). They’re more compact in size than natural keys.

One major advantage of a surrogate key is its consistency. Unlike natural keys, which can be altered due to changes in data or business requirements, surrogate keys remain constant and help ensure system stability while avoiding potential issues.

Another advantage of a surrogate key is that it eliminates the need to write locking contention code. This saves significant time during SQL development and reduces crash risks.

However, if you decide to utilize a surrogate key, make sure it meets the needs of your business scenario. Select an ID which can be verified as unique among rows in a table and not too wide.

Have Audit Fields

When designing dimension tables, it is essential to include audit fields. These records store information about data changes over time and can be utilized for real-time queries and aggregation.

Another purpose of audit fields is to guarantee that any changes made in a field are properly recorded in the database. Doing this helps avoid data loss due to errors or missing values which can occur during migration or updates to fact tables.

Audit fields can be utilized in several ways, but the most popular is to create a table that contains each possible change to each row of data in your dimension table. This way, all modifications are stored centrally and easily queried using the same query.

Another option is to create multiple versions of a table that contain different surrogate keys and/or version numbers for each natural key in the dimensional table. This enables reporting historical data with differing effective dates within one query without having to reprocess the fact table.

Finally, when working with a large denormalized dimension table, segregating columns that get accessed more frequently into their own table can improve performance since only one copy of the data is stored in the data warehouse. This reduces both table size and load times.

This also guarantees that each possible alteration to a row of data in the dimensional table is recorded in the database. Doing this prevents data loss due to errors or missing values, allowing for a more systematic reorganization of its logical structure within the database.

Don’t Place Dimension Processing in Fact Jobs

When you need to process your dimensions but lack the time or motivation, consider setting up a task for processing at another convenient time, such as during the day or when a business user is in the office. This way, you won’t have to wait until your next nightly job to finish processing them.

To complete this process successfully, you’ll need a thorough understanding of your database structure and the size and complexity of the dimension table. Furthermore, you should know which processing option should be utilized for each dimension.

One of the most essential is using ProcessFull, which allows you to process only your dimension table and ignore any dependent partitions. This enables you to take advantage of modern storage technology in an efficient manner while minimizing impact on other tables in your database.

To discover how to accomplish this, contact your IT department or consult a reliable consultant for help designing an optimal approach tailored to your environment. You must understand your database architecture and the dimensions you plan on processing so that you can create the appropriate sized and structured solution suitable for both application and platform needs.

If you don’t feel confident tackling this task manually, consider using an automated tool such as SSIS. This will let you utilize one command line and the most efficient data source to optimize your dimension table.

Have a Zero (0), Unknown Row for Every Dimension

The most straightforward and consistent way to deal with an orphan dimension row is to assign it to a zero (0), unknown row. This ensures measurement data remains populated in the fact table – even when some details cannot be fully described by dimension data. Furthermore, assigning orphan facts to this zero (0) unknown row reduces the risk of dropping them when inner joins are used in queries.

SQL Server Analysis Services uses a process known as reduced row echelon to accomplish this goal. It starts with a matrix with non-zero entries and makes all non-zero rows equal to zero; once all non-zero rows have been dealt with, the process stops. Because this approach can be expensive, setting an upper threshold value for how many non-zeros should be processed by this step is recommended.

So, here are a few shortcut guidelines which may be useful as a quick checklist.

Dimension Table Effective Practices

• The table naming convention should identify it as a dimension table. For example:
  • Suffix Pattern:
    • <<TableName>>_Dim
    • <<TableName>>_D
  • Prefix Pattern:
    • Dim_<TableName>>
    • D_<TableName>>
• Have Primary Key (PK) assigned on table surrogate Key
• Audit fields – Type 1 dimensions should:
  • Have a Created Date timestamp – When the record was initially created
  • have a Last Update Timestamp – When was the record last updated
• Job Flow: Do not place the dimension processing in the fact jobs.
• Every Dimension should have a Zero (0), Unknown, row
• Fields should be ‘NOT NULL’ replacing nulls with a zero (0) numeric and integer type fields or space ( ‘ ‘ ) for Character type files.
• Keep dimension processing outside of the fact jobs