An analytic workspace initially contains only the detail data from the relational schema. However, it also contains the hierarchies, levels, and parent relations that are needed to aggregate the data. The aggregate data in an analytic workspace replaces the use of materialized views; all of the aggregate data is created in the analytic workspace. To optimize run-time performance, you must generate and store some aggregate data.
Strategies for Calculating Aggregates
A data cube in an analytic workspace can be solved at two distinct times:
A typical strategy is to aggregate some of the data as a data maintenance procedure and the rest of the data on demand. This strategy is called skip-level aggregation. The data cube is presented to the application fully solved, with no detectable difference between the values that were retrieved from storage and the values that were calculated for the query. When skip-level aggregation is done correctly, the time to calculate the unsolved levels is negligible.
How to Select Levels to Pre-Aggregate and Store
A good strategy for identifying levels for pre-aggregation is to determine the ratio of dimension members at each level, and to keep the ratio of members to be rolled up on the fly at approximately 10:1. This ratio assures that all answer sets can be returned quickly. Either the data is stored in the analytic workspace, or it can be calculated by rolling up 10 values at a time into each aggregate value.
This 10:1 rule is best applied with some judgment. You might want to permit a higher ratio for levels that you know are seldom accessed. Or you might want to pre-calculate levels at a lower ratio if you know they have heavy use.
About Aggregation Plans
An analytic workspace that was created by the Create Analytic Workspace wizard has a default set of rules, called an aggregation plan, for each cube. A default plan specifies that:
The default aggregation plans assure that the measures in a cube are always presented to an application with fully solved data; that is, all levels of all hierarchies in an answer set are populated. However, because the default plans specify that all levels must be calculated during the user's session, their use typically causes unacceptably slow performance.
You can define and deploy aggregation plans that precalculate some of the data. Each measure can have its own aggregation plan, or any number of measures in a cube can share the same one. For each aggregation plan that you create, you must specify:
An aggregation plan does not take effect until it is deployed. Deployment creates and modifies objects in the analytic workspace to support the aggregation plan, and then calculates all stored aggregate levels. While you can create an aggregation plan in a few minutes, deployment can take much longer, depending on the amount of data that needs to be calculated and the available resources. You may want to schedule aggregation for off-peak hours.
How to Create and Deploy an Aggregation Plan
An aggregation plan can be used for all the measures in a cube, or just for selected measures.
Creating an Aggregation Plan
To create an aggregation plan:
The aggregation plan is a permanent part of your analytic workspace until you explicitly delete it.
Changing the Aggregation Operator
The Aggregation Plan wizard always specifies SUM as the method of aggregation. However, you can change the method to one of these operators:AVERAGE FIRST LAST MAX MIN
A procedure in the DBMS_AWM package changes the operator in an existing aggregation plan. The syntax of the procedure call is this:EXECUTE DBMS_AWM.SET_AWCUBEAGG_SPEC_AGGOP('aggplan', 'aw_owner', 'aw_name', 'cube', 'measure', 'dimension', 'operator')
For the BI Beans to be aware of this change, you must either re-enable your analytic workspace or manually execute theCWM2_OLAP_METADATA_REFRESH.MR_AC_REFRESH procedure.
Deploying an Aggregation Plan
Deployment first deletes any previously aggregated data, then solves the data for the specified levels. To deploy an aggregation plan:
You can edit an aggregation plan, but no change is made to your data until you redeploy the modified plan. Similarly, you can delete an aggregation plan, but no change is made until you deploy another plan for the same measures. You must redeploy your aggregation plans whenever you refresh the data.
OLAP Related Interview Questions
|Informatica Interview Questions||Data Warehouse ETL Toolkit Interview Questions|
|PL/SQL Interview Questions||Data Warehousing Interview Questions|
|Testing Tools Interview Questions||SQL Database Interview Questions|
|MySQL Interview Questions||ERP Tools Interview Questions|
|Oracle 11g Interview Questions||Hyperion Financial Management Interview Questions|
|Hyperion Essbase 5 Interview Questions||Database Design Interview Questions|
|Data modeling Interview Questions||Oracle Hyperion Planning Interview Questions|
|Biztalk Esb Toolkit Interview Questions|
OLAP Related Practice Tests
|Informatica Practice Tests||PL/SQL Practice Tests|
|Data Warehousing Practice Tests||Testing Tools Practice Tests|
|SQL Database Practice Tests||MySQL Practice Tests|
|ERP Tools Practice Tests||Oracle 11g Practice Tests|
|Hyperion Financial Management Practice Tests||Hyperion Essbase 5 Practice Tests|
|Database Design Practice Tests|
The Multidimensional Data Model
The Sample Schema
Developing Java Applications For Olap
Defining A Logical Multidimensional Model
Creating An Analytic Workspace
Sql Access To Analytic Workspaces
Exploring A Standard Form Analytic Workspace
Adding Measures To A Standard Form Analytic Workspace
Predicting Future Performance
Acquiring Data From Other Sources
Administering Oracle Olap
Materialized Views For The Olap Api
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.