Online Modeler Updated to 0.95

The online modeler has been updated to version 0.95, which includes the following features and fixes:

NEW FEATURES

  • Full support for bitemporal modelling.
  • Latest views behave like tables thanks to insert, update, and delete triggers.
  • Restatement control for historized attributes.
  • Searching in models.
  • Optionally hidden mnemonics for less cluttering.
  • Optionally hidden attributes using implode/explode on models or parts of a model.
  • Metadata is now optional.
  • Improved layout for smaller screens.
  • Keyboard shortcuts.
  • Editing window can be closed by pressing ‘Return’.
  • First field in the editing window automatically gets focus.
  • Public models can be shared using a simple URL.
  • Twitter integration for public models.
  • Models can now be deleted from the cloud.
  • Models can now have a description.
  • Improved model browser for the cloud.
  • Straight edges can be set to improve performance on older hardware.
  • Capsules (schema owner) can be specified.

BUG FIXES

  • Added checks for mnemonic uniqueness.
  • Fixed the issue where historized ties could get an uneditable time range.
  • Fixed keyboard shortcuts from being active in all editable fields.
  • Fixed CR+LF problem in the generated SQL.
  • Ties will now always have an identifier.
  • Reduced the number of string objects created by the modeller.
  • Fixed bug where cardinalities were not shown in the SVG output.
  • Fixed missing items in the generated HTML documentation.
  • Changed the z-index of menus so they overlap gracefully.
  • Refactored global variables that were just global because of laziness.
  • Rewrote the delete operation, so that it will no longer go berserk.
  • Fixed mouse wheel zooming on Macs.
  • Fixed bug in imploded edges.
  • Fixed the issue where only 20 items from the cloud would be returned.
  • Removed recording time from knots.
  • Improved security in the cloud connection.
  • Fixed the isEmpty function for boolean values.
  • Fixed bug with empty WHERE clause for 1-1 ties.
  • Fixed select all (Ctrl+A) in the generated code window.
  • Fixed bug where default capsule would not affect new items.
  • Fixed bug where default identity would not affect new anchors.

IMPORTANT: The XML may be updated such that it is no longer backwards compatible. Please save and keep a copy of your model XML before migrating to a newer version, in case you need to go back. The old versions can be found by clicking here.

Temporal Entity Integrity

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.

Temporal Dimensional Modeling

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.

Retail Skeleton Model

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.

Complex Bitemporal Test

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:

https://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

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.

Using the Bitemporal Triggers

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 script tests the insert, update, and delete trigger on the latest view for ST_Stage. Insert and delete are quite straightforward in that they do what you expect, insert rows and logically delete rows. Update is slightly more complex with its behavior depending on what is updated. If only ST_NAM_Stage_Name is updated, then a new version is created. If both ST_NAM_Stage_Name and ST_NAM_ErasedAt are updated, then a correction is made by first logically deleting the old row then inserting a new one. If only ST_NAM_ErasedAt is updated, then only a logical delete is performed. These views and their functions should cover all possible use cases.

Twitter integration and more

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.

Four features for performance

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.