Why are lookup tables used in SQL

Master data tables and AnalyticsCreator

In many of my projects there is a need not only to import master data, but also to enable the entry and modification of master data. So far I have used various options for this:

  • Microsoft Master Data Services (MDS)
  • Sharepoint lists and their import
  • Master data tables in the SQL server and access to these tables via SSMS, via Microsoft Access and ODBC, via web applications, via specially programmed front ends for entering the data

In this article I would like to describe the use of separate master data tables in more detail.

It is very easy to create a separate database just for the master data. The tables can be linked with one another with foreign keys, the foreign keys ensure consistent data, delete rules and update rules can be used. Since SQL Server 2016, the use of temporal tables also offers built-in data historization. These tables, including the required foreign keys, can be created very easily without the AnalyticsCreator.

The AnalyticsCreator can then use this database as a connector: either as a SQL Server Connector, whereby the data is imported into the main DWH (then the database can also be located on another instance) or as a “Direct Connector”, where the The database to be connected is on the same instance as the main database. The connection is made via views, so that no separate import processes and import tables are necessary.

But what do I do if I don't want to maintain the master data in a separate database, but in the main DWH?

There are several possible reasons for this:

  • you just want a database
  • the foreign keys relate to other master data that is imported into the main DWH from other sources.

How do you create real foreign keys with the AnalyticsCreator? I have already created a feature request for this:

0000490: Creation of real physical foreign keys in the target DWH

A suggestion from the AC chief developer:

The simplest is - create a post-deployment script with ALTER TABLE ADD CONSTRAINT
You can create the ALTER TABLE commands either manually or from the repository with an SQL script.

This is a pragmatic solution and shows once again that you can use the AnalyticsCreator to implement things that have not yet been implemented.

My solution:

  • I use “Add externally filled table” to add a new table
  • I will probably do without physical foreign keys, instead I use at least logical references in AnalyticsCreator, because I use them always and everywhere
  • However, if physical foreign keys are not used, the front end should offer look-up lists for the possible entries in order to simplify the input of consistent data for the user.
    Microsoft Access with ODBC connections to the tables is ideal for this.
  • A transformation follows the table, which checks whether the entered content matches the other master data
  • only consistent entries are historicized
  • for inconsistent entries there is an optional view that can display inconsistent entries to the user filling the table

On the following picture you can see in the bottom line from left to right:

  • the table for entering the data
  • a transformation associated with all lookup tables
  • another transformation with a WHERE condition, in which it is checked whether there is a match with the lookup tables:

this transformation is the source of the Historicization

In the second line from the bottom there is a transformation above the source transformation for the historization that supplies all data records that do not match. The filter is: