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

Dynamic Filter

Hi friends , Today I'm showing you very interesting dynamic filter where we can change out filter parameter dynamically.

Step1:  Create a "Variable Prompt" type dashboard prompt and give the prompt name,some static values(here is "One' and "Two")

Step 2: 

Select filter for Product type

Edit the filter formula and click OK.

case when '@{filtervariable}'='One' then "Products"."P2  Product Type" end

Add Filter value and OK.

Add the prompt and Report in your Dashboard-

Run the report and Enjoy-

Aggregate at

• The usage of Aggregate at Function in OBIEE 11g

Step 1: Include the Revenue column

Step 2: Apply the Aggregate at  function on revenue column

Step 3: Fill up the function parameter

or you can do multiple levels from different Hierarchies.

Lookup Tables in OBIEE 11g

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

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

OBIEE 11g Installation

1) Download OBIEE 11g
Download the OBIEE 11g from the link
http://www.oracle.com/technetwork/middleware/bi-enterprise-edition/downloads/biee-111150-393613.html

Also Download RCU-

Once you download all the files, extract them and keep them under one directory(Keeping RCU in the same directory is not mandatory, just for convinience).

Before starting the installation keep your have got database up and running. RCU will create two schemas in the installation process…

First step of installaton is running RCU (Repository Creation Utility), go to C:\Software\OBIEE11gR1\bishiphome\rcuHome\BIN and double click on rcu.bat file.

Welcome page of the installation wizard will open …click Next. Select Create

Click Next

Enter all the required information, be sure that the database user has got sysdba previlages…Click Next

Click OK

Enter the Prefix (or leave the default one DEV) and select the components install…

Click OK and enter the passwords for all the users (2 users) that created in the following screen…

Click Next

Click OK

Click OK

Click Create

Click Close..we are done with RCU

Once we are done with RCU, start installing OBIEE 11g 64-bit software.

2) Go to Disk1 ( in my case C:\OBIEE 11g64bit\bishiphome\Disk1\) and Double click on setup.ext file

Click Next


Select “Simple Install” and click Next


Click Next

Select the path where you want to keep your Middle Ware home (e.g. C:\MW_OBI)

Create the user(weblogic is default) and give password, then click Next

Click Next

Give the connection string of the database, BIPLATFORM username and password which we created in the part 1 (RCU)

If you have got Oracle Support account, give that credentials here.

Click Install. Once installation finishes Configuration will start automatically. If you disable windows firewalls, you can go for lunch  See the task bar for Elapsed Time: (It took around 100 minutes for me)

Click Finish, automatically Persentations server opens in a web browser.

login with the user name and passowrd…you are done.

Now just leave everything on OBIEE setup it is automatically install and configurethe component you have selected.
If unfortunately the setup is get fail them check first the log file in install log file. Thelocation of log file is available on setup screen.
Some important links of OBIEE with simple install are:
        

Component	Web link
Presentation	http://machine_name:7001/analytics
Enterprise Manger	http://Machine_name:7001/em
Weblogic(Console)	http://Machine_Name:7001/console

Here is a video tutorial to Install the OBIEE 11G.

OBIEE learning from OTN :  
1) Tutorial
http://www.oracle.com/technetwork/middleware/bi-enterprise-edition/tutorials/index.html
2) Documentation Library:
http://www.oracle.com/technetwork/middleware/bi-enterprise-edition/documentation/index.html

Blog Ref: http://123obi.com