For Training Contact: 91 8374899166 or 91 8790 99 81 82 or rritec@gmail.com
Tuesday 29 March 2016
Hierarchies
Handson07: Hierarchies
- What is hierarchy
- Types Of hierarchies
- What is level based hierarchy
- What is parent child hierarchy
- What is unbalanced or ragged and skipped hierarchy
Calculations
Handson06: Calculations
- How many types of
calculations available in BMM layer.
We can create three types of calculations
1. Creating calculation based on logical columns
2. Creating calculation based on physical columns
3. Creating calculation using Calculation Wizard
- What scenarios automatically handled
using calculation wizard.
Below two scenarios handled by using calculation wizard
1. To handle NULL values
2. To handle Divide by Zero values - A calculation which is
taking longer time then ,where we need to calculate
Request ETL team to calculate . - A calculation which is taking lesser time and reusable then ,where we need to calculate
If a calculation is reusable , simple and takes less run time then calculate in RPD . - A calculation which is taking lesser time and not reusable then ,where we need to calculate
If a calculation is taking less time and not reusable then do the calculations in the report only. - What is CAST() function?
This function changes the data type of an expression or a null literal to another data type.For example, you can cast a customer_name (a data type of Char or Varchar) or birthdate (a datetime literal). The following are the supported data types to which the value can be changed:
CHARACTER, VARCHAR, INTEGER, FLOAT, SMALLINT, DOUBLE PRECISION, DATE, TIME, TIMESTAMP, BIT, BIT VARYING
Use CAST to change to a DATE data type. Do not use TO_DATE.
Example
CAST(hiredate AS CHAR(40))
Please refer help document for all four below evaluate functions
- I have two columns in two tables, one column data type is varchar and another one is number. Then can we create foreign key join in physical layer.
Yes By using CAST() function ,we can achieve it
- What is evaluate function
EVALUATE
This function passes the specified database function with optional referenced columns as parameters to the back-end data source for evaluation. This function is intended for scalar calculations, and is useful when you want to use a specialized database function that is not supported by the Oracle BI Server, but that is understood by the underlying data source.
The embedded database function may require one or more columns. These columns are referenced by %1 ... %N within the function. The actual columns must be listed after the function.
The ability to use EVALUATE is disabled by default. To enable support for this function, change the EVALUATE_SUPPORT_LEVEL parameter in NQSConfig.INI. See Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for more information.
Syntax
EVALUATE('db_function(%1...%N)' [AS data_type] [, column1, columnN])
Where:
db_function is any valid database function understood by the underlying data source.
data_type is an optional parameter that specifies the data type of the return result. Use this parameter whenever the return data type cannot be reliably predicted from the input arguments. However, do not use this parameter for type casting; if the function needs to return a particular data type, add an explicit cast. You can typically omit this parameter when the database-specific function has a return type not supported by the Oracle BI Server, but is used to generate an intermediate result that does not need to be returned to the Oracle BI Server.
column1 through columnN is an optional, comma-delimited list of columns.
Examples
This example shows an embedded database function.
SELECT EVALUATE('instr(%1, %2)', address, 'Foster City') FROM employees
Examples Using EVALUATE_AGGREGATE and EVALUATE to Leverage Unique Essbase Functions
The following examples use the EVALUATE_AGGREGATE and EVALUATE functions. Note that expressions are applied to columns in the logical table source that refers to the physical cube.Use EVALUATE_AGGREGATE to implement custom aggregations. For example, you may want to compare overall regional profit to profits for the top three products in the region. You can define a new measure to represent the profits for top three products resulting in the Logical SQL statement:
SELECT Region, Profit, EVALUATE_AGGREGATE('SUM(TopCount(%1.members, 3, %2), %3)',
Products, Profit, Profit) Top_3_prod_Profit FROM SampleBasic
The Oracle BI Server generates the following expression for the custom aggregation:
member [Measures].[MS1] AS 'SUM(Topcount([Product].Generations(6).members,3,[Measures].[Profit]),[Measures].[Profit])'
Use the EVALUATE function on projected dimensions to implement scalar functions that are computed post-aggregation. EVALUATE may change the grain of the query, if its definition makes explicit references to dimensions (or attributes) that are not in the query.
For example, if you would like to see the Profits for the top five products ranked by Sales sold in a Region, after creating the applicable measure, the resulting Logical SQL statement is as follows
SELECT Region, EVALUATE('TopCount(%1.members, 5, %2)' as VARCHAR(20), Products, Sales), Profits FROM SampleBasic
The Oracle BI Server generates the following expression to retrieve the top five products:
set [Evaluate0] as '{Topcount([Product].Generations(6).members,5,[Measures].[Sales]) }'
- Please explain how to add or subtract date columns.
We cannot perform an arithmetic operation(plus ,minus ....etc) on a DATE or TIMESTAMP. These are considered Non-numeric types. To calculate difference between two dates, OBIEE provides a TIMESTAMPDIFF function. The syntax for using the function is:
TIMESTAMPDIFF(interval,
first_date, second_date)
First_date and second_date have to
be valid values in the TIMESTAMP type.
Interval is a specified value.
Valid values for INTERVAL are:
- SQL_TSI_SECOND
- SQL_TSI_MINUTE
- SQL_TSI_HOUR
- SQL_TSI_DAY
- SQL_TSI_WEEK
- SQL_TSI_MONTH
- SQL_TSI_QUARTER
- SQL_TSI_YEAR
If the dates
are not in a valid TIMESTAMP format, use CAST function to convert values into
TIMESTAMP type. See example below:
TIMESTAMPDIFF(SQL_TSI_DAY,CAST(VALUEOF("MY_DATE_COLUMN")as
TIMESTAMP), CURRENT_DATE)
We can also
use TIMESTAMPADD function to add an interval to a date.
10. Please explain Filter function.
Insert Filter dialog
Use this dialog to add a SQL FILTER function to a column formula in the "Edit Column Formula dialog: Column Formula tab".
For more information, see:
"Editing the Formula for a Column"
Components
Filter Function Area
Displays the SQL function in the form:
FILTER(expr USING filter_expressions)
Where:
expr is an expression that contains at least one measure column. For example, the expression "sales + 1" is allowed if "sales" is a measure column. The expression "productid" is not allowed if "productid" is a scalar attribute.
filter_expressions is a Boolean expression (evaluates to TRUE or FALSE) and does not contain any measure columns. Also, this expression cannot contain any nested queries.
Note:
If you selected some text in the Formula area before clicking Filter, then it is incorporated in the inserted SQL function, replacing the expression token (expr). For example, if you had selected "Sales Measures".Dollars before clicking Filter, then the SQL function would look like this: FILTER("Sales Measures"."Dollars" USING filter_expressions).
A complex filter statement might replace the filter_expressions token as follows:
FILTER("Sales Measures".Dollars USING ((Periods."Year" = '1999') AND ((Markets.District = 'CINCINNATI DISTRICT") OR (Markets.District = 'DENVER DISTRICT'))))
Even we can use in RPD too :)
11. Please explain Lookup function.
Lookup Functions
Multilingual schemas typically store translated fields in separate tables called lookup tables. Lookup tables contain translations for descriptor columns in several languages, while the base tables contain the data in the base language. Lookup is when a query joins the base table and lookup table to obtain the translated values for each row in the base table. A LOOKUP function is typically used in the Business Model and Mapping layer as an expression in a translated logical table column.
See the following sections in Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for full information:
"Supporting Multilingual Data"
"About the LOOKUP Function Syntax"
Syntax
Subscribe to:
Posts (Atom)