BI Master - BI Architecture

Dear friends,
Today, I will show you the BI Architecture defined to my master degree (data warehouse class) that it’s also based in the project that I’m developing to my company. The objective is not explain in detail each module definition, but describe how I apply the BI architecture to my project. I suppose you already read a lot about this subjects like ETL, ODS, DW, Cube, BSC … I want to show you what I’m doing and I want to receive feedback from you to improve my work and to share knowledge between us.

The architecture defined for the project follow the Inmon approach, the 3 tier-architecture.
It was necessary to customize the ODS in order to satisfy one of the business requirements that is to keep the data imported dynamic. Keep data dynamic means that the business user administrator could change the data (from last year) directly to ODS when he needs. The reason is because some data from operational system must be corrected or because it has bad quality or there is an error. The data inside ODS is stored for one year.

The following image shows the relation between all modules and how they are integrated between itself.

1. Operational Systems (OS)
Operational Systems record details of business transactions in real time. It’s here where data is stored and will support data warehouse. These operational systems include data from external data sources like interest rates and exchange rates, and others…

2. Extract Transform and Load (ETL)
The ETL, Extract, Transform and Load data from Operational System into OLAP database.
There are two ETL processes in this architecture:
2.1 Makes the extraction of the data from OS, transform and integrate into the ODS.
a. Initial ETL
b. Daily process ETL
2.2. Makes the processing update of the cube.

3. Operational Data Store (ODS)
After the extraction, cleanness and integration of the data from the operational systems into ODS, it is necessary to include additionally calculations and aggregations to allow indicators to be generated dynamically to our OLAP cube in spite of create it static inside an ETL. Normally, all the calculations are made inside an ETL, but in this case the business requirements imply a customization. And these requirements are:

· Problems in financial instruments evaluation.
· Requests from managing entities to change positions
· Positions corrections
· Data in operational systems could be incorrect

4. Data Warehouse – OLAP Cube
In this module, is implemented the multi dimensional view of the new system. It will be created the famous cube that will enable the users to visualize business indicators from different perspectives. This cube is supported by an OLAP database and an OLTP database that will process the cube and update periodically the OLAP database, MOLAP.

5. Reports and Data Analysis
In this module will be available all the value, measurable and complete information that will be important to decision makers. Will be created static reports (pre-defined) and dynamic reports (Adhoc-reports) that allows the business users to create their own questions to the cube.

Feel free to make questions!
Regards!
Pedro.