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: