Difference B/W OBIEE 10g and 11g

• OBIEE 11g uses WebLogic Server as the application server as compared to Oracle AS or OC4J in OBIEE 10g. 
• The session variables get initialized when they are actually used in OBIEE 11g unlike OBIEE 10g where they were initialized as soon as a user logs in.
• In 11g Users and groups are created in Enterprise Manager (EM) whereas in 10g Users and groups are created in repository.
• New time series functions PERIOD ROLLING and AGGREGATE AT are introduced.
• Timeseries function can be created in Answers as well.
• Logical Table Source(LTS) Priority ordering is introduced.
•  OBIEE Join is done from fact to dimension whereas in 10g join is done from dimension to fact.
• A new column called the hierarchical column in introduced.
• OBIEE 11g now supports Ragged (Unbalanced) and Skipped Hierarchy.
• Parent Child hierarchy is introduced.
• We can now model lookup tables in the repository.
• SELECT_PHYSICAL command is supported in OBIEE 11g.
• In OBIEE 11g we can create KPIs to represent business metrics.

    In OBIEE 11g there are some changes in terminology as well:

• iBots are renamed as Agents.
• Requests are renamed as Analysis.
• Charts are renamed as Graphs.
• Presentation Columns are renamed as Attribute Columns.
• The new UI called Unified Framework now combines Answers, Dashboards, and Delivers.

       

Time Series Functions in OBIEE

1) Ago

Ago is a time series function that calculates the aggregated value from the current time back to a specified time period. this is useful for comparison  such as amount compared to amount a month ago.

Syntax:  AGO(<Measure >,<Level>,<number of period>)

Measure: A measure column.

Level: On which level you want to calculate this measure such as Year,Quarter,Month etc.

Number of period: how many level you want to go back from current time such as 2 year back,2 months back etc.

Example : AGO(Amount,Year,1) this will give the last year amount .

2) ToDate 

To Date is a time series function that calculates a measure attributes from the beginning of a specified time period to the currently displayed time period.

Syntax : ToDate(<Measure>,<Level>)

Measure: A measure column.

Level: On which level you want to calculate this measure such as Year,Quarter,Month etc.

Example : ToDate(Amount,Year) this will give the amount from the beginning of the year to Currently displayed time.

3) Period Rolling

Period Rolling is a new time series function in 11g .It computes the sum of a measure over the period starting x units of time and ending y units of time from the current time .This function allow us to create a aggregated measures across a specified set of query grain period rather than within a fixed time series grain.

Syntax: PeriodRolling(<Measure>,<Starting Period Offset>, < Starting Period Offset>)

Measure: A measure column.

Starting Period Offset: Starting period used in rolling aggregation.

Ending Period Offset: Ending period used in rolling aggregation.

Example : PeriodRolling(Amount,-2,0) this will give three months rolling sum of column Amount.It includes past two months with the current month. Numerical value -2 indicates May-14,Jun-14 if current month is Jul-14.Numerical value 0 indicates current month.

Note : This function has no time series grain ,the length of the rolling sequence is determined by query grain for example it will give the last three Month amount if the query grain is month , if the query grain is year then it will give last three Year Amount.

Repository and Session Variables

Session Variables: Session variables are created and assigned a value when each user logs on(when a new session is started). When a user begins a session, the Oracle BI Server creates new instances of session variables and initializes them.

There are two types of session variables:

1. System Variables: Oracle BI Server and Oracle BI Presentation Services use for specific purposes. System variables have reserved names, which cannot be used for other kinds of variables (such as static or dynamic repository variables, or for non-system session variables). 

Ex: To filter a column on the value of the variable LOGLEVEL set the filter to the Variable NQ_SESSION.LOGLEVEL.

2. Non System Variables: A common use for nonsystem session variables is setting user filters. For example, you could define a nonsystem variable called SalesRegion that would be initialized to the name of the sales region of the user.

Repository Variables :  A repository variable has a single value at any point in time.

There are two types of repository variables:

1. Static Variable: The value of a static repository variable is initialized in the Variable dialog. This value persists, and does not change until an administrator decides to change it.

2. Dynamic Variable : The values of these variables change with the values returned by queries. When the repository is started the value returned by the query in the initialization block will be assigned to the dynamic variable.

Implicit Fact column in OBIEE

An  Implicit fact column is used when we have multiple fact tables and the report is generated only from dimension tables. 

A user may create a report where it have only dimensions and not a single fact measure. when these are created BI server has multiple join paths available between dimensions and facts so it joins to nearest fact table and pulls the data hence results obtained might be different from what user is expecting.To overcome this we can create a Implicit fact column.This Implicit fact column shows the join path for the BI server.

Slowly Changing Dimensions (SCD)

Slowly Changing Dimensions (SCD) : With Slowly Changing Dimensions (SCDs) , data changes slowly, rather than changing on a time-based, regular schedule. In Data Warehouse there is a need to track changes in dimension attributes in order to report historical data.

Example of SCD dimensions are : Employee marital Status , City

Varios Types of SCD:

·         Type 0 - The passive method

·         Type 1 - Overwriting the old value

·         Type 2 - Creating a new additional record

·         Type 3 - Adding a new column

·         Type 4 - Using historical table

·         Type 6 - Combine approaches of types 1,2,3 (1+2+3=6)

SCD Type 1: Overwriting the old value. In this method no history of dimension changes is kept in the database. The old dimension value is simply overwritten be the new one. 

Example, we have this table:

Emp ID	Empname	City
123	Naveen	Delhi

After Naveen moved from Delhi to bangalore, the new information replaces the new record.

Emp ID	Empname	City
123	Naveen	Bangalore

The disadvantage of the Type I method is that there is no history in the data warehouse. It has the advantage however that it's easy to maintain

Note: This type should be only used when it is not  necessary  to track  historical changes.

SCD Type 2: This method tracks historical data by creating multiple records means a new record is added to give us the new information hence both records will be present

In our example,we have this table 

Emp ID	Empname	City	Start_Date	End_Date	Active_Flg
123	Naveen	Delhi	14-10-2010	31-12-9999	Y

after naveen moved from Delhi to bangalore ,a new record will be added in the table:

Emp ID	Empname	City	Start_Date	End_Date	Active_Flg
123	Naveen	Delhi	14-10-2010	22-5-2014	N
124	Naveen	Bangalore	23-5-2014	31-12-9999	Y

The advantage of the Type 3 method is that it helps us to keep all historical information  .it has the disadvantage however that this will cause the size of the table to grow fast

Note: This type should be only used when it is necessary  to track  historical changes.

SCD Type 3:  This method tracks changes using separate columns and preserves limited history.

In this type usually only the current and previous value of dimension is kept in the datawarehouse. there will be two columns one represents previous information  and another represents current information .there will also be a column that indicates when the current value becomes active.

Emp ID	Empname	Original City	Current_City	Effective_start_date
123	Naveen	Delhi	Bangalore	23-5-2014

The advantage of the Type 3 method is that it helps us to keep previous change only also it does not increase the size of table.it has the disadvantage however that it won't be able to keep all history where an attribute is changed more than twice.

Disabling OBIEE Queries Cache Hit

When we run reports the results get cached(Physical query won't generated ) and we need to clear the cache for the report each and every time when we run that report.

OBIEE Queries cache hit can be simply disable by prefixing a clause setting a variable value in the logical SQL of the query.

Go to the Advanced Tab in Answers for that particular report, Below XML section we have the Prefix and the Postfix sections there. Just go to the Prefix section and paste the below mentioned command:

SET VARIABLE LOGLEVEL=2, DISABLE_CACHE_HIT=1;

The next time you will run the report it won't hit the cache.

Numbers gets changed while adding Varchar column in OBIEE report

We faced a strange issue last week ,thought it might help you as well so posting this.

We have a multi-fact model implemented and most calculations on the data are done on BI layer (grouping and summing). 

We have a column (say C1) of 255 Varchar as length which when selected in a report used to change the actual numbers. We came across a shortcoming with OBIEE where on the presentation layer BI server was not able to correctly group the data based on C1 which use to add-on OR eliminate few numbers. 

Cause: The data field C1 had length specified as 255 whereas OBIEE server only correctly groups data when it is max of 80 character length.

Mitigation: We used a specific CAST operation (ex. CAST(C1 as CHAR(255))) and it solved the issue. 

This, though a simple resolution, but has solved a major problem faced by us where the Numbers impacted were the Profit and Loss figures and had a lot of hue an cry over it, and gave us a learning about the limitation of OBIEE.

Conformed Dimension With Example

In Data Warehousing,Conformed dimension is a dimension that has exactly the same meaning and content when being referred from different fact tables .Dimensions are conformed when they are either exactly the same (including keys) or one is a perfect subset of the other.For example, two dimension tables that are exactly the same except for the primary key are not considered conformed dimensions.

The time dimension is a common conformed dimension in an organization.

As you can see in the above figure,the time and Store dimensions are called confirmed dimensions as they are shared.

Factless Fact table

Factless Fact

A factless fact table is a table that contains nothing but dimensional keys.

There are two kinds of factless fact tables:

• Factless fact table describes event 
• Factless fact table describes condition.

Factless fact table for event

You often find that you want to track events or activities that occurs in your business process but you can’t find measures to track.  In these situations, you can create a transaction-grained fact table that has no facts to describe that events or activities.

Example

For example, you may want to track employee leaves.

At the center of diagram below is the FACT_LEAVE table that has no facts at all. However the FACT_LEAVE table is used to measure employee leave event when it occurs.

The following SQL statement is used to count number of leaves that  and employee has been taken:

Select employee_name as name,count(leave-type_id) as leave from fact_leave ,Dim_employee where fact_leave.employee_id = Dim_employee.employee_id.

Factless fact table for condition:

It is used to support negative analysis report, Factless fact table can be also used in these situations:

For example a store  that  did not sell product for a given period, to make this report you must have a fact table to capture all combinations: