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

Optimising Aggregations in OLAP

I have been investigating two very useful interesting, and possibly useful, procedures this week. They are part of the DBMS_AW package and allow you to tune your aggregation process. One of the many questions that get asked during OLAP projects is: which levels to precompute and which levels to compute at query time? Most people seem to think you must pre-compute all levels across all their dimensions. Well, the flip side of this approach is the data load and aggregations stage can take more time than you expected and possibly have available. So is it possible to balance the time taken for the data load/aggregation and pre-solving as much as possible to maintain query performance? As part of the DBMS_AW package there are two procedures that can help you optimise this aggregation process by identifying the most expensive members within a dimension. The two packages are:

• ADVISE_CUBE
• ADVISE_REL

Both allow you to define a percentage value to precompute as part of the aggregation step that forms the normal build process. In 11g this is all included directly within AWM, but for those customers using 10g here is a quick overview of how to use these procedures.


Advise_Cube
The ADVISE_CUBE procedure helps you determine how to preaggregate a standard form cube in an analytic workspace. The procedure takes two arguments:

• Aggmap_name:The name of an aggmap associated with the cube.
• Precompute_percentage: The percentage of the cube's data to preaggregate. The default is 20%.

Each RELATION statement in the aggmap must have a pre-compute clause containing a valueset. ADVISE_CUBE returns a list of dimension members in each valueset. If the valueset is not empty, ADVISE_CUBE deletes its contents before adding new values.

The aggmap must have a pre-compute clause in each of its RELATION statements. The precompute clause must consist of a valueset. Based on the precompute percentage that you specify, ADVISE_CUBE returns a set of dimension members in each valueset.

Now I tried this using the common schema and ran into a few problems in getting the correct results. For each measure within a cube there is a formula that refers to an AGGMAP object, as shown below where the aggmap is OBJ1123208571

DEFINE SALES_PRT_TOPFRML FORMULA DECIMAL
EQ aggregate(this_aw!SALES_PRT_TOPVAR using this_aw!OBJ1124208571)

The aggmap looks like this:

DEFINE OBJ1124208571 AGGMAP
AGGMAP
RELATION this_aw!TIME_PARENTREL(this_aw!TIME_AGGRHIER_VSET) PRECOMPUTE(this_aw!TIME_AGGRDIM_VSET) OPERATOR SUM args DIVIDEBYZERO YES DECIMALOVERFLOW YES NASKIP YES COUNT NO
RELATION this_aw!CHANNELS_PARENTREL(this_aw!CHANNELS_AGGRHIER_VSET) PRECOMPUTE(this_aw!CHANNELS_AGGRDIM_VSET) OPERATOR SUM args DIVIDEBYZERO YES DECIMALOVERFLOW YES NASKIP YES COUNT NO
RELATION this_aw!GEOGRAPHIES_PARENTREL(this_aw!GEOGRAPHIES_AGGRHIER_VSET) PRECOMPUTE(this_aw!GEOGRAPHIES_AGGRDIM_VSET) OPERATOR SUM args DIVIDEBYZERO YES DECIMALOVERFLOW YES NASKIP YES COUNT NO
RELATION this_aw!PRODUCTS_PARENTREL(this_aw!PRODUCTS_AGGRHIER_VSET) PRECOMPUTE(this_aw!PRODUCTS_AGGRDIM_VSET) OPERATOR SUM args DIVIDEBYZERO YES DECIMALOVERFLOW YES NASKIP YES COUNT NO
MODEL this_aw!SALES_OBJ1124208571_TIME_AWXMLMODEL PRECOMPUTE(NA)
MODEL this_aw!SALES_OBJ1124208571_CHANNELS_AWXMLMODEL PRECOMPUTE(NA)
MODEL this_aw!SALES_OBJ1124208571_GEOGRAPHIES_AWXMLMODEL PRECOMPUTE(NA)
MODEL this_aw!SALES_OBJ1124208571_PRODUCTS_AWXMLMODEL PRECOMPUTE(NA)
AGGINDEX NO
CACHE SESSION
END

In the documentation the example shows a much simpler AGGMAP as follows:

DEFINE UNITS_AGG AGGMAP
RELATION product_parentrel PRECOMPUTE (prodvals)
RELATION time_parentrel PRECOMPUTE (timevals)

Executing the ADVISE_CUBE procedure against the standard form AGGMAP does not generate an error but it does not generate a result either. The only way I could get this to work was to create another AGGMAP using a different set of valusets as follows:

DEFINE SALES_REVENUE_AGGMAP AGGMAP
AGGMAP
RELATION TIME_PARENTREL PRECOMPUTE (TIME_VALS)
RELATION CHANNELS_PARENTREL PRECOMPUTE (CHANNELS_VALS)
RELATION GEOGRAPHIES_PARENTREL PRECOMPUTE (GEOGRAPHIES_VALS)
RELATION PRODUCTS_PARENTREL PRECOMPUTE (PRODUCTS_VALS)
END

And then copy the results from the new valueset to the master valueset. This is the script I used to execute the procedure:

SQL>set serveroutput on
SQL>exec dbms_aw.execute('aw detach SH_AW')
SQL>exec dbms_aw.execute('aw attach SH_AW RW')
SQL>exec dbms_aw.advise_cube ('SALES_REVENUE_AGGMAP', 40)
SQL>exec dbms_aw.execute('status PRODUCTS_VALS')
SQL>exec dbms_aw.execute('status GEOGRAPHIES_VALS')
SQL>exec dbms_aw.execute('status CHANNELS_VALS')
SQL>exec dbms_aw.execute('status TIME_VALS')
SQL>exec dbms_aw.execute('limit PRODUCT_AGGRDIM_VSET to PRODUCTS_VALS')
SQL>exec dbms_aw.execute('limit TIME_AGGRDIM_VSET to TIME_VALS')
SQL>exec dbms_aw.execute('limit CHANNELS_AGGRDIM_VSET to CHANNELS_VALS')
SQL>exec dbms_aw.execute('limit GEOGRAPHIES_AGGRDIM_VSET to GEOGRAPHIES_VALS')
SQL>exec dbms_aw.execute('update;commit;aw detach SH_AW')

I am not sure how this works, in terms of computing a cost for a specific member(s) within a dimension. Things are a little cleared with the other package, Advise_Rel.

Advise_Rel
I had a lot more success with the ADIVISE_REL procedure. This works on a specified dimension and can use the existing the valuesets referenced by the standard form aggmap. The procedure takes three arguments:

• Family_relation_name: The name of a family relation, which specifies a dimension and the hierarchical relationships between the dimension members.
• Valueset_name: The name of a valueset to contain the results of the procedure. The valueset must be defined from the dimension in the family relation. If the valueset is not empty, ADVISE_REL deletes its contents before adding new values.
• Precompute_percentage: A percentage of the dimension to preaggregate. The default is 20%.

The valueset must be based on the dimension to be analyzed and in this case can be the valueset listed in the standard form aggmap. Based on the pre-compute percentage that you specify, ADVISE_REL returns a set of dimension members into the valueset specified. The values to a pre-compute are selected on the cost of drilling on a parent member. The more children a parent has the longer it will take to compute at query time.

This is the script I used to execute the procedure:

SET SERVEROUTPUT ON
spool advise_dims.log
EXEC dbms_aw.execute('aw attach SH_AW rw');
EXEC dbms_aw.advise_rel('PRODUCT_PARENTREL', 'PRODUCT_AGGRDIM_VSET', 25);
EXEC dbms_aw.advise_rel('CHANNELS_PARENTREL', 'CHANNELS_AGGRDIM_VSET', 25);
EXEC dbms_aw.advise_rel('GEOGRAPHIES_PARENTREL', 'GEOGRAPHIES_AGGRDIM_VSET', 25);
EXEC dbms_aw.advise_rel('TIME_PARENTREL', 'TIME_AGGRDIM_VSET', 25);
EXEC dbms_aw.execute('show values(PRODUCTS_AGGRDIM_VSET)');
EXEC dbms_aw.execute('show values(GEOGRAPHIES_AGGRDIM_VSET)');
EXEC dbms_aw.execute('show values(CHANNELS_AGGRDIM_VSET)');
EXEC dbms_aw.execute('show values(TIME_AGGRDIM_VSET)');
execute dbms_aw.execute('update;commit;aw detach SH_AW');
spool off
exit

Once this is complete the last step is load data into the cube and the aggregation engine will now use these values to pre-compute specific members.

For more information on these procedures check the OLAP Reference Manual:

http://download.oracle.com/docs/cd/B19306_01/olap.102/b14350/dbms_aw004.htm#CEGJCHHA
http://download-uk.oracle.com/docs/cd/B14117_01/olap.101/b10334/dbms_aw.htm#CEGFHDII

Hope this is useful

Keith

10.1.3.3.1 is now available on OTN

Over the weekend, Oracle Business Intelligence Suite Enterprise Edition, v. 10.1.3.3.1 became available on OTN (that would be the Oracle Technology Network).
You can download the Windows (x86) installable here (1.4GB). Downloads for Linux, HP, Solaris, and IBM are also available - see details at the download page here.
The documentation library is available online here.
And if you care to download the doc library as a zip file for offline viewing, it can be downloaded here (it's a 19MB zip file).

Some of the new features for this release are listed in the "New Features Guide", specifically under the 8 General New Features in Release 10.1.3.3.1 and 22 New Features in Release 10.1.3.3.1 chapters. You will now find support for additional languages in BI EE, specifically Arabic, Thai, and Hebrew.

Happy downloading, upgrading, installing.

BI Office in 10.1.3.3 - Secure Image

I don't like the image that appears after I have secured BI charts.

So you don't like the image and want to change it. Change is good. And you can have it.
How?

First a brief recap.
I am talking about Oracle Business Intelligence for Microsoft Office in the 10.1.3.3 release of Oracle Business Intelligence Suite Enterprise Edition.
Given a BI chart (specifically, given a chart that has been inserted into Excel or PowerPoint using the Oracle BI Office Add-in)- as a native Office chart, or as a refreshable image, or as a refreshable Flash chart - you can secure it.

And once secured, this is what appears in place of the chart data:



Refresh it and back appears the chart and the data.

What we want to do is change the image that appears. This image:

And let's say I want to put in its place this image:


To do that, for Excel to begin with, go here:


c:\program files\oracle is obviously where you installed BI Office.
You will see a file named "secure_content.png"
Make a copy of it if you please. And then copy the image of your choice (yeah... we can have the imagination run wild here. But not now) to this folder.


Now go back to Excel. And try the secure operation again.



There you go. You could do the same with PowerPoint too.



Go to this folder:



Replace the default image with this one:


Note that the secure_image.gif file is generated by the Add-in if it is not there - so if you do delete it, then it shouldn't really matter, I think, as it will be regenerated the next time you click the 'Secure Oracle BI Data' button.

BI Publisher Discoverer Integration - 5 - How to setup Discoverer

In a post long, long ago (last month actually - BI Publisher Discoverer Integration - 4 - getting the software) I described where and how you could get the Discoverer patch that had the web services for enabling the integration with BI Publisher. What I left out was that there are a few steps you need to do perform after you install the one-off patch. You should contact your Discoverer support analyst to get hold of that document. It's quite simple actually, but the reason I am not reproducing that doc here is that I am not sure whether I can share it externally. Really - that simple. The steps in that document are:
- Create a container for the machine.
- Add a library path for the patch for the discWS.jar file
- Provide access in the jazn-data.xml for the Discoverer web services code to perform a lookup from GUID to SSOUsername
- and a few others.

If I find out I can distribute the document I shall do that. In the meantime there is always the trusty Oracle support analyst to ping.

Learning more about BI Publisher - OBE

If you want to quickly learn about or get a feel for how BI Publisher works, and this may be especially the case for those who come more from the Discoverer side of things, I would suggest you spend a few minutes going over the excellent OBEs (Oracle By Example) series available on BI Publisher on OTN.

The Oracle by Example (OBE) series provides hands-on, step-by-step instructions on how to implement various technology solutions to business problems. OBE solutions are built for practical real-world situations, allowing you to gain valuable hands-on experience as well as use the presented solutions as the foundation for production implementation, dramatically reducing time to deployment.

The BI Publisher OBE page lists these OBEs:

• Getting Started with Oracle BI Publisher
• Integrating Oracle BI Publisher with Oracle Business Intelligence Enterprise Edition
• Exploring Advanced Features of Oracle BI Publisher

While these OBEs are based on the 10.1.3.2 release of BI Publisher, it doesn't matter for the most part - there are some new features in BI Publisher that you could read up on separately; enhancements to the Template Builder, support for digital signing of PDF files, support for Flash as a report format, etc...

Also, you can probably skip the second OBE for now, but it is still useful as it gives a glimpse into how you could integrate Discoverer content with a BI Enterprise Edition Dashboard page.

BI Publisher Discoverer Integration - 4 - getting the software

After the grand success of my first three posts on BI Publisher and Discoverer integration (BI Publisher Discoverer integration, BI Publisher Discoverer integration 2, and BI Publisher Discoverer Integration - 3) I considered retiring and moving to a life of book tours and guest lectures. Then I thought of something better - why not a fourth post. Well, why not? And mind you the thought was merely rhetorical (कभी कभी मेरे दिल मे ख़याल आता है - kabhi kabhie mere dil me khayal aata hai). So here goes.

This post describes how to get the Discoverer patch that contains the secret sauce that makes the magic of Discoverer web services come alive.

Since it is a bit late at night here, and I have to complete a document this week (preferably tonight), and since I have been making very little progress, I want to get this post done quickly, and also with the hope that writing this post will get me back into the groove of writing the bit that remains in the other document.

Let's get the simple stuff out of the way:
This integration is available in the 10.1.3.3.0 release of BI Publisher (Download the software - home page, or download the standalone version of BI Publisher for Windows or Linux). You can read 10.1.3.3 is now available for more information on 10.1.3.3.0. I shall describe the details of the integration in a future post; for now let's just focus on getting the software.

For the Discoverer bit, you need to have Oracle Application Server 10gR2 (version 10.1.2.2) installed. That's mandatory, no getting round it. Then, you need to download a patch, a Discoverer specific patch that contains the BI Publisher integration code. Actually it is the code that exposes certain functionality via SOAP based web services, but since this code was built primarily for the reason of integrating BI Publisher and Discoverer, we have been calling it the 'XMLP web services' - which is horribly wrong for at least two reasons: (i) the correct product name is 'BI Publisher', 'XML Publisher' was the old name, and certainly calling it 'XMLP' is a no-no, and (ii) these are not 'xmlp web services', these are Discoverer web services. But you know what I am talking about, right?
So where was I? Web services patch. Right. You can download the patch from Metalink (http://metalink.oracle.com/). Good. Can I go? May I go? I forgot to mention some specifics, didn't I? The devil is in the details, as they say.

Ok. Let's do it in a cleaner, more 'brevitous' manner, shall we?
- Download and install Oracle Application Server 10gR2 (version 10.1.2.2).
- Choose the 'BI & Forms' type install.
- Verify that Discoverer is working.
- Go to Metalink and search for all patches for 'Discoverer Plus or Viewer' for 'iAs 10.1.2.2'.
- Select patch '5648158'; the one that says 'Merge XMLP API to Main' (see what I said about using non-standard abbreviations externally?)
- Download it.


The 'Advanced Search' screen for patches on Metalink



The Discoverer web services patch



The patch download page

Bye...?
No?

Ok, just one more bit that I have to tell you.
The patch is password protected.
You need to get the password from your support analyst.
The support rep shall provide you with the password.
The support analyst shall first verify with you that you really need the patch and that you are not mistaking the patch for something that it's not (turn-by-turn instructions on how to get to Jhumri Tilaiya).

It (the patch) is a 12MB zip file for both platforms (Windows and Linux).

BI Publisher Discoverer Integration - 3

So far I have blabbed blogged two posts (BI Publisher Discoverer integration and BI Publisher Discoverer integration 2) on the BI Publisher and Discoverer integration in the BI EE 10.1.3.3 release.
In the second post I took a Discoverer worksheet and showed a barebones BI Publisher report created using that worksheet as the data source. No layout, no template, but showed glimpses of what is possible here.

This, the third post, goes a bit further, wherein I focus on the basics and show how you can take a single Discoverer worksheet and from it create multiple tables, charts, crosstabs, totals, etc... Basically to showcase one of the benefits of this integration and of the capabilities of hi fidelity layouts that BI Publisher enables. For specifics and details on BI Publisher I would suggest going to OTN and going over the excellent documentation (PDF User Guide, or HTML version), OBEs (Oracle By Example), the BI Publisher blog, or the other training material available from Oracle University.

Let's start from the beginning, a very good place to start.
Using a public connection ('Sales History' - as in based on the SH schema), I have created a Discoverer workbook, which has two worksheets in it. One worksheet is a table while the other is a crosstab (pivot table).


The table and pivot table are identical as far as the items go - both have the same items (fields if you will) on them, and both have the same parameters in use.


There is a parameter on 'Product Category', on 'Year', and one on 'Region'. The 'Year' parameter accepts only single values, while the other two parameters can accept multiple values - that is the way I have defined the parameters sir, not that 'Year' is any snootier than the other parameters.


And for reasons that may become clearer in a future post (or else it can become the plot for a blockbuster whodunit - 'The Curious Case of the Gratuitous Properties Screenshot') you can see the workbook properties, including the name, the identifier, and owner.


Using the steps I outlined in an earlier post, I create a BI Publisher report and point to this Discoverer worksheet as my data source. So, the first step is to create a new report as shown below, and give it a name.


If you remember, in the previous post I showed how you could use the web interface to specify that the data set for a BI Publisher report could come from a Discoverer worksheet. Well, there is more than one way to skin a cat (or is it 'bell a cat'? In any event I am a vegetarian, so these jokes are in extremely poor taste... the word 'taste' itself is a poor pun), and we shall look at a few skins bells ways here.
For that, let's go to Word and logon to BI Publisher from the 'Oracle BI Publisher' menu in Word. If you do not have the BI Publisher Template Builder for Word installed you can do that by downloading it from the BI Publisher Enterprise site.


After you have logged in the Template Builder displays a window for you to select either a template or define a new template.
So, if you remember the report we created in BI Publisher a few minutes back, you can now browse to the report (I saved it under 'Shared Folder' : 'Discoverer'). You can see from the right hand panel that my report is there: 'Sales History 1'). But if you look down at the panel at the bottom, the one that says 'Layout Templates', it is empty. Right? Correct? Yes. That is because we do not have a layout template defined for this report. And that is what this post is all about, right? So what I could do here is double-click the 'New' text and begin creating a layout template for this report.


More Than One Way To Create a Layout
But.... let's step back a minute (please do not try this when standing near a precipice, lest you bring matters to an abrupt end) and ask ourselves: "what if I wanted to start in Template Builder to begin with, and did not actually have a report created in BI Publisher Enterprise?" theek hai (ठीक है)- that's not a problem. You can begin in Template Builder itself.
Simply click the 'Workspace' dropdown and select 'Discoverer' from the dropdown list of values. You can see that 'Oracle BI' would actually take you to an Answers repository (or BI EE), whereas selecting 'Discoverer' takes you down the BI SE path.

Click 'Discoverer' and out pops a dialog asking you to select a connection - you see the Sales History connection: that's what you have to select. And the 'Select Connection' dialog couldn't be any more self-explanatory.

If you are familiar with Discoverer, and I dearly hope you are - else this whole series of posts is going to go down into the big aluminum can marked as non-bio-degradable, then you will know that I still have to select a workbook and worksheet. Once you have selected the connection the list of workbooks is displayed as a hierarchical list at the left, while the panel at the right shows the worksheets available. You will notice that because the Discoverer relational folder structure is basically one level deep (you do not have sub-foldering in Discoverer relational) the expand/collapse signs are a bit redundant and clicking them doesn't really do anything. They are useful when browsing workbooks from an OLAP connection. Anyway....

Say for this exercise I want to use the Discoverer table worksheet above, I double-click it. I now have to select a location in BI Publisher where this report should be saved under. So, another popup appears asking me to do that. In this case I choose to save it under the 'Shared Folder' folder directly (I already have one BI Publisher report based on the same Discoverer worksheet saved under the 'Discoverer' sub-folder).


Trust but verify - so let's make a quick detour into BI Publisher on the web and see whether the report does indeed exist where it should. Under the 'Shared Folders' folder you can see the report has been saved, and with a default name - 'For BI Publisher.Table' - which is a concatenation of the Discoverer workbook and worksheet name. And if you so desired you could click the 'Edit' link and you would see all the appropriate fields populated with values - just as if you had created the report from the web interface of BI Publisher. So you see.. that's what I meant that there is more than one way to bell the cat (CAT aspirants - sorry... but this is not what you are looking for).


Let us now return to Word and the Template Builder that is still waiting for us to return from this needless diversion and get down to the task of actually building a layout.

From the BI Publisher toolbar click 'Insert' and select 'Crosstab' as shown below.

The Cross Tab layout editor is quite simple and intuitive to use. It's after all meant to let you layout your fields on the two edges (column and row) and drop a measures item onto the body of the crosstab. You can click 'Preview' to see how the crosstab shall look like, or simply click 'OK'.


I also have added a couple of charts - the layout editor is again simple enough to use... It's probably a good idea to select a style and to uncheck the '3D' and 'Gradient' checkboxes. I don't like 3D effects or gradients in charts. Yes, yes, I am being rude in foisting my personal opinions, but regular readers of this blog would have been inured to that by now (I hope). A quick note, a sort of gotcha to be aware of. The image that gets displayed in the chart section of the editor is actually a sample image. There is one image for each combination of chart type and style and 3D effect and Gradient effect. There are more than 900 such images, one for each combination, that are available to the Template Builder.

So what does it have to do here? Simply this, that if you click the 'Preview' button, the image changes to show you an actual version of the chart based on your data. Then if you change any property in the chart the image reverts back to a standard, canned image from the stock images. So don't panic - this is expected behavior, and certainly simpler than generating a new image at design time everytime you change a graph property.


So, I now have one crosstab, two bar charts - one for Sales and one for Quantity, and I shall add one table, using the Table wizard. The wizard is, again, fairly straightforward to use.


Having selected all available fields to include in my table, this next screen is important as it lets me specify how I want my fields to be grouped. Here I want a grouping by Year ('Group above') and then by Quarter.


So now I have my four views defined.


Using these layouts, I want to preview my report. At this point more than the appearance I want to make sure that my data appears ok and that the views - the crosstab, the table, the charts - are all there in the report. If I click 'Preview' and select 'PDF' I shall be first prompted to save my report as a RTF file.

Why RTF? Because RTF is not a proprietary format like the binary DOC format that Word uses by default. As a diversion (yet another one!), take this very simple Word document with one line as shown below, and save it first as a .doc document and then as a RTF format document. Open the .RTF and .DOC files in a text editor and you shall see what I mean.






This is how my reports appears. You would notice that all the layouts are there as they should be. But not very pretty. This is because I have not started formatting them as I would like to. I am using Word, so that should be a fairly easy task to do. All I need to know is how to use the formatting capabilities of Word - headings, fonts, colors, page-breaks, table borders


Without getting into the specifics of what and how I format I report, this below is a preview of the same report, but after I have applied lots of formatting, layout changes - and yes one change - I added a Pie graph also to the report.



This below is the same report - what I have done is added an image to the header of the page so that it appears at the top of every page (as I have always maintained, an image is worth a thousand words, or sometimes even more)



See this screenshot below - this is the table I had defined above. What I have done is applied a 'Heading 1' level style to the 'Year' and 'Quarter' items, and also a blue background. That may not be the prettiest style, but then I am not an interface designer, and like a good man I know my limitations (or maybe not).
Another thing of note that I want to point out are the totals that I have inserted at the end of each country below. That is very easily done by usnig the Template Builder option of inserting a field and then selecting the 'sum' as the aggregation method.


And in the footer of the page I have set the page numbering. And oh yes, I also have a page break after every country, so that each country's data appears on a new page - again using Word's insert page break feature (or simply enter 'Ctrl+Enter' where you want the page break - but you already knew that).



Having created on layout template, I now want to upload this to the BI Publisher Server. That can be done in two ways. You can do that straight from Word itself. Simply select 'Upload Template As...' from the Template Builder menu.


Enter a name for the layout template and click 'OK'


And voila! It's done.


You would already have guessed that using the option I could make changes to the layout template in Word and upload it as a new layout, and so on. So by making incremental changes I could very quickly create new layouts for use.

Back at BI Publisher in the browser, I now want to run the report and view it in any of the supported formats: The parameters are displayed for me to select from. And, to repeat, you can either multi-select or single-select depending on how the parameters have been setup in Discoverer.




A simple post - the next one shall look at something different in this area.