In the past few weeks we have had a lot of materials that covered--data warehouse design cycle, balanced scorecard, dimensional modeling, star schema design, and dash board design. Especially data warehouse design remind me a meeting a couple years ago.
As we know, there are two major types of organizational information, operational database and data warehouse. Many companies they directly use their operational information and also many companies are using data warehouse, meaning OLTP (Online Transaction Processing) vs. OLAP (Online Analytical Processing). The meeting I had a couple years ago, a project manager suggested that we must generate reports from data warehouse ( the company didn't have one), but one of the developer asked why not we cannot directly use OLTP data. No one answered.
In OLTP database there is detailed and current data, and schema used to store transnational databases is the entity model ( usually 3NF). OLAP is characterized by relatively low volume of transactions. Many small organizations often directly access data from their Online Transaction Processing systems. Directly using Online Transaction Processing system can save money for small organizations. They don't have the budget for many IT staff and expensive applications. The primary advantage to query data and generate reports is speed to information. The data accessed in the Online Transaction Processing system is the "raw" transaction data, and the queries are often less complex. Of course there is no need separately maintain a data warehouse. The disadvantage of reporting directly against Online Transaction Processing database is it slows down the process apparently.
Using Online Analytical Processing in business intelligence helps to organize huge amount of information for end user and perform efficient data analysis. The main advantage is the speed of query execution. Often we hear from work, the report is take too long. By using correctly designed cube, reports usually process very fast, just a few minutes.
References:
1. http://datawarehouse4u.info/OLTP-vs-OLAP.html
2. https://galaktikasoft.com/ranet-olap/advantages-of-using-olap-for-business-intelligence.html
Hi Sijun,
ReplyDeleteGood job! I enjoyed reading your blog. I liked that you focused on the difference between OLTP verses OLAP.
I can really relate to complaints about things taking too long. At my organization, we are working on minimizing major reporting against the production instance during the day. We encouraging our users to set up overnight deliveries for their larger reports in order to ensure the system does not get bogged down by reporting during the day while people are working.
Thank you,
Joe Ledbetter
Ita useful for Module Two--OLTP vs. OLAP blog post!!!
ReplyDeleteGranular Analytics
Analytics for Micro Markets
Hyper-Local Data
Hyper Local insights