OLAP Workshop Part 2 : Understanding OLAP Technology

OLAP Workshop Part 2 : Understanding OLAP Technology


In the last posting I hopefully explained some of the basic concepts behind OLAP. In this posting I want to explore how those basic concepts are exposed by the various OLAP aware ETL and reporting tools provided by Oracle and other BI vendors.

Architecture of Oracle OLAP




For a long time now Oracle has been unique in the marketplace. With Oracle Database 9i, 10g and 11g, all data (relational and multidimensional) is stored in one Oracle database. Only Oracle OLAP provides native multidimensional data types within the database.

A high-level architectural view of the Oracle OLAP option contains three parts:

Oracle Database 10g OLAP option, which comprises:

• Multidimensional data types
• OLAP calculation engine
• Open-access interfaces

End-user tools, which provide access to OLAP data for a wide spectrum of analytic needs
Administrative tools used to create and manage multidimensional data types. Oracle provides two administrative tools that can be used to create multidimensional data types in Oracle Database 10g:

• Oracle Warehouse Builder
• Analytic Workspace Manager

The following sections examine these three layers in more detail.

Components of Oracle OLAP
With the Oracle OLAP option, you get two powerful arenas of functionality:

• OLAP API and the analytic workspace (AW).
• OLAP API Functionality

Analytic Workspace The Analytic Workspace is the container for the multidimensional data types and leverages the multidimensional calculation engine of the OLAP Option. The AW also provides a standard SQL interface to the multi-dimensional model. This provides an industry standard access layer that can be used by any BI reporting tool that generates SQL commands, from SQL Developer, to Application Express on to more sophisticated tools such as BI EE.

The AW also povides an XML API for administration, and a programming language (OLAP DML).

Multidimensional Data Store The OLAP option provides true array-based multidimensional data types within the Oracle database. These multidimensional data types are contained in special tables in Oracle called analytic workspaces. Some data types are used to store data, whereas others are calculated instantaneously using the multidimensional engine.

Multidimensional Calculation Engine The OLAP Option’s multidimensional engine includes an impressive library of multidimensional-aware calculation functions and support for planning functionalities such as statistical forecasts, models, allocations, projections, and “what-if”scenarios, in the context of Analytic Workspaces. The multidimensional engine interacts with the multidimensional data types in the analytic workspace in the Oracle database.

The Oracle OLAP option provides a specialized Java API that developers can use to exploit the full power of the Oracle OLAP option by using advanced dimensionally-aware tools and applications. This API is used by Oracle Business Intelligence tools such as OracleBI Beans, OracleBI Discoverer, OracleBI Spreadsheet Add-In, and Oracle Reports OLAP Plug-in to provide a true multi-dimensional query and calculation environment.

OLAP DML The OLAP DML is an extremely powerful and analytically rich feature of the AW. It is a dimensionally-aware, high-level procedural language that runs in the database and exploits the multidimensional engine and multidimensional data types.

With the Oracle OLAP option developers can exploit the OLAP DML to add more sophisticated calculations and analysis to AWs and to extend the functionality of applications that access them. The OLAP DML is briefly introduced in the lesson titled “Previewing Advanced Oracle OLAP Features.”

AW API The AW API is a Java API that is used to define and physically build multidimensional analytic workspaces inside Oracle Database 10g. The AW API is used by administrative tools such as Analytic Workspace Manager and may be used by developers, if required, to enhance and extend analytic workspaces as necessary for a specific application.

Query Access to Oracle OLAP
Different users with different end-user tool requirements can all access the same data, taking advantage of the same calculations, and benefit from the same security, scalability, performance, and availability of the Oracle database.




OLAP API The OLAP API is designed to work with both multidimensional data types and relational data types in the Oracle database.

• The API enables you to directly access multidimensional data types in the AW.
• To access relational data types, you can register a relational physical dimensional model (such as a star or snowflake schema) to the Oracle Database OLAP Catalog.

Therefore, tools that use the OLAP API can be deployed against multidimensional analytic workspaces or suitable relational schemas that are registered to the Oracle OLAP option’s metadata layer.


Many Oracle source business intelligence tools take full advantage of the multidimensional query data model provided by the Oracle OLAP option through the OLAP API. These include OracleBI Spreadsheet Add-In, OracleBI Discoverer, OracleBI Beans, and OracleBI Reports.

SQL Interface
Unlike other multidimensional OLAP server products, Oracle OLAP provides not only a specialized API but also industry-standard SQL to access multidimensional data types.

You can use a simple SQL query with relationally oriented tools and applications to gain access to the multidimensional data types in the Oracle database. As a result, your SQL-based applications (such as report generators and ad hoc query tools) can access multidimensional data and calculations managed by the Oracle OLAP option.
SQL and PL/SQL are also used to manage and maintain multidimensional analytic workspaces and to move data between relational and multidimensional data types within the Oracle database.

For example, Oracle Application Express is an easy-to-use tool that is supplied with Oracle Database 10g for Web access to the Oracle database. Oracle Application Express is an example of a SQL-based application with no built-in OLAP knowledge that can nevertheless leverage the power of Oracle OLAP.

Many third-party tools from independent software vendors and Oracle partners, such as Arcplan, Business Objects, Cognos, and a large and growing number of business intelligence vendors throughout the world, access OLAP data through the Oracle OLAP option. Some of these vendors’ tools leverage the OLAP API, whereas others exploit the SQL query interface.

Dimensionally Aware Products
Oracle BI Spreadsheet Addin OracleBI Spreadsheet Add-In makes it easy to access OLAP data through the familiar spreadsheet environment of Microsoft Excel. After installation of OracleBI Spreadsheet Add-In, “OracleBI” appears as a new menu item in Excel. By using OracleBI Spreadsheet Add-In, you can establish a secure connection to the OLAP data source and use Excel as the front-end access tool to the data in the database.

Here are some of the features of OracleBI Spreadsheet Add-In:

• It combines the flexibility and familiarity of Excel and the power, scalability, and security of the Oracle OLAP option.
• OracleBI Query and Calculation Builders: After the connection is established, you can use the wizard-driven interface to drill, pivot, page through large cubes, and create reports.
• Access to native Excel features
• Powerful data-formatting features of Excel,
• Combine Oracle OLAP data with other Excel data
• Write Excel macros that leverage all your data.
• Create formulas and graphs in Excel

Excel users can quickly and easily combine the powerful analytic capabilities of Oracle OLAP with standard Excel functions that you know and use each day.



When using Excel OLAP calculations are performed directly in the database: The benefit of using OracleBI Spreadsheet Add-In is that you no longer need to download massive amounts of data to your spreadsheet. Oracle Database 10g OLAP performs all the OLAP calculations quickly and efficiently in the database. The calculations and business logic are defined only once in the database and then shared across the user community.

For more information goto the Spreadsheet Addin home page on OTN:

Spreadsheet Addin OTN Home Page
http://www.oracle.com/technology/products/bi/spreadsheet_addin/index.html

OracleBI Spreadsheet Add-In Feature Overview
http://www.oracle.com/technology/products/bi/spreadsheet_addin/htdocs/feature_overview/oraclebi_spreadsheet_addin_fov.htm

Introduction to OracleBI Spreadsheet Add-In
http://www.oracle.com/technology/products/bi/spreadsheet_addin/viewlets/olapsa_welcome_viewlet_swf.html



OracleBI Discoverer
This is another Oracle Business Intelligence tool that can directly access Oracle OLAP data. Discoverer Plus OLAP is an ad hoc query, reporting, analysis, and Web-publishing tool. It enables you to:

• Perform OLAP query, reporting, and analysis on both multidimensional data models (analytic workspaces) and relational OLAP data models (star or snowflake schemas).
• Access and analyze multidimensional data from your company’s database without having to understand complex database concepts. The wizards and menus of Discoverer Plus OLAP guide you through the steps to retrieve and analyze multidimensional data.

Because Discoverer Plus OLAP understands the dimensional data model, you formulate your queries in the language of business — you use real dimensions, hierarchies, levels, and measures through a simple interface. You can also exploit the rich features of OLAP through dimensionally-aware query and calculation builders, thereby simplifying the tasks of defining queries and calculations. Worksheets that are authored in Discoverer Plus OLAP are published to the Web, where Discoverer Viewer and Oracle Portal users can access them.




For more information goto the Discoverer home page on OTN:

Discoverer OTN Home Page
http://www.oracle.com/technology/products/discoverer/index.html

Creating OLAP worksheets
http://www.oracle.com/technology/products/discoverer/files/viewlets/1012_plus_olap_creating.html

Modifying OLAP worksheet properties
http://www.oracle.com/technology/products/discoverer/files/viewlets/1012_Plus_OLAP_Modifying.html


OracleBI Beans
OracleBI Beans is used by business intelligence and OLAP developers. OracleBI Beans is used for developing applications such as Oracle Enterprise Planning and Budgeting and tools such as OracleBI Discoverer and OracleBI Spreadsheet Add-In. BI Beans is also available to third-party software developers to accelerate development of custom OLAP applications.




BI Beans is a set of standards-based Java beans that is integrated into Oracle JDeveloper. It provides analysis-aware application building blocks designed for the Oracle OLAP option of the Oracle database. Using BI Beans, you can create customized business intelligence applications that take advantage of the robust analytic capabilities of Oracle OLAP.

Applications can include advanced features such as interactive user interfaces, drill-to-detail reports, forecasting, and what-if analysis. BI Beans includes Java beans for acquiring data from the Oracle database, presenting data in a variety of crosstab and graph formats, and saving report definitions, custom measures, and data selections.
Using BI Beans, you can develop business intelligence applications from Oracle JDeveloper, or any Java application development environment, and deploy them through any application server as a thin or thick client.

For more information goto the BI Beans home page on OTN:

BI Beans OTN Home Page
http://www.oracle.com/technology/products/bib/index.html

Oracle BI Beans Feature Overview
http://www.oracle.com/technology/products/bib/1012/htdocs/feature_overview/BI_Beans_Feat_Oview.htm

Developing a Dashboard Application with Oracle BI Beans
http://www.oracle.com/technology/products/bib/1012/viewlets/MS Developing Executive Insight.html


SQL Aware Products
Oracle Business Intelligence EE
Oracle Business Intelligence Suite Enterprise Edition 10g, Release 3 (BI EE 10g ) delivers significant new product enhancements to further enable enterprise-wide BI, including integration with Oracle OLAP. In this release, Oracle's native multidimensional data model -- the analytic workspace (AW) -- is made accessible to BI EE 10g by creating the required metadata in Oracle BI Administration Tool. The AW data is exposed to the BI EE 10g product stack, and the OLAP engine is leveraged for analysis of that data.

Creating access to Oracle OLAP data is a simple 3-step process. Each these steps is covered in detail as part of an training document provided as an Oracle by Example. This explains how to: prepare an AW for access by BI EE 10g; create the required metadata using Oracle BI Administration Tool; and create analytic reports of AW data using Oracle BI Answers




For more information goto the BI EE home page on OTN:

BI EE OTN Home Page
http://www.oracle.com/technology/products/bi/enterprise-edition.html

Ad-hoc query and reporting
http://www.oracle.com/technology/products/bi/enterprise-edition-platform-components.html

Oracle By Example:
These lessons are designed for completion in the order that is shown below. Each subsequent lesson depends on the completion of the previous lesson. Click on any of the links below to begin.

Lesson 1: Preparing an Analytic Workspace for Access by Oracle BI EE 10g
http://www.oracle.com/technology/obe/obe_bi/bi_ee_1013/olap/PrepareAW.htm

Lesson 2: Creating BI EE 10g Metadata for the Analytic Workspace
http://www.oracle.com/technology/obe/obe_bi/bi_ee_1013/olap/CreateMetadata.htm

Lesson 3: Querying OLAP Data Using Oracle BI Answers
http://www.oracle.com/technology/obe/obe_bi/bi_ee_1013/olap/QueryData.htm



Oracle Application Express
The Oracle OLAP option provides a SQL interface to access multidimensional data types, thus enabling any SQL-aware tool to access data in the analytic workspace.
Oracle Application Express is an easy-to-use report builder that is provided with Oracle Database 10g to simplify the creation of database-centric interactive Web pages.

Thus, with the Oracle OLAP option, you can use Oracle Application Express to provide Web-based access to key performance indicators such as profitability, sales, units shipped, trends, and period-to-period comparisons and forecasts.
It provides support for some interactive reporting (for example, you can use hypertext links to call more SQL queries), but it is not a fully interactive analysis system like OracleBI Discoverer or like other specialized business intelligence tools.




The Web report in the slide is generated with the following SQL code:

SELECT region_desc, sales, units, cost, profit,
ROUND(pct_margin ,3), fcast_sales
FROM mysalesaw_view
WHERE time_desc = TO_CHAR(ADD_MONTHS(SYSDATE,-3), 'Mon-YY')
AND product_level = ‘ALL'
AND channel_level = ‘ALL'
AND customer_level= 'REGION'
ORDER BY sales DESC;

Because the multidimensional data model presents data to the query layer as if it were precalculated, prejoined, and preaggregated, your query does not need to perform any calculations, joins, or aggregations. SQL code is thus very simple to write and fully leverages the power of the AW. The multidimensional engine returns the requested data from the AW extremely efficiently and quickly, even if the AW is calculating much of the data instantaneously.

For more information goto the Application Express home page on OTN:

Application Express OTN Home Page
http://www.oracle.com/technology/products/database/application_express/index.html

What is Oracle APEX?
http://www.oracle.com/technology/products/database/application_express/html/what_is_apex.html

3.0 New Features
http://www.oracle.com/technology/products/database/application_express/html/3.0_new_features.html



Tools to Build an Analytic Workspace

Two tools are available for IT and power users to easily build analytic workspaces (AWs) and load them with data (for analysis with tools such as OracleBI Beans, OracleBI Discoverer, and OracleBI Spreadsheet Add-In):



Shared API for AW Creation Both OWB and AWM use the AW XML API to build and maintain AWs. Therefore, an AW model that is created by AWM can be incorporated back into the OWB repository to provide version control, lineage, and impact analysis if changes to the original source systems are required.

Note: Both OWB and AWM generate scripts that may be called and scheduled from PL/SQL scripts or other scheduling processing tools, if required.

Analytic Workspace Manager (AWM) AWM is a tool that makes it easy to build and maintain AWs from a suitable (clean) data source. AWM is used on source data that has been cleaned by the ETL process.

The clean data may have been created by Oracle Warehouse Builder or by another ETL process; your IT department would have a preferred method of preparing data.
AWM is focused on the simple task of building AWs. It has an intuitive wizard-based user interface and is therefore suitable for both IT and power users.

AWM supports the complete process of creating an AW from beginning to end in a single, dimensionally-aware design environment.

The creation process includes three easy steps:

• Design the dimensional model.
• Map the dimensional object to the source data.
• Load the data into the AW.

After you have performed these steps, the AW is immediately available for your tools to query.

For more information goto the OLAP home page on OTN:

OLAP OTN Home Page
http://www.oracle.com/technology/products/bi/olap/olap.html

Analytic Workspace Manager 10.2.0.3.0A
http://www.oracle.com/technology/software/htdocs/devlic.html?url=http://download.oracle.com/otn/java/olap/AWM_102030A_Win32.zip

Analytic Workspace Manager 10g
http://www.oracle.com/technology/products/bi/olap/1451_AWM10g.pdf

Improve SQL Based Business Intelligence Tools with Oracle OLAP 11g
http://www.oracle.com/technology/products/bi/olap/Oracle_OLAP_11g_TWP.pdf

Leveraging Business Intelligence Tools
http://www.oracle.com/technology/products/bi/olap/40261_leveragingtools.pdf

Analytic Workspace Manager 10.2.0.3.0
http://www.oracle.com/technology/products/bi/olap/viewlet/AWM102_viewlet_swf.html


OracleBI Warehouse Builder 10g R2 (OWB) OWB is an advanced management and ETL tool, used by developers and database administrators to build and manage data warehouses in the Oracle database. OWB manages the entire process of collecting and cleaning data.

• Collecting data: OWB collects data from the various operational systems that feed the data warehouse.
• Cleaning data: OWB performs various required transformations and data-cleansing activities (for example, dealing with inconsistencies between different source systems, matching and merging data from them, and processing missing or erroneous data).

The result is a set of clean tables in the Oracle database.

• OWB includes advanced data-profiling features.
• OWB enables data warehouse developers to optionally populate relational star or snowflake schemas or multidimensional AWs for access by the Oracle OLAP option.
• OWB is, therefore, a professional IT tool.

Warehouse Builder is free to database customers and this free functionality includes deploying OLAP schemas. Some Warehouse Builder features are costed options and these include:

• Enterprise ETL
• Data Quality
• CRM/ERP Connectors

For more information goto the Warehouse Builder home page on OTN:

OTN Home Page
http://www.oracle.com/technology/products/warehouse/index.html

Oracle Warehouse Builder 10gR2 and Oracle OLAP
http://www.oracle.com/technology/products/warehouse/pdf/OWB10gR2 and Oracle OLAP.pdf

Benefits of a Multi-dimensional Model
http://www.oracle.com/technology/products/warehouse/pdf/Benefits of a multi-dimensional model.pdf


Summary
In this latest post you should have learned how to describe:

• Oracle OLAP and its components
• The flexibility of (and options for) accessing Oracle OLAP data
• Oracle business intelligence tools and applications that access the analytic workspace in the Oracle database
• Oracle administrative tools that enable building and maintenance of analytic workspaces

OLAP Workshop : Basic Overview of OLAP Concepts

I have recently been working with two of our key OLAP experts (Stuart Bunby and Kevin Lancaster) helping deliver various OLAP workshops. Based on the questions and discussions I had with many people during this years OpenWorld I think it might be useful to share some of information we put together for those workshops via this blog. This series of postings (not sure how many it will be at this stage) are designed to take you from the OLAP basics right through to more advanced features such as creating your own calculations using the huge library of OLAP analytical functions and on to more DBA type topics such as monitoring cube data load and aggregation processes. I am hoping I can keep the flow of topics in the correct order....

Let’s start with the most obvious question “What Is OLAP”?

Online analytical processing (OLAP) is in my opinion a highly abused term that has lost much of its original meaning. In its original guise, dating from the early 1990’s, the term was used to describe a class of computer systems that were designed and optimized for analysis. This is still true of Oracle OLAP but not for many of the other proprietary solutions currently available in the marketplace.

From my perspective OLAP is about working with data in business terms without having to understand the underlying storage mechanism and having the ability to intelligently and transparently support the many different types of business rules that always exist within an organisation. For example, a very simple and obvious example is Stock. Most query tools do not understand how to correctly analyse stock over time, it is left to the user to select the correct aggregation method.

By using this term, it is possible to differentiate the more analytical requirements of the business analyst and senior management community from the requirements of the more general basic requirements that are easily and quickly answered by most directly query a transaction processing (OLTP) system. OLAP has now evolved into a more generic environment that is centred around use of the term “business intelligence”. Here the emphasis is more on “online” or active access as well as being far more “analytical” in terms of the reports that are generated.

What do these terms, online and analytical mean?

Online: Although most OLAP tools and applications enable development of reports that can be saved and printed when not connected to live data, OLAP emphasizes live access to data rather than static reporting. Analytic queries are submitted against the database in real time, and the results are returned in real time.

Analytical processing: This is the key concept with OLAP. End users can:

• Easily navigate multidimensional data to perform unpredictable ad hoc queries and to display the results in a variety of interesting layouts
• Transparently manage business rules across dimensions and cubes
• Drill through levels of detail to uncover significant aspects of data
• Rapidly and efficiently obtain the results of sophisticated data calculation and selection across multiple dimensions of data

A standard transactional report or query might ask, “When did order 84305 ship?” This query reflects the basic mechanics of doing business. It involves simple data selection and little or no calculation processing. It can be answered directly from the transactional system, probably without impacting other operations. Every organisation needs this basic level of information.

In contrast, OLAP systems are typically deployed to extend and enhance an organization’s ability to answer a much broader range of business questions about the data they are collecting in their transactional systems:

• How do sales for our top 10 most profitable products across Europe for this quarter compare with sales a year ago?
• What are the differences in the product-sales mix between the regions, relative to the global sales mix?
• What are our forecast units, unit price per service, unit cost per product, sales, cost trends, and profit for the next 12 months?
• In what ways does the mix vary by salesperson, and what is the relative performance of our salespeople?
• What are the products making up 40% of our profit for each region over time?

These questions are more analytical and complex, and the answer to one question often leads immediately to another question as the user follows a train of thought in researching a business problem or opportunity.

OLAP is designed to make it easy for end users to ask these types of analytical questions without requiring:

• Assistance from the IT department
• Programming skills
• Technical knowledge about the organization of the database

The results of queries also need to be rapid so that the analyst’s train of thought is not interrupted and the value of the analysis is not diminished.






A typical multidimensional business query, would be something like the following:

For each region of the world, what was the percentage change in revenue for our top 20% products, over a rolling three-month period this year compared to the same period last year?
This simple business question describes both the data that the user wants to examine and the structural form of that data. Business users typically want to answer questions that include terms such as what, where, who, and when. For example, you find the following essential questions embedded in the sample question:

• What products are selling best? (“…top 20%…”)
• Where are they selling? (“…each region of the world…”)
• When have they performed the best? (“…percentage change in revenue…”)

If you examine the query in detail it appears to be translate into a very complex query. When I have discussed this type of query during presentations at conferences and with customers you can always spot the DBAs because they immediately try to translate this question into a SQL statement. Of course it is possible to create a SQL query to answer this question. But lets start by breaking this query down and examining it in more detail:

• There are two calculations (percentage change in revenue and rolling three-month total).
• There is a ranking element (the top 20% of products)
• There are aggregations (region level of the geography dimension)
• There are multidimensional selections (specific products, specific time periods, and specific regions)
• The result of the query is a multidimensional view of the data (perhaps as a tabular display on the screen, perhaps as a graph, or both)

Now I think many people would agree this does in fact look like a complex query. To try and frame this within a traditional query and reporting tool would require considerable skill. And don’t forget this is the starting point for the analysis not the final result. The data returned by this query will drive other even more interesting and complex queries.

But the complexity of this query is a technical issue rather than a business issue.
Therefore, a key goal of OLAP technology is to make it very easy for end users to ask such questions about their data without placing a burden on the IT department.


How Does OLAP Make This Easy?

Business users think dimensionally. By design, OLAP technology stores, processes, and presents data in a dimensional way. The data model of OLAP systems reflects the users’ picture of their business data, making it easy to formulate queries in business terms.

OLAP systems are optimised for fast retrieval of data for dimensional analysis.
We will now examine the multidimensional logical model, which serves as the basis for OLAP systems.

The key Objects within the OLAP Model Most OLAP data models are built around two key concepts: measures and dimensions.

Measures
Measures represent factual data; they are sometimes called “facts.” Typical examples of measures are sales, cost, profit, and margin. Measures are organized by one or more dimensions. Many people visualize measures as being a simple cube type shape, in which the edges of the shape are the dimensions and the contents of the shape are the measure values. The image below shows a generic simple three dimensional measure.




Of course measures are not restricted to just three-dimensional definitions. A measure can have as many or as few dimensions as required to accurately manage the data associated with the measure. In additional Oracle’s OLAP option allows you to design and manage multiple cubes each with different dimensionality. In addition Oracle OLAP supports a number of different data types for measure such as: numeric (Oracle SQL data type), decimal, integer, text, date and boolean.

Measures can be divided into two categories:

• Stored Measures
• Calculated (or derived) Measures

Stored measures are loaded, aggregated and stored directly within the database. Alternatively, they can be derived from the results of calculations that are stored. For example a forecast could be derived from another stored measure such as revenue and the results of the forecast calculation stored in the database.



Calculated measures are measures whose values are calculated dynamically at query time. Only the calculation rule(s) is stored in the database. Common calculations include measures such as ratios, differences, moving totals, and averages. Calculations do not require disk storage space, and they do not extend the processing time required for data maintenance.

Note: Oracle OLAP has a library of several hundred multidimensional calculation functions that can be used in calculated measures. It is even possible for expert users of Oracle OLAP to define their own functions to perform virtually any calculation.




From a business user perspective both measures appear and are used in exactly the same way and have equally as fast query access. All measures are equivalent as far as the end-user interface is concerned. However, it can be useful to at least provide them with the access to the definition of a calculated measure via a tooltip for example, just to avoid confusion.

Below is an example of a typical report contained both stored and calculated measures. The two calculated columns are “Profit” and “Margin”. Because both types of measures are treated the same, business users can use them in queries, conditions, to drive colour coding, exception reporting etc.





So what is the difference between a cube and a measure? If you have used Analytic Workspace Manager you will be familiar with the concept of a cube. This is a high level container, which is invisible to business users querying the OLAP data, for grouping together measures that share the same dimensionality. Cubes do make your life much easier in terms of being able to manage a whole group of measures collectively, store them all to an XML template, load data into a group of measures via single reference and so on.


Dimensions
Dimensions identify and categorize the data within your measures by forming the edges of the measures. Examples of dimensions include product, geography, time, and distribution channel.

Dimensions have three key components:
· Hierarchies
· Levels
· Attributes




With the Oracle OLAP data model, dimensions are stored once and are used repeatedly. This allows dimensions, and their members, to be shared across measures. While dimensions form the edge of a measure, the members point to individual cells inside the multi-dimensional measure, as can be seen above.

In the example below, there is just one Time dimension even though it appears three times. The three measures in the picture have different shapes, or dimensionality. Sales and Units are both dimensioned by the Customer, Product, and Time. Price is only dimensioned by Product and Time; it does not use the Customer dimension because the price does not vary by customer.





Hierarchies Dimension hierarchies are optional but are common in OLAP systems. A hierarchy is a logical structure that groups like members of a dimension together for the purpose of analysis. For example:

• A Time dimension might have a hierarchy that describes how months are grouped together to represent a quarter and how quarters are grouped together to represent a full year.
• An Organization dimension might have a hierarchy that makes it easy for you to identify the direct reports of a specific manager.

Each dimension can have multiple hierarchies if required. For example, the time dimension can have a hierarchy that represents the Julian calendar and another hierarchy that represents a fiscal calendar.




A dimension’s structure is organized hierarchically based on parent-child relationships. These relationships enable:

• Navigation between levels: Hierarchies on dimensions enable drilling down to lower levels or navigating (rolling up) to higher levels. Drilling down on the Time dimension member “2005” will likely navigate you to the quarters Q1 2005 through Q4 2005. In a calendar year hierarchy, drilling down on Q1 2005 would navigate you to the months January 05 through March 05. These kinds of relationships make it easy for users to navigate large volumes of multidimensional data.
• Aggregation from child values to parent values: The parent represents the aggregation of its children. Data values at lower levels aggregate into data values at higher levels. Dimensions are structured hierarchically so that data at different levels of aggregation can be manipulated together efficiently for analysis and display. You learn about the aggregation capabilities of Oracle OLAP in the lesson titled “Applying Advanced Dimensional Design and Cube Processing Techniques.”
• Allocation from parent values to child values: The reverse of aggregation is allocation and is heavily used by planning, budgeting, and similar applications. Here, the role of the hierarchy is to identify the children and descendants of particular dimension members for “top-down” allocation of budgets (among other uses).
• Grouping of members for calculations: Share and index calculations take advantage of hierarchical relationships (for example, the percentage of total profit contributed by each product, or the percentage share of product revenue for a certain category, or costs as a percentage of the geographical region for a retail location).

In this example, you can do the following in the Product hierarchy:

• Navigate up through each level in the hierarchy from the lowest level to the highest level
• Navigate down the hierarchy from the highest level to the lowest level
• Aggregate data from the lowest level (individual products) up through the hierarchy to the highest level (total product)

Levels Each level represents a position in the hierarchy. The level above the base level contains aggregate values for the levels below it. The members at different levels have a one-to-many parent-child relationship. A hierarchy typically contains several levels, and a single level can be included in more than one hierarchy.

If data for the Sales measure is stored at the Product level, then the higher levels of the product dimension enable the sales data to be aggregated correctly into Subcategory, Category, and All Products levels.

If there are multiple hierarchies built over a dimension, it may be that a level would appear in more than one hierarchy or may exist in only one hierarchy.



Types of Hiearchies Within a multi-dimensional model there are two basic types of hierarchies:

• Level Based
• Value Based
  

Most of the hierarchies are level based, including the Product dimension hierarchy shown in the previous slide and the Time dimension hierarchy shown in this slide. In the time hierarchy example, there are Day, Month, Quarter, and Year levels in the hierarchy.

Sales forces also generally have a level-based structure, as in the following example:
Representative > Area > Region > Country > Continent > World

Other dimensions may have hierarchies that are not strictly level based. For example,
there is clearly a hierarchy in an organization chart, but all the direct reports of the President may not be at the same level. In the example, the two VPs (vice presidents) and the President’s Admin (administrative assistant) are all direct reports of the President but are not at the same level. The VPs are not at the Admin level, and the Admin is not at the VP level.





Attributes
Attributes provide descriptive information about the dimension members and are also useful when you are selecting dimension members for analysis:

• Select the products whose color (attribute) is “Blue.”
• Select the customers who have two children.
• Select the promotions that are of type “Multipack.”
• Select all time periods whose description contains “January.”

Most types of attributes are entirely optional. Oracle OLAP permits a large number of attributes to be created if required. Some attributes are valid for all the members of the dimension, regardless of level. For example, all products at all levels have a description. Others attributes are valid for certain levels or certain hierarchies only. For example, only individual product items have a color.




In the above picture certain products are displayed together with their parent levels in the hierarchy (subcategories and categories) on the Product dimension. In addition, two sample attributes (Color and Product Manager) are displayed for these products.
Other examples of typical attributes might include:

For Product dimensions: Color, Flavor, Pack Size, Brand Manager Name, and so on
For Customer dimensions: Gender, Marital Status, Date of Birth, and other types of demographic information

Note: Sometimes, attributes may also be modeled as levels in an alternate hierarchy. Consider the above example: If the business requirement was that measures should be aggregated by Product Manager (total for Bruce, John, Karl, Mary, and so on.) or by Color (total for Blue, Green, Red, White, Yellow, and so on) and by the ability to drill down, aggregate, allocate, or calculate data based on these values, then many designers would consider creating additional hierarchies on the Product dimension for this purpose. However, many attributes have little business use as aggregates and are used simply in filtering.

What's coming next?
The next posting will provide an overview of the various products that can be used to build, maintain and query Oracle OLAP cubes.

11g Available for Windows

The Windows version of 11g is now available for download. If you visit the main database download page you can agree to the terms and conditions and download the software:

http://www.oracle.com/technology/software/products/database/index.html?rssid=rss_otn_soft


For OLAP, Data Mining and Warehouse Builder you will need to download the database, client and Examples zip files. Look for the See All link on that page:

Microsoft Windows (1.7 GB) | See All (Including Client, Examples, Gateways, and Clusterware)

http://www.oracle.com/technology/software/products/database/oracle11g/111060_win32soft.html


All the documentation to support 11g is also available via OTN:
http://www.oracle.com/technology/documentation/database11gR1.html