Posted on

End To End BI 1

Star Schema

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.

Star Schema
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.

Posted on

The End to End BI Concept

Considering the Full Landscape

Cornerstone Solutions® use an End to End BI approach and here’s why. When thinking about Business Intelligence architecture we need to consider the entirety of the component parts as a whole and not simply individually. In other words consideration is made of the full BI system as a whole and the individual components are not treated in exclusivity of each other. This is because the components will need to act interdependently regardless of whether they have been procured from a single or multiple software vendors.

In this respect the BI system is similar to the human body i.e. everything is so closely related that a felt symptom in one area (a pain in the arm) may be caused by an unseen symptom in another part of the body (a problem with an internal organ). To relieve the pain felt in the arm we treat the unseen causal effect in the internal organ. The same concept of unseen relationship and causal effect applies to the Business Intelligence system. Data flows from end to end through the Business Intelligence system in a similar way to blood circulating in the body and must not be blocked, lost or corrupted at any stage. The BI Architect must ensure this.

End to End BI
The components in the BI system are akin to a linked interdependent chain

Interdependency of the BI system

The early phases of a BI implementation can be usefully considered as akin to those of the Rational Unification Process (RUP) stages of Strategy, Inception, Elaboration, and Construction. A well defined BI strategy is very important. However, perhaps paradoxically the Construction phase is often delivered using an Agile delivery method.

Construction frequently commences with source system analysis, end user requirement gathering and the installation and configuration of the software components. If using SAP Business Information Warehouse, cubes and queries will be developed, or if a relational platform is used a dimensional modelling exercise is undertaken and then the physical tables are developed. The ETL system is designed and developed and reports and dashboards are built.

The relationship between these things is one of interdependency. It’s like a linked interdependent chain. This is why in the implementation methodology of BI System Builders we practice our philosophy of End to End BI. End to End BI takes the view that as each component in the BI system has interaction with and therefore dependency on its related components, BI Breakpoints can occur. BI System Builders take full consideration of the interdependencies in the landscape to ensure prevention rather than cure in their End to End BI project delivery method.