This example shows how to create an analytic workspace by acquiring data from relational tables and flat files. It uses Global data simply because you are already familiar with this data set;
These are the basic steps:
Designing and Implementing the GLOBALX Star Schema
Because Global data is already stored in the GLOBAL star schema, GLOBALX can simply mimic its design for the Price and Units cubes. The only difference is that while GLOBAL is populated with data, GLOBALX contains empty tables.
GLOBALX Schema Diagram
Procedure: Creating the GLOBALX Sample Schema
Take these steps to create the sample GLOBALX schema:
Creating OLAP Catalog Metadata for the GLOBALX Schema
The metadata for the GLOBALX star schema can be generated by any available method: the OLAP Management tools in Oracle Enterprise Manager, the OLAP Bridge in Oracle Warehouse Builder, or the CWM2 PL/SQL package. This example arbitrarily uses the CWM2 packages.
Take these steps to create OLAP Catalog metadata for the GLOBALX schema:
GLOBALX Metadata Displayed in Analytic Workspace Manager
Creating the GLOBALX Analytic Workspace
You can create the GLOBALX analytic workspace from the empty tables and OLAP Catalog metadata using any of the available methods: The Create Analytic Workspace wizard in Analytic Workspace Manager, the OLAP bridge in Oracle Warehouse Builder, or the DBMS_AWM PL/SQL procedures. This example uses the wizard to generate a script containing calls to DBMS_AWM, then modifies the script. Take these steps to create the GLOBALX analytic workspace.
Fetching the Price Cube From Relational Tables
The Price cube has two measures, UNIT_COST and UNIT_PRICE, with two dimensions, PRODUCT and TIME. In this example, the data is loaded manually from the GLOBAL star schema. However, it could be loaded from any form of relational tables using the method described here.
Take these steps to populate the Price cube in the GLOBALX analytic workspace:
Loading Products From GLOBAL.PRODUCT_DIM
The GETPROD program shown in Example fetches data into the PRODUCT dimension, the member_parentrel relation and the long_description variable. Note that parent values must be added to the PRODUCT dimension before their children, otherwise an error will occur in populating the parent relation. Thus the SELECT statement lists the level columns from the highest level of aggregation to the lowest. The member_inhier Boolean variable is populated with values of 1 for true and 0 for false. The member_levelrel relation is also populated with text values that match the values of the levellist dimension.
Define the example program, then execute it with this command:CALL getprod
OLAP DML Program for Loading Products From GLOBAL.PRODUCT_DIM
Example shows a selection of the data to verify that the load was successful.>
Exampe Viewing the PRODUCT Dimension and Attributes
Loading Time From GLOBAL.TIME_DIM
The program to fetch TIME members, shown in Example, is very similar to the previous program for fetching PRODUCT members. It differs only in the addition of time span and end date attributes.
However, TIME members must be sorted chronologically within levels in order to support time series analysis functions. Each row contains dimension members at every level, so the TIME dimension is populated with the levels completely mixed. Example shows a program that sorts the TIME dimension. It uses the SORT command to order the current, temporary status of the TIME dimension, saves this order in a valueset, then loops over the valueset with the MAINTAIN command to reorder the values permanently.
Define the example programs, then execute them with these commands:CALL gettime CALL timesort
OLAP DML Program for Loading Time From GLOBAL.TIME_DIM
OLAP DML Program for Sorting TIME Dimension Members
The TIME dimension has too many members to list in its entirety, but selecting members by ancestry (as shown for PRODUCT) temporarily reorders the dimension. The results will show whether the objects were populated correctly, but not necessarily whether the members are sorted correctly. Example uses LIMIT commands that do not change the original order. The report shows the correct sort order.
Viewing the TIME Dimension and Attributes
Loading the PRICE Cube From PRICE_AND_COST_HISTORY_FACT
Example shows the program for fetching data into UNIT_PRICE_VARIABLE and UNIT_COST_VARIABLE. Note that the data must be loaded into the variables, not into the measuredef formulas, which have the same names as the logical measures. These are the definitions for these variables:
The ORDER BY clause in the DECLARE CURSOR SELECT statement sorts the rows so that PRODUCT (ITEM_ID) is the slower varying dimension and TIME (MONTH_ID) is the faster varying dimension. This organization corresponds to the order in which the values are stored in the workspace variables, as shown by their definitions. This sort order enables the data to be loaded as quickly as possible.
All of the dimension members must already exist in the analytic workspace. If a value is found without a match among the dimension members, then the program fails with an error.
Define the example program, then execute it with this command:CALL getpricecube
OLAP DML Program to Load the PRICE Cube From PRICE_ AND_ COST_ HISTORY _ FACT
Unlike most measures, those from the Price cube are dense so that it is easy to check the data. The LIMIT commands in Example select members at all levels of the PRODUCT and TIME hierarchies. There is only data at the lowest levels, so the other levels are calculated on demand. Notice that the measuredef formulas are shown, not their underlying variables.
To make a quick check for any values in a variable, use the ANY function:SHOW ANY(variable NE NA)
For example:SHOW ANY(unit_price_variable NE NA)
A return value of YES indicates that at least one cell has data; a value of NO indicates that all cells are empty.
Validating the PRICE_CUBE Data Load
Loading the Units Cube From Flat Files
The Units cube has one measure, UNITS, and four dimensions, TIME, CUSTOMER, PRODUCT, and CHANNEL. The TIME and PRODUCT dimensions have already been added to the analytic workspace in "Fetching the Price Cube From Relational Tables", so unless additional dimension members are contained in the flat files, these two dimensions do not need to be maintained. However, the CUSTOMER and CHANNEL dimensions must be fully populated before loading the UNITS measure.
This example loads data from three flat files:
The basic process for loading from flat files is the same as loading from relational tables, as described earlier in "Fetching the Price Cube From Relational Tables". The difference is only in the OLAP DML programs.
Loading Channels From CHANNELS.DAT
CHANNELS.DAT is a comma-delimited file as shown in Example . It has fields that correspond to the columns of the CHANNELS_DIM dimension table in the Global star schema:
All Channels ID
All Channels Description
With these fields, you can populate the CHANNEL dimension, the CHANNEL_LONG_DESCRIPTION attribute, the CHANNEL_PARENTREL relation, and the CHANNEL_LEVELREL relation. In addition, you can populate CHANNEL_INHIER and CHANNEL_LEVELREL with literal text during the data load.
CHANNELS.DAT Flat File2,Direct Sales,1,All Channels 3,Catalog,1,All Channels 4,Internet,1,All Channels
Loading the dimension values is straightforward except for the All Channels dimension member (1), which appears only in the third field. It must be added to the CHANNEL dimension before it can be used as the parent of other dimension members in CHANNEL_PARENTREL. For this reason, the third field is read first as a dimension member that has no parent, and again as a parent value. Example shows the program for loading the data. Define the sample program, then execute it with this command:CALL read_channels
OLAP DML Program for Loading Channels from CHANNELS.DAT
CHANNEL is a very small dimension with only four members, so you can review the results of the load without selecting a sample.
Example shows the results of the load.
Viewing the CHANNEL Dimension and Attributes
Loading Customers From CUSTOMERS.DAT
CUSTOMERS.DAT is a structured file, so that text columns are enclosed in double quotes. It has fields that correspond to the columns in the CUSTOMERS_DIM dimension table in the GLOBAL star schema:
Market Segment ID
Market Segment Description
Total Market ID
Total Market Description
All Customers ID
All Customers Description
Example shows the first six fields of a few sample records. It contains the same types of information as CHANNELS.DAT, so that all of the equivalent workspace objects are populated. The one significant difference is that the data supports two hierarchies.
CUSTOMERS.DAT Flat File
The load program for CUSTOMERS.DAT, like the one for CHANNELS.DAT, must read parent dimension members before their children. Field 13 contains the most aggregate level, All Customers ID, so it is loaded first. The program shown in Example 11–18 loads the parent members for the SHIPMENTS_ROLLUP hierarchy first, then the parent members for the MARKET_ROLLUP hierarchy. The base level, SHIP_TO, belongs to both hierarchies.
Define the example program, then execute it with this command:CALL read_customers
OLAP DML Program for Reading CUSTOMERS.DAT
CUSTOMER is too large a dimension to show the complete results of the load. Example shows how to select a few base-level dimensions and their ancestors, so that you can check that the supporting objects were populated correctly.
Viewing the CUSTOMER Dimension and Attributes
Reading the UNITS_CUBE.DAT File
UNITS_CUBE.DAT contains just the Units measure with columns for each dimension key. Example shows several sample rows.
UNITS_CUBE.DAT Flat File
The data is written to the UNITS_VARIABLE variable, not to the UNITS formula. This is the definition of UNITS_VARIABLE:DEFINE UNITS_VARIABLE VARIABLE DECIMAL <TIME UNITS_CUBE_COMPOSITE<CUSTOMERPRODUCT CHANNEL>>
Notice that it is dimensioned by UNITS_CUBE_COMPOSITE, but the incoming data is aligned with the base dimensions, as shown in Example. All four base dimensions are already populated.
Define the example program, then execute it with this command:
OLAP DML Program For Reading UNITS_CUBE.DAT
Measures typically contain vast amounts of data but are quite sparse, so you must target specific cells to verify that the data was loaded correctly. You can do this by selecting a row or two from the source file and limiting the workspace dimensions to those values, as shown in Example.
Validating the UNITS_CUBE Data Load
Populating Additional Standard Form Metadata Objects
If you enable the GLOBALX analytic workspace for the BI Beans now, the dimension views will have many empty columns. For example, the view of the CHANNEL dimension has these empty columns:
The ___POP_FMLYREL and ___ORDR.HIERARCHIES programs populate the workspace objects that are displayed by these columns. Example shows the commands used for the CHANNEL dimension. Repeat these commands for the PRODUCT, CUSTOMER, and TIME dimensions.
You do not need to re-enable the GLOBALX workspace after populating these objects. The data is available through the views as soon as you commit the changes to the database.
OLAP DML Commands to Populate CHANNEL Metadata Objects
Using Tools with the GLOBALX Analytic Workspace
You can now use the Create and Deploy Aggregation Plan wizards and the enablers.
For refreshing the data, you must revise your data loading programs to access new data sources or to restrict the load to new time periods.
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.