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.
- 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:
- Which organelles form like the cytoskeleton
- How can Apple be bothered
- Are there songs about alarm clocks
- How can you cut ice cream
- What is fern
- What are the best drugs for neuropathy
- Is Kim Kardashian married now
- The NEET 2018 was easy
- How do I lose weight with an operation
- What kind of thick hair should I eat?
- Monobasic zinc phosphate is soluble in water
- How often are Alexa rankings updated
- How do we divide the co-founders' equity
- Gladiators are allowed to marry
- What do you think is marketing
- Who owns the fence between the neighbors
- What's your rating of Olympus Cameras
- What is the mode of nutrition of the plants
- How can I cancel a messenger group
- How do I find a shaded area
- Why do so many people love rap
- Who is the previous president of the USA
- Successful managers are effective managers
- What is the best power dialer
- How to make electro-acoustic music
- What kind of exercise do CEOs do
- Why do bridges sway
- What is a refillable diary
- Are lemons actually purple?
- Blondes are prettier
- What is known about Apache RTR 180
- What was your best university experience
- What are some fantastic facts about Indian heritage
- How was Adobe Systems founded