BI tools (OLTP,ETL,OLAP,MDX, BI Report)
Large amount of data gathered in one trade day and analysts want to analyze them in the next morning. Key factor in analyzing the data is prepare data quickly.
OLTP Data Bases
Sales data from retail outlets around the world in OLTP databases which has optimize with real time transactions . The source could be any type of virtual databases or the data The result will be one purpose ; Fast and interactive visual Reports of data for convenient decision making.
For example user map or graph and receive valuable data , this can be done in real time because of million of records. For such purposes there is a technology called OLAP Cube , They aggregate and store information in specific way that allows users the interactivity of reports in real time.
How data initially in various sources is loaded in the cube ?
First data must be arranged into consistent formation and combine in the single place, this is done by ETL process which are built by OLTP , OLAP Cubes and other source.
ETL process will be three steps .
- Data Extraction from Servers Ram (Oracle SQL Server and …)
- Transformation of data clean up
- Data Load in the shared storage which is called data warehouse
There is large amount of data in data warehouse that is more than 10 terabyte after the storage ETO process will give you ultimate command to connect the source to read and operate on raw data. As a result the cube contain the same data as data warehouses and aggregate in multi dimensional format that allows users rapidly query the data for Reports. This allows users generate reports in real time .Data analyst will use Query language MDX , which is the industry standard for accessing data stored in the lab databases.
The performance of these components and speed of interactions may be increased over time , which will happen if the system correctly designed.