|
Oracle® Business Intelligence Concepts Guide
10g Release 2 (10.1.2.1) B16378-01 |
|
 Previous |
 Next |
|
Oracle® Business Intelligence Concepts Guide
10g Release 2 (10.1.2.1) B16378-01 |
|
|
Previous |
Next |
This chapter provides two scenarios to show you how to craft a business intelligence system. One scenario uses OLAP for advanced analytics, and the other scenario reports against transactional data.
This chapter contains the following topics:
This chapter describes two fictitious companies, with two different data sets and two different sets of requirements. It presents an Oracle Business Intelligence solution for each one, using a set of Oracle Business Intelligence components.
The Global example is based on the Global schema, which is available on the Oracle Business Intelligence Web site at
http://www.oracle.com/technology/bi/olap/olap.html
The Acme example is based on the sample schemas provided with Oracle Database. Both solutions follow the same basic steps, which are listed in the next topic.
Implementing a business intelligence system requires careful planning to assure that it meets expectations. These are the basic steps:
It is important to anticipate how end users will analyze the data. By interviewing key users, you can identify the questions that the business intelligence system needs to answer.
You can ask questions such as:
What information do you have now?
What additional information do you need?
How do you want the information presented?
Business requirements can be generated at all levels of your organization. The following are examples of the requirements you might need to address:
Board of Directors
Competitive analysis
Key indicator tracking
Trend analysis
Exception reporting
Administrative Analysis and Planning
Investment and acquisitions assessment
Reorganization analysis
Long-range planning
Resource allocation
Capacity planning
Human resource planning
Finance Department
Budgeting
Consolidation
Variance analysis
Financial modeling
Cash management
Asset liability modeling
Activity-based management
Sales and Marketing Department
Product profitability
Customer profiling
Distribution analysis
Sales performance and effectiveness
You can find out about the reports and data sources currently available, and what users like and dislike about their current information system. You may also discover their expectations about run-time performance.
From the types of questions that end users want answered, you can identify the sources of the data that can provide the answers. The data can be distributed among numerous locations, such as transactional databases and flat files. If the data is not available within your company, then you should discuss whether it is possible to acquire the data or whether end users must modify their expectations.
The logical data model must support the needs and expectations of your end users. The logical data model presents the data in business terms so that users can quickly identify the data they need to use.
For OLAP tools, you define dimensions, measures, and so forth. Then you can map the metadata objects to the physical data sources.
For relational tools, you define items, calculations, joins, and so forth using any existing relational data source.
You must deploy the data model as physical objects in the database and load the data from its sources.
For OLAP tools, the data store is an analytic workspace.
For relational tools, the data store may be the current OLTP system or a star schema in a data warehouse.
Business intelligence data is essentially hierarchical, so that data can be summarized at various levels. For performance, some of this data (ideally the data most frequently queried) is summarized and stored as a data maintenance procedure.
In analytic workspaces, summary data is stored in the same analytic workspace objects as the base-level data. In relational schemas, summary data is stored in materialized views.
The client tools query the metadata to find out what data is available, where to get it, and how to present it.
Users must have database access rights granted to them so that they can view and manipulate the data.
After the data store is ready for client access, you can distribute the software and provide documentation to your end users.
Global Enterprises sells computer hardware and software in a variety of outlets. While they have been industry leaders for many years, the price of hardware has fallen dramatically in the last few years. They have little room for error if they are going to remain profitable.
To create a data warehouse, they need to consolidate information from disparate sources from around the world. This will provide them with the data to answer the following business analysis questions:
What products are profitable?
Who are our customers, and what and how are they buying?
What accounts are most profitable?
What is the performance of each distribution channel?
Is there a seasonal variance to the business?
Identifying trends in the data that will answer these questions requires ad-hoc analysis and sophisticated analytic computations, such as:
Global Enterprises uses OracleBI Warehouse Builder to generate a star schema for their data warehouse. Their current problems arise not in the data itself, but in the tools to manipulate that data. They believe that a choice of OracleBI Discoverer Plus OLAP and OracleBI Spreadsheet Add-In will satisfy their sales managers. However, they are also planning to have their IT department take a close look at OracleBI Beans to explore custom solutions to their most aggressive requirements, such as forecasting and what-if analysis.
The applications development team at Global Enterprises will use Analytic Workspace Manager for developing analytic workspaces. After they finish designing the logical model, they plan to turn over responsibility for managing the analytic workspaces to the IT department, who will use OracleBI Warehouse Builder. Until then, the IT department will continue to deploy star schemas.
Global Enterprises will use a number of components of Oracle Business Intelligence to implement their BI solution.
Prerequisite: Install the software identified under "Software Requirements".
Global Enterprises has already identified its end-user requirements and the data sources, as described previously. To create an analytic workspace for Global Enterprises, take these remaining steps, which are described in more detail below:
Open the Model View of Analytic Workspace Manager, and define these objects:
Analytic workspace
Dimensions
Levels
Attributes
Hierarchies
Cubes
Measures
Analytic Workspace Manager stores the logical model as standard form metadata in the analytic workspace, and it creates all of the objects needed to instantiate the model at the same time.
When the source data is in a star or snowflake schema, you can quickly define a logical multidimensional model. The dimension tables contain columns for values at various levels, and their attributes. For example, a Time dimension table might have surrogate keys for weeks, quarters, and years; they are the levels of a hierarchy, which you might name the Calendar hierarchy. The display names, end date, and time span columns are attributes of the Time dimension. Each fact table is a cube, and the columns containing facts are the measures. Other types of schemas require individual analysis.
To map the logical objects to their data sources, choose the Mapping folder in the Model View Navigator of Analytic Workspace Manager. You can drag-and-drop the source tables onto the mapping canvas, then draw connectors between the logical objects and the appropriate columns. The Maintenance Wizard loads data from relational data sources into the analytic workspace.
You can define the aggregation rules for each cube. The rules identify the aggregation operator for each dimension and the portion of data that you want to presummarize and store. This plan provides the default summarization rules for all measures in the cube. The Maintenance Wizard executes the aggregation rules and generates the stored aggregates.
Using the Calculation Wizard, you can easily define the derived measures for all users, thus adding a wealth of information to your analytic workspace.
OracleBI Discoverer Plus OLAP and OracleBI Spreadsheet Add-In enable users to define additional calculations (custom measures) and saved selections, so they do not have to be defined entirely in the analytic workspace. Discoverer Plus OLAP stores them in the Discoverer Catalog, where they can be accessed by other users with the appropriate permissions.
For users to access the data, they require the following database privileges:
CONNECT
QUERY REWRITE
SELECT on the table in which the analytic workspace is stored
You can use Oracle Enterprise Manager or SQL to define users and groups, and to assign these privileges.
After the analytic workspace is ready for use, you can make OracleBI Spreadsheet Add-In and OracleBI Discoverer Plus available for installation. You can also set up a dashboard with Discoverer portals so that power users can publish their reports.
Advanced analytics take development time. You can define forecasts, models, and what-if scenarios in the analytic workspace by using OracleBI Beans. Meanwhile, analysts can take advantage of the rich analytics already available through custom measures.
Check the Oracle Technology Network at http://www.oracle.com/technology for new versions of Analytic Workspace Manager Release 2, which will support these definitions in a graphical interface.
Acme Corporation operates worldwide to fill orders for several different products. The company has several divisions:
Human Resources tracks information on company employees and facilities.
Order Entry tracks product inventories and sales of company products through various channels.
Product Media maintains descriptions and detailed information on each product sold by the company.
Information Exchange manages shipping through business-to-business (B2B) applications.
Acme wants to provide a flexible, out-of-the-box query and analysis tool to their executives and managers, who are distributed across the globe. The tool must enable them to query vast amounts of data that is stored in their Oracle databases, regardless of the product edition or the schema designs. They expect the queries to range from analysis of individual transactions on a daily basis to historical expenditure profiles for the whole organization. They do not have any requirements for extensive or ad-hoc analysis.
Acme chooses OracleBI Discoverer for its query and analysis tool. The majority of users need only a Web browser to retrieve and review their data in numerous graphical formats, and they can drill and pivot through the data.
OracleBI Discoverer provides all of the components needed to satisfy Acme's business intelligence requirements. Since they already have their data stored in Oracle databases, and their immediate goal is analyzing "as is" data, they have no need for an ETL tool at this time. At a later date, they may want to use OracleBI Warehouse Builder to create a true data warehouse containing cleaned, transformed, and aggregated data in a star schema.
Acme Corporation has already identified its end-user requirements and the data store already exists, as described previously. To implement a Discoverer system for Acme Corporation, you must take these additional steps, which are described in more detail below.
Prerequisite: Install the software identified under "Software Requirements".
|
See Also: Oracle Business Intelligence Discoverer Administration Guide for detailed instructions for performing each of these steps. |
The EUL contains the metadata that defines one or more business areas. A business area is a conceptual grouping of tables and views that apply to a user's specific data requirements. Business areas can be set up to reflect the needs of the user or group of users accessing the EUL.
After identifying the requirements of your users, you should have a good idea of the information different user groups need to access. For example, one group of users might want to access sales information, another group might want to access manufacturing information, and so on.
In OracleBI Discoverer Administrator, you define a business area for classifying information with a common business purpose. Then you specify which database tables and views hold that class of information. You also load metadata and other information about the tables and views into the business area.
Use OracleBI Discoverer Administrator to create summary tables, which will optimize query performance. You also have the option of using OracleBI Discoverer automated summary management or registering existing summary tables.
The default settings and contents of a business area are sufficient to enable users to access and analyze data. However, OracleBI Discoverer Administrator provides you with a number of features to enhance the default analysis capabilities so that users can view the data in the most flexible and understandable way.
These are some of the steps you can take to refine business areas:
Create optional and mandatory conditions to restrict the number of rows returned in a folder.
Create calculated items so that users do not need to formulate complex calculations themselves.
Create joins to combine folders that were not joined when tables were loaded from the database.
Combine folders into complex folders to completely hide joins and relational structures from users.
Create custom folders to represent a result set returned by SQL.
Edit item names, descriptions, and other formatting information to make data easier to understand.
Create item classes to support lists of values, alternative sorts, and drill to detail.
Create hierarchies to simplify drill-down operations.
You can grant access to business areas based on user requirements for accessing data.
OracleBI Discoverer does not compromise database security. Users cannot see information in OracleBI Discoverer that they do not have database privileges to access. All OracleBI Discoverer security and privileges are imposed in addition to database security.
After the EUL has been created and access rights have been granted, you can make OracleBI Discoverer Plus available to users. You can also set up an OracleAS Portal dashboard where power users can publish their reports through OracleBI Discoverer Portlet Provider.
