A custom measure is calculated from one or more measures stored in the analytic workspace. Often, it is created by an analyst just for the duration of a session. However, a custom measure can also be saved as a permanent part of the analytic workspace.
These saved custom measures can either be solved at run-time or stored in variables. Run-time calculations do not require disk storage space and do not extend the processing time required for data maintenance. However, they may slow performance. You need to decide which measures to calculate on demand and which, if any, to store. The custom measures described in this chapter are calculated for a query.
Methods of Defining Custom Measures
Two PL/SQL packages support custom measures in an analytic workspace:
In addition, you can use the OLAP_TABLE function to define and access custom measures outside of the framework of standard form, as described in "Using OLAP_TABLE for Direct Access to Workspace Data".
Analytic Support for Custom Measures
Regardless of the method that you use to define a custom measure, you will express the calculation itself using the OLAP DML. Following are descriptions of the many functions and commands available for manipulating your data. In addition, you can perform inter-row calculations using operators for multiplication (*), division (/), addition (+), subtraction (-), and so forth.
Forecasts and Regressions
The OLAP DML offers the most sophisticated and up-to-date forecasting and regression tools, including simple linear regressions, non-linear regression methods, single exponential smoothing, double exponential smoothing, and the Holt-Winters method.
Time Series Manipulation
The time series functions perform operations such as lead, lag, and moving average. Table describes the time series functions, which can easily be incorporated into custom measures.
OLAP DML Time Series Functions
The financial functions include interest rate calculations, depreciation, and payment schedules, similar to those provided in spreadsheets.
For example, the FPMTSCHED function calculates a payment schedule (principal plus interest) for paying off a series of fixed-rate installment loans over a specified number of time periods. The following call to FPMTSCHED calculates 36 payments based on the amounts listed in the LOANS variable, at the interest rates listed in the RATES variable, for the MONTH dimension of these variables.FPMTSCHED(loans, rates, 36, month)
Statistical operations include standard deviation, rank, and correlation. For example, the STDDEV function calculates the standard deviation. The function call STDDEV(units month) returns the standard deviation of values in the UNITS measure for all months that are currently selected.
Functions are available to perform a wide variety of computations (such as sine, cosine, square root, minimum, and maximum) and data type conversions.
For example, the MAX function compares two expressions and returns the larger value. This function call
compares the ACTUAL and FORECAST measures and returns the larger values for all dimension members currently selected.
The OLAP DML provides support for manipulating both single-and multibyte character sets, with functions for concatenating strings, locating a string within a larger body of text, inserting a string, and so forth.
For example, the EXTCHARS function extracts a portion of text. The function callEXTCHARS('lastname,firstname', 1,8)
extracts the first 8 characters, which contains the characterslastname
Allocations are a critical part of planning applications. Given a target for the organization, whether for sales quota, product growth, salary, or equipment, managers must allocate that target among its contributors. The supported allocation methods include:
Aggregation is a basic feature of analytic workspaces. When you create a standard form analytic workspace, it contains a default aggregation plan for each cube. Wizards in Analytic Workspace Manager enable you to identify stored aggregate levels quickly and easily.
The OLAP DML offers a broader range of aggregation methods than are currently available through Analytic Workspace Manager or PL/SQL procedures. You can choose whatever method seems appropriate: by level, individual member, member attribute, time range, data value, or other criteria.
A model is a set of interrelated equations. These are some of the modeling features supported by the OLAP DML:
You can assign results either to a variable or to a dimension member.
Dimension-based equations provide flexibility; since you do not need to specify the modeling variable until you solve a model, you can run the same model with any other measure with the same dimension. For example, you could run the same model on Budget and Actual, which both have a Line dimension.
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.