BI Tools
Advantages of a DWH
There are several advantages when building and reporting data from a Data warehouse:
1) The main platform system is not loaded with extra processing needed to extract reports. This extra processing can degrade the platform with the consequences of
- A bad user experience while playing.
- Poor performance while the employees are using the Back-Office.
2) Integration of different sources into one centralized database.
3) Reporting is simplified because the data is all found in one place.
4) Data transformation, verification & validation is done only once and not per each reporting solution, leading to Data Standardization and Consistency.
5) The Data Transformation will be customized for customer and therefore the data schema of the DWH is optimized to satisfy each customer reporting needs.
6) Any KPIs which currently do not exist on the current system can be written specifically for the client and these will be processed during the ETL and not during the report rendering.
7) Adding new data to a report/dashboard/cube is simplified because the max effort is carried out once to move the data to the DWH.
8) Enhanced return on Investment (ROI).
9) Improves data security.
10) Storing historical data in one centralized database.
The DWH can provide the source of data to multiple BI outputs:
- a) Power BI paginated Reports which are Ideal for the fixed statutory/fixed/daily reports.
These reports can also be scheduled to be received automatically by email in several file formats such as csv, excel, pdf etc.
- b) Dashboards – Power BI/ Tableau
- c) OLAP Cube – SSAS
- d) SQL – Direct querying using SQL statements, for data analytics and scientists
Overview
a. Data from the source platforms will be read on a daily basis.
i. Most DWH systems, data is read once a day however the frequency can even go down to hourly.
ii. This process is non-intrusive and therefore will have very little effect on the performance of the source system.
b. The source data will be staged into a staging database. It will be copied ‘as is’ in flat tables.
c. Once all the source data has been staged, the data will be loaded into the Datawarehouse.
i. During the loading phase, data will first be verified.
ii. After which the data will be validated.
iii. Finally, the data is loaded in the Datawarehouse structures in the form of dimensions and facts.
d. All of the above process will be fully automated.
i. All control and audit logs will be stored into a sperate ‘Audit’ database.
ii. Another important function of the Audit database is to offer resilience to the ETL process. The process can be started/stopped without any data loss.
iii. The DWH will be built in such a manner that after the sign-off, very little manual intervention will be required.
General Schema
The Datawarehouse will be built using Ralph Kimball methodology, i.e., using data marts organized in a Star schema (which is the best schema to build OLAP cubes and facilitate reports).
One of the data marts will be the Transaction data mart. This will consist of a centralized transaction fact table surrounded by its relevant dimension tables (as detailed below)
