Just like a house, Data Warehouse also has it architecture, which captures all blueprints ranging from aesthetic patterns & embellishments to the tensile strength of the reinforced concrete. Following are the architecture components of a data Warehouse:
Data Warehouse Architecture- Functional
Business Requirements This is the list of business requirements as captured in the Data Warehouse Business Requirement Phase . This will be the top page of the architecture book as the starting point. Dimension Model This is the logical representation of how data will be organized to ensure that the business requirements are met and they are extensible. This subject is also covered in the Data Warehouse Dimensional Modeling Concepts and Data Warehouse Dimensional Modeling Process.
Data Warehouse Design and Architecture-ETL
This is the blue print covering the journey of data from the production/Source systems to the final resting place, that’s the loaded area, where the users through end user tools will use it. Source Systems This part covers the details of Source Systems, their owners, and brief descriptions, which data they contain which will be used for Extraction.
Extraction Design
Extraction Design part covers the details of how data will be pulled out from the source systems to the staging area (which is the play-ground for transforming the data to be placed in the loaded area). It covers the details about which and from where the data will be pulled out, when it will be pulled out and where it will be stored in the staging area and in which form.
Transformation Design
Transformation Design part covers the details on which data will undergo what transformation and in what sequence till it is ready to be loaded.
Loading Design
Loading Design part provides the details on which, how and when the prepared data set (through Transformation) has to be loaded in what sequence and in which table and in which data mart in the loaded area.
NOTE- the design elements for all of the above (source systems, Extraction, Transformation and Loading) will contain the details on any tool which will be used, the configurations of the tools etc. AND the reason for selection of these tools.
Data Warehouse Management Architecture
This blueprint of the architecture provides the details on how the management of Data Warehouse back-end and front-end activities will be done.
Meta Data Design
Data Warehouse Meta Data, in its traditional definition is called 'Data about Data'. However, it is used for much bigger purpose. This can (OR ideally) contain all information, and in other words can be called the central reference Repository. You can refer General Section on Metadata more details.
Job Control
Details of this part can be included in Meta-Data. Nonetheless, it needs a separate mention. Most of the back-end operations (ETL.) happen in batch mode. The Job Control and Audit is the process flow chart of the jobs, which will be executed, their timings, their linkage to other jobs in precedence/ succession terms, their start & end criteria, the failure definitions, error handling, notifications etc. This includes the ETL operations and interim quality assurance jobs as well.
Access & Security This part contains the access & Security matrix to all components of Data Warehouse, which includes the Database in staging, in loaded areas, jobs functions and scripts, versioning system, user access through end user tools. Activity Monitoring and audit This part contains the details on how the various activities will be monitored including jobs time and resource spend, the job run reports, the access and usage details by end user tools. Back-up and Archival This part will provide the detail on back-ups (their timings, methods, destination, destination storage.) and archival method and process.
Access and Delivery Architecture
This blueprint provides the technical description on how the services will be provided. Warehouse Browsing Data Warehouse Access and Browsing part details on the areas of meta-data, which can be browsed by which all users and applications. It also covers the presentation method.
Query & reporting Management
This part covers the functions and tools, which will manage the query requests to the loaded OR staging area. While the loaded area (presentation server) is the final destination of data for usage, many organizations are also using the staging area, where they access the transformed and cleansed data for other purposes.
Infrastructure & System Platforms Hardware This part contains the hardware, make, capacity & configurations details all components of a data- Warehouse. Operating Systems This part contains the operating systems details in terms of it being MS OR Unix etc. Staging Area DBMS This part details out the DBMS (mostly RDBMS) platform and its configuration. It will state the platform, size distribution across data tables, indexes, buffers, workspace etc. Loaded Area DBMS
This part contains the DBMS (could be MDDB- Multi Dimensional OR RDBMS) platform and its configurations. The above will include a detailed explanation of the reason for the selection of the above-said components.
Tools and Destination Systems
Though tools and destination systems are not integral part of a data-warehouse. They are users of data warehouse and not the data warehouse itself. Yet, most of the data warehouse will have these tools etc. part of the project, as the end result is to bring the data to the users. You can also refer BI Architecture Scenarios to see how these tools fit in the big picture.
Standard Reporting Tools
This part contains the details on the reporting tools, their configurations, functionalities and capabilities, the number of initial licenses, the operational details of reporting, the destination of reports etc. It covers high-level details on the areas (in staging and loaded area) and the data-marts, which these reporting tools are expected to access.
Analytics tools
It contains the specification of the tool used and its configuration. The details are also linked to what all capabilities exist in the tool.
Data Mining/Modeling Tools This part details on the data mining and modeling tools, which will be accessing the data, their timing, source and frequency. Downstream Data-marts
This part details out the downstream data-mart, which the data-warehouse may be populating, their timings and frequency.
Downstream Operational Systems This part details out operational systems to which the data warehouse will be feeding the data (typically CRM, Field systems OR operational data stores), which data will be populated, the frequency and the timings. |
