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
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:
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:
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:
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:
500 Oracle Parkway
Redwood Shores, CA 94065
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.