{"id":92015,"date":"2020-08-21T19:42:13","date_gmt":"2020-08-21T17:42:13","guid":{"rendered":"https:\/\/prohoster.info\/blog\/administrirovanie\/obzor-gibkih-metodologij-proektirovaniya-dwh"},"modified":"2020-08-21T19:42:13","modified_gmt":"2020-08-21T17:42:13","slug":"obzor-gibkih-metodologij-proektirovaniya-dwh","status":"publish","type":"post","link":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/obzor-gibkih-metodologij-proektirovaniya-dwh","title":{"rendered":"Overview of flexible data warehouse design methodologies","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"<p>Developing a data warehouse is a lengthy and serious matter. <\/p>\n<p>Much in the life of a project depends on how well the object model and database structure are planned from the outset.<\/p>\n<p>The common approach has been, and remains, various combinations of the 'star' schema with the third normal form. Generally, the principle is: source data \u2014 3NF, data marts \u2014 star. This time-tested approach, supported by numerous studies, is the first (and often only) thought that comes to an experienced DWH specialist's mind when considering how an analytical warehouse should look.<\/p>\n<p>On the other hand, businesses in general and client requirements in particular tend to change rapidly, while data grows both 'deeper' and 'broader'. Here, the main drawback of the star schema emerges \u2014 its limitations. <b>flexibility<\/b>.<\/p>\n<p>And if in your quiet and cozy life as a DWH developer suddenly:<\/p>\n<ul>\n<li>the task arose to 'quickly put something together, and we\u2019ll see later';<\/li>\n<li>a rapidly developing project emerged, with the integration of new sources and a reshaped business model at least once a week;<\/li>\n<li>a client appeared who has no idea what the system should look like or what functions it should perform in the end but is open to experimentation and gradually refining the desired outcome while moving closer to it;<\/li>\n<li>the project manager popped in with the joyful news: 'And now we have Agile!'.<\/li>\n<\/ul>\n<p>\nOr if you're simply curious about other ways to build warehouses \u2014 welcome under the cut!<\/p>\n<p><img decoding=\"async\" alt=\"Overview of flexible data warehouse design methodologies\" src=\"\/wp-content\/uploads\/2020\/08\/59cd70ca3af4841d0c5c9bfbbd7636a3.png\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<noindex><a rel=\"nofollow\" name=\"habracut\"><\/a><\/noindex><\/p>\n<h3>What does 'flexibility' mean?<\/h3>\n<p>\nTo begin, let's determine what properties a system must have to be called 'flexible'. <\/p>\n<p>It\u2019s worth noting that the described properties should pertain specifically to <b>network management system and CI\/CD becomes full-fledged configuration versioning.<\/b>, not to <b>the process <\/b>of its development. Therefore, if you wanted to read about Agile as a development methodology, it would be better to refer to other articles. For instance, there are many interesting materials available here on Habr (like <noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/post\/436178\/\">review articles<\/a><\/noindex> and <noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/post\/284012\/\">practical<\/a><\/noindex>and <noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/company\/dataart\/blog\/245605\/\">problems.<\/a><\/noindex>).<\/p>\n<p>This does not mean that the development process and the structure of the Data Warehouse (DW) are unrelated. In general, developing a flexible architecture data warehouse using Agile should be significantly easier. However, in practice, options often occur with Agile development of classical Data Warehouses following Kimball and DataVault methodologies \u2014 through a waterfall approach, rather than the fortunate coincidence of flexibility in both of its forms on a single project.<\/p>\n<p>So, what capabilities should a flexible data warehouse possess? Three main points can be highlighted:<\/p>\n<ol>\n<li><b>Early delivery and rapid refinement<\/b> \u2014 this means that ideally the first business result (for instance, the first working reports) should be achieved as early as possible, that is, before the entire system has been fully designed and implemented. At the same time, each subsequent refinement should also take as little time as possible.<\/li>\n<li><b>Iterative refinement<\/b> \u2014 this means that ideally, each subsequent refinement should not affect already functioning functionality. This aspect often becomes the biggest nightmare on large projects \u2014 sooner or later, individual objects start to accumulate so many connections that it becomes easier to completely replicate the logic in a copy alongside than to add a field to an existing table. And if you are surprised that analyzing the impact of a refinement on existing objects can take longer than the refinement itself \u2014 you probably have not yet worked with large data warehouses in banking or telecom.<\/li>\n<li><b>Constant adaptation to changing business requirements<\/b> \u2014 the overall object structure should be designed not just with potential expansion in mind but also with the expectation that the direction of this next expansion could not have even crossed your mind at the design stage.<\/li>\n<\/ol>\n<p>\nAnd yes, compliance with all these requirements in a single system is possible (of course, in certain cases and with some caveats).<\/p>\n<p>Below, I will explore the two most popular methodologies for flexible data warehouse design \u2014 <b>Anchor model<\/b> and <b>Data Vault<\/b>Many brilliant techniques remain outside the scope, such as EAV, 6NF (in its pure form), and anything related to NoSQL solutions\u2014not because they are inferior, and not even because the article risks becoming the size of an average dissertation. It\u2019s simply that all this pertains to a somewhat different class of solutions\u2014either to techniques that you can apply in specific cases, regardless of the overall architecture of your project (like EAV), or to fundamentally different paradigms of data storage (such as graph databases and other NoSQL options).<\/p>\n<h3>Problems of the \u201cclassical\u201d approach and their solutions in agile methodologies<\/h3>\n<p>\n<i>By the \u201cclassical\u201d approach, I mean the old reliable star schema (regardless of the specific implementation of the underlying layers, forgive me, followers of Kimball, Inmon, and CDM).<br \/>\n<\/i><\/p>\n<h4>1. Rigid cardinality of relationships<\/h4>\n<p>\nThe basis of this model is a clear separation of data into <b>dimensions (Dimension)<\/b> and <b>facts (Fact)<\/b>. And this, damn it, makes sense\u2014because data analysis in the overwhelming majority of cases boils down to the analysis of specific numeric indicators (facts) across specific dimensions.<\/p>\n<p>At the same time, relationships between objects are established in the form of links between tables via foreign keys. This looks quite natural, but it immediately leads to the first limitation of flexibility\u2014 <b>the rigid definition of relationship cardinality.<\/b>.<\/p>\n<p>This means that at the design stage of the tables, you must precisely determine whether each pair of related objects can relate as many-to-many, or only one-to-many, and \"in which direction.\" The primary key in one of the tables and the foreign key in another directly depend on this. Changing this relationship when new requirements arise will likely lead to a reworking of the database.<\/p>\n<p>For example, when designing the object \u201ccash receipt,\u201d you relied on solemn assurances from the sales department that the possibility of one promotion would apply <b>to multiple receipt items<\/b> (but not vice versa):<\/p>\n<p><img decoding=\"async\" alt=\"Overview of flexible data warehouse design methodologies\" src=\"\/wp-content\/uploads\/2020\/08\/15226cb79c30364d94147383ba36a8fd.png\" style=\"display:block;margin: 0 auto;\" \/><br \/>\nThen, after some time, colleagues introduced a new marketing strategy where multiple promotions could apply simultaneously <b>to the same item.<\/b>And now you need to revise the tables, isolating the relationship into a separate object. <\/p>\n<p>(All derivative objects where the join check is on promo now also require modifications).<\/p>\n<p><img decoding=\"async\" alt=\"Overview of flexible data warehouse design methodologies\" src=\"\/wp-content\/uploads\/2020\/08\/3c4d84a31088660257d74c4c703071e0.png\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<b>Connections in Data Vault and Anchor Model<\/b><\/p>\n<p>Avoiding such a situation turned out to be quite simple: don't trust the sales department for this, just <b>store all connections initially in separate tables<\/b> and process them as many-to-many. <\/p>\n<p>This approach was proposed <b>by Dan Linstedt<\/b> as part of the paradigm <b>Data Vault<\/b> and fully supported <b>by Lars R\u00f6nnb\u00e4ck<\/b> downward API support (simultaneously with this in <b>in the Anchor Model<\/b>.<\/p>\n<p>As a result, we get the first distinctive feature of flexible methodologies:<\/p>\n<blockquote><p>Connections between objects are not stored in the attributes of parent entities, but represent a separate type of objects.<\/p><\/blockquote>\n<p>In <b>Data Vault<\/b> Such link tables are called <b>Link<\/b>, then moving this task to the section <b>Anchor Model<\/b> \u2014 <b>Tie<\/b>. At first glance, they seem very similar, although their differences are not exhausted by their names (which will be discussed below). In both architectures, link tables can connect <b>any number of entities<\/b> (not necessarily 2).<\/p>\n<p>This seemingly redundant structure provides significant flexibility during modifications. This structure becomes tolerant not only to changes in the cardinalities of existing connections but also to adding new ones \u2014 if now a check line also has a reference to the cashier who punched it, the emergence of such a link becomes just an addition to the existing tables without affecting any existing objects and processes.<\/p>\n<p><img decoding=\"async\" alt=\"Overview of flexible data warehouse design methodologies\" src=\"\/wp-content\/uploads\/2020\/08\/6a3b942a6e5d04dcbe2ff0881ebdf1bf.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<h4>2. Data Duplication<\/h4>\n<p>\nThe second problem addressed by flexible architectures is less obvious and is primarily characteristic of <b>SCD2-type measurements<\/b> (slowly changing dimensions of the second type), although not limited to them.<\/p>\n<p>In a classic warehouse, a dimension typically represents a table that contains a surrogate key (as the PK) as well as a set of business keys and attributes in separate columns. <\/p>\n<p><img decoding=\"async\" alt=\"Overview of flexible data warehouse design methodologies\" src=\"\/wp-content\/uploads\/2020\/08\/059dd47b2302b58c19a0144061b78cb4.png\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<br \/>\nIf the dimension supports versioning, the standard set of fields is supplemented with time bounds for the version's validity, and several versions appear in the warehouse for one row in the source (one for each change in versioned attributes).<\/p>\n<p>If a dimension contains at least one frequently changing versioned attribute, the number of versions for that dimension can be substantial (even if the other attributes are non-versioned or never change), and if there are multiple such attributes, the number of versions may grow exponentially with their quantity. Such a dimension can occupy a significant amount of disk space, even though most of the data it contains are merely duplicates of the values of immutable attributes from other rows.<\/p>\n<p><img decoding=\"async\" alt=\"Overview of flexible data warehouse design methodologies\" src=\"\/wp-content\/uploads\/2020\/08\/f3d5a4fd83a5ef36355961302173791d.png\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<br \/>\nAt the same time, denormalization is often applied, <b>denormalization<\/b> \u2014 some attributes are intentionally stored as values rather than references to a directory or another dimension. This approach speeds up data access by reducing the number of joins when accessing the dimension.<\/p>\n<p>As a rule, this leads to <b>the same information being stored simultaneously in multiple places<\/b>. For example, information about the region of residence and customer category affiliation can be stored simultaneously in the dimensions 'Customer', and the facts 'Purchase', 'Delivery', and 'Calls to the Call Center', as well as in the linking table 'Customer \u2014 Customer Manager'.<\/p>\n<p>Overall, the above applies to ordinary (non-versioned) dimensions as well, but in versioned ones, it may have a different scale: the emergence of a new version of an object (especially retroactively) does not just lead to updating all related tables, but to a cascading emergence of new versions of related objects \u2014 when Table 1 is used in the construction of Table 2, and Table 2 is used in the construction of Table 3, etc. Even if none of the attributes of Table 1 participate in the construction of Table 3 (but other attributes of Table 2, obtained from other sources, do), versioned updates of this structure will at the very least lead to additional overhead costs and at most to unnecessary versions in Table 3, which is completely 'unrelated' here, and so on down the chain.<\/p>\n<p><img decoding=\"async\" alt=\"Overview of flexible data warehouse design methodologies\" src=\"\/wp-content\/uploads\/2020\/08\/2935f93abc46f02bdc528accda2af758.png\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<\/p>\n<h4>3. Non-linear complexity of modifications<\/h4>\n<p>\nAt the same time, each new view built based on another increases the number of places where data can 'diverge' when changes are made in ETL. This, in turn, leads to an increase in the complexity (and duration) of each subsequent modification.<\/p>\n<p>If the above applies to systems with rarely modified ETL processes, it is possible to live in such a paradigm \u2014 it is sufficient to simply ensure that new changes are correctly reflected in all related objects. However, if changes occur frequently, the likelihood of inadvertently 'missing' several connections significantly increases.<\/p>\n<p>Moreover, considering that a 'versioned' ETL is considerably more complex than a 'non-versioned' one, avoiding errors during frequent modifications becomes quite challenging.<\/p>\n<h3>Storing objects and attributes in Data Vault and Anchor Model<\/h3>\n<p>\nThe approach proposed by the authors of flexible architectures can be summarized as follows:<\/p>\n<blockquote><p>It is necessary to separate what changes from what remains unchanged. In other words, store keys separately from attributes.<\/p><\/blockquote>\n<p> At the same time, it is important not to confuse <b>non-versioned<\/b> attributes with <b>unchangeable<\/b>: the former does not store the history of its changes but can change (for instance, when correcting input errors or receiving new data), while the latter never changes.<\/p>\n<p>Opinions on what can be considered unchangeable in Data Vault and Anchor models vary.<\/p>\n<p>From an architectural perspective <b>Data Vault<\/b>, unchangeable can be considered <b>the entire set of keys<\/b> \u2014 natural (e.g., organization INN, product code in the source system, etc.) and surrogate. At the same time, other attributes can be divided into groups by source and\/or frequency of changes and <b>for each group, maintain a separate table<\/b> with an independent set of versions.<\/p>\n<p>In the paradigm of <b>Anchor Model<\/b> , only the surrogate key <b>of the entity is considered unchangeable. Everything else (including natural keys) is merely a specific case of its attributes. Moreover,<\/b> all attributes are by default independent of each other <b>, thus a<\/b>separate table must be created for each attribute. <b>Tables containing entity keys are called<\/b>.<\/p>\n<p>In <b>Data Vault<\/b> Hubs. <b>Hubs always contain a fixed set of fields:<\/b>Natural entity keys<\/p>\n<ul>\n<li>Surrogate key<\/li>\n<li>Link to the source<\/li>\n<li>Time of record addition<\/li>\n<li>Records in Hubs<\/li>\n<\/ul>\n<p>\nare never changed and do not have versions. <b>never change and have no versions<\/b>Externally, hubs resemble ID-map tables used in some systems for generating surrogates. However, in Data Vault, it is recommended to use a hash of the set of business keys instead of an integer sequence as surrogates. This approach facilitates loading relationships and attributes from sources (there's no need to join on the hub to obtain a surrogate; it's enough to calculate the hash of the natural key), but it may introduce other issues (related, for example, to collisions, case sensitivity, and non-printable characters in string keys, etc.), which is why it's not universally accepted.<\/p>\n<p>All other entity attributes are stored in special tables called <b>Satellites (Satellit)<\/b>. A single hub can have multiple satellites that store different sets of attributes.<\/p>\n<p><img decoding=\"async\" alt=\"Overview of flexible data warehouse design methodologies\" src=\"\/wp-content\/uploads\/2020\/08\/e145f211b8cfb51894e6e1789991e1cd.png\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<br \/>\nThe distribution of attributes across satellites is based on the principle of <b>joint evolution<\/b> \u2014 one satellite may hold non-versioned attributes (for example, birth date and SNILS for individuals), another may store rarely changing versioned attributes (such as last name and passport number), and a third may contain frequently changing ones (like delivery address, category, last order date, etc.). Versioning is managed at the level of individual satellites rather than the entity as a whole; thus, it is advisable to distribute attributes in such a way that the overlap of versions within a single satellite is minimized (which reduces the total number of stored versions). <\/p>\n<p>Additionally, to optimize the data loading process, attributes obtained from different sources are often extracted into separate satellites.<\/p>\n<p>Satellites are linked to the Hub via <b>foreign key<\/b> (which corresponds to a cardinality of one-to-many). This means that multiple attribute values (for example, several contact phone numbers for a single client) are inherently supported by this architecture.<\/p>\n<p>In <b>Anchor Model<\/b> tables that store keys are referred to as <b>Anchors (Anchor)<\/b>. And they store: <\/p>\n<ul>\n<li><b>Only surrogate keys<\/b><\/li>\n<li>Time of record addition<\/li>\n<li>Records in Hubs<\/li>\n<\/ul>\n<p>\nNatural keys, from the perspective of the Anchor Model, are considered <b>ordinary attributes<\/b>. This option may seem more complex to understand, but it allows for much greater flexibility in identifying an object.<\/p>\n<p><img decoding=\"async\" alt=\"Overview of flexible data warehouse design methodologies\" src=\"\/wp-content\/uploads\/2020\/08\/0bf941581fd1177eda228d4429cf69db.png\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<br \/>\nFor instance, if data about the same entity can come from different systems, each using its own natural key, this can lead to quite bulky constructions of several hubs (one for each source plus a unifying master version) in Data Vault. In the Anchor model, however, the natural key of each source is included in its attribute and can be used during loading independently of all others. <\/p>\n<p>But here lies a tricky point: if attributes from various systems are combined in one entity, there are likely certain <b>merging rules<\/b>, according to which the system must understand that records from different sources correspond to one instance of the entity. <\/p>\n<p>In <b>Data Vault<\/b> These rules will most likely determine the formation of <b>the surrogate hub of the master entity<\/b> and will not affect the Hubs that store the natural keys of the sources and their original attributes. If at any point the merging rules change (or an update of the attributes used for merging comes), it will be enough to reformat the surrogate hubs.<\/p>\n<p>In <b>In the Anchor model<\/b> , such an entity will most likely be stored in <b>a single anchor<\/b>. This means that all attributes, regardless of the source they come from, will be tied to the same surrogate. Separating mistakenly merged records and generally tracking the relevance of merging in such a system can turn out to be significantly more difficult, especially if the rules are quite complex and frequently change, and the same attribute can be obtained from different sources (although it is certainly possible, since each version of the attribute retains a link to its source).<\/p>\n<p>In any case, if your system anticipates the implementation of functionality for <b>deduplication, merging of records, and other MDM elements<\/b>, it is worth paying particular attention to the aspects of storing natural keys in flexible methodologies. The more cumbersome Data Vault structure may unexpectedly prove to be safer in terms of merging errors.<\/p>\n<p><b>The Anchor model<\/b> also provides for an additional type of object called <b>Knot<\/b> , essentially this is a special <b>degenerate form of an anchor.<\/b>, which can contain only one attribute. Nodes are intended to hold flat reference data (e.g., gender, marital status, customer service category, etc.). Unlike Anchors, a Node <b>does not have related attribute tables.<\/b>, and its only attribute (name) is always stored in the same table with the key. Nodes are linked to Anchors through relationship tables (Tie), just as anchors are linked to each other.<\/p>\n<p>There is no consensus on the use of Nodes. For instance, <noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/users\/azathot\/\">Nikolai Golov<\/a><\/noindex>, who actively promotes the use of the Anchor model in Russia, believes (not without reason) that no reference data can be definitively claimed to be <b>recommendations only<\/b> static and single-level, so it is better to use a full Anchor for all objects from the start.<\/p>\n<p>Another important difference between Data Vault and the Anchor model lies in the presence of <b>attributes for relationships.<\/b>:<\/p>\n<p>In <b>Data Vault<\/b> Relationships are just as complete objects as Hubs, and they can have <b>their own attributes.<\/b>. In <b>In the Anchor model<\/b> Relationships are used solely to connect Anchors and <b>cannot have their own attributes.<\/b>. This distinction results in significantly different modeling approaches for <b>facts<\/b>, which will be discussed further.<\/p>\n<h3>Fact Storage<\/h3>\n<p>\nPrior to this, we mostly discussed dimension modeling. The situation with facts is somewhat less clear-cut.<\/p>\n<p>In <b>Data Vault<\/b> a typical object for storing facts \u2014<b> a Link<\/b>, in the Satellites of which physical measures are aggregated.<\/p>\n<p>This approach seems intuitively understandable. It provides straightforward access to analyzed metrics and generally resembles a traditional fact table (only the metrics are stored not in the table itself, but in a 'neighboring' one). However, there are pitfalls: one of the typical enhancements to the model\u2014extending the fact key\u2014necessitates <b>adding a new foreign key to the Link.<\/b>. This, in turn, 'breaks' modularity and potentially necessitates modifications to other objects.<\/p>\n<p>In <b>In the Anchor model<\/b> A Link cannot have its own attributes, so this approach will not work\u2014all attributes and measures must be tied to one specific anchor. The conclusion is simple\u2014 <b>each fact also needs its own anchor.<\/b>For part of what we perceive as facts, this may seem natural \u2014 for example, the fact of a purchase is nicely reduced to the object 'order' or 'receipt', a website visit translates to a session, and so on. But there are also facts for which finding such a natural 'object-carrier' is not so straightforward \u2014 for instance, stock levels in warehouses at the start of each day. <\/p>\n<p>Consequently, there are no issues with modularity when expanding the fact key in the Anchor model (it is sufficient just to add a new Relation to the corresponding Anchor), but designing the model for displaying facts is less straightforward; 'artificial' Anchors can emerge that do not clearly represent the business object model.<\/p>\n<h3>How flexibility is achieved<\/h3>\n<p>\nThe resulting structure in both cases contains <b>significantly more tables<\/b>, than traditional measurements. But it can occupy <b>significantly less disk space<\/b> with the same set of versioned attributes as traditional measurements. There is, of course, no magic here \u2014 it all comes down to normalization. By distributing attributes across Satellites (in Data Vault) or separate tables (Anchor Model), we reduce (or completely eliminate) <b>the duplication of the values of some attributes when others are changed.<\/b>.<\/p>\n<p>For <b>Data Vault<\/b> The gain will depend on how attributes are distributed across Satellites, and for <b>In the Anchor model<\/b> \u2014 it is practically directly proportional to the average number of versions per measurement object.<\/p>\n<p>However, the gain in space occupied is important, but not the main advantage of separate storage of attributes. Along with the separate storage of relationships, this approach makes the warehouse <b>a modular construct<\/b>. This means that the addition of both individual attributes and entirely new subject areas in such a model appears as <b>an extension<\/b> over the existing set of objects without altering them. And this is precisely what makes the described methodologies flexible.<\/p>\n<p>This also resembles the transition from unit production to mass production \u2014 if in the traditional approach each table of the model is unique and requires individual attention, in flexible methodologies, it is already a set of standard 'parts'. On one hand, the number of tables increases, and the processes of data loading and retrieval seem more complex. On the other hand, they become <b>standardized<\/b>. Thus, they can be <b>automated and managed by metadata<\/b>. The question of 'how will we lay it out?' which could have taken a significant portion of the design work for modifications, is simply not an issue anymore (just like the question of how changes to the model will affect the working processes). <\/p>\n<p>This doesn\u2019t mean that analysts are completely unnecessary in such a system \u2014 someone still needs to work through a set of objects with attributes and figure out where and how to load everything. However, the volume of work, as well as the likelihood and cost of errors, are significantly reduced. Both during the analysis phase and when developing ETL, which can largely boil down to editing metadata. <\/p>\n<h3>The dark side<\/h3>\n<p>\nEverything described above makes both approaches truly flexible, technological, and suitable for iterative refinement. Of course, there is also a 'barrel of tar,' which I think you are already suspecting.<\/p>\n<p>The decomposition of data underlying the modularity of flexible architectures leads to an increase in the number of tables and, consequently, <b>overhead<\/b> for joins during queries. To simply retrieve all the measurement attributes, a single select is sufficient in a classic warehouse, while a flexible architecture will require a whole series of joins. Moreover, while these joins can be pre-written for reports, analysts accustomed to writing SQL by hand will face double the burden.<\/p>\n<p>There are several facts that ease this situation:<\/p>\n<p><b>When working with large dimensions, almost never are all its attributes used simultaneously.<\/b> This means that there may be fewer joins than it seems at first glance. In Data Vault, the expected frequency of co-use can also be taken into account when distributing attributes across satellites. Meanwhile, the Hubs or Anchors are primarily needed for generating and mapping surrogates during the loading phase and are rarely used in queries (especially in the case of Anchors).<\/p>\n<p><b>All joins are by key.<\/b> Moreover, a more \"compressed\" way of storing data reduces the overhead for scanning tables where necessary (e.g., when filtering by attribute value). This may result in querying a normalized database with many joins being faster than scanning a single large dimension with many versions per row.<\/p>\n<p>For example, in the <noindex><a rel=\"nofollow\" href=\"http:\/\/www.anchormodeling.com\/wp-content\/uploads\/2011\/05\/Anchor-Modeling.pdf\">this <\/a><\/noindex> article, there is a detailed comparative performance test of the Anchor model against a single table query.<\/p>\n<p><b>Much depends on the engine.<\/b> Many modern platforms have internal mechanisms for optimizing joins. For instance, MS SQL and Oracle can \"skip\" joins to tables if their data is not used anywhere except in other joins and does not affect the final selection (table\/join elimination), while MPP Vertica has, according to colleagues from Avito, <noindex><a rel=\"nofollow\" href=\"http:\/\/www.anchormodeling.com\/wp-content\/uploads\/2011\/05\/Big_Data_Normalization.pdf\">shown itself to be an excellent engine for the Anchor model with some manual query plan optimization. On the other hand, storing the Anchor model on Click House, which has limited join support, does not seem to be a good idea.<\/a><\/noindex>Furthermore, there are<\/p>\n<p>specific techniques <b>that facilitate data access (both in terms of query performance and for end users). For example,<\/b>Point-In-Time tables <b>in Data Vault or<\/b> special table functions <b>in the Anchor model.<\/b> The essence of the discussed flexible architectures lies in the modularity of their \"construction.\"<\/p>\n<h2>Total<\/h2>\n<p>\nIt is this property that allows: <\/p>\n<p>After some initial setup related to deploying metadata and writing basic ETL algorithms,<\/p>\n<ul>\n<li>to quickly deliver the first results to the customer <b>in the form of a couple of reports containing data from just a few source objects. It is not necessary to fully think through (even at a high level) the entire object model for this.<\/b> The data model can start to work (and provide benefits) with just 2-3 objects, and then<\/li>\n<li>gradually grow <b>(regarding the Anchor model, Nikolai<\/b> made a <noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/company\/avito\/blog\/322510\/\">nice comparison with a mycelium). <\/a><\/noindex>Most modifications, including expanding the subject area and adding new sources,<\/li>\n<li>do not affect existing functionality and do not carry the risk of breaking anything that already works. <b>does not affect existing functionality and does not pose a risk of breaking something that is already working<\/b>.<\/li>\n<li>Thanks to the decomposition into standard elements, ETL processes in such systems appear uniform, their writing is amenable to algorithmization and ultimately, <b>automation<\/b>.<\/li>\n<\/ul>\n<p>\nThe price of such flexibility is <b>performance<\/b>. This doesn't mean that achieving acceptable performance with such models is impossible. Most often, you may just need to put in more effort and attention to detail to achieve the desired metrics.<\/p>\n<h2>Applications<\/h2>\n<p><\/p>\n<h4>Entity types <b>Data Vault<\/b><\/h4>\n<p>\n<img decoding=\"async\" alt=\"Overview of flexible data warehouse design methodologies\" src=\"\/wp-content\/uploads\/2020\/08\/6bbaee505587152d7e5c11b2889bf25a.png\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<br \/>\nLearn more about Data Vault:<br \/>\n<noindex><a rel=\"nofollow\" href=\"https:\/\/danlinstedt.com\/\">Dan Linstedt's website<\/a><\/noindex><br \/>\n<noindex><a rel=\"nofollow\" href=\"http:\/\/www.dwh-club.com\/ru\/dwh-bi-articles\/vse-o-data-vault.html\">All about Data Vault in Russian<\/a><\/noindex><br \/>\n<noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/post\/348188\/\">About Data Vault on Habr<\/a><\/noindex><\/p>\n<h4>Types of entities <b>Anchor Model<\/b><\/h4>\n<p>\n<img decoding=\"async\" alt=\"Overview of flexible data warehouse design methodologies\" src=\"\/wp-content\/uploads\/2020\/08\/d518f01e6a5c241e9e73d1ea0210c557.png\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<br \/>\nLearn more about Anchor Model:<\/p>\n<p><noindex><a rel=\"nofollow\" href=\"http:\/\/www.anchormodeling.com\/\">Website of the creators of Anchor Model<\/a><\/noindex><br \/>\n<noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/company\/avito\/blog\/322510\/\">Article about the experience of implementing Anchor Model at Avito<\/a><\/noindex><\/p>\n<p>Summary table of common features and differences of the discussed approaches:<\/p>\n<p><img decoding=\"async\" alt=\"Overview of flexible data warehouse design methodologies\" src=\"\/wp-content\/uploads\/2020\/08\/807717245fd874ab141031fc64e584fc.png\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<br \/>Source: <a content=\"nofollow\" rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/company\/glowbyte\/blog\/515940\/\">habr.com<\/a> <\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"<p>\u0420\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u043a\u0430 \u0445\u0440\u0430\u043d\u0438\u043b\u0438\u0449\u0430 \u2014 \u0434\u0435\u043b\u043e \u0434\u043e\u043b\u0433\u043e\u0435 \u0438 \u0441\u0435\u0440\u044c\u0435\u0437\u043d\u043e\u0435. \u041c\u043d\u043e\u0433\u043e\u0435 \u0432 \u0436\u0438\u0437\u043d\u0438 \u043f\u0440\u043e\u0435\u043a\u0442\u0430 \u0437\u0430\u0432\u0438\u0441\u0438\u0442 \u043e\u0442 \u0442\u043e\u0433\u043e, \u043d\u0430\u0441\u043a\u043e\u043b\u044c\u043a\u043e \u0445\u043e\u0440\u043e\u0448\u043e \u043f\u0440\u043e\u0434\u0443\u043c\u0430\u043d\u0430 \u043e\u0431\u044a\u0435\u043a\u0442\u043d\u0430\u044f \u043c\u043e\u0434\u0435\u043b\u044c \u0438 \u0441\u0442\u0440\u0443\u043a\u0442\u0443\u0440\u0430 \u0431\u0430\u0437\u044b \u043d\u0430 \u0441\u0442\u0430\u0440\u0442\u0435. \u041e\u0431\u0449\u0435\u043f\u0440\u0438\u043d\u044f\u0442\u044b\u043c \u043f\u043e\u0434\u0445\u043e\u0434\u043e\u043c \u0431\u044b\u043b\u0438 \u0438 \u043e\u0441\u0442\u0430\u044e\u0442\u0441\u044f \u0440\u0430\u0437\u043b\u0438\u0447\u043d\u044b\u0435 \u0432\u0430\u0440\u0438\u0430\u043d\u0442\u044b \u0441\u043e\u0447\u0435\u0442\u0430\u043d\u0438\u044f \u0441\u0445\u0435\u043c\u044b \u201c\u0437\u0432\u0435\u0437\u0434\u0430\u201d \u0441 \u0442\u0440\u0435\u0442\u044c\u0435\u0439 \u043d\u043e\u0440\u043c\u0430\u043b\u044c\u043d\u043e\u0439 \u0444\u043e\u0440\u043c\u043e\u0439. \u041a\u0430\u043a \u043f\u0440\u0430\u0432\u0438\u043b\u043e, \u043f\u043e \u043f\u0440\u0438\u043d\u0446\u0438\u043f\u0443: \u0438\u0441\u0445\u043e\u0434\u043d\u044b\u0435 \u0434\u0430\u043d\u043d\u044b\u0435 \u2014 3NF, \u0432\u0438\u0442\u0440\u0438\u043d\u044b \u2014 \u0437\u0432\u0435\u0437\u0434\u0430. \u042d\u0442\u043e\u0442 \u043f\u043e\u0434\u0445\u043e\u0434, \u043f\u0440\u043e\u0432\u0435\u0440\u0435\u043d\u043d\u044b\u0439 \u0432\u0440\u0435\u043c\u0435\u043d\u0435\u043c \u0438 \u043f\u043e\u0434\u043a\u0440\u0435\u043f\u043b\u0435\u043d\u043d\u044b\u0439 [&hellip;]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":92016,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-92015","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t<!-- All in One SEO 5.0.2 - aioseo.com -->\n\t<meta name=\"description\" content=\"\u0420\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u043a\u0430 \u0445\u0440\u0430\u043d\u0438\u043b\u0438\u0449\u0430 \u2014 \u0434\u0435\u043b\u043e \u0434\u043e\u043b\u0433\u043e\u0435 \u0438 \u0441\u0435\u0440\u044c\u0435\u0437\u043d\u043e\u0435.\" \/>\n\t<meta name=\"robots\" content=\"max-image-preview:large\" \/>\n\t<meta name=\"author\" content=\"Yuri Gagarin\"\/>\n\t<link rel=\"canonical\" href=\"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/obzor-gibkih-metodologij-proektirovaniya-dwh\" \/>\n\t<meta name=\"generator\" content=\"All in One SEO (AIOSEO) 5.0.2\" \/>\n\t\t<meta property=\"og:locale\" content=\"en_US\" \/>\n\t\t<meta property=\"og:site_name\" content=\"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b\" \/>\n\t\t<meta property=\"og:type\" content=\"article\" \/>\n\t\t<meta property=\"og:title\" content=\"\ud83e\udd47\u041e\u0431\u0437\u043e\u0440 \u0433\u0438\u0431\u043a\u0438\u0445 \u043c\u0435\u0442\u043e\u0434\u043e\u043b\u043e\u0433\u0438\u0439 \u043f\u0440\u043e\u0435\u043a\u0442\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u044f DWH | ProHoster\" \/>\n\t\t<meta property=\"og:description\" content=\"\u0420\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u043a\u0430 \u0445\u0440\u0430\u043d\u0438\u043b\u0438\u0449\u0430 \u2014 \u0434\u0435\u043b\u043e \u0434\u043e\u043b\u0433\u043e\u0435 \u0438 \u0441\u0435\u0440\u044c\u0435\u0437\u043d\u043e\u0435.\" \/>\n\t\t<meta property=\"og:url\" content=\"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/obzor-gibkih-metodologij-proektirovaniya-dwh\" \/>\n\t\t<meta property=\"og:image\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:secure_url\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:width\" content=\"350\" \/>\n\t\t<meta property=\"og:image:height\" content=\"350\" \/>\n\t\t<meta property=\"article:published_time\" content=\"2020-08-21T17:42:13+00:00\" \/>\n\t\t<meta property=\"article:modified_time\" content=\"2020-08-21T17:42:13+00:00\" \/>\n\t\t<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<!-- All in One SEO -->\n\n","aioseo_head_json":{"title":"\ud83e\udd47Overview of Agile Data Warehouse design methodologies | ProHoster","description":"Developing a data warehouse is a lengthy and serious matter.","canonical_url":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/obzor-gibkih-metodologij-proektirovaniya-dwh","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"en_US","og:site_name":"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b","og:type":"article","og:title":"\ud83e\udd47\u041e\u0431\u0437\u043e\u0440 \u0433\u0438\u0431\u043a\u0438\u0445 \u043c\u0435\u0442\u043e\u0434\u043e\u043b\u043e\u0433\u0438\u0439 \u043f\u0440\u043e\u0435\u043a\u0442\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u044f DWH | ProHoster","og:description":"\u0420\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u043a\u0430 \u0445\u0440\u0430\u043d\u0438\u043b\u0438\u0449\u0430 \u2014 \u0434\u0435\u043b\u043e \u0434\u043e\u043b\u0433\u043e\u0435 \u0438 \u0441\u0435\u0440\u044c\u0435\u0437\u043d\u043e\u0435.","og:url":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/obzor-gibkih-metodologij-proektirovaniya-dwh","og:image":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:secure_url":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:width":350,"og:image:height":350,"article:published_time":"2020-08-21T17:42:13+00:00","article:modified_time":"2020-08-21T17:42:13+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"92015","title":null,"description":null,"keywords":null,"keyphrases":null,"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":null,"og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"","isEnabled":true},"graphs":[]},"schema_type":null,"schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":null,"robots_max_videopreview":null,"robots_max_imagepreview":"large","priority":null,"frequency":null,"local_seo":null,"seo_analyzer_scan_date":null,"breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-02-28 12:16:44","updated":"2022-09-27 14:57:59","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts\/92015","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/comments?post=92015"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts\/92015\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/media\/92016"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/media?parent=92015"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/categories?post=92015"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/tags?post=92015"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}