Write Back Setting in OBIEE 11G

Write Back is the ability to enter values directly into a report and have those values written in the database & used in calculations and charts in the report.
Please follow the steps listed below to configure Write Back in OBIEE 11g.

A- Repository Level Changes in all 3 layers to set Write Back Column :

Step 1:Physical Layer - From Physical Table Properties Go to-> General tab dialog box. Uncheck Cacheable

Step 2: Business Model & Mapping Layer -  Logical Column Properties Go to-> General tab. Check Writable option.

Step 3: Presentation Layer - Presentation Column Properties Go to-> General tab -> Permissions -> Set permission -> Read / Write (Radio Button), on the User / Application Role which you want to authenticate for WriteBack feature.

Step 4: Presentation Layer - Go to -> Manage-> Identity Manager -> Identity Management (Left pane) -> Select Application Role (to which you need Write Back Permission) -> Permission -> Query Limits (tab) -> Select Database (to which you need direct database execution rights) -> Change option from ignore to allow to the field named "Execute Direct Database Requests".

Now we are done with Repository level changes for WriteBack column. Let us proceed with other changes.

B- Changes in the File Level :

Step 1: Enable Write Back. Add the LightWriteback tag within  the serverinstance tag of instanceconfig.xml file  ( file path - $ORACLE_INSTANCE/config/OracleBIPresentationServicesComponent/coreapplication_obips1 )

<LightWriteback>true</LightWriteback>

If this entry already exists then no modifications required, else restart OracleBIPresentationServicesComponent for this change to be effective.

Step 2: WriteBack Template. You may give any name for the writeback.xml file. Here for example i am using the file name as writeback_sample.xml

Things to do before you start the Template:

• Identify the columns that are to be referenced. 
• We can use the column position or by column id in the XML definition. 
• We must include both insert and update statements in the template.

Template to be placed in the path:
$ORACLE_INSTANCE/bifoundation/OracleBIPresentationServicesComponent/coreapplication_obips1/analyticsRes/customMessages

Write-Back Template example: Name - eg. writeback_sample.xml
=================================================
<?xml version="1.0" encoding="utf-8" ?>
<WebMessageTables xmlns:sawm="com.siebel.analytics.web/message/v1">
<WebMessageTable lang="en-us" system="WriteBack" table="Messages">
   <WebMessage name="WriteBack_Template_Name">
      <XML>
         <writeBack connectionPool="Connection_pool_name">
            <insert>INSERT INTO Customer VALUES('@1','@2',@3,'@4',@5)</insert>
            <update>UPDATE Customer set Address='@2' where Name='@1'</update>
<!-- Identify the columns that are to be referenced. We can use the column position like @1 for first column, or by column id as you see them in the XML definition. -->
         </writeBack>
      </XML>
   </WebMessage>
<WebMessage name="Template_Name">
      <XML>
         <writeBack connectionPool="ConnectionPool_Name">
            <insert></insert>
            <update>UPDATE Product set Prod_Description='@2' where Name='@1'</update>
         </writeBack>
      </XML>
   </WebMessage>
<WebMessage name="Template_Name">
      <XML>
         <writeBack connectionPool="ConnectionPool_Purchase">
            <insert>INSERT INTO Supplier VALUES('@1','@2',@3,@4)</insert>
            <update></update>
         </writeBack>
      </XML>
   </WebMessage>
</WebMessageTable>
</WebMessageTables>
<!-- We may also use multiple insert / update commands for same or different connection pools as well based on our need -->
==================================================
[ Note - We need to mention insert & update statements based on our requirement. Above insert & update code is only for sample purpose & content within <!-- #### --> are comments. If we do not want to include SQL commands within the elements, then we must insert a blank space between the opening and closing tags. eg. <insert> </insert> rather than <insert></insert>]

In the above xml file...
Important Tags -

• WebMessage name - The name here will be used as WriteBack template name in analysis -> Table Properties -> Write Back.
• Write Back connectionPool - Connection pool name for Write Back.
• insert - Based on your requirement or leave blank ( if not required)
• update - Based on your requirement or leave blank ( if not required)

Now we are done with changes in the files. Lets proceed further & know what changes we need to make in analysis for the writeback report...

C- Changes in Analytics :

Step 1: Give Privilege to the Role for Write Back ( Administration -> Manage Privilege -> Write Back & add the role for writeback privilege). 

Step 2: Now you need to edit Column Properties -> writeback -> Check 'Enable Write Back' of the field needed for writeback, from criteria tab.

Step 3: Now edit Table Properties view -> writeback & check 'Enable Write Back' & mention Template Name as mentioned in writeback file ( WebMessage name="WriteBack_Template_Name" ). You may also rename Apply, Revert & Done Button & change Button Position if required.

You are now done with all WriteBack settings. You may test the report for set writeback features.

Math Functions in OBIEE

There are many science, research and development functions which are not much explored or used in business intelligence. Most of such functions belong to Math Functions. I am listing below all Math Functions.

Math Functions

Abs, Acos, Asin, Atan, Atan2, Ceiling, Cos, Cot, Degrees, Exp, Floor, Log, Log10, Mod, Pi, Power, Radians, Rand, RandFromSeed, Round, Sign, Sin, Sqrt, Tan, Truncate

As I am not working in any scientific or research & development project, so I decided to manually create such functions, to describe math functions. I then thought to randomly select some of trigonometric functions & test result through their graph, so I choose sin, cos and tan. But for trigonometric function I had to further make use of pi (you may use degrees as well).

The functions applied in my case for different functions is as below:

Sin - SIN((RCOUNT(1)-1)*PI()/4)

Cos - COS((RCOUNT(1)-1)*PI()/4)

Tan - TAN((RCOUNT(1)-1)*PI()/4)

Angle in Degree - CEILING(DEGREES((RCOUNT(1)-1)*PI()/4))

Where RCOUNT(1)-1 gives numeric value starting from zero & 1 in RCOUNT(1) is numeric symbolizing first column with distinct data & PI()/4 is used to multiply each count to pi/4 ( quarter of semicircle).

Math Function Test
Test Maths
Calculated Attributes
Angle in Degree	Sine Value	Cos Value	Tan Value
0	0	1	0
45	0.707	0.707	1
90	1	0	6.18986E+14
135	0.707	-0.707	-1
180	0	-1	0
225	-0.707	-0.707	1
270	-1	0	2.16235E+14
315	-0.707	0.707	-1
360	0	1	0
405	0.707	0.707	1
450	1	0	1.20485E+14
495	0.707	-0.707	-1
540	0	-1	0
585	-0.707	-0.707	1
630	-1	0	9.01976E+13
675	-0.707	0.707	-1
720	0	1	0
765	0.707	0.707	1
810	1	0	6.77417E+13
855	0.707	-0.707	-1

So as verifying from the graph & tabular value, it is clear to make use of them, leaving tan pi/2 as an exception of giving a finite value. However still aviating, some more scientific calculus functions, which will make obiee full package to be used in scientific research & development projects.

J

To Get Age between two Days in OBIEE Report

For Getting Age between two dates we use SQL Query as :

datediff(d, startdate, enddate) as Age

However default date format in OBIEE is Timestamp. So you need timestamp function (Available under Calendar/Date Function Heading) to get required result.

Syntax:

TIMESTAMPDIFF(interval, timestamp1, timestamp2)

Where:
interval is the specified interval. Valid values 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

timestamp1 and timestamp2 are any valid timestamps.

Example
For Number of Days between two days:
TimestampDiff(SQL_TSI_DAY, “FROM_DATE_COLUMN”, “TO_DATE_COLUMN”)

For Number of Days till current date:
TimestampDiff(SQL_TSI_DAY, “FROM_DATE_COLUMN”, CURRENT_DATE)

*You can also use this function for getting age difference for other time dimension attributes as week, quarter, month or year. All you need to do is change first attribute i.e 'interval' & other attribute's accordingly.

Conditional Expressions ( Function )

Conditional Expressions

Expressions are building blocks for creating conditional expressions that convert a value from one form to another. Expressions include:

·         CASE (Switch)

·         CASE (If)

CASE (Switch)

This form of the CASE statement is also referred to as the CASE(Lookup) form. The value of expr1 is examined, then the WHEN expressions. If expr1 matches any WHEN expression, it assigns the value in the corresponding THEN expression.

If none of the WHEN expressions match, it assigns the default value specified in the ELSE expression. If no ELSE expression is specified, the system automatically adds an ELSE NULL.

If expr1 matches an expression in multiple WHEN clauses, only the expression following the first match is assigned.

Syntax

CASE expr1

     WHEN expr2 THEN expr3

     {WHEN expr... THEN expr...}

     ELSE expr

END 

Where:

CASE starts the CASE statement. Must be followed by an expression and one or more WHEN and THEN statements, an optional ELSE statement, and the END keyword.

WHEN specifies the condition to be satisfied.

THEN specifies the value to assign if the corresponding WHEN expression is satisfied.

ELSE specifies the value to assign if none of the WHEN conditions are satisfied. If omitted, ELSE NULL is assumed.

END ends the CASE statement.

Example

CASE "TableHeading"."Column Name" 
WHEN 'Col_Val1' THEN 'Val1' 
WHEN 'Col_Val2' THEN 'Val2' 
WHEN 'Col_Val3' THEN 'Val3' 
ELSE "TableHeading"."Column Name" 
END

In this example, the WHEN statements must reflect a strict equality. 

CASE (If)

This form of the CASE statement evaluates each WHEN condition and if satisfied, assigns the value in the corresponding THEN expression.

If none of the WHEN conditions are satisfied, it assigns the default value specified in the ELSE expression. If no ELSE expression is specified, the system automatically adds an ELSE NULL.

Syntax

CASE 

     WHEN request_condition1 THEN expr1

     {WHEN request_condition2 THEN expr2}

     {WHEN request_condition... THEN expr...}

     ELSE expr

END 

Where:

CASE starts the CASE statement. Must be followed by one or more WHEN and THEN statements, an optional ELSE statement, and the END keyword.

WHEN specifies the condition to be satisfied.

THEN specifies the value to assign if the corresponding WHEN expression is satisfied.

ELSE specifies the value to assign if none of the WHEN conditions are satisfied. If omitted, ELSE NULL is assumed.

END ends the CASE statement.

Example

CASE 
WHEN ("TableHeading"."ColumnName1"='Val_A' AND "TableHeading"."ColumnName"='Value1') THEN 'Val1' 
WHEN ("TableHeading"."ColumnName2"='Val_B' AND "TableHeading1"."ColumnName2"='Value2') THEN 'Val2' 
WHEN "TableHeading"."Column Name"='Value3' THEN 'Val3' 
ELSE "TableHeading"."Column Name" 
END

Note :

Unlike the case-switch form, the WHEN statements in the case-if form allow comparison operators in a CASE statement, AND has precedence over OR.

Time Series Functions OBIEE

Time Series Functions

Time series functions operate on time-oriented dimensions. The time series functions calculate AGO, TODATE, and PERIODROLLING functions based on user supplied calendar tables, not on standard SQL date manipulation functions.
These functions let you use Expression Builder to call a logical function to perform time series calculations instead of aliasing physical tables and modeling logically.
To use time series functions on a particular dimension, you must designate the dimension as a Time dimension and set one or more keys at one or more levels as chronological keys.

Functions include:

• AGO
• PERIODROLLING
• TODATE

AGO

This function is a time series aggregation function that calculates the aggregated value from the current time back to a specified time period. For example, AGO can produce sales for every month of the current quarter and the corresponding quarter-ago sales.
Time series functions operate on members of time dimensions which are at or below the level of the function. Because of this, one or more columns that uniquely identify members at or below the given level must be projected in the query. Alternatively, you can apply a filter to the query that specifies a single member at or below the given level.
If unsupported metrics are requested, NULL values are returned and a warning entry is written to the nqquery.log file when the logging level equals three or above.
Multiple AGO functions can be nested if all the AGO functions have the same level argument. You can nest exactly one TODATE and multiple AGO functions if they each have the same level argument.

Syntax:

AGO(expr, [time_level], offset)

Where:

expr is an expression that references at least one measure column.

time_level is an optional argument that specifies the type of time period, eg. quarter / month / year.

In the Administration Tool, specify a logical level for time_level.

offset is an integer literal that represents the time shift amount.

Example :

The following example returns last year's sales:

SELECT Year_ID, AGO(sales, year, 1)

About the AGO Function Level

It is recommended that you explicitly specify the level of the AGO function using the [time_level] argument.

If you do not explicitly specify the[time_level] argument, the default level is determined as follows:

For example, the result of the query:
SELECT year, AGO(sales, 1) WHERE quarter=1
is the same as:
SELECT year, AGO(sales, year_level, 1) WHERE quarter=1

· If the measure used in the expression is a level-based measure in the time dimension (as set in the Administration Tool), then that same level is considered the default AGO level.

· Otherwise, the grain of the measure used in the expression, as determined by the BY clause of the measure shown in the logical request, is the default Ago level.

You can see the default AGO level for a given query in the Logical Request section of the query log.

PERIODROLLING

This function computes the aggregate of a measure over the period starting x units of time and ending y units of time from the current time. For example, you can use PERIODROLLING to compute sales for a period that starts at a certain quarter before and ends at a certain quarter after the current quarter.

Time series functions operate on members of time dimensions which are at or below the level of the function. Because of this, one or more columns that uniquely identify members at or below the given level must be projected in the query. Alternatively, you can apply a filter to the query that specifies a single member at or below the given level.

You cannot nest AGO and TODATE functions within a PERIODROLLING function. Also, you cannot nest PERIODROLLING, FIRST, and LAST functions.

If you embed other aggregate functions (like RANK, TOPN, PERCENTILE, FILTER, or RSUM) inside PERIODROLLING, the PERIODROLLING function is pushed inward. For example, PERIODROLLING(TOPN(measure)) is executed as TOPN(PERIODROLLING(measure)).

Syntax

PERIODROLLING(measure, x ,y [,hierarchy])

Where:
measure is the name of a measure column.

x is an integer that specifies the offset from the current time. Precede the integer with a minus sign (-) to indicate an offset into the past.

y specifies the number of time units over which the function will compute. To specify the current time, enter 0.

hierarchy is an optional argument that specifies the name of a hierarchy in a time dimension, such as yr, mon, day, that you want to use to compute the time window. This option is useful when there are multiple hierarchies in a time dimension, or when you want to distinguish between multiple time dimensions.

If you want to roll back or forward the maximum possible amount, use the keyword UNBOUND. For example, the function PERIODROLLING (measure, -UNBOUND, 0) sums over the period starting from the beginning of time until now.

You can combine PERIODROLLING and AGGREGATE AT functions to specify the level of the PERIODROLLING function explicitly. For example, if the query level is day but you want to find the sum of the previous and current months, use the following:

SELECT year, month, day, PERIODROLLING(AGGREGATE(sales AT month), -1)

Examples

SELECT Month_ID, PERIODROLLING(monthly_sales, -1, 1)

SELECT Month_ID, PERIODROLLING(monthly_sales, -UNBOUND, 2)

SELECT Month_ID, PERIODROLLING(monthly_sales, -UNBOUND, UNBOUND)

Determining the Level Used by the PERIODROLLING Function

 
The unit of time (offset) used in the PERIODROLLING function is called the level of the function. This value is determined by the measure level of the measures in its first argument and the query level of the query to which the function belongs. The measure level for the measure can be set in the Administration Tool. If a measure level has been set for the measure used in the function, the measure level is used as the level of the function. The measure level is also called the storage grain of the function.

If a measure level has not been set in the Administration Tool, then the query level is used. The query level is also called the query grain of the function. In the following example, the query level is month, and the PERIODROLLING function computes the sum of the last, current, and next month for each city for the months of March and April:

SELECT year, month, country, city, PERIODROLLING(sales, -1, 1)

WHERE month in ('Mar', 'Apr') AND city = 'New York' 

When there are multiple hierarchies in the time dimension, you must specify the hierarchy argument in the PERIODROLLING function. For example:

SELECT year, fiscal_year, month, PERIODROLLING(sales, -1, 1, "fiscal_time_hierarchy")

In this example, the level of thePERIODROLLING function is fiscal_year. 

TODATE

This function is a time series aggregation function that aggregates a measure from the beginning of a specified time period to the currently displayed time. For example, this function can calculate Year to Date sales.
Time series functions operate on members of time dimensions which are at or below the level specified in the function. Because of this, one or more columns that uniquely identify members at or below the given level must be projected in the query. Alternatively, you can apply a filter to the query that specifies a single member at or below the given level.
If unsupported metrics are requested, NULL values are returned and a warning entry is written to the nqquery.log file when the logging level equals three or above.

A TODATE function may not be nested within another TODATE function. You can nest exactly one TODATE and multiple AGO functions if they each have the same level argument.

The following example returns the year-to-month sales:

TODATE is different from the TO_DATE SQL function supported by some databases. Do not use TO_DATE to change to a DATE data type. Instead, use the CAST function.

Syntax

TODATE(expr, time_level)

Where:

expr is an expression that references at least one measure column.

time_level is the type of time period, such as quarter, month, or year.

Example

SELECT Year_ID, Month_ID, TODATE(sales, year)

Reference : otn.oracle.com