January 15, 2013

Seven Drawbacks of Traditional OLAP



In 1993, E.F. Codd, the acknowledged founder of relational databases, introduced the term Online Analytical Processing (OLAP). OLAP is intended for the non-data processing professionals like the business expert, and is expected to be intuitive, rapid, and flexible.

  Intuitive user interface: clear a technical roadblock for business personnel to operate freely
  Rapid analysis procedure: accommodate the fast-changing business environment to seize opportunities and make decisions
  Flexible computing ability: able to confront many complex business computation

When the traditional OLAP product was just introduced, it experienced 2-digit growth rates in the global market for years before clients start to complain about that projects would easily fail, no obvious effect, and the time span is a bit too long. Since 2004, the growth of OLAP drops dramatically, owing to the seven major drawbacks of traditional OLAP tools(
what’s the remedies of OLAP’s drawbacks?):


1. Pre-modeling as a must
Regarding the business data, the traditional OLAP tools do not allow for the immediate analysis without pre-modeling. These tools without a good OLAP engine cannot convert the data to a pattern in which business personnel can operate directly.

Assume that you are requested to analyze the profit for a telecommunications enterprise. Firstly, you need to draft a star or snowflakes model involving the date, region, client gender, client’s occupation, credit ratings, and other dimensions. Then, adapt the model to the practical database through repeated modifications. For example, remove the dimension of “customer’s occupation” from the model if it is not covered in the business data; replenish the ignored dimension of “hierarchy of consumption” to the analytical model; and use the ETL tool to synchronize the “sales network” from another database to this database. The finalized stable model will be filled with data regularly at scheduled times for use in analysis. Even so, you will find that the data is not up to the convention or short of historical data. Then, you will have to establish an additional data warehouse or data mart; In case no key data from the model is found, then you will return to modify the business system instead.

Modeling is a time-consuming procedure of a great many steps. Users thus have to pay the expensive cost in advance.

2. Great dependence on IT
Although business personnel is the intended user of OLAP, they will still have to work with the IT pros because the traditional OLAP tools requires a complex modeling procedure and its users have to write a great numbers of codes/scripts/SQL.

The below jobs cannot be completed without the assistance from technicians: Propose a common analytical model; Map dimensions of model to the names of fields, tables, and views of the business database; Use ETL tool or Perl scripts to migrate and integrate all required data to a same database if they are in different databases; Establish the reasonable scheduling rules, and fill data to the model by writing SQL statements or stored procedures; Deploy OLAP application to the server, and write .net/java application for user to use. Not to mention setting-up the database warehouse or data mart and modifying the business system to replenish the key data, business personnel is unable to handle without IT involvement.

The traditional OLAP tools depend heavily on the involvement of IT pros, and cost great human resources. What’s even worse, the IT pros without business expertise cannot fully understand the analysis goal. The model built by such IT pros may not be trustworthy enough. OLAP projects built through months or years of efforts often deviate away from the requirement of actual business, which gives rise to lots of project failures.

3. Poor computation capability
The data computing refers to a procedure of processing and transforming data through a series of specific steps toward a concrete goal. Data computing is a basic feature of OLAP. The traditional OLAP tools are of insufficient computational capabilities and few computational methods such as drilling, slicing, rotation, and simple column computation. This is because their architectures are old, lacking the innovation, and hard to strike a balance between user-friendliness and flexibility. Taking the below computational goal for example, it is hard to implement with the traditional OLAP tools.

How to find the workshop whose defective rate continuous to drop for 3 months in a row?
How to make statistics on students whose score on each course is above B?

In the 1st month since the new product enters the market, how many days will it takes to reach the 1/3 of the total sales in that month?

Lacking the computational capabilities impedes the flexibility of OLAP tool greatly. Analyzers are confined to a narrow and small area, incapable to analyze freely, and even have to resort to the 3rd party to perform this kind of computation. In the similar business computations, OLAP is often abandoned in an awkward situation.

4. Short of Interactive analysis ability
Data analysis is the most important feature of OLAP. Not like the data computing, data analysis is an interactive procedure requiring the perfect step-by-step computational mechanism. Toward the obscure goal, users need to observe the current data and make the reasonable assumption, and then verify/falsify the assumption to ultimately achieve the rather complicated business analysis goal. Unfortunately, the model of traditional OLAP tools is too old to provide so free a style of interactive analysis.

For example, why the sales improved greatly?
The possible assumptions include: orders flood in, sales forces beefed up in many respects, and any large orders are placed. Of which, the procedure to check the large orders is as given below:

1. List the order data, and then filter the data of recent 6 months.
2. Compute the average sales of each order.
3. Multiply the average sales by 300%, as the criterion of”large order” standard.
4. Group the data from step 2 by month, and filter the result from step 3 to get the large order.
5. View the result and lower the criterion for comparison if few orders are up to the criterion, and raise the criterion for comparison if there are too great orders after filtering.
6. Count the large orders of each month.
7. Compute the increment of large orders in each month.

Considering the above result, users may still need to keep investigating the cause of emerging large orders through computation until the clear and reliable basis for decision-making is found. The cause may be the vocational training for sales persons starts to bear fruit.

Traditional OLAP tools suffer from the limited interactive analysis abilities. They are unable to provide the above-mentioned flexible step-by-step computational capabilities, unable to solve the complex business computing, or provide the true basis for decision-making. So, quite a few clients just take OLAP tools as an expensive and apparently single-purpose reporting tool for presentation. They are trying to get more leverage, but only find the limited uses of OLAP for the time being.

5. Slow in reacting
Traditional OLAP tools require pre-molding and cooperation between people of various departments. Therefore, it is usually slow in reacting to the business analysis demands.
Assume that a toy manufacturer needs the below information before Christmas: Of the top 100 cities with the largest population, which cities require the sales effort being strengthened immediately. Then, you will find that no population data of the city in the existing model. The usage of traditional OLAP tools is as given below:

Business personnel download population data from census bureau or Wikipedia, and then use Excel and other similar tools to list the top 100 cities by population. The above step will not take more than 1 hour. But in the step followed, they will have to ask IT pros for help, because these data cannot be imported to OLAP for use directly.

Firstly, to coordinate: the business personnel raise a request to his/her superior. The superior will request the support from R&D department, and assign a project leader who will investigate the request initially, and build the project schedule and budget. The R&D department will approve and set up a development team. If cost is not a priority, then the development team can be standby all time for the business personnel.

Then, to implement: Perform the demand analysis in details and confirm it is to modify the model, schedule the task and export from Excel or database for other models to use. In addition, there are also steps of design, implementation, deployment, and verification as well as communication with database administers Web application administer, and programmers. When it comes to the final acceptance, further improvement is still required because IT pros may not be in the picture and are on the different page with business personnel. The improvement would be unavoidable unless unfortunately the Christmas was gone and they missed it.

The traditional OLAP is slow in reacting, requires great workload, and takes a bit too long time to implement the goal. Facing the fast-changing challenges, the enterprises will miss commercial opportunities, and find themselves in a disadvantage position in the intense competition.

6. Abstract model
The traditional OLAP tools convert the data of 2 dimensional from database and Excel to the multi-dimensional. To use the OLAP tools freely, business personnel must correctly understand the concepts of slicing, rotating, drilling, and other concepts as prerequisites. The abstraction of model hinders the business personnel from analyzing freely.

For example, to business personnel, the employee data, regional data, and product data are simply 3 lists or 3 sheets in Excel. Even the most ordinary business personnel know how to group, sort, and filter these data. However, things changes once these data is converted to the multi-dimensional data, business personnel have to say good bye to the data organized 2-dimensionally as flat as computer screen, and image these data as a cube. Never having the business personnel seen such data in their work or life before, they feel a bit tough to operate these abstract data.

The abstract model requires analyst to think 3-dimentionally. This gives rise to the difficulty to understand. Users are hard to convert the business language to the abstract multi-dimensional operations. Therefore, it is hard to truly implement the goal of on-line-analysis.

7. Great potential risk
Traditional OLAP tools have a huge potential risk due to the lacking of the computation and low interactive analysis ability, and the implementation relies on the cooperation with IT pros. The procedure and the cycle are a bit long.

The analyst are often forced to abandon the OLAP due to its poor computation ability that results in the failures to submit data of huge amount, and great difficulty to provide valuable references for the decision-maker. Lacking the interactive analysis ability, unable to solve the complex business analysis problem or find the true cause and solution, these drawbacks give rise to the faulty conclusion of analysis, and bring about great direct loss to the enterprise. In the analysis aimed to the business, too much work is relied on the IT pros. No wonder the procedure and cycle is lengthy, huge manpower and physical resources are consumed, while users are still unable to react effectively and efficiently to the constant changing business environment. Quite often, the ”stars” business is exceeded and ”cash cow” business is captured by others too. The analysis conclusion often deviates from the original goal of analysis, and the wrong decision may be easily made.

These potential risks can easily incur the failure of OLAP project, and bring about unrecoverable loss to the enterprise.

All in all, the traditional OLAP tools do not implement On Line Analytical Processing according to its true essence. They are just the “OLAP in its narrowest senses or the subset of OLAP”. It is neither intuitive nor fast or flexible.

3 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Do you think of any alternatives to traditional OLAP?

    ReplyDelete
    Replies
    1. Hi, amr,

      I believe many vendors are making efforts to remedy the drawbacks of traditional OLAP. So do we. If you have used BI tools, then you must understand the disadvantages of traditional OLAP clearly.

      Actually, we have gained our fruits now, the raqsoft es family should be modern OLAP tools. esProc and esCalc perfectly overcomes all the drawbacks mentioned above.

      To know a detail for a traditional OLAP alternative, visit: www.raqsoft.com

      Delete