The following case study explains the choices made in creating an analytic workspace from the GLOBAL star schema.
Defining the GLOBAL_AW Workspace User
This example creates the GLOBAL analytic workspace in a different schema from the source tables. Example lists the SQL commands to define the GLOBAL_AW user with sufficient access rights to use Analytic Workspace Manager and to access the GLOBAL star schema. Alternatively, you can define users through Oracle Enterprise Manager.
Example SQL Script for Defining the GLOBAL_AW User
Examining Sparsity Characteristics for GLOBAL
By using SQL SELECT commands with the COUNT and COUNT(DISTINCT) functions, you can estimate how dense the resulting multidimensional cubes will be in the analytic workspace.
The PRICE_AND_COST_HISTORY_FACT table has 1407 rows with values for both UNIT_PRICE and UNIT_COST out of a possible 1728 dimension value combinations (48 months * 36 products). The Price cube (which is mapped to the PRICE_AND_COST_HISTORY_FACT table) is 80% dense, and so a composite will actually slow performance rather than improve it. A dense cube is fairly unusual, and the Create AnalyticWorkspace wizard does not support it, nor does the DBMS_AWM PL/SQL package. Thus, you will need to make some modifications to the workspace object definitions before loading the data.
The UNITS_HISTORY_FACT table has 110,166 rows with values for UNITS out of a possible 316,224 dimension value combinations (48 months * 36 products * 61 customers * 3 channels). This cube is 30% dense, which is sufficiently sparse for a composite.
Because Time is aggregated to the month level, the Time dimension is probably dense. This cube can use the default composite.
Running the Create Analytic Workspace Wizard
Make these choices when running the Create Analytic Workspace wizard:
The selected build option causes the Create Analytic Workspace wizard to define all of the workspace objects and fetch all of the data from the dimension tables. The measures are defined but do not have data. You can make changes to the object definitions before loading the data.
(An alternative approach to the one taken in this example is to generate a SQL script and modify the calls to DBMS_AWM, so that the analytic workspace is generated correctly and does not require modification.)
Manually Changing Object Definitions
Measure names such as UNIT_PRICE and UNIT_COST are given to the formulas in the analytic workspace that are used to aggregate the variables. The data itself is stored in variables with names like UNIT_PRICE_VARIABLE and UNIT_CUBE_VARIABLE.
Using OLAP Worksheet and the OLAP DML, you can make two changes to UNIT_ PRICE_ VARIABLE and UNIT_COST_VARIABLE:
These are significant changes that require the variables to be redefined, not simply modified.
Take these steps to redefine the variables:
Completing the Build
After all of the changes are made to the workspace object definitions, run the Refresh wizard to load the data, as described in "Refreshing the Data in an Analytic Workspace" .The dimensions do not need to be refreshed; only the cubes.
The workspace can be enabled now or after deploying an aggregation plan.
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|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.