Anchor model + Data vault

I already discussed about data modelling techniques promoted by Data Vault (DV) and Anchor Modelling (AM) approaches.   Now what if I were to borrow ideas from both techniques in order to come up with a different model.  This post describes exactly this,  with the aim to improve on somewhat contradicting criteria:  flexibility and coherence!

Note that this proposal is subjective and based on my preference and experience in data warehousing architecture.  At the end, the resulting data model could go either way as it would yield very close solution.  The fact that both have reached similar modelling techniques while originating from independent and different background is a testimony of their sound principles.

This post uses terms from both DV and AM to name data model primitives according to the relevant context.  For anyone not familiar with both context, here is the name DV vs AM terms matching:

Hub       =  Anchor   

Satellite =  Attribute

Link      =  Tie      

Ref       =  Knot     

1. Naming convention

For naming, I'd adopt the easier and more friendly DV style.  Although AM proposed a completely formalised and rigid convention, I find expressiveness and clarity to be important virtues when it comes to naming things.   AM naming convention is good with objectives like full automation and avoiding collision, but it turns out that the use of mnemonic and descriptive text result in long and cryptic column and table names.   DV style is quite soft in this respect with a simple annotation (suffix or prefix) of table based on their primitive type.

Preference:   DV (favour simplicity over rigidity)

2. Audit / Metadata information

In this respect, I'd rather use AM approach that cleanly separate all ETL-related metadata from real data.  So each data element have a FK referring to its metadata stored in separate table (for things like data source, load timestamp, job details, ...).   Mixing metadata & data blurs the line between the two, and could mislead users to confuse temporal validity of attribute  (when a new value became valid vs when did we learn about this new value).

Preference:   AM (get the metadata out of the model)

3. Reference data 

Reference data in DV seems to be more of an after-thought than a valid 4th model type.   Ok this is a subjective opinion, but in AM Knots are official primitive type that even impact the name of the underlying primitive (ex. a knotted-attribute, a knotted-tie? ).   The way I see it, there is a good motivation to account for low/fix cardinality and immutable descriptive reference data.  

Preference:  AM (make reference data first-class citizen)

4. Temporal Relationship 

Links in DV are multi-way relationship identifiable through the combination of all referred Hubs. These are recorded as unique tuple of FK's with the addition of artificial surrogate-key acting as the PK.  In contrast, AM's relationship (Tie) is uniquely identified by some set of Anchors, but in addition, we can have optional non-identifiable Hub (or Knot) for which we need to keep historical changes.  As eluded in previous post, this adds the advantage of supporting temporal relationship directly in Tie, however, with the cost that Tie can no longer support over-hanging Attributes.

I'd argue for increased flexibility: let's support both styles and apply the most suited one for our use-case.   For ex, let's apply DV style when we need to keep track of attributes inherent of multi-way relationship.  In other words, when there is no single Hub that uniquely identify the relationship, avoid creating an artificial Anchor just to support Attributes as typically done in AM.   In contrast, let's leverage inherent temporal features of AM style when relationships go through various mutable state that we need to keep track of, or when the relationship has a unique Hub identifier.


Preference:  DV-AM (allow for more flexibility with Relationship)

5. Time Point or Time Segment 

When we need to keep track of Attributes changes, AM 6th NF dictates no data redundancy such that only a single time point is stored.   In DV, the full time segment is stored which requires an "update" operation during loading to close preceding value.   Advantage of AM is the more elegant and optimised "insert-only" strategy, while DV favours the cleaner and easier query logic and execution.   Here, I 'd say either approaches because it is mostly based on the physical DB engine capability.  I then make the DV time segment not mandatory but optional:  only due to constraint originating from your DB engine implementation.

(I like this idea that every batch load will only bring new values, but ...)

Preference: AM (use a single time point if you can afford it)

6. Business Key  

In DV, Business key (aka natural key) are considered immutable and stored along the Hub data.  No such assumption is done with AM, and arguably this offers better longterm flexibility (see my previous post).  Here I definitely adopt the AM style and use a dedicated Natural-key stored in an external Attribute table, as it accounts for more situation down the road....  alternate nat-key replacement, nat-key temporal mutability, etc..

There are also cases for weak-entity (i.e. entities having PK coming from other Anchor) ... also discussed about nat-key View and .

Preference: AM (most things are mutable, so avoid immutability assumption for business key)

7. Satellites or Attributes normalised form   

In AM, 6th NF imposes a very strict rule: 1 Attribute = 1 Table.  I clearly see the advantage of this for temporal-type attributes.  However, I'd rather use the more flexible DV approach where the rule is more relax here...

Aside from the table explosion issues, I see two arguments in favour of regrouping some attibutes à la DV style:

• Some Attributes are not meant to be historised.  (for ex, most transactions are atomic and immutable, so that change in state can only happen in a subsequent or corrective transaction)

• Some Attributes are purely functionally dependent on other attribute (ex. a code has often a descriptive tag attached to it) 

Preference: DV (favour flexibility and allow for one or more attributes) 


Martin

EAV vs AM

One important area of concern in terms of database flexibility is the data model structures used for integrating new content.    This post will look at two completely different strategies used for building model structures that can easily accommodate change (at least compared to the typical 3rd normal form but not so for the typical noSQL form).     These are :

1. Use an Entity Attribute Value structure (EAV)
2. Use an Anchor Modeling structure (AM)  

In some way, these two structures are opposed to each other in their way to provide greater flexibility: EAV ingests new data content through its generic model, while AM uses highly normalised model for ingesting new content to adapt and grow incrementally without .   Note the term content here implies the modification of data structure and adding new entity and not simply the addition of new record, although this distinction is blurred for the case of EAV.

I'll discuss the pros/cons of these two here.

1) EAV structure

The Entity Attribute Value structure uses an open schema to store Parameter/Value pair.  This provides extreme flexibility in dealing with evolving scope of entities and parameters (here Parameter=Attribute).

This structure is good for : 1) high Sparsity Attribute values; 2) Attributes highly variable and unknown. 

Pros:

1. Fully evolutive: Data model and API layer can accommodate addition of any new Parameter type with some magic use of metadata (only metadata must be changed) 
2. Efficient storage for highly sparse data
3. Seem common in neuroinformatics world (ref to metadata standard: odML)

Cons:

1. EAV is more of an exception or anti-pattern in data modelling and is applicable for a few limited use cases
2. Require a rich metadata infrastructure: data-related business rules no longer enforced by relational engine but by its metadata layer
3. Higher complexity in data request, ad-hoc query (especially those cross-referencing multiple Attributes), data bulk extraction etc..

Implementation Impact:

1. often need a dedicated layer for managing data query complexity (e.g. ElasticSearch)
2. a no-SQL backend maybe more adapted (except for the metadata) unless there are clear requirements for concurrent transactional integrity and some form of isolation level 
3. When Historisation is a requirement, need to enrich EAV to support temporal attributes, and thus increasing querying complexity further.  There does not seem to be any temporal field inside the EAV instance table of its typical reference implementation (see ycmi as an example)  

2)  AM structure

AM favors strong (and fine-grained) typing of database schema while reducing a few symptoms common to rigid relational data model.  This is achieved by using higher normalisation structure (6NF).  

The AM structure is good for 1) integration of unforeseen and evolutive data model piece-wise, 2) offering clear and semantic-rich schema.

Pros:

1. Allows for data model evolution through organic extension without revisiting existing model structure
2. Historisation of all attributes is built-in without any compromise  
3. Not penalised by highly sparse data (NULL never needed)  
4. Produce models that are expressive and clear while carrying insights to the particular domain

Cons:

1. Require extension of both data model and API layer with each new data model type 
2. May suffer from table explosion (mitigated with a virtual access-layer) 
3. Need special Query execute plan optimisation (table elimination) 

Implementation Impact:

1. need DB engine with necessary optimisation (seem PostgreSQL meets some requirement, but should be further validated)
2. assume entities have strong identity with available candidate for natural keys
3. could require applying some form of generalisation to be less sensitive to addition of parameter (equivalent of ETL pattern when working in PULL mode)
4. Data modeller needs to know enough insight and have good Data Domain knowledge (data model end-result actually looks similar to domain ontologies) 

-----------

Data Model example

Here I try to model the scope that was presented to me at work.  However, I have skipped a few details and generalise the concept furthermore,  

In AM we should correctly separate Entity having clear identity (the Hub , shown in blue) from their attributes/descriptive/measures (the Attribute, shown in red), and identify relationship/association /transaction (the Tie shown in yellow).   The important thing to retain is that AM offer end-result model that are expressive and provide insights to the particular domain.  This is more easily done with one ore more SME.
   

An example of a Anchor Data model

Note: this does not follow a strict AM form :  Tie have attributes, naming convention not follow, more than one Attribute per table, audit metadata inside primitive, etc...

For the EAV, I used one reference implementation:
http://ycmi.med.yale.edu/nadkarni/EAV_CR_frame.htm.  I've added historisation capability at the attribute/value pair (EAV tables) as well as adding explicit common attributes to all Entities type.  Here, I've added natural-key for lookup and update existing entities (oddly enough there was none in the EAV ref, I guess these are buried inside one EAV attribute?).

This is inspired from the concepts of odML and its  metadata relationship: Section →0..n Property →1..m Value.   Section is used for grouping entities of same type and associated with a set of Property relevant to these entities.  These Section/Property/Value will grow as we add new data type into the PARAM_SPACE:  

An example of a EAV data model

Martin

Anchor modeling (part-2)

In previous post, I describe the Anchor modeling approach.  Here I will attempt to comment on its merit and providing a very small example of a data model .

My take on it:

Although, I never implemented AM on a large scale project myself, I can certainly see a few points that are very attractive at first glance.  Here are some that quickly come to mind :

• The model prescription really goes up to the 6NF (except when we decide to include multiple non-identifying Anchor FK or knotted attribute into a single Tie).  This 6NF implies that no other attributes are stored along with the Anchor surrogate-key, not even the natural-key(s).  Potential benefits are :  
  • Improved maintenance when dealing with evolutive or unstable natural-key.  This follows the principle of model extension where we can accommodate new or change of natural keys in the future.  

• The modelling prescription details specific and strict rules .
• Advantage: This reduces the likelihood of our model to go “wrong”.   As a data modeller you are offered much smaller degrees of freedom, at least after you have defined Anchors and Ties.  Modelling Tie may be more flexible, as it seems, you are free to add more or less non-identifying keys in the Relationship. 

• The model guideline recognises the importance of State often describing relationship (these sort of state are always mutually exclusive and exhaustive).  
• Advantage: Tie can be Knotted for storing this data in a straightforward way with no need to come up with external structure besides the Knot.   In DV, you’d need external Satellites for storing the attributes and their time-segment.  This feature is convenient as in real-world project we often see this pattern where relationship goes through some form of lifecycle changing state.  
• Disadvantage:  The flip side of this is that once your relationship is built like that, then it becomes less flexible and can hardly accommodate new stuff...In this respect, DV Links modelled as immutable intersection of Keys offer better longterm flexibility.

• The model guideline also recognise the importance of having at most one key, called Role, in the relationship outside the Tie key identifiers.  
• Advantage: For Historical Tie, this makes explicit what changes have triggered an update in the relationship.

• The model guideline recommends keeping Tie’s width small (#of Roles).   In practice, we should try breaking down larger Tie into smaller constituents.  This helps in regard to : 
  • handling of late arrival facts (or asynchronous) which would delay the capture of the relationship as recording can only happen once all roles are known;
  • decreased data redundancy generated from historised relationships with frequent changing state/property.  In this scenario, lots of nearly identical rows arise since all roles, except the non-identifying state/property role, stay identical.
  • increased stability:  larger relationship are more likely to become deprecated rendering your AM model less evolutive with more frequent replacement of existing Tie with new ones.

• The Knot is a primitive modeling construct holding important referential data:
• Advantage: static set text values are easily recognised.  We can avoid disseminating these text values, typical of lesser normalised structures, and thus reducing the data redundancy throughout our data model.

• The model guideline proposes a clear separation between metadata and data.  Auditing info is stored and maintained externally from data into their own metadata structures.  Metadata is referred to by FK pointing to a global Audit table in a standard and global way.   
• Maintaining data separate from metadata leads to cleaner data structures and less chance of misinterpreting timestamp data fields.

However an AM implementation has its drawback, the most important one being that we probably need a complete toolset and a separate access layer to help us manage the explosion of data structures (table, views..).  No one would want to interact directly at the physical level any real size AM implementation.

Other important issues could come from DB vendor's limitation which may not support some functions required (ex. Table Elimination is almost mandatory for query optimisation).   Bottom line, you certainly need a lot of experiment before leveraging this technique, and see if the additional complexity can be managed and mitigated in a large scale implementation.

A small Example:

I conclude this post by presenting a small example.   Using the available modeling tool, you can quickly start creating your own model.  So let’s imagine a DB used to collect any info, statistics, indicators/ metrics that are produced about countries and cities in the world.   As these data would need to be refreshed periodically it is important to capture the history as well as sourcing, format, and other meta-info.

The diagram model (each entity type has dedicated appearance) presented next, gives the overview of the data model.  Red squares are used for representing Anchor, circle for Attribute, rounded square for Knot while grey diamond-shaped represents Tie.   Optionally, we may choose to keep Historisation for Tie or Attribute, in which case we have outline represented as double-line.

General view of a simple AM model

The naming and mnemonic convention is used to automatically provide physical tables names (as well as views and functions).    We can toggle between mnemonic or real name in the diagram.

We see below the two main Anchor: City and Country naturally linked through a Tie, as well as with a few other Anchors tied together.

Model zoom-in around Hubs Country and City

Let’s suppose that during the lifetime of our Data repository, we had decided to keep as natural-key the ISO ALPHA-2 (2-letter code).  This was used as the country look-up with the help of Natural-key View.  However later on, we realised that a  lot of data sources actually used the ISO ALPHA-3 (3-letter code) instead as country identifier.   No problem here, our AM model supports transparently the addition of any sort of Attributes.  The only impact is that we now have two Natural-key View to choose from for the look-up.

We later discovered with disbelief, that our natural-keys are NOT immutable… quite annoying for a database key.   As always, this seemed easy to know in hindsight, but at the time who could have guessed that our ISO standard key will betray us.   Our data model must then allow for natural-keys evolution in time!  Again no problem, our AM model has “Historisation by Design” built-in, and the only impact is the addition of a new field for temporal validity inside the natural Attribute tables.  We would also need a slight change to the "Natural-key View"  to accommodate for the time point entry necessary to manage the new look-up logic.

Important note: had we kept our natural-key(s) along the surrogate, these evolutions would have involved breaking existing elements of our model!  This illustrates the benefit of higher normalisation with AM.

Conversely, from our small business case, we clearly see where the 6th NF could cause us real harm… If we start adding new each indicator as one Attribute around the Country and City (and historized as each can be updated in future), there will quickly be an explosion of tables making the entire data model unsustainable!!

To avoid this pitfall we make use of data model generalization principles and create more abstract entities.  So in place of adding new tables to store values for each new indicator, we turned these tables into rows.   In our simple scenario, this becomes possible by defining new abstract entities :  Indicator and the Indicator-instance.      Each Indicator has a clear identity (Anchor: Indicator) and is described by its name, its definition, its source (i.e. its surrounding Attributes).   The Indicator-value is represented as an anchor (IndInstance), whose identity correspond to the Indicator-value given for a particular country (or city) and at a given year.   One  Attribute holds the exact value while a Tie is needed to tie everything together (the indicator, the country and the year-period).

Model entities for Indicator and their instance values

More meta-info could be used in order to locate the information sourcing on the web, and other info related to format, document type.


Martin

What is Anchor Modeling? (part-1)

Anchor Modeling (AM) is another approach well suited for modeling the integrated/consolidated data layer within an enterprise data warehouse.  AM was originally created by Olle Regardt with formalisation done by Lars Rönnbäck.  It offers an open source database modeling technique accompanied by an online modeling tool available through an MIT license.  Most info is maintained around AM home.

The approach has some theoretical background with ideas borrowed from the information modeling world.  More specifically it is inspired by a method called Object Role Modeling (ORM) developed by Dr. Terry Halpin.   ORM, not to be confused with the Object Relation Mapping, is a modeling notation designed to help non-expert doing conceptual modeling of database schema.

The theoretical formalisation has also helped provide a very strict and prescriptive methodology compared to some more ad-hoc approaches used in the world of data warehouse (often heuristic-based developed with time and experience).  

It also offers some implementation ecamples, but at this time, the only complete implementation is ported onto the Microsoft SQL-Server engine.

Comparison to Data Vault

I will not go into details as to what is Anchor Modeling here.   Instead I will describe the similarities it shares with Data Vault (DV),  since both aims to decompose any data model into smaller and more basic constituents.    For those interested in detail description, please refer to this excellent article Anchor modeling - agile information modeling in evolving data environments written by its creators.  

1. Anchor ≈ Hub

• Both Anchor and Hub store Entities having strong identity.   There is an important difference:  in AM, an Anchor stands completely on its own, i.e. it only stores the surrogate-key (aka technical key) so keep natural-key (aka business-key) in separate Attribute table.   

IMO, this offers additional flexibility especially when natural-key(s) are chosen based on operational systems. These tend to have shorter lifecycle than the EDW whose lifetime should be, at least what EDW team hope for,  infinite!    By keeping natural-key(s) outside the Hub, we avoid making any assumption as to what are the natural-key(s) and their data type(s) in future.    AM propose to use a Natural-key View for handling the loading and look-up logic, and any change in natural keys would involve updating this View and adding the new Attribute table only.

2. Attribute ≈ Satellite

• Attribute is similar to the Satellite notion in DV.    Both adds contextual and descriptive data which are inherently time-dependent.   However to preserve the 6NF temporal integrity, AM enforces that each Attribute lands in dedicated table.   DV is more relaxed here and accepts any number of attributes in the same Satellite.  

AM supports either temporal Attribute (referred to as historised in AM modeling front-end tool), or static and immutable Attribute where no temporal validity period is required.   DV modeling guideline assume historisation by default, and requires to store the full time-segment with two distinct time points: Valid-from and Valid-to.  Again the higher-normalised nature of AM restricts us from storing the redundant Valid-to time point (= subsequent Valid-from time point).

The multi-attributes in DV imply that we must proceed with row comparison to know which one has changed between active record and previous one.  However, I'd argue that AM rule 1-Attribute = 1-table is too strict, and could be relaxed for some exceptional cases.   One example are attributes having strict functional dependency on a master attribute; think of user-friendly text attribute providing descriptive info for technical short code.  Other examples are for immutable attributes guaranteed to
remain so in the lifetime of the entity they describe.

3. Tie ≈ Link

• Tie is similar to the Link notion in DV whose role is to store the relationship between Anchor/Hubs entities .   However Tie does not support over-hanging Attributes the same way Link have their own Satellites.  This limitation is due to the fact that historisation is built-in for Tie as with Attribute.   Switching-on historisation adds Valid-from time point to the Tie's Primary-key, and consequently any Attributes referring to the Tie would be left dangling (or else requires duplicating the Attribute row in violation of 6NF rule).   

The drawback of AM follows that for any natural many-to-many associations having inherent attributes will involve the creation of a “tied anchor”.   DV is more flexible here, as it allows attaching Satellites to Link.   In DV time-period is not built-in inside the Link structure, so we are left with constructing the history of the relationship ourselves by adding time segment in external Satellite.  This is required when we want to keep track of time-period validity of any relationship.

Tie may also have some form of attributes like when we deal with relationship having state/role information. Under this circumstance, one can add a Knot attribute to the Tie, becoming a "Knotted-tie" that can now support historical change of its state/role (the knot carries this state information).   With this scheme, you should have at most one Knotted attribute outside the relationship identifier (or another un-identifying relation in extra inside the Tie) for keeping track of the state or role information of the relationship (or the extra role-key tie). 

4. Knot ≈ Reference 

• Knot is similar to the Reference notion in DV.    Both of them hold immutable data of lower cardinality.  They normally represent a set of fixed categories, codes or static attributes and are normalised into separate table to avoid update anomalies and duplicating string values throughout the database.   Main difference is that Reference in DV can store multiple strings whereas again in AM only one string code per table is allowed.    It seems though that Knots are first class citizen, whereas in DV, Reference seems more like an ad-hoc optional structure.   Knots also enhance the semantic of its reference, for ex, an Attribute referring a knot becomes a knotted Attribute, a Tie becomes a knotted Tie, both being either static or historised.  

A small digression on Time

Temporal aspect of AM: The notion of Time in AM is well framed semantically with clear definition.   It provides support of 3 notions of Time: Happening time.   Corresponds to when some events/transactions occurred: as represented by specific Attribute(s) attached on Anchor Changing Time.   Corresponds to when some Attribute/Tie (when historised) are valid: as represented by the built-in “Valid-From” Time point in Attribute/Tie. Note that no redundant “Valid-To” is added to close the time-interval in order to comply with the 6NF requirement.  This avoids the nasty “update” during data loading process, and the potential risk of recording invalid time period.  But this comes at the price of additional complexity for reporting valid temporal attribute at specific time.  Recording time.  Corresponds to when the data was recorded into our platform (i.e. loading metadata):  as represented by FK’s  referencing a particular log entry in metadata Audit tables. Externalising the “Recording time” has the advantage of separating data from metadata, where all timestamp found in the data structures correspond to functional dates whereas metadata is located outside these data structures.  This contrasts with DV where metadata-info sits alongside the data.  Although the metadata timestamp “Load_Start” is not supposed to carry any functional meaning, it is confusing that most Query/Report examples given in DV documentation use this metadata timestamp for returning the valid data at given point in time!   Time when data is loaded are rarely correlated with Time of data validity.  This can happen in some occasion, like we have no other alternatives (see case 3 below).   The way I see things, when we need to determine the right functional “Valid-from”, we can be faced with 3 different situations : Dates do exist explicitly at source, so we load them as-is (typical of data having specified lifespan with effective/expiration date , etc..) Dates do not exist explicitly at source but can be deducted from some technical dates t source, so we use these technical dates (typical example is source having LastStatusUpdate meta-info allowing us to know when the data has changed) Dates do not exist explicitly at source neither can they be deducted from other technical dates, so that one is left with the only option of using the Loading time Ideally, situation 3 is more the exception than rules in most EDW implementation.

 

Main advantages

Most advantages of the AM approach results from its highly normalised structure.  This offers benefits such as:

• Simpler Data loading involves cheap “insert” and no expensive “update/delete”
  • Fault recovery is more easily done since all rows data have a unique batch load traceability and can be deleted on exceptional situation (when erroneous)
  • Strict "No Null" policy :   null values never appear, thus eliminating difficulties like interpretability, indexing, and other issues.
  • Non-persistent elements never removed but flagged with Knot indicating their state of persistence
  • Maintenance is overall improved and simplified

• Data model evolution only involves extension of previous version
  • New content always involve incremental addition of either Anchor / Tie / Attribute / Knot structures 
  • These New data structures may, sometimes, render some existing structure obsolete, then we can simply leave them as-is with no onward refresh.

• Potential Higher Performance (here, it’s more tricky as the higher normalisation can also penalise performance, but the authors have highlighted a few valid points worth mentioning)
  • Narrow Query (hitting small number of attributes) can have very good performance with the aid of Table Elimination
    
  • Storage is highly efficient since practically no data redundancy exist, only a small number of indexes is actually needed due to the use of clustered table (aka index-organised table in different DB vendor) and also narrowness of table.   On this last point, it'd be nice to see how column-store architecture could be leveraged to exploit the AM unique schema.
  • Data loading practically free from locking and other concurrency issues as only “insert” are processed.

• Reduce translation logic between different representation:  the unique AM graphical representation is used for both conceptual and logical modeling
  
  • Furthermore, the physical representation maps directly model entities onto tables, simplifying modeling abstraction levels or even eliminates the need for translation logic. 

Martin 

BI “ideal” platform

When dealing with BI projects, you are much more likely to work under the constraint of existing platform than building one from scratch.   This means having to deal with existing idiosyncrasies, sub-optimal architecture, complex and big data models, tangled ETL loading dependencies and scheduling, confusing or mixing different approaches and architecture, etc.

This post presents my “ideal” platform blueprint I would use when no existing platform is in place which compromises good design principles.  This ideal BI platform leverages the Multilayered architecture's concept borrowed from the more general Design Pattern in software design engineering.  The advantage of layering is obvious enough:

1. Visibility:  any layer can only depend on the immediate preceding layer
2. Functional role:  each layer focus on clear and none contradicting roles
3. Volatility:
   • Delivery (highest) layer are made up of components that are dependent only on end-user BR changes (i.e. new “business” rule should only affect Delivery layer)
   • Raw Consolidated (lowest) layer are made up of components that change according to technical/system changes happening in operational world
   • Business (middle) layer isolate lowest and highest layers from each other and from their life changing cycle (here the Business-oriented data of the Consolidation layer is the best place to design this in-between layer)

All layers control and isolate data dependencies :  each layer is only sourced (or dependent) from its direct layer underneath, and layer only expose data to layer above.

The following diagram depicts an high-level view of this idealistic  BI platform:
 


 Obviously, this diagram’s focus is on simplification, detail of less important are not represented; ex. possible staging areas in between each layer, metadata layer (for ETL business rules, KPI and Report definition, ..),  delivery layer usually has an eclectic choice of architecture based on technologies and tools.

Source Layer

Layer Definition

This layer is simply all systems collecting and generating data.   Not much can be said here as this layer does not fall under the BI group’s responsibility.   However one design criteria note worthy concerns its physical implementation and is meant to avoid disrupting the on-going operational process :

• We should never target the real “Production” system, but Leverage whenever possible some data replication/copy mechanisms (hardware or software -based)
• Persisted Staging Area or ODS are different ways for BI to get their own our copy of source data with minimal impact on source

Integration/Consolidated Layer

Layer Definition
This layer is the one closest to the original Inmon’s def: “subject oriented, non-volatile, integrated, time variant collection of data in support of management's decisions”.  Some may refer to it using different name like Integrated Layer,  but the fundamental idea remains the same: consolidate and store multi-source data in one database repository.  

All data generated by the organisation’s activity and business processes (automatic, semi or even manual) are conveniently centralised here.  We can further distinguish two sub-layers :

1. “Raw” layer which captures all data as-is to ensure full data lineage and traceability
2. “Business” oriented layer which adds business-provided data (reference,  grouping, hierarchical structures..), and provides some early data transformation for reducing downstream transformation complexity for the Delivery layer.

Functional Role of Raw Integration-Layer:

• Provide the first integration and consolidation point of multi-source data
• Capture and archive all data as-is (no modification nor transformation) : Load 100% of data 100% of the time
• Provide the full historical view of the detailed data:  “System of records”
• Identify and load master data source for important and recognized business entity
• Attach “platform-neutral” Surrogate keys to all natural keys of business entity
• Align entity across different source
• Standardise through an easy and repeated data modelling patterns all data

Functional Role of Business Integration-Layer:

• Data structure designed to apply business rules transformation
• Bridge and link non-conformed entities across data sources
• Provide Grouping, Hierarchies, and other business-provided data
• Create structure to decrease the Delivery layer transformations complexity
• Fragment complex transformations rules into smaller, more manageable  and auditable steps
• Recognise and flag conformed Entity across different data source
• Flag “erroneous” or “wrong” data  as such (rejecting data to be done in Delivery layer)

 

Layer-Design Criteria (raw and business)

• Use a uniform Data modelling technique
  • Do not merely copy and add timestamp/audit data to source OLTP Data models
  • Use a standardized approach and stick to it for every data source integrated
  • Avoid data model diversities and idiosyncrasies typically found in source layer (harder to learn and more costly to absorb)

• Use a Highly Normalised data structure*
  • Methodology like Data Vault or other similar approaches Anchor-modeling
  • Discourage usage of large data structure (these structures result in important downstream dependency, rigid relationships, and are associated with unstable and hard to maintain ETL logic)

• Favor Easy Data Loading over Easy Data Reading (compromise to pay)
  • Must be Easy and Quick to load new data structure
  • May be Hard and Messy to query existing data structure

• Allow for growing organically/incrementally
  • Start small with one source and a few data components
  • Build small incremental step gradually as you ingest more data source

• Accept gracefully system source changes
  • At best, every change should simply “add” on the existing (no dependencies downstream  impacted)
  • At worst, every change should only affect a small number of “localized” data structures (very limited dependencies impacted)

• Accept gracefully addition of new source
  • Each new source is integrated independently
  • “Bridge” these independent “universe” (data source) through easy flexible model structure, like the concept of Link in Data Vault.

• Support full traceability and auditability of source data
  • Don’t “fix” and don’t do any data cleansing
  • Leave “erroneous” or “wrong” data AS-IS  (these come for a reason)

• Favor understandability through simple and repeatable data model patterns
  • Both raw and business layer must leverage identical data modeling techniques and standard
  • Data model easily recognized and understood and by “unaware” (new) eyes

*Highly normalized structure : To keep track of a temporal database using database normalization, it is well accepted that a normalization less than 6NF has shortcomings (see 6NF Conceptual Models and DW 2.0).  The 6NF form ensures that every non-key gets stored into its dedicated Relation.   In this purest form, we’ll end up with all Relations having one Key plus one non-key attribute, hence making the Relation irreducible  (keeping track of history is simply managed by adding time semantics).   Practical approaches normally accept some kind of compromise to this purest form (eg. DV would combine multiple attributes within a single Satellite).   But they still benefit from major advantages of higher normalization: +Model much more Resilient to changes in the outside world of the EDW (able to non-destructively evolve the model) +Model are Historization-Ready making all attributes potentially temporal (possible by design) +Model limits data redundancy even for temporal and time-variant data +Model can support more performant Zero Update strategy +Model results in the elimination of NULL and support early arriving facts (entity loaded only depend on an Identity)

Delivery or Presentation Layer

Layer Definition
This layer provides the Business view and/or Departmental view (different version of the truth) of the data.   It is accompanied with Reporting tool and any Data Analytics platform used by business people inside the organisation.

Delivery-Layer –Functional Role

• Support complex business rules implementation
• Provide flexibility to change data transformation rules
• Offer a sensible view to the business community of all organization’s operations and processes
• Offer a cleansed and standardized view of the often messy operational data
• Provide support decision-making tools and platforms
• Provides data to allow for more advanced analytical and mining activities

Delivery Layer -Design Criteria

• Favor simple and easily communicable data model
  • Employ recognized Methodology such as dimensional and star-schema
• Favor easy Data Reading over hard Data Loading (compromise to pay)
  • Must be Fast and Easy to query data
  • May be Hard and Messy to load data
• Accept gracefully new Hierarchies, Grouping or other Business provided-info
• Fully support multiple “version” of the data (departmental or organizational view )
• Accept gracefully business rules changes (can be frequent)
  • At best, every business change should simply mean to re-construct the Virtualized layer (no re-loading necessary)
  • At worst, every business change should only affect very “localized” data loading jobs
• Favor Communication of data model tied to Business terms and meaning
  • Data model easily understood and accepted and by “new” business eyes
• Implementation more dependent on Vendor tools:  follows platform features, choice and characteristics

Martin