Posted on

End To End BI 1

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.

Posted on

ETL Data Services

A critical component in the BI system is the ability to take your source system data, clean it, and then load it into data your warehouse. It is this quality assured data that forms the backbone of your ‘single version of the truth’ in business intelligence. To achieve the timely loading of quality assured data a software program known generically as an ETL tool is used. We’ll take the SAP BusinessObjects suite as an example. The tool is based on an ETL program originally known as Data Integrator. Data Integrator is often used with another component tool known as Data Quality. As its name suggests Data Quality provides advanced data cleansing capabilities. Metadata Manager is a third component tool that can be used closely with Data Integrator to provide visibility into data lineage and data dependencies. There are several similar ETL tools in the market the best known of which are Informatica and Microsoft’s SQLServer Integration Serices (SSIS).

Posted on

Business Intelligence Dashboards

A dashboard can be thought of as a type of business cockpit. The dashboard can be corporate i.e. shared with several users or it can be personal for the consumption of the owner only. A dashboard usually provides an information summary of the corporate or user’s view of the world with hyperlinks to more detailed reports.

Corporate information can be consumed and shared through many different types of dashboard visualization. Examples of the information presented on a dashboard might be a list of KPIs, a balanced scorecard, a strategy map, alerts on statistical process charts, and data analytics.

Although a dashboard looks like a single component it will usually consist of several different components using widgets and reports integrated together seamlessly. An example of a canvas for building a dashboard is SAP BusinessObjects’ Dashboard Builder.

The most powerful dashboards are also designed to be interactive. In terms of SAP BusinessObjects they use an interactive dashboard tool known as Crystal Xcelsius (now renamed SAP BusinessObjects Dashboards). Once the Xcelsius Dashboard has been created it can become one of the many components on the main dashboard. When Xcelsius is combined with Query as a Web Service the data can be refreshed at the click of a button.