FYI: Kimball Fact Table Primer (Star Schemas)

One fact table does not fit all, depending on your requirements you may have a need to identify all invoices transactions for a given point in time.  Maybe the end user wants to identify sales revenue for a regular, predictable date interval (such as quarter).  Also consider the order fulfillment pipeline which creates a scenario where a supply chain specialist must identify the span of time it takes a product to be processed through the pipeline.

Should 1 denormalized fact table design be used for the above scenarios? Of course not . Take, for example, Oracle Business Intelligence Applications HR Analytics 7.9.6.3 . Oracle designers created work force fact tables of various designs to capture the business process being measured: Transactional fact tables for employee salary, periodic fact tables for headcount, and accumulating fact tables for the HR recruitment process.

Below is a primer of Kimball fact tables and the characteristics of each one. Before selecting the fact table type to use for your design, always make sure you:

1) Identify the business process you are trying to measure
2) Determine the grain of the fact table
3) Identify the attributes of the fact table (dimensions)
4) Identify the measures to be captured in the fact table.

Also highly recommended: Read Kimball's Data Warehouse Tool kit. This is a must read for any OBIEE Architect. The table below is referenced from the above book.

CHARACTERISTIC	TRANSACTION GRAIN	PERIODIC SNAPSHOT GRAIN	ACCUMULATING SNAPSHOT GRAIN
Time period represented	Point in time	Regular, predictable intervals	Indeterminate time span, typical short-lived
Grain	One row per transaction event	One row per period	One row per life
Fact table loads	Insert	Insert	Insert and update
Fact row updates	Not revisited	Not revisited	Revisited whenever activity
Date dimension	Transaction date	End-of-period date	Multiple dates for standard milestones
Facts	Transaction activity	Performance for predefined time interval	Performance over finite lifetime

These three fact table variations are not totally dissimilar because they share conformed dimensions, which are the keys to building separate fact tables that can be used together with common, consistent filters and labels. While the dimensions are shared, the administration and rhythm of the three fact tables are quite different.

How to: Default Date Prompt to NULL in OBIEE Answers

Consider the following scenario:

• You have an accumulating periodic fact table that tracks an item as it moves through its distribution life cycle from warehouse to the point of sale at a store.

If applying Kimball Design Principles (which you should always follow in a denormalized dimensional model), your accumulating periodic fact table would be sparsely populated and contain NULL values for stages the item has not yet encountered. Take the fact table below as an example.

This fact table is what Kimball classifies as a 'periodic accumulating snapshot' because:

1) each record represents the life cycle of 1 item
2) each record is updated as it reaches a key date, as opposed to a new record being added
3) each record has a definite beginning and end

Assume you had a requirement to create a report that identified all of the products that arrived at the store but have yet to be sold.

To translate this technically, you're looking for Sales Dates that are NULL.

You cannot simply create a filter that says 'Sales Date <> NULL' because NULL is not a valid DATE type. NULL in DATE is represented as a '01-01-1900'. You could then be tempted to create a filter as 'Sales Date <> '01-01-1900' but that is not a valid DATE either, since by default Dates are stored as DATETIME stamps.

An easy way to exclude Dates that are stored as NULL, is to do it in answers as follows:


Step 1: Create a Dashboard Prompt for your Date that Defaults to SQL Values:

and populate it with:

SELECT

case when 1=0 then Time."Fiscal Date" else Cast(NULL as Date)  END

FROM "ENTER LOGICAL FACT TABLE HERE"

The 'Enter logical fact table here' should be replaced with the name of the logical fact table of that subject area in the BMM.

Step 2: Edit Column Formula to Cast as Date

Date dimensions default to DATETIME in the repository so you will need to cast your column as a Date.

This will generate the following result:

Note that although the default value looks 'blank', a filter of NULL is being applied in the physical query:

How to: Complete OBIEE 10g installation Linux Part 1 of 4 - Configuration of JDK for Oracle Business Intelligence

Consider the following scenarios:

* You receive a request to demo  Oracle Business Intelligence on a firm's local network
* A new code migration process requires multiple environments for validation
* New security protocols are implemented which requires developers to use their local machine for all pre-UAT work

What is the commonality in the above scenarios? Each requires you install, configure, set up a complete OBIEE 11g stack.

The ability to do this is so critical to the success and self worth of an OBIEE practitioner, that I have decided to create a series of tutorials on how to set up, install and configure a complete OBIEE 10g solution, commonly referred to as a '10g stack'.

The installation of an OBIEE 10g stack can be summarized in the following steps:


1) Configuration of JDK for Oracle Business Intelligence 
2) Set up of the Oracle Business Intelligence Server
3) Set up of the Oracle Business Intelligence Presentation Services
4) Set up of the Oracle Business Intelligence Client Tool set

This post will focus on step 1 which is really a prerequisite to the installation of Oracle Business Intelligence Server. I made this its own step because it is critical to the success of the installation, and if done incorrectly, will make steps 2 through 4 a nightmare.

To successfully complete this how-to, you will need:

1) a basic understanding of fundamental unix commands specific to the redhat distribution (see: http://bsd.org/unixcmds.html )

2) a basic understanding of vi (see: http://www.washington.edu/computing/unix/vi.html )

3) ability to access root on your linux box  (or have your sys admin perform certain commands on your behalf)

4) a redhat RHEL 5.x box

5) the willpower to not give up when things go wrong, because if this is your first time doing this, you will mess up :)

--
Let's begin.
 

Step 1: Confirm JDK 1.5.0 or higher is not installed

Redhat RHEL 5.4 comes installed with JRE 1.6 which is not the same as JDK 1.5 . JRE contains the run time binaries needed to run java applets , JDK contains the APIs needed to execute java functions in your application.

We need JDK because in an OBIEE 10g basic installation, J2EE is the Application Server.

To start, open your terminal:

in terminal, run :

java -version

it will generate

java version "1.6.0_13"
Java(TM) SE Runtime Environment (build 1.6.0_13-b03)
Java HotSpot(TM) Server VM (build 11.3-b02, mixed mode)

in terminal, run:

echo $JAVA_HOME

it will generate no result. $JAVA_HOME is an environmental variable that you configure during your JDK installation. Since neither java -version or echo $JAVA_HOME mention JDK,  we can confirm that you do not have JDK installed


Step 2: Download Java Development Kit 5.xx

Download the binary file :

http://download.oracle.com/otn/java/jdk/1.5.0_22/jdk-1_5_0_22-linux-i586-rpm.bin

You need to make this file executable. This can be done by changing the permissions.

In terminal, run:

chmod a+x jdk-1_5_0_22-linux-i586-rpm.bin

Step 3:  Create appropriate folders

We need to move the file to the folder that OBIEE utilizes during install - /usr/local/java. This folder doesn't exist by default so you will have to create it.

In terminal, run:

mkdir /usr/local/java/

cd /usr/local/java/ 

Note that your account may not have privileges to create a new folder in a system-wide directory, so you may need your sys admin to do this for you.

Now you need to move the  jdk-1_5_0_22-linux-i586-rpm.bin file you downloaded into the /usr/local/java folder.

In terminal, run:

mv /desktop/jdk-1_5_0_22-linux-i586-rpm.bin ./

The above statement assumes you downloaded the file to your /usr/home/username/Desktop folder.


Step 4: Install JDK 5.xx

Now that you've moved the file to the /usr/local/java/ folder, we can begin the install:

In terminal, run:

./jdk-1_5_0_22-linux-i586-rpm.bin

If successful, you will see the following:

Do you agree to the above license terms? [yes or no]
yes
Unpacking...
Checksumming...
0
0
Extracting...
  inflating: jdk-1_5_0_22-linux-i586.rpm
Preparing...                ########################################### [100%]
   1:jdk                    ########################################### [100%]

Done.

Step 5: Create an account with read/write privilege to /usr/local/java

If you've been paying attention, you'll notice that all of the above commands had to be executed by either 'root' or a super user. You cannot install OBI as root, nor should you attempt to run a server as root due to security concerns.

The appropriate solution is to create a new user with access to the folders needed for installation.

We are now going to create a new user which we will use for the rest of the installation.

Have your admin/root account run the following commands in terminal:

groupadd oinstall #create a group called oinstall

/usr/sbin/useradd obi -g oinstall  #create a user called obi, with membership to group oinstall

passwd obi #create a new password for user obi

Now give user obi read/write access to the /usr/local/java folder. This is necessary because during the start of BI presentation services, log files will be created and .jar files will be executed.

In terminal, run the following command:

chown -R obi:oinstall /usr/local/java

chmod -R 777 /usr/local/java

cd /usr/local

ls -l

Command ls -l should generate:

drwxrwxr-x  3 obi  oinstall 4096 May 23 13:42 java

User obi now has read/write access to /usr/local/java

Step 6: Update the bash profile for user obi

 Remember in step run, when we ran echo $JAVA_HOME , no results were returned? This is not ok because $JAVA_HOME is an environmental variable that OBIEE server uses during its install. We need to create this variable prior to installing OBIEE Server.

As I stated earlier, OBIEE Server cannot be installed on root. We created a user obi which we will utilize during installation. So we need to modify the bash profile for user obi.

Log out of your current user, and into user obi.

In your bash shell terminal, run the following command:

vi ~/.bash_profile

and modify it to include:

# Java Home
JAVA_HOME=/usr/java/jdk1.5.0_22
export JAVA_HOME

I assume you have a basic understanding of how to use vi, if not review http://www.washington.edu/computing/unix/vi.html prior to attempting this.

After you save your changes, restart your machine , log back into obi and in terminal , run:

echo $JAVA_HOME

you should get the following output:

/usr/local/java/jdk1.5.0_22 

--

You now have:

1) JDK installed

2) a user called obi

3) user obi has access to /usr/local/java folder
4) user obi has an environmental variable called JAVA_HOME

Notice how we haven't even downloaded or installed OBIEE yet. This is fundamental pre-work that must be done. Take the time to understand this and do not proceed until echo $JAVA_HOME is populated and obi user has read/write access to /usr/local/java.

The next post will cover the installation and configuration of OBIEE 10g Server

FYI: OOTB HR Analytics Workforce Fact table Informatica Workflow (Peoplesoft HCM 9.1)

w_wrkfc_evt_month_f - just mentioning it to OBIEE practitioners send shivers down their back.

Why? This behemoth of a fact table is the fact table of OBIA HR Analytics. It is used across all OBIA HR subject areas for cross functional analysis. It has 12 logical aliases in the BMM and it exists in all 3 of Kimball's fact table types: Transactional, period, and accumulating.

There is no other OBIA fact table that spans as many subject areas, reports, and KPIs as w_wrkfc_evt_month_f.

Which brings me to the following point.  

What if the business requirement results in modifications to this fact table?

Proceed with caution. Can the business process you're trying to measure be created using another fact table? Would it be less work to create a new star schema than to modify the workforce event tables?


Oracle provides little documentation on this fact table in terms of impacted Informatica work flows and mappings.  Any modifications to the w_wrkfc_evt_month_f fact table or its cousins ( e.g. _agg), will require your ETL developer to use the existing mappings.

Here is how  w_wrkfc_evt_month_f is populated for OOTB OBIA HR 7.9.6.3 with PSFT HCM 9.1 as the source:

Step 1: Temp table

Informatica Folder: (SDE_PSFT_91_Adaptor)

W_PSFT_WEVT_JOB_PS

1. SDE_PSFT_PersistedStage_WorkforceEvent_Job_Full
2. SDE_PSFT_PersistedStage_WorkforceEvent_Job
3. SDE_PSFT_PersistedStage_WorkforceEvent_Job_Full (Mapping)
4. SDE_PSFT_PersistedStage_WorkforceEvent_Job_Mntn (Mapping)

Step 2: Staging table

W_WRKFC_EVT_FS

1. SDE_PSFT_WorkforceEventFact_Job
2. SDE_PSFT_WorkforceEventFact_Job_Full
3. SDE_PSFT_WorkforceEventFact_Job (Mapping)

Step 3: Fact table (does not exist in Metadata layer, only used for ETL PLP)

Informatica folder: (SILOS)

 

W_WRKFT_EVT_F

1. SIL_WorkforceEventFact
2. SIL_WorkforceEventFact (Mapping)

Step 4:  Fact table PLP 2

W_WRKFC_EVT_MERGE_F

1. PLP_WorkforceEventFact_Merge
2. PLP_WorkforceEventFact_Merge_Full
3. PLP_WorkforceEventFact_Merge (Mapping)
4. PLP_WorkforceEventFact_Merge_Mntn (Mapping)

 

Step 5:  Metadata/RPD fact table
 

W_WRKFC_EVT_MONTH_F

1. PLP_WorkforceEventFact_Month_PositionHierarchy_Update_TD
2. PLP_WorkforceEventFact_Month_PositionHierarchy_Update
3. PLP_WorkforceEventFact_Month_Full
4. PLP_WorkforceEventFact_Month
5. PLP_WorkforceEventFact_Month_PositionHierarchy_Update_TD (Mapping)
6. PLP_WorkforceEventFact_Month_PositionHierarchy_Update (Mapping)
7. PLP_WorkforceEventFact_Month_Mntn (Mapping)
8. PLP_WorkforceEventFact_Month (Mapping)

For those of you following along, that's right, the data has the possibility of flowing through 21 different mappings! You must consider this before you attempt to modify this fact table. 

Unless absolutely critical, I recommend creating a new star schema.

How to: First Day of Last Month & the various combinations

A common requirement is to have default date values in a prompt - see below:

1) It helps limit the data set, and 2) it is a way to tell the end user the format of the input values.

But most of the time, the user will want the values to change as time progresses, i.e. 'default to last month's date range'. In this case, a static variable will not due.

How do you create default dates in the prompt that change as time progresses?

Step 1:

Identify what the default values should be. In the above example, the start date is 'First day of last month' and the end date is 'last day of last month', assuming the current month is May 2012.

First day of last month is created as:

TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

Last day of last month is created as:

 TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)) 

Step 2:

The issue is that you cannot add the above 2 functions to a prompt because it is not in a recognized format, it must be in one of the following formats:

 Step 3:

We can enclose the above 2 functions in a logical SQL format that Answers will understand ,as follows  :

SELECT

case when 1=0 then Time."Fiscal Date" else  INSERT FUNCTION HERE

from

'Your-logical-subject area'

END
 

 We must do a case 1=0 operation to force the sql code to always default to the timestampadd function

 Step 4:

Using the above logic:

first day of last month:

SELECT

case when 1=0 then Time."Fiscal Date" else TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)) end

FROM "Subject area - Subject area name"

last day of last month:

SELECT

case when 1=0 then Time."Fiscal Date" else TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)) 

 end 

FROM "Subject area - Subject area name"

Step 5:

Paste the above into your prompt with default values as 'SQL Results':

 

 Step 6:

And make sure to cast your prompt value as Date - it defaults to datetime.

 

EXTRA Credit:


What if you have 20 prompts that have this requirement?

Answer: Create 2 dynamic variables in the rpd with the following logic:

First day of last month:

select Last_Day(ADD_MONTHS(SYSDATE,-2))+1 FROM DUAL

Last day of last month:

select Last_Day(ADD_MONTHS(SYSDATE,-1))  FROM DUAL

Note how Oracle does NOT have a first_day function :(


Need other day combinations? First day of last quarter? Last day of next month? First day of last year? 

Use the logic below:

First Day of the Previous Year

TIMESTAMPADD( SQL_TSI_YEAR , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)) 

From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD removes a year from the returned date for the First Day of the Previous Year.

First Day of the Current Year 
TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE) 
This calculation returns the first day of the year by deducting one less than the total number of days in the year.
 
First Day of the Next Year 
TIMESTAMPADD( SQL_TSI_YEAR , 1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

 From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD adds a year to the date returned which will give the first day of the next year.
 
First Day of the Previous Month 
TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)) 

From right to left the first TIMESTAMPADD returns the first day of the Current Month. The second TIMESTAMPADD then subtracts one month from the first day of the Current Month arriving to the First Day of the previous month.

First Day of the Current Month

TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)

 This expression gets the current day of the month and subtracts one less than the current day to arrive at the first day of the month.
 
First Day of the Next Month 
TIMESTAMPADD(SQL_TSI_MONTH, 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)) 

From right to left the first TIMESTAMPADD returns the first day of the Current Month. The second TIMESTAMPADD then adds one month from the first day of the Current Month arriving to the First Day of the next month.
 
First Day of Current Quarter 
TIMESTAMPADD( SQL_TSI_DAY , DAY_OF_QUARTER( CURRENT_DATE) * -(1) + 1, CURRENT_DATE) 

This was included to show the calculations discussed above can be used with other functions. This is the same expression as the one that returns the first day of the current month except this one uses the DAY_OF_QUARTER property to return the first day of the current quarter.
 
Last Day of the Previous Month 
TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)) 

From right to left the first TIMESTAMPADD returns the first day of the Current Month. The second TIMESTAMPADD subtracts a month to arrive at the first day of the previous month.
 
Last Day of Current Month 

TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))) 

From right to left the first TIMESTAMPADD finds the first day of the current Month. The second TIMESTAMPADD adds one month to the date to arrive at the first day of the next month. The final TIMESTAMPADD subtracts one day from the returned date to arrive at the last day of the Current Month.
 
Last Day of the Next Month 
TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 2, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))

 From right to left the first TIMESTAMPADD finds the first day of the current Month. The second TIMESTAMPADD adds two months to the date to arrive at the first day of month after next. The final TIMESTAMPADD subtracts one day from the returned date to arrive at the last day of the Next Month.
 
Last Day of Previous Year 

TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1,
CURRENT_DATE)) 

From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD subtracts one day to arrive at December 31st of the previous year.
 
Last Day of Current Year 
TIMESTAMPADD(SQL_TSI_YEAR, 1, TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE))) From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD deducts one day to arrive at December 31 of the previous year. The third TIMESTAMPADD adds a single year to the date to arrive at December 31 of the Current Year.
 
Last Day of the Next Year 
TIMESTAMPADD(SQL_TSI_YEAR, 2, TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))

 From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD deducts one day to arrive at December 31 of the previous year. The third TIMESTAMPADD adds 2 years to the date to arrive at December 31 of the Next Year.

Last Day of Current Quarter

TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_QUARTER , 1, TIMESTAMPADD( SQL_TSI_DAY , DAY_OF_QUARTER( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))) 

Demonstrated using Quarters. From right to left the first TIMESTAMPADD returns the first day of the Current Quarter. The second TIMESTAMPADD returns the first day of the next quarter. The final TIMESTAMPADD subtracts a single day from the date to arrive at the last day of the Current Quarter.
 
Number of days between First Day of Year and Last Day of Current Month

 TIMESTAMPDIFF(SQL_TSI_DAY, CAST('2010/01/01 00:00:00' AS DATE), TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))) 

For simplicity I hard coded the January 1, 2010 date and CAST it to a date. I could have used the First Day of the Current Year calculation but didn’t want to over clutter the example. The second part of the TIMESTAMPDIFF uses Last Day of the Current Month calculation to force the TIMESTAMPDIFF to calculate the number of days between the first day of the year and the last day of the current month. 

keywords: obiee 10g, answers, prompt, presentation variables, first day last month combinations, repository variables,