Welcome to “Data Warehouse Architecture Overview.” After watching this video, you will be able to: List use cases that drive data warehouse design considerations. Describe a general data warehousing architecture and list its component layers. Distinguish between general and reference enterprise data warehouse architecture and Describe reference architectures for two enterprise data warehouse platforms. The details of the architecture of a data warehouse depend on the intended usage of the platform. Requirements can include report generation and dashboarding, exploratory data analysis, automation and machine learning, and self-serve analytics. Let’s start by considering a general architectural model for an Enterprise Data Warehouse, or EDW, platform, which companies can adapt for their analytics requirements. In this architecture, you can have various layers or components, including: Data sources, such as flat files, databases, and existing operational systems, an ETL layer for extracting, transforming, and loading data, optional staging and sandbox areas for holding data and developing workflows, an enterprise data warehouse repository, sometimes, data marts, which are known as a “hub and spoke” architecture when multiple data marts are involved, and an analytics layer and business intelligence tools. Data warehouses also enforce security for incoming data and data passing through to further stages and users throughout the network. Enterprise data warehouse vendors often create proprietary reference architecture and implement template data warehousing solutions that are variations on this general architectural model. A data warehousing platform is a complex environment with lots of moving parts. Thus, interoperability among components is vital. Vendor-specific reference architecture typically incorporates tools and products from the vendor’s ecosystem that work well together. Next, let’s check out IBM-specific reference data warehouse architecture. Each layer of the architecture performs a specific function: The data acquisition layer consists of components to acquire raw data from source systems, such as human resources, finance, and billing departments. The data integration layer, essentially a staging area, has components for extracting the data, transforming it, and loading it into the data repository layer. It also houses administration tools and central metadata. The data repository layer stores the integrated data, typically employing a relational model. The analytics layer often stores data in a cube format to make it easier for users to analyze it. And, the final presentation layer incorporates applications that provide access for different sets of users, such as marketing analysts, users, and agents. Applications consume the data through web pages and portals defined in the reporting tool or through web services. IBM reference architecture is supported and extended using several products from the IBM InfoSphere suite. IBM InfoSphere DataStage is a scalable ETL platform that delivers near real-time integration of all data types, on-premises, and in cloud environments. IBM InfoSphere MetaData Workbench provides end-to-end data flow reporting and impacts analysis of information assets in an environment that allows organizations to share easily, locate, and retrieve information from these systems. Use the built-in data flow reporting capabilities to monitor how IBM InfoSphere DataStage moves and transforms your data. IBM InfoSphere QualityStage, designed to support your data quality and information governance initiatives, enables you to investigate, cleanse, and manage your data. This solution helps you create and maintain consistent views of key entities, including customers, vendors, locations, and products. IBM Db2 Warehouse is a family of highly performant, scalable, and reliable data management products that manage both structured and unstructured data across on-premises and cloud environments. And finally, IBM Cognos Analytics is an advanced business intelligence platform that generates reports, scoreboards, and dashboards, performs exploratory data analysis, and even curates and joins your data using multiple sources. In this video, you learned that: An architectural model for a general data warehousing platform includes data sources, ETL pipelines, optional staging and sandbox areas, an enterprise data warehouse repository, optional data marts, and analytics and business intelligence tools. Companies can modify general enterprise data warehouse architecture to suit their analytics requirements. Vendors offer proprietary reference architecture based on the general model, which they test for interoperability among components. An IBM enterprise data warehouse solution combines InfoSphere with Db2 Warehouse and Cognos Analytics.
