In order to ensure temporal entity integrity in a bitemporal model a few constraints have to be in place:

• Primary key constraint over (ID, ChangedAt, RecordedAt, ErasedAt) that ensures that no duplicate rows can exist.
• Unique constraint over (ErasedAt, ID, ChangedAt) that ensures that no temporal duplicates can co-exist for the same entity and version.
• Check constraint that ensures that RecordedAt < ErasedAt.
• Check constraint that ensures no overlaps in the recording timeline.

These have now been added to the modeling tool in the test version, and can optionally be switched off from the “Defaults” menu. Below is a script that tests the functionality of these constraints.

Borrowing concepts from Bitemporal Anchor Modeling we have created a suggestion for how to temporalize Dimensional Modeling. It makes use of the distinction between immutable and mutable information to introduce a dimensional anchor for every dimension. It uses the rewind and point-in-time parametrized views. It used the proper primary keys together with foreign keys to achieve table elimination (immutable data is not touched during execution of a query). It uses constraints to ensure temporal entity integrity.

Note that this does in no way imply that we favor Dimensional Modeling over Anchor Modeling. It just shows that the techniques developed in Anchor Modeling can be widely applied in other modeling techniques.

We have made our first generic skeleton model for retail businesses available. You can open it in the modeler by clicking here. Feedback and improvements are very welcome, either by sending them to us or by “forking” and publishing your own modified version of the model. It should also serve as a somewhat larger example model that we can and will use in presentations and courses.

We recently brought attention to the need for more complex bitemporal test data in the LinkedIn group Temporal Data. Craig Baumunk of temporaldata.com then made data following some of our suggestions available to the group, and we have now implemented that using Anchor Modeling. The model can be seen here:

http://www.anchormodeling.com/modeler/test/?id=ag1hbmNob3Jtb2RlbGVycg0LEgVNb2RlbBiCyhEM

In order to generate the bitemporal SQL code for the model, first set Temporalization to “Bitemporal” from the Defaults-menu. You will then get the first part of the script seen below. It is followed by a section in which Craig’s data is loaded into the model. Finally, scroll down to the bottom to see how easily bitemporal data can be queried and joined.

Bitemporal Anchor Modeling is now ready for testing in the latest test version. The last piece of the puzzle, bitemporal relationships (ties), have now been added as well. I believe there is little need for triggers on the ties. Let us know if you think otherwise. The following views should be available on all ties, where l = latest, p = point-in-time, d = difference, and the first position for changing (valid) time and the second for recording (transaction) time:

ll, lp, pl, pp, dl

The other combinations, such as ld, pd, etc, have been considered of less use and may be added at a later time. Once this version has been tested and deemed stable, it will be pushed to release status.

We have pushed a new version to test, in which the triggers on bitemporal attributes can be tested. If you generate the SQL code for our example model with Temporalization set to “Bitemporal” and no metadata, then you can use the script below to test the triggers.

The Swedish Computer Society together with the magazines Computer Sweden and Internet World will hold a one day conference on Big Data at the Museum of Modern Arts in Stockholm on April 26. We will be there presenting Anchor Modeling. You can find more information and sign up for the conference by clicking here.

Twitter has been integrated into the test version. Public models can thereby be tweeted from the public model browser. A sharable URL can also be generated for any model if you want to use other social media networks or just link to the models. Models can also be searched by keywords and are now sorted in descending popularity order. It is now also possible to add a description (1000 characters) to models.

Here is an example link to a model:

An example model

Clicking the link will start the modeler and load that specific model into the tool.

We have updated the Support page with more information aiding users to get the best performance out of Anchor Modeling. There are four key features of a database engine that help produce the best possible performance; table elimination, clustered indexes, foreign keys and statistics.

Read this in order to learn how:

• Indexes and statistics needs to be properly maintained as information is added.
• Adding information may be sped up by temporarily deferring indexes and keys.
• Full table elimination can only be achieved when foreign keys are declared and queries carefully designed.
• Fresh statistics help the query optimizer pick the optimal join order, starting with the smallest intermediate result set and continuing progressively with as few rows as possible through the joins.

The triggers have been rewritten and can now be used both in DW and OLTP environments. In order to show how they are used we made a very simple example showing some inserts, updates, and deletes. Updates are translated to inserts by the triggers. Similar triggers will be available in Bitemporal Anchor Modeling once it is finished, where also the deletes will result in inserts, instead of being actual deletes.

Note that the code above works with our standard example model. Just generate the SQL code from the example model in the tool (version 0.94 if you want the delete trigger) and run it in SQL Server and you will be able to test the code above. Using the triggers is optional and meant to be a way to simplify the way you use an anchor database. You can always use inserts directly to get the same behavior.