logo for information-management-architect.com
leftimage for information-management-architect.com

Business Intelligence Architecture

Improve service with David Bowman’s information management guidelines for business intelligence architecture

This site is designed for Information Technology professionals who need to improve service and require guidance and direction to help teams consistently produce error free results.

It provides information management guidelines for business intelligence architecture.

Data Warehouse Architecture

The following diagram depicts a typical data warehouse and business intelligence environment.

data warehouse architecture

Information management solutions are usually involved with some, or all of, the following:
  • Extracting data from operational source systems;
  • Transforming data as required to ensure consistency;
  • Loading data into a data warehouse or some form of storage optimized for efficient storage;
  • Extracting data from the data warehouse;
  • Loading departmental data marts optimized for reporting and analytics; and
  • Some form of reporting tool architecture e.g. universe design or some middle ware design to insulate users from the data warehouse.
Information is a valuable corporate asset and getting the right information, to the right people at the right time is a key management objective.

Achieving this objective requires robust technology and optimal data integration architecture based on industry best practices.

Business Intelligence Architecture Environments

Business Intelligence Architecture for Data Marts
  • Should ensure that only one version of the truth exists for each reference, control, dimension, and fact table;
  • Should ensure that data mart dimension tables have a name that begins with 'DIM' and the rest of the name conveys the business concept for that table;
  • Should ensure that dimensions table column names will be the same as the names in the EDW;
  • Should ensure that data mart fact tables have a name that begins with FACT and the rest of the name conveys the business process that defines the measures/columns;
  • Should ensure that fact table columns are the same as in the EDW and ensure that new columns are created to represent a new measurement concept e.g. calculated columns;
  • Should use the generated unique key identifier added in the EDW as the primary key of each row in the data mart tables;
  • Should ensure that constraints are dropped before loading and re-applied after data loading;
  • Should ensure that a date dimension is added as a one-time load and has the ability to extend the number of rows if needed;
  • Should ensure that a semantic layer or business friendly mask is developed to insulate business intelligence/reporting users from the fact/dimension table/column names;
  • Should ensure data integration processes loads each row from the EDW that matches selection criteria;
  • Should ensure data integration processes use the same column characteristics as the EDW;
  • Should ensure data integration processes calculate any fact table columns as required;
  • Should ensure data integration processes handle slowly changing dimensions; and
  • Should ensure data integration processes populate audit columns as appropriate.
Sandbox
  • Should provide power users the capability of creating their own sandbox up to a pre-defined size without requiring assistance or permission from database administrators or other information technology staff;
  • Should provide power users the capability to load their own data into their sandbox;
  • Should provide the capability to combine sandbox data with production data for specialized analytics;
  • Should provide the capability to restrict sandbox access to only authorized sandbox users;
  • Should ensure that sandbox data is not used to production reporting;
  • Should ensure that sandbox data is not used to provision other reporting data marts; and
  • Should ensure that governance processes purge data per a predefined service level agreement.
Extracts
  • Should be obtained from the data mart using the business intelligence reporting tool and/or other data extract tool;
  • Should provide the capably to download extracted data to user personal computers using the functionality of the intelligence reporting tool or data extract tool; and
  • Should provide the capability to send extracts to internal/external users upon request.
Portal
  • Should be designed as a one-stop entry point with single-sign on capabilities to ensure identity management, authentication and authorization for all users; and
  • Should direct users to their appropriate data mart or sandbox.
Metadata Repository
  • Should be created to support all components of the data warehouse and business intelligence operations;
  • Should contain business metadata, application or technical metadata; and process or event metadata;
  • Should provide the capability of automatically notifying metadata users of changes to metadata that impact their area of interest;
  • Should provide the capability of storing and reporting business rules about data stored in the solution;
  • Should provide the capability to allow users to view and report metadata as appropriate to their role; and
  • Should provide the capability to store and report metadata security classification rules associated with every data element in the solution.
Development and Test Environments
  • Should include personal schema's, used by individual developers for development and unit testing prior to certifying that code is ready for system testing;
  • Should include system test, used to certify that a related group of modules is built to design specifications and is ready for integration testing;
  • Should include integration test, used to certify a release is built to design specifications and is ready for quality assurance testing;
  • Should include quality assurance test, used to certify a release is is built to requirement specifications and is ready for user acceptance testing;
  • Should include user acceptance test, used to certify that a release meets user expectations and is ready for production; and
  • Should include release test, used to certify that all components of a release package work as expected and that there is no adverse affect on production.
Data Marts
  • Should be a critical part of data warehouse design;
  • Should be created to store data required for analysis by specific departments e.g. manufacturing department, sales department or inventory control; and
  • Should contain data extracted from the warehouse and transformed into facts and dimensions within the data mart.
Business Intelligence Architecture Facts

A fact is something that has occurred e.g. an invoice was sent, on a specific date, to customer A, at address B, for a specific quantity of product X, that was sold by sales representative Y.

Business Intelligence Architecture Dimensions

A dimension is a way of analyzing the fact e.g.
  • Date or time period dimension answers questions such as what was the quantity of product X sold by date, week, month, quarter, year or fiscal year.
  • Product dimension answers additional questions such as what was the quantity of product X sold by e.g. product type, product category, product market interest group or any other product related attribute.
  • Customer dimension answers additional questions such as which customers purchased product X, or conversely what products were purchased by customer A.
  • Address dimension answers additional questions such as what product was sold by city, sales territory, state/province, country; or conversely which customers live in each of the address areas?
Business Intelligence Architecture for Star Schema

A dimensional model usually has the fact in the middle with the dimensions surrounding it and it looks like a star, hence the name, star schema.

Business Intelligence Architecture for Snowflake

Dimensions are usually de-normalized e.g. each row of an address dimension might contain address id, city name, sales territory name state/province name and country name.
Sometimes, for performance reasons, the dimension is normalized e.g.
address dimension contains address id, city name. sales territory name, state/province code and country name and state/province dimension (or snowflake) contains state/province code and state/province name.
The snowflake is related to the dimension and reduces the amount of data that needs to be duplicated in the address dimension e.g. only the state/province code, which is the primary key, needs to be maintained on the address dimension.

Business Intelligence Architecture Guidelines
  • Should maintain a history of changes and support slowly changing dimensions;
  • Should provide a semantics layer, shielding business users from having to understand technology and IT-related terms;
  • Should include usage-monitoring reports;
  • Should be capable of logging usage data;
  • Should identify data elements in demand versus those that have become obsolete and can be removed;
  • Should proactively manage performance impact through usage governors;
  • Should provide a full range of Business Intelligence capabilities e.g. operational reporting, ad-hoc query reporting, OLAP, data mining, corporate intranet portals, corporate performance management scorecards, dashboards, etc;
  • Should include analytic applications;
  • Should define a data classification scheme, which identifies different levels of data sensitivity and the corresponding protective measures; and
  • Should provide the capability, where appropriate, for business user to “drill through” or “drill across” the various physical and logical layers in the information.
Summary...

Information is a valuable corporate asset and getting the right information, to the right people at the right time is a key management objective.

Achieving this objective requires robust technology and optimal data architecture based on industry best practices.

This site provided information management guidelines for business intelligence architecture.