As you know I dropped all indexes of my data warehouse and therefore also unique constrains on my tables. The application should make sure that everything is fine. But I have a regular report which checks, if the constrains are still intact.

Lately after working on a project with huge amount of data, I got hit occasionally with duplicate rows in my hubs and links.

This was the reason to investigate further about why it did happen. Although I have used all measures below, it happened. Maybe the unique constraint indexes have additional functions when loading data to it. I must admit, I’m not the export in that area. I’m educated by mistakes and experience. Therefore, this is a little sum up about my lessons learnt.

Depending on the number of queries running at the same time with SELECT, UPDATE and DELETE, SQL Server decides, which data is locked, read, waited for, overwritten or inserted. There are some methods to influence its decision making.

SET TRANSACTION ISOLATION LEVEL

Working with transactions is usually something for applications having SQL Server as a backend, to ensure, that the data is written correctly to the database spanning multiple table manipulations. In the data area I didn’t respect to work with transactions for some time, as I was usually transforming data from source to target, hardly inserting data in parallel into the same table. With Data Vault it is rather a usual pattern.

The transaction is written like that:

BEGIN TRANSACTION
  INSERT INTO ...
  SELECT *
  FROM ...
COMMIT
OPTION(RECOMPILE)

By default I add OPTION(RECOMPILE) after fighting against bad execution plans.

This layout I had in place, when I discovered duplicate entries. My search for a solution led me to learn more about Transaction Isolation Level. SQL Server has a few of them:

• READ UNCOMMITTED
• READ COMMITTED (default)
• REPEATABLE READ
• SNAPSHOT
• SERIALIZABLE

I don’t want to explain all the nifty details about each option. There are plenty of resources at google.

I suspected that although I had all those table locks (see below) in place, data got changed between an INSERT and its SELECT statement. Especially in fact loading with looking up dimension keys. Sounds weird, but I couldn’t help suspecting.

Many websites express, that SERIALIZABLE is the most secure option, but has a very hard disadvantage – it leads to deadlocks. A deadlock is an issue when SQL Server tries to find out what to do next in a safe way. Sometimes 2 queries want to have access to the same resource, but queuing won’t help as other resources are blocked too. SQL Server terminates 1 query telling it that is a „victim of deadlock“. With restartable jobs this isn’t an issue. But still bad.

I added the following statement to my scripts:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

All following transactions are then executed with that isolation level.

I was hit with a lot of deadlock errors in the coming weeks. But I got the issue solved about having duplicates in hubs and links.

Hints

Hints are another way to manipulate the SQL Servers decision engine. There are a lot of hints, which we can add to a query. Some are affecting the whole query manipulating the execution plan (query hints) and some the queried table (table hints).

Query hints are added at the end with OPTION(something). As you know, by default I add RECOMPILE to it to enforce getting the best plan for every data set instead of recycling old plans. Sometimes I add FORCE ORDER or HASH JOIN to it, depending on performance.

To combat my issue, I rather use table hints. My favorite is TABLOCKX. By default, SQL Server tries to lock as little as possible. Sometimes only pages or rows of a table.  But I want to be sure that no other query sneaks into a running query manipulating something in between. TABLOCK will try to grab a „shared“ lock on that table while the X gets an exclusive lock.

My statement looked usually like that:

INSERT INTO ... WITH (TABLOCKX)
SELECT *
FROM ...

But somehow, I still got duplicates in my hubs and links. How could that be? It must be something with the SELECT query. My SELECT query has also a part in it where it checks the very same table, I’m inserting data into for records that already exists. Could it be that the INSERT locks are not applied to the SELECT tables although being the same table?

Reading through other blogs I have seen following recipe:

INSERT INTO ... WITH (TABLOCKX)
SELECT *
FROM ... WITH (UPDLOCK)

UPDLOCK acquires a modify lock telling the system it intends to modify the data but in fact I won’t modify it. I just want that other queries will modify them when I’m reading it.

Maybe this is the way to go, maybe not. It’s hard to find something useful in the data warehousing space as usually all the questions asked are for applications and record level issues.

While we talk about table hints, I got also into another issue. In my pattern collection I have 3 different insert methods. Each optimized for perfect fast loading. One is for the initial load. When the table is empty, I don’t need to retrieve the last record e.g. with Satellite loading for comparing hash values and deciding if it is a change or not. If the table is empty, just pump the data in.

IF EXISTS (SELECT 1 FROM table)
  QUERY INITIAL INSERT
ELSE
  QUERY NORMAL INSERT

So, what happens? The first query will check if there is a record in the table. Then it will run query 1 or query 2. But what happens in the split second between the 2 queries? Or maybe there is another INSERT already running? As the first query will run in READ COMMITTED it won’t find a record and tells the initial insert to not check for existing data.

We must modify it a little and adding table hints.

IF EXISTS (SELECT 1 FROM table WITH(UPDLOCK,HOLDLOCK))
  QUERY INITIAL INSERT
ELSE
  QUERY INSERT

The UPDLOCK will wait until any insert has finished and sets a modify lock. If the query is run with only UPDLOCK it will check for the record and releases the lock immediately again. The HOLDLOCK will keep the LOCK until the transaction is committed. The first statements sets a lot which is kept until the end.

The final layout looked like that:

IF EXISTS (SELECT 1 FROM table WITH(UPDLOCK,HOLDLOCK))
  INSERT INTO ... WITH (TABLOCKX,HOLDLOCK)
  SELECT *
  FROM ... WITH (UPDLOCK,HOLDLOCK)
ELSE
  QUERY INSERT

I believe there are way too many locks. And again, I had a lot of deadlock issues to deal with.

Is there not something easier to work with? Maybe something like a queue?

Application Lock

There is another method in SQL Server to work with: Application Lock. This means, it is completely independent of any table locks.

With this lock we can invent a locking mechanism by defining a random name for a resource. As my procedures are loading data to a target, I use the target name as a resource. It has nothing to do with the underlying table and doesn’t affect it.

How to set a lock:

sp_getapplock @Resource = 'table', @LockMode = 'Exclusive', @LockOwner = 'Session'

With this statement we request a virtual lock for the resource named ‚table‘. If any other query or stored procedure issues the same statement it will get into the queue and waits until the first lock gets released.

There are 2 levels to define these locks:

• Session
• Transaction

If we define @LockOwner = 'Transaction' the lock gets released after the commit.

But as I could have multiple transactions in one script, I rather like to specify it on session level. The whole script has to run through until I release the lock with the following statement at the end.

sp_releaseapplock @Resource = 'table', @LockOwner = 'Session'

My full script looks like that:

DECLARE @v_lock_code_{table} INT
EXEC @v_lock_code_{table} = sp_getapplock @Resource = '{table}', @LockMode = 'Exclusive', @LockOwner = 'Session'
IF @v_lock_code_{table} NOT IN (0,1)
BEGIN
  RAISERROR('Unable to acquire exclusive lock on {table}', 16, 1)
  RETURN
END
... a lot of code ...
EXEC @v_lock_code_{table} = sp_releaseapplock @Resource = '{table}', @LockOwner = 'Session'

Summary

With this method I create locks on all data manipulation objects. The queue will complete query after query and tries not to sneak into other workflows. If I finish an object, I release the lock and everything is good.

The focus is really to have a queue for every INSERT or UPDATE object.

SELECT queries or JOINs don’t need to issue this lock. The table is free to read. By the way: with the table hint method above there is also a hint named NOLOCK which should disrespect any existing locks. But when I use it, I seldom get any data returned until all locks are released.

With application locks I could also move back to READ COMMITTED isolation level.

Another side effect is, that other non-related queries are not locked too. Even though a TABLOCKX is only for a specific table, I have experienced locks on other transactions and in other databases. Lately I made a test. I stopped the whole execution engine on my test machine and started all jobs at the same time. I have never seen so much traffic on my databases and CPU was at 100%.

I hope, I have now peace of mind and no dead locks anymore.

On the market are a few solutions, which build Data Vaults nearly automatically. According to some metadata fields from the source a data vault can be built quite easily. Some are spending a lot of time tuning model conversion rules or with data modelling. Don’t get me wrong, modelling is an important part! But when the core model is defined, matching source data to it shouldn’t take long.

Most of my time is spent on implementing business rules. Persisting data in a Raw Vault needs no time.

As you have seen with my other blog posts, I like patterns. Repeatable patterns. I have heard that there are rule engines and other fancy stuff out there. So far, I can’t imagine how such a rule engine could work. Maybe somebody can show me their solution.

Developing

Before we are going into the patterns, what kind of tools are available:

• Graphical interfaces such as SSIS, Informatica, Talend and others
• Programming outside SQL with Python, Scala, etc.
• Programming in SQL

Graphical Interface

I used to work with SSIS. Graphical interfaces are nice. Especially the look at how the data is streaming through each step is very intriguing.

But I found it difficult to use with repeatable patterns. Usually when I learnt something new about how to ingest data, I had to go through all existing loading pipelines and modify the thing I learnt.

To import data from source and building raw vaults definitely a nice way to go.

Programming outside SQL

I’m sure that Python and Scala have their use case and the patterns below can be easily adapted.

If the data platform of your choice has no compute nodes, then it is the default thing to take the data out, do something and save it again in the platform.

Programming in SQL

But as I work with SQL Server, there is no need to take the data out, do something and load it again. Just a waste of bandwidth and the compute power of the server is not used. But I understand that for bigger solutions other ways must be found and used.

When working with SQL I have several choices:

• Work with Stored Procedure aligning all calculation steps after each other
• Work with Common Table Expression and combining all calculation steps into one statement
• Work with Views/Tables building on each other
• Or a combination of all above

I use templates to create stored procedures doing all the necessary steps AFTER I create the base SQL query. Therefore, I don’t want to meddle with it by modifying. This would break the maintainability.

I love to work with Common Table Expressions. At least for drafts. Fantastic to build data sets upon each other and enhancing the calculation. But when it comes to debugging, it is very cumbersome to always rewrite the SELECT statement at the end extracting data from each step.

Therefore, I like a combination of Views and Tables to separate each data set and calculation step. For debugging it is perfect. Sometimes I list all elements after each other and filter on a certain key to find out what it is doing in each step and if that is the right behavior.

Feel free to use your own method. Wanted just to share my experience.

Patterns

Just a quick note: If I write about keys, I mean all possible keys in Data Vault: Hub Hash Key, Link Hash Key, Natural Keys, Business Key etc. It just defines the grain of data I’m looking at. Is it on sales order or sales item level? Or something else?

What patterns did I find and use nowadays:

• Define the target key
• Define the source key
• Get all source keys
• Calculation
  • Get the data for the calculation
    • All values, last values or Point-In-Time
  • Define the LoadDate
  • Calculate
• Prepare Result Data Set

Example

For easier understanding, let’s take an easy example. We have the following Data Vault Model:

• hub_customer
• hub_sales_order
• link_sales_order_item (and feel free to read it also as hub_sales_order_item)

I have a raw vault satellite attached to the hub_sales_order which has a value for discount_amount. To calculate the margin on item level, I need to distribute this amount to all sales_order_items.

Define the target key

Before I start any job, I need to define what the grain of my result set should be. Maybe we have not yet an idea, how the calculation should look like, but I have to make sure, what the target grain should be.

In this example it will be a satellite attached to link_sales_order_item.

Define the source key

Apart from the calculation this is the most important step. What key do I need as a basis.

• What will be my source data key?
• What will be the grain of detail for the calculation?
• Does a „higher level“ key make more sense?

Sometimes it is the same grain as the target. Like when calculating the vat_amount for each sales_order_item.

In our case the level of hub_customer would be too high. The level of hub_sales would just be right. If just a SINGLE sales_order_item changes, I want to recalculate everything for ALL sales_order_items of a given order.

Get all source keys

When the grain of the source key is defined, I collect all source keys for which the calculation is needed.

I discovered that persisting that data into a table has the best performance.

Usually I define in my „delta“ object, what my target satellite will be. The template will look up the maximum LoadDate value from it and use that value to retrieve the newest data from the source.

Sometimes I add a time window by subtracting x days from the retrieved LoadDate to make sure that I don’t miss any beat. At the beginning of my journey I had the aspiration to have only real deltas and wondered how others are doing that when you have 2 source tables while 1 lags a little behind of time. With subtracting x days of the LoadDate this issue can be taken care of. The focus is on preparing the data, but if there is no change, it won’t load to the target.

And sometimes I have multiple satellites to gather my source keys from.

To have such a „bucket“ of keys is really a great thing. I try to find as many rules as possible why something needs an update. The goal is to get all possible keys for which I need to do a calculation or an update of old calculations. E.g. for calculating contribution margin 3, I need to add payment costs. Payment costs are not easily attributable. Therefore a factor is usually needed. If management decides to modify it later and also for old orders, what can I do? Either truncate the satellite and re-calculate it again or add a rule! I usually do the later. I don’t want to think about all actions others might do. It just has to work!

Calculation

As we have now all keys to work with, we can start collecting data and do the calculation.

Get Data for Calculation

Identify all satellites, reference tables and other tables where you need to extract data from.

As all my calculations are views, I create sometimes views of all my source satellites to make it visible what data I’m extracting from where and what I need. Just for easier reading for others. And sometimes not.

Then I join all those data views to my source keys. Of course, there is sometimes the case, that I join a source view later in the calculation steps.

In my example I would join now discount_amount from sat_sales_order_discount and gross_sales_amount from lsat_sales_order_item_amount.

The result data set is now complete. All source keys are available, all needed data points for the calculation are there.

All values, last values or Point-In-Time

While joining the source data I need also to answer the question, if I need all values, last values or to create a mini Point-In-Time table.

If the source key is of the same grain as the target, for example when I clean strings of bad characters, I just take the source satellite and use each row in the calculation.

If the grain is different like in our example, I usually take the last value of the source satellites. For easier immediate access I create by default for all of my satellites last value views.

Of course, I’m not limited to the last values only. I have also other views: Last, First, Current (excluding deleted), Timeline (from timestamp till 9999-12-31), Infinite (from 0001-01-01 till 9999-12-31).

Sometimes my users request also values being calculated for each data point in the past. This means that I create on the fly a mini point-in-time table for all LoadDates since the last load.

At the beginning, I thought that I need now to create a PIT table. But maintaining a full-fledged PIT table is usually not possible. Sometimes I have multiple business rules sitting on each other. When I would create the first PIT for my source satellites, do I create a new PIT for the just updated business vault satellite? And what about the next business vault satellite? Another PIT including it? Or just updating the old one? Kind of stupid. Therefore, I calculate a PIT on the fly with only the LoadDates needed in that pipeline and only with the newest LoadDates.

For our example it is enough to extract the last known value.

Define LoadDate

Usually my calculation is based on one main raw vault satellite. Then I continue to take THAT LoadDate for my newly created business vault satellites data.

If I load from more than one satellite, I calculate sometimes the maximum value of ALL LoadDates for a given business key.

,(
    SELECT MAX(v)
    FROM  (
             VALUES
                     (satellite_1.dss_load_datetime)
                    ,(satellite_2.dss_load_datetime)
                    ,(...)
          ) AS VALUE(v)
) AS dss_load_datetime

If the calculation like the current age and age_group of a customer is so random and needs to get calculated daily, I use the current system datetime as LoadDate. If the age changes I need a new LoadDate, otherwise the new value can’t be loaded into the Satellite.

If the LoadDate is not defined correctly, updates for new results will not get imported into the target, as the primary key (hash key and LoadDate) from the old calculation already exists.

Calculate

Do whatever you want. Sometimes it is a simple calculation, sometimes many steps with intermediate result sets.

In my case, I would create a view summarizing all gross_sales_amounts of sales_order_items for each sales_order.

In the next step I would use a calculation like gross_sales_amount / sum_gross_sales_amount * discount_amount to distribute it to each sales_order_item.

If I need to round it to 2 digits, I will have also to take care about cent differences (sum of all discount_amounts of all sales_order_items versus the discount_amount on sales_order level). I would sort the rows by gross_sales_amount DESC and add the cent difference to that line with the biggest gross_sales_amount.

Prepare Result Data Set

If the calculation is looking correct and I believe I have finished, I select those columns which should be loaded to the business vault satellite, calculate the hash_diff and load the data set to the target. Sometimes I also calculate new hash_keys to create same-as-links or other data vault objects.

Before continuing, take a break and congratulate yourself for the fantastic job you did. Of course, it is 100% error free.

Bonus Task

As you have your results saved, think about ways how to ensure that the result is always correct. Here comes the Error Mart into play.

What can you check:

• Do the same task again but in a different way and compare both results
• Compare summary of amounts per key of source and target satellite (based on last view)
• Compare counts of rows per key of source and target satellite (based on last view)
• Define keys which should have a result and check if the keys in the business vault satellite exists
• Etc.

Make it a habit that each business vault satellite has at least 1 QA view.

In our case we collect them in a separate database. We placed it next to the reports database. In my opinion it should be rather named as error reports than error mart. And I have error reports for Data Stores, Data Vault, Data Mart and Exports.

The created QA view shows only the faulty keys or rows. Everyday a job is run to count the rows in each QA view and reports a summary for all checks. Then we take actions and fix the issues.

Summary

You have seen my way of building business vault satellites. I hope you agree with the main patterns. If you have more patterns, let me now.

I have heard of business rules engines, but newer seen one. If you have one, get in touch. I would be happy to learn more about it.

Here is also a picture of such a transfer (another case):

Since then I have worked with this pattern happily without any incidents.

Some time ago I thought, well, are these patterns still true? Did I forget a pattern? Or has my view towards these patterns changed? This falls fully in line being smart in accordance to Jeff Bezos. Before I read it, I fixed stuff „stealthily“. I didn’t want to appear not knowing it beforehand.

I restarted at the drawing board and have been pondering about my patterns. And then I waited… until some inspiring thoughts appeared. On the way to work, in a lunch break, in the night in the bed (where I should rather sleep than thinking about issues) or while feeding my newly born baby.

And I came up with some changes and surprises. And I believe that the following pattern are so easy to understand, that it is a shame that I didn’t came up with earlier. But that’s usual. First digging into it, exploring the edges, finding common ground to work with and then summarizing up.

In my last post I split up the pipeline into 2 parts for easier consumption. In one of my meetups I learnt that to view it that way is not common. It was caused by the way how WhereScape works.

Here I present the enhanced vendor neutral Universal Loading Pattern. Before we start, do you think Data Vault, Dimensional Modeling or Anchor Modelling are so different? They are just flavors to work with data! I believe I can load them all with this pattern.

Source Query Automation

Here are the bits and pieces, to help me with writing a query and taking care about all those nifty little details. But for that to work, we need a query.

Extract & Transform

This is the place where my input to the processing of my data starts. A simple SELECT statement with FROM, JOINs and WHERE clauses. If I’m happy with the content and the calculation, I use it.

Now the interesting stuff starts.

Delta Loading

For any of my targets I try to load and process only delta data. But I’m too tired to write that in my origin query. And I have an option implemented, to do a full load if I want to. I need to be flexible.

If I structure all the layers as sub queries, I can specify on the next layer just the following statement. SQL Server will still make the best of it and will apply the filter already to the origin query.

WHERE dss_load_datetime > (SELECT COALESCE(MAX(dss_load_datetime),'0001-01-01') FROM [Target])

This works best for views. If I have a stored procedure, I would load the result of the above statement into a variable @last_dss_load_date and use it instead. A lot faster.

Source to Target Data Type mapping

This layer is only important if you care about data type consistency. Like in programming when you define variables with a specific type.

The mapped columns between source and target doesn’t necessarily have to be of the same data type. And if I have transformations in my source query, it is hardly known which data type I just created. Except you specify that in the source query. But I’m lazy about that. The system should fix that.

Any platform tries to avoid producing errors while inserting data into a table. They silently ignore the fact, that the data type don’t match. This is called implicit conversion. Have a look at the rules, what SQL Server does.

In this layer I create an explicit data type conversion. If I have transformations, I CAST it to the target data type. If the source and the target data type don’t match, I use some of the rules from the implicit conversion where I believe they are safe. Anything else throws an error while creating a view or stored procedure.

Business Keys

Last time I was thinking, yeah, a business key is just a business key. Yeah, there are some rules. I know that they are important. But I didn’t know that they are THAT important.

This time I want to spend more time elaborating about business keys.

Business Keys are really fundamental! A table without business keys is really hard to understand and work with. Until now I have been cleaning business keys only for hubs as suggested by Dan Linstedt. If we load data later to dimensions and facts, the business keys for them are already cleaned.

Sometimes I don’t build a data vault model but going straight from my Data Store (=Persistent Staging Area) to Dimensions & Facts. Having a Data Store gives me the freedom to do that and later adding a Data Vault when the business rules are getting too complex and loading time is slow.

I changed my view that not only data vault objects should have their business keys cleaned, but all my tables: Data Stores, Hubs, Links, Satellite, Dimensions and Facts.

But wait, are we not going to change the data which it consists? Shouldn’t be the Data Store equal than the source system? If the source systems CHAR column has mixed cases, shouldn’t we preserve it?

I make strong distinctions of business key columns and data columns. All columns of a source table are by definition data columns. I would derive my business key column from a data column. Business Key columns are here to ensure uniqueness in my target objects. But if the business key column is of type INTEGER, there is no need to duplicate it. If you want to preserve a mixed-case CHAR column, duplicate and preserve it.

Just a side note: If the mixed-case business key column should be preserved, we would have anyway a problem. Usually database joins don’t make a distinction of mixed cases. Then I would need to specify a collation with „ignore cases“ being turned off or using a binary collation.

Cleaning

So, how are business keys cleaned? The goal is, to get the value as close as possible to NULL. So far I only clean CHAR columns. Remove any spaces. If the data in the column is entered manually by a user and the application doesn’t check for clean data, I must even search for tabs, returns and other strange characters. The goal is to get the essence of the column value. Only human readable characters. A CHAR column should also avoid having mixed cases. I set them all upper case.

My statement is: NULLIF(UPPER(LTRIM(RTRIM(column))),'')

The benefit of trying to NULLify everything is, that the business key column has a constraint of NOT NULL. Therefore, if I hit a NULL it will crash and I will be safe of bad data.

Usually people see the business key cleaning and the following zero key replacement as a minor distinction. In this step I focus on the cleanliness of business keys. If the lineage from the source system via a data store and now to the target is kept, I clean it only once!

Zero Key Replacement

Here I „complete“ unknown data to ensure the working of my target. Data Stores, Hubs, Links, Satellites, Dimensions, Facts have different requirements. And all have Primary Keys. Primary Key values with NULL don’t work. And asking somebody to bring me product „NULL“ usually confuses people.

In the above step we might hit now a business key having a value of NULL. What do we do with it when the target tells me that it shouldn’t be NULL?

The book says for Data Vault to replace it with „-1“. „-1“ is just the value to represent „unknown“.

Let’s take a step back. Is Data Vault the only model which has NULL values? Fact tables referencing Dimensions could have NULL values too. Can we formulate a rule for that?

I used to use „-1“ everywhere in my templates. But I got into problems because of different loading patterns. The right thing to do is, to lookup the value on the linked objects business key meta data. Hubs and Dimensions are the objects, for which we can define zero keys. Any other object references to them to find the right zero key. In WhereScape I can define a zero key for any dimensional column. I will lookup that value and use it as a zero key. For hubs it doesn’t exist, but I would do that too, if it would exist.

Here are my self-defined „unknown“ replacement values, if the linked objects business key has no zero key defined:

As you can see, for BIT and TIME the unknown value falls into normal values. It is not possible to find values outside the scope. DECIMALs are used for money values, quantities etc. Having a „-1“ is very dangerous.

Roelant Vos has written a blog post about more flavors of unknown. Maybe this is something more for your liking.

Having 2 distinct rule sets for cleaning business keys, we could still hit a NULL value. How could that be? My fact tables are having also business keys defined. But not all business key columns have a related dimension. E.g. a line number of a sales order.

The result is now, that we have nice-looking business keys. Great!

Surrogate Key

In my previous model I had a layer to calculate hash keys and a layer to lookup dimension keys.

While pondering over the layers, a thought appeared: What do hash keys and dimension keys have in common? And how does a natural key fit into this picture?

Actually, they are all surrogate keys. A surrogate key is a key to represent a business key for further usage. To make it easier and faster.

But what type of surrogate keys do exist? Wikipedia lists the following characteristics for a surrogate key:

• the value is system generated
• the value is not manipulable by the user or application
• the value contains no semantic meaning
• the value is not visible to the user or application

Surrogate keys in practice just lists sequence keys with different data types (INT, UUID, GUID, etc.) as valid options.

I would say, Dan Linstedt invented a new surrogate key in the form of a string.

When searching the web, I didn’t find more types of surrogate keys. So, what are the options:

System-generated Surrogate Key (e.g. Sequence Key)

If we have a Fact object as a target, we want to join Dimensions to it. By default, we can join on business keys saved in both objects. If Dimensions and Facts are Views on top of a Data Vault, this will be definitely that case as it is impossible, to include a system-generated sequence key into a view.

If we have tables, we can gain some performance when we add an auto-generated sequence key on the dimension and use that value in the fact object. Instead of a multi-column CHAR join, we could have now a four-byte INTEGER really fast join option.

Theoretically you can also use UUID or GUID or whatever data type is auto generated in your system. But INTEGER is the fastest option.

This was also the idea and option in Data Vault 1.0. Being really fast on joins. There the surrogate key of hubs and links is also a sequence key. The systems used at the beginning of this centenary were not that fast as today’s systems. It made sense to have something like that.

User-generated Surrogate Key (e.g. Natural Key for Hubs and Links)

Instead of looking up the sequence key, Dan Linstedt improved the model to load data in parallel to the target. Everybody is speaking about the variation which consists of Hash Keys and so did I.

But in the last year I learnt more about the patterns behind it. There are platforms for which a hash key is not suggested. (e.g. Teradata has its own mechanism to treat business keys)

Therefore, it makes sense to focus more on the part which is shared on all platforms and what Dan really meant about the improvement.

Instead of having a sequence key we would need a concatenated string of all business keys separated by a separator. Just a string without hashing.

It took me some time to understand what the implications for that is. How it is saved on a hub, how it moves to the link to form the natural link key which then is used in satellites. All as strings.

This confirmed my resolution to see this as the real surrogate key for hubs and links. A hashed version of it is just for saving space, having a consistent data type (e.g. BINARY(16) for MD5) and faster joins.

Although in Dimensions a sequence key is usually used, you could use also natural keys. Inserting data into Dimensions and Facts in parallel as in Data Vault. Do you see the intriguing similar pattern? Dan Linstedt has improved the dimensional model into the new model Data Vault. The patterns are the same. And I believe when I will dig into Anchor Modeling, I will discover the same loading pattern as shown here. Nothing new. To improve your fact and dimension views on top of a Data Vault, generate a natural key as a dimension key. Should give a better join performance.

This is truly a Universal Loading Pattern. Give me your flavor of modelling and I will load it.

To summarize, surrogate keys are just representing the business key and are used to improve performance and to make it easier to work with multi-column business keys.

Data Column

There is nothing we can or should do with data columns. All manipulations are in the origin source query in the first section of the pipeline.

Calculate Change Diff

The only thing what we can do is, to improve the loading speed to the target. While inserting the data we need to know if a row is a change to the existing row or not.

We can compare data in a huge WHERE clause, where we compare each column with the target.

Or we concatenate all column values into one string. Then the WHERE clause is just a one-liner.

This pattern can be used for all objects auch as Data Store, Data Vault and Data Mart.

Of course, saving a huge string is a waste of space. The next section will help with that.

Hashing

I thought hashing is the most important part in Data Vault. Actually, we need it only because of technical deficiencies of the platform.

In the pipeline we have now data columns, cleaned business key columns and surrogate keys.

Now comes the part to improve the performance (or decrease it, as hashing takes its toll on speed too). Hashing is only there to improve things. Not because we need it! If things get bad, don’t hash.

Instead of using natural keys as long strings, we could calculate and use a „representation“ value of that. A representation! Hashing would be an ideal way to accomplish that, as it transforms data of arbitrary size into a fixed size.

We can hash only the natural keys and change diffs.

That’s it! Nothing more to write. Stupid boring hashing.

By the way, have you noticed the multiple use of „representation“? A business key is represented by a surrogate key which is further represented by a hash value. Just to make it easy and fast to work with data.

UNION ALL

So far, we talked only about one data set, one source query. What about having multiple data sets which are loaded to the same destination?

Here is the layer to merge them all into one data set before continuing to the next steps.

Row Condensing

All steps before were source oriented. Now we change view to the target. What do we need to do, to load data to the target?

Which data is necessary to preserve and what can we omit?

Removing Duplicates

Depending on the target object type, we can filter out now unneeded data. The target object tells us what it needs.

Ok, people are using DISTINCT for hub loading. But this works only for a single batch, a single LoadDate. If you load data in a full data set as I do, we need somehow to get 1 row out. Sorting it is the easiest form.

If you want to learn more about row condensing and more advanced forms, check Roelant’s blog.

Compare last saved row with first new row

When we load data incrementally to Data Stores or Satellites, it could happen, that a particular row with the same change hash is already present in the target.

Therefore, we can filter out all already existing rows in the target.

We have now a clean and relevant data set, ready to get inserted.

Timeline Calculation

Data Vault evolved in the meantime into an insert-only model. All timeline calculations are deferred to views generated on top of a satellite.

As you know, I try to make my work as easy as possible. For creating views, I use this very same pipeline we have been discussing so far. If any of my targets has any of the specific timeline columns, it kicks in. No matter if the object is a table or a view.

Therefore, let’s discuss, how a timeline is built.

I maintain 2 timelines. One is based on the LoadDate, the other on EventDate.

For LoadDate I have the following columns:

• dss_start_datetime
• dss_end_datetime
• dss_is_current

For EventDate I have the following columns:

• dss_effectivity_datetime
• dss_expiry_datetime

Currently I have no template to build bi-temporal objects. Hopefully… some when…

In a view we just use the calculation. In a table if one of the following columns exists, we need to UPDATE outdated rows after INSERTing new data to a table:

• dss_end_datetime
• dss_is_current
• dss_expiry_datetime

WHERE (NOT) EXISTS

Now comes the last test. If the data to get inserted already exists, I don’t want to cause any primary key violation.

What are the rules to detect existing rows:

The unique constrain can be created with the table and this is the true check for existing data.

But… Database systems might not behave as expected. In a WHERE clause, a database system might try to match characters with each other like SQL Server does. It will not find a difference in „ss“ and „ß“. Set a binary collation in the WHERE clause or create the Business Key CHAR columns with a binary collation.

Don’t use hash keys for comparison. An accidental hash collision will be skipped and you are bereft of telling a story that you found one. The primary key constraint violation on the hash key will tell you that you found one. I believe, winning in a lottery is easier than finding a hash collision.

If you load Hubs and Links separately from Satellites (like here), this step can happen already after the Business Key cleaning. No need to go into the slow hashing function.

INSERT, UPDATE, DELETE

When we know now, if the data already exists or not, we can fire now the commands to change the data.

Persistent Points

In the diagram above you see some points in the pipeline. The whole pipeline could work without interruption if your database system can handle all the rules in a timely manner.

I discovered to persist data into a temp table before hashing, increases performance. SQL Server has sometimes odd behaviors. Calculating 100m hashes and then taking the newest 100.000 in a delta load. Very bad.

The next point is the turning point from source to target. Having a temporary object helps with loading several targets such as Hubs, Links and Satellites in parallel.

It can help also with the next step: To Update timelines.

Post-Process Update Timeline

This step is only needed, if we have persisted timeline end dates. The end date of an existing record changes from ‚9999-12-31‘ to the start date of the new record.

If we have a temporary object from above, we only need to update the changed timelines for a given Business Key. All others can be skipped.

Summary

Quite a change from my last blog post about Universal Loading Pattern. It has a lot more context now.

For me the biggest break-through was to emphasize on Business Keys and Surrogate Keys for any given object. Everything else will follow. And using hashes is just a technical necessity. Nothing else.

I hope it will help somebody designing loading procedures for any Data Warehouse Automation solution. Or at least being a checklist, if you have thought about all transformation rules to load data to a target.

There are different naming conventions on the internet, but they usually just tell about prefixing objects. This is probably the easy part. How about structuring hubs with 100s of satellites?

There is no general rule. I believe, that most other developers like to develop their own style. And when you have a look into the data warehouse maybe you can identify who was working on them. Like people who can tell, which code came from which hacker or virus programmer.

So this is my style!

Projects

In WhereScape I work with projects. A project is just a collection of objects. And these projects can be grouped.

My project numbering follows the value flow. It starts with the load from the source and ends with the export to destinations.

I understand that there is also different approach to have a more holistic approach, combining all steps into one project, and deploy that as a whole. This didn’t work for me. If we would be a huge team, I would reconsider that.

The list above are just the main nodes. The next 1-n digits are used to order the projects according to the execution steps.

Databases

Consequentially I use the naming convention also for my databases.

Usually I have a main database without a suffix. If I see that the source or the area will get quite big, I separate them.

Objects

Here a list of how I name my main objects:

Double-underscore „__“ have the meaning of being „interpretation“ or „steps“. The main object name has only dashes to separate the words.

Columns

And here is the list about all my columns and system columns used. Quite a lot.

The list above is also my sort order. The system sorts automatically the columns accordingly.

Templates

And for those who work with templates and automation, here my list of naming conventions:

I hope, somebody finds that useful. If you have more stuff to add or have another approach, please get in touch. I’m interested in learning your approach.

Then I got my training and I still see a slide with my inner eye where a whole pipeline of source systems along a business process are pumped into 1 hub. With different business keys and structure.

This was very intriguing. Design an industry data model and save your data in satellites around it! What a break-through!

But… with implementation and working with the data models, questions started to arise, and solutions had to be found.

At first when I started to model my hubs, I started with what was lying in front of me. Let’s take an easy example.

We are selling nutrition products over the internet. We have multiple web shops. In every country 1. In Switzerland 2 because of multiple languages.

The source system has for each web shop a database. And customers who are buying products are saved in the database which the web shop is depended on. This means if I move to another country, I have to register again. I don’t question the model, in BI we use what we get.

So how to ingest data? I have been extensively evaluating which business key is best. As I was teached in my certification class, I tried to avoid the usage of an id but failed to find a better solution. Finally, I went with shop_code and customer_id.

So, I created a hub with the following columns:

• hub_customer_key
• shop_code
• customer_id
• dss_load_datetime
• dss_record_source

After I have been developing our model for the source system of our shop, I started to load data from our email campaign provider. They have also an id as primary key. But the better business key is email.

For each shop we have an account. It was easy to reuse the existing business key columns:

• shop_code = account (same as web shop)
• customer_id = email address

The only thing I had to change was the data type of customer_id. From INT to NVARCHAR. Everything was smooth.

(Of course, I could have chosen to create another hub and name it hub_recipients, but didn’t)

On top of that I built a same-as-link to travel from one system to the other.

On the horizon is another source system, but this doesn’t match the existing business key definition.

So, this is the first Hub-lemma.

Hum-lemma 1: Composite vs Concatenated Business Key

Although the hub definition matches, the business key looks different among source systems. How to model and ingest them?

Option 1: Create separate hubs

If the existing business key definition for a hub doesn’t match, create separate hubs.

hub_customer_a

• shop_code
• customer_id

hub_customer_b

• email

The advantage is, that you have a clear view and your design doesn’t conflict with existing structure.

Option 2: Concatenate business key

Instead of saving each business key column in its own column, some are advocating to concatenate the business keys into 1 column, separated by a separator. E.g. DE|1234 as shop_code|customer_id.

The advantage is that a hub looks always the same. A hash key and ONE business key column. Great for automation.

The disadvantage is, that you need to load the business key columns into a separate satellite. Otherwise the business key is not reachable, and a need arises to decompose the hub business key, which is slow and bad.

Hub-lemma 2: Overlapping business keys

We have found now ways to load „not compatible“ business keys into hubs. What happens, if of a sudden a business key of a hub overlaps meaning? The easiest example is, customer 1 of source system a is somebody else than customer 1 of source system b.

This „threat“ had been nagging me since the beginning.

Option 1: Separate by connection

Maybe you remember my last blog post about structuring files? Some of these elements can be reused.

If you have same looking databases from a source system, use the connection name as a separator.

As you read above, when I wrote about my journey, I found the same answer. I added shop_code to separate my customers from each other. In that way the customers don’t overlap.

Option 2: Separate by source system

If the source systems are really disparate and you believe no better business key can be found, then adding a source system name to the business key could be an option.

I don’t like this option. If I add the source system name as a prevention measure to everything, the whole concept of data integrating with data vault leads into absurdity and makes things over-complicated.

Option 3: Multi tenancy

What is multi tenancy. The Wikipedia description adapted to data means:

Multi tenancy refers to a data architecture in which a single instance of a business key saved in a hub serves multiple tenants.

This is nothing else than adding to every hub by design another column for the tenant.

In contrast to the previous option, its not about multiple source systems, its about multiple customers if we are selling our model to clients. We would have a management system to maintain the tenants and collect meta data about them and features we offer to customers which are enabled or disabled.

Option 4: Separate hubs

Instead of combining everything into 1 hub, building separate hubs would be an option:

Instead of saying that the suffix is the source system name, I would rather say this is the „business key space“. If another source system reuses the existing business key (e.g. email), there is no need to add another source system name.

Option 5: Separate satellites

I must admit, that I came up with this option when I searched for solutions against the threat of overlapping business keys long time ago. I had a very strong belief to not having multiple hubs with same meaning. I can’t remember who implanted that into my thoughts. Probably me ;-). My focus was to have only a few well-defined hubs and integrate any data into them.

The naming scheme would be:

• sat_{hub}_{space}_{content} (Raw + Business Satellites)
• sat_{hub}_{content} (Unified golden record)

My understanding was, that a business key by itself has no meaning. To give meaning I would always need to join a satellite to it. On a resent meetup I learnt, that others believe a single hub entry is key and you should be able to join every satellite to it.

With that option, it could happen that a hub business key has different meaning depending on the satellite I join. Usually I join only satellites with the same „business key space“ together. If I need to join data from another space, I would create a same-as-link and combine data sets from both sides and use the hub twice.

The advantage is that it looks clean and tidy. I see immediately from the satellite’s name which data I can join together. And there is no discussion about hub suffixes.

The disadvantage of that design is, that objects are getting clumsy and you need to work wisely on joins.

Maybe not the best design when I think from today’s standpoint and what I learnt so far.

Hub-lemma 3: Surrogate Key

A surrogate key is an alternate key to access a record. Maybe you have seen in dimensional modeling a dimension key which was created on a dimension and added to facts for faster and easier lookup. This is a surrogate key.

Option 1: Data Vault „0“

Dan Linstedt’s definition of a hub is: a unique list of business keys

There is nowhere written that a surrogate key is required to get a hub running.

In his certification course he told the story about the invention. The initial data vault model had no surrogate keys.

Option 2: Data Vault 1

At the beginning of this millennium, Dan Linstedt published his paper about Data Vault. There he wrote about the usage of an INT surrogate key similar to dimensional modelling.

This pattern is very fast on joining. But slow on loading.

First, not existing hub business keys need to get loaded. Then those newly created surrogate keys can be extracted to form the business key for a link load which in turns provide a new surrogate key to be used in satellites. The orchestration of this loading pattern seems to me quite challenging.

I have to admit that I don’t have hands-on experience with this. Just from documentation and books.

Option 3: Data Vault 2

The sequential loading issue had been addressed when Dan published Data Vault 2. The INT surrogate key was replaced with a hash key.

Suddenly we are able now to load all targets in parallel. A huge speed improvement.

The only caveat with hash keys is hash collisions. A hash collision is when suddenly 2 business keys generate 1 hash key. I don’t have one at hand but to make it clearer:

• „ABC“ => 0A4561…
• „ZXY“ => 0A4561…

In this fantasy model both keys generate the same hash value.

The chance that a hash collision will occur is very very very VERY rare. A hash key is used with one hub. It is not used system-wide. So the hash collision could only happen in a hub. I remember reading somewhere, to get a 50% chance of hitting a collision based on MD5, I would need to insert every second 600.000 records into 1 hub for the next 50 years.

Not on my system. Okay, I’m safe to use it!

Option 4: Natural Key

Back to the roots! Data Vault „0“. This topis was raised in a blog post from Roelant Vos. And I just stumbled over a blog post of Dan about this topic.

As there is no required need for any surrogate key, some want to move back to the origins. From a users perspective this has some benefits. If I look into a satellite and seeing a meaningless INT value or hash key, I always need to join a hub to see the business keys.

If you go with a concatenated business key option from above this would make it quite a good combo. Having a look into a satellite and immediately understanding the business key.

Just a word of caution. Using standard „text“ on JOIN is not always safe. Especially on databases which have collations which translate and sort characters at their own discretion. E.g. é = e or ss = ß.

But who will have such keys consisting of those strange characters anyway?!? I know, nobody… Except us. We bid on keywords and people use these strange characters. I’m pushing for 100% being safe for anything. If you go for that, create the business key columns with a binary collation. Then you are safe.

Performance wise joining NVARCHAR(100) (with 200 bytes) takes a lot more resources than joining a binary MD5 hash key with 16 bytes or INTs with 4 bytes.

Summary

The hub-lemma is a broad topic. By analyzing it, we have different options. Choose which patterns help you most and consider, what you leave behind. There is no perfect solution. And if you ask for advice, everybody’s favorite answer is „it depends“ ;-).

With time your skills evolve, and you learn how to optimize the queries, maybe using a temporary table or a common table expression. The more you learn, the more you are going to use. And suddenly it appears, as if your queries are stuck. You rewrite parts of the query, try to ease the pain of the SQL engine.

And some when you come across OPTIONs, which can be added to your table, your joins, your query or your stored procedure. It will tell the SQL engine how it should optimize the execution plans. Reading and optimizing execution plans is a huge playing field. I’m far from being an expert in that field. I would like to discuss and explain certain behaviors I discovered in BI and BI only. No OLTP optimization or single record stuff. In BI I work with data sets.

As you have discovered, my loading functions are all created with Stored Procedures. My typical layout looks like this:

CREATE PROCEDURE [update_stage]
AS

  -- Initialize
  TRUNCATE TABLE [STAGE]

  -- Temporary Table for Hash Calculation
  CREATE TABLE #temp
  INSERT INTO #temp
  SELECT *
  FROM   [SOURCE]
  JOIN   ...

  -- Insert new records
  INSERT INTO [STAGE]
  SELECT *
        ,HASHBYTES(...)
  FROM   #temp

This is my typical layout. The temporary table is needed, because sometimes SQL Server doesn’t know if it should calculate 100 Mio. hashes and filter after that the needed 100000 or vice-versa.

When I compile now the stored procedure, SQL Server tries to already create an execution plan which is saved with the stored procedure. If tables are around, it gets the counts and other meta-data and tries to figure out the best way to process.

Usually in my business vault satellites, the first load will be the biggest. The following loads will be delta. So, SQL Server sees all the data and thinks, wow, a lot to process. Well, THIS execution plan would be the best to process THAT lot of data.

On the following loads, it sees the data and the already existing execution plan and thinks, okay, we have a plan, so let’s get it done. It takes the assumption of the previous run and applies it to the small delta load. And this can lead to longer running queries or even „hanging“ queries. Again, I’m not expert, I just observe.

Now comes the OPTIONs into play. In particular OPTION(RECOMPILE). If you google for information about it, there are plenty of voices who suggest, to not use it. Instead improve your query writing skills and build better indexes and so on. What they don’t mention is, that they are writing those queries for OLTP application. There is speed king. If you have tons of transactions and need every time to RECOMPILE, it is a waste of time. Totally agreed.

But in BI we have little bit more time. At least on my system. Still needs to be fast. But I don’t need to update my objects every minute. I’m willing to wait a second to get a decent execution plan. Longer it hardly takes.

So, what options do we have:

RECOMPILE Stored Procedure

CREATE PROCEDURE [update_stage]
WITH RECOMPILE
AS
  ...

WITH RECOMPILE on stored procedure level is a very drastic measure. Always recompile everything. Each and every piece. Therefore, no cache and no stats. No way to sniff what SQL Server has actually done when the procedure did run.

RECOMPILE Query

CREATE PROCEDURE [update_stage]
AS

  -- Temporary Table for Hash Calculation
  INSERT INTO #temp
  SELECT *
  FROM [SOURCE]
  JOIN ...
  OPTION(RECOMPILE)

  -- Insert new records
  INSERT INTO [STAGE]
  SELECT *
        ,HASHBYTES(...)
  FROM #temp
  OPTION(RECOMPILE)

As you can see, each statement has its own OPTION(RECOMPILE). In that way, we are still able to analyze the stats afterwards – if you ever want to.

It is just nicer to still have some control than using the club-like option WITH RECOMPILE on stored procedure level.

Conclusion

Since I have introduced this measure to my procedures, I’m not surprised anymore with waits or „hanging“ queries.

Over the time, I figured out myself an optimal design for organizing the very first layer of a data lake or in old school language a ‚file repository‘. And recently I completed it by asking the experts at a DDVUG meetup, where Michael Olschimke showed us their solution. Thanks Michael.

So, what layers have I found:

/source

This layer represents the data provider. If it is from an external partner, I name it after him. If it comes from an internal system, I name it after that.

It could be also the technology, which can be the master of all data following. E.g. mysql, kafka, etc.

There are also tons of files which are produced by my company and don’t fall into any category. E.g. Excel-Files, manually maintained data, etc. I set the structure above anything, so we as a company are a source too.

/connection

If there is a chance of having multiple connections for the same source, add a connection layer. If there is the slightest chance of having another set of credentials, separate it.

/operation

Usually I start collecting data and process them. But some when in the future I start to pump data in the opposite direction. So where to put these files? Could be a completely different file repository. Or it can be combined in this repository.

This is the place to specify the kind of operation we do. I have:

• Import
• Export
• Update (log files of operation on the source system I do)
• …

/object type

This layer could be used to group the source objects into a structure. E.g.

• tables
• meta
• log
• …

Could be also a directory structure like database_name/schema_name if data from SQL systems are to be saved.

/object

This is the data object. In data warehousing this is the table name. Another way to describe it, is to think about all other following directories and files below summed up at this level. If a file doesn’t match the object definition or object meaning, create another one.

I had difficulties to find a proper word for that layer. Maybe content is more likely. Choose your favorite.

Examples are ‚customer‘, ’stock‘ or something like ‚marketing_cost‘. You get the point.

/data set type

What happens if we have different data sets for the very same object?

I have seen the following data sets so far:

• Full
• Delta
• CDC

Full means that we get the files every day in full. E.g. a customer table export from an application. Or an Excel-File which is modified by the users.

Delta means that we only get changed records. E.g. log files, transaction files.

CDC (Change Data Capture) is a very detailed transaction log file of a database having all INSERTs, UPDATEs and DELETEs of the source system. Usually it is ingested directly from a database to another database.

If the rows in a file are just data, why do we need to separate Full and Delta at all? The only reason for separating them is, if we need to detect deleted records in the source system. Then I’m able to create a status tracking satellites or in non-data vault systems to delete the record or mark it as deleted.

The difference between Delta and CDC is, that CDC contains information about deleted records.

/version

Version? What is a version for? This principle I derived from APIs. E.g. Google API evolved from v3 to v4. What happens, if the source suddenly decides to modify the file structure? E.g. translating all column names from German to English? Or modifying from csv to json? Adding columns is not a new version. The loading procedure should cope with that.

The main reason for a new version is, if the existing loading procedure can’t process the file. Then add a new version and write a new loading procedure.

My default version is ‚1‘. I have only a few objects were a ‚2‘ had to be created. But I want to be prepared! Modifying an existing layer after a need occurs, is not future-proof. Tweaking loading procedures to understand were something ends and something new starts is bad.

/load_date

If you are going to model your data lake or file repository to build data vault objects, this layer is a must have. You’ll want to preserve, when the data first appeared in your data lake. The definition of the load_date can be found here.

All others can probably omit that.

/group

Sometimes I need to ingest data by different characteristics. E.g. per company, per shop, per account, per machine, per anything. Per day is not a valid group (see below in event date).

Usually the differentiator is not part of the file name but part of the directory structure of the source system. Here is the place to preserve and save it.

The semantics of a group and connection could be kind of equal. Sometimes it makes sense to use it as a connection above or as a group here.

Note to myself 1: Why not placing group before data_set_type? Because we need a loading procedure for each data_set_type. Groups and the rest can get looped through.

Note to myself 2: Why not placing group before load_date? Could be, but a load_date should be a single entity. It serves better for incremental loading instead of having it inside each group. A group is data and data comes at the end.

Note to myself 3: Why not placing group after event_date? Sure, feel free.

/event_date

First question: Does there exists an event date as content inside the file? If yes, all good! No need for an extra layer. Nevertheless it could be created in a Hive environment for speed purposes to partition the files.

Second question: Should the file have an event date? If yes and we don’t have it, we need to add this somehow to the path.

Maybe the file has a string in the filename like ‚YYYY-MM-DD‘ or an integer value representing a timestamp. Then add this information as an event_date.

If there is nothing available, use as a last resort the create datetime of the file.

Structure it as /YYYY-MM-DD or /year/month/day. As you like.

/missing

Do we still miss something? If a key information is missing inside the file, add it as a layer. Manipulating files to add a missing column is a no-go!

The only way possible to add more information is to create them outside the file content. Maybe as another layer.

Actually group and event_date from above are just examples of missing data.

/file.ext

Finally, our source file.

If you ever need the create datetime of the file, preserve it by renaming the file. It doesn’t survive the copy process to any cloud platform and is lost.

And don’t give too much meaning to a filename. To extract data from the filename is difficult and needs precise instructions, where to look for what. No automation.

A file represented by a filename is just data!

Summary

All these components can be used as directories or if your system supports it, as meta data to the files. If you use meta data, you will probably find and use even more tags to describe your files.

Choose which elements from above are important for you. In a directory structure, the full string will look like this:
/source/connection/operation/object_type/object/data_set_type/version/load_date/group/event_date/file.ext

A very very long path and people might get lost. But computers will like it, as many pieces can help to automate the loading procedures. Some systems are even suggesting to use key=value as a directory name, e.g. year=2018 or shop_code=de. This makes it even easier to automate.

The absolute minimum structure would be in my opinion:
/source/object/version

If you believe that a table with 2 versions could be automated too, then the 2nd version is not of importance ;-).

For ingesting data, we need a procedure for each combination of
/source/object/data_set_type/version

If you believe that there are way too many directories and you don’t need all these nifty things, don’t use load_date and modify the filename to include the omitted layers:
{group}__{event_date}__original_file_name.ext
{group}__{event_date}__{object}.ext


Please tell me your solution, if you believe I missed a case.

Update 16.08.2020

In the last two years I have been working with that model. So far I didn’t miss any new type and I didn’t find any new layer. It was a joy working with that. Every file found easily its place.

What I discovered was that I would merge the layer data set type and version to keep version. While it was clear that a certain data set type in the file is full I changed it to delta while loading. And I found a new data set type: Multiple full. A inventory file which consisted of multiple full data sets, each for a different date.

Therefore the data set type is deferred to how the data is transformed to Data Store or Data Vault.

This should be a post to give credit to Dan Linstedt and thank him for inventing Data Vault and introducing LoadDate.

When I encountered the hub-and-spoke concept of Data Vault I thought, why on earth do I need a LoadDate? Combining in a Satellite the HubHashKey with the LoadDate to form the Primary Key makes sense. But why would I need it in a Hub or a Link? Just to know when a Hub entry was first introduced to the data warehouse? The usage is universal and far reaching!

What is the LoadDate? The definition is:

„The LoadDate is the date and time of insert for the record“

It is the moment when a record is first inserted into a Data Warehouse. It is not the extract date from the source or the current datetime in the source system. It is really the INSERT datetime in my Data Warehouse! The first time a record exists.

How to ingest it:

• Save the value in a variable and insert it with the data set into the load table
• Set a default constrain like SYSDATETIME() on the LoadDate column

What about when the insert takes a long time with a lot of data? Do I get a LoadDate for every second it loads? This would be very precise. In my world one LoadDate is one batch. One batch could be the load of one file. The next file has a new LoadDate. When loading data in a loop of something, each loop has a new LoadDate.

When the record has been inserted into the load table, the LoadDate stays with the record. Where ever it goes! Like a tracking marker.

The LoadDate is used in:

• Load
• Data Store
• Hub
• Link
• Satellite
• Dimension
• Facts

I use it everywhere!

In a Data Store (aka Persistent Staging Area) I use it to order the data and batch-load the data to the target. Together with the SequenceNo it forms also the Primary Key.

In Link and Hub I know when the first appearance of a business key happened.

In Raw Vault Satellites I use it to load data incrementally from the Data Store.

In Business Vault Satellites where multiple source tables are joined together, which LoadDate succeeds? If history is important, I use it to create mini-Point-in-Time tables. If history is not important, I use the most recent record and calculate the MAX value of all LoadDates.

SELECT *
      ,(
         SELECT MAX(v)
         FROM (VALUES
                 (table_1.dss_load_date)
                ,(table_2.dss_load_date)
                ,...
              ) AS VALUE(v)
       ) AS dss_load_datetime
...

In that way I can use delta loading and it fits perfectly to my Universal Loading Pattern.

How can I use it in Dimensions & Facts? The main question in those objects is, when do I need to UPDATE columns with new data? When I was researching the best way, I used first to calculate a ChangeHash. Very time consuming! After detecting the formula above, I thought, why not use the LoadDate for change detection? If for a given Business Key the LoadDate changes, chances are high that the record needs an UPDATE.

With this approach I’m much faster now. And I’m also faster in preparing the data in a stage table as I can use the LoadDate for the delta loading layer too.

Are there any other similar concepts? In SQL Server one could use rowversion (altered from timestamp). It is a system-wide incremental number saved as a binary. But copying data into the next table will create a new rowversion except you extract the value and save it as a normal value. In my view not easy to handle and not readable. How do you know, when record 1234 was inserted?

Dan Linstedt: Thanks for introducing LoadDate to the Business Intelligence Community and forcing users to use it correctly. It is of tremendous help! I use it in batch loading, delta loading and change detection (only in Dimensions & Facts). I couldn’t do without it anymore. Thanks a lot!

From my background as an accountant and controller I had by nature a good feeling for numbers. This helped me to understand, how to structure data.

A couple of years ago when I started to work in a startup, I usually grabbed data directly from source systems to support our cause to tackle financial issues and finding process deficits.

Over time the queries grew in sizes and run-time got longer. When the run-time was not any more satisfying and our excel sheets with tons of formulas and recalculations broke, the need arose to build something more reliable.

First, I created queries and jobs which persisted data in advance. Just a bunch of reports. But we wanted to give these data also to users. So, we started to build a data warehouse with dimensions and facts and giving access to them with a nice tool. My data journey from Kimball Star Schema to Data Vault is for another blog post. I want to focus here on indexes.

My first objects had no indexes. When performance got degraded, I started to investigate. In SQL Server, there is a feature called „Database Engine Tuning Adviser“. I would consider its suggestions and create indexes and statistics.

Time passed and processing time was growing again. At one time, I analyzed my created indexes and thought, what would happen, if I would drop all indexes? Wow, the processing time decreased from 3.5 hours to 2 hours.

Lessons learned: Don’t create too many indexes. Index creation uses also processing time.

Proper Indexing

My next adventure was about clean data. From my background as an accountant, I want to be sure that my numbers are always correct. My nightmare situation is, when data is not correct and numbers, especially financials don’t match.

Sometimes my Dimensions, Facts or Stage tables would create cartesian products (duplicates). The solution was to make sure, that I would never ever get duplicates. I started to use UNIQUE indexes.

The confidence in my data grew, because any error would alert me.

The downside of it was, that when something broke, parts of my whole pipeline stopped at that stage. If the processing time is 6 – 8 hours and users want data at 9, what happens, if my processes breaks in the night?

We set up a shift plan among our team, so that first thing after getting up is, to check emails on the phone if everything runs smoothly. This would save time until my users get their data. Not a satisfying solution, but with indexes breaking the load, what should I do?!?

Lessons learned: Indexes are great for data quality. But can break loading processes.

Exploring Cloud Databases

At a fast-growing startup, available data explodes. We got into a habit to regularly replace our SQL server to accommodate our increasing data need.

We figured out fast, that always increasing the size of our server is not satisfying. At that time Hadoop with MapReduce made its appearance. Just adding commodity hardware. No high-performance expensive single-server anymore.

I’m a SQL guy. I had a trauma replacing all my code somehow with MapReduce.

To leverage that, I started to analyze some cloud offerings such as Redshift and others.

When testing those analytical platforms, I discovered that none of them supported constrains. No primary key, no UNIQUE indexes. Even nowadays Azure SQL Data Warehouse does not support it. Their primal goal and use is to analyze a lot of data fast without any obstacles.

Their answers to my issues was, that the application must make sure, that there will be no duplicates. BI has usually no application.

For my accountant’s heart this was kind of no go. How to make sure that my or my colleagues programming will be always correct?

Lessons learned: Cloud databases are not always the answer.

BI automation

One way to ensure good results is, to make sure that data processing will always be the same. This is also something that Dan Linstedt inspired me, when he showed me the maturity diagram which he translated to a data warehouse. On the highest level you would have:

• BI automation
• Parallel Teams
• Rapid Delivery

This was something I wanted to achieve. I can say now, that I’m able to produce best results for any objects I’m loading data in. Give me more developers and all will produce great data!

Lessons learned: Using a framework to support data processing application is a big win!

No indexes

A year ago when I migrated to SQL Server 2016, I discovered a new index type. Actually, it is rather new table storing type. Instead of a row store, data is now stored in column stores. Think of saving in Excel column A, B, C, etc. instead of Rows 1, 2, 3.

Over time, I added those CLUSTERED COLUMNSTORE INDEXES to all my objects (except stage tables). The immediate effect was, that I saved a lot of disc space. Usually 10 – 20% from the former disc space is used now.

On top of that I still had my NONCLUSTERED indexes. With time and analyzing execution plans I discovered, that SQL Server hardly uses any of my NONCLUSTERED indexes. And when it uses them, the execution plan might include nasty „Nested Loops“ instead of „Hash Matches“ to join 2 data sets together.

With growing confidence in my BI automation templates, I’m now in the situation that my application makes sure, that anybody in my team produces great results and that I can remove any UNIQUE constrains.

The immediate effect was, that our processing speed increased. Why? Data is not anymore loaded into the databases logfile for checking the UNIQUE constrains before inserting the data, but immediately inserting the data without any delay. Like any other cloud database.

I’m now kind of par with the cloud offerings. There is no need to migrate to cloud because of speed issues. Any indexes, especially UNIQUE indexes, are speed blockers. They don’t have them, I don’t have them. Another benefit was disc space. The indexes are using up to 2 times the space of a CLUSTERED COLUMNSTORE table. Saving space and time!

Do I have a safeguard routine? Of course! As the BI automation solution has all the metadata, I regularly query for duplicates.

When I look back, I started with no indexes and ended with no indexes. The difference is, that with BI automation I increased data processing quality and with CLUSTERED COLUMNSTORE a new technology emerged.

From now on I don’t need to grab my phone at first sight in the morning. The reasons for breaks now are usually external dependencies.

Lessons learned: Sleep is a precious good!

Today I would like to talk about the last part in that model. Views based on satellites. Satellites are usually built with INSERT only. Any logic interpreting the data should be deferred to a view. What kind of views do I have in our system:

• First: Not used yet but possible, retrieves the first record of a satellites surrogate key
• Last: Retrieves the last valid record. Could also be called „current“
• Timeline: Generates a timeline for LoadDatetime and EventDatetime. EndDate is 9999-12-31.
• Infinity: Generates a timeline for LoadDatetime and EventDatetime. In contrast to the previous timeline the first record in the timeline has StartDate = 0001-01-01.

Especially the last option „infinity“ has been of great help. For effectivity satellites it was important to get the timeline as wide as possible. But also in other circumstances like adding purchasing costs to a sales order line. Instead of searching for the first valid purchase cost which might be after the first sales I can be sure, that I always get a price.

At first, I had a configurable timeline where I could „overwrite“ the first StartDate. Over time it became difficult to distinguish timelines with a wide date bandwidth and those without. To make it clearer for us, we decided to create 2 separate views if needed. A traditional timeline and a wide timeline. „Infinity“ sounds great for that.

Let’s see. In our database, satellites can have 2 date columns (of course even more, but I automated those).

• LoadDatetime
• EventDatetime

Instead of using LoadDatetime as the first value to calculate timelines on, we use own columns to represent the timelines. This has the advantage that I don’t „overwrite“ the LoadDatetime to represent ‚0001-01-01‘. This is also the strategy of WhereScape which is Data Vault compliant.

What are the names used:

• LoadDatetime
  • StartDatetime
  • EndDatetime
• EventDatetime
  • EffectivityDatetime
  • ExpiryDatetime

Let’s get to the code. Easy stuff first.

First & Last Value views

One could think that calculating a timeline first and then getting the EndDatetime = ‚9999-12-31‘ would be a nice solution. Still doable, but not fast.

The fastest option I found was to calculate it like that.

SELECT *
FROM   satellite
INNER JOIN (
    SELECT hub_hash_key AS hub_hash_key
          ,MAX(dss_load_datetime) AS dss_load_datetime
    FROM   satellite
    GROUP BY hub_hash_key
) AS last_rows
ON  last_rows.hub_hash_key = satellite.hub_hash_key
AND last_rows.dss_load_datetime = satellite.dss_load_datetime

Nice, easy and fast. For the first value replace the MAX function with MIN.

Timeline & Infinity views

When calculating only one timeline from a date is easy. To calculate it with 2 date columns is just a little trickier because of odd but understandable behavior. A bi-temporal view comes some when in the future when I grasp it.

Timeline calculation uses a lot of window functions and execution plans are quite bad as it is sorting the data forth and back.

What are the requirements:

• dss_load_datetime/dss_event_datetime: Stays as it is
• dss_start_datetime/dss_effectivity_datetime: Takes the datetime. For infinity views it should represent the first value of the timeline as ‚0001-01-01‘
• dss_end_datetime/dss_end_datetime: Takes the next datetime or if there is none ‚9999-12-31‘

What will be the PARTITION BY clauses for each timeline:

• dss_load_datetime: The hub_hash_key of the satellite
• dss_event_datetime: The hub_hash_key or the driving_key of the satellite

What is the driving key? The driving key is useful for effectivity satellites to force a 1:m instead of a m:n relationship. A good blog post which describes it in-depth can be found on Roelant Vos’s Blog.

From my universal loading pattern blog post, you see that I organized all logic in sub-queries. To calculate the timeline, I need 3 sub-queries.

1. Sorting the whole dataset by dss_load_datetime
2. Calculate dss_start_datetime and dss_end_datetime
3. Calculate dss_effectivity_datetime and dss_expiry_datetime

Sorting dataset

I use this function not only in satellites but also for calculating timelines in stage tables. Sometimes a cartesian product happens and produces bad results. In a cartesian product 2 or more lines look the same. It can happen that for the LoadDatetime timeline the first row is being used and for the EventDatetime timeline the second. Resulting in both records being returned and violating a unique constrain.

Therefore, I sort the whole dataset by dss_load_datetime and use that number in the following queries.

ROW_NUMBER() OVER (PARTITION BY hub_hash_key ORDER BY dss_load_datetime ASC) AS dss_row_no

Calculate LoadDatetime timeline

For normal timelines just take the dss_load_datetime as dss_start_datetime. For infinity timelines where we want to set the first dss_start_datetime as '0001-01-01', we need to figure out, if this record is the first or not.

CASE WHEN LAG(dss_load_datetime,1,NULL) 
          OVER (PARTITION BY hub_hash_key ORDER BY dss_row_no ASC) IS NULL
     THEN '0001-01-01'
     ELSE dss_load_datetime
END

For dss_end_datetime we need the following window function query:

LEAD(DATEADD(ns,-100,dss_load_datetime),1,CAST('9999-12-31' AS DATETIME2(7))) 
OVER (PARTITION BY hub_hash_key ORDER BY dss_row_no ASC )

As you can see, we order both queries with the dss_row_no from the previous query. As we need both window function LEAD and LAG, SQL Server will sort the data forth and back. I didn’t find another solution for that yet.

Currently I use a DATEADD function to subtract 1 time tick to being able to use BETWEEN queries. If you don’t need that, just remove the DATEADD function.

Calculate EventDatetime timelines

The calculation looks like the previous one. Except that we can sort the data by hub_hash_key or driving_key.

In contrast to satellites where we can be sure, that we don’t get any duplicates per hub_hash_key and dss_load_datetime we can get duplicates on dss_event_datetime. Instead of just sorting the data by dss_event_datetime we use also dss_load_datetime represented by dss_row_no for sorting.

The calculation for the dss_effectivity_datetime looks like the following:

CASE WHEN LAG(dss_event_datetime,1,NULL) 
          OVER (PARTITION BY driving_key ORDER BY dss_event_datetime ASC,dss_row_no ASC) IS NULL
     THEN '0001-01-01'
     ELSE dss_event_datetime
END

The calculation for the dss_expiry_datetime looks the same as for the dss_end_datetime.

LEAD(DATEADD(ns,-100,dss_event_datetime),1,'9999-12-31') 
OVER (PARTITION BY driving_key ORDER BY dss_event_datetime ASC,dss_row_no ASC)

Again, the DATEADD function can be removed.

You see that we can end up with 4 sorting functions which can have a huge impact on performance. Create 2 separate views or persist them (or not).

Happy sorting!

Well, I don’t know if this is the right way. But I want to share my experience about it with others.

First, I want to say that WhereScape enabled me to reach my goals faster. At least now. It took a year to get to full speed. This is due to the fact that I had first to discover, how it works and what is important to me. The first sentence of the sales consultants was: „We can do whatever you want!“.

You have to take that really literally! Don’t underestimate it. It is a blessing that you can form this tool to your likings. It is a curse that it takes time and has no blue print attached. Well, WhereScapes has own templates which are filled with options, accumulated from the beginning to present by user requests. Either I click through the whole process over and over again or I develop my own templates to remove manual work.

Object creation

It is a dream to design transfers. Create the first load object and then drag the objects or the columns from one step to next. E.g. Load → Stage → Data Store → Stage → Satellite → Stage → Dimension → Stage → Export. So easy with drag & drop. From one or multiple source tables. And then just building the right JOIN statements and an optional WHERE and GROUP BY clause.

The objects are created with the right data type with a mouse click. Stored Procedures are created without further input. Indexes are added automatically if needed. I just have to worry about business logic implementation.

But to get there has taken us a long time. And it has some limitations.

32 bit vs 64 bit

The application is still running in 32 bit. For a meta data management platform this is ok. It gets nasty when I have to work with execution.

My loading works with SSIS, ODBC or PowerShell. In ODBC I have to use a 32 bit ODBC driver. Calculate yourself, how much memory I can use for a data transfer from a database? Depending on row size crashes happen at around 50 to 100 Mio. records.

PowerShell is another beast. Windows has 3 versions of it. The 32 bit Version in the 64 bit Windows directory and vice versa. And then there is a third version as well. Figuring out which works in combination with WhereScape for a given job is tiresome. And if you need to extend PowerShell with more functionality, most modules are in 64 bit. So I have to start a 32 bit PowerShell, switching to a 64 bit version and then start executing.

Object name length

This limitation is really bad! RED has a limit of 64 characters for any object like tables, columns, indexes, stored procedures, etc. Only „parameters“ (kind of global variable) have a length of 256 characters but the system can address only 64 of them?!?

The 64 characters is for all objects. So if I create a table with 64 characters, I still get errors because of other objects appending to its string.

• Stored Procedure: update_{object name}
• Index: {object name}_idx_BK

With this calculation I have only 57 characters left.

In 3D there is a limit at 1024 characters. Enough space to evolve.

I hate abbreviated object names. Nobody except me is going to understand them. If you think of something like stage_{satellite}_{source system}_{source table}_{calculation step}, you can see that I might run out of space easily. And when I need to compose a multi source mapping and adding src_{hub}_ to the front, this runs out even faster.

3D to RED deployment

As described, there is a discrepancy of 1024 to 64 characters for object names. When bringing objects back to RED, object names are cut at 64 characters. For custom indexes it gets cut at 40 characters already. I don’t know why.

So I need to take care about object naming inside 3D. 3D is a nice program to design. But if I need to take care about object length for later usage in RED, it gets cumbersome.

My aim is currently to get a rough idea in 3D and then do the transfers in RED directly. With my customized templates this is as fast as working in 3D.

Object defaults

In 3D, I can set default column names with default data types. This is not possible in RED. E.g. I improved performance by switching from CHAR(32) to BINARY(16). Or, all of my date system columns should be datetime2(7). Not possible to set. Unfortunately until recently I had to manually modify them or using a QA query to modify the meta data in the database regularly. When I started writing this blog entry, I thought why not using triggers for that?!? Well, after such along time without it, I have that now in place.

In my opinion, users shouldn’t interfere with the application database directly. This is really really bad behavior, but unfortunately necessary.

Pebble

Pebble is the templating language WhereScape introduced. The intention was right to give the user the flexibility to develop their own transfers. It has taken me a year to develop fail prove transfers to get industries standards mapped. It is a trade off swapping manual time with development time. But satisfying when I get more insights, how things should work.

With successful usage the appetit grows to get more manual work incorporated into my templates. Then the problems are starting to appear. Pebble doesn’t scale with complexity. Macros suddently stop running, variables are getting forgotten while compiling. If you don’t check each and every creation, suddenly a bad stored procedure is there. Very unstable. Then time is wasted to find alternative ways to accomplish the same output. Another developer would question my bad design. But it has to work and I can’t wait!

For small and easy templates without logic, Pebble is running well.

Object Types

I like the split for every type of table I need: Stage, Data Store, Hub, Link, Satellite, Dimension, Fact, Export, etc. I love the concept of having a load object for getting outside data into the data warehouse. This is far more logical than having a stage table directly and load that content to the target.

Stage is my workhorse. It is there to prepare the data from target to target. Load is also a target.

7 object types = 7 templates to rule them all. But bringing logic inside it was not easy. A long and steep learning curve.

Stage Table and Stage View

See the Key Features graphic on their website: https://www.wherescape.com/solutions/automation-software/wherescape-red/

You see that there are options for:

• Stage View
• Stage Table

Stage View doesn’t exist!

All target objects like Stage, Data Store, Hub, Satellite are tables. And there is an additional object type called View. This is the only view.

But when developing a transfer, I don’t want to recreate the object as a view when I want to find out, if this runs faster. If I have a stage table, I want to be able to switch between a view and a table.

Objects are grouped for each target type. So switching between the stage menu node and view menu node is cumbersome.

There is an option to create a DDL statement. I developed a template for that. But once a view, it stays a view. I can’t switch back to a table without going manually to the database and dropping the view.

Data Vault

WhereScape is proud of their Data Vault skills. But it has taken them very long time to get that right. To add columns to a HashKey or ChangeHash is a dream!

The defaults are running ok. But they forgot that hubs are loaded from multiple stage tables. And just until recently I had to circumvent that with creating fake objects and using fancy templates. And in my opinion Links are not built correctly. The Unique Constrain of Hub Hash Keys are not enforced.

Advanced forms don’t work:

• Multi-Active Satellites
• Links with peg-legged Hubs (e.g. sales order item id for which I don’t want to form a hub)
• Point-in-time (no template)
• Bridge (no template)

Meta Data

The application collects all the meta data necessary to implement an object. It is really an open application with nearly total freedom. You are absolutely free to implement bugs and stuff which doesn’t work.

At first, every team member evolved their own development style. Through discussions and documentation we agreed on some principles and layout of our objects. But things are getting forgotten or are not getting checked.

The need arose to write some QA queries to check for issues. This proved to be very unreliable as there are too many bits and pieces to configure for each object.

This is the downside of having total freedom. Other programs which are guiding you through the development steps might be easier and more consistent to work with.

Jobs

When developing, I create tables and stored procedure. Through lineage I see which object depend on others. This is really a great feature.

When adding a job based on my work, I click the needed objects together. There it should end. But… I have to group them by execution layer and setting the dependencies manually?!? By default it maps EVERY object from one layer to EVERY object in the next layer. Then I need to delete the obsolete mappings. I mean, the system knows the dependencies of each object. Why the hell I need to teach the program it again?!? I see the need of a manual overwrite. But the first draft should be done by itself.

Another issue is that I can set the dependencies on another job. This is great and runs well. But sometimes, especially when developing, I want to force start this job. If the dependent job didn’t run, this job will not run either. Either I delete the dependency or I add another copy of the same job without a dependency. The later we do currently.

Support

The support is based in New Zealand. If I have an issue which I want to show somebody, I will have to schedule here in Europe a slot in the evening to midnight.

Usually this is not necessary. The help provided is fast. For bugs and feature requests, you will receive a number and then the issue is forgotten. Sometimes you see according to the low number it is known for years. The development focus is on delivering more fancy features than ironing out old bugs.

In every release I get newly introduced bugs of stuff which worked before. Then I get an info that they will fix it for the next release. Release cycles are roughly 9 to 12 months.

Support for Pebble doesn’t exist. Somehow understandable, as this is an area for a lot of user questions and resources would be bound. And I believe they already experienced themselves, that Pebble is unpredictable and don’t want to waste time on it. Or maybe we are too small and they make rather big players happy. I don’t know.

Implementation

I envy those companies which are able to implement WhereScape in one week. The success stories of WhereScape are full of this. Maybe they sent a full team of success engineers in and get them to full speed or did that themselves.

Our success engineer was great. Helping us here and there with the applications default functionality. Maybe our „We can do whatever you want“ was too complicated. I can’t imagine how others are doing it. I mean, I have only 7 object types. Loading e.g. a Fact or a Data Store should be everywhere the same. My aim was to get standards implemented accurately, automatically and without too much manual work. And this has taken us a lot of time!

Final Words

I love WhereScape for their flexibility. I hate it because I have to (re-)invent everything. Their default is too „free“ and only basic. I love the drag & drop of columns. I love it for creating tables, views and stored procedures instantly. I can’t do without it anymore. I hate it for their limitations and bugs. I hate/love the job engine. I love it for the speed of deploying objects to production. I hate Pebble.

I love/hate WhereScape!

[Update 09.12.2019]

I see that this blog post is sometimes on the top of google search. In the meantime I have to rectify my view about WhereScape.

Some of the limitations above are still true. But overall I love WhereScape. With my perfectly tuned templates we are terribly fast in developing data products. The effect is, that we don’t maintain any release cycles anymore. Sure we can do that, but my users love the rather prompt help I can offer. Telling the issue, showing a draft, finishing up.

I can’t imagine to work with any box moving applications like SSIS. Rather write the query, select the template and it is done. And then going to the next issue.

I concluded that instead of having 7 templates (for each object type) 2 would be enough. One for stage and one for any target. They have all similar patterns.

While compiling all the patterns, I came to a solution that even 1 template would be enough. A universal loading pattern. Usable for any target type!

As we are loading data with stored procedures generated with WhereScape, see the following captions as sub-queries. In that way we add value in each layer to the result set. Also for maintaining template code this is much easier. SQL Server will find the best way to execute them.

I separated them into stage and target loading for easier reading. But in reality, it is just one big data flow.

Stage Loading Process

What would the loading pattern for a stage object look like?

From my first draft only little changed.

Extract & Transform

The only user generated logic is defined here. A simple SELECT statement and some additional meta data about what to save in addition, e.g. hash keys, change hashes or dimension keys. Everything afterwards is automated and for data preparation.

Delta Loading

[themify_box icon=“highlight“]Target Object Name is defined[/themify_box]

A stage table can optionally have a link to its target. With that in place delta loading is possible and will add the following WHERE clause.

WHERE dss_load_datetime > (SELECT COALSESCE(MAX(dss_load_datetime),'0001-01-01') FROM [Target])

CAST to Target Data Type

[themify_box icon=“highlight“]Has Hash Columns[/themify_box]

Hashing is a beast of itself. It looks trivial, but can still be tricky. Just recently we figured out, we would need to add another step in between: Casting to Target Data Type. E.g. the source column is of data type datetime and the target column has date. Of course, nobody does that ;-)! Hashing without casting would mean that it hashes e.g. ‚2018-03-08 12:13:14‘ instead of just the date ‚2018-03-08‘. Any rehashing later will fail. Of course, it should have been taken care about in the transformation, but other examples can be found. This step is just adding the invisible implicit transformation to a visual explicit transformation before the hashing step.

Another way to model it, is to create a temporary table with the same data types as the stage table and persist the result before going into the hashing function. This is anyway faster in delta loading because SQL Server comes usually up with odd execution plans.

Clean Business Key for Hash Keys (other Business Keys are not touched)

[themify_box icon=“highlight“]Has Hash Columns[/themify_box]

There are many options out there. I decided to go with the following:

CHAR Data Types	ISNULL(NULLIF(UPPER(LTRIM(RTRIM(source_column))),''),'-1')
INT Data Types	ISNULL(source_column,-1)
DATE Data Types	ISNULL(source_column,'0001-01-01')

If a CHAR column is empty I set it to NULL and every NULL column is set to -1. Then remove any spaces and set it to upper case.

If an INT column is empty set it to -1. 0 (=zero) is a good value. No need to NULL it.

Compute Hash Keys and Change Hash

[themify_box icon=“highlight“]Has Hash Columns[/themify_box]

Hash Key	HASHBYTES('md5',     CAST(column_1 AS NVARCHAR(MAX)) +'||'+     CAST(column_x AS NVARCHAR(MAX)) )
Change Hash	HASHBYTES('md5',     ISNULL(CAST(column_1 AS NVARCHAR(MAX)),'') +'||'+     ISNULL(CAST(column_x AS NVARCHAR(MAX)),'') )

Casting everything to NVARCHAR(MAX) is the easiest thing. NVARCHAR has the best implicit conversion from any data type to string. Don’t ever cast to VARCHAR(MAX) only. As long as there is no true UTF8 character set (with variable byte length) for VARCHAR, stick with UTF16 in NVARCHAR. Looking beautiful. Only DATETIME and another (which I can’t remember) are looking ugly. But as I use only DATETIME2 I don’t come across.

Dimension Lookup

[themify_box icon=“highlight“]Has Dimension Columns[/themify_box]

Just looking up the surrogate key.

Row Condensing

[themify_box icon=“highlight“]Business Key is defined[/themify_box]

Removing true duplicates. Be aware. REALLY! Wait until you hit a duplicate and talk to the owner of your source system! And only then implement it, if he can’t fix it.

Load

Preparing everything from the previous steps and sort the columns in the right order for the INSERT statement.

Post-Process Update Dimension Key

[themify_box icon=“highlight“]Has Dimension Columns[/themify_box]

If a business key in the stage table for a fact object doesn’t exist in the dimension we could add it if this is a desired outcome.

After INSERTing the missing business key into the dimension, we need to update the stage table.

Target Loading Process

The target loading can be as simple as having a short INSERT statement.

Which differences are in the target object types:

• Persist timeline calculation? (e.g. having EndDate in a table or calculate it through views)
• Update existing or insert as new row ?(e.g. Dimension vs Satellite)
• Is it allowed to insert „in-between“ data? (e.g. Satellite)

Row Condensing

Here happens the real magic, where every target type is different. Now is the right time to figure out, if we need to persist all the rows.

Target Type	Rule
Stage	We need only the last record by Business Key (if it is specified)
Data Store	We need only changes in rows sorted by LoadDate
Hub	We need only the first record by Hub Hash Key
Link	We need only the first record by Link Hash Key
Satellite	We need only changes in rows sorted by LoadDate and sorted by EventDate.
Dimension	Dimensions without SCD should be already unique in stage. No condensing.
Fact	It should be already unique by Business Key in stage. No condensing.

Compare last saved row with first new row

[themify_box icon=“highlight“]Has StartDate Column[/themify_box]

An additional step is to check the last saved row with the first new row. Happens only in tables with a timeline column. Usually Satellites or History.

The result after this step is, that the data set is now cleaned, useful, ready to be inserted in the target. From a data point of view.

History Columns

[themify_box icon=“highlight“]Has History Columns[/themify_box]

The history columns can be deferred to a view. Then we have an INSERT only system which can have a huge performance gain. The question is, if we can wait that long for the calculation of the timeline in the view to finish. If not, we should persist the history columns.

Column names for the first timeline. The calculation is based on the LoadDate.

• dss_start_datetime
• dss_end_datetime
• dss_version
• dss_current_flag

Column names for the second timeline: The calculation is based on the EventDate.

• dss_effectivity_datetime
• dss_expiry_datetime

For templates, not all columns are needed. Just add those which you need.

I know, this process fails if we are talking about bi-temporal objects. I need to learn first, how to build and use them.

Load

This step brings all the data from the previous steps together and adds the columns, which were missing in the extract layer.

Where (Not) Exists

This step was the most difficult one. Not because of detecting what exists and what not, but where to place?

If I put it after the extract layer, it would be impossible to do a full load. All existing records are removed from the data set and the history calculation is therefore rubbish. Also in delta load are problems. What about loading data in-between LoadDates? The whole row condensing step will produce wrong results.

What about placing it after the row condensing layer? Seems to me the best place. I could save time calculating the history layer as I’m not going to update an existing row anyway. But I would need to UPDATE the timeline after the INSERT. Any single UPDATE costs me 10 times more than a proper INSERT.

And where do we have history calculations anyway? Only with satellites and data store. Therefore, it can be placed at the last place possible.

What is new and what is existing? This are the rules for detecting it:

Target Type	Definition
Data Store	Business Key, Load Date and Sequence No
Hub	Hub Hash Key and Business Key (the unique constrain will tell if there is a hash collision)
Link	Link Hash Keys, Hub Hash Keys and peg-legged Business Keys (the unique constrain will tell if there is a hash collision)
Satellite	Hub Hash Key or Link Hash Key and LoadDate
Dimension	Business Key
Fact	Business Key

Which objects could have an UPDATE statement? Maybe not only in delta but also in full load?

Target Type	Definition
Data Store	No, there can't be any changes in the data. Otherwise the LoadDate is used wrongly.
Hub	No. If it is of interest, when a business key became available, we can update the LoadDate if there is an earlier one.
Link	No. If it is of interest, when a business key became available, we can update the LoadDate if there is an earlier one.
Satellite	No. If by accident there are different Change Hashes for a given record which already exists, the whole calculation in stage must have changed. Then the whole satellite should be reloaded instead.
Dimension	Yes!
Fact	Yes!

For Facts DELETEing the record could be a faster option than UPDATEing it.

Everything else is going to be INSERTed.

Post-Process Update Timeline

[themify_box icon=“highlight“]Has History Columns for a timeline (StartDate only is not a timeline)[/themify_box]

In that step we are going to update the timelines of our target. By joining to the stage table, we only need to update the changed timelines per Business Key/Driving Key.

In our thinking we had first also a Pre-Process Update step where we would outdate old data. This works, if we run the INSERT batch-by-batch. But as we don’t know, if there will be any inserts at all after the „Row Condensing“ layer we must omit that pre-processing step.

Conclusion

Above you see 2 images. Actually, in my data flow they are not separated. It is just a huge select statement incorporating every rule. Of course, empty layers are not shown.

Now, I can just throw any data for any target type at it. By designing it in layers, I’m flexible in adding new rules when I discover them. I’m also able to take out each layer in a separate temporary table to speed up processing time, if I figure out that this way would be faster.

By writing down my thinking gave clarity to the desired outcome. If you want to discuss this model or want to share your experience with me, please get in touch.

Maybe it is time now, to package them all up into an application, which is ready to be loaded into your environment.

You get templates for the following WhereScape objects

• Stage
• Stage Merge
• History (Data Store)
• Hub
• Link
• Satellite
• Dimension
• Fact

which you can create as

• TABLE
• VIEW

Included is also an extensive list of meta data checks. Modify it to your liking.

It is hopefully also a good resource if you want to learn writing templates for yourself.

Before using, please read the LICENSE and the README.

After that, your first stop is the template nu3_sqlserver_utility_configuration. I moved all „hard-written“ code into here for modification. Just go through it and modify it to your environment.

If you like it and the templates are helping you, visit the code sponsors website at http://www.nu3.com/ and buy some products.

In an ideal world, I would specify my objects and good is. All data would come into their place automatically. Unfortunately, that doesn’t happen too often.

Currently we create tables and according to the template produce stored procedures to load the data. Then we create jobs to process them. We would need to check that they are in the right order, that we didn’t forget a task and that we maintain the dependencies. A lot of auxiliary work.

To remove all this auxiliary work, we would need to specify our objects as views instead of tables. So far WhereScape doesn’t offer this choice. Even not in stage objects. Would that be really achievable?

In our templates, I have written a lot of code and tried to give „intelligence“ to it. What could I reuse to create views based on them? It’s the SELECT statement of the INSERT statement. No UPDATES and no delta loading. This pure SELECT statement I placed in a block template which can be reused in a procedure and in a DDL statement.

You will find now in my library all objects for

• Stage
• Stage Merge
• Hubs
• Links
• Satellites
• Dimensions
• Facts

which you can create now as

• TABLE (with a stored procedure) or
• VIEW.

Unfortuately, there is a little inconvenience with it. WhereScape warns currently when we want to recreate an object which has a DDL creating a VIEW. You would need to delete that manually from the database and recreate it with WhereScape.

Hopefully you have a machine which is powerful enough to calculate only views based on our history (DataStore) layer a.k.a. Persistent Staging Area.

In my case we have too much data to only work with views. What I do is using stage views as much as possible. Especially in multi-step stage preparation. No need to fiddle creating nesting jobs for them. And in loading business vault satellites I use stage views only. For the raw vault we would need a stage table to load hubs, links and satellites in parallel. But for all the cleaning and calculation while our only target is another satellite, there is no need to create a table for that. A view would be faster.

I reached now my main goal of having all objects created as tables or views. If you liked so far my contribution, please leave a message.

But how to ensure that things are done always the same way? And not only for me but also for my collegues?

We started to create meta views for objects, columns and indexes. On top of them we created „error views“ to list all issues which were important to us: e.g. all tables and indexes should be compressed, data type for hash columns should be binary, etc.

For some rules we implemented a metadata cleaning service which constantly scanned for issues and fixed them straight away.

But some issues were still views which we needed to check regularly.

Lately when I gave some thoughts to this issue, I came to the conclusion that this is not an efficiant way to do it. When we develop, I want to get feedback straight away. Not checking once a week or every day my error views for issues.

It is out of scope to believe, that we can persuade WhereScape to change their software to our way of thinking. There are too many different views from customers in the market.

How can we achieve to get some feedback in our work? Did we forget something? Do the objects have the correct Key Types? We can’t change the way, how the system works, but maybe we are able to pass some error messages into our output. And what is our main output? Tables with their corresponding stored procedures.

I created another section in our stored procedures, where we list all meta data checks. If the check is ok, I add the message as a comment. If there is an error, then without the comment dashes. In that way, the compilation of the stored procedure will fail and we can check the messages!

We created 2 templates:

• nu3_utility_metadata (for any platform-independent metadata checks)
• nu3_sqlserver_utility_metadata (for any SQL Server checks)

What could be platform-independent checks:

• A hub needs one hub hash key
• A link needs one link hash key
• A satellite needs either hub or link hash key (and only 1)
• A satellite needs a change hash key
• If the stage tables has hash keys, make sure that all columns belong to a hash key definition
• In a satellite, verify that all columns which are hashed in the change hash key on the stage table are also available in the satellite
• …

SQL Server checks could be:

• All hash columns should be of data type ‚binary‘
• Objects of type DataStore, Satellite, Link, Hub should have a Clustered Columnstore Index
• Any index of stage tables should have the feature ‚Drop Index before Table Update‘ enabled
• …

These are just some ideas.

I believe to include such things into the template, abuses the original idea of templating. But it helps to get the meta data quality straight.

First, do I set business keys or not? If I set business keys the whole loading procedure is based on a cursor which updates or inserts 1 record at a time. Not good if I’m going to load 100 mio. of records. This will take ages.

Nowadays more and more people like clustered columnstore indexes. Cursors don’t work with them.

Ok, no business keys. Then I’m able to tick the „Set Based Insert“ option. But with incremental loading I would need to delete or update updated records. So, I tick the „Include Delete Before Insert“. Now I’m able to select a column which matches stage and fact table. But only 1 column! This creates the following WHERE clause:

WHERE fact.column1 IN ( SELECT DISTINCT stage_fact.column1 FROM [TABLEOWNER].[stage_fact] stage_fact )

What about if I have multiple columns as a business key? By the way, we have already decided in the previous step that we don’t have business keys?!?

This was not satisfactory to work with.

What are the requirements:

Stage

In the stage table, we prepare the data set to be loaded. If we have dimensions with surrogate keys, we want to add a dim_key column to the stage table. This will look up the business key in the dimension table and returns the surrogate key. For business keys which don’t exist we want to add the unknown key.

Maybe we have late arriving dimensions or we are loading dimensions and facts in parallel. In that case, we don’t want to have the unknown key, but adding missing business keys to the dimension table. But for some dimensions I don’t want to get missing keys created (e.g. date dimension or dimensions with a fixed set of values).

Fact

Replace or insert data as fast as possible. Either use the dss_load_datetime or the dss_change_hash for delta detection.

Stored Procedure

Stage

There is one thing I want to point out from the standard stage template. How can we add missing business keys to a dimension?

WhereScape by default has another stored procedure to look up dimension keys and update it in the stage table. Again, with a cursor. 1-by-1. Unfortunately, we can’t create templates for that stored procedure.

Some when I figured out, that I could add another section in the stage template by inserting missing business keys into the dimensions. A really brilliant and fast solution.

I just INSERT a DISTINCT list of business keys for a dimension followed by an UPDATE statement. Both are set based. No cursor. Incredibly fast!

INSERT INTO [TABLEOWNER].[dim_product] WITH ( TABLOCKX )
(
       shop_code
      ,sku
      ,dss_load_datetime
      ,dss_create_datetime
      ,dss_update_datetime
)
SELECT DISTINCT
       shop_code
      ,sku
      ,'0001-01-01'
      ,@v_current_datetime
      ,@v_current_datetime
FROM   [TABLEOWNER].[stage_demo_fact]
WHERE  dim_product_key = 0

UPDATE [TABLEOWNER].[stage_demo_fact] WITH ( TABLOCKX )
SET    dim_product_key = dim_product.dim_product_key
FROM   [TABLEOWNER].[dim_product]
WHERE  stage_demo_fact.dim_product_key = 0
       AND dim_product.shop_code = stage_demo_fact.shop_code
       AND dim_product.sku = stage_demo_fact.sku

Fact

The following steps are defined:

• Delete existing records/Update changed records
• Insert new records
  • INSERT INTO Fact
  • SELECT Result
  • SUB-SELECT Where Not Exists
  • SUB-SELECT Extract from Stage Table

Delete existing records/Update changed records

If you have traditional tables, the template will create an UPDATE statement. If you are working with Clustered Columnstore Indexed tables then UPDATE has a really bad performance. Then DELETE and INSERT is much faster. With DELETE it sets only a marker on the record like is_deleted instead of replacing all the values. Please read the following blog entry for more background information.

Select Extract from Stage

Load the data from the stage table.

Where Not Exists

Load Data only if the fact doesn’t exist. Records that existed have already been updated or deleted.

WHERE NOT EXISTS (
        SELECT 1
        FROM   [TABLEOWNER].[demo_fact] demo_fact__not_exist
        WHERE  demo_fact__not_exist.sku = demo_fact.sku
               AND demo_fact__not_exist.date = demo_fact.date
)

Result

The resulting query looks like this:

INSERT INTO [TABLEOWNER].[demo_fact] WITH ( TABLOCKX )
(
        dim_product_key
       ,dim_date_key
       ,sku
       ,date
       ,purchase_cost
       ,dss_load_datetime
       ,dss_create_datetime
       ,dss_update_datetime
)
SELECT 
       demo_fact.dim_product_key AS dim_product_key 
      ,demo_fact.dim_date_key AS dim_date_key 
      ,demo_fact.sku AS sku 
      ,demo_fact.date AS date 
      ,demo_fact.purchase_cost AS purchase_cost 
      ,demo_fact.dss_load_datetime AS dss_load_datetime 
      ,@v_current_datetime AS dss_create_datetime 
      ,@v_current_datetime AS dss_update_datetime 
FROM   (
  SELECT demo_fact.*
  FROM   (
    SELECT 
           stage_demo_fact.dim_product_key AS dim_product_key 
          ,stage_demo_fact.dim_date_key AS dim_date_key 
          ,stage_demo_fact.sku AS sku 
          ,stage_demo_fact.date AS date 
          ,stage_demo_fact.purchase_cost AS purchase_cost 
          ,stage_demo_fact.dss_load_datetime AS dss_load_datetime 
    FROM [TABLEOWNER].[stage_demo_fact] stage_demo_fact
  ) AS demo_fact
  WHERE NOT EXISTS (
          SELECT 1
          FROM   [TABLEOWNER].[demo_fact] demo_fact__not_exist
          WHERE  demo_fact__not_exist.sku = demo_fact.sku
                 AND demo_fact__not_exist.date = demo_fact.date
  )
) AS demo_fact

Implementation

Download from the template section the following templates for WhereScape written in Pebble:

• nu3_sqlserver_procedure_fact
• nu3_sqlserver_block_fact
• nu3_sqlserver_procedure_stage (updated)

Select the template in the properties.

For any dimension, for which we don’t want to have missing business keys automatically created, modify the extended property on the dimension insert_missing_bk = false (see blog post).

That’s it.

What are the requirements:

Stage

Nothing special. Just make sure that the business key is unique.

Dimensions

This has taken me some time to figure out all rules how to build a dimension fast and properly.

The primary key of a dimension is the surrogate key. It is usually an integer identity column start counting from 1 upwards.

But when working with it, I found a lot of exceptions. Especially around zero keys. What is the zero key? It is the record used when no value is defined (aka NULL) in the fact table.

I got confused because the feature is called zero key. It implies that this record is always of the value 0. But in Data Vault, we define missing values as -1. Should I use here also -1 as unknown value? I decided to go with 0 because maybe I need to revert back to WhereScape standard functionality with SCD stuff and so on and WhereScape defines it as 0.

But then I hit dimensions where 0 couldn’t be the zero key. Then the argument came that we shouldn’t give meaning to the surrogate key. But people do. E.g. the key in the date dimension is usually in the form of yyyymmdd. And then I read that the zero key of the date dimension should be something in the future because otherwise the data would be in the same partition as old data.

This leads to the following requirements:

• I want to control the id generated. I need to map a column from stage to the dimension table
• I want to be able to set the zero key to a user-defined value
• I want to suppress the zero key generated
• I want to suppress that missing business keys are getting inserted by the fact table

For that to work I used the recently introduced feature of extended properties. I have the following extended properties defined on the dimension:

zero_key_id	Optional: Define another zero key value
insert_zero_key	Optional: Set it to false if no zero key should be added.
insert_missing_bk	Optional: Set it to false if no missing business keys from a fact table should be added

Stored Procedure

The following steps are defined:

• Insert Zero Key
  • SELECT Result
  • SUB-SELECT Where Not Exists
  • SUB-SELECT Zero Key Record
• Update existing records
• Insert new records
  • INSERT INTO Dimension
  • SELECT Result
  • SUB-SELECT Where Not Exists
  • SUB-SELECT Extract from Stage Table

Insert Zero Key

As already mentioned, I had some difficulties to understand, how to set up the zero key properly.

By default, WhereScape creates the artificial key, business key and dss_create_datetime and dss_update_datetime. Any other column for which you can define zero key values, are not considered. They are just NULL.

But I wanted to add dss_load_datetime as a system column. This is not supported.

For our cube, I don’t want to have NULL values. I came up with a view template, which replaces any NULL values from a dimension column based on the zero key value specified. For normal SQL queries on the table, NULL will still be shown.

Update existing records

The existing solution was just too slow. Comparing all columns for a change takes ages if the dimension table goes into the millions and having tons of columns.

We learnt already when loading data to a history table, that comparing only one column is much faster. The option here is to define a dss_change_hash. This will speed up the update process compared to default WhereScape behavior.

But there is something faster. Calculating dss_change_hash takes up also some processing time. With what can we replace it?

Until now we have taken a lot of energy to pump dss_load_datetime throughout the system. The idea is now, to just update columns if there is a change in dss_load_datetime. If there is a change let’s just update it. No need to compare if there is REALLY a change. This would kill the performance again.

How can we make sure, that there is always the most recent dss_load_datetime? Especially if we are joining data from dozens of satellites? I have modified the stage template slightly to calculate the most recent datetime by adding a transformation on dss_load_datetime.

(
  SELECT MAX(v) FROM (VALUES 
    (first.dss_load_datetime)
   ,(second.dss_load_datetime)
   ,(....dss_load_datetime)
  ) AS VALUE(v)
)

Is that not brilliant? In that way, we get always the most recent data. If somebody updates data on an existing record it will fail of course. But then he doesn’t do Data Vault.

Here the update query:

UPDATE [TABLEOWNER].[dim_demo] WITH ( TABLOCKX )
SET     column1 = stage_dim_demo.column1
       ,column... = stage_dim_demo.column...
       ,dss_load_datetime = stage_dim_demo.dss_load_datetime
       ,dss_update_datetime = @v_current_datetime
FROM [TABLEOWNER].[stage_dim_demo] stage_dim_demo
WHERE  dim_demo.shop_code = stage_dim_demo.shop_code
       AND dim_demo.sku = stage_dim_demo.sku
       AND dim_demo.dss_load_datetime <> stage_dim_demo.dss_load_datetime

Select Extract from Stage

Load the data from the stage table.

Where Not Exists

Load Data only if the dimension doesn’t exist. Records that does exist have already been updated.

WHERE NOT EXISTS (
        SELECT 1
        FROM   [TABLEOWNER].[dim_demo] dim_demo__not_exist
        WHERE  dim_demo__not_exist.shop_code = dim_demo.shop_code
)

Result

The resulting query looks like this:

INSERT INTO [TABLEOWNER].[dim_demo] WITH ( TABLOCKX )
(
        sku
       ,product_group_l1
       ,product_group_l2
       ,product_family
       ,dss_load_datetime
       ,dss_create_datetime
       ,dss_update_datetime
)
SELECT 
       dim_demo.sku AS sku 
      ,dim_demo.product_group_l1 AS product_group_l1 
      ,dim_demo.product_group_l2 AS product_group_l2 
      ,dim_demo.product_family AS product_family 
      ,dim_demo.dss_load_datetime AS dss_load_datetime 
      ,@v_current_datetime AS dss_create_datetime 
      ,@v_current_datetime AS dss_update_datetime 
FROM   (
  SELECT dim_demo.*
  FROM   (
    SELECT 
           stage_dim_demo.sku AS sku 
          ,stage_dim_demo.product_group_l1 AS product_group_l1 
          ,stage_dim_demo.product_group_l2 AS product_group_l2 
          ,stage_dim_demo.product_family AS product_family 
          ,stage_dim_demo.dss_load_datetime AS dss_load_datetime 
    FROM [TABLEOWNER].[stage_dim_demo] stage_dim_demo
  ) AS dim_demo
  WHERE NOT EXISTS (
          SELECT 1
          FROM   [TABLEOWNER].[dim_demo] dim_demo__not_exist
          WHERE  dim_demo__not_exist.shop_code = dim_demo.shop_code
                 AND dim_demo__not_exist.sku = dim_demo.sku
  )
) AS dim_demo

Implementation

Just download from the template section the following templates for WhereScape written in Pebble:

• nu3_sqlserver_procedure_dim
• nu3_sqlserver_block_dim

Select it from the properties. That’s it.

Before Data Vault 2.0 a hub or link surrogate key was an integer. Nowadays modern data vaulters would use hash keys instead to allow parallel loading. Others are going back to the roots and are using natural business keys. I dropped that idea because there was a reason to define something else instead. In our case it is about the handling of strings. If I want to be sure that something is unique, I would have to specify a binary collation for the business key column. Too much work and I wanted to have a properly working data warehouse first before going into experimental work with all the obstacles involved.

The following pictures shows the data flow for all data vault objects:

From the stage table, we load data to satellite, hubs and links. As you can imagine, there might be multiple satellites for 1 hub. Unfortunately, WhereScape doesn’t allow multi-to-one mapping. What exists is a kind of merge stage table.

This leaves us with the following data pipeline:

From the stage table (which loads also data to the satellite) we create a stage view with columns like they consist in the hub. Then we would add a stage merge to combine multiple views into one. The hub WOULD then load data from the stage merge (but doesn’t).

This pipeline is for lineage only. We can track columns and tables forth and back without breaking the lineage.

What we do is to „recycle“ the stored procedure which loads data from the merge stage table to the hub/link and replace the source table with the stage view.

In that way, we can load anything in parallel.

What are the requirements:

Stage

For the hub or link we need to create a hash key. The hash key for a hub can be calculated from multiple business keys. And the link hash key from multiple hub business keys. I add also business keys to the link which have no hubs. A peg-legged hub.

That’s it what we need from the stage table.

Hub

The primary key of the hub is the hash key. The business keys form a unique constrain.

Link

The primary key of the link is the hash key. The hub hash keys and added peg-legged hub columns should form a unique constrain. Unfortunately, WhereScape doesn’t support that yet. This is caused by the key type. Usually the key type ‚A‘ is the business key. Hub columns have the key type ‚h‘, so we can’t assign ‚A‘ to it. Maybe they come up with a solution some when.

Stored Procedure

Before going into the detail, I want to stress one major difference to other templates I have seen. Usually they insert new records where the business keys doesn’t exist. I changed it to insert new records where the hash key don’t exist. Why so? First, the hash key is the primary key and has usually an index on it. Much faster to search in. If I would scan for the business keys they can consist of multiple columns which is slower again.

The main objective against using business keys is, that SQL Server might not find a difference in the value due to the collation used. You would have to set the business key to a binary collation to capture every change. Without a binary collation, it would lead to satellite records not having a hub record and therefore a broken foreign-key relationship. Happened to us.

What about hash key collisions? A lot is talked about. To get these collisions, I check for the existence of both, hash key and business keys. The unique constrain on the hub or link (when it gets supported) will give us a crash, if I get duplicate records.

What are the steps:

• Insert new records
  • INSERT INTO Hub
  • SELECT Result
  • SUB-SELECT Row Condensing
  • SUB-SELECT Where Not Exists
  • SUB-SELECT Extract from Stage Table

Extract from Stage Table

Get the data from the stage table.

Where Not Exists

We want to load only new records.

SELECT *
FROM (sub_query) AS hub
WHERE NOT EXISTS (
  SELECT 1
  FROM   hub AS hub__not_exist
  WHERE  <hub/link_key> = <hub/link_key>
         AND <business key columns> = <business key columns>

If you want to get faster performance and remove collisions, delete the check for <business key columns>.

Very easy to make sure that it crashes when you hit one. Tell me about it, when you have one (really in your work, not by googling for a collision). Maybe I’m dead until this happens.

Row Condensing

It is possible, that we get multiple rows for a hub, but having many different dss_load_datetime values. We want to get the first used.

We must make also sure that we don’t suppress hash collisions. Meaning we could have same hash key for different business keys.

ROW_NUMBER() OVER (PARTITION BY <hub/link_hash_key>, <business key columns> ORDER BY dss_load_datetime ASC AS dss_row_no

followed by

WHERE dss_row_no = 1

Result

As we load data from stage views instead of the merge stage, we need to replace the merge stage table name with the stage view name.

The result looks like this:

SET @v_source_table = CASE WHEN @p_task_name = 'hub_demo' THEN 'hub_demo' ELSE @p_task_name END
SET @v_sql = N'
INSERT INTO [TABLEOWNER].[hub_demo] WITH ( TABLOCKX )
(
       hub_product_key
      ,shop_code
      ,sku
      ,dss_load_datetime
      ,dss_record_source
      ,dss_create_datetime
)
SELECT 
       hub_demo.hub_product_key AS hub_product_key 
      ,hub_demo.shop_code AS shop_code 
      ,hub_demo.sku AS sku 
      ,hub_demo.dss_load_datetime AS dss_load_datetime 
      ,hub_demo.dss_record_source AS dss_record_source 
      ,@v_current_datetime AS dss_create_datetime 
FROM   (
  SELECT *
        ,ROW_NUMBER() OVER (PARTITION BY hub_product_key , shop_code , sku ORDER BY dss_load_datetime ASC AS dss_row_no
  FROM   (
    SELECT *
    FROM   (
      SELECT 
             stage_hub_demo.hub_product_key AS hub_product_key 
            ,stage_hub_demo.shop_code AS shop_code 
            ,stage_hub_demo.sku AS sku 
            ,stage_hub_demo.dss_load_datetime AS dss_load_datetime 
            ,stage_hub_demo.dss_record_source AS dss_record_source 
      FROM [TABLEOWNER].[stage_hub_demo] stage_hub_demo
    ) AS hub_demo
    WHERE NOT EXISTS (
            SELECT 1
            FROM   [TABLEOWNER].[hub_demo] hub_demo__not_exist
            WHERE  hub_demo__not_exist.hub_product_key = hub_demo.hub_product_key
    )
  ) AS hub_demo
) AS hub_demo
WHERE dss_row_no = 1
'
SET @v_sql = REPLACE(@v_sql,'@v_current_datetime','CAST(''' + CAST(@v_current_datetime AS NVARCHAR) + ''' AS DATETIME2(7))')
SET @v_sql = REPLACE(@v_sql,'stage_hub_demo',@v_source_table)

Implementation

Download from the template section the following templates for WhereScape written in Pebble:

• nu3_sqlserver_procedure_hub_link
• nu3_sqlserver_block_select_hub_link
• nu3_sqlserver_ddl_view_stage (if you want to configure stage objects as stage views)

For the hub just select the template. This will generate the stored procedure to load data from the merge stage table.

Now comes the tricky (or dirty) part. Open the properties of the stage view and select this generated stored procedure in the „Custom Procedure“ option field. With the replace function in place, we can call now on the stage view „Execute Custom Procedure“ and it will load from the view instead of the default merge stage object.

That’s it.

• Hub
• Link
• Satellite

I’m not going to discuss what Data Vault is. There are lots of resources in the Internet from people who are much smarter in teaching what Data Vault is than I am.

How does a loading procedure look like in WhereScape?

In this post, I’m going to talk about loading to a satellite. The next one will be about hubs and links.

First, a satellite is a system of record like a history table. The difference is that the data is collected around a HashKey of a hub or link (which represent business keys of a business concept) instead of the business key from the source table.

What are the requirements to load data into a satellite?

Stage

The creation of the stage table is discussed in-depth in another blog post. The main goal is to prepare the date to be loaded to a satellite.

Data from a history table is loaded first to a Raw Vault Satellite. There should be no transformations except hard business rules like data type changes.

If we need to clean the data, do some calculations or derive values from, we load data from existing satellites into a Business Vault satellite. Do whatever you want in the stage table.

After the data set is like we want it to be, we need to define the  hub/link_keys and the dss_change_hash. The cleaning of the business keys for the hub or link hash key is taken care of by the template.

Until the history level we had the following system columns:

• dss_load_datetime
• dss_sequence_no
• dss_record_source

For satellite loading we are dropping the dss_sequence_no. For the first load from a history table we need to do also a little transformation by using the dss_sequence_no to make sure that we reduce the chance of hitting a duplicate row. No need to remove any rows from the source. Take them all!

DATEADD( NS , 100 * dss_sequence_no , dss_load_datetime )

Satellite

A table of records for a given HashKey. The Primary Key of a satellite is:

• hub/link_key
• dss_start_datetime

For a multi-active satellite add another business key to the table. Refrain as much as possible of doing that. As WhereScape doesn’t support multi-active satellite yet, I was forced to do it differently and this made my life easier by accident.

The dss_start_datetime is loaded by dss_load_datetime.

As I have an insert only system we move the calculation of the dss_end_datetime further down to a current or timeline view. All our satellites are Clustered Columnstored Indexed tables. Great performance for INSERT and SELECT.

View

To make the satellite more readable and not to write too many WHERE clauses in other objects we need to create 2 views:

• Current
• Timeline

The current view retrieves the last valid record for a hub/link_key while the timeline gives back a whole timeline. Currently I’m not sure what the first datetime of the timeline should be: '0001-01-01' or the first dss_start_datetime? The templates support both options.

Stored Procedure

Which steps do I take to load data from a stage table to a satellite:

• Insert new records
  • INSERT INTO Satellite
  • SELECT Result
  • SUB-SELECT History Columns
  • SUB-SELECT Row Condensing
  • SUB-SELECT Where Not Exists
  • SUB-SELECT Extract from Stage Table

Extract from Stage Table

All the cleaning and transforming is done in stage. Now it’s time to load new data.

Where Not Exists

We have now 2 options. If you have a dss_version column we must make sure that the order of records inserted is correct. No old records are allowed.

If you delete this column like me, loading old and new data is possible.

SELECT *
FROM (sub_query) AS sat_demo
WHERE NOT EXISTS (
  SELECT 1
  FROM   sat_demo AS sat_demo__not_exist
  WHERE  <business key columns> = <business key columns>
         AND dss_load_datetime = dss_load_datetime

Row Condensing

This process is derived of the great work from Roeland Vos on this topic.

We could save all records in a satellite. But that would be a waste of space. So, we partition the data by hub/link_keys and order it by dss_load_datetime. What can we check the data for?

• Is there a difference between dss_change_hash from one row to the other?
• Is the dss_load_datetime different from one row to the other? This is a hard rule. We circumvent a Primary Key constrain warning by eliminating duplicates which is dangerous. BUT… we have already taken care of. We added in the stage table loading process a little time variance. The chance of hitting a duplicate now is nearly zero.

Following the snippets to calculate both:

CASE WHEN LAG( dss_change_hash , 1 , CONVERT(BINARY(16),'') ) OVER ( PARTITION BY hub_product_key ORDER BY dss_load_datetime ) = dss_change_hash THEN 0
     ELSE 1
END AS is_hash_change

CASE WHEN LAG( dss_load_datetime , 1 , '0001-01-01' ) OVER ( PARTITION BY hub_product_key ORDER BY dss_load_datetime ) = dss_load_datetime THEN 0
     ELSE 1
END AS is_load_datetime_change

History Column

The template offers currently the following history columns:

• dss_start_datetime
• dss_version

The dss_start_datetime is taken from dss_load_datetime.

If the first dss_start_datetime should be '0001-01-01' or not is decided in the timeline view.

Result

Here is the result including the dss_version column:

SELECT 
       sat_demo.hub_product_key AS hub_product_key 
      ,sat_demo.displayable_quantity AS displayable_quantity 
      ,sat_demo.dss_event_datetime AS dss_event_datetime 
      ,sat_demo.dss_load_datetime AS dss_load_datetime 
      ,sat_demo.dss_record_source AS dss_record_source 
      ,sat_demo.dss_change_hash AS dss_change_hash 
      ,sat_demo.dss_start_datetime AS dss_start_datetime 
      ,sat_demo.dss_version AS dss_version 
      ,@v_current_datetime AS dss_create_datetime 
FROM   (
  SELECT sat_demo.*
        ,dss_load_datetime AS dss_start_datetime
        ,CASE WHEN current_rows.hub_product_key IS NULL
              THEN 1
              ELSE current_rows.dss_version + dss_row_no
         END AS dss_version
  FROM   (
    SELECT sat_demo.*
          ,ROW_NUMBER() OVER ( PARTITION BY hub_product_key ORDER BY dss_load_datetime ) AS dss_row_no
    FROM   (
      SELECT sat_demo.*
            ,CASE WHEN LAG( dss_change_hash , 1 , CONVERT(BINARY(16),'') ) OVER ( PARTITION BY hub_product_key ORDER BY dss_load_datetime ) = dss_change_hash THEN 0
                  ELSE 1
             END AS is_hash_change
            ,CASE WHEN LAG( dss_load_datetime , 1 , '0001-01-01' ) OVER ( PARTITION BY hub_product_key ORDER BY dss_load_datetime ) = dss_load_datetime THEN 0
                  ELSE 1
             END AS is_load_datetime_change
      FROM   (
        SELECT sat_demo.*
        FROM   (
          SELECT 
                 stage_sat_demo.hub_product_key AS hub_product_key 
                ,stage_sat_demo.displayable_quantity AS displayable_quantity 
                ,stage_sat_demo.dss_event_datetime AS dss_event_datetime 
                ,stage_sat_demo.dss_load_datetime AS dss_load_datetime 
                ,stage_sat_demo.dss_record_source AS dss_record_source 
                ,stage_sat_demo.dss_change_hash AS dss_change_hash 
          FROM [TABLEOWNER].[stage_sat_demo] stage_sat_demo
        ) AS sat_demo
        WHERE NOT EXISTS (
                SELECT 1
                FROM   [TABLEOWNER].[sat_demo] sat_demo__not_exist
                WHERE  sat_demo__not_exist.hub_product_key = sat_demo.hub_product_key
                       AND sat_demo__not_exist.dss_load_datetime < sat_demo.dss_load_datetime
        )
      ) AS sat_demo
    ) AS sat_demo
    WHERE  is_hash_change = 1
    AND    is_load_datetime_change = 1
  ) AS sat_demo
  LEFT OUTER JOIN (
          SELECT
                 sat_demo__current.hub_product_key AS hub_product_key
                ,MAX(sat_demo__current.dss_start_datetime) AS dss_start_datetime
                ,MAX(sat_demo__current.dss_version) AS dss_version
          FROM   [TABLEOWNER].[sat_demo] sat_demo__current
          GROUP BY
                 sat_demo__current.hub_product_key
  ) AS current_rows
      ON  current_rows.hub_product_key = sat_demo.hub_product_key
) AS sat_demo

See the sql example in full here.

Views

Current View

To identify the most current record we must find out the most recent dss_start_datetime for a hub/link_key.

SELECT *
FROM  sat_demo
INNER JOIN (
          SELECT
                 hub_product_key AS hub_product_key
                ,MAX(dss_start_datetime) AS dss_start_datetime
          FROM   [sat_demo] AS sat_demo
          GROUP BY
                 hub_product_key
  ) AS current_rows
      ON  current_rows.hub_product_key = sat.hub_product_key
      AND current_rows.dss_start_datetime = sat.dss_start_datetime

 Timeline View

Currently there are 4 columns supported:

• dss_start_datetime
• dss_end_datetime
• dss_effectivity_datetime
• dss_expiry_datetime

Currently I’m not sure what will the first dss_start_datetime be. I added an extended property to define it. It will calculate the first dss_start_datetime with the following query:

CASE WHEN LAG(dss_start_datetime,1,NULL) OVER (PARTITION BY hub/link_key ORDER BY dss_start_datetime) IS NULL THEN '0001-01-01 ELSE dss_start_datetime END

Add dss_end_datetime as a column without a source column definition.

The template will calculate it with the following query:

LEAD(DATEADD(ns,-100,dss_start_datetime),1,'9999-12-31') OVER ( PARTITION BY hub/link_key ORDER BY dss_start_datetime )

The other 2 columns exist, but are not perfect yet. I need to learn more about bi-temporal stuff to get it right.

Implementation

Download from the template section the following templates for WhereScape written in Pebble:

• nu3_sqlserver_procedure_sat
• nu3_sqlserver_block_select_sat
• nu3_sqlserver_ddl_view_sat_current
• nu3_sqlserver_ddl_view_sat_timeline

There is not much to do to set it up. Just select the template. That’s it.

People in the Data Vault Space would probably suggest creating a Source Vault if I don’t want to build a proper Raw Vault with good Business Keys. I considered that option too. I would have probably built it also that way. But I thought, if WhereScape has a history layer already built in, why not using it? And I used that kind of history already in the past.

So, how do I ingest data to the history layer?

What are the requirements:

Load

We have any kind of source type and source system. Loading the external data to the landing table is the first step. Unmodified, as original as possible.

To the loaded data, 3 system columns are added:

• dss_load_datetime
• dss_sequence_no
• dss_record_source

What is the dss_load_datetime? It is the datetime of that instance when the batch is inserted into the data warehouse. I give the whole batch the same datetime although it might load for minutes. Very important, it is not the extraction date. It is rather the insert date. See it as a human readable rowversion. In my language, it is also a batch. And there can be many batches in the load table. E.g. looping through files or looping through similar databases having the same data structure.

Each row in a batch is sequenced. I have seen that in the book from Dan Linstedt and in other designs. He doesn’t persist it, as it is not needed in satellites. But as I have a history layer I want to keep it for later use in satellites (I describe it later). If I can’t set e.g. the dss_sequence_no right in the load table, I add it in the stage step. Having it auto-increment in the load table is not possible, because I can have multiple batches.

The dss_record_source belongs to the Data Vault Standard. Just add it the way you want. We have the following rules:

• {source_name}.{source_database}.{source_schema}.{source_table}
• {source_name}.api.{api}
• {filename} (which contains the subdirectory structure including the source_name)

Stage

The creation of the stage table is discussed in-depth in another blog post.

The goal is to add any missing system column which are not yet defined in the load table and adding a hash value for dss_change_hash.

If you have many batches, you can speed up the next step by adding a non-clustered index on dss_load_datetime.

History

A system of records for a given business key at a specific time. Only changes from one batch to another are persisted. How do we identify a batch? It’s the dss_load_datetime and this is always counting towards the future. Only in the first load you can load historical data. After that only new data arrives.

Persisting data over time needs at least the following system columns

• dss_start_datetime
• dss_end_datetime

WhereScape has also these other system columns on offer:

• dss_current_flag
• dss_version

Someone might think that the dss_start_datetime is the same as dss_load_datetime. I thought about that at the beginning too. But I had troubles later joining different datasets together.

I concluded that I start with the first batch at 0001-01-01 and having an end date of 9999-12-31. With the 2nd historization and following, dss_load_datetime matches dss_start_datetime. I still have the dss_load_datetime in my history table. This is used throughout the system.

Views

To have a current view could speed up the development later. No need to write WHERE clauses you might forget. And you can create in WhereScape an alias to another database, e.g. raw_mart.

The timeline is not needed as an extra view. It is already inside the history table. But to be consistent with later stuff, I added it here.

The error view is a nice add-on if you load data from users which tend to write anything into Excel and you want to be sure that it matches the later process. It doesn’t prevent (aka breaking the load), but informs you.

Stored Procedure

Which steps do I take:

• Create Cursor from dss_load_datetime
• Update expiring records
• Insert new records
  • INSERT INTO History Table
  • SELECT Result
  • SUB-SELECT History Columns
  • SUB-SELECT Where Not Exists
  • SUB-SELECT Extract from Stage Table

Cursor

First, extract the last saved dss_load_datetime from the history table.

The cursor consists of a distinct and sorted list of dss_load_datetime which are equal or greater than the last dss_load_datetime. Why equal and not just greater than? If something breaks, I want to start over where it ended. And if it just takes another second to check, it is a safety check and no duplicate data is inserted.

Especially while migrating old data with thousands of batches, sometimes loading breaks. So, it is a good way to restart where it ended and not just do all over again. This feature is not available by default in WhereScape, so I used views before templates existed to just load not yet historized data.

Update expiring records

It sets for the expiring record a dss_end_datetime as 100 nanoseconds before the next dss_start_datetime. In that way, we can use BETWEEN in SQL statements. There is no time tick available 2 times. I recently learned another view on that topic from Dirk Lerner, but this is for another blog post. This is currently our rule set.

Extract from Stage Table

All the cleaning and transforming is done in stage. Now it’s time to load new data.

WHERE dss_load_datetime = cursor

We don’t clean for duplicates. The indexes will make sure that it will crash if you hit one.

Where Not Exists

To find out if a record has changed we need to get the latest record from the history table and check if the dss_change_hash differs.

SELECT *
FROM (sub_query) AS history_table
WHERE NOT EXISTS (
  SELECT 1
  FROM   history_table AS history_table_not_exist
  WHERE  <business key columns> = <business key columns>
         AND dss_change_hash = dss_change_hash
         AND dss_current_flag = 'Y'
)

History Columns

WhereScape has 4 history columns on offer. If you don’t need dss_version or dss_current_flag, delete them.

dss_start_datetime	CASE WHEN current_rows.shop_code IS NULL THEN CAST('0001-01-01' AS DATETIME2(7)) ELSE hist_demo.dss_load_datetime END
dss_end_datetime	CAST('9999-12-31' AS DATETIME2(7))
dss_current_flag	'Y'
dss_version	CASE WHEN current_rows.shop_code IS NULL THEN 1 ELSE current_rows.dss_version + 1 END

To get the last version we need to add a LEFT OUTER JOIN to extract the maximum version for a business key. See below.

Add-On

There is also a performance booster available if you insert history data with 1000s of batches. I experienced, when I refresh the indexes every 100 batches the insert performance is accelerated. But only if the table is not too big. Delete it or use it.

Future

Instead of looping through a cursor I want to load it in one go. I didn’t had time yet to explore and define a template, so you will have to wait. And it needs to be bullet-proof, because the history layer is the foundation for everything afterwards.

Result

Here is the query to UPDATE:

UPDATE [TABLEOWNER].[hist_demo] WITH ( TABLOCKX )
SET    dss_end_datetime = DATEADD(NS,-100,stage_hist_demo.dss_load_datetime)
      ,dss_current_flag = 'N'
      ,dss_update_datetime = @v_current_datetime
FROM [TABLEOWNER].[stage_hist_demo] stage_hist_demo
WHERE  stage_hist_demo.dss_load_datetime = @v_batch_start
       AND hist_demo.dss_current_flag = 'Y'
       AND hist_demo.shop_code = stage_hist_demo.shop_code
       AND hist_demo.sid = stage_hist_demo.sid
       AND hist_demo.request_id = stage_hist_demo.request_id
       AND (
              hist_demo.dss_change_hash <> stage_hist_demo.dss_change_hash
           )

Here is the query to INSERT:

INSERT INTO [TABLEOWNER].[hist_demo] WITH ( TABLOCKX )
(
       shop_code
      ,sid
      ,request_id
      ,times
      ,order_id
      ,total_price
      ,dss_load_datetime
      ,dss_sequence_no
      ,dss_record_source
      ,dss_change_hash
      ,dss_start_datetime
      ,dss_end_datetime
      ,dss_current_flag
      ,dss_version
      ,dss_create_datetime
      ,dss_update_datetime
)
SELECT 
       hist_demo.shop_code AS shop_code 
      ,hist_demo.sid AS sid 
      ,hist_demo.request_id AS request_id 
      ,hist_demo.times AS times 
      ,hist_demo.order_id AS order_id 
      ,hist_demo.total_price AS total_price 
      ,hist_demo.dss_load_datetime AS dss_load_datetime 
      ,hist_demo.dss_sequence_no AS dss_sequence_no 
      ,hist_demo.dss_record_source AS dss_record_source 
      ,hist_demo.dss_change_hash AS dss_change_hash 
      ,hist_demo.dss_start_datetime AS dss_start_datetime 
      ,hist_demo.dss_end_datetime AS dss_end_datetime 
      ,hist_demo.dss_current_flag AS dss_current_flag 
      ,hist_demo.dss_version AS dss_version 
      ,@v_current_datetime AS dss_create_datetime 
      ,@v_current_datetime AS dss_update_datetime 
FROM   (
  SELECT hist_demo.*
        ,CASE WHEN current_rows.shop_code IS NULL
              THEN CAST('0001-01-01' AS DATETIME2(7))
              ELSE hist_demo.dss_load_datetime
         END AS dss_start_datetime 
        ,CAST('9999-12-31' AS DATETIME2(7)) AS dss_end_datetime 
        ,'Y' AS dss_current_flag 
        ,CASE WHEN current_rows.shop_code IS NULL
              THEN 1
              ELSE current_rows.dss_version + 1
         END AS dss_version 
  FROM   (
    SELECT hist_demo.*
    FROM   (
      SELECT 
             stage_hist_demo.shop_code AS shop_code 
            ,stage_hist_demo.sid AS sid 
            ,stage_hist_demo.request_id AS request_id 
            ,stage_hist_demo.times AS times 
            ,stage_hist_demo.order_id AS order_id 
            ,stage_hist_demo.total_price AS total_price 
            ,stage_hist_demo.dss_load_datetime AS dss_load_datetime 
            ,stage_hist_demo.dss_sequence_no AS dss_sequence_no 
            ,stage_hist_demo.dss_record_source AS dss_record_source 
            ,stage_hist_demo.dss_change_hash AS dss_change_hash 
      FROM [TABLEOWNER].[stage_hist_demo] stage_hist_demo
      WHERE  stage_hist_demo.dss_load_datetime = @v_batch_start
    ) AS hist_demo
    WHERE NOT EXISTS (
            SELECT 1
            FROM   [TABLEOWNER].[hist_demo] hist_demo__not_exist
            WHERE  hist_demo__not_exist.shop_code = hist_demo.shop_code
                   AND hist_demo__not_exist.sid = hist_demo.sid
                   AND hist_demo__not_exist.request_id = hist_demo.request_id
                   AND hist_demo__not_exist.dss_change_hash = hist_demo.dss_change_hash
                   AND hist_demo__not_exist.dss_current_flag = 'Y'
    )
  ) AS hist_demo
  LEFT OUTER JOIN (
          SELECT
                 hist_demo__current.shop_code AS shop_code
                ,hist_demo__current.sid AS sid
                ,hist_demo__current.request_id AS request_id
                ,MAX(hist_demo__current.dss_version) AS dss_version
          FROM  [TABLEOWNER].[hist_demo] hist_demo__current
          GROUP BY
                 hist_demo__current.shop_code
                ,hist_demo__current.sid
                ,hist_demo__current.request_id
  ) AS current_rows
      ON  current_rows.shop_code = hist_demo.shop_code
      AND current_rows.sid = hist_demo.sid
      AND current_rows.request_id = hist_demo.request_id
) AS hist_demo

Of course, it would be possible to replace both steps with a MERGE statement. My experience with that is not the best. With huge tables or data sets it just takes time and sometimes SQL Server „got irritated“ about what to do.

Current View

Often, we use only the current valid record. Instead of writing everywhere WHERE clauses, I just create a view from the history table and select the appropriate template. That’s it. What will it do?

It adds a simple WHERE dss_current_flag = 'Y'. Not that difficult. But it will get more complex when we are coming to Satellites and Link Satellites.

If this view is business critical, I added also a template for an indexed view.

Implementation

Download from the template section the following templates for WhereScape written in Pebble:

• nu3_sqlserver_procedure_hist
• nu3_sqlserver_view_hist_current

To set up:

• Select the template „nu3_sqlserver_procedure_history“
• Set the Business Key Columns
• Set the Change Detection Fields to „dss_change_hash“

That’s it.