ALSIC has recognized the need for Business Intelligence very early as an added value to sharing and analyzing information within an company. Companies spend a lot of money on transactional systems that generate valuable information. They often have poor reporting capabilities like the use of spreadsheets, inhibiting the sharing of key information between or inside companies departments.
The following description is an example of how we built generic Business Intelligence solutions. We take as base the Corporate Information Factory (CIF) model as defined by William Inmon, one of the founders of data warehousing and BI systems. The philosophy of the MS SQL Server BI platform follows close the CIF model. The CIF model is a top-down design approach and a Datawarehouse build by this model is follows these rules:
- Subject-oriented: The data in the data warehouse is organized so that all the data elements relating to the same real-world event or object are linked together.
- Integrated: The data warehouse contains data from most or all of an organization's operational systems (different data sources) and these data are made consistent with each other. This is the most important aspect of a data warehouse.
- Non-volatile: Data in the data warehouse is never over written or deleted. If data changes a historical record is kept in the data warehouse.
- Time-Variance: Every piece of data in the warehouse has is accurate in particalur moment in time. The key structure of the data should always be provided with a time element.
We specialize in delivering global solutions based on the MS SQL Server platform. The figure below shows our MS SQL implementation of the CIF model.
The solution can be split in three different parts:
Extract transform and load of raw data (ETL)
Analyze the processed data
Apply the results found in de data warehouse and report them
Extract Transform & Load
The ETL process is responsible for storing data in the data warehouse. It has the following common steps:
- Identifying data from different sources.
- Extract and verify.
- Convert and combine.
- Storing the data in a structured way in the data warehouse so it is easily accessible and optimized for further analysis and reporting purposes.
ALSIC recommends MS SQL Server Integration Services for realizing ETL processes because it’s one of the best and most advanced ETL tools.
The core of business intelligence is the data warehouse which is the central data storage for integrated information. On a conceptual level it is the interface between the various data sources and the offering information to end users.
SQL Server database engine is being used for the data warehouse. The data warehouse is different from operational data bases:
- A data warehouse is subject oriented where the information is integrated as much as possible.
- Data is being gathered from different sources and unified for further analysis purposes.
- Information in a data warehouse typically ranges from 5 to 10 years.
- Key information always contains a time element.
The data warehouse exists out of different types of information that are presented to end users as a single information source called the virtual data warehouse. These types are:
For Online Analytic Processing, MS SQL Server Analysis Services are being used. This allows the aggregation of massive amounts of data in conjunction with dimensions. The features of OLAP are:
- Getting the results from a massive amount of data (records) is a matter of seconds.
- OLAP is being used when there is a need for interactive exploration of information.
- The basis of OLAP is a n-dimensional cube.
- Cube information is also read only.
- Cubes need to be processed in order to get new information.
- The last step is supplying information to the end user. This features:
- End-user should be able to access information in a flexible way suited to their needs.
- Information should be delivered to the end users on time.
- The information delivered to the end user should be consistent.
MS SQL Reporting Services is being used to deliver information to the end user. This technology allows users to consult predefined reports with various exporting capabilities.
MS Excel can be used to allow end users to access cubes and perform OLAP analysis.
A report model and report builders allows end user to define their own reports bases on a predefined Report model.