Lookup table and its Usages..

Lookup table is a new feature in obiee 11g.
This is used mainly to:
• Resolve code columns and get description/name values from a lookup table.
• It can also be used for checking the currency conversion values from a daily updated source.


Lets try to see how this works.Assume that our customer table XW_CUSTOMER_D, has a column cust_state which is just the state code and not the state name:

We also have a table called STATE in the database which contains the state code and state names:

If we had a customer dimension and want to show the state name, instead of state code we need to create a lookup based on the STATE table.
Step-1
Import the STATE table into the physical layer of OBIEE and make an alias out of it.

Step-2
Define the Key column for the STATE table in the physical layer.

Step-3
Pull the lookup table to the BMM layer. You notice that it appears as a fact table with a # symbol in its icon(see arrow below). This is because the lookup table is not joined to any other table and OBIEE assumes it to be a fact table by default.


Step-4
Double click on Lkup_State_Name logical table in the BMM layer and check the “Lookup table” checkbox. In the Keys tab add a key which is based on the ID column.

Step-5
Define 2 new logical columns in the Dimn_Customers_D logical dimension table by right clicking on the lodical table name as follows :
These 2 columns are “Derived from existing columns using an expression”
1-Dense Looked Up State Name
Formula:
Lookup(DENSE "Sales"."Lkup_State_Name"."STATE_NAME",-> Lookup Value
"Sales"."Dimn_Customer_D"."Customer State"-> Lookup column
)

2-Sparse Looked Up State Name
Formula:
Lookup(SPARSE "Sales"."Lkup_State_Name"."STATE_NAME", -> Lookup Value
‘Lookup State Name not Found', ->String used when code not found(remove)
"Sales"."Dimn_Customer_D"."Customer State" -> Lookup column
)


We now have the 2 lookup column in place in the customer dimension as follows :

Step -6
Pull the new columns to the presentation layer into the customer dimension display folder and save your work.

---

Once done we create a report with the “Customer State” and “Sparse Looked up State Name”. The result will be as follows. Notice that code ‘UT1’ doesn’t have a corresponding lookup value in STATE lookup table and we see the string “Lookup State Name not Found”

This shows that “SPARSE” lookup causes an outer join between the customer and the lookup table.
The OBIEE server fires 2 SQL’s as follows and joins the results in the reporting layer.
SAWITH0 AS (select distinct T13265.CUST_STATE as c1
from
XW_CUSTOMER_D T13265 /* Dimn_CUSTOMER_D */ )
select distinct 0 as c1,
D1.c1 as c2
from
SAWITH0 D1
And
select T42371.STATE_NAME as c1,
T42371.ID as c2
from
STATE T42371 /* Lkup_State_Name */

Next we create a report with the “Dense Looked UP State Name” and we see the following result.

This shows that DENSE lookup executes an inner join between the customer and lookup table
We had a value ‘UT1’ which was not brought forward due to the inner join by the dense lookup, since that value was not present in STATE lookup table.
Moral :
Dense lookup = Inner join between Dimension and lookup table
Sparse Lookup=Outer join (with Nulls having a custom name) Dimension and lookup table
Hope you enjoyed this blog !!

Ref : http://www.adivaconsulting.com/adiva-blog/item/30-lookup-tables.html

What is a FACTLESS FACT TABLE?Where we use Factless Fact

We know that fact table is a collection of many facts and measures having multiple keys joined with one or more dimesion tables.Facts contain both numeric and additive fields.But factless fact table are different from all these.

A factless fact table is fact table that does not contain fact.They contain only dimesional keys and it captures events that happen only at information level but not included in the calculations level.just an information about an event that happen over a period.

A factless fact table captures the many-to-many relationships between dimensions, but contains no numeric or textual facts. They are often used to record events or coverage information. Common examples of factless fact tables include:

• Identifying product promotion events (to determine promoted products that didn’t sell)
• Tracking student attendance or registration events
• Tracking insurance-related accident events
• Identifying building, facility, and equipment schedules for a hospital or university

Factless fact tables are used for tracking a process or collecting stats. They are called so because, the fact table does not have aggregatable numeric values or information.There are two types of factless fact tables: those that describe events, and those that describe conditions. Both may play important roles in your dimensional models.

Factless fact tables for Events
The first type of factless fact table is a table that records an event. Many event-tracking tables in dimensional data warehouses turn out to be factless.Sometimes there seem to be no facts associated with an important business process. Events or activities occur that you wish to track, but you find no measurements. In situations like this, build a standard transaction-grained fact table that contains no facts.

For eg.

The above fact is used to capture the leave taken by an employee.Whenever an employee takes leave a record is created with the dimensions.Using the fact FACT_LEAVE we can answer many questions like

• Number of leaves taken by an employee
• The type of leave an employee takes
• Details of the employee who took leave

Factless fact tables for Conditions
Factless fact tables are also used to model conditions or other important relationships among dimensions. In these cases, there are no clear transactions or events.It is used to support negative analysis report. For example a Store that did not sell a product for a given period.  To produce such report, you need to have a fact table to capture all the possible combinations.  You can then figure out what is missing.

For eg, fact_promo gives the information about the products which have promotions but still did not sell

This  fact answers the below questions:

• To find out products that have promotions.
• To find out products that have promotion that sell.
• The list of products that have promotion but did not sell.

This kind of factless fact table is used to track conditions, coverage or eligibility.  In Kimball terminology, it is called a "coverage table."

Note:

We may have the question that why we cannot include these information in the actual fact table .The problem is that if we do so then the fact size will increase enormously .

Factless fact table is crucial in many complex business processes. By applyingyou can design a dimensional model that has no clear facts to produce more meaningful information for your business processes.Factless fact table itself can be used to generate the useful reports.

Ref: http://dwhlaureate.blogspot.com/2012/08/factless-fact-table.html