The XYZ Ltd Booking Information System website was then created to simulate the train ticket booking process and used this relational database to insert records into other tables. This website can be seen as an example of online transaction processing (OLTP) system, often simply referred to as transactional systems. They are designed to process small, quick, interactive workloads for which users expect a fast response time. (Root, R., & Mason, C., 2012)
Figure 3.1: Screenshot of XYZ Transport Limited’s website
3.4 DATA ETL (EXTRACTION, TRANSFORMATION AND LOADING)
ETL as a BI concept means Extract, Transform and Load. In an ETL process, data is firstly extracted from a number of multiple systems which is usually unstructured. The extracted data is then transformed into a structure ideal for analysis and reporting processes. Data is then loaded into the cube(s) for visualization. (Passionned Group., 2014)
As mentioned before for OLAP the data is entered through the online booking website, data is saved in an SQL Server database, The data was extracted from the database to an excel file as raw data:
As below a query was run and then saved the results as an excel file:
OLAP (On-line Analytical Processing): In the OLAP process there is a high level of data transformation and aggregation. This usually consists of complicated formulas and queries to produce the relevant required data. (Data Warehouse, 2009)
After the data was extracted from the database to excel we transformed it and assigned a specific page for each route.
For example the figure below shows the data of Sheffield – Manchester route,
When all the data routes are transformed to an excel page, OLAP process is launched to be applied by manipulating the data using complicated formulas as to obtain the upper layer of aggregated data.
For example: As the figure above, in column N, an IF formula was used to obtain the week split from the date of travel field: =IF(D3>41719,"Week4",IF(D3>41712,"Week3",IF(D3>41705,"Week2","Week1")))
The same formula is used in column O to obtain the week day from the date of travel field: =IF(E3>0.75,"SHIFT4" &"_DAY"& DAY(D3),IF(E3>=0.5,"SHIFT3"&"_DAY"& DAY(D3),IF(E3>=0.25,"SHIFT2"&"_DAY"& DAY(D3),"SHIFT1"&"_DAY"& DAY(D3))))
After aggregation of the data to obtain the higher level, the results for the route (in our example Manchester - Sheffield) was positioned in a standard area of the route page like below:
Figure 3.4: Result of MS excel Transformation
As seen from the above figure, we obtain the total income, no of passengers and No of confirmation for the route Manchester – Sheffield on a Monthly, Weekly, Daily and Hourly basis. This is repeated for all the six routes.
The transformed data for each route (each page) was combined in one central page, which will be our operation room from which we load our BO Xcelcius for visualization purposes, as below:
With the record or data of the employees productivity and...