Time Dimension Table Design for Data Warehouse Project withh Sample Data

When we are working with Business Intelligence Projects, It is mandatory to have Time dimension to create lot of measures like Year Ago, Month Ago, Week Ago, Date Ago or Year To Date, MTD, YTD , WTD and much more.

Designing or Creating a Time dimension table with data would be easier task. Here I am making a simpler way to create a Time Dimension which would fit for any requirement with all the relevant columns.

Step1: Create a Time Dimension Table with the below DDL Query

# WC_TIME_D: /* DDL for the date dimension */

*********************************************

CREATE TABLE WC_TIME_D (

DATE_KEY SMALLINT NOT NULL,

FULL_DATE DATE,

DAY_OF_WEEK SMALLINT,

DAY_NUM_IN_MONTH SMALLINT,

DAY_NUM_OVERALL SMALLINT,

DAY_NAME VARCHAR(9),

DAY_ABBREV CHAR(3),

WEEKDAY_FLAG CHAR(1),

WEEK_NUM_IN_YEAR SMALLINT,

WEEK_NUM_OVERALL SMALLINT,

WEEK_BEGIN_DATE DATE,

WEEK_BEGIN_DATE_KEY SMALLINT,

MONTH SMALLINT,

MONTH_NUM_OVERALL SMALLINT,

MONTH_NAME VARCHAR(9),

MONTH_ABBREV CHAR(3),

QUARTER SMALLINT,

YEAR SMALLINT,

YEARMO INT,

FISCAL_MONTH SMALLINT,

FISCAL_QUARTER SMALLINT,

FISCAL_YEAR SMALLINT,

LAST_DAY_IN_MONTH_FLAG CHAR(1),

SAME_DAY_YEAR_AGO_DATE DATE,

PRIMARY KEY (DATE_KEY));

 Step2:Download the excel Sheet which has the prefilled with data of up to Year 2025.

Step3: Generating the Time Data for Years we want

To Get the the value up to the year you want, Just Open the downloaded excel sheet and select the last row of the first sheet and drag it down. It will automatically fill the the value with the prebuilt formula.

Step 4: Inserting Data into Table - There are 2 ways

Way 1: After we have generated the data,  just Copy and "Paste as values" in another sheet and insert from Excel directly to Oracle Database

Way 2: There is insert statement generated in the first sheet (Z column). Just drag up to the value you have created to create insert statement for all rows and then copy paste the query in SQL developer or put as an .sql file and run in DB to load.

Note:

If you face the below error while running insert statement then execute the alter statement and continue the insertion;

SQL Error: ORA-01861: literal does not match format string 01861

Solution:

ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD'

Do not forget to commit after insert completes.

Cheers!

Bose

OBIEE 11.1.1.9 Released and available for download

Hi All,

OBIEE 11.1.1.9 is released and available for download. Here is the link you can download the software.

http://www.oracle.com/technetwork/middleware/bi-enterprise-edition/downloads/bus-intelligence-11g-165436.html

OBIEE 11.1.1.9: New Features for Front-End Developers and Business Users

http://www.rittmanmead.com/2015/05/obiee-11-1-1-9-new-features-for-front-end-developers-and-business-users/

OBIEE 11.1.1.9: New Features for Systems Administrators and Developers

http://www.rittmanmead.com/2015/05/whats-new-in-obiee-11-1-1-9-for-systems-administrators-and-developers/

Will explore more in coming days.

Till then Cheers!

Bose

OBIA 11.1.1.7.1 upgrade Guide to 11.1.1.8.1

Hi,

Here is the OBIA Upgrade Guide

Cheers!
Bose

OBIA 11.1.1.8.1 Documentation and Product Guide

Hi Folks,

You can find the most important OBIA 11.1.1.8.1 documentation at the following locations :
Oracle Business Intelligence Applications (OBIA) Release 11.1.1.8.1 Documentation Library 
Major topics :

• Install Guide
• Configuration Guide
• ETL Guide
• Administrator’s Guide
• Security Guide
• Release Notes

OBIA 11g: Data Lineage Documentation for Oracle Business Intelligence Applications 11.1.1.8.1

BI Applications 11.1.1.8.1 Data Model Reference Guide
Major Topics :
The information provided in this document includes data modeling concepts, nomenclature, table and column descriptions, and star schema diagrams. This information is useful for understanding the functional areas that are mapped in the Oracle Business Analytics Warehouse.
BI Applications 11.1.1.8.1 Content Guide
Content Guide includes following topics for BIApps 11.1.1.8.1:
Section 1, “Oracle Business Intelligence Applications – Licenses and Products
Provides a list of all Products within Oracle Fusion BI Applications
Section 2, “Oracle Business Intelligence Applications – Dashboard
Provides a list of dashboards for each application along with their paths
Section 3, “Oracle Business Intelligence Applications – Dashboards and Reports
Provides a list of dashboards and reports for each application along with their exact paths
Section 4, “Oracle Business Intelligence Applications – All Reports
Provides a complete list of reports including those not exposed in any dashboard page
Section 5, “Oracle Business Intelligence Applications – Subject Areas
Provides a high level overview of the subject areas included in each application
Section 6, “Oracle Business Intelligence Applications – Facts & Dims by Application
Provides a list of fact and dimension relationships for each presentation catalog. The user can filter on a particular dimension table and find all its fact tables across presentation catalogs and vice versa
Section 7, “Oracle Business Intelligence Applications – Webcatalog Function Security
Provides a list of report name, path, associated users, duty/roles and permissions

Cheers! 
Bose

How to Start and Stop OBIEE 11g in Linux

Create boot.properties:

Create a "boot.properties" file for each of the servers, so we don't need to manually enter credentials on startup/shutdown. This only has to be done once.

# AdminServer

echo "username=weblogic" > $DOMAIN_HOME/servers/AdminServer/security/boot.properties

echo "password=Admin123" >> $DOMAIN_HOME/servers/AdminServer/security/boot.properties

# bi_server1

cp $DOMAIN_HOME/servers/AdminServer/security/boot.properties $DOMAIN_HOME/servers/bi_server1/security/boot.properties

 starting OBIEE 11g Services:

 Step 1 of 4: Starting Admin Server

Linux Command Terminal
$ nohup $DOMAIN_HOME/bin/startWebLogic.sh & $ tail -f nohup.out

After issueing the start command we tail the log until we see the message below, indicating that the server has started successfully.
<WebLogicServer> <BEA-000360> <Server started in RUNNING mode>

Step 2 of 4: Start NodeManager
There should be a single Node Manager for the machine.  Start it up with the command below.

Linux Command Line
$ nohup $MW_HOME/wlserver_10.3/server/bin/startNodeManager.sh > NOD.out & $ $ tail -f NOD.out

After issuing the start command we tail the log until we see the message below, indicating that the server has started successfully.
<Info> <Secure socket listener started on port ####>

Step 3 of 4: Start ManagedWeblogicServer

By default there should be one managed server, bi_server1 that we will start up.  At this point you should start  any other Managed Servers in this Weblogic domain.

Linux Command Line
$ nohup $DOMAIN_HOME/bin/startManagedWebLogic.sh bi_server1 & $ tail -f nohup.out

After issueing the start command we tail the log until we see the message below, indicating that the server has started successfully.
Server started in RUNNING mode

Step 4 of 4: Startup OPMN Components
Oracle Process Manager should be used to shutdown all system components.

Linux Command Line
$ nohup $MW_HOME/instances/instance1/bin/opmnctl startall #To check all the services run the below status command $ nohup $MW_HOME/instances/instance1/bin/opmnctl status $

Stopping OBIEE 11g services:

It is exactly in the opposite process of the starting the services. Follow below steps:

Step 1 of 4: Stop all the OPMN Components

Oracle Process Manager should be used to shutdown all system components.

Linux Command Line
$ $MW_HOME/instances/instance1/bin/opmnctl stopall

Step 2 of 4: Shutdown Managed Server
By default there should be one managed server, bi_server1 that we will shut down.  At the point you should shut down any other Managed Servers in this WebLogic domain.

Linux Command Line
$DOMAIN_HOME/bin/stopManagedWebLogic.sh bi_server1

Verify that the Managed Server has shut down completely with the following commands

$ ps -ef | grep bi_server1.

Step 3 of 4: Shutdown Node Manager

There should be a single Node Manager for the machine.  Shut it down with the command below.

Linux Command Line
$ $MW_HOME/wlserver_10.3/server/bin/stopNodeManager.sh # Find the process of nodemanager with the below command and kill it $ ps -ef | grep Node | grep nodemanager | grep -v grep | cut -c10-15 $ kill -9 PID

Step 4 of 4: Shutdown Admin Server
There should be a single Node Manager for the machine.  Shut it down with the command below.

Linux Command Line
$ $DOMAIN_HOME/bin/stopWebLogic.sh

Verify that the Managed Server has shutdown completely with ps -ef | grep AdminServer.

Cheers!

Bose

How to refresh GUIDs for OBIEE 11g ?

Purpose

The purpose of this document is to provide clear steps for refreshing GUIDs in OBIEE 11g. The document also shows what to expect in the logs to confirm GUID refresh has completed successfully.

Details

1) Stop all obiee services (opmnctl stopall) 

2) Edit presentation server instanceconfig.xml with the following - put it inside Catalog tag
<UpdateAccountGUIDs>UpdateAndExit</UpdateAccountGUIDs> 

3) Edit obiee server NQSconfig.INI file with the following
FMW_UPDATE_ROLE_AND_USER_REF_GUIDS = YES; 

4) Start OBIEE services (opmnctl startall) 

5) Wait until presentation services are listed as down (opmnctl status) 

6) stop obiee services (opmnctl stopall) 

The saw log should show the following:
-----------------
Saw.log
[2012-12-19T09:38:03.000-07:00] [OBIPS] [NOTIFICATION:1] [] [saw.subsystem.catalog.initialize.upgrade] [ecid: ] [tid: ] Starting to update Account GUIDs[[
File:initializecatalog.cpp
Line:1046
Location:
   saw.subsystem.catalog.initialize.upgrade
   saw.subsystem.catalog.initialize
   saw.subsystems.catalogbootstrapper.loadcatalog
   saw.webextensionbase.init
   saw.sawserver
ecid:
]]
[2012-12-19T09:38:07.000-07:00] [OBIPS] [NOTIFICATION:1] [] [saw.subsystem.catalog.initialize.upgrade] [ecid: ] [tid: ] Succeeded in updating account GUIDs from back end user population store[[
File:initializecatalog.cpp
Line:1131
Location:
   saw.subsystem.catalog.initialize.upgrade
   saw.subsystem.catalog.initialize
   saw.subsystems.catalogbootstrapper.loadcatalog
   saw.webextensionbase.init
   saw.sawserver
ecid:
]]

7) Edit presentation server instanceconfig.xml with the following
<UpdateAccountGUIDs>none</UpdateAccountGUIDs> 

8) Edit obiee server NQSconfig.INI file with the following
FMW_UPDATE_ROLE_AND_USER_REF_GUIDS = NO; 

9) Start OBIEE services (opmnctl startall) 

10) Verify OBIEE services are up (opmnctl status)

Reference:

http://docs.oracle.com/cd/E14571_01/bi.1111/e10543/privileges.htm#CHDHGAAH

Cheers!

Bose

ADF_FACES-60097 : For more information, please see the server's error log for an entry beginning with: ADF_FACES-60096: Server Exception during PPR, #1

Occurs:

When I was trying to upload the latest repository (.rpd) in em, I couldn't save the changes and stopped by this error. So here is the steps what I followed to overcome this issue. Read below to know about the issue and resolve.

 

Applies to:

Business Intelligence Server Enterprise Edition - Version 11.1.1.5.0  and later
Information in this document applies to any platform.

Symptoms:

When selecting the 'Coreapplication' within the Oracle Business Intelligence (OBIEE) folder (under the Server farm) in Enterprise Manager the following error is generated:

ADF_FACES-60097 : For more information, please see the server's error log for an entry beginning with: ADF_FACES-60096: Server Exception during PPR, #1

Cause:

Bug:10372673 - QA: HOT: UNABLE TO ACCESS EM > COREAPPLICATION - INCIDENT ERROR REPORTED

The diagnostics.dmp shows the following:

[ServletContext@122564910[app:em module:/em path:/em spec-version:2.5]] Root cause of ServletException.
java.io.IOException: Stream closed
at java.io.BufferedInputStream.getInIfOpen(BufferedInputStream.java:134)
atjava.io.BufferedInputStream.fill(BufferedInputStream.java:218)

etc....
 

Solution:

1. stop all services, including WebLogic

2.Delete the instance.jspx.xml file in folder 'FMWhome\user_projects\domains\bifoundation_domain\sysman\mds\partition1\ai\bi\mdssys\cust\user\USER_NAME'

3.  Restart the services,  The instance.jspx.xml file will be recreated.

Note:

The USER_NAME refers to the userid in your specific system. For example, weblogic or biadmin.
This solution is also documented in the following document:
<Doc ID 1303644.1> OBIEE Enterprise Manager Error: Stream Closed Server Exception during PPR, #13

Please ensure that a backup of the environment is taken beforehand.

References

NOTE:1303644.1 - OBIEE 11g: Error: "Stream Closed" Server Exception in Enterprise Manager during PPR, #13

Tip:

If you have used the username like 'Weblogic', 'WEBLOGIC', the corresponding username folder will be created under that folder. so we could simply login into em and we could restart the services!

Cheers!

Bose