Oracle OLAP Spreadsheet Add-In: Feature Overview
|
|
ORACLE
BUSINESS INTELLIGENCE 10g
ORACLE BUSINESS INTELLIGENCE SPREADSHEET
ADD-IN
January 2005
|
Executive Summary
Most organizations have seen a mass proliferation of data-extraction programs
and downloading of static data into spreadsheets within their data warehouse
environment. This causes enormous problems, both technical and business-related.
The result of this process of continual download and manipulation is an ever-expanding
range of disconnected spreadsheets. It is never clear from the spreadsheet
analysis how historical data based on numerous sources is managed. For example,
what happens when the source systems are updated or restated? Because there
is no link to the source data, the end user is not alerted to the availability
of refreshed data. As a result, it is never clear which spreadsheet is the
latest version. Spreadsheets also have scalability limitations in terms of
the volume of data that can be processed by a single sheet. Again, users resolve
this by creating multiple worksheets and gluing them together with formulas.
The Oracle BI Spreadsheet Add-In resolves all of these issues and many more.
It combines the analytic power and scalability of Oracle OLAP with the familiarity
of Microsoft Excel by embedding OLAP capabilities directly within Excel. Users
report against common business definitions that are stored centrally in the
Oracle Database. This provides a consistent and quality view of their corporate
information. In addition, users can perform ad hoc analysis on this data using
traditional OLAP exploration techniques such as drilling, pivoting, and paging
the view of data.
The following image provides an example of how Oracle OLAP data can seamlessly
integrate with existing Excel functionality such as functions and charts. This
is possible because the add-in is directly embedded within the Excel and not
the other way around, which is a common approach.
Worksheet with OLAP Query Editor and Excel charts
Solving a Business Problem
It is generally accepted that many Excel users spend more time creating their
data set rather than analyzing their data and creating useful business information.
Most Excel applications are based on static data that must be updated either
manually or via error-prone macros. Most applications contain hundreds of
complicated formulas that are difficult to understand and debug. As a result,
each spreadsheet contains its own set of business rules and business terminology,
making it difficult to compare and contrast results from different worksheets.
What is needed is the ability to view business data intelligently from within
Excel by accessing a central database repository of rules and definitions
and by executing the required query directly within the database. By running
queries at the database level, users benefit from the performance and security
of the Oracle database, and at the same time, users can be sure of getting
the very latest information. Once the data has been retrieved, users can modify
and enhance the result set using standard Excel features.
Easy access through the Oracle
BI Spreadsheet Add-In
The Oracle BI Spreadsheet
Add-In provides easy access to:
- Common Business Rules and definitions
on the OLAP Catalog
- The scalability of Oracle OLAP
- The power of Oracle OLAP
- Excel functionality
Common
Business Rules and definitions on the OLAP Catalog
One of the most important advances in the business intelligence industry is
the establishment of a metadata standard. Metadata is used to represent a wide
range of information, including information about the sources that are used
to create the data warehouse, data transformations, security, business rules,
and so on. Metadata provides meaning to the data in the data-warehousing environment.
Oracle’s OLAP solutions use the Common Warehouse Metadata (CWM) model
to describe the underlying data warehouse schema for all Oracle’s OLAP
analytic reporting solutions: Discoverer, Business Intelligence Beans (BI Beans),
Reports, and Oracle BI Spreadsheet Add-In. All these tools share a common repository
of business definitions: measures, dimensions, hierarchies, levels, and attributes.
These tools are then used to drive the two key components that are used by all
Oracle’s BI reporting solutions, including the Oracle BI Spreadsheet Add-In:
- OLAP Calculation
Builder Wizard
- OLAP Query Builder
Wizard
As a result, all Oracle’s reporting
business intelligence products provide exactly the same easy-to-understand,
easy-to-use, business-oriented view of the underlying data warehouse.
Easy
Access to the Scalability of Oracle OLAP
Users often download large amounts of data into Excel in order to perform analysis.
In most cases, the majority of that data is not required for the final analysis.
It is simply required to generate new aggregate result sets or other calculations.
The Oracle OLAP Spreadsheet Add-In features a wizard to define OLAP calculations
that run in the database. This can be an advantage for certain types of calculations
-- typically calculations that require large amounts of data to be processed.
An example is percent growth of sales from last year. In Excel, this calculation
would require data from the previous year to be downloaded into Excel. By creating
an OLAP calculation with the add-in, the calculation will be performed on the
server without additional data being downloaded to Excel.
The Oracle OLAP Calculation Wizard allows users to define new business indicators
for analysis, thus extending and enhancing their data warehouse environment.
For example, they may create a 50-day moving average for sales based on historical
information. The Calculation Wizard in the add-in allows users to create such
derived calculations (custom measures) using template-driven pages. The Calculation
Wizard has full support for the advanced functions of Oracle OLAP including
ratios, shares, time-series analysis, and many other calculations. The Calculation
Wizard allows users to define new measures using business terms and definition.
It functions in the way that users think about their business.
Oracle OLAP Calculation Wizard
Easy Access to the Power of Oracle OLAP
Advanced analytics are the heart of any business intelligence application.
The Oracle database, with its embedded OLAP engine, offers the advanced analytic
features that allow users to obtain the answers to complex data questions. The
Oracle BI Spreadsheet Add-In makes it easy for end-users to view OLAP data.
A wizard guides users through the process of building a query to retrieve data
directly into the worksheet. The Oracle OLAP Query Wizard, which is shared with
other Oracle OLAP BI tools, presents the user with a logical view of the data,
based on the business terms and definitions that are retrieved from the OLAP
catalog.
The Oracle OLAP Query Wizard simplifies the formulation of complex business
questions and the presentation of insightful answers. The patented Query Wizard
enables end users to ask questions the way they look at their business. The
wizard provides users with different ways of building queries and of saving
those queries as favorites for use within other queries and or worksheets.
Oracle OLAP Query Editor
Easy Access to Excel Functionality
The Oracle BI Spreadsheet Add-In retrieves data from Oracle OLAP and dynamically
updates the Excel cells as the user navigates the OLAP cube. For this reason,
standard Excel functionality can be used with the data. The user can use formatting,
can extend the queries with Excel formulas, and can even run macros to process
the data. The queries are stored completely in standard Excel workbooks. These
workbooks can be shared with other users by e-mail, shared file system folders,
and so on. Any user that has access to Oracle OLAP can refresh and analyze the
data on demand. A power user can effectively create an Excel-based application
that can be shared with other users, and all users will be running against the
same centrally stored data.
Working with OLAP Queries
With the Oracle BI Spreadsheet Add-In, users can easily define and save OLAP
queries.
Defining an OLAP Query
A query can be defined in one of three ways, as described in the following
list:
- Users can pick individual or groups of dimension values, such as Products,
from a hierarchical list.
- Users can use advanced selection criteria to create data driven queries,
such as the Top 10 selling products.
- Users can mix and match by manually selecting values and then adding conditions
to further refine the query. For example:
- What are the top five selling products in Asia in 2003?
- Which sales representatives exceeded their quota by at least 10% over
the last year?
- By what percent have in-store sales increased over the same period
a year ago?
Coding these queries would require a hundred of lines of SQL code. However,
by taking advantage of the user-friendly and powerful Oracle OLAP Query Wizard,
a business user can specify this query without knowing any SQL or without
understanding the underlying structure of the database. The wizard supports
all of the functions that business intelligence users are accustomed to, such
as ranking and exception tools. Other selection tools include level selections,
attribute selections, hierarchical selections, and time-based selections.
Saving
an OLAP Query
The steps to define a query can be complex, and end users might find themselves
re-creating the same query steps every time the data is updated. The Oracle
BI Spreadsheet Add-In makes it possible to store the query definition as a saved
selection that can be reused. The user can keep a library of saved selections
that can be easily applied from the Query Wizard or directly from Excel using
a simple macro.
Example of Query using Saved Selections
Key
Features of the Oracle Business Intelligence Spreadsheet Add-In
The following list outlines the key features of the Oracle BI Spreadsheet Add-In:
- Ability to create compelling reports in a familiar environment:
- Create OLAP-enabled worksheets.
- Share OLAP-enabled worksheets with other users.
- Refresh OLAP data on demand.
- Extend OLAP queries with regular Excel features: formulas, charts, and
macros.
- Leverage existing knowledge of Excel.
- Easy access to common business rules and definitions:
- Access a shared view of the data based on common business rules.
- Share the same definitions with all Oracle BI reporting solutions.
- Extend the business definitions using Oracle OLAP wizards.
- Easy access to the scalability of Oracle OLAP:
- Execute advanced OLAP queries directly in the Oracle database.
- Execute advanced calculations directly in the Oracle database.
- Access terabytes of data with ease and simplicity.
- Easy access to the power of Oracle OLAP:
- Display an easy-to-use wizard to help define queries using business
terms.
- Display an easy-to-use wizard to help define advanced OLAP calculations.
- Navigate through the OLAP cube by drilling, pivoting, and paging directly
in Excel.
- Use advanced analytic features of Oracle OLAP including rankings, exceptions,
hierarchies, and lag/lead calculations.
Summary
The Oracle BI Spreadsheet Add-In makes it easy for users to create compelling
reports based on Oracle OLAP data sources, structured according to pre-defined
and shared business rules. All this is performed within the familiar environment
of Excel.
Wizards guide users through the process of creating advanced queries and powerful
calculations. OLAP queries and calculations are executed directly within the
Oracle server, leveraging the scalability and processing power of the Oracle
OLAP engine. When the data is displayed in Excel, users can perform ad hoc analyses
using traditional OLAP exploration techniques such as drilling, pivoting, and
paging through the view of data. Because the Oracle BI Spreadsheet Add-In is
embedded directly within Excel, users can access standard Excel functionality
to further enhance their OLAP reports. Once a worksheet has been defined, it
can quickly and easily be shared with other users.
In summary, the Oracle BI Spreadsheet Add-In provides users with access to:
- A repository of common shared business rules and definitions.
- The scalability, security, and performance of the Oracle database.
- Powerful querying capabilities, which reflect the way users view their business.
- The ability to extend and enhance OLAP analysis using standard Excel features.
Oracle Corporation
World Headquarters
500 Oracle Parkway
Redwood Shores, CA 94065
U.S.A.
Worldwide Inquiries:
+1.650.506.7000
Fax +1.650.506.7200
http://www.oracle.com/
Copyright © Oracle Corporation
2005
All Rights Reserved
This document is provided for
informational purposes only,
and the information herein is
subject to change
without notice. Please
report any errors herein to
Oracle Corporation. Oracle
Corporation does not provide
any warranties covering and specifically
disclaims any
liability in connection with
this document.
Oracle is a registered trademark
of Oracle Corporation.
All other company and product
names mentioned are used
for identification purposes only
and may be trademarks of
their respective owners.
| 
Bookmark
