Poor Man’s Auditability

One common question in Anchor Modeling is what to do with values that suddenly disappear. Our answer has been that values do not disappear, but may become unknown, and if such occurances are of interest then this fact should be modeled explicitly. The exception is when corrections are made, and if you need to keep track of them the solution is to use concurrent-reliance-temporal modeling. However, there may be scenarios in which you only want to keep some simple auditability of corrections (or some bastardization of disappearing values) while still remaining in unitemporal modeling. This is now possible in the test version of the online Anchor Modeler, thanks to the addition of deletability.

In order not to break compatibility with existing code, new columns have been added in the latest views for deletable attributes. It works as follows with the update trigger on the latest view for an attribute marked as deletable:

update lST_Stage
   ST_NAM_Stage_Name = null,
   ST_NAM_ChangedAt = '2018-05-04',
   Deletable_ST_NAM = 1
   ST_ID = 42;

Values can be deleted only when Deletable_ST_NAM is set to 1 in an update. Without that row, the update works exactly as before, which is to ignore anything being set to null. The result of the code above is that a new table is created, named ST_NAM_Stage_Name_Deleted, to which all matching rows are moved. Note that this clears out all history. The new table will have one additional column containing the date of the deletion, which is the current date if the attribute is static or no ST_NAM_ChangedAt is present in the update or whatever ST_NAM_ChangedAt is set to if it is present. This should give you a “poor man’s auditability” for disappearing values in unitemporal modeling.

Optimizing BETWEEN joins

Have you ever found yourself needing to do a join on a BETWEEN condition? If so, you have probably also been struggling with performance. The optimizer in SQL Server just cannot seem to produce a good execution plan for BETWEEN. Thanks to a lot of trial and error, we were however able to find a workaround!

This workaround, using a CROSS APPLY together with a SELECT TOP 1 … ORDER BY, should prove useful outside of the Anchor Modeling community as well. The example is a draw consisting of a bunch of floating point numbers, similar to the Fisher’s noncentral hypergeometric distribution, but this works similarly for date ranges.

Performance improvements

The code generated by the test version of the online modeler has had some modifications made in order to achieve better performing insert triggers and perspectives. Currently, these modifications have only been made for the unitemporal models and only affect the historized parts of the model. Thanks to the “Live Query Statistics” in SQL Server, a bottleneck with the scalar-valued functions finding previous and following values in the insert trigger was identified. This code has instead been inlined. When writing new BIML generation for the sisula ETL framework, an apparently faster way to find the latest version was found, using ROW_NUMBER and a “superfluous” GROUP BY to retain table elimination. This has only been tested on SQL Server 2016 so far, but should work since version 2008 and onwards. As a side note, the SSIS packages generated from the BIML are magnitudes faster than using the insert triggers, so we recommend using these for large volume data warehouses or when priming with historical data. Please test and report back, and if no issues are found this will go into the production version as a new release.

Co-locating in an MPP-RDBMS

The idea behind a Massively Parallel Processing Relational Database Management System is that each node that participates in the cluster should be as autonomous as possible. We have earlier shown that using the available distribution techniques in for example HP Vertica, it is possible to co-locate an instance of an anchor and all of its attributes along with any knots they relate to and any ties the instance participates in. Recently, we were asked how to additionally co-locate closely related instances.

In a very simple thought experiment, this is actually not so difficult to do. Imagine a model of restaurants and their menus. It is reasonable that in many cases an instance of a resturant and its related menu-instances would be part of the result set. If we can co-locate these instances on the same node, the node will become even more autonomous. However, using a simple modular hash on the identities of restaurants and menus will distribute them evenly across the nodes, but for the most part not keep related instances together.

Let us look at a possible solution using some pseudo code.

Let 3 be the number of nodes in the cluster.
Let s_resturant be a sequence starting with 1 incremented by 1.
Let s_menu0     be a sequence starting with 3 incremented by 3.
Let s_menu1     be a sequence starting with 1 incremented by 3.
Let s_menu2     be a sequence starting with 2 incremented by 3.
Let {1, 2, 3, 4, 5, 6} be ids of six restaurants.
Let id modulo 3 determine the node on which to locate an instance.
Then restaurants {3, 6} will be co-located on node 0.
Then restaurants {1, 4} will be co-located on node 1.
Then restaurants {2, 5} will be co-located on node 2.
Let s_menu0 generate ids for menus of restaurants on node 0.
Let s_menu1 generate ids for menus of restaurants on node 1.
Let s_menu2 generate ids for menus of restaurants on node 2.
Find the node of restaurant 4 through 4 modulo 3 = 1.
Use s_menu1 to create menus {1, 4, 7, 10} at restaurant 4.
Then menus {1, 4, 7, 10} will be co-located on node 1.

The drawback is that you will need as many sequences as you have nodes and the fact that the number of nodes may not stay the same over time. However, using similar logic it may be able to apply similar thinking to cover the redistribution of instances in the case of an added node in the cluster.

Anchor Modeling Academy

We will bringing some of our courses online, starting with an Introduction to Anchor Modeling. It is a four hour video course for $299 that will give you a general introduction to Anchor, serving as a good start to build from if you are interested in the technique. A link to the course can be found in the sidebar to the left, or you can click here: anchor.teachable.com. This will hopefully be an opportunity for those of you who have shown interest in our courses, but have been unable to travel to Sweden in order to take them. We’re open to suggestions on what you would like to see as a second course that builds upon this one, so let us know!


Anchor Modeler 0.99 Released

We are proud to announce the release of version 0.99 of the modeling tool. This version has been in the making for over one year, and code generated from it is, as usual, already in production for a number of data warehouses and system databases. Expect a more user friendly interface, that again works in Chrome, after they unexpectedly and deliberately broke SVG support. The generated code has, among other things, improved trigger logic. For example, if you try to update a static attribute, you will now get a warning, rather than a failure. This should make life easier, particularly for those using our sisula ETL Framework for metadata driven Data Warehouse automation. Using this framework with the latest modeling tool we have built and put a DW in production in record time. It took less than a week to model, populate three years of history, and start a daily incremental load of a Data Warehouse used in a high security environment.

Anchor Model Generator

A while back Juan-José van der Linden created a script that would reverse-engineer a database into an Anchor model. He was kind enough to donate that script to the community, and it is available in our forum. Now there’s also a second effort which is available in the form of the script below. This is a work in progress and it will be updated with more features in the future. Perhaps we can merge the best features from JJ’s script into this one or the other way around.

Please note that the script will use column statistics in order to determine if knots should be created, so it may take a long time to run when no statistics are available. It will reuse existing statistics, so a second run of the script is much faster. It tries to determine ties based on primary keys and matching column names.

The following script can be used to generate knot loading code, based on the data stored in the descriptions of the knots in the model after running the script above.

The following script can be used to generate source to target mappings for use with the sisula ETL framework, based on the data stored in the descriptions of the attributes in the model after running the script above.

24 New Certified Anchor Modelers

Last week we had the pleasure of certifying 24 new Anchor Modelers in the Netherlands. In other words, if you want to find expertise on Anchor modeling, you should head over to our Directory and contact them! We also held a public session with an introduction to Anchor modeling, showing them how it is possible to capture facts like “someone being somewhat sure about something, while someone else is completely sure about its opposite”. We were happy to have participants from Nike, the Dutch Police, Rabobank, Essent, ChainPoint, consultant companies like Free Frogs, Ordina, Cap Gemini, Kadenza, and a number of freelancers.

IMG_1031 IMG_1032

Thanks everyone for participating at the courses and presentations, and the great discussions during the breaks!

34th International Conference on Conceptual Modeling

We had a great time at the 34th International Conference on Conceptual Modeling, also known as ER2015, running a demo station of the online Anchor modeler, standing in for Hans Hultgren and presenting Ensemble Modeling, participating in a panel discussion on Big Data, and presenting our own paper “Big Data Normalization for Massively Parallel Processing Databases” at MoBiD.

Here are some photos of Nikolay Golov presenting our paper. He had a very interesting story to tell of how Anchor has helped Avito scale out their data warehouse as the both the business and requirements grew very rapidly, multiplying the number of sources, rows, and terabytes with a factor of ten over three years.


Implementing constraints

One common question I am asked is how constraints are implemented in an Anchor modeled database. Temporality make constraints less straightforward than in static tables. With unitemporal history the constraint needs to hold for every possible time slice in the database given every point in changing time (tc). If concurrency and bitemporal history is used, they need to hold for every possible time slice for every positor given every point in bitemporal time (p, tp, tc). The good thing is that a slice behaves just like a static database. It is a snapshot of what the information looked like at that point.

Implementing this using a CHECK constraint would be quite cumbersome. Even if you can limit the points of time that needs to be checked to those actually used in the database they quickly grow to a large number. The better way is to do the checking with an AFTER INSERT trigger. At insert time, it is possible to check only against those slices that are affected by the insert.

Below is a script that generates a unique constraint for the stage name in our example model. Note that time slice checking is done by applying the point-in-time perspective of the affected anchor. This “template” for constraints can of course be extended to include more complex rules, such that two performances cannot be held on the same stage on the same date, found further down in the same script.

Note that in the case of the performance date constraint, we do not have to take time into account, since none of the involved constructs are historized. However, there is one additional important consideration. Since the trigger joins the tie, the tie is expected to have its rows populated before the attributes. In other words, for the trigger to detect duplicates, the loading order must be:

  1. Generate the desired number of identities in the performance anchor.
  2. Populate the tie by connecting these identities to the associated stages.
  3. Populate the attributes on the performance anchor.

If the loading order cannot be guaranteed, a similar constraint must be placed on the tie. In that case, attributes will pass through its trigger if loaded first, since join with the tie “truncates” the result set, but the trigger on the tie will fail because of duplicates. A larger transaction containing the two steps would then still roll back to what the database looked like before the insert.