1. Getting Acquainted with the Modeling Tool
In this 4 minute video tutorial we get acquainted with the online modeling tool, looking at the example model, the control buttons, and the different menus.
2. Building Your first Model
In this 4 minute tutorial we build a small model of a restaurant business containing two anchors with one attribute each and a tie connecting the anchors. In business terminology, we need to keep track of our restaurants and their available dishes.
3. Knotting an Attribute
In this 2.5 minute tutorial we add a knotted attribute on the Restaurant anchor. In business terminology, we want to categorize the restaurants by their cuisine.
4. Historizing an Attribute
In this 2 minute tutorial we add a historized attribute on the Dish anchor. In business terminology, we need to store the current and historical prices of the dishes.
5. Adding a Transaction
In this 3 minute tutorial we model a transaction as an anchor and two ties. In business terminology, we need to keep track of what dishes were consumed at the restaurants, when they were consumed, and how much was consumed.
6. Historizing a Tie and Invalidating Instances
In this 3 minute tutorial we historize one of the existing ties in the model, as well as knot it in order to be able to invalidate instances of the tie. In business terminology, we want to be able to capture what dishes are served at the restaurants and additionally when they stopped being served.
7. Creating a Database Implementation
In this 2.5 minute tutorial we create a database implementation of the conceptual model from the previous tutorials. Thanks to the 1-1 mapping onto tables and automated rules for index, view, and function creation this can all be done in seconds.
8. Demonstrating Table Elimination
In this 3 minute tutorial we demonstrate a query optimization feature of modern database engines called table elimination. Thanks to the way indexing and foreign keys are created in the database table elimination is used to its fullest in the views and table valued functions, effectively raising the performance in anchor databases.
9. Evolving the Model
In this 2.5 minute tutorial we evolve an already existing model that has been in use for some time. Two new requirements result in a knotted attribute on an existing anchor and a new anchor, tie, and attribute. In business terminology, we now also need to know the type of dish, i.e. main course, dessert. It is now also possible to identify some of the guests and we need to know what and how much these have consumed.
10. Evolving the Database
In this 2 minute tutorial we propagate the changes made to the model in the previous tutorial into the database. As none of the existing tables are altered in any way, this can be done in seconds and without taking down the database for maintenance.
11. Using the Temporal Perspectives
In this 3.5 minute tutorial we look at the different temporal perspectives available in Anchor Modeling, i. e. the latest, point-in-time, and interval perspectives.
12. The Ghost in the Database
In this 4 minute tutorial we show how we can write a query that eliminates as many tables as possible. Surprisingly, also all anchors are eliminated from the query. In business terminology, we answer the question ‘What was the average consumption amount per dish between the years 2007 and 2009’
13. Inserting Data for an OLTP Environment
In this 5 minute tutorial we insert data into a knot table and into a latest view, using its automatically generated ‘instead of insert’ trigger. In business terminology, we create three types of dishes, ‘Entree’, ‘Main’ and ‘Dessert’. Then we create two dishes, ‘Rigatoni al Pollo’ and ‘Tiramisu’, part by part and all at once, respectively.
14. Inserting Data for a DW Environment
In this 4.5 minute tutorial we associate anchor identities with source data. These identities are either copied and flagged as previously known, or created and flagged as previously unknown. The creation of new identities is done using an automatically generated stored procedure. In business terminology, we prepare to load a number of new dishes along with new information for some existing ones.
15. Building an ETL Template
In this 3 minute tutorial we demonstrate the loading of the source data from the previous tutorial using an ETL tool. The structure of the involved tasks are the same for every anchor, making it possible to reuse and automate these.
16. The Absence of NULL
In this 2 minute tutorial we show how missing information is represented by the absence of rows in an anchor model, and not by NULL values. In business terminology, we add a new dish called ‘Profiterole’, of which we know the price but not (yet) its classification.
17. The Naming Convention
In this 5 minute tutorial we go through the naming convention enforced by the modeling tool. It is designed to yield syntactically unique names with some semantics in the name. From a name it is possible to deduce what type of object it is (anchor, attribute, tie, or knot) and for ties and attributes also to what they are connected. They also keep an anchor and its attributes close if sorted in a list.
18. Keyboard Shortcuts
In this 3 minute tutorial we demonstrate the keyboard shortcuts available in the modeling tool. Once you have become familiar with the tool you can build your models faster using the keyboard shortcuts for modeling operations, rather than clicking through the context sensitive menus.
19. The Layout Algorithm
In this 5 minute tutorial we discuss the layout algorithm used in the anchor modeler and some of the settings affecting it. The layout algorithm is an interactive force-directed graph algorithm in which we did away with the requirement to be physically accurate in order to do heavy performance optimizations, while still producing an attractive layout.
20. Schema Evolution Maintenance
In this 5 minute tutorial schema evolution is discussed. A row with the schema is inserted in the table _Schema every time a generated SQL script is run. The XML is then shredded into rows and columns in the views _Anchor, _Knot, _Attribute, and _Tie. These are used by the table valued function _Evolution(@timepoint), which compares the current database tables with the schema that was in effect at the given @timepoint.
Way cool! :^)
Very nice! It’s the first time I know about anchor modeling.
Educational and, as the anchor modeling approach and modeler, clean and simple.
I very much appreciate your generosity towards the data community in providing us with a modeling technique, a modeling tool, and the tutorials to quickly become productive with them.
I can see that a tremendous effort has been made to keep anchor modeling concise. I consider that to be a positive attribute in any approach. It is much easier to make it convoluted and complex than it is to keep something simple and nimble.
Kudos.
Hello,
This tool model and tool is amazing. I took the class about this model on KTH. Since we should provide feedback about this tool as part of our assingment I just realize that a it would be great to have a “find” feature in order to find anchors or nodes quickly. If the model is to big and as you know always change places is hard to find one specific anchor.
An excellent and clean approach. Some of the most useful ER techniques for BI have been formalised in this methodology. It is also comforting to know that this has been proven in the real world.
Hello!
I’ve noticed, right now, about Anchor from LinkedIn – Intelligence d’affaires Région de Québec, discussion group.
I would to say Thanks for allow us to learn this amazing modeling approach and I became a fan instantly.
An stupid question from a first time sailor: The generate SQL works out with Oracle and MySql databanks?
Sincerely yours
Eduardo
Unfortunately it’s only for Microsoft SQL Server right now, but Oracle is on its way. MySQL does not support table elimination, which makes it less useful for Anchor Modeling. However, the recent fork, MariaDB, which is feature compatible with MySQL does! It will also be supported.
Terrific resource! Thank you very much for your efforts. Will postgres be a supported platform?
Yes, it will be, but I can’t say exactly when.
Posted the same question the other day in the Forum section, before I saw Thomas’ question and your March 2013 response here in the Tutorial section. Any update on when Postgres support will be available?
Thanks!
Dave
There have been some efforts made on PostgreSQL, but none in the official codebase. We would welcome help in that area, since we have no previous experience with PostgreSQL. With the new sisula engine, porting to other SQL flavours should be a breeze, as long as you are an expert in the target flavour. So, if someone can help us, we could probably get this going rather quickly.
Very much interested in pursuing this approach and thank you for some obviously excellent thinking AND practical application work on this.
I too would need to be able to work with postgres (as well as MSSQL) and look forward to availability (given some of postgres development / extension capabilities, I would have thought this might make an excellent fit).
Is there any way that such support might be … supported or encouraged by us?
Absolutely! Encouragement in the form of many requests will of course make this a higher priority, but not necessarily speed things up much, as we are all tied up in (paid) projects, lectures, and events. So, the best way to speed things up is to either contribute directly by joining the Open Source development team or by freeing some of our time by monetary contributions. On a side note, I would really like to have PostgreSQL support, as this would make Anchor Modeling possible in a fully Open Source environment.
Spent a good deal of time looking through the tutorial videos and find the approach very interesting. One specific query. In the event of say a dish eg tiramisu being taken off the menu for period of time and then put back on the menu (after this period). Can this be done by retaining the same key id? How would you go about this in your demo?
Thanks
Luke
Yes, the simplest way would be to include a historized attribute or tie connecting a knot in the model stating whether or not a dish is eligible for inclusion in a menu. This knot could then be “toggled” over time, while retaining the same key id for the dish.
We are looking into different “extendable” modeling techniques for the database supporting a large environmental science project. Your online anchor modeling tool and tutorials have been very helpful. Do you have any timeline as to when support for Postgres or MariaDB will be available?
We are currently working on splitting up the XSLT that generate the SQL code in order to simplify porting to other databases. This work is scheduled to be finished before the summer. Once completed, work on supporting PostgreSQL or/and MariaDB will begin. In other words, sometime during the autumn would be a good bet. If people skilled in XSLT and PostgreSQL/MariaDB were to help out we could speed things up.
Anchor Modelling is simply amazing. I first knew about it two years ago, but I have not yet used it at work. I cannot understand how these techniques are not being boosted in EVERY organization by now.
Kudos.
Hello!
Haven’t still figured out where I should store natural (business) key. For example, I have Anchor CU_Customer. It contains surrogate keys (1, 2, 3 etc). The corresponding natural keys for the customers in OLTB database are (‘AAAAQW’, ‘AAAFDS’, ‘HFJEF’, etc). I need to model a table that stores “natural key” – “surrogate key” relation. I guess an Attribute table should be created CUNID_Customer_NaturalKey with the content:
1 ‘AAAAQW’; 2 ‘AAAFDS’, 3 ‘HFJEF’ right?
Thanks
It sounds like it should be an attribute on the customer anchor, provided that the key doesn’t belong to another anchor in the model.
Hello!
Nulls are not allowed in Anchor Modeling, which is clear. Nevertheless, sometimes Nulls are part of the business. For example, customer “Emma Charlotte Black” married and changed her last name to “White”. In addition, she decided to leave her middle name empty in new document (in some countries that is possible, in some not). Now she is known as “Charlotte White”. Once we have captured customer data modifications we should populate historized customer attributes with new data. However we can’t coz’ nulls are not allowed. What is the target solution for this case?
Thanks
In Anchor Modeling we assume that values are exhaustive, which simply means that for any given point in time there is a value of some kind in effect. I’d say Null is never part of the business and just a poorly implemented artifact from an insufficient understanding at the time databases were invented. What probably is part of the business are one or more of the following _special_ values:
* There is no applicable value
* There is an applicable value but it is currently not known
* There is an applicable value but it is decidedly not set
* There is an applicable value but it has been anonymized
…and so on.
There are two ways to achieve this. Either you have to create a user-defined data type that along with a regular value encodes the necessary _special_ values (the hard but elegant way), or you create a knotted historized attribute alongside your other attribute, that indicate the state a value is in (the easy but cumbersome way).
The reason I don’t mention the third way, allowing Nulls, is that it destroys many of the things Anchor modeling rely on in the database engine and its query optimizer. This is because Null is not treated as a value by the database, so it requires special treatment everywhere, as opposed to an actual value. I tried allowing it, but failed.
As a workaround, we do however support “Poor Man’s Auditability” (http://www.anchormodeling.com/?p=1145) in the test version of the modeling tool, which can be used in your particular situation.
>> Either you have to create a user-defined data type that along with a regular value encodes the necessary _special_ values (the hard but elegant way)
Could you elaborate your 1st solution, please? Do you mean hard-coding? For example decoding Null value to ‘n/a’ and inserting this new value into attribute table and then handling it when populating data marts?
Two questions
How can the types of cusines that could be relavent for modeling brought out for representation?
Example: Italian Cusine can only be served with Wine
Also Can the knots be historised? as these can change over time
How to model knots with many attributes? Examples of Gender_Code and Gender Description? Are the knots in 2 tables?
May I ask ..
1. What if there are ‘name’ attributes from various sources. Is modeled in one S_customer_name table or become 2 attribute tables?
2. Can we add Load_Date or Record_Source as metadata on every table in anchor modeling?
You can toggle an option in the online modeler for Metadata. When that is on, every table will have an additional identifier (Metadata_Id) which you can use to reference your own metadata structure, in which you can keep things like Load_Date and Record_Source.
Hello!
In 11 video (Using the temporal perspective) there is a field “ValidFrom” when you select from dbo.ldi_Dish latest view. Where did it came from? I want to enable such fields either but do no know how to do it.
It’s called “ChangedAt” in the newer versions, but in the modeling tool you can change it to whatever you want in the “Defaults” menu.
Thanks!
Hello again!
In online modeller there is a uniqueness limitation on anchor mnemonics (two letters, unique in all model). Am i right that maximum available count of anchors in model is A226=26!/(26−2)!=26!/24!=25⋅26=650 ? How can i avoid this limitation or turn off anchor mnemonics in modeller to have more than 650 anchors in my DWH?
Hello!
Is there a convenient approach to loading TIEs in DWH?
Please, make a new video tutorial for it or just answer something! =)
Lars, the Forum on this site is not working for me: though i registered and logged in it still asks me for login when i try to ask new question (new topic). Is it intentionally done (forum is closed for new questions?) or maybe some bug?
Maybe we (anchor makers) and You as a main information dealer can move our discussion onto some more comfortable forum site or, even better, a Wiki?
Hello
i was wondering if you have any research about anchor modeling and NOSQL databases for example with cassandra database or mongodb. i’m working on a project of agile modeling for data wearhouse and by curiosity we would like to try anchor modeling with NOSQL databases .
I am not aware of any current projects with Anchor modeling in NoSQL databases. There was an effort made using Anchor together with Splice Machine, but I was only involved in an early stage, so I do not know the results of their tests or if something went into production. Would you want to use the formalized (mathematical) Anchor model as a layer directly on top of a NoSQL database, or would you use the relational implementation in between?
Thanks for your reply
i would like to go from an anchor model to NOSQL model. for example in the current anchormodeling tool we could generate an sql code from an anchor model. What i would like to do is to generate from an anchor model a CQL code for example for cassandra database or a looklike sql code for others version of databases. i was looking on the internet for any research that could help me through this but still didn’t find anything important.