modeler - Add the possability to specify which attribute/role is part of the natural/business key.
delostilos opened this issue · 12 comments
Hi,
We are now adding tags in the 'description' field of an anchor to specify the natural/business key of an anchor. We use it to generate business/natural key lookup views and extended tie views we use in our ELT code. We currently made a SQL Server extended dialect to generate them.
Maybe it it nicer to add an extra property to the attribute/role to specify if it is used in the key.
Then it should be easier to generate the extra views.
Regards,
JJ.
Actually, the theoretical part of natural key management is already there, but not yet implemented. As it turns out a natural key is a directed route in an anchor model graph. You can think of it as a bus stopping at the attributes involved in the key.
If you look at example.xml you will find the constructions like <key stop="1" route="n" of="PE"/>. This means that this attribute is the first part of a composite natural key for the PE anchor. Of course there may be many ways to identify PE, in which case there would be other routes for the same anchor.
That is indeed a good way to look at the 'natural' identifiers of an anchor. We currently solve this by creating 'overlapping' anchor models where we specify the BK as an annotation. We have 3 BK integration patterns currently:
- The BK is of the same domain in each source system. We put the anchor and the BK attributes in our 'generic' capsule. In our case the electricity/gas connection has a unified BK that is used in the Netherlands.
- The BK is of a different domain, but is only present in a single source system. The source systems use different BK's but the entity instances are not overlapping. We can 'stack' all BK's in the same anchor. Now the anchor is in the 'generic' capsule, but the BK's in the source specific capsule. In our case contracts are in different systems, but there are no contract instances that are same in the different systems.
- The BK is of a different domain, and instances of the entity can be in multiple source systems. Now each source system get's its own anchor in its own source specific capsule, and we specify a generalized anchor in the generic capsule. We connect the specific anchors to the generic anchor via ties. The ties are then filled based on business rules to integrate the different BK's. Customer in the different systems would be an example of this.
We use the concept of overlapping anchor models. We specify one generic anchor model that has the 'generic' capsule as default capsule. We create an anchor model for each source system with its own default capsule and the 'generic' components get the 'generic' capsule. So in essence we have a sort of subview-anchor models. In the database all of this is now one big anchor model :-)
The thing is that we have to keep the 'generic' stuff consistent in all the models.
We made our own SQL extended dialect to have the correct generation of all the stuff.
I've added the mapping/bk drawing for the traffic example to show how I think BK's could be specified.
But I like the path idea.
Made a picture of the Traffic example with the path based version of the BK definition corresponding to the descriptions of the BK determination.
I think the dependent BK's are the difficult ones to get right.
Hi,
I was checking the example model on how you added the natural key of PE_Performance. It is a dependent key, but you specified the dependent attribute on 2 ways.
The route 'n' of PE:
1 - The natural key of ST_Stage (via PE_wasHeld_ST_at i think), the key tag in the ST_LOC_Stage_Location attribute.
2 - The natural key of PR_Program (i think), the key tag is now on the tie PE_at_PR_wasPlayed with PR->PE as path.
3 - The attribute PE_DAT_Performance_Date as the last one, with the key tag.
In the case of stop 1 and 2 you have two different ways of specifying the key.
The specification of the key tag in the tie is the way to go I think.
Hi,
I was thinking of creating an extra node type in the modeler for a key. Similar to a knot. So you can connect it to the attributes/ties that are in the key. The modeler should then have an option to hide/unhide the key nodes. Maybe have a (k) after the name of an attribute or tie if it plays a role in the key?
Oh and of course it should connect to the anchor of which it is the key.
It would then add the key tags similar to the knotRole. Maybe a keyRole ;-)
Made a picture of how it could be done. On the left side the traffic example of sisula-etl and on the right side a department - employee example with de employee key depending on the department key.
Used a combination of the attribute/tie symbol to visualise the key.
Hi,
Did a little bit of coding to have the possibility to create key-nodes.
I have cloned the repository to my repo: github/anchor
I've changed the example to have some key nodes:
and added the traffic example:
Creating a key node is possible via the Anchor.
Connecting the key node with a Attribute/Tie can be done via the multiple select and pressing 'c'.
The edges get a 'roman' number e.g. I, II, III, IV ... X after that they get the many icon.
It generates the XML also and loads from the XML :-)
Next stop is to generate key views via sisula and do some testing/bug fixing.
And change the context menu of the key node to be able to add the key-edges in 'bulk' and change the seuence of the edges.
Regards,
JJ.
I've changed the context menu of 'key role' edges to be able to change the sequence of the 'key role' in the key.
I've changed the icon of the key node and made the edges connecting the key and the attribute/tie to a dotted line.
Are we satisfied with the way these can be illustrated in the current test version?
Yes, it is a nice way to define and visualize the path of a key.