End to End BI Recommendations
This is the first in a series of short articles elaborating end to end BI philosophies and methods.
In the end to end BI philosophy (referred to as Cornerstone Solution® by BI System Builders) the BI component of the data warehouse is considered to consist of four layers. The data warehouse is not regarded as simply a set of physical tables and views on a RDBMS.
The four layers are the Staging Layer, the Data Mart Layer, the Semantic Layer, and the Presentation Layer. Collectively these can be thought of as a Business Intelligence Data Warehouse (BIDW). This is an important principle because this way of thinking engenders the beginning of an end to end BI mindset.
Achieving a successful BIDW commences with modelling the data mart layer according to business user requirements. Committing to undertake this activity first becomes a catalyst to all other necessary requirements to create an end to end BI solution:
- The Business Architecture is considered (‘as is’ and ‘to be’) and the Business Event Analysis and Modelling (BEAM) methodology is used to understand the business requirements. This will result in the development of a set of logical dimensional models that will later directly translate to physical data warehouse tables. These physical models form the ETL target tables in the data mart layer.
- Data profiling of source data should now be carried out and fields mapped from source systems/ source files to staging tables. This activity will enable the commencement of ETL job development to populate the staging tables. Note that a source for BI may be among others a flat file, third party data, a legacy system, an ODS, or a 3NF data warehouse.
- Fields are now mapped through from staging tables to data mart tables enabling further ETL jobs to be developed.
- The semantic layer is configured as an abstraction of the data mart tables for business use and querying purposes. It’s a good practice to start this early as it often illuminates any flaws in the dimensional design, thus de-risking the programme.
- BI reports can be developed in the presentation layer for information consumer purposes.
The next article will be a short introduction to sizing, hardware and BI Platform for the BIDW.