Yeees after Dream life,Dream Wife :D, Dream team… now there 's a Dream Run Time.
But What is Run Time and who had ever dream of a Dream Run Time :D, at least I' m sure there are some people who they were always dreaming of a Dream Run Time :D.
When I attended my actual job as an IT engineer in LES EAUX MINERALES D'OULMES about one year and half ago, my boss said to me in our first meeting that my essential mission is to resolve the IT problems related to the sales department, so the first thing I did, was to get in touch with people working in the sales department.
What I retain about these meetings, that everyone was complaining about three major problems, the first was about our ERP system ORACLE JD EDWARDS; sales assistants said that the system is very slow and interrupts often, so entering sales orders onto the system was a painful operation who takes so many time than it should, even customers were complaining sometimes because they were spending too much time to make their orders, the second and the third problems were about our Reporting System which is based on Oracle Report Builder and Oracle Report Run time, they said that the Reporting system is very slow so we spend too much time to have data than to analyze it, they were also saying that the data provided by the reporting system is very poor to make their analyzes correctly and to take the right Business Decision.
So Run Time is a reporting tool from Oracle, using it, users can access reports already build with Oracle Report Builder, our IT department staff use Report Builder to develop reports which were demanded by other departments staff , the reports are composed essentially by SQL Queries upon Oracle materialized views which were created to make data aggregation.
Even I was hired by my company to resolve these problems :D, I didn’t have the opportunity to do it at the beginning , because I was asked to do other tasks; first I was involved to develop a lot of reports using Report Builder (daily sales reports, sales by regions, sales by product, pricing,... [Really a lot of reports :(] ) , helping the company staff to have the data that they need by making some data extraction from the database, initiating the project of the implementation of ITIL ( Information Technology Infrastructure Library ) into our Departments, helping to complete the integration of the sales order module of our ERP by driving some modifications in this module…, after that I was taking the responsibility of integrating the last part into our ERP which was the Fixed Assets part, this project took more than 4 months.
Waaaw, so many work :D, after finishing that I came back to my first mission hhh, and on the same time I was asking to take the responsibility of the DBA.
The first problem we had with our reporting system, it's that the ERP and the Reporting system used the same database, so both of them become slow due to the high activity of the two system, so we decided to put the reporting system into another database, as a DBA I took the responsibility of this task (I talk about this task in a previous post in this blog hhh :D).
That’s resolved only the first problem; the ERP works better :D, because there' s less activities in its database but the Report system remained slow and the biggest problem that in some times the reports aren't available at all due to the incompleteness of the refreshing process of the materialized views, imagine if the sales staff can't access in a whole day the information about their sales, it's like driving a car without a dashboard hhhhhh :D, this is what was happening hhhh :D.
So how can I resolve this problem :D ? at the beginning I tried to make a tuning to the Reporting database so the refreshing process takes less time and I succeed in this hamdolilah :), after I try to tune reports by creating appropriate indices, this make the reports very fast HoHoHo, for example some reports took about 15 minutes and after creating the right indices now they take less than one minute (great results :D).
But even at this level. I was not satisfied, because I had an aim which I didn't reach it yet hhhh, so I made a lot of efforts to recreate the materialized views with fast refresh option so they can be refreshed very fast (so never we will had to drive without a dashboard hhh), it was really a big challenge for me , I made a lot of efforts to do it, and I was so determined to do it, it took to me more than 3 weeks of hard work hhh.
But thanks to Allah who said إن لا نضيع أجر من أحسن عملا} الكهف 30} , I success to reduce the period of refreshing the materialized views from 8 hours to only 30 minutes (if you want to know How hhhh? be aware, soon inchallah I will provide a tutorial about materialized views in my BI blog http://bi-only.blogspot.com/), so we will never lost direction hhhh, and now really i reach my aim a Dream Run Time :).
NB:The picture Above show the database activity before transforming the materialized views to the fast option and the picture on the bottom after, I think you see the difference :D.
But thanks to Allah who said إن لا نضيع أجر من أحسن عملا} الكهف 30} , I success to reduce the period of refreshing the materialized views from 8 hours to only 30 minutes (if you want to know How hhhh? be aware, soon inchallah I will provide a tutorial about materialized views in my BI blog http://bi-only.blogspot.com/), so we will never lost direction hhhh, and now really i reach my aim a Dream Run Time :).
NB:The picture Above show the database activity before transforming the materialized views to the fast option and the picture on the bottom after, I think you see the difference :D.
