| Multidimensional OLAP tools are a major component of the BI decision-support tool suite. Terms such as OLAP , relational OLAP (ROLAP), multidimensional OLAP (MOLAP), decision support, multidimensional analysis, and executive information system (EIS) are all used to describe the explosive growth in the field of data access and data analysis tools. These terms are frequently associated with expectations of built-in functionality and ease of use. While much of the literature uses OLAP to represent all of these terms, each OLAP tool vendor seems to have its own definition of OLAP. Hence, OLAP tools support only the definitions of their vendors ”most of the time. A widely accepted definition of OLAP is the following: OLAP refers to online analytical processing technology that creates new business information through a robust set of business transformations and calculations executed upon existing data. Advantages of OLAP Tools Although BI decision-support applications use conventional reporting and querying tools as much as multidimensional OLAP tools, the majority of business people seem to favor OLAP tools because of their additional functionalities. There are two distinct advantages for business people who use OLAP tools. -
The focus in analytical processing is on the data, specifically the multidimensional aspects of the data that are supported by OLAP tools. Business objects are represented as dimensions (e.g., product, customer, department), which are naturally interrelated through functional subject areas (e.g., sales) and are often hierarchical (e.g., products roll up into product categories, departments roll up into divisions). -
Business analysts navigate through these dimensions by drilling down, rolling up, or drilling across. They can drill down to access the detailed level of data, and they can roll up to see the summarized data. They can roll up through the hierarchy levels of dimensions or to specific characteristics or data elements ( columns ) of the dimensions. They can also drill across dimensions to access the data of interrelated dimensions. In addition, powerful computational services provide functions such as ranks, averages, return on investment (ROI), and currency conversions. The OLAP category of software tools makes many business analysts self-sufficient by giving them easy and intuitive access to their data for analysis ”and getting developers out of the report-writing business. This is accomplished through the following tool characteristics: -
OLAP tools allow business analysts to combine their data in any order they desire , at any level of summarization, and over several time periods. Business analysts can design their queries by clicking on the dimensions and by selecting the desired data elements for the analysis they need to perform. -
Different OLAP tools support a variety of access and analysis needs. They provide multiple views for data access, from the senior executive's desire to browse through summarized data to the business analyst's need to perform complex detailed analysis. OLAP tools are a very important component of application development in a BI decision-support environment. While conventional query and reporting tools are used to describe what is in a database, multidimensional OLAP tools are used to answer why certain business events are true. For example, an OLAP tool could be used to prove or disprove a hypothesis that a business analyst has formulated about a correlation among certain data values. Let us assume that a business analyst makes the observation that customers with low income and high debt often default on their loans. The business analyst then concludes that this group of customers should be considered bad credit risks. In order to verify this conclusion, the business analyst prepares a query against the BI target databases, for example: "Compare write-off amounts for products by product type, by territory, by customer, by month, where the customer income level is below a certain amount to those where the customer income level is above a certain amount." This type of analysis query would run against a multidimensional database in which summarized data is stored as precalculated measures (facts), with one such measure being "write-off amount." OLAP Tool Features OLAP tools are popular not only because they make the business analysts more self-sufficient but also because the tools provide innovative ways to analyze data. -
Tools present a multidimensional view of the data, which is intuitive and familiar to the business people. For example, organizations always like to set their strategies for increasing revenue by: -
- Introducing new products -
- Exploring new markets -
- Increasing price These three characteristics are based on increases or decreases in revenue that can be tracked more easily through a multidimensional view of the sales data (by product, by region, by customer). -
Tools provide summarizations and aggregations of the data at every dimensional intersection. The terms summarization and aggregation are commonly used interchangeably. Even though both terms refer to "adding up" atomic data values and both are used in creating measures or facts, their precise definitions are not the same. -
- Summarization refers to totaling or summing one atomic data value (vertically) to produce a total or sum of that value, for example, adding up the annual salaries of all employees to produce the value Total Annual Salary Amount. (In popular multidimensional database design jargon, summarization is referred to as aggregation. ) -
- Aggregation refers to derived data, which is produced from gathering or adding multiple atomic values (horizontally) to create a new aggregated value, for example, adding the annual salary, the bonuses, and the dollar value of an employee's benefits package (health care plan, retirement plan) to produce the value Employee Compensation Plan Amount. -
Tools provide interactive querying and analysis capabilities of the data. Business analysts can perform "what if" analysis with the help of OLAP tools, for example, "What if we lowered the price of the product by $5? How much would our sales volume increase in the state of Alaska?" Business analysts like to run queries interactively and act upon the query results by changing the values of some variables and rerunning the query to produce a new result. -
Tools support business analysts in designing their own analysis queries, in creating their own custom members within dimensions, and in creating custom measures. -
- One of the main goals of a BI decision-support environment is to make the business analysts as self-sufficient as possible. This can be done with parameterized queries, where business analysts can change their assumptions (parameters) and rerun the same queries with new parameters. A prerequisite for effective use of parameterized queries is a well-documented query library. -
- OLAP tools can also give business analysts the ability to create custom members (also called aggregates ) within a dimension (e.g., Hot-Car, which would be defined as any red convertible ) that can SUM, AVG, MAX, and MIN a group of member values. -
- OLAP tools can also provide the ability to create custom measures or facts (e.g., Percent Female, which would be defined by a formula provided by a business analyst). These custom measures can then be picked as a new measure from a drop-down menu for a fact table. -
Tools support drill-down, roll-up, and drill-across features of multidimensional analysis. For example, a business analyst who wants to find a way to lower the cost of manufactured goods could drill down into the actual detailed costs of purchased raw materials. He or she could also summarize these costs by rolling up the raw materials into predefined categories. Then he or she could drill across to another table to include the production costs of the manufactured goods. -
Tools offer analytical modeling capabilities useful to business people. To expand on the previous example, lowering the cost of manufactured goods could also be accomplished by reducing the working capital so that the borrowing costs are lower. Analytical modeling techniques provided by the OLAP tools could be used to find the optimum amount of working capital. -
Tools support functional models for trend analysis and forecasting. OLAP trend analysis functionality could be used to analyze past data and make predictions about the future. -
Tools display data in charts and graphs that offer quick visual summaries. The saying "A picture is worth a thousand words" has never been so true as when analyzing vast amounts of data from BI target databases. Visually appealing, understandable, and useful charts and graphs are important components of every OLAP tool. There is a diversity of OLAP tools based on different architectures and features. It is important to know the access and analysis requirements of BI applications in order to make an informed decision about purchasing the right OLAP tools.  |
