Ralph Kimball and Inmon, the co-founders of the data warehouse, significantly had their own differences in the design and architecture of the data warehouse. Inmon advocated a “dependent data mart structure” whereas Kimball advocated the “data warehouse bus structure”.
The Dependent Data Mart structure:The top down approach
Bill Inmon saw a need to transfer data from diverse OLTP systems into a centralized place where the data could be used for analysis. He insisted that data should be organized into subject oriented, integrated, non volatile and time variant structures. The data should be accessible at detailed atomic levels by drilling down or at summarized levels by drilling up. The data marts are treated as sub sets of the data warehouse. Each data mart is built for an individual department and is optimized for analysis needs of the particular department for which it is created.
The data flow in the top down OLAP environment begins with data extraction from the operational data sources. This data is loaded into the staging area and validated and consolidated for ensuring a level of accuracy and then transferred to the Operational Data Store (ODS). The ODS stage is sometimes skipped if it is a replication of the operational databases. Data is also loaded into the Data warehouse in a parallel process to avoid extracting it from the ODS. Detailed data is regularly extracted from the ODS and temporarily hosted in the staging area for aggregation, summarization and then extracted and loaded into the Data warehouse. The need to have an ODS is determined by the needs of the business. If there is a need for detailed data in the Data warehouse then, the existence of an ODS is considered justified. Else organizations may do away with the ODS altogether.
Once the Data warehouse aggregation and summarization processes are complete, the data mart refresh cycles will extract the data from the Data warehouse into the staging area and perform a new set of transformations on them. This will help organize the data in particular structures required by data marts. Then the data marts can be loaded with the data and the OLAP environment becomes available to the users.
The Data warehouse Bus Structure: The Bottom-Up Approach
Ralph Kimball designed the data warehouse with the data marts connected to it with a bus structure. The bus structure contained all the common elements that are used by data marts such as conformed dimensions, measures etc defined for the enterprise as a whole. He felt that by using these conformed elements, users can query all data marts together. This architecture makes the data warehouse more of a virtual reality than a physical reality. All data marts could be located in one server or could be located on different servers across the enterprise while the data warehouse would be a virtual entity being nothing more than a sum total of all the data marts.
In this context even the cubes constructed by using OLAP tools could be considered as data marts. In both cases the shared dimensions can be used for the conformed dimensions.
This model strikes a good balance between centralized and localized flexibility. Data marts can be delivered more quickly and shared data structures along the bus eliminate the repeated effort expended when building multiple data marts in a non-architected structure. The conformed dimensions along the bus fit very well with the shared dimension and virtual cube capabilities of Microsoft’s OLAP services.
The bottom-up approach reverses the positions of the Data warehouse and the Data marts. Data marts are directly loaded with the data from the operational systems through the staging area. The ODS may or may not exist depending on the business requirements. However, this approach increases the complexity of process coordination. The standard procedure where data marts are refreshed from the ODS and not from the operational databases ensures data consolidation and hence it is generally recommended approach.
The data flow in the bottom up approach starts with extraction of data from operational databases into the staging area where it is processed and consolidated and then loaded into the ODS. The data in the ODS is appended to or replaced by the fresh data being loaded. After the ODS is refreshed the current data is once again extracted into the staging area and processed to fit into the Data mart structure. The data from the Data Mart, then is extracted to the staging area aggregated, summarized and so on and loaded into the Data Warehouse and made available to the end user for analysis.
Hybrid Approach
The Hybrid approach aims to harness the speed and user orientation of the Bottom up approach to the integration of the top-down approach. The Hybrid approach begins with an Entity Relationship diagram of the data marts and a gradual extension of the data marts to extend the enterprise model in a consistent, linear fashion. These data marts are developed using the star schema or dimensional models. The Extract, Transform and Load (ETL) tool is deployed to extract data from the source into a non persistent staging area and then into dimensional data marts that contain both atomic and summary data. The data from the various data marts are then transferred to the data warehouse and query tools are reprogrammed to request summary data from the marts and atomic data from the data warehouse.
Federated approach
This is a hub-and –spoke architecture often described as the “architecture of architectures. It recommends an integration of heterogeneous data warehouses, data marts and packaged applications that already exist in the enterprise. The goal is to integrate existing analytic structures wherever possible and to define the “highest value” metrics, dimensions and measures and share and reuse them within existing analytic structures. This may result in the creation of a common staging area to eliminate redundant data feeds or building of a data warehouse that sources data from multiple data marts, data warehouses or analytic applications. Hackney-a vocal proponent of this architecture—claims that it is not an elegant architecture but it is an architecture that is in keeping with the political and implementation reality of the enterprise.
As with any major undertaking, a business intelligence implementation blueprint represents a big picture vision of what the ultimate system will look like; it is the information architect’s rendering of the concept aligned with the CIO’s directive and vision. The big picture is based on information requirements across various functional areas and business units of the enterprise. This is the thinking big step: to have a solid, unwavering yet flexible concept that is bought into by all the stakeholders of the business intelligence system. The tricky part is to start planning for building the BI solution to meet the vision. This is the phase where the Business Intelligence Competency Center (BICC) will need to delicately balance the big-picture vision with small steps, one step at a time. Practically the start-small phase translates to guiding the creation of several business intelligence applications, which in turn are based on subject-aligned data marts.
Of course, introducing an additional data layer creates several challenges in terms of resources, ownership, information latency and breadth/depth of content; all of which cause some stakeholders to hold a robust argumentative line to resist introduction of a data mart to support business intelligence. The urge to base the business intelligence layer right off the underlying base tables of a data warehouse on the basis of rapid latency requirements needs to be questioned right up front: does the requirement really need “real time” latency? Often times, while users might think they want the information “as it happens”, further analysis negates this requirement, thus making the data mart a more feasible solution for the business intelligence requirements.
A consistent standard data model and semantic rules, as well as central modeling committee within the BICC will have overview of the several data marts as they are designed and created. This is key to ensure that there is consistency in content and structure across the data marts, and if required some dimensions are appropriately conformed across to ensure future flexibility of the data mart models. Data marts are typically composed of one or more related opportunity areas.
A data mart can address more than one opportunity area when the areas have similar business users, dimensions and measures, or departmental sponsorship. For example, because product margin analysis and sales analysis both relate to sales, one may decide to group these and other related opportunities together into one subject-specific data mart called sales.
The overall goal is to roll these data marts out one a time, or in lock step as parallel implementations, starting with the ones that have the greatest priority and the lowest anticipated effort and then proceeding to the other data marts based on the BICC’s importance/difficulty rankings. Each data mart is treated as a separate project, with its own budget, timing, and success parameters. At the end of this process, the BICC will have a series of subject-specific data marts that will collectively define the new data warehouse or enhance the existing data warehouse, and thus form the structural foundation for the higher-level business intelligence vision.
