Top-down versus bottom-up design methodologies
 |
Bottom-up design
Ralph Kimball, a well-known author on data warehousing,[8] is a proponent of an approach to data warehouse design which he describes as bottom-up.[9]In the bottom-up approach, data marts are first created to provide reporting and analytical capabilities for specific business processes. It is important to note that in Kimball methodology, the bottom-up process is the result of an initial business-oriented top-down analysis of the relevant business processes to be modelled.
Data marts contain, primarily, dimensions and facts. Facts can contain either atomic data and, if necessary, summarized data. The single data mart often models a specific business area such as "Sales" or "Production." These data marts can eventually be integrated to create a comprehensive data warehouse. The integration of data marts is managed through the implementation of what Kimball calls "a data warehouse bus architecture".[10] The data warehouse bus architecture is primarily an implementation of "the bus", a collection of conformed dimensions and conformed facts, which are dimensions that are shared (in a specific way) between facts in two or more data marts.
The integration of the data marts in the data warehouse is centered on the conformed dimensions (residing in "the bus") that define the possible integration "points" between data marts. The actual integration of two or more data marts is then done by a process known as "Drill across". A drill-across works by grouping (summarizing) the data along the keys of the (shared) conformed dimensions of each fact participating in the "drill across" followed by a join on the keys of these grouped (summarized) facts.
Maintaining tight management over the data warehouse bus architecture is fundamental to maintaining the integrity of the data warehouse. The most important management task is making sure dimensions among data marts are consistent. In Kimball's words, this means that the dimensions "conform".
Some consider it an advantage of the Kimball method, that the data warehouse ends up being "segmented" into a number of logically self-contained (up to and including The Bus) and consistent data marts, rather than a big and often complex centralized model. Business value can be returned as quickly as the first data marts can be created, and the method gives itself well to an exploratory and iterative approach to building data warehouses. For example, the data warehousing effort might start in the "Sales" department, by building a Sales-data mart. Upon completion of the Sales-data mart, the business might then decide to expand the warehousing activities into the, say, "Production department" resulting in a Production data mart. The requirement for the Sales data mart and the Production data mart to be integrable, is that they share the same "Bus", that will be, that the data warehousing team has made the effort to identify and implement the conformed dimensions in the bus, and that the individual data marts links that information from the bus. Note that this does not require 100% awareness from the onset of the data warehousing effort, no master plan is required upfront. The Sales-data mart is good as it is (assuming that the bus is complete) and the Production-data mart can be constructed virtually independent of the Sales-data mart (but not independent of the Bus).
If integration via the bus is achieved, the data warehouse, through its two data marts, will not only be able to deliver the specific information that the individual data marts are designed to do, in this example either "Sales" or "Production" information, but can deliver integrated Sales-Production information, which, often, is of critical business value.
Top-down design
Bill Inmon, one of the first authors on the subject of data warehousing, has defined a data warehouse as a centralized repository for the entire enterprise.[10] Inmon is one of the leading proponents of the top-down approach to data warehouse design, in which the data warehouse is designed using a normalized enterprise data model. "Atomic" data, that is, data at the lowest level of detail, are stored in the data warehouse. Dimensional data marts containing data needed for specific business processes or specific departments are created from the data warehouse. In the Inmon vision, the data warehouse is at the center of the "Corporate Information Factory" (CIF), which provides a logical framework for delivering business intelligence (BI) and business management capabilities.Inmon states that the data warehouse is:
Subject-oriented
The data in the data
warehouse is organized so that all the data elements relating to the same
real-world event or object are linked together.
Non-volatile
Data in the data
warehouse are never over-written or deleted — once committed, the data are
static, read-only, and retained for future reporting.
Integrated
The data warehouse
contains data from most or all of an organization's operational systems and
these data are made consistent.
Time-variant
For An
operational system, the stored data contains the current value. The data
warehouse, however, contains the history of data values.
The top-down design methodology generates highly consistent
dimensional views of data across data marts since all data marts are loaded
from the centralized repository. Top-down design has also proven to be robust
against business changes. Generating new dimensional data marts against the
data stored in the data warehouse is a relatively simple task. The main
disadvantage to the top-down methodology is that it represents a very large
project with a very broad scope. The up-front cost for implementing a data
warehouse using the top-down methodology is significant, and the duration of
time from the start of project to the point that end users experience initial
benefits can be substantial. In addition, the top-down methodology can be
inflexible and unresponsive to changing departmental needs during the
implementation phases.[10]Hybrid design
Data warehouse (DW) solutions often resemble the hub and spokes architecture. Legacy systems feeding the DW/BI solution often include customer relationship management (CRM) and enterprise resource planning solutions (ERP), generating large amounts of data. To consolidate these various data models, and facilitate the extract transform load (ETL) process, DW solutions often make use of an operational data store (ODS). The information from the ODS is then parsed into the actual DW. To reduce data redundancy, larger systems will often store the data in a normalized way. Data marts for specific reports can then be built on top of the DW solution.It is important to note that the DW database in a hybrid solution is kept on third normal form to eliminate data redundancy. A normal relational database however, is not efficient for business intelligence reports where dimensional modelling is prevalent. Small data marts can shop for data from the consolidated warehouse and use the filtered, specific data for the fact tables and dimensions required. The DW effectively provides a single source of information from which the data marts can read, creating a highly flexible solution from a BI point of view. The hybrid architecture allows a DW to be replaced with a master data management solution where operational, not static information could reside.
The Data Vault Modeling components follow hub and spokes architecture. This modeling style is a hybrid design, consisting of the best practices from both 3rd normal form and star schema. The Data Vault model is not a true 3rd normal form, and breaks some of the rules that 3NF dictates be followed. It is however, a top-down architecture with a bottom up design. The Data Vault model is geared to be strictly a data warehouse. It is not geared to be end-user accessible, which when built, still requires the use of a data mart or star schema based release area for business purposes.
Data warehouses versus operational systems
Operational systems are optimized for preservation of data integrity and speed of recording of business transactions through use of database normalization and an entity-relationship model. Operational system designers generally follow the Codd rules of database normalization in order to ensure data integrity. Codd defined five increasingly stringent rules of normalization. Fully normalized database designs (that is, those satisfying all five Codd rules) often result in information from a business transaction being stored in dozens to hundreds of tables. Relational databases are efficient at managing the relationships between these tables. The databases have very fast insert/update performance because only a small amount of data in those tables is affected each time a transaction is processed. Finally, in order to improve performance, older data are usually periodically purged from operational systems.
Confirmed Dimensions, Junk Dimensions,
and Degenerated Dimensions
Conformed Dimensions (CD): these dimensions are something that
is built once in your model and can be reused multiple times with different
fact tables. For example, consider a model containing multiple fact
tables, representing different data marts. Now look for a dimension that
is common to these facts tables. In this example let’s consider that the
product dimension is common and hence can be reused by creating short cuts and
joining the different fact tables.Some of the examples are time dimension,
customer dimensions, product dimension. §
Junked Dimensions (JD): When you consolidate lots of
small dimensions and instead of having 100s of small dimensions, that will have
few records in them, cluttering your database with these mini ‘identifier’
tables, all records from all these small dimension tables are loaded into ONE
dimension table and we call this dimension table Junk dimension table.
(Since we are storing all the junk in this one table) For example: a
company might have handful of manufacture plants, handful of order types, and
so on, so forth, and we can consolidate them in one dimension table called
junked dimension table.
Degenerated Dimension (DD): An item that is in the fact
table but is stripped off of its description, because the description belongs
in dimension table, is referred to as Degenerated Dimension. Since it
looks like dimension, but is really in fact table and has been degenerated of
its description, hence is called degenerated dimension. Now coming to the
slowly changing dimensions (SCD) and Slowly Growing Dimensions (SGD): I
would like to classify them to be more of an attributes of dimensions its
self.
Although other might disagree to this view but Slowly Changing Dimensions
are basically those dimensions whose key value will remain static but
description might change over the period of time. For example, the
product id in a companies, product line might remain the same, but the
description might change from time to time, hence, product dimension is called
slowly changing dimension.
Lets consider a customer dimension, which will have a unique
customer id but the customer name (company name) might change periodically due
to buy out / acquisitions, Hence, slowly changing dimension, as customer number
is static but customer name is changing, However, on the other hand the
company will add more customers to its existing list of customers and it is
highly unlikely that the company will acquire astronomical number of customer
over night (wouldn’t the company CEO love that) hence, the customer dimension
is both a Slowly changing as well as slowly growing dimension
Source: Wikipedia.
