tag:blogger.com,1999:blog-37093298165772220292024-02-21T02:19:26.187+01:00Data Matters (Unseen)Musing on (unseen) things that matter for data modeling, datawarehousing and business intelligenceAnonymoushttp://www.blogger.com/profile/13164768885306814216noreply@blogger.comBlogger44125tag:blogger.com,1999:blog-3709329816577222029.post-80534218669827755572018-12-21T11:11:00.001+01:002018-12-21T11:11:08.469+01:00Hoe databedreven is jouw organisatie?<a href="https://www.passionned.nl/hoe-databedreven-is-jouw-organisatie/">Hoe databedreven is jouw organisatie?</a>Anonymoushttp://www.blogger.com/profile/13164768885306814216noreply@blogger.comtag:blogger.com,1999:blog-3709329816577222029.post-16287037523763046732018-12-21T11:10:00.001+01:002018-12-21T11:10:50.489+01:00Gezocht: data-architecten met holistische visie<a href="https://www.passionned.nl/gezocht-data-architecten-holistische-visie/">Gezocht: data-architecten met holistische visie</a>Anonymoushttp://www.blogger.com/profile/13164768885306814216noreply@blogger.comtag:blogger.com,1999:blog-3709329816577222029.post-83898761307315431542016-01-19T23:11:00.000+01:002016-01-19T23:12:44.241+01:00Star Wars and the Tao of Data Management<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhLAKCKrfOshgc9sLtaLF7M9tyq2d8NjbyDel77UlrpYkgTVio4Gc2o3JbuVGabv17KQQeZVE-CQt5UCYe7ZA0RIpJz4kCff-EcwoPz5HCwHoHD5PNIhw9PGnVyuuJ0U0HRQEIF73-eSrhi/s1600/YingYangStarWars4.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhLAKCKrfOshgc9sLtaLF7M9tyq2d8NjbyDel77UlrpYkgTVio4Gc2o3JbuVGabv17KQQeZVE-CQt5UCYe7ZA0RIpJz4kCff-EcwoPz5HCwHoHD5PNIhw9PGnVyuuJ0U0HRQEIF73-eSrhi/s1600/YingYangStarWars4.jpg" /></a></div>
<h4 style="text-align: center;">
</h4>
<h4 style="text-align: center;">
<a href="https://www.linkedin.com/pulse/star-wars-tao-data-management-martijn-evers" target="_blank">LinkedIn Article</a></h4>
Anonymoushttp://www.blogger.com/profile/13164768885306814216noreply@blogger.comtag:blogger.com,1999:blog-3709329816577222029.post-10871875363470754862016-01-13T10:51:00.003+01:002016-01-13T14:27:57.090+01:00"Information Technology" is about *Information* and NOT about *Technology*<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/proxy/AVvXsEigcLGxEw-E8KldSu332T_24CyYWeTxPa5dXt2KOfrYCnou1rlno8BDZ9-wZ19M7tHVfUTE_83XIvMZhcjPS5htygo4QhBMzBmBrkQlF7tWul0ToentcHonn67C_se-Zf8CQMYVZq69y5lON8tgHe9tVbK3kgXvr0WMmUJJ2O4LHXxvCc0zMR_TQfZZunsaD7sToIGTa7EvHgTXfaujtUJN7TcfmoeLwCl4Z_mdVQ=w530-h304-p-rw" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/proxy/AVvXsEigcLGxEw-E8KldSu332T_24CyYWeTxPa5dXt2KOfrYCnou1rlno8BDZ9-wZ19M7tHVfUTE_83XIvMZhcjPS5htygo4QhBMzBmBrkQlF7tWul0ToentcHonn67C_se-Zf8CQMYVZq69y5lON8tgHe9tVbK3kgXvr0WMmUJJ2O4LHXxvCc0zMR_TQfZZunsaD7sToIGTa7EvHgTXfaujtUJN7TcfmoeLwCl4Z_mdVQ=w530-h304-p-rw" /></a></div>
<br />
<h4 style="text-align: center;">
<a href="https://www.linkedin.com/pulse/information-technology-martijn-evers" rel="nofollow" target="_blank">LinkedIn Article</a></h4>
Anonymoushttp://www.blogger.com/profile/13164768885306814216noreply@blogger.comtag:blogger.com,1999:blog-3709329816577222029.post-26443876778497927612014-06-27T13:16:00.000+02:002014-06-27T13:34:58.586+02:00Short cutting Temporal Joins<h2>
Introduction</h2>
When you create temporal data models, either with Data Vault, Anchor modeling or any other way, you end up with several tables with timelines in them. These timelines often need to be combined for going to Data Marts or other processing. This combining is done with a temporal JOIN<br />
<h2>
Example</h2>
<ul>
<li><b>Table Product</b></li>
<li><b>Table Product Cost</b></li>
<li><b>Table Product Price</b></li>
<li><b>Calculating Product Revenue = Sales Price- Manufacturing Cost</b></li>
</ul>
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiU-OeKBgs2GQ-A_qsWHAyFr-zWs8xbejB-Sr31NGTVdmcPHp2JZh1J6CIf54lEMA_qxVyhPcFeT6kt6a6H6Qo-Y7UsiGVd7zZ1PxwlM2bUokQMUryIFe5T82xsoDaxnhFa9VhxDTZA0ele/s1600/tempjoin.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiU-OeKBgs2GQ-A_qsWHAyFr-zWs8xbejB-Sr31NGTVdmcPHp2JZh1J6CIf54lEMA_qxVyhPcFeT6kt6a6H6Qo-Y7UsiGVd7zZ1PxwlM2bUokQMUryIFe5T82xsoDaxnhFa9VhxDTZA0ele/s640/tempjoin.png" height="339" width="640" /></a></div>
<br />
Conceptually the query will look like this (using the Allen operator)<br />
<br />
<div style="background-color: white; padding: 5px 8px 5px 8px;">
<blockquote class="tr_bq">
<span style="font-family: Courier New;"> <span style="background-color: white;"><span style="color: blue;">SELECT</span> <span style="color: maroon;">"product cost"</span><span style="color: silver;">.</span><span style="color: maroon;">cost</span> <span style="color: maroon;">Cost</span><span style="color: silver;">,</span><br />
<br />
<span style="color: maroon;">"product price"</span><span style="color: silver;">.</span><span style="color: maroon;">price</span> <span style="color: blue;">AS</span> <span style="color: maroon;">Price</span><span style="color: silver;">,</span><br />
<br />
<span style="color: maroon;">"product price"</span><span style="color: silver;">.</span><span style="color: maroon;">price</span> <span style="color: silver;">-</span> <span style="color: maroon;">"product cost"</span><span style="color: silver;">.</span><span style="color: maroon;">cost</span> <span style="color: blue;">AS</span> <span style="color: maroon;">Revenue</span><span style="color: silver;">,</span><br />
<br />
<span style="color: maroon;">"product cost"</span><span style="color: silver;">.</span><span style="color: maroon;">period </span></span></span><span style="background-color: white;"><span style="color: blue; font-family: 'Courier New';">INTERSECTS </span><span style="color: maroon; font-family: 'Courier New';">"product price"</span><span style="color: silver; font-family: 'Courier New';">.</span><span style="color: maroon; font-family: 'Courier New';">period</span><span style="font-family: 'Courier New';"> </span><span style="color: blue; font-family: 'Courier New';">AS</span><span style="font-family: 'Courier New';"> </span><span style="color: maroon; font-family: 'Courier New';">Period</span></span><br />
<span style="background-color: white;"><span style="font-family: Courier New;"> <br />
<span style="color: blue;">FROM</span> <span style="color: maroon;">"product cost"</span><br />
<br />
<span style="color: blue;">INNER</span> <span style="color: blue;">JOIN</span> <span style="color: maroon;">"product price"</span><br />
<br />
<span style="color: blue;">ON</span> <span style="color: maroon;">"product price"</span><span style="color: silver;">.</span><span style="color: maroon;">id</span> <span style="color: silver;">=</span> <span style="color: maroon;">"product cost"</span><span style="color: silver;">.</span><span style="color: maroon;">id</span><br />
<br />
<span style="color: blue;">WHERE</span> <span style="color: maroon;">"product cost"</span><span style="color: silver;">.</span><span style="color: maroon;">period</span> </span><span style="color: blue; font-family: 'Courier New';">OVERLAPS </span><span style="color: maroon; font-family: 'Courier New';">"product price"</span><span style="color: silver; font-family: 'Courier New';">.</span><span style="color: maroon; font-family: 'Courier New';">period</span></span></blockquote>
<br /></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgOrjVRtB0b1mMqhWGgOLDJkgfEtTuB-e85ymekgcHDzTUFaShxRxVp0DnAwgZe_-hxDxQwP-rWRZY4Ucmyx3Dyk4ue7umMsLc_eRGSqqE1x8lyCrhzRDhfhcVUTqXR4Rov4qTKYd-_N3so/s1600/timelinepic.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgOrjVRtB0b1mMqhWGgOLDJkgfEtTuB-e85ymekgcHDzTUFaShxRxVp0DnAwgZe_-hxDxQwP-rWRZY4Ucmyx3Dyk4ue7umMsLc_eRGSqqE1x8lyCrhzRDhfhcVUTqXR4Rov4qTKYd-_N3so/s640/timelinepic.png" height="221" width="640" /></a></div>
<span style="font-size: 12pt;"><!--[if gte vml 1]><v:shapetype id="_x0000_t75"
coordsize="21600,21600" o:spt="75" o:preferrelative="t" path="m@4@5l@4@11@9@11@9@5xe"
filled="f" stroked="f"> <v:stroke joinstyle="miter"/> <v:formulas> <v:f eqn="if lineDrawn pixelLineWidth 0"/> <v:f eqn="sum @0 1 0"/> <v:f eqn="sum 0 0 @1"/> <v:f eqn="prod @2 1 2"/> <v:f eqn="prod @3 21600 pixelWidth"/> <v:f eqn="prod @3 21600 pixelHeight"/> <v:f eqn="sum @0 0 1"/> <v:f eqn="prod @6 1 2"/> <v:f eqn="prod @7 21600 pixelWidth"/> <v:f eqn="sum @8 21600 0"/> <v:f eqn="prod @7 21600 pixelHeight"/> <v:f eqn="sum @10 21600 0"/> </v:formulas> <v:path o:extrusionok="f" gradientshapeok="t" o:connecttype="rect"/> <o:lock v:ext="edit" aspectratio="t"/> </v:shapetype><v:shape id="_x0000_i1025" type="#_x0000_t75" style='width:6in;
height:148.5pt'> <v:imagedata src="file:///D:\TEMP\msohtml1\01\clip_image001.png" o:title=""/> </v:shape><![endif]--><!--[if !vml]--><!--[endif]--></span>The issues here are twofold, how to implement this in plain SQL, and what to define for Revenue for the omissions. Here we could state that the revenue is undefined, so in this case the first period is not available. Alas, this is not always the correct result.<br />
<h2>
<span style="font-size: large;"><br />
</span></h2>
<h2>
<span style="font-size: large;">SQL Implementation</span></h2>
<div>
There are a lot of ways to construct an SQL query that implements an intersection of 2 timelines. The most simplest are checking overlap between each 2 periods. This will quickly create unwieldy queries when more timelines need to be intersected. A more sophisticated approach rewrite, given x,y,z are periods:</div>
<div>
y OVERLAPS y OVERLAPS y to NOT EMPTY(x INTERSECTS y INTERSECTS z) and to</div>
<div>
say a = (x INTERSECTS y INTERSECTS z) then a.start_data<a .end_date.="" div="" href="http://www.blogger.com/blogger.g?blogID=3709329816577222029"></a><br />
<div>
<br />
max(x.start_date,y.start_date,z.start_date) to min(x.end_date,y.end_date,z.end_date) is a valid period, which means:<br />
max(x.start_date,y.start_date,z.start_date) < min(x.end_date,y.end_date,z.end_date)<br />
<br />
<h3>
Basic Greatest/Least SQL Query</h3>
</div>
<div>
Timeline Alignment<br />
<br />
All these kinds of queries only work correctly when the different tables have aligned their timelines (have the same start and end dates for each key). For the end date this works ou as long am 'high end date' is used. For start dates additional records need to be created with empty/default/null values to align on a 'low start date' (say 1753-01-01).<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgOPlY-CQzVxWuRXTadTlBBgpVeEj787UT1n2_WbtthrW308gUi4gGcgc_8P77zCZ62ii8bqtLM5VtzYzdW8hEQzHPU4mQ8K3I0DDVSPlqSGb4fJTEavNMuxIq6wqIo6BhXQPFbl3gk_CAl/s1600/timeline+alignment.png" imageanchor="1" style="background-color: white; margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgOPlY-CQzVxWuRXTadTlBBgpVeEj787UT1n2_WbtthrW308gUi4gGcgc_8P77zCZ62ii8bqtLM5VtzYzdW8hEQzHPU4mQ8K3I0DDVSPlqSGb4fJTEavNMuxIq6wqIo6BhXQPFbl3gk_CAl/s1600/timeline+alignment.png" height="196" width="640" /></a></div>
<br />
DBMS implementation<br />
<br />
These types of queries work in Both Oracle, Teradata and SQL Server. In Oracle and Teradata these queries can be used because the functions greatest and least are defined. In SQL Server however this is not the case.<br />
<br />
<h2>
SQL Server (T-SQL) implementation</h2>
To create this type of query in SQL Server we need both a Least and Greatest function. we can either code a .NET assembly for this, or an user defined function. Alas the udf won't perform at all. A trick here is to rewrite a scalar udf to an inline view udf. These will get optimized and peform OK. We only need to (CROSS) APPLY them to the query to get our wanted result.<br />
One of the issues here is that you cannot use the helper functions in indexed views, you need to substitue the case statement, and duplicate it both in the select and where clause.<br />
<h3>
Helper functions</h3>
<div>
Here are the SQL Server helper functions greatest and least and their usage pattern.</div>
</div>
<div style="background-color: white; padding: 5px 8px 5px 8px;">
<ol style="background-color: #f8f8f8; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', monospace, serif; font-size: 12px; line-height: 21px; margin: 0px; padding: 0px 0px 0px 48px;">
<li class="li1" style="-webkit-user-select: none; color: #acacac;"><div class="de1" style="-webkit-user-select: text; background-color: white; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; color: black; margin: 0px 0px 0px -7px; padding: 0px 5px; position: relative; vertical-align: top;">
<span class="kw1" style="color: blue;">CREATE</span> <span class="kw1" style="color: blue;">FUNCTION</span> <span class="br0" style="color: grey;">[</span>dbo<span class="br0" style="color: grey;">]</span>.<span class="br0" style="color: grey;">[</span>ivf_least<span class="br0" style="color: grey;">]</span> <span class="br0" style="color: grey;">(</span>@1 <span class="kw1" style="color: blue;">datetime</span><span class="sy0" style="color: grey;">=</span>null,@2 <span class="kw1" style="color: blue;">datetime</span> <span class="sy0" style="color: grey;">=</span> null ,@3 <span class="kw1" style="color: blue;">datetime</span> <span class="sy0" style="color: grey;">=</span> null,@4 <span class="kw1" style="color: blue;">datetime</span><span class="sy0" style="color: grey;">=</span>null,@5<span class="kw1" style="color: blue;">datetime</span> <span class="sy0" style="color: grey;">=</span> null<span class="br0" style="color: grey;">)</span></div>
</li>
<li class="li2" style="-webkit-user-select: none; color: #acacac;"><div class="de2" style="-webkit-user-select: text; background-color: white; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; color: black; margin: 0px 0px 0px -7px; padding: 0px 5px; position: relative; vertical-align: top;">
<span class="kw1" style="color: blue;">RETURNS</span> <span class="kw1" style="color: blue;">TABLE</span> <span class="kw1" style="color: blue;">WITH</span> SCHEMAB<span class="sy0" style="color: grey;">IN</span>D<span class="sy0" style="color: grey;">IN</span>G</div>
</li>
<li class="li1" style="-webkit-user-select: none; color: #acacac;"><div class="de1" style="-webkit-user-select: text; background-color: white; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; color: black; margin: 0px 0px 0px -7px; padding: 0px 5px; position: relative; vertical-align: top;">
<span class="kw1" style="color: blue;">RETURN</span></div>
</li>
<li class="li2" style="-webkit-user-select: none; color: #acacac;"><div class="de2" style="-webkit-user-select: text; background-color: white; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; color: black; margin: 0px 0px 0px -7px; padding: 0px 5px; position: relative; vertical-align: top;">
<span class="br0" style="color: grey;">(</span><span class="kw1" style="color: blue;">WITH</span> coal<span class="br0" style="color: grey;">(</span>a1,a2,a3,a4,a5<span class="br0" style="color: grey;">)</span></div>
</li>
<li class="li1" style="-webkit-user-select: none; color: #acacac;"><div class="de1" style="-webkit-user-select: text; background-color: white; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; color: black; margin: 0px 0px 0px -7px; padding: 0px 5px; position: relative; vertical-align: top;">
<span class="kw1" style="color: blue;">AS</span> <span class="br0" style="color: grey;">(</span><span class="kw1" style="color: blue;">SELECT </span><span class="kw1" style="color: blue;">COALESCE</span><span class="br0" style="color: grey;">(</span>@1,@2,@3,@4,@5<span class="br0" style="color: grey;">)</span>,<span class="kw1" style="color: blue;">COALESCE</span><span class="br0" style="color: grey;">(</span>@2,@3,@4,@5,@1<span class="br0" style="color: grey;">)</span>,<span class="kw1" style="color: blue;">COALESCE</span><span class="br0" style="color: grey;">(</span>@3,@4,@5,@1,@2<span class="br0" style="color: grey;">)</span>,<span class="kw1" style="color: blue;">COALESCE</span><span class="br0" style="color: grey;">(</span>@4,@5,@1,@2,@3<span class="br0" style="color: grey;">)</span>,<span class="kw1" style="color: blue;">COALESCE</span><span class="br0" style="color: grey;">(</span>@5,@1,@2,@3,@4<span class="br0" style="color: grey;">)</span><span class="br0" style="color: grey;">)</span></div>
</li>
<li class="li2" style="-webkit-user-select: none; color: #acacac;"><div class="de2" style="-webkit-user-select: text; background-color: white; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; color: black; margin: 0px 0px 0px -7px; padding: 0px 5px; position: relative; vertical-align: top;">
<span class="kw1" style="color: blue;">SELECT</span> <span class="kw1" style="color: blue;">case</span> <span class="kw1" style="color: blue;">when</span> a1<span class="sy0" style="color: grey;"><=</span>a2 and a1<span class="sy0" style="color: grey;"><=</span>a3 and a1<span class="sy0" style="color: grey;"><=</span>a4 and a1<span class="sy0" style="color: grey;"><=</span>a5 <span class="kw1" style="color: blue;">then</span> a1 </div>
</li>
<li class="li1" style="-webkit-user-select: none; color: #acacac;"><div class="de1" style="-webkit-user-select: text; background-color: white; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; color: black; margin: 0px 0px 0px -7px; padding: 0px 5px; position: relative; vertical-align: top;">
<span class="kw1" style="color: blue;">when</span> a2<span class="sy0" style="color: grey;"><=</span>a3 and a2<span class="sy0" style="color: grey;"><=</span>a4 and a2<span class="sy0" style="color: grey;"><=</span>a5 <span class="kw1" style="color: blue;">then</span> a2</div>
</li>
<li class="li2" style="-webkit-user-select: none; color: #acacac;"><div class="de2" style="-webkit-user-select: text; background-color: white; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; color: black; margin: 0px 0px 0px -7px; padding: 0px 5px; position: relative; vertical-align: top;">
<span class="kw1" style="color: blue;">when</span> a3<span class="sy0" style="color: grey;"><=</span>a4 and a3<span class="sy0" style="color: grey;"><=</span>a5 <span class="kw1" style="color: blue;">then</span> a3</div>
</li>
<li class="li1" style="-webkit-user-select: none; color: #acacac;"><div class="de1" style="-webkit-user-select: text; background-color: white; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; color: black; margin: 0px 0px 0px -7px; padding: 0px 5px; position: relative; vertical-align: top;">
<span class="kw1" style="color: blue;">when</span> a4<span class="sy0" style="color: grey;"><</span>a5 <span class="kw1" style="color: blue;">then</span> a4</div>
</li>
<li class="li2" style="-webkit-user-select: none; color: #acacac;"><div class="de2" style="-webkit-user-select: text; background-color: white; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; color: black; margin: 0px 0px 0px -7px; padding: 0px 5px; position: relative; vertical-align: top;">
<span class="kw1" style="color: blue;">else</span> a5 <span class="kw1" style="color: blue;">end</span> <span class="kw1" style="color: blue;">as</span> <span class="kw1" style="color: blue;">out</span> <span class="kw1" style="color: blue;">from</span> coal<span class="br0" style="color: grey;">)</span>;</div>
</li>
<li class="li1" style="-webkit-user-select: none; color: #acacac;"><div class="de1" style="-webkit-user-select: text; background-color: white; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; color: black; margin: 0px 0px 0px -7px; padding: 0px 5px; position: relative; vertical-align: top;">
GO</div>
</li>
<li class="li2" style="-webkit-user-select: none; color: #acacac;"><div class="de2" style="-webkit-user-select: text; background-color: white; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; color: black; margin: 0px 0px 0px -7px; padding: 0px 5px; position: relative; vertical-align: top;">
</div>
</li>
<li class="li1" style="-webkit-user-select: none;"><div class="de1" style="-webkit-user-select: text; background-color: white; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; margin: 0px 0px 0px -7px; padding: 0px 5px; position: relative; vertical-align: top;">
<span style="color: blue;">CREATE</span><span style="color: black;"> <span class="kw1" style="color: blue;">FUNCTION</span> <span class="br0" style="color: grey;">[</span>dbo<span class="br0" style="color: grey;">]</span>.<span class="br0" style="color: grey;">[</span>ivf_greatest<span class="br0" style="color: grey;">]</span> <span class="br0" style="color: grey;">(</span>@1 <span class="kw1" style="color: blue;">datetime</span><span class="sy0" style="color: grey;">=</span>null,@2 <span class="kw1" style="color: blue;">datetime</span> <span class="sy0" style="color: grey;">=</span> null ,@3 <span class="kw1" style="color: blue;">datetime</span> <span class="sy0" style="color: grey;">=</span> null,@4 <span class="kw1" style="color: blue;">datetime</span><span class="sy0" style="color: grey;">=</span>null,@5<span class="kw1" style="color: blue;">datetime</span> <span class="sy0" style="color: grey;">=</span> null<span class="br0" style="color: grey;">)</span></span></div>
</li>
<li class="li2" style="-webkit-user-select: none; color: #acacac;"><div class="de2" style="-webkit-user-select: text; background-color: white; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; color: black; margin: 0px 0px 0px -7px; padding: 0px 5px; position: relative; vertical-align: top;">
<span class="kw1" style="color: blue;">RETURNS</span> <span class="kw1" style="color: blue;">TABLE</span> <span class="kw1" style="color: blue;">WITH</span> SCHEMAB<span class="sy0" style="color: grey;">IN</span>D<span class="sy0" style="color: grey;">IN</span>G</div>
</li>
<li class="li1" style="-webkit-user-select: none; color: #acacac;"><div class="de1" style="-webkit-user-select: text; background-color: white; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; color: black; margin: 0px 0px 0px -7px; padding: 0px 5px; position: relative; vertical-align: top;">
<span class="kw1" style="color: blue;">RETURN</span></div>
</li>
<li class="li2" style="-webkit-user-select: none; color: #acacac;"><div class="de2" style="-webkit-user-select: text; background-color: white; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; color: black; margin: 0px 0px 0px -7px; padding: 0px 5px; position: relative; vertical-align: top;">
<span class="br0" style="color: grey;">(</span><span class="kw1" style="color: blue;">WITH</span> coal<span class="br0" style="color: grey;">(</span>a1,a2,a3,a4,a5<span class="br0" style="color: grey;">)</span></div>
</li>
<li class="li1" style="-webkit-user-select: none; color: #acacac;"><div class="de1" style="-webkit-user-select: text; background-color: white; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; color: black; margin: 0px 0px 0px -7px; padding: 0px 5px; position: relative; vertical-align: top;">
<span class="kw1" style="color: blue;">AS</span> <span class="br0" style="color: grey;">(</span><span class="kw1" style="color: blue;">SELECT </span><span class="kw1" style="color: blue;">COALESCE</span><span class="br0" style="color: grey;">(</span>@1,@2,@3,@4,@5<span class="br0" style="color: grey;">)</span>,<span class="kw1" style="color: blue;">COALESCE</span><span class="br0" style="color: grey;">(</span>@2,@3,@4,@5,@1<span class="br0" style="color: grey;">)</span>,<span class="kw1" style="color: blue;">COALESCE</span><span class="br0" style="color: grey;">(</span>@3,@4,@5,@1,@2<span class="br0" style="color: grey;">)</span>,<span class="kw1" style="color: blue;">COALESCE</span><span class="br0" style="color: grey;">(</span>@4,@5,@1,@2,@3<span class="br0" style="color: grey;">)</span>,<span class="kw1" style="color: blue;">COALESCE</span><span class="br0" style="color: grey;">(</span>@5,@1,@2,@3,@4<span class="br0" style="color: grey;">)</span><span class="br0" style="color: grey;">)</span></div>
</li>
<li class="li2" style="-webkit-user-select: none; color: #acacac;"><div class="de2" style="-webkit-user-select: text; background-color: white; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; color: black; margin: 0px 0px 0px -7px; padding: 0px 5px; position: relative; vertical-align: top;">
<span class="kw1" style="color: blue;">SELECT</span> <span class="kw1" style="color: blue;">case</span> <span class="kw1" style="color: blue;">when</span> a1<span class="sy0" style="color: grey;">>=</span>a2 and a1<span class="sy0" style="color: grey;">>=</span>a3 and a1<span class="sy0" style="color: grey;">>=</span>a4 and a1<span class="sy0" style="color: grey;">>=</span>a5 <span class="kw1" style="color: blue;">then</span> a1 </div>
</li>
<li class="li1" style="-webkit-user-select: none; color: #acacac;"><div class="de1" style="-webkit-user-select: text; background-color: white; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; color: black; margin: 0px 0px 0px -7px; padding: 0px 5px; position: relative; vertical-align: top;">
<span class="kw1" style="color: blue;">when</span> a2<span class="sy0" style="color: grey;">>=</span>a3 and a2<span class="sy0" style="color: grey;">>=</span>a4 and a2<span class="sy0" style="color: grey;">>=</span>a5 <span class="kw1" style="color: blue;">then</span> a2</div>
</li>
<li class="li2" style="-webkit-user-select: none; color: #acacac;"><div class="de2" style="-webkit-user-select: text; background-color: white; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; color: black; margin: 0px 0px 0px -7px; padding: 0px 5px; position: relative; vertical-align: top;">
<span class="kw1" style="color: blue;">when</span> a3<span class="sy0" style="color: grey;">>=</span>a4 and a3<span class="sy0" style="color: grey;">>=</span>a5 <span class="kw1" style="color: blue;">then</span> a3</div>
</li>
<li class="li1" style="-webkit-user-select: none; color: #acacac;"><div class="de1" style="-webkit-user-select: text; background-color: white; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; color: black; margin: 0px 0px 0px -7px; padding: 0px 5px; position: relative; vertical-align: top;">
<span class="kw1" style="color: blue;">when</span> a4<span class="sy0" style="color: grey;">></span>a5 <span class="kw1" style="color: blue;">then</span> a4</div>
</li>
<li class="li2" style="-webkit-user-select: none; color: #acacac;"><div class="de2" style="-webkit-user-select: text; background-color: white; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; color: black; margin: 0px 0px 0px -7px; padding: 0px 5px; position: relative; vertical-align: top;">
<span class="kw1" style="color: blue;">else</span> a5 <span class="kw1" style="color: blue;">end</span> <span class="kw1" style="color: blue;">as</span> <span class="kw1" style="color: blue;">out</span> <span class="kw1" style="color: blue;">from</span> coal<span class="br0" style="color: grey;">)</span>;</div>
</li>
<li class="li1" style="-webkit-user-select: none; color: #acacac;"><div class="de1" style="-webkit-user-select: text; background-color: white; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; color: black; margin: 0px 0px 0px -7px; padding: 0px 5px; position: relative; vertical-align: top;">
GO</div>
</li>
</ol>
<br />
Using this functions in the following way:<br />
<br />
<br />
<ol style="background-color: #f8f8f8; color: #acacac; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', monospace, serif; font-size: 12px; line-height: 21px; margin: 0px; padding: 0px 0px 0px 48px;">
<li class="li1" style="-webkit-user-select: none;"><div class="de1" style="-webkit-user-select: text; background-color: white; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; color: black; margin: 0px 0px 0px -7px; padding: 0px 5px; position: relative; vertical-align: top;">
<span class="kw1" style="color: blue;">CREATE</span> <span class="kw1" style="color: blue;">TABLE</span> SAT1 <span class="br0" style="color: grey;">(</span>DV_ID <span class="kw1" style="color: blue;">int</span>,START_DT <span class="kw1" style="color: blue;">datetime</span>,END_DT <span class="kw1" style="color: blue;">datetime</span><span class="br0" style="color: grey;">)</span> <span class="kw1" style="color: blue;">PRIMARY</span> <span class="kw1" style="color: blue;">KEY</span> <span class="br0" style="color: grey;">(</span>DV_ID,START_DT<span class="br0" style="color: grey;">)</span></div>
</li>
<li class="li2" style="-webkit-user-select: none;"><div class="de2" style="-webkit-user-select: text; background-color: white; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; color: black; margin: 0px 0px 0px -7px; padding: 0px 5px; position: relative; vertical-align: top;">
GO</div>
</li>
<li class="li1" style="-webkit-user-select: none;"><div class="de1" style="-webkit-user-select: text; background-color: white; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; color: black; margin: 0px 0px 0px -7px; padding: 0px 5px; position: relative; vertical-align: top;">
<span class="kw1" style="color: blue;">CREATE</span> <span class="kw1" style="color: blue;">TABLE</span> SAT2 <span class="br0" style="color: grey;">(</span>DV_ID <span class="kw1" style="color: blue;">int</span>,START_DT <span class="kw1" style="color: blue;">datetime</span>,END_DT <span class="kw1" style="color: blue;">datetime</span><span class="br0" style="color: grey;">)</span> <span class="kw1" style="color: blue;">PRIMARY</span> <span class="kw1" style="color: blue;">KEY</span> <span class="br0" style="color: grey;">(</span>DV_ID,START_DT<span class="br0" style="color: grey;">)</span></div>
</li>
<li class="li2" style="-webkit-user-select: none;"><div class="de2" style="-webkit-user-select: text; background-color: white; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; color: black; margin: 0px 0px 0px -7px; padding: 0px 5px; position: relative; vertical-align: top;">
GO</div>
</li>
<li class="li1" style="-webkit-user-select: none;"><div class="de1" style="-webkit-user-select: text; background-color: white; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; color: black; margin: 0px 0px 0px -7px; padding: 0px 5px; position: relative; vertical-align: top;">
<span class="kw1" style="color: blue;">select</span></div>
</li>
<li class="li2" style="-webkit-user-select: none;"><div class="de2" style="-webkit-user-select: text; background-color: white; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; color: black; margin: 0px 0px 0px -7px; padding: 0px 5px; position: relative; vertical-align: top;">
SAT1.<span class="me1" style="color: #202020;">DV_ID</span>,</div>
</li>
<li class="li1" style="-webkit-user-select: none;"><div class="de1" style="-webkit-user-select: text; background-color: white; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; color: black; margin: 0px 0px 0px -7px; padding: 0px 5px; position: relative; vertical-align: top;">
GREATEST_DT.<span class="kw1" style="color: blue;">out</span> <span class="kw1" style="color: blue;">as</span> START_DT,</div>
</li>
<li class="li2" style="-webkit-user-select: none;"><div class="de2" style="-webkit-user-select: text; background-color: white; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; color: black; margin: 0px 0px 0px -7px; padding: 0px 5px; position: relative; vertical-align: top;">
LEAST_DT.<span class="kw1" style="color: blue;">out</span> <span class="kw1" style="color: blue;">as</span> END_DT</div>
</li>
<li class="li1" style="-webkit-user-select: none;"><div class="de1" style="-webkit-user-select: text; background-color: white; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; color: black; margin: 0px 0px 0px -7px; padding: 0px 5px; position: relative; vertical-align: top;">
<span class="kw1" style="color: blue;">from</span> SAT1 <span class="kw1" style="color: blue;">inner</span> join SAT2 <span class="kw1" style="color: blue;">on</span> SAT1.<span class="me1" style="color: #202020;">DV_ID</span><span class="sy0" style="color: grey;">=</span>SAT2.<span class="me1" style="color: #202020;">DV_ID</span></div>
</li>
<li class="li2" style="-webkit-user-select: none;"><div class="de2" style="-webkit-user-select: text; background-color: white; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; color: black; margin: 0px 0px 0px -7px; padding: 0px 5px; position: relative; vertical-align: top;">
cross apply ivf_greatest<span class="br0" style="color: grey;">(</span>SAT1.<span class="me1" style="color: #202020;">START_DT</span>,SAT2.<span class="me1" style="color: #202020;">START_DT</span>,<span class="sy0" style="color: grey;">NULL</span>,<span class="sy0" style="color: grey;">NULL</span>,<span class="sy0" style="color: grey;">NULL</span><span class="br0" style="color: grey;">)</span> <span class="kw1" style="color: blue;">as</span> GREATEST_DT</div>
</li>
<li class="li1" style="-webkit-user-select: none;"><div class="de1" style="-webkit-user-select: text; background-color: white; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; color: black; margin: 0px 0px 0px -7px; padding: 0px 5px; position: relative; vertical-align: top;">
cross apply ivf_least<span class="br0" style="color: grey;">(</span>SAT1.<span class="me1" style="color: #202020;">END_DT</span>,SAT2.<span class="me1" style="color: #202020;">END_DT</span>,<span class="sy0" style="color: grey;">NULL</span>,<span class="sy0" style="color: grey;">NULL</span>,<span class="sy0" style="color: grey;">NULL</span><span class="br0" style="color: grey;">)</span> <span class="kw1" style="color: blue;">as</span> LEAST_DT</div>
</li>
<li class="li2" style="-webkit-user-select: none;"><div class="de2" style="-webkit-user-select: text; background-color: white; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; color: black; margin: 0px 0px 0px -7px; padding: 0px 5px; position: relative; vertical-align: top;">
<span class="kw1" style="color: blue;">where</span></div>
</li>
<li class="li1" style="-webkit-user-select: none;"><div class="de1" style="-webkit-user-select: text; background-color: white; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; color: black; margin: 0px 0px 0px -7px; padding: 0px 5px; position: relative; vertical-align: top;">
GREATEST_DT.<span class="kw1" style="color: blue;">out</span> <span class="sy0" style="color: grey;"><</span> LEAST_DT.<span class="kw1" style="color: blue;">out</span> <span class="co1" style="color: teal;">-- assuming an [Closed,Open) period</span></div>
</li>
<li class="li2" style="-webkit-user-select: none;"><div class="de2" style="-webkit-user-select: text; background-color: white; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; color: black; margin: 0px 0px 0px -7px; padding: 0px 5px; position: relative; vertical-align: top;">
GO</div>
</li>
</ol>
</div>
</div>
Anonymoushttp://www.blogger.com/profile/13164768885306814216noreply@blogger.com0tag:blogger.com,1999:blog-3709329816577222029.post-29360961976233664612013-11-08T15:34:00.002+01:002013-11-11T13:08:18.778+01:00One "Data Modeling" approach To Rule Them all<div class="separator" style="clear: both; text-align: center;">
</div>
<div style="margin-left: 1em; margin-right: 1em;">
<img height="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj-Cxu52td7ZyIFWvGX188CYL9W98t5Xg0QO08FghepwWQbviE5aWOt5hD21-kMaViH9c02z-aSSNmzhn9arkndRDQhdtggHUOi4oeAMOw0lsf6BI492V4u9KtBOTT7cedZmaOMsOeA05T0/s640/one-ring-to-rule-them-all-1.png" width="640" /></div>
<br />
<br />
<br />
<h2>
The Task</h2>
In a previous post on <i>implementation (data) modeling styles</i> I talked about needing a 'universal data modeling technique/method' that could be a starting point for all your structural model transformations (semantically equivalent derivations), be that dimensional, normalized or Anchor Style Modeling. Such a modeling technique should allow for easy transformation, it needs to be agnostic to most transformation techniques, but should still guarantee several levels of semantic equivalence. It also should allow for (database) model re-engineering and allow for data representations on any desired "level" (conceptual, logical). It should facilitate conceptual data integration and facilitate temporal aspects as well.<br />
<h2>
The magic wand of Semantic Equivalence</h2>
It is important to realize that all implementation modeling techniques like dimensional and Data Vault rely on a certain class of model transformations I call fully semantic equivalent. These class of transformations preserve (parts of) the data (they preserve the functional, join, multi value dependencies as defined in the RM) and hence provide a degree of data tracability from one transformation to the next. However, they DO make some aspects more difficult, especially maintaining all kinds of (complex) constraints. These kinds of transformations are usually biased towards certain aspects. Things like constraint minimization and standardization are best served by (highly) normalized models. Manual temporal processing and schema change impact isolation are best served by Anchor style modeling techniques like Data Vault. User access and data navigation are best served by (logical) Dimensional Models (see the OASI concept of van der Lek).<br />
<h2>
The Candidates</h2>
<div>
For me there where only 2 serious options, namely an anchored version of 6NF on the logical level, or a <i><b>Fact Based Modeling technique</b></i> (FOM) from the NIAM family: <a href="http://www.fco-im.nl/">FCO-IM</a>, <a href="http://en.wikipedia.org/wiki/NIAM">ORM </a>or CogNIAM on the conceptual level. Other techniques like OWL are poor on constraint modeling and derivation and hence lack the desired easy (semantic equivalence) transformation. <a href="http://en.wikipedia.org/wiki/Sixth_normal_form">6NF </a>is an interesting candidate on the logical level since it is irreducible, time extendable and is able to house constraints and derivations. It does not contain key-tables (anchors) by default, but we can create an anchored version (A6NF) that creates an anchor for each key that has a functional dependency. Date, Darwen an Lorentzos showed how to formally define <a href="http://www.dcs.warwick.ac.uk/~hugh/TTM/TemporalData.Warwick.pdf">temporal features</a> in the relational model as well. However, apart form conceptual aspects like classification and quantification, verbalization and specialization/generalization normalized models also do not directly abstract away from relationship implementation (which is also a <i>bonus</i>, but not in this scenario), which, given the myriad types of surrogation strategies used in data sources is considered a deficiency here. Abstracting away from foreign keys to relationships/roles gives rise to some additional issues but all in all FOM's can do all of this nicely (as far as it goes). The Fact Based modeling family lacks something in operators, esp temporal ones, but that can be remedied with a conceptual query language and simple conceptual temporal extension. (Another issues is that we need to facilitate robust and customizable data model reverse engineering, something current FCO-IM only has in a simple form.) I think that of all the FBM dialects FCO-IM lends itself best for <i>understanding </i>model transformations/derivations because it's focusing on fact types. For me this means that FCO-IM is an ideal candidate to use both as a modeling technique and as a modeling method (actually a diagramming method btw). Coupled with the fact that FCO-IM is taught and researched over here in the Netherlands means that it was an easy choice for me to make.<br />
<h2>
The One Model Methodology</h2>
The result is that I depend on FCO-IM for my transformation strategy analysis, and that it has become an important part of the <a href="http://dm-unseen.blogspot.nl/2012/09/introducing-matter-advanced-data.html">MATTER </a>program. It allows me to analyze, verbalize, display and derive implementation data model schema's in a consistent and generic way. This allows me to understand arbitrary <i>implementation modeling styles </i>in just one conceptual data model as a restructuring of fact (types). This way I resolve arbitrary diagramming, modeling, designing of data model schema's in a set of consistent, complete and correct fact restructuring directives.<br />
<h2>
Derivations</h2>
<h3>
Transformation vs Derivations</h3>
<div>
FCO-IM is usable in describing the class of fully semantic equivalent transformations since it conceptually captures dependencies. The actual restructuring is done on the conceptual schemas of FCO-IM itself. a Model transformation first becomes (conceptual) model (schema) standardization and from there model schema derivation. We call this structural transformation or model restructuring, but in fact it is model schema derivation strategy. </div>
<h3>
Implementations vs Definitions</h3>
<div>
As long as FCO-IM diagrams cannot be implemented directly we would actually transform an FCO-IM diagram to an an Anchorized 6NF model. This would be our idealized implementation model. from here all our implementation models become <i>derivation </i>models. Hence, implementation models are logical models that are logically derivable and controllable from a A6NF schema, while they are conceptually derived from a FBM model. Some implementation modeling styles that are closely related to A6NF (like normalized or Anchorized styles) lend themselves for creating central data repositories, while others (like dimensional) lend themselves to become derivated abstraction layers on top of this. Alas, in real world implementations we are usually forced to materialize some of these derived implementation modeling styles directly in databases, creating extra overhead that needs to be managed by some sort of data <i>automation</i>.<br />
<h3>
Implementation Modeling Styles?</h3>
</div>
<div>
Implementation data modeling styles are not 'physical' data modeling styles. Physical is a misnomer from the ER and SQL database world. a Physical data model is what is stored inside a database like indexes and table spaces. Implementation modeling styles are logical model <i>deviations </i>used for logically accessing data, but also serving some non functional requirements around presentation, processing, performance and maintenance. They are <b><i>artifacts </i></b>created to counter the poor separation of concerns within current data toolings like SQL DBMSes, ETL tools and bad data management, poor data quality, poor temporal support etc etc. They are used for restructuring database schema's to separate and handle these concerns. In a TRDMS, a <i>true </i>relational database management system we would generate just 1 logical schema. Data Vaults and Dimensional models as we know them now would not be needed.</div>
<h2>
The Mission</h2>
<div>
For a lot of BI professionals however, Information modeling using FCO-IM is terra incognita and semantically equivalent model transformations (if done at all) are done using basic ER diagramming, which prohibits good standardization, transformation and (formal) derivations and hence understanding of implementation data modeling styles. ER modeling is just about visualization/drawing/noting model schema's, and do not help understanding this. To make understanding implementation data modeling styles better and more objective we need to educate professionals in this respect. Also since derivation and transformation go hand in hand, they need to understand the role of the relational model and model derivation as well, not just the (technical oriented) artifacts like Data Vaults and Dimensional Models.<br />
<h2>
Your Chance!</h2>
</div>
<div>
In December we start our MATTER FCO-IM track so BI professionals can dive deep into this aspect of information modeling. We start withg 3 days hand on FCO-IM (8-10 dec.). See for yourself how FCO-IM works and facilitates good data modeling. See <a href="http://www.bi-podium.nl/showcase/dwh_matter_program_hands_on_fco_im/32">BI Podium website</a> for more info.</div>
<br />
<br /></div>
<br />Anonymoushttp://www.blogger.com/profile/13164768885306814216noreply@blogger.com0tag:blogger.com,1999:blog-3709329816577222029.post-11807256709467368522013-09-13T14:09:00.002+02:002013-09-13T14:09:58.967+02:00Testberichte DETestberichte DEAnonymoushttp://www.blogger.com/profile/13164768885306814216noreply@blogger.comtag:blogger.com,1999:blog-3709329816577222029.post-10099326060018282862013-09-13T14:04:00.000+02:002013-09-13T14:04:12.876+02:00testbericht NLtestbericht NLAnonymoushttp://www.blogger.com/profile/13164768885306814216noreply@blogger.comtag:blogger.com,1999:blog-3709329816577222029.post-30212045374200266472013-08-27T11:33:00.002+02:002013-09-13T14:05:25.241+02:00Reprising the MATTER Data Vault in-the-trenches track this fall<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiBSm7rwMtmdBmv2HyMVwLfEmeojXHW6AXTWAS9dyspcthGLZ59QLfD8y2vMrdn92TTBBGZvlOm142jTomkVSR2FxyLRtnTTD5DrMChcxsw9nyawqbn4RbtThrR-oIzkBI-3yG8uRwa5a7L/s1600/dali-disintegration-of-persistence-of-memory-jpg.jpeg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="241" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiBSm7rwMtmdBmv2HyMVwLfEmeojXHW6AXTWAS9dyspcthGLZ59QLfD8y2vMrdn92TTBBGZvlOm142jTomkVSR2FxyLRtnTTD5DrMChcxsw9nyawqbn4RbtThrR-oIzkBI-3yG8uRwa5a7L/s320/dali-disintegration-of-persistence-of-memory-jpg.jpeg" width="320" /></a></div>
This fall the <a href="http://dm-unseen.blogspot.nl/2012/09/introducing-matter-advanced-data.html" target="_blank">MATTER program</a> will reprise the <a href="http://dm-unseen.blogspot.nl/2012/10/data-vault-in-trenches-track.html" target="_blank">Data Vault in the trenches track</a>. This track, meant for the more experienced Data (Vault) modelers/architects/BI specialist focuses on the broad subject of Data Vault, Anchor Style modeling, 'Temporal data modeling' and even some <a href="http://dm-unseen.blogspot.nl/2013/06/data-vault-vs-anchor-modeling-who-is-one.html" target="_blank">Anchor Modeling</a>. This is a 'no hold barred' track, so if you want to have thorough insights into Data Vault and other techniques this is your track. We'll deep dive in all the issues and opportunities that these kinds of model <i>approaches </i>have to offer.<br />
<div>
<br /></div>
<div>
I will personally teach the track's upcoming installments:</div>
<div>
<ol>
<li>Data Vault & Temporal Data Modeling in the trenches (11-12 sept.)</li>
<ul>
<li>Focusing on (advanced) modeling constructs like key satellites, model transformation, model optimization, temporalization, model segmentation and many more issues.</li>
</ul>
<li>Data warehouse and Data Vault oriented Architecture, metadata & ETL (planned for begin oct.)</li>
<ul>
<li>Focusing on Data Architecture, Raw/Rule Vault, business data models, metadata, transformation & generation & Data logistics.</li>
</ul>
<li>Advanced Data Modeling & Data Vault subjects (23-24 oct.)</li>
<ul>
<li>Focusing on advanced modeling concepts like sub/super-typing (specialization/generalization), abstraction, multiple keys, virtualization, ...</li>
</ul>
</ol>
</div>
<div>
See the<a href="http://dm-unseen.blogspot.nl/2013/02/data-vault-agenda-2013.html" target="_blank"> Data Vault agenda</a> or <a href="http://www.bi-podium.nl/showcase/data_vault_modeling/20" target="_blank">BI-podium</a> website for more details. If you have questions around the track's qualifications, information or contents, don't hesitate to contact me or comment below this post and I'll get back to you.</div>
Anonymoushttp://www.blogger.com/profile/13164768885306814216noreply@blogger.com0Nederland52.132633 5.291265999999950549.638064 0.12769199999995084 54.627202 10.454839999999951tag:blogger.com,1999:blog-3709329816577222029.post-23725252185685931052013-06-11T11:53:00.000+02:002013-09-13T14:05:25.234+02:00Presentations Next generation DWH Modeling available for downloadFor those that want to download my own and all the other the presentations given last Thursday at the Next Generation DWH Modeling Conference, they are now available <a href="http://bi-podium.nl/showcase/next_generation_dwh_modeling/21" target="_blank">here</a>.Anonymoushttp://www.blogger.com/profile/13164768885306814216noreply@blogger.comtag:blogger.com,1999:blog-3709329816577222029.post-72325209143003683312013-06-11T11:39:00.002+02:002013-09-13T14:05:25.288+02:00Data Modeling Zone<div class="separator" style="clear: both; text-align: center;">
</div>
<div style="margin-left: 1em; margin-right: 1em;">
<img alt="Data Modeling Zone Europe" src="http://www.datamodelingzone.eu/2013/wp-content/uploads/2013/02/DMZ-2013-Logo.png" /></div>
<br />
<br />
<br />
Last fall I was at the first Data Modeling Zone in Baltimore, USA. This fall there will be 2 conferences dedicated to data modeling. One in Baltimore,USA 8-10th of October and one European one in Hanover, Germany on 23rd and 24th of September.<br />
<h3>
Content</h3>
<div>
There will be a lot of info on <a href="http://www.fco-im.nl/">FCO-IM</a>, Model transformations, Data Vault, Anchor Modeling and much more.</div>
<h3>
Presentations</h3>
<div>
I'll be doing 2 presentations on the European Zone. One on <a href="http://www.fco-im.nl/">Information Modeling</a> and implementations around Data Vault, Anchor Modeling etc. and one on the success of Data Vault and Data warehouse Automation in the Netherlands.</div>
<br /><h4>
DMZ Promotion</h4>
For those that would like to attend the organization has set up individual promotion codes for each of the speakers - my code is EversDMZPromo. Anytime someone registers for DMZ Europe with this code on <a href="http://datamodelingzone.com/ProgramEurope.aspx">DataModelingZone.com</a> or <a href="http://www.datamodelingzone.eu/2013/join">DataModelingZone.eu</a>, you will receive 100 Euros off the registration price. For every two people that register using your code, one student from a local university will be allowed to register for the conference for free. The promotion code is active up until the date of the conference and can be used any number of times. Please use the code if you intend to come<div>
<span style="background-color: white; color: #444444; font-family: Calibri, sans-serif; font-size: 15.454545021057129px; line-height: 21.81818199157715px;"><br /></span>
<span style="background-color: white; color: #444444; font-family: Calibri, sans-serif; font-size: 15.454545021057129px; line-height: 21.81818199157715px;"><i><b>Hope to see you there!</b></i></span></div>
Anonymoushttp://www.blogger.com/profile/13164768885306814216noreply@blogger.comtag:blogger.com,1999:blog-3709329816577222029.post-48191619859004267252013-06-07T16:05:00.001+02:002013-09-13T14:05:25.265+02:00Data Vault vs Anchor Modeling: Who Is The One?<h2>
<img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg_KoA3hIDYwsMat2ituea9wBUb81Ghn6lLGhgMzMctKBJ-srQVWhqXhskA0LbJNdEyNNz-ZezawrvieijCyk91i-eIdSCf9aXHAHu45NybVNlj2r2AZDghDEFqBa7fXAcTkomIfEOkwXk/s1600/neo.gif" /></h2>
<h2>
Introduction</h2>
Yesterday Data Vault and Anchor Modeling went head to head on the <a href="http://www.bi-podium.nl/showcase/next_generation_dwh_modeling/21">DWH Next generation</a> Conference organized by<a href="http://www.bi-podium.nl/"> BI-Podium</a> and <a href="http://www.visservanbaars.nl/">Vissers & van Baars</a> recruitment. It was a great conference with over 300 attendees and lot's of sessions on Anchor Modeling and Data Vault<br />
<br />
<br />
<img height="184" src="http://www.anchormodeling.com/wp-content/uploads/2013/05/Anchor.png" width="640" /><br />
<h2>
</h2>
<h2>
Anchor Modeling</h2>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://www.worldofintelligence.nl/assets/AM_5.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="304" src="http://www.worldofintelligence.nl/assets/AM_5.jpg" width="640" /></a></div>
<a href="http://www.anchormodeling.com/">Anchor Modeling</a> has been invented by Lars Rönnbäck and Olle Regardt. It is a highly normalized anchor style modeling approach that has some aspects of 6NF. It looks somewhat similar to Data Vault, but there are a lot of 'gotcha's'. It is a closed business model driven approach with it's own simple business/information model technique and just like Data Vault uses a small sets of building blocks and adds time to the data. There have been several blogs on Anchor Modeling, by <a class="g-profile" href="http://plus.google.com/115664362120037421509" target="_blank">+Richard Steijn</a> <a href="http://blog.vaiper.nl/dwhblog/2011/07/26/de-charme-van-anchor-modeling/">here</a>, Hennie de Nooijer <a href="http://bifuture.blogspot.nl/2012/02/anchor-modeling.html">here</a> and WorldOfIntelligence <a href="http://www.worldofintelligence.nl/2012/anchor-modeling/">here</a>.<br />
<img height="227" src="http://datavaultglobaltour.com/images/dvpic.jpg" width="320" /><br />
<h2>
Data Vault vs Anchor Modeling</h2>
Lars made a start with comparing Anchor Modeling and Data Vault <a href="http://www.anchormodeling.com/wp-content/uploads/2011/05/AM-and-DV-comparison-chart.pdf">here</a>. Is there any truth to glean from the the specific items, their meaning and the score? Not really, because THE Data Vault flavour does not exist that we can compare with the tight definitions of Anchor Modeling. I found it more the Agile Data Vault style of <a class="g-profile" href="http://plus.google.com/103851005830514593078" target="_blank">+Hans Patrik Hultgren</a> compared to Anchor Modeling. Many of the issues mentioned stem either from (past) best practices or lack of detailed standards on e.g. temporal data management. Most are not essential or irrevocably some differences run much deeper. Interestingly most techniques currently in use in Anchor Modeling(except the annex helper tables for bi-temporal modeling) are also used at (some) Data Vaults.<br />
<h2>
The Hidden Snag: Auditability & Adaptability</h2>
If fact, most aspects of Anchor Modeling we can apply to Data Vault or Anchor Vault as well, but auditability creates some serious issues. Anchor Models can never be guaranteed 100% audtiable in all circumstances, and proving Anchor Models are semantic equivalent with (arbitrary) source models requires some serious additional effort. This also means the transformations and loading routines have different issues that with a Data Vault, esp. on complex non auditable transformations (e.g. time line repairs). The flexibility of Data Vault flavors allows us to adapt to very different environments without losing the source data. The tightly defined Anchor Models can be easier to use due to their (internal) business model driven nature coupled with their high normalization. All this does not mean that source driven Anchor Models do not exist or are impossible. On the contrary, we see many people designing source driven Anchor Models, just as some create Business Model driven Data Vaults.<br />
<h2>
It's my party and I'll Vault how I want to.</h2>
<div>
It is clear to me that unless we address the issue of formally addressing data integration and source data models with<i> integration modeling</i> comparing Data Vault and Anchor Modeling is not really going to work. Instead we should focus on combining approaches. In fact my presentation on the <a href="http://www.bi-podium.nl/showcase/next_generation_dwh_modeling/21">DWH Next generation</a> Conference showed a nice example of combining Anchor Modeling and Data Vault. As discussed in my post on <a href="http://dm-unseen.blogspot.nl/2012/11/implementation-data-modeling-styles.html">data modeling styles</a> they are just edge cases of a whole family. Since DV needs to work on a vastly larger range of architectures, it cannot be so tightly defined as Anchor Modeling. It is this flexibility that proves that when we look at Data Vault in a flexible way, it should be better 'by definition' at capturing sources in an Achor Style, while Anchor Modeling is better at handling business models in an Anchor Style modeling technique. This is exactly how I see the usage of such patterns, not either/or but used both in the right context.<br />
<h2>
An Example</h2>
Here is the example to show we can use Anchor Modeling and Data Vault together effectively.<br />
<h4>
Challenge</h4>
<div style="direction: ltr; margin-bottom: 0pt; margin-left: 0.3in; margin-top: 6pt; text-indent: -0.3in; unicode-bidi: embed; word-break: normal;">
<span style="color: #d34817; font-family: 'Wingdings 2';"></span><span style="font-family: 'Trebuchet MS'; vertical-align: baseline;">Integrate disparate Business Keys</span><br />
<span style="color: #ff6566; font-family: 'Wingdings 2';">¡</span><span style="color: #6c6c6c; font-family: 'Trebuchet MS';">Keys not integrated</span></div>
<div class="O1" style="direction: ltr; margin-bottom: 0pt; margin-left: 0.57in; margin-top: 5pt; text-indent: -0.25in; unicode-bidi: embed; word-break: normal;">
<span style="color: #ff6566; font-family: 'Wingdings 2';">¡</span><span style="color: #6c6c6c; font-family: 'Trebuchet MS';">No Master Key</span><br />
<span style="color: #ff6566; font-family: 'Wingdings 2';">¡</span><span style="color: #6c6c6c; font-family: 'Trebuchet MS';">Want a 1-1 time variant mapping between system keys</span><br />
<span style="color: #ff6566; font-family: 'Wingdings 2';">¡</span><span style="color: #6c6c6c; font-family: 'Trebuchet MS';">Internal de-duplication should be possible</span><br />
<span style="color: #ff6566; font-family: 'Wingdings 2';">¡</span><span style="color: #6c6c6c; font-family: 'Trebuchet MS';">Business Rule Driven mappings</span><br />
<span style="color: #ff6566; font-family: 'Wingdings 2';">¡</span><span style="color: #6c6c6c; font-family: 'Trebuchet MS';">Efficient and flexible implementation</span><br />
<h4>
Resulting Model</h4>
</div>
</div>
The result of this challenge is the following model. It contains 3 normal hubs (with corresponding sats and links not shown). It contains 3 1-1 key mapping ties (end dated links) that register which Business Keys need to be unified. It contains 1 Anchor (a Business Key-less/Empty Hub) that connects it all together. Since none of the source keys is a true business key, and we do not want to invent our own, an Anchor is an ideal construct to use here since it does not contain a business key(just a surrogate key). We assume here that we have (complex) business rules that allow us to map the different keys to the central Anchor.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiIcZVJGojVZ0aahY8e7LJvUFZq7G8uS7HzoleYgWBAudMVK1yLkHBZ_KManJJl6bt8PJCbT8Uhln97z5LIUKnGJa-3KHxNhmqtgjtgH5GltJ15ySJ24WKaQcNZUr8cpJZji6oZ2n1Lnwdn/s1600/DV+AM+diag.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="540" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiIcZVJGojVZ0aahY8e7LJvUFZq7G8uS7HzoleYgWBAudMVK1yLkHBZ_KManJJl6bt8PJCbT8Uhln97z5LIUKnGJa-3KHxNhmqtgjtgH5GltJ15ySJ24WKaQcNZUr8cpJZji6oZ2n1Lnwdn/s640/DV+AM+diag.png" width="640" /></a></div>
<div class="O1" style="direction: ltr; margin-bottom: 0pt; margin-left: 0.57in; margin-top: 5pt; text-indent: -0.25in; unicode-bidi: embed; word-break: normal;">
<span style="color: #6c6c6c; font-family: 'Trebuchet MS';"><br />
</span></div>
<div class="O1" style="direction: ltr; margin-bottom: 0pt; margin-left: 0.57in; margin-top: 5pt; text-indent: -0.25in; unicode-bidi: embed; word-break: normal;">
<div style="direction: ltr; margin-bottom: 0pt; margin-left: 0.3in; margin-top: 6pt; text-indent: -0.3in; unicode-bidi: embed; word-break: normal;">
<h4>
<span style="color: #d34817; font-family: 'Wingdings 2';"></span>Solution</h4>
<span style="font-family: 'Trebuchet MS'; vertical-align: baseline;">Classical Single BK Source Driven Hubs</span></div>
<div class="O1" style="direction: ltr; margin-bottom: 0pt; margin-left: 0.57in; margin-top: 5pt; text-indent: -0.25in; unicode-bidi: embed; word-break: normal;">
<span style="color: #ff6566; font-family: 'Wingdings 2';">¡</span><span style="color: #6c6c6c; font-family: 'Trebuchet MS';">No Multi key or changing key etc. so no Anchor required</span></div>
<div style="direction: ltr; margin-bottom: 0pt; margin-left: 0.3in; margin-top: 6pt; text-indent: -0.3in; unicode-bidi: embed; word-break: normal;">
<span style="color: #d34817; font-family: 'Wingdings 2';"></span><span style="font-family: 'Trebuchet MS'; vertical-align: baseline;">Efficient 1-1 Key Maps/Ties</span></div>
<div class="O1" style="direction: ltr; margin-bottom: 0pt; margin-left: 0.57in; margin-top: 5pt; text-indent: -0.25in; unicode-bidi: embed; word-break: normal;">
<span style="color: #ff6566; font-family: 'Wingdings 2';">¡</span><span style="color: #6c6c6c; font-family: 'Trebuchet MS';">Efficient</span></div>
<div class="O1" style="direction: ltr; margin-bottom: 0pt; margin-left: 0.57in; margin-top: 5pt; text-indent: -0.25in; unicode-bidi: embed; word-break: normal;">
<span style="color: #ff6566; font-family: 'Wingdings 2';">¡</span><span style="color: #6c6c6c; font-family: 'Trebuchet MS';">Business Rule Driven, so stable cardinality</span></div>
<div class="O1" style="direction: ltr; margin-bottom: 0pt; margin-left: 0.57in; margin-top: 5pt; text-indent: -0.25in; unicode-bidi: embed; word-break: normal;">
<span style="color: #ff6566; font-family: 'Wingdings 2';">¡</span><span style="color: #6c6c6c; font-family: 'Trebuchet MS';">Dynamic and traceable</span></div>
<div style="direction: ltr; margin-bottom: 0pt; margin-left: 0.3in; margin-top: 6pt; text-indent: -0.3in; unicode-bidi: embed; word-break: normal;">
<span style="color: #d34817; font-family: 'Wingdings 2';"></span><span style="font-family: 'Trebuchet MS'; vertical-align: baseline;">Empty Hub/Anchor to tie all Hubs to</span></div>
<div class="O1" style="direction: ltr; margin-bottom: 0pt; margin-left: 0.57in; margin-top: 5pt; text-indent: -0.25in; unicode-bidi: embed; word-break: normal;">
<span style="color: #ff6566; font-family: 'Wingdings 2';">¡</span><span style="color: #6c6c6c; font-family: 'Trebuchet MS';">No “Master” Business Key required</span></div>
<h4>
Architecture</h4>
The Architecture we see emerging clearly uses Data Vault for capturing sources, while it will use Anchor like constructs for connecting them together in a slightly more flexible way that Data Vault does. This way both techniques are used where their strengths lies.</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiGWOCGGtjMRC-26m4bm_cfCvJL0Rt_NJ9KkCXnYaRCdyjCBktlf3tm5Lfrp-ljviwpJi1P4G6I6u9sdIgD_7mc7UToYAe95hxQOQgHCvGKjyT0ciEt5xZBYkgUqokyA_cQx82I6vaI32bA/s1600/DV+AM+ARCH.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="430" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiGWOCGGtjMRC-26m4bm_cfCvJL0Rt_NJ9KkCXnYaRCdyjCBktlf3tm5Lfrp-ljviwpJi1P4G6I6u9sdIgD_7mc7UToYAe95hxQOQgHCvGKjyT0ciEt5xZBYkgUqokyA_cQx82I6vaI32bA/s640/DV+AM+ARCH.png" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<h2>
A Unifying Style</h2>
<div>
While advances in Data Vault and Anchor Modeling are nice, i'd like to state that choice between and adaptability of the methods/approaches to circumstances by practitioners is also important. But for this we need to understand all the detailed differences between all these modeling techniques. Especially when we want to combine them. If we want this then we're back at a generic Information modeling or logical modeling technique to unify all Data Vault and Anchor Modeling styles. While Data Vault and Anchor Modeling try to seriously simplify the construction of Data Warehousing within their won context, WE still need to evaluate the approaches in our clients context. For this we need all the detail, so we can make a informed, independent, correct decision on which techniques to use. Alas, with Anchor Modeling and Data Vault using their own nomenclature, this does NOT make it easier for experts to understand and trade-off methodologies. Only the 'workers' doing the implementation have it far easier once an architecture and methodology has been chosen and automated. This realization was one of the key drivers for the <a href="http://dm-unseen.blogspot.nl/2012/09/introducing-matter-advanced-data.html">MATTER </a>educational program and also my research in unifying Anchor Modeling and Data Vault modeling using Fact Oriented information modeling and model to model transformations using <a href="http://www.fco-im.nl/">FCO-IM</a>.</div>
<div>
<br /></div>
<div>
In the end, Chosen Ones only exists in stories. For us mortals, as Codd said, only correct, consistent and complete and utter information hiding will save us from having to understand all these modeling techniques. In the mean time we'll be forced to thoroughly understand all aspects of data modeling, be it abstract information models or specific implementation modeling styles. <sigh><i><sigh></sigh></i></sigh></div>
<div>
<br /></div>
Anonymoushttp://www.blogger.com/profile/13164768885306814216noreply@blogger.com0tag:blogger.com,1999:blog-3709329816577222029.post-90155404333079042612013-06-01T12:09:00.000+02:002013-09-13T14:05:25.308+02:00Repeating the Past: Repairing your history in with temporal data.<div style="text-align: center;">
<img alt="File:George Santayana.jpg" src="http://upload.wikimedia.org/wikipedia/commons/thumb/2/2a/George_Santayana.jpg/492px-George_Santayana.jpg" /></div>
<div style="text-align: center;">
<span style="font-family: Times, Times New Roman, serif; font-size: large;">Those that forget the past are Condemned to repeat it</span></div>
<div style="text-align: center;">
<span class="mw-cite-backlink" style="font-family: sans-serif; font-size: 12px; line-height: 17.265625px; text-align: left;"><b><a href="http://en.wikipedia.org/wiki/George_Santayana#cite_ref-2" style="background-image: none; background-position: initial initial; background-repeat: initial initial; color: #0b0080; text-decoration: none;">^</a></b></span><span style="background-color: #ddeeff; font-family: sans-serif; font-size: 12px; line-height: 17.265625px; text-align: left;"> </span><span class="reference-text" style="font-family: sans-serif; font-size: 12px; line-height: 17.265625px; text-align: left;">George Santayana (1905) <i>Reason in Common Sense</i>, volume 1 of <i><a href="http://en.wikipedia.org/wiki/The_Life_of_Reason" style="background-image: none; background-position: initial initial; background-repeat: initial initial; color: #0b0080; text-decoration: none;" title="The Life of Reason">The Life of Reason</a></i></span></div>
<br />
<h2>
Introduction</h2>
When you forget the past, you are condemned to repeat it. This also holds true when you model history and history of history in your database. When you recreate a transaction history in a fully temporal information system with loading data from a source that does not record one you are alas forgetting the past. While there is no way to reconstruct the pas from thin air, when the source information system maintains a valid timeline (a user controllable timeline that among others allows for backdating), you can still recreate an approximation of the past by repeating your valid timeline into your transaction timeline.<br />
<div>
<h2>
Forgetting the past</h2>
<div>
<h3>
Temporal Data</h3>
<a href="http://en.wikipedia.org/wiki/Temporal_database">Temporal Databases</a> or temporal data modeling techniques allow you to record timelines. You can record when somebody lived at a certain address. We have 2 different classes of timelines; those that are connected to our own time, usually called logging, transaction, actual or asserted time, and time fully controllable by the end users, usually called valid, stated or user time. We talk about bi-temporal time when data is registered against 2 timelines, of each class one.<br />
<br />
<h3>
Data warehousing and logging</h3>
</div>
<div>
Data warehouses that log all data (implement a transaction/logged timeline) complement source systems that only register stated time so that they record a bi-temporal time. This is typically done with e.g. a Data Vault. Of course, a Data vault can never show the logged time for the past that it did not log, even if the source recorded a stated/valid time. However, we can create an approximation of the past with the source stated time.</div>
<h2>
Repeating the past</h2>
<div>
There are 2 strategies to repair the past, extension of the first recorded occurence into the past, and transposing the source valid timeline to the past. The first strategy is simple and can always be applied, but is a gross simplification. The second strategy only works when there is a valid timeline of the past. It assumes that this valid timeline is a 1-1 with the transaction timeline.<br />
<h2>
Example</h2>
<div>
Lets show a detailed example on how the repair the past by repeating it. we will show the second strategy, the first is left as an exercise to the reader. We start with a source table example that registers a stated and logged timeline for a certain value in the Data warehouse. We assume the source has been registering the data for some time. We also assume that for the past there is an 1-1 between the stated and logged time. This is a serious assumption, and the reconstructed past is certainly not auditable in the sense that it has been properly logged. We start processing the source data at time t3 (@Tran_hist_start).</div>
<div>
<br /></div>
<div>
Source Table:</div>
<div>
<table border="0" cellpadding="0" cellspacing="0" class="MsoNormalTable" style="border-collapse: collapse; mso-padding-alt: 0cm 0cm 0cm 0cm; mso-yfti-tbllook: 1184; width: 597px;"><tbody>
<tr style="height: 18.15pt; mso-yfti-firstrow: yes; mso-yfti-irow: 0;"> <td style="background: #EAD850; border-bottom: solid white 3.0pt; border: solid white 1.0pt; height: 18.15pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 96.0pt;" valign="top" width="128"><div class="MsoNormal">
<b><u><span lang="EN-US">LOAD DT </span></u></b><o:p></o:p></div>
</td> <td style="background: #EAD850; border-bottom: solid white 3.0pt; border-left: none; border-right: solid white 1.0pt; border-top: solid white 1.0pt; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"><div class="MsoNormal">
<b><span lang="EN-US">END DT </span></b><o:p></o:p></div>
</td> <td style="background: #EAD850; border-bottom: solid white 3.0pt; border-left: none; border-right: solid white 1.0pt; border-top: solid white 1.0pt; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 102.0pt;" valign="top" width="136"><div class="MsoNormal">
<b><u><span lang="EN-US">VALID FROM_DT </span></u></b><o:p></o:p></div>
</td> <td style="background: #EAD850; border-bottom: solid white 3.0pt; border-left: none; border-right: solid white 1.0pt; border-top: solid white 1.0pt; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 91.0pt;" valign="top" width="121"><div class="MsoNormal">
<b><span lang="EN-US">VALID TO_DT </span></b><o:p></o:p></div>
</td> <td style="background: #EAD850; border-bottom: solid white 3.0pt; border-left: none; border-right: solid white 1.0pt; border-top: solid white 1.0pt; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"><div class="MsoNormal">
<b><span lang="EN-US">VALUE </span></b><o:p></o:p></div>
</td> </tr>
<tr style="height: 18.15pt; mso-yfti-irow: 1;"> <td style="background: #FFC000; border-top: none; border: solid white 1.0pt; height: 18.15pt; mso-border-top-alt: solid white 3.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 96.0pt;" valign="top" width="128"><div class="MsoNormal">
<span lang="EN-US">T3 </span><o:p></o:p></div>
</td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 3.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"><div class="MsoNormal">
<span lang="EN-US">T4 </span><o:p></o:p></div>
</td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 3.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 102.0pt;" valign="top" width="136"><div class="MsoNormal">
<span lang="EN-US">T0 </span><o:p></o:p></div>
</td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 3.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 91.0pt;" valign="top" width="121"><div class="MsoNormal">
<span lang="EN-US">T1 </span><o:p></o:p></div>
</td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 3.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"><div class="MsoNormal">
<span lang="EN-US">1 </span><o:p></o:p></div>
</td> </tr>
<tr style="height: 18.15pt; mso-yfti-irow: 2;"> <td style="background: #FFC000; border-top: none; border: solid white 1.0pt; height: 18.15pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 96.0pt;" valign="top" width="128"><div class="MsoNormal">
<span lang="EN-US">T3 </span><o:p></o:p></div>
</td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"><div class="MsoNormal">
<span lang="EN-US">- </span><o:p></o:p></div>
</td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 102.0pt;" valign="top" width="136"><div class="MsoNormal">
<span lang="EN-US">T1 </span><o:p></o:p></div>
</td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 91.0pt;" valign="top" width="121"><div class="MsoNormal">
<span lang="EN-US">T2 </span><o:p></o:p></div>
</td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"><div class="MsoNormal">
<span lang="EN-US">3 </span><o:p></o:p></div>
</td> </tr>
<tr style="height: 18.15pt; mso-yfti-irow: 3;"> <td style="background: #FFC000; border-top: none; border: solid white 1.0pt; height: 18.15pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 96.0pt;" valign="top" width="128"><div class="MsoNormal">
<span lang="EN-US">T3</span><o:p></o:p></div>
</td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"><div class="MsoNormal">
<span lang="EN-US">T5 </span><o:p></o:p></div>
</td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 102.0pt;" valign="top" width="136"><div class="MsoNormal">
<span lang="EN-US">T2 </span><o:p></o:p></div>
</td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 91.0pt;" valign="top" width="121"><div class="MsoNormal">
<span lang="EN-US">T3</span><o:p></o:p></div>
</td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"><div class="MsoNormal">
<span lang="EN-US">4 </span><o:p></o:p></div>
</td> </tr>
<tr style="height: 18.15pt; mso-yfti-irow: 4;"> <td style="background: #FFC000; border-top: none; border: solid white 1.0pt; height: 18.15pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 96.0pt;" valign="top" width="128"><div class="MsoNormal">
<span lang="EN-US">T3</span><o:p></o:p></div>
</td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"><div class="MsoNormal">
<span lang="EN-US">- </span><o:p></o:p></div>
</td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 102.0pt;" valign="top" width="136"><div class="MsoNormal">
<span lang="EN-US">T3 </span><o:p></o:p></div>
</td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 91.0pt;" valign="top" width="121"><div class="MsoNormal">
<span lang="EN-US">- </span><o:p></o:p></div>
</td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"><div class="MsoNormal">
<span lang="EN-US">6 </span><o:p></o:p></div>
</td> </tr>
<tr style="height: 18.15pt; mso-yfti-irow: 5;"> <td style="background: #FFC000; border-top: none; border: solid white 1.0pt; height: 18.15pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 96.0pt;" valign="top" width="128"><div class="MsoNormal">
<span lang="EN-US">T4</span><o:p></o:p></div>
</td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"><div class="MsoNormal">
<span lang="EN-US">- </span><o:p></o:p></div>
</td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 102.0pt;" valign="top" width="136"><div class="MsoNormal">
<span lang="EN-US">T0 </span><o:p></o:p></div>
</td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 91.0pt;" valign="top" width="121"><div class="MsoNormal">
<span lang="EN-US">T1 </span><o:p></o:p></div>
</td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"><div class="MsoNormal">
<span lang="EN-US">2 </span><o:p></o:p></div>
</td> </tr>
<tr style="height: 18.15pt; mso-yfti-irow: 6;"> <td style="background: #FFC000; border-top: none; border: solid white 1.0pt; height: 18.15pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 96.0pt;" valign="top" width="128"><div class="MsoNormal">
<span lang="EN-US">T5</span><o:p></o:p></div>
</td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"><div class="MsoNormal">
<span lang="EN-US">- </span><o:p></o:p></div>
</td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 102.0pt;" valign="top" width="136"><div class="MsoNormal">
<span lang="EN-US">T2 </span><o:p></o:p></div>
</td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 91.0pt;" valign="top" width="121"><div class="MsoNormal">
<span lang="EN-US">T3 </span><o:p></o:p></div>
</td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"><div class="MsoNormal">
<span lang="EN-US">5</span><o:p></o:p></div>
</td> </tr>
<tr style="height: 18.15pt; mso-yfti-irow: 7; mso-yfti-lastrow: yes;"> <td style="background: #FFC000; border-top: none; border: solid white 1.0pt; height: 18.15pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 96.0pt;" valign="top" width="128"></td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"></td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 102.0pt;" valign="top" width="136"></td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 91.0pt;" valign="top" width="121"></td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"></td> </tr>
</tbody></table>
</div>
<div>
<br /></div>
<div>
This Source Table in a picture:</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiQbBPZNMBepS20xGryG90TBdIsCDMiOy-CJYTWxDS5IDjAVn7dCBcuHVme22eddrdRjc59qYX23gwYDwMcX_0d4vHr-FUTH-fhfXweRNBPddrete5nKrOshxFsoanNiQAvNopKDzeRfrhf/s1600/hist+of+hist+example.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="409" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiQbBPZNMBepS20xGryG90TBdIsCDMiOy-CJYTWxDS5IDjAVn7dCBcuHVme22eddrdRjc59qYX23gwYDwMcX_0d4vHr-FUTH-fhfXweRNBPddrete5nKrOshxFsoanNiQAvNopKDzeRfrhf/s640/hist+of+hist+example.png" width="640" /></a></div>
<div>
<br /></div>
We can for example read the time points as months, with T0 being January. T1=February and so on. The Value can be seen as e.g. Status values, 1='Created',2='Open',.. 6='Destroyed'. We can read the lower boxes in the picture as: we started to record in april that from jan to feb the status was Created.In May we recorded that in the the jan to feb time frame the status was 'Open'. This is currently still true (true until end of time).<br />
<br />
<h3>
Transposed Situation</h3>
<div>
Lets transpose the valid timeline at time T3(@Tran_hist_start) and use that as a basis to reconstruct the unknown past. We assume here that in that past we don't know the future as known at T3.</div>
<div>
Our bi temporal table will now look like this:</div>
<div>
<br /></div>
Table Values:<br />
<table border="0" cellpadding="0" cellspacing="0" class="MsoNormalTable" style="border-collapse: collapse; mso-padding-alt: 0cm 0cm 0cm 0cm; mso-yfti-tbllook: 1184; width: 597px;"><tbody>
<tr style="height: 18.15pt; mso-yfti-firstrow: yes; mso-yfti-irow: 0;"> <td style="background: #EAD850; border-bottom: solid white 3.0pt; border: solid white 1.0pt; height: 18.15pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 96.0pt;" valign="top" width="128"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<b><u><span lang="EN-US" style="color: white; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">LOAD DT </span></u></b><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> <td style="background: #EAD850; border-bottom: solid white 3.0pt; border-left: none; border-right: solid white 1.0pt; border-top: solid white 1.0pt; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<b><span lang="EN-US" style="color: white; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">END DT </span></b><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> <td style="background: #EAD850; border-bottom: solid white 3.0pt; border-left: none; border-right: solid white 1.0pt; border-top: solid white 1.0pt; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 113.0pt;" valign="top" width="151"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<b><u><span lang="EN-US" style="color: white; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">VALID FROM_DT </span></u></b><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> <td style="background: #EAD850; border-bottom: solid white 3.0pt; border-left: none; border-right: solid white 1.0pt; border-top: solid white 1.0pt; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<b><span lang="EN-US" style="color: white; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">VALID_ TO_DT </span></b><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> <td style="background: #EAD850; border-bottom: solid white 3.0pt; border-left: none; border-right: solid white 1.0pt; border-top: solid white 1.0pt; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<b><span lang="EN-US" style="color: white; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">VALUE </span></b><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> </tr>
<tr style="height: 18.15pt; mso-yfti-irow: 1;"> <td style="background: #CEBCA4; border-top: none; border: solid white 1.0pt; height: 18.15pt; mso-border-top-alt: solid white 3.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 96.0pt;" valign="top" width="128"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: #394350; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">T0 </span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> <td style="background: #CEBCA4; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 3.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: #394350; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">T1 </span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> <td style="background: #CEBCA4; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 3.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 113.0pt;" valign="top" width="151"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: #394350; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">T0 </span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> <td style="background: #CEBCA4; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 3.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: #394350; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">- </span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> <td style="background: #CEBCA4; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 3.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: #394350; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">1 </span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> </tr>
<tr style="height: 18.15pt; mso-yfti-irow: 2;"> <td style="background: #CEBCA4; border-top: none; border: solid white 1.0pt; height: 18.15pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 96.0pt;" valign="top" width="128"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: #394350; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">T1 </span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> <td style="background: #CEBCA4; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: #394350; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">T3 </span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> <td style="background: #CEBCA4; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 113.0pt;" valign="top" width="151"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: #394350; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">T0 </span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> <td style="background: #CEBCA4; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: #394350; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">T1 </span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> <td style="background: #CEBCA4; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: #394350; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">1 </span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> </tr>
<tr style="height: 18.15pt; mso-yfti-irow: 3;"> <td style="background: #866C49; border-top: none; border: solid white 1.0pt; height: 18.15pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 96.0pt;" valign="top" width="128"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: white; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">T1 </span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> <td style="background: #866C49; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: white; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">T2 </span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> <td style="background: #866C49; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 113.0pt;" valign="top" width="151"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: white; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">T1 </span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> <td style="background: #866C49; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: white; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">- </span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> <td style="background: #866C49; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: white; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">3 </span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> </tr>
<tr style="height: 18.15pt; mso-yfti-irow: 4;"> <td style="background: #866C49; border-top: none; border: solid white 1.0pt; height: 18.15pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 96.0pt;" valign="top" width="128"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: white; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">T2 </span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> <td style="background: #866C49; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: white; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">T3 </span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> <td style="background: #866C49; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 113.0pt;" valign="top" width="151"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: white; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">T1 </span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> <td style="background: #866C49; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: white; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">T2 </span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> <td style="background: #866C49; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: white; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">3 </span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> </tr>
<tr style="height: 18.15pt; mso-yfti-irow: 5;"> <td style="background: #866C49; border-top: none; border: solid white 1.0pt; height: 18.15pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 96.0pt;" valign="top" width="128"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: white; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">T2 </span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> <td style="background: #866C49; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: white; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">T3 </span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> <td style="background: #866C49; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 113.0pt;" valign="top" width="151"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: white; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">T2 </span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> <td style="background: #866C49; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: white; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">- </span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> <td style="background: #866C49; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: white; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">4 </span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> </tr>
<tr style="height: 18.15pt; mso-yfti-irow: 6;"> <td style="border-top: none; border: solid white 1.0pt; height: 18.15pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 96.0pt;" valign="top" width="128"></td> <td style="border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"></td> <td style="border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 113.0pt;" valign="top" width="151"></td> <td style="border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"></td> <td style="border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"></td> </tr>
<tr style="height: 18.15pt; mso-yfti-irow: 7;"> <td style="background: #FFC000; border-top: none; border: solid white 1.0pt; height: 18.15pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 96.0pt;" valign="top" width="128"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: #394350; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">T3 </span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: #394350; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">T4 </span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 113.0pt;" valign="top" width="151"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: #394350; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">T0 </span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: #394350; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">T1 </span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: #394350; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">1 </span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> </tr>
<tr style="height: 18.15pt; mso-yfti-irow: 8;"> <td style="background: #FFC000; border-top: none; border: solid white 1.0pt; height: 18.15pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 96.0pt;" valign="top" width="128"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: #394350; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">T3 </span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: #394350; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">- </span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 113.0pt;" valign="top" width="151"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: #394350; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">T1 </span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: #394350; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">T2 </span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: #394350; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">3 </span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> </tr>
<tr style="height: 18.15pt; mso-yfti-irow: 9;"> <td style="background: #FFC000; border-top: none; border: solid white 1.0pt; height: 18.15pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 96.0pt;" valign="top" width="128"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: #394350; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">T3</span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: #394350; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">T5 </span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 113.0pt;" valign="top" width="151"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: #394350; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">T2 </span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: #394350; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">T5 </span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: #394350; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">4 </span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> </tr>
<tr style="height: 18.15pt; mso-yfti-irow: 10;"> <td style="background: #FFC000; border-top: none; border: solid white 1.0pt; height: 18.15pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 96.0pt;" valign="top" width="128"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: #394350; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">T3</span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: #394350; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">- </span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 113.0pt;" valign="top" width="151"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: #394350; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">T3 </span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: #394350; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">- </span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: #394350; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">6 </span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> </tr>
<tr style="height: 18.15pt; mso-yfti-irow: 11;"> <td style="background: #FFC000; border-top: none; border: solid white 1.0pt; height: 18.15pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 96.0pt;" valign="top" width="128"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: #394350; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">T4</span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: #394350; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">- </span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 113.0pt;" valign="top" width="151"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: #394350; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">T0 </span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: #394350; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">T1 </span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: #394350; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">2 </span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> </tr>
<tr style="height: 18.15pt; mso-yfti-irow: 12;"> <td style="background: #FFC000; border-top: none; border: solid white 1.0pt; height: 18.15pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 96.0pt;" valign="top" width="128"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: #394350; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">T5</span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: #394350; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">- </span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 113.0pt;" valign="top" width="151"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: #394350; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">T2 </span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: #394350; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">T3 </span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="color: #394350; font-family: "Eras Medium ITC","sans-serif"; font-size: 18.0pt; mso-ansi-language: EN-US; mso-bidi-font-family: Arial; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL; mso-font-kerning: 12.0pt;">5</span><span style="font-family: "Arial","sans-serif"; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL;"><o:p></o:p></span></div>
</td> </tr>
<tr style="height: 18.15pt; mso-yfti-irow: 13; mso-yfti-lastrow: yes;"> <td style="background: #FFC000; border-top: none; border: solid white 1.0pt; height: 18.15pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 96.0pt;" valign="top" width="128"></td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"></td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 113.0pt;" valign="top" width="151"></td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"></td> <td style="background: #FFC000; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 18.15pt; mso-border-left-alt: solid white 1.0pt; mso-border-top-alt: solid white 1.0pt; padding: 3.6pt 7.2pt 3.6pt 7.2pt; width: 79.0pt;" valign="top" width="105"></td> </tr>
</tbody></table>
<br />
<br />
a Visual representation of the data and the transformation:<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjQZqHnltALOl1Xv1fi9HBkyolXBqyY4mjkSsmZYJ0MQZktNsHAVE-5Kw-68n9oetOk5tHqluAXICpSysoBNCG65dF7RsM94I04EP84CnT_7ZgAktBawFyJHHyH1Tl9ui7oO_4Fv4OiwW8Z/s1600/hist+of+hist+example+transpose.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="457" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjQZqHnltALOl1Xv1fi9HBkyolXBqyY4mjkSsmZYJ0MQZktNsHAVE-5Kw-68n9oetOk5tHqluAXICpSysoBNCG65dF7RsM94I04EP84CnT_7ZgAktBawFyJHHyH1Tl9ui7oO_4Fv4OiwW8Z/s640/hist+of+hist+example+transpose.png" width="640" /></a></div>
<br />
Let's look at the 'SQL' Code to produe this past. We see that we create set of vertical oriented timeslices and a set of horizontal timelsieces to fill in the missing past.<br />
<br />
Pseudo SQL Code<br />
<br />
Vertical History:<br />
<script src="http://pastebin.com/embed_js.php?i=88bxLnWe"></script><br /></div>
Horizontal History<br />
<script src="http://pastebin.com/embed_js.php?i=uPCGJxyG"></script><br />
Total History:<br />
<script src="http://pastebin.com/embed_js.php?i=qQBWtdGj"></script><br />
<h2>
Conclusion</h2>
<div>
Assuming the user controlled timeline (valid timeline, stated timeline) does not diverge significantly from the unknown transaction/logged timeline you can reconstruct the non logged past in a information system/data warehouse by pivoting and substituting the target transaction timeline with the source user controlled timeline for the missing past. By repeating this information we can 'repair the past'. This is especially handy when querying the past. Note that we are now repeating the past from the present because we 'forgot' to register this past in the first place. The question if we should actually persist this past or just derive this information at query time is a separate performance discussion.</div>
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br /></div>
Anonymoushttp://www.blogger.com/profile/13164768885306814216noreply@blogger.com0tag:blogger.com,1999:blog-3709329816577222029.post-83608694296248244702013-05-10T15:30:00.002+02:002013-09-13T14:05:25.296+02:00Data Vault Introductions<h2>
<img src="http://www.clipartheaven.com/clipart/business_&_office/cartoons_(a_-_c)/business_introduction_1.gif" /></h2>
<h2>
Introduction</h2>
No, this is not a post about introducing Data Vault, but a post referencing materials that introduce Data Vault. I've collected blog posts, articles and book references for you to browse to get to grips with Data Vault. If after all this info you still have questions left (or maybe even more than you started with), come back here and I'll see what I can do.<br />
<h2>
Blogs with Data Vault introductions and overviews</h2>
The following blogs have entries for introductions to Data Vault. <a class="g-profile" href="http://plus.google.com/103851005830514593078" target="_blank">+Hans Patrik Hultgren</a> has written a blogpost <a href="http://hanshultgren.wordpress.com/2011/03/25/data-vault-core-components/">here</a>. Martin Ouellet did a good blogpost about Data vault on <a href="http://martin-ouellet.blogspot.nl/2013/01/data-vault-modelling.html">his blog</a>. <a class="g-profile" href="http://plus.google.com/102489399758666953357" target="_blank">+Daniel Linstedt</a> has a good post on it <a href="http://danlinstedt.com/about/data-vault-basics/">here</a>, and an additional one <a href="http://biblogg.no/2013/02/15/data-vault-introduction-and-overview/">here</a>. He has also a slideshare presentation <a href="http://www.slideshare.net/dlinstedt/introduction-to-data-vault-dama-oregon-2012">here</a>. Several blogs about Data vault from the people at <a href="http://www.lucruminc.com/">lucrum </a>can be found <a href="http://makingdatameaningful.com/tag/data-vault/">here</a>. There are also some blog entries on AgileDSS describing <a href="http://www.agiledss.com/en/blog/data-vault-whats-know-about-data-warehouse-vision.html">basic Data Vault (modeling) and architecture</a>.<br />
<h2>
Books on Data Vault</h2>
<a href="http://learndatavault.com/super-charge-your-data-warehouse/">Supercharge your Data warehouse</a> by <a class="g-profile" href="http://plus.google.com/102489399758666953357" target="_blank">+Daniel Linstedt</a>, <a href="https://www.createspace.com/4042432">Modeling the Agile Data Warehouse with Data Vault</a> by <a class="g-profile" href="http://plus.google.com/103851005830514593078" target="_blank">+Hans Patrik Hultgren</a>.<br />
<h2>
Introduction Papers for Data Vault.</h2>
<div>
The <a href="http://www.tdan.com/view-articles/5054/" target="_blank">TDAN Articles</a> are quite old but still readable. <a class="g-profile" href="http://plus.google.com/100388796966993474179" target="_blank">+Kent Graziano</a> has a <a href="http://kentgraziano.files.wordpress.com/2012/02/introduction-to-data-vault-modeling.pdf">paper</a>, <a href="http://www.slideshare.net/kgraziano/introduction-to-data-vault-modeling">slideshare </a>intro as well. <a class="g-profile" href="http://plus.google.com/103851005830514593078" target="_blank">+Hans Patrik Hultgren</a> has a <a href="http://hanshultgren.files.wordpress.com/2012/09/data-vault-modeling-guide.pdf">DV into paper</a> as well. <a href="http://www.nippur.nl/">Nippur </a>has also an introduction presentation online <a href="http://www.nippur.nl/Portals/0/artikelen/QOSQO%20Data%20Vault%20Introduction.pdf">here</a>. My own <a href="http://sqlbits.com/">SQLBits </a> presentation is also still online <a href="http://sqlbits.com/Sessions/Event8/Data_Vault_An_introduction_to_the_new_datawarehouse_supermodel">here</a>! Wikipedia also has a <a href="http://en.wikipedia.org/wiki/Data_Vault_Modeling">Data Vault article</a>. <a class="g-profile" href="http://plus.google.com/107368119373371874228" target="_blank">+Roelant Vos</a> has written an article about <a href="http://roelantvos.com/blog/wp-content/uploads/2012/05/Data-Vault-and-the-Truth-about-the-Enterprise-Data-Warehouse.pdf">Data Vault</a>. A presentation of his can be found <a href="http://www.slideserve.com/darrin/data-vault">here</a>.</div>
<h2>
Courses introducing Data vault</h2>
<div>
See my <a href="http://dm-unseen.blogspot.nl/2013/02/data-vault-agenda-2013.html">Data Vault Agenda</a> for more information. Except courses from the MATTER program most events and courses (also) target the novice audience.</div>
<div>
<h2>
Data Vault Comparisons</h2>
</div>
<div>
<a class="g-profile" href="http://plus.google.com/111743719788883511685" target="_blank">+Stefan Frost</a> blogged on his <a href="http://it.toolbox.com/blogs/bi-aj/">toolbox blog</a> about Data Vault vs. , <a class="g-profile" href="http://plus.google.com/100388796966993474179" target="_blank">+Kent Graziano</a> did a comparison <a href="http://kentgraziano.com/2013/01/20/data-vault-vs-the-world-1-of-3/">here </a>. Hennie de Nooijer did a comparison in his blog <a href="http://bifuture.blogspot.nl/2012/03/four-different-datamodeling-methods.html">here</a>. Wherescape has also a comparison on data vault <a href="http://blog.wherescape.com/technology/comparative-analysis-3nf-vs-dv/">here</a> (slidshare from Wherescape is <a href="https://www.box.com/shared/mic6eo2np68y9fff4y7b">here</a>) The bibackend has a comparison <a href="http://thebibackend.wordpress.com/2012/06/05/thoughts-on-data-vault-vs-star-schemas/">here</a>. Another comparison for Data Vault has been blogged about by <a class="g-profile" href="http://plus.google.com/107368119373371874228" target="_blank">+Roelant Vos</a> <a href="http://roelantvos.com/blog/?p=740">here</a>. A dutch blog by worldofintelligence is <a href="http://www.worldofintelligence.nl/2012/welke-data-architectuur-is-geschikt-voor-uw-informatiebehoefte/">here</a>.</div>
Anonymoushttp://www.blogger.com/profile/13164768885306814216noreply@blogger.com0tag:blogger.com,1999:blog-3709329816577222029.post-19784205444622176602013-05-02T14:50:00.000+02:002013-09-13T14:05:25.227+02:00Data Vault Model Examples<div class="separator" style="clear: both; text-align: center;">
<a href="http://datavaultglobaltour.com/images/dvpic.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="454" src="http://datavaultglobaltour.com/images/dvpic.jpg" width="640" /></a></div>
<h2>
Introduction</h2>
There are several example models on Data Vault around the net. this post tries to list the ones publicly available as blog posts or as downloads.<br />
<h2>
Northwind</h2>
The SQL Server Northwind database has been used as an DV transformation example in the original TDAN Data Vault papers by Dan Linstedt. You can find the paper with Northwind <a href="http://www.tdan.com/view-articles/5155/" target="_blank">here</a>. Another blog where Northwind is used is the wherescape blog <a href="http://blog.wherescape.com/technology/comparative-analysis-3nf-vs-dv/" target="_blank">here</a>. The pdf files of both the <a href="https://docs.google.com/file/d/0B68pRrkVBmiYY0tDTzZubnJGSDQ/edit?usp=sharing" target="_blank">original</a> and the <a href="https://docs.google.com/file/d/0B68pRrkVBmiYY0tDTzZubnJGSDQ/edit?usp=sharing" target="_blank">Data Vault</a> are also available. The Nothrwind database was an example database from Microsoft for it's SQL Server DBMS product. The original database is, like a lot of example databases from DBMS vendors, quite badly designed. For Data Vault this gives some nice issues to solve going to a Data Vault. The given example however is very basic. It ignores the use of reference tables and for examples sake solely creates a link for the orders table where a hub & link would be more apropriate. It als does not assign Data Vault sequence numbers.<br />
<h2>
Adventureworks</h2>
The basic adventureworks can be found on <a href="http://sourceforge.net/projects/open-data-vault/files/Generic%20Data%20Models/" target="_blank">sourceforge</a>. A repaired DDL version can be found on the blog of <a href="http://infosupport.getcolourful.nl/adventureworks-data-vault/" target="_blank">Hans Geurtsen</a>. Others have used Adventureworks database with for example Quipu. See blogs <a href="http://bifuture.blogspot.nl/2012/03/four-different-datamodeling-methods.html" target="_blank">here</a> and <a href="http://bifuture.blogspot.nl/2012/03/four-different-datamodeling-methods.html" target="_blank">here</a>. Some info on the source database can be found <a href="http://www.wilsonmar.com/sql_adventureworks.htm" target="_blank">here</a><br />
<h2>
NDC</h2>
<div>
On <a href="http://sourceforge.net/projects/open-data-vault/files/Generic%20Data%20Models/" target="_blank">Sourceforge </a>there is also a National Drug Code database example created by Dan Linstedt as well as a weblog data vault and a metrics vault.</div>
<div>
<br /></div>
<h2>
TPC-H</h2>
<div>
I myself use the TPC-H model's as examples for Data vault transformations. The source model and information can be found <a href="http://www.tpc.org/tpch/" target="_blank">here</a>. The different <a href="http://dm-unseen.blogspot.nl/2012/09/styles-of-data-vault-modeling-part-i.html" target="_blank">Data Vault model styles</a> <span id="goog_1492074397"></span><span id="goog_1492074398"></span><a href="http://www.blogger.com/"></a>will be introduced in detail on my blog in future blog posts. (I still need to fix them up). They are also used in the MATTER program.</div>
<div>
<br /></div>
<h2>
Other Examples</h2>
<div>
Other Examples of Data Vaults on the web are for example for <a href="http://martin-ouellet.blogspot.nl/2013/02/data-vault-model-mobile-telecom-example.html" target="_blank">mobile</a> solutions. Other people blogged about DV models, like <a href="http://it.toolbox.com/blogs/bi-aj/part-4-modeling-one-model-for-persistence-and-access-with-data-vault-44762" target="_blank">Stefan Frost</a>. Simple examples used to explain Data Vault transformations can be found <a href="http://bukhantsov.org/2012/04/what-is-data-vault/" target="_blank">here</a> and <a href="http://www.dwhautomation.org/data-warehouse-generation-algorithm-explained/" target="_blank">here</a>.</div>
Anonymoushttp://www.blogger.com/profile/13164768885306814216noreply@blogger.com0tag:blogger.com,1999:blog-3709329816577222029.post-45834795483161125012013-04-18T10:25:00.000+02:002013-09-13T14:05:25.258+02:00The Rule & the Raw & the Business (Data) Vault: Data Vault Architecture and Business Rules<h2>
Introduction</h2>
<div>
Every now and then the discussion on derived data, centralization of data logic and the definition of the (Raw) Data Vault pops up. Both <a class="g-profile" href="http://plus.google.com/102489399758666953357" target="_blank">+Daniel Linstedt</a>'s supercharge book, <a class="g-profile" href="http://plus.google.com/103851005830514593078" target="_blank">+Hans Patrik Hultgren</a>'s Agile Date warehousing book and <a class="g-profile" href="http://plus.google.com/115737443390667538288" target="_blank">+Ronald Damhof</a>'s <a href="http://www.linkedin.com/in/ronalddamhof" target="_blank">EDW papers</a> (see the papers section of his his linkedin profile) describe varieties of the concept<b> </b>of storing derived data into a Data Vault oriented EDW. They also blogged about this subject and the terminology regarding the Raw Vault. See <a href="http://prudenza.typepad.com/dwh/2011/02/dan-linstedt-ronald-damhof-lets-be-clear-about-the-raw-data-vault.html" target="_blank">Ronald's</a> (and Dan's) and <a href="http://hanshultgren.wordpress.com/2011/02/20/data-vault-layers-the-raw-vault/" target="_blank">Hans's</a> blog post. In this blog post I'll discuss these views and my (and <a class="g-profile" href="https://plus.google.com/115737443390667538288" target="_blank">+Ronald Damhof</a>'s) take on this architecture pattern.<br />
<h2>
Centralized Logic</h2>
</div>
<div>
Often it makes sense to centralize not only the storage of data, but also some (all?) of the additional logic required to process the data before it goes out to the users/data marts/bi systems. This logic is often classified as 'Business Rules', 'Calculations' or 'Data Quality Rules'.<br />
<h2>
Business Rules?</h2>
</div>
<div>
From an information system perspective a formalized business rule is a constraint expressed on data. This means that also keys and domain definitions are business rules.<br />
We personally prefer to talk about 2 different kinds of business rules: structural and non structural.</div>
<h3>
Structural Business Rules</h3>
<div>
Structural business rules are rules that structure elementary information. They contain the basic building blocks for creating data models. </div>
<div>
<ol>
<li>Elementary structure rules like domain, key, dependency, foreign key/subset constraint and basic entity (relation/fact type).</li>
<li>Model derivation/transformation rules to create new semantical/logical equivalent (elementary) model structures.</li>
</ol>
</div>
<div>
<h3>
Non Structural Business Rules</h3>
</div>
<div>
Non structural business rules define calculations/derivations, constraints & Data Quality rules and structural repair rules. Of those the structural repair rules are the most interesting. These are rules that change/extend date models (create new structural business rules!) based on derived data/calculations. So we have.</div>
<div>
<ol>
<li>Derived data/calculations</li>
<li>Constraints/Data Quality rules</li>
<li>Structural repair rules</li>
</ol>
</div>
<h2>
Derived Data and Business rules</h2>
<div>
We see not a big difference in handling non-structural business rule data and raw (source system) data. A business rule is just a small (source) data model that happens to derive its data from the Data Vault and not externally. It is (normally) loaded in the same manner and into the same kind of objects as with a raw Data Vault. This means from a metadata perspective we can add traceability information, and we have better control over the rule/data/metadata that that of externally sourced information systems, but that's it. Dan's "Business Data Vault" suggests the business is in the lead on that data (and business rule definitions). While this is often the case, business rules can also have a more technical origin like performance (aggregation satellites), so this is a bit suggestive. We prefer the term (Business) Rule Vault instead to emphasize the importance of the rule processing over the exact business definition, specification and naming.<br />
<h2>
The (Business) Rule Vault</h2>
</div>
<div>
For us all DV objects that are only/mainly driven by business rules belong to the Rule Vault. Note that since Rule Vault objects can(and often do) directly relate/depend on entities in the Raw Vault you should not see them as 2 completely different and independent (storage) tiers, they are in the same tier, just 2 different logical (sub)layers within the Data Vault. This separation is just a soft one. We can load the same DV entity from a rule or raw source, either in consecutive order or sometimes even at the same time. Our metadata will keep the <b><i>records </i></b>auditable according to the source, but assigning DV entities exclusively to the Rule or Raw Vault is not always possible or sensible, esp not over time.<br />
<h2>
The Raw (Data) Vault</h2>
The classical source driven but business oriented (Raw)Data Vault, the 'Raw Vault' for short, is the classical Data Vault. It is organized around integration through 'business keys', but also directly related/auditable through to the source (data). We'll forego the discussion here on (Single) Source Data Vaults driven by the primary/surrogate key and debunk Source Data Vaults, also called Stage Data Vaults, in another post.</div>
<h2>
The (Complete) Data Vault: Rule Vault + Raw Vault:</h2>
<div>
So for us the (Complete) 'Data Vault' combines the Business Rule (Data) Vault, the "Rule Vault' for short, and the 'Raw' Data Vault, or Raw Vault.</div>
<h2>
Business (Data) Vault and Business View Perspectives</h2>
<div>
But even with the Rule Vault we still need to connect our business rule results to the business. For this we use the "Business View" concept of <a class="g-profile" href="http://plus.google.com/115737443390667538288" target="_blank">+Ronald Damhof</a> It brings together Rule and Raw Vault objects to present a consistent business view on all the data (<b><i>a </i></b>'truth' in classical EDW thinking). Business Views are (conceptual) (business) data models bereft on detailed structural, temporal and other <i>transformations/derivations</i>. These business views can be based on available enterprise, industry or source system information models, greatly reducing overall data and business rule complexity.<br />
<h3>
Perspectives</h3>
These Business Views can be implemented through <i><b>several </b></i>perspectives in different structures (Dimensional, Normalized and Data Vault!) and with different temporal aspects (now, point-in time, period). We define a Business Data Vault as a <i>Business View perspective</i> in Data Vault <b><i>format</i></b>. These Business Views sit between our Data Vault and our Data Marts as a decoupling layer. This layer is driven by information models managing our different truths as different business views. Since this layer provides a semantic abstraction it is still appropriate even if there is <i>no </i>structure change from the Data Vault to the business view perspective. A Business Data Vault is still a complete Data Vault transformation from a business information model, <i>albeit (</i>usually<i>) a </i><b>fully</b><i> </i><b>virtual </b>one<i>!</i>). Interestingly, of all the perspectives of a Business View, the Data Vault perspective is usually the first and foremost that is implemented.</div>
<div>
<div>
<h2>
Data (Definition) Architecture: Concerning Layers</h2>
</div>
</div>
<div>
When we look at the layers of the overall EDW Data Architecture we see a 3 layered approach, a Data Vault layer concerning storage and processing, a Business View layers concerning abstraction and consistency and an Access layer (Data Mart/Staging Out) concerning access, aggregation and navigation.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjbZ6tQwONiMEQH6GmDgw5TXGQVkg4Ilg252dhsm3DYG5F_CH7RuqXTxZD1ZTjxtudZTH0P6T-_pIjUu4yTdPTq5uYJrMzPbkFLS2P20aaAcu5wSWTMVr-ipf_NwQ0DeNyPy1J58_1kI5tv/s1600/BDV+Perspective.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="513" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjbZ6tQwONiMEQH6GmDgw5TXGQVkg4Ilg252dhsm3DYG5F_CH7RuqXTxZD1ZTjxtudZTH0P6T-_pIjUu4yTdPTq5uYJrMzPbkFLS2P20aaAcu5wSWTMVr-ipf_NwQ0DeNyPy1J58_1kI5tv/s640/BDV+Perspective.png" width="640" /></a></div>
<br /></div>
<h2>
Data Logistics Architecture: Tiering your Data</h2>
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh0Ssz1g6jFbQUSgFw0OoM0emsLVDImt2obSW_bm5qH9CJxKOwj_HVTR9R0zI3am7aY9aT1SoYfrFFJWJNv6oUVlUa6ZLIL5AwyRRsIbYEX5ZfXEyN708OMdhrBCYgoMtHfYKChnDyKPivb/s1600/Tier.png" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh0Ssz1g6jFbQUSgFw0OoM0emsLVDImt2obSW_bm5qH9CJxKOwj_HVTR9R0zI3am7aY9aT1SoYfrFFJWJNv6oUVlUa6ZLIL5AwyRRsIbYEX5ZfXEyN708OMdhrBCYgoMtHfYKChnDyKPivb/s640/Tier.png" width="426" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;"><span style="font-style: italic;">EDW Logistical Functions: Acquiring</span><span style="font-style: italic;">(staging), Storing(Data Vault) and Access</span><span style="font-style: italic;"> and delivery of data.</span></td></tr>
</tbody></table>
<div>
Separate from the data layers we also have the data tiers. These are connected to the data logistics functions like acquiring, storing, propagating and data delivery. From the Data Architecture perspective we want to minimize logistics through maximizing virtualization/derivation. We can infer that at minimum we need 1 data tier (apart from e.g. a staging tier). In practice we can create many tiers in all the data layers. We can even have tiers that cross layers. Since most EDW architectures (inadvertently) combine/entwine these two architecture aspects we'll discuss the connection between tiers and layers in a future blog post.<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
</div>
<h2>
Conclusion</h2>
<div>
Personally we don't make too much of the distinction between Raw and Rule Vault, except in the metadata. We do think a (formal) business view layer (preferably in Data Vault format) is an important, nay essential, part of any Data Vault Architecture. (c)<br />
<br />
<i>©<a class="g-profile" href="https://plus.google.com/115482897209792903822" target="_blank">+Martijn Evers</a> and <a class="g-profile" href="https://plus.google.com/115737443390667538288" target="_blank">+Ronald Damhof</a> (w. thanks to <a class="g-profile" href="http://plus.google.com/104674327053221428221" target="_blank">+Tom Breur</a>)</i><br />
<div style="text-align: left;">
<br /></div>
</div>
<div>
<br /></div>
Anonymoushttp://www.blogger.com/profile/13164768885306814216noreply@blogger.com0tag:blogger.com,1999:blog-3709329816577222029.post-19962613783161129672013-04-12T12:48:00.002+02:002013-09-13T14:05:25.229+02:00Seeing the last of the 'Last Seen Date': Tracking Deletes<h2>
Introduction</h2>
With Data Vault data models we sometimes add a metadata column to hubs/links called the 'Last Seen Date'. It represents the last time <b><i>any </i></b>source delivered said hub/link record to the Data Vault (again), and is only relevant on processing full loads. It needs to be checked on each load and usually when doing the full loads, will update (almost) all hub and link records (sometimes even has multiple passes as well). An expensive, tricky and performance wise problematic action because for the rest hub/link records should not be altered in <b><i>any </i></b>way. IMO, it's the last thing I'd like to see in a Data Vault (That's how you should remember its name ;).<br />
<h2>
Alternative</h2>
<div>
The best alternative is using a full blown hub/link status satellite that tracks the status: inserted, deleted for a hub/link (driving key) entry. The problem lies in tracking the 'hard' deletes from a source system information because the 'vanilla' DV with just basic full loads does not address the deletes tracking issues.</div>
<h2>
Handling Deletes</h2>
There are 5 levels of 'hard' delete tracking you can use on a Data Vault<br />
<br />
<ol>
<li>None: We assume there are no 'hard'/physical deletes in a source system, only logical deletes, indicated by a status or end time attribute. Physical deletes, if done, are actually driven by archiving and do not indicate the records becoming invalid (false), but just be removed for e.g. space constraints. Archiving done for security/privacy/legal reasons <b><i>might </i></b>have impact on the Data Vault, but it depends if we will then register a delete in the DV (We might even have to, <i>oh horror</i>, remove data from our DV)<br />If we <b><i>do </i></b>happen to stumble on source system deletes in this scenario we become non auditable, and only a <i>Last Seen Date</i> might give us an <i>indication </i>that a delete actually occurred.</li>
<li>Driving Key: In Links that are subject to Non Key updates we can use the driving key to track dummy deletes. This happens (among others) when when a 1:M relationship is encoded in a M:N link</li>
<li>Key Omission: If we assume source systems <b><i>do </i></b>deletes and we track them using full key compares between DV and source. This is a basic precaution when doing basic full loads on a DV.</li>
<li>(Key) Deletes: We actually get a list of source (record) deletes. This is of course ideal to track deletes, but don't forget to cater for key updates/mutations as well (where possible in the source).</li>
<li>(Key) Mutations: A Key mutation is logically just a combination of a key delete and a key insert. If we track all (key) mutations (where allowed/expected), like with a Change Data Capture driven solution we can easily track key deletes generated by key mutations as well, which saves us the work of having to do a full (key) compare. Additionally, we can now also populate a key mutation driven SAME-AS Link.<br />As an alternative we can track the key updates <b><i>only </i></b>when we look at the source surrogate key (stored in e.g. a key satellite), given that it is stable.</li>
</ol>
<h2>
Conclusion</h2>
<div>
The Last Seen Date is a expensive approximation of handling deletes. IMO the efforts can be better invested in better delete tracking. The Last Seen Date should be seen as a last ditch measure tracking deletes, not a basic metadata for a DV.</div>
Anonymoushttp://www.blogger.com/profile/13164768885306814216noreply@blogger.comtag:blogger.com,1999:blog-3709329816577222029.post-66367198824815525682013-04-12T09:56:00.000+02:002013-09-13T14:05:25.275+02:00EDW: Building a House of Glass<div 5px="" 8px="" padding:="" white="">
<h2 style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;">
<img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhiWEBqPlAtxOe97TxJXQSOmOy5imLEsPhzd2UCukpCn2VUyYKUjC9GEhPMMepuNiPRffcy9kyfKBwuwYk4FOugGydfUUQCLZ4nlEN_eAOjjU_WTdl_fbNziXaprm4OXqPTY0bmsy_dEf29/s320/colored_boxes.png" /></h2>
<h2>
Introduction</h2>
In software testing we have the <a href="http://en.wikipedia.org/wiki/Software_testing" target="_blank">boxing </a>approaches to determine how to test our software applications. We test it systems with a certain knowledge of the internal workings of said systems.<br />
But also in data management and information system development (for example data warehousing) we develop/manage our data with a certain level of knowledge of the data in our (source) systems. Often the knowledge of these system internals can be used to evaluate our data (management) practices, data/information development (integration, business intelligence) and do some data quality testing on the side as well.<br />
<h2>
Data in a Box</h2>
How can we see our data (silo's)?<br />
<br />
<ul>
<li>As black boxes where data goes in, and (some) information/knowledge comes out through UI screens, documents, reports, lists and user (actions)?</li>
<li>As grey boxes, where you have a (reasonable?) amount of understanding of your (technical) data. You know (most of) the technical data structures, their descriptions and direct usage but a lot of knowledge about how your data is treated by the system (detailed process rules and constraints) is either implicit knowledge or not understood at all.</li>
<li>As white/glass boxes, where data and processes and interaction are well documented (modeled!) and understood? You can interface, interact with the data in a safe and consistent way understanding all processes and constraints that work on the data. You not only have accurate 'technical' (logical?) data modeling information including (complex) constraints, but also on the business/semantic level (including business rules and processes) and have a data model schema at the 'logical' level (esp. important when the actual database schema has been 'denormalized' or otherwise mangled beyond recognition).</li>
</ul>
<br />
<h2>
Knocking up Information Hiding</h2>
The principle of information hiding is a good practice in data modeling, but when looking at boxing we should understand the current situation with COTS information systems. Preferably we look at an information system through an information/Data interface/layer representing a (logical) data model schema. Interestingly this should be the main function of a database schema even if in reality it is often not the case. Alas, a COTS is usually technical black box system and is usually poorly abstracted with a functional data layer (ie a data layer that is a logical, accurate, coherent, consistent, constrained and complete representation of the data). So while information hiding is good practice, in current COTS systems there is usually no good (formal) abstraction data/information layer that allows us correct and consistent access to information systems. We are usually stuck with a "black hole" box (where data goes in, but cannot get out easily if at all (save through arbitrary 'extractors', UI, reports or lists).<br />
<h2>
Unboxing your applications data</h2>
<div>
<img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEii-_J8KcjReDAGjKn-EhO5bMvihJHgfwKRZa7Dhg13QVwrZJszFxqsal_ly3EYUqp7OOug3hAgIkrmRAX_jfXWdw11bpKjjubzztwAJtzVFX2PIqVWsRji-24J8lqqZVqVMOE5yPsGbokz/s320/blackWhiteBoxTesting.jpg" /><br />
<br />
A lot of integration and information initiatives start with unboxing the murky data (models) from the source COTS information systems. When organizations purchase or design applications usually they see them as black boxes. As soon as data integration or BI initiatives are started they start delving into data models and data bases trying to understand the base data. They usually work up to a grey box understanding of their systems using internal data models because external/logical data model schema's, if available at all, are usually proprietary, incorrect, inconsistent or incomplete. From there they start developing their additional data processing (ETL, interfacing).<br />
But most organizations who have done this still have serious issues understanding their data because they usually miss things or don't have all the knowledge together and integrated. They are usually implicitly trying to <b>white </b>box their information systems, but have no formal way of doing so. Detailed information about these systems is usually scattered in process and database diagrams, application code, knowledge workers, data warehouses or data marts and IT personnel. Unboxing usually stops at some grey box level.<br />
<h2>
EDW's: Building a House of Glass</h2>
<div>
<img src="http://blog-images.barefootfloor.com/images/2010/11/glass_house.jpg" /></div>
<div>
<br />
Most (enterprise) data warehouse initiates (esp those talking about a 'single version of the truth') have an explicit or implied goal of constructing a house of glass, a glass box (globe) to see through all the data. They are actually trying to unbox their data to a white/glass box scenario However, they still have the issue of unboxing their OLTP/source systems. The house of glass built on top of murky source system data is actually conjuring up an illusions of control and transparency founded on a marsh of misunderstanding source systems. Instead of focusing on the house of truth they should be focusing on truly trying to unbox their source systems and focus on a house of (source system data) facts. A (logical) EDW housing both the (derived/implied) 'truth's' as well as the source data facts is the only way to construct a true data house of glass. this way an EDW can help you not just with your BI initiatives, but also with unlocking and unboxing your data for other initiatives like Data Quality control, interfacing/integration and data migration.</div>
<br />
<div style="text-align: center;">
Copyright Datamasters (Unseen) 2013</div>
</div>
</div>
Anonymoushttp://www.blogger.com/profile/13164768885306814216noreply@blogger.comtag:blogger.com,1999:blog-3709329816577222029.post-15497594015025172512013-03-30T11:17:00.003+01:002013-09-13T14:05:25.219+02:00Blog statistics milestone!: <div class="separator" style="clear: both; text-align: center;">
</div>
<div style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;">
<img src="http://www.wiebekoo.nl/kt/italie/rome/image2/rom_mijlpaal.jpg" /></div>
<div style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;">
<h2>
Miliarium Aureum</h2>
This picture is of the golden milestone (<a href="http://www.wiebekoo.nl/kt/italie/rome/miaure.htm" target="_blank">Miliarium Aureum</a>) of the via appia at the heart of the roman empire. At the hight of the empire this milestone was considered at the center of the whole empire. It was constructed 20BC by Augustus. All important milestones in the empire gave the distance relative to this stone.<br />
<br />
<h2>
Milestone</h2>
At the end of march my blog reached both 15.00 visits mainly in the last six months and is now over 3.000 visits a month. A nice first milestone for which I want to thank all my readers. I still have a lot of post coming up so I hope to see everyone back soon.</div>
<br />Anonymoushttp://www.blogger.com/profile/13164768885306814216noreply@blogger.comtag:blogger.com,1999:blog-3709329816577222029.post-86243588038491489152013-03-27T16:15:00.001+01:002013-09-13T14:05:25.299+02:00Data Vault: On The Nature of Hubs<h2>
<img height="427" src="http://us.123rf.com/400wm/400/400/patrimonio/patrimonio0901/patrimonio090100036/4174917-driver-license-identification-card.jpg" width="640" /></h2>
<h2>
Introduction</h2>
There are always a lot of discussions about the nature of hubs. There have been several linkedin discussions and blog-post who address the issue. Since these discussions are usually crossing all kind of levels of conceptualization I'd like to clarify on which levels these discussions take place. In this short post I'd like to shortcut these different levels of discussions around the nature of hubs.<br />
<br />
<h2>
Levels of Abstraction</h2>
I'll discern 4 levels of abstraction:<br />
<br />
<ol>
<li>The logical level, on which hubs are just (business) keys</li>
<li>On the conceptual level in which hubs are identified by concepts and their (stated) identifiers</li>
<li>On the meta identifier level where we design and construct and scope identifiers and their supporting (identification) processes needed to identify concepts at the conceptual level.</li>
<li>On the ontologic level where we identify abstract concepts and ignore actual identification design.</li>
</ol>
<br />
<h2>
1. Hubs as (key) transformations</h2>
Basically a HUB is an independent (logical) key (a key with no part of it dependent on another key). The only exception is when there are 2 keys and one is the surrogate key of the other, then the surrogate key does not become a hub (but optionally a keysat), but this is a minor issue since we can choose to ignore (sourced) surrogate keys altogether. When there is ONLY a surrogate key, we have an interesting issue, since a key on it's own can never be a surrogate key (because it's only a surrogate for <i>another </i>key), even if that was the intention. We might say it's a 'technical' key that will source a Hub when no other candidate key is found. The sources for Hubs/keys for a Data Vault(=Raw Data Vault+Rule Data Vault) are all relevant source system data models and all business data models. If there are several situations that try to model the same hub with different keys, you basically model all of the distinct keys. Some optimization/consolidation is possible when having multiple keys for the same <i style="font-weight: bold;">concept </i>but these decisions should be delegated to the correct modeling of the business information model using specialization/generalization. This idea relegates a lot of design and definition of (central) hubs to (master) data management and conceptual data model design. Since we distinguish between concepts and keys, a concept identified with a dependent key is a link by definition, but still an integration point. From a conceptual point a link can be seen as a hub as well.<br />
<br />
<h2>
2. Hubs as conceptual entities</h2>
<div>
Most people will equal hubs with conceptual entities like customer or product. The assumption is that an important master hub usually houses an important identifier like tax id, Social Security Number or product code. The discussion on which identifiers to use (or ignore) as 'master' hub is however not a Data Vault discussion, but a business information model discussion. Here we try to find business identifiers with the correct scope and meaning. In the Data Vault we just implement (one or more) of the available model identifiers as the master key in our hub. Again, if we have several identifiers for (entity sub-types of ) one concept we can opt to use key satellites to model this in the Data Vault.</div>
<h2>
3. Hubs as Identification schemes</h2>
<div>
A lot of practitioners try to fix business key issues in the Data Vault, with the goal to create/construct or identify a hub that will house the master list of a certain entity. They are often enticed to try to construct their own identification or consolidation scheme. Again, this is not the task of a Data Vault but a task of (master) data management. Approved matching and fixing rules can still be applied to the (Business) Rule Data Vault. These kinds of actions are usually a result of failing to find/implement a single good identifier for a conceptual entity, which in turn might lead to multiple entities encoding the same concept.</div>
<h2>
4. Hubs as (abstract) concepts</h2>
Most people trying to create the ultimate hub will end up creating ontological supertypes like 'all people' or 'all organizations'. But since there are not identification scheme's for all the 'people' they either have to invent their own (very hard) or accept that data quality will be low (duplicates abound). Again, this is not the task of a Data Vault to design these kinds of hubs (although it is natural to ask in the context of a Data Vault), but just to create or facilitate them when they have been correctly defined elsewhere. It is usually only something to define in an ontology or information model (as generalization), and usually business have no reason to sponsor these kind of endeavors in an information model when they are only interested in their own customers or vendors. So while you can define a conceptual/ontological supertype 'person', a concrete person's Hub is usually not very sensible (A derived supertype can always be constructed, of course).<br />
<h2>
Conclusion</h2>
From a formal perspective, in a Data Vault we are only interested in representing keys in an efficient and usable manner using hubs and optionally keysats (and even keylinks). Other discussions on the nature of hubs are important, but not the privilege of the Data Vault, but the providence on business information/data modeling, generalization and specialization and conceptual ontologies. The reason we see them crop up so often is because most organizations don't engage in serious business information/data modeling, which means the Data Vault/EDW designers/developers have to face a task they should ideally lay elsewhere. It is the lack of data management that makes us discuss these kinds of concepts instead of relaying them to the business (data model). Educating business and Data Vault practitioners on (conceptual/logical) data modeling is the only way to make sure these issues are tackled at the right level instead of (incorrectly) claiming that Data Vault can <b><i>solve </i></b>these kinds of issues while it is only an implementation pattern for a given (modeled) solution.Anonymoushttp://www.blogger.com/profile/13164768885306814216noreply@blogger.comtag:blogger.com,1999:blog-3709329816577222029.post-70130074085518005742013-03-20T16:20:00.000+01:002013-09-13T14:05:25.282+02:00SAP Powerdesigner: The beginning of the end? PRICE UPDATE<h2>
<img src="http://www.powerdesigner.de/wp-content/uploads/2011/04/positioning.gif" /></h2>
<h2>
Introduction</h2>
In this post https://plus.google.com/u/0/106553443733559106071/posts/65vnSaUEioZ <a class="g-profile" href="http://plus.google.com/106553443733559106071" target="_blank">+Mirko Pawlak</a> discusses a price hike of the sybase products like Powerdesigner (an ER modeling tool) of around 80%. Since Powerdesigner is already one of the most (if not THE most) expensive ER modeling tool this means Powerdesigner Licensing costs will go through the roof.<br />
<br />
<h2>
ER tools and standards</h2>
<div>
ER modeling tools and standards have always been an issue. Interoperability and standardization have never been very strong here, leading to expensive (but sometimes interesting) closed ecosystems and tooling applications. There are different notations and approaches like Barker and Information Engineering, some tools support several notations while others stick to one notation. ER tools have extended the notation to different aspects of information/data modeling and databases, so they could claim full support of the development cycle, even if this is not always what Chen had in mind when he developed ER modeling/diagramming. Transporting metadata from one tool to another has always been a costly endeavor, especially for not non basic metadata data of entities, attributes and keys. The only vendor seriously addressing this aspect is metaintegration: http://www.metaintegration.net/ which shows the how small and exclusive this market really is.</div>
<div>
<br /></div>
<h2>
Powerdesigner vs the rest</h2>
<div>
Powerdesigner has always been one of my favorite tools, not because it was always the best, or that it did not have serious (sometimes even almost fatal) flaws, but because it is quite extensible and hence you can fix a lot of issues around diagramming and design, model generation and transformation. Other good tools are Embarcadero's ER/Studio, which lacks the integrated extensibility, but is a good tool with a reasonable automation interface. CA Erwin is one of the most active tool vendors out there with integrating and partnering. For example a Data Vault ER modeling notation and integration with BIReady. If this means Erwin is going to be customizable to the extent that powerdesigner has now I don't know.</div>
<div>
Powerdesigner also has Enterprise Architecture, UML, XML, ETL, BPM and other notations under it's roof, but product integration with 3rd party tools (except database platforms) is not that extensive. However, integration into the SAP technology stack is going to be fixed in a short time frame, allowing Business Objects, SAP HANA and other tools to integrate into Powerdesigner.</div>
<div>
<br /></div>
<h2>
Buying into Powerdesigner</h2>
<div>
Like Mirko mentioned, if you'd like to buy (more) powerdesigner (licenses) you might do it now (before the end of march). If not, plan B might just need a good dusting off. As with Business Objects, SAP customers are currently faced with a new (expensive) tool from outside the SAP realm with which they have no experience. The current (non SAP) customer base however needs to address the fact that their tool gets the most attention when integrating into the SAP ecosystem. I don't mind if this means that the foundation of powerdesigner becomes better, but I fear a lot of new developments will be specific extensions for product integration, that might give rise to more issues instead of less.</div>
<h2>
The future of Powerdesigner, ER modeling and database tools.</h2>
<div>
The market for data and database diagramming is constantly changing. I see a commodization of database development diagramming tools like Oracle SQL Developer or Microsoft SQL Server Data Tools. UML diagramming is also becoming a commodity. This alas does not imply that good data modeling is becoming a basic skill, since I see toolsets, vendors and notations ruling the data modeling space instead of good data modeling standards and foundations like the Relational Model and Fact Orientend Modeling (NIAM/FCO-IM). I also see more and more semantic tools and technologies as well, but connection to the database realm is opportunistic at best. In this light the expensive Case Tools and ER tools are not winning any war. But it does mean that one of the few tools out there that is flexible enough to accommodate all kinds of diagramming is becoming more of a niche tool in the SAP ecosystem.<br />
<br />
<br />
<h2>
<span style="color: #cc0000;">PRICE UPDATE!</span></h2>
</div>
<div>
As shown at the toolpark <a href="http://www.powerdesigner.de/neue-preise/" target="_blank">website</a>: PD prices will go up indeed. As discussed <a href="https://plus.google.com/u/0/106553443733559106071/posts/87swnt47zoi" target="_blank">here </a>by <a class="g-profile" href="http://plus.google.com/106553443733559106071" target="_blank">+Mirko Pawlak</a> , it is just to match the prices of the competition (although licensing schemes and functionality are difficult to match) . For example, the old price of the basic Data Architect standalone seat (the cheapest license btw)<b> </b>goes from<strong style="border: 0px; font-family: Calibri, Helvetica, Arial; font-size: 15px; line-height: 24.625px; margin: 0px; outline: 0px; padding: 0px; vertical-align: baseline;"> 2.440 Euro netto </strong><span style="border: 0px; font-family: Calibri, Helvetica, Arial; font-size: 15px; line-height: 24.625px; margin: 0px; outline: 0px; padding: 0px; vertical-align: baseline;">to</span><span style="border: 0px; font-family: Calibri, Helvetica, Arial; font-size: 15px; line-height: 24.625px; margin: 0px; outline: 0px; padding: 0px; vertical-align: baseline;"> </span><strong style="border: 0px; font-family: Calibri, Helvetica, Arial; font-size: 15px; line-height: 24.625px; margin: 0px; outline: 0px; padding: 0px; vertical-align: baseline;">4.600 Euro netto.</strong></div>
<div>
<br /></div>
<div>
It is clear to me that market forces here are driving the prices up instead of down. Which means I have to conclude that there is no real competitive market, as any specialist can attest to who has done a migration from one ER tool to another.</div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<br /></div>
Anonymoushttp://www.blogger.com/profile/13164768885306814216noreply@blogger.com0tag:blogger.com,1999:blog-3709329816577222029.post-33892838662592667502013-03-15T10:56:00.000+01:002013-09-13T14:05:25.247+02:00Data Vault Agenda 2013<h2>
<span style="font-family: inherit;">
Introduction</span></h2>
<span style="font-family: inherit;">I've decided to try to record all events where Data Vault will be a subject. It will contain a list of presentations, courses, events and seminars. It will probably be updated several times throughout the year. Please feel free to add suggestions or clarifications for this list in the comments section. I've <span style="background-color: #3d85c6;">Highlighted </span>items I'm either teaching, attending, organizing or (co-)hosting.</span><br />
<span style="font-family: inherit;"><br /></span>
<br /><span style="font-family: inherit;"><br /></span>
<b><span style="font-family: inherit; font-size: large;">February</span></b><br />
<ul>
<li><span style="font-family: inherit;">25 - 26th<span class="Apple-tab-span" style="white-space: pre;"> </span>Data Vault Modeling & Certification - Netherlands: </span><a href="http://www.geneseeacademy.com/" style="line-height: 20.98958396911621px;" target="_blank">genesee academy</a></li>
<li><span style="background-color: #3d85c6; font-family: inherit;">27 - 28th + 6 march <b><a href="http://dm-unseen.blogspot.nl/2012/09/introducing-matter-advanced-data.html" target="_blank">MATTER </a></b>DV In the Trenches: Data (Vault) Modeling I, Netherlands: <a href="http://www.bi-podium.nl/showcase/data_vault_modeling_%22in_the_trenches%22" target="_blank">BI podium website</a> <b><span style="color: #cc0000;">ALREADY FULL!!</span></b></span></li>
</ul>
<b><span style="font-family: inherit; font-size: large;">March</span></b><br />
<div>
<div>
<ul>
<li>11 - 12th Data Modeling For Data warehousing, Nieuwegein, Netherlands: <a href="http://www.dikw-academy.nl/opleidingen/5" target="_blank">DIKW Academy</a></li>
<li><span style="font-family: inherit;">12 - 13th<span class="Apple-tab-span" style="white-space: pre;"> </span>Data Vault Modeling & Certification - Stockholm: </span><a href="http://www.geneseeacademy.com/" style="line-height: 20.98958396911621px;" target="_blank">genesee academy</a></li>
<li><span style="font-family: inherit;">18 - 19th<span class="Apple-tab-span" style="white-space: pre;"> </span>Data Vault Modeling Seminar - Lausanne Switzerland: </span><a href="http://www.geneseeacademy.com/" style="line-height: 20.98958396911621px;" target="_blank">genesee academy</a></li>
<li><span style="font-family: inherit;">21 - 22nd<span class="Apple-tab-span" style="white-space: pre;"> </span>Data Vault Modeling Seminar - Lausanne Switzerland: </span><a href="http://www.geneseeacademy.com/" style="line-height: 20.98958396911621px;" target="_blank">genesee academy</a></li>
</ul>
<ul></ul>
<b><span style="font-family: inherit; font-size: large;">April</span></b></div>
<div>
<ul>
<li><span style="font-family: inherit; line-height: 20.98958396911621px;">Data Vault 2.0 Boot Camp & Certification, Saint Albans, Vermont,US: www.danlinstedt.com</span></li>
<li>3rd April, Crash Course Data Vault, Netherlands: <a href="http://www.centennium.nl/index.php/crash-courses">Centennium</a></li>
<li><span style="font-family: inherit; line-height: 20.98958396911621px;"><span style="background-color: #3d85c6; font-size: small; line-height: normal;">9 -11th <b><a href="http://dm-unseen.blogspot.nl/2012/09/introducing-matter-advanced-data.html" target="_blank">MATTER </a></b>DV In the Trenches: Data (Vault) Architecture II, Netherland</span><span style="background-color: #3d85c6;"><span style="font-size: small; line-height: normal;">s: </span><a href="http://www.bi-podium.nl/showcase/data_vault_modeling_%22in_the_trenches%22" style="line-height: normal;" target="_blank">BI podium</a></span></span></li>
<li><span style="font-family: inherit;"><span style="line-height: 20.98958396911621px;">24 - 25th</span><span class="Apple-tab-span" style="line-height: 20.98958396911621px; white-space: pre;"> </span><span style="line-height: 20.98958396911621px;">Data Vault Modeling & Certification - Netherlands: <a href="http://www.geneseeacademy.com/" target="_blank">genesee academy</a></span></span></li>
<li>28 - 2nd May DAMAI-EDW Conference in San Diego, CA, US:<a href="http://edw2013.dataversity.net/" target="_blank">Enterprise Data World 2013</a></li>
</ul>
<span style="font-family: inherit; font-size: large; font-weight: bold;">May</span><br />
<ul>
<li>2 - 3rd Implementing a Data Vault, Veenendaal,Netherlands: <a href="http://www.infosupport.com/Training/CursusInfo/IDV/Implementing-a-Data-Vault" target="_blank">infosupport</a></li>
<li><span style="font-family: inherit;">22 - 23rd<span class="Apple-tab-span" style="white-space: pre;"> </span>Data Vault Modeling & Certification - Stockholm: </span><a href="http://www.geneseeacademy.com/" style="line-height: 20.98958396911621px;" target="_blank">genesee academy</a></li>
<li>24th Data Vault Modeling (Intro): Netherlands: <a href="http://www.vijfhart.nl/oracle-java/opleidingen/oracle-java-cursus-pagina/training-id/6507/data-vault-modelleren.htm">5Hart</a></li>
</ul>
<b><span style="font-family: inherit; font-size: large;">June</span></b></div>
<div>
<div>
<ul>
<li><span style="background-color: #3d85c6;">3-5th <b><a href="http://dm-unseen.blogspot.nl/2012/09/introducing-matter-advanced-data.html" target="_blank">MATTER </a></b>Anchor Modeling course, Netherlands: www.bi-podium.nl</span></li>
<li><span style="background-color: #3d85c6; font-family: inherit;">6th Next Generation DWH Modeling - Netherlands: http://www.bi-podium.nl/showcase/next_generation_dwh_modeling</span></li>
<li><span style="font-family: inherit;">10 - 11th<span class="Apple-tab-span" style="white-space: pre;"> </span>Data Vault Modeling & Certification - Netherlands: </span><a href="http://www.geneseeacademy.com/" style="line-height: 20.98958396911621px;" target="_blank">genesee academy</a></li>
<li>12 Data Vault Modeling (Intro) Netherlands: <a href="http://www.vijfhart.nl/oracle-java/opleidingen/oracle-java-cursus-pagina/training-id/6507/data-vault-modelleren.htm">5Hart</a></li>
<li>20th Crash Course Data Vault, Netherlands: <a href="http://www.centennium.nl/index.php/crash-courses">Centennium</a></li>
<li><span style="font-family: inherit;">21 - 22nd<span class="Apple-tab-span" style="white-space: pre;"> </span>Data Vault Modeling & Certification - Munich: </span><a href="http://www.geneseeacademy.com/" style="line-height: 20.98958396911621px;" target="_blank">genesee academy</a></li>
</ul>
<b><span style="font-family: inherit; font-size: large;">July</span></b></div>
<div>
<ul>
<li><span style="font-family: inherit;">19 - 20th<span class="Apple-tab-span" style="white-space: pre;"> </span>Data Modeling: Operational, Data Warehousing & Business Intelligence: Colorado, US: <a href="http://www.geneseeacademy.com/" style="line-height: 20.98958396911621px;" target="_blank">genesee academy</a> </span></li>
</ul>
</div>
<b><span style="font-family: inherit; font-size: large;">August</span></b><br />
<ul>
<li><span style="font-family: inherit; line-height: 20.98958396911621px;">Data Vault 2.0 Boot Camp & Certification, Saint Albans, Vermont,US: www.danlinstedt.com</span></li>
<li><span style="font-family: inherit; line-height: 20.98958396911621px;"><span style="line-height: normal;">19 - 20th Implementing a Data Vault, Veenendaal,Netherlands: </span><a href="http://www.infosupport.com/Training/CursusInfo/IDV/Implementing-a-Data-Vault" style="line-height: normal;" target="_blank">infosupport</a></span></li>
<li>22 - 23rd Implementing a Data Vault, Mechelen, Belgium: <a href="http://www.infosupport.com/Training/CursusInfo/IDV/Implementing-a-Data-Vault" target="_blank">infosupport</a></li>
<li><span style="font-family: inherit; line-height: 20.98958396911621px;">22 - 23rd<span class="Apple-tab-span" style="white-space: pre;"> </span>Data Vault Modeling & Certification - Iceland: </span><a href="http://www.geneseeacademy.com/" style="line-height: 20.98958396911621px;" target="_blank">genesee academy</a></li>
</ul>
<b><span style="font-family: inherit; font-size: large;">September</span></b><br />
<ul>
<li>6th, Data Vault Modeling (Intro): Netherlands: <a href="http://www.vijfhart.nl/oracle-java/opleidingen/oracle-java-cursus-pagina/training-id/6507/data-vault-modelleren.htm">5Hart</a></li>
<li><span style="background-color: #3d85c6; font-family: inherit;">11- 12 <b><a href="http://dm-unseen.blogspot.nl/2012/09/introducing-matter-advanced-data.html" target="_blank">MATTER </a></b>DV In the Trenches: Data (Vault) Modeling I, Netherlands: <a href="http://www.bi-podium.nl/showcase/data_vault_modeling_%22in_the_trenches%22" target="_blank">BI podium website</a> <span style="color: lime;">PLACES AVAILABLE<b>!!</b></span></span></li>
<li>24th, Crash Course Data Vault, Netherlands: <a href="http://www.centennium.nl/index.php/crash-courses">Centennium</a></li>
<li><span style="background-color: #3d85c6; font-family: inherit;">23-24, Data Modeling Zone Europe, Hannover,Germany: http://www.datamodelingzone.eu/2013/</span></li>
</ul>
<b><span style="font-family: inherit; font-size: large;">October</span></b></div>
<div>
<div>
<ul>
<li><span style="font-family: inherit;">Data Vault 2.0 Boot Camp & Certification, Saint Albans, Vermont,US: www.danlinstedt.com</span></li>
<li><span style="font-family: inherit;">8 - 10th Data Modeling Zone 2013 Baltimore,US: http://www.datamodelingzone.com/</span></li>
<li><span style="font-family: inherit;">9 - 10th<span class="Apple-tab-span" style="white-space: pre;"> </span>Data Vault Modeling & Certification - Stockholm: </span><a href="http://www.geneseeacademy.com/" style="line-height: 20.98958396911621px;" target="_blank">genesee academy</a></li>
<li><span style="font-family: inherit;">16 - 17th<span class="Apple-tab-span" style="white-space: pre;"> </span>Data Vault Modeling & Certification - Netherlands: </span><a href="http://www.geneseeacademy.com/" style="line-height: 20.98958396911621px;" target="_blank">genesee academy</a></li>
<li><span style="font-family: inherit;">21 - 22nd<span class="Apple-tab-span" style="white-space: pre;"> </span>Data Vault Briefing - Helsinki: </span><a href="http://www.geneseeacademy.com/" style="line-height: 20.98958396911621px;" target="_blank">genesee academy</a></li>
<li><span style="font-family: inherit;"><span style="background-color: #3d85c6;">23 -24 <b><a href="http://dm-unseen.blogspot.nl/2012/09/introducing-matter-advanced-data.html" target="_blank">MATTER </a></b>DV In the Trenches: Data (Vault) Advanced III, Netherland</span><span style="background-color: #3d85c6; line-height: 20.98958396911621px;"><span style="font-size: small; line-height: normal;">s: </span><a href="http://www.bi-podium.nl/showcase/data_vault_modeling_%22in_the_trenches%22" style="line-height: normal;" target="_blank">BI podium</a></span></span></li>
</ul>
</div>
<b><span style="font-family: inherit; font-size: large;">November</span></b></div>
<div>
<b><span style="font-family: inherit; font-size: large;">December</span></b><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;"><br /></span>
</div>
</div>
Anonymoushttp://www.blogger.com/profile/13164768885306814216noreply@blogger.com2tag:blogger.com,1999:blog-3709329816577222029.post-7954160123814246342013-03-04T10:32:00.000+01:002013-09-13T14:05:25.293+02:00Data Vault: Deletes MATTER<h2>
Introduction</h2>
<a href="http://icons.iconarchive.com/icons/franksouza183/fs/256/Places-trash-full-icon.png" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" src="http://icons.iconarchive.com/icons/franksouza183/fs/256/Places-trash-full-icon.png" /></a>There was a short discussion on twitter about Data Vault and the tracking of (key) deletes. Few realize that the basic/traditional Data Vault is ONLY 100% auditable when keys/records are never deleted (and never updated). I personally think Data Vault not precise enough in formulating it's auditability requirement in relation to data model transformations and the tracking database data/transactions.<br />
<h2>
Auditability</h2>
The auditability requirement can be restated using semantic equivalent data model (schema) transformation and data transactions. Being auditable means that both the model transformation is "lossless" and that all transactions are accounted for. A "lossless" model transformation is a <b>bijective </b>function between 2 model schema's. It means that we can we can transform back and forth between the data models. If we want to account for all transactions we either need to apply a (kind of) transactional replication or, when we do not need transactional consistency, we need to apply full snapshots at a certain interval (say a day). If we want to <b><i>add </i></b>a transaction timeline to one of the models (like Data Vault does), we effectively transform record deletes and updates to record inserts. So we are required to log deletes if we want to be 100% auditable.<br />
<h2>
Tracking deletes</h2>
<div>
While there are some very good arguments against physically deleting record in a database, it is often the case we need to account for them.There are basically 2 techniques for tracking deletes: Transaction logging(Change Data Capture, Audit Trails) and full compare of snapshots. With the snapshots we only need to compare (all) the key(s) of a table (there can be more than 1!!), hence the name "Key Compare". With an audit trail/CDC we can not only track record/key deletes, but also track <b>key mutations. </b>We can fill a SAME-AS link (actually a FROM-TO link) or <i style="font-weight: bold;">Key Sattelite</i> directly from a CDC stream. Note that a key update is logically equivalent of a delete and an insert and knowing how old key value mutates into the new one.<br />
<br /></div>
<div>
A special case with deletes is data archiving, since archiving usually means physically deleting the records while logically they might still be valid. While this post is not about Data Vault and archiving (I feel another post coming up ;) The correct way to deal with archiving is to set an archiving indicator for those records, and not to logically or physically delete them in your Data Vault/EDW. Alas, regulatory and privacy concerns make handling data archiving (both in the Data Vault as well as in the source system) a complex subject.</div>
<h2>
Data Vault</h2>
<div>
The basic Data Vault does a trade off between performance, ease of use and full consistency and transparency. This is not an issue as long as this trade-off is made in an informed and consistent way. Alas, too many people take the 100% auditability requirement literally, but for the Basic Data Vault this is just a goal, not a fact. For those who want to make 100% auditability a certainty, YOU need make sure you're model transformation is correct and all your transactions/records are accounted for. The Data Vault standards where designed for a lot of good reasons , but they where never precise enough to scientifically/mathematically guarantee 100% data/model auditability on data and models in all circumstances.</div>
Anonymoushttp://www.blogger.com/profile/13164768885306814216noreply@blogger.comtag:blogger.com,1999:blog-3709329816577222029.post-31990465010135773712013-02-19T13:45:00.001+01:002013-09-13T14:05:25.244+02:00Data Vault: An excuses for not modeling?<h2>
Introduction</h2>
<br />
I do sometimes hear about Data Vault initiatives with questionable directives, architectures or data model schema's. I think this is usually due to a lack of serious data modeling (skills). An argument I see a lot is that Data Vault allows you to be flexible modeling wise, so serious well though out (big, enterprise) data models are not required (they where too expensive and difficult anyway ;). <b>Nothing could be further from the truth</b>. Data Vault is a technique that allows you to implement a schema that is conductive to <b><i>postponing </i></b>certain modeling choices and activities, <i>but that is no excuse to <b>abolish </b>them outright!</i> All data modeling tools (ER tooling), skills, techniques (NIAM/FCO-IM, ER diagramming) and formalization (Relational Model) are still <i>extremely </i>relevant (even more so, but for practitioners maybe sometimes hard to correlate to Data Vault) when creating a DWH architecture with a Data Vault. Data Vault does allow you to align data model schema better with an Agile approach to DWH development, but that's about it, modeling is still required. In fact, since we want automation and also data model schema transformation/derivation, good modeling is even more relevant than with Kimball or classic Inmon style Data warehousing, where we could push modeling issues to the underlying (complex) ETL layer. With Data Vault we should never shove complexities under the "ETL carpet", but model them explicitly.<br />
<br />
Personally, I can teach a lot of Data Vault and it's variation, implementation and architectures to someone very well versed in serious data modeling with e.g. NIAM/FCO-IM. The other way around is definitely harder. Data Vault (modeling) is NOT an island, even if it's fairly 'stand alone' definition might give people that impression.Anonymoushttp://www.blogger.com/profile/13164768885306814216noreply@blogger.com0tag:blogger.com,1999:blog-3709329816577222029.post-46908489192942019282013-02-19T12:01:00.003+01:002013-09-13T14:05:25.263+02:00Finding time for understanding<h2>
Introduction</h2>
This post is not about Data vault, data modeling or Data warehouse architecture. It is about how I look at learning and training from the standpoint of an experienced professional (which we eventually all become, don't we?).<br />
<h2>
Understanding</h2>
Most knowledge workers like IT professionals (either employees or self employed) either have time allotted to them to go on training or reserve time for self study and training. Most of them try to separate the time for <b>doing </b>and time for <b>learning </b>(this is usually related to the way their work is financed and organized). They either follow courses and/or read books, and after that go back to the work at hand. In my opinion this approach seldom reaps great benefits. You usually forget what you just learned because you don't have the opportunity to directly use your new gained insights and skills. There is usually something getting in the way of implementing your new gained knowledge on the spot.<br />
<br />
In my view we should therefore focus on time for <i style="font-weight: bold;">understanding </i>during our regular work related activities<i style="font-weight: bold;"> </i>and try to make the most out of that. It should be an <i>integral </i>part of our work, and not a separate activity. So study, self study or training should ideally be done in a <i>flow </i>of work related activities. And it <i>is </i>important you increase the understanding of what you do, because if you don't understand something work related you might jeopardize your personal conviction (you get frustrated, bored or disillusioned) or your professional commitment (you might no (care to) get an optimal solution).<br />
<br />
We humans do need LOTS of time to understand things. We're humans and prone to focus on looking for well known patterns instead of really understanding what we do. We need time to grasp new ideas. Don't see your training time as the only time to try to understand things, see it more as a time to <i>trigger </i>better understanding. You should be training and learning<i> all the time.</i><br />
<br />
Before looking around for all kinds of courses first look at yourself. You might just have taken too little time to <i>understand </i>and focused too much time on <i>just doing</i>. Fix this first and <i>then </i>look for complementing learning or training opportunities.Anonymoushttp://www.blogger.com/profile/13164768885306814216noreply@blogger.com