OBIEE Software Only Installation Step-by-step Part-2

Part 2

Ok, now we have to create and configure the listener.

The listener is a separate process that runs on the database server computer. It receives incoming client connection requests and manages the traffic of these requests to the database server. This chapter describes how to configure the listener to accept client connections. http://docs.oracle.com/cd/B10501_01/network.920/a96580/listener.htm

[oracle@localhost /]$ netca 

Done!

Now we will create a database instance.

Database Configuration Assistant (DBCA) is the preferred way to create a database, because it is a more automated approach, and your database is ready to use when DBCA completes. DBCA can be launched by the Oracle Universal Installer (OUI), depending upon the type of install that you select. You can also launch DBCA as a standalone tool at any time after Oracle Database installation. http://docs.oracle.com/cd/B28359_01/server.111/b28310/create002.htm#ADMIN12479

[oracle@localhost /]$ dbca

Ok, let's test the database.
[oracle@obiee ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu May 28 17:35:57 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production


SQL>

Nice job!

Now we have a database, next step is create the OBIEE required repositories.

Many of the Oracle Fusion Middleware components require the existence of schemas in a database prior to installation. These schemas are created and loaded in your database using the Repository Creation Utility (RCU). http://docs.oracle.com/cd/E21764_01/doc.1111/e14259/overview.htm#RCUUG291

Unzip rcu 11.1.1.9 and execute it.

[oracle@obiee bin]$ ./rcu

Ok, now we finished the Part 2 of the tutorial.

The next step is create the weblogic server, OBIEE in software only mode and create the domain.

[]'s

Igor Gentil

OBIEE Software Only Installation Step-by-step Part-1

Part 1

Hey There!

Today I'll post a complete installation process of Oracle Business Intelligence Enterprise Edition version 11.1.1.9. Not just OBIEE, but the operational system OS, java, environment variables, repositories, weblogic server and OBIEE.

It will be a lot of information for this installation. So, download all required files for this tutorial.

The operational system will be Oracle Linux x64, therefore all files must be linux and x64 architecture.

all installations will be held in Oracle Virtual Box.

Oracle Virtual Box
Oracle Linux 5.7 x64
Java JDK 1.6.0_45  x64
Oracle Database 11gR2 x64
Repository Creation Utility 11.1.1.9 x64
OBIEE 11.1.1.9 x64
Weblogic 10.3.6 x64 generic


So here we go!

With all files in hands, install Oracle Virtual Box, for install the OBIEE, I recomend at least 2 processors, 5 gb RAM and 150 gb harddrive space for the virtual machine. I'll use Host-only for network.

Oracle Linux Installation

1.1   - Create the virtual machine and mount Oracle Linux image. Press enter to start the installation.
1.2   - Skip disk test.
1.3   - Next
1.4   - Select your language, next.
1.5   - Select your keyboard type, next.
1.6   - Yes to clear the disk and start the installation.
1.7   - Next
1.8   - Yes
1.9   - Next (we will configure the network after)
1.10 - Select your Region, next.
1.11 - Type and confirm a root password. For this tutotial i will use "welcome1" for all passwords, next.
1.12 - Select these options:

This option will install all required parameters, user, groups etc for Oracle Database 11gR2.

1.13 - Next. The installation will start.
1.14 - Reboot
1.15 - Foward
1.16 - Agree the licence Agreement, foward.
1.17 - Set the firewall to Disabled, foward, yes.
1.18 - Set SELinux to Disabled, foward, yes.
1.19 - Foward
1.20 - Adjust date and time, foward.
1.21 - Foward, continue.
1.22 - Foward.
1.23 - Finish, ok for reboot.

Oracle Enterprise Linux created! Let's login and start the system configuration.
User: root
Password: welcome1

IMPORTANT!!!!

THIS IS NOT AN OFFICIAL INSTALLATION, PLEASE SEE ORACLE DOCS FOR PRODUCTION INSTALL

Operational System Configuration.

First of all, lets change oracle user password, so do this:

[root@localhost ~]# passwd oracle

I will use oracle/welcome1 as username and password.

Update /etc/hosts file

[root@localhost ~]# vi /etc/hosts

# Do not remove the following line, or various programs

# that require network functionality will fail.

127.0.0.1             localhost.localdomain      localhost

192.168.56.101   obiee.hostonly.domain     obiee

~

~

Create the application directory and grant privileges:

[root@localhost /]# mkdir -p /u01/app

[root@localhost /]# chown -R oracle:oinstall /u01/app

[root@localhost /]# chmod -R 775 /u01/app

Install and Configure Java JDK 1.6.0_45

[root@localhost ~]# rpm -ivh /u01/download/jdk-6u45-linux-amd64.rpm

[root@localhost ~]# alternatives --install /usr/bin/java java /usr/java/jdk1.6.0_45/bin/java 17000

[root@localhost ~]# /usr/sbin/alternatives --config java

There are 2 programs which provide Java

  Selection    Command

-----------------------------------------------

 + 1           /usr/lib/jvm/jre-1.4.2-gcj/bin/java

*  2           /usr/java/jdk1.6.0_45/bin/java

Enter to keep the current selection[+], or type selection number: 2

Press Enter. Verifies if Java are correct.

[root@localhost ~]# java -version

java version "1.6.0_45"

Java(TM) SE Runtime Environment (build 1.6.0_45-b06)

Java HotSpot(TM) 64-Bit Server VM (build 20.45-b01, mixed mode)

Now we are done with root user. Change to oracle user and let's proceed.

Config the bash_profile file to set all required system variables for oracle database installation.

[oracle@localhost ~]$ vi ~/.bash_profile

#add the folloing

TMP=/tmp; export TMP^M

TMPDIR=$TMP; export TMPDIR^M

ORACLE_HOSTNAME=obiee; export ORACLE_HOSTNAME ^M

ORACLE_UNQNAME=orcl; export ORACLE_UNQNAME ^M

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE^M

ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME^M

ORACLE_SID=orcl; export ORACLE_SID^M

ORACLE_TERM=xterm; export ORACLE_TERM^M

PATH=/usr/sbin:$PATH; export PATH^M

PATH=$ORACLE_HOME/bin:$PATH; export PATH^M

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH^M

CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

All clear... proceed to the oracle database installation!!

Oracle 11gR2 Database Install

Unzip the files

[oracle@localhost database]$ unzip V17530-01_1of2.zip

[oracle@localhost database]$ unzip V17530-01_2of2.zip

[oracle@localhost database]$ cd database/

[oracle@localhost database]$ ./runInstaller

In Part 2, I'll configure the listener and create one database.

Please Comment!

[] 'S

Time Dimension - Oracle

Time Dimension

Quite simple!

I will create a Time Dimension. It is sometimes used when the client wants to have this grain.

Sometimes, I saw my colleagues put this information on the same date dimension. This seems wrong, very wrong, you will populate a dimension quite large, it will consume a lot of disk space and CPU processing when creating analysis.

Let's calculate an example:

One day has 60 seconds, 60 minutes and 24 hours.

Then, 60 * 60 * 24 = 86400.

If you have two years of data to use in your dimension, you will have:

86400 * 365 * 2 = 6307200.

Wow! is a lot of data!

But if we divide into two dimensions, we will have a cleaner model and practical to create our analysis.

I'll use Oracle 11gR2 for this table.

Let's create our table:

DROP TABLE DIM_TIME;

CREATE TABLE DIM_TIME

AS

SELECT 

   n AS SK_TIME,

   TO_NUMBER(TO_CHAR(to_date(n,'SSSSS'),'HH24')) AS HOUR,

   TO_NUMBER(TO_CHAR(to_date(n,'SSSSS'),'MI'))   AS MINUTE,

   TO_NUMBER(TO_CHAR(to_date(n,'SSSSS'),'SS'))   AS SECOND,

   TO_CHAR(to_date(n,'SSSSS'),'HH24') || ':' || TO_CHAR(to_date(n,'SSSSS'),'MI') || ':' || TO_CHAR(to_date(n,'SSSSS'),'SS') AS TIME,

   CASE WHEN n BETWEEN 0 AND 21599 THEN 'DAWN'

        WHEN n BETWEEN 21600 AND 43199 THEN 'MORNING'

        WHEN n BETWEEN 43200 AND 64799 THEN 'AFTERNOON'

        ELSE 'NIGHT'

   END AS PERIOD,

   SYSDATE AS LOAD_DATE

FROM (

   SELECT

      level-1 n

   FROM 

      DUAL

   CONNECT BY LEVEL <= 86400

);

Guithub file:
https://github.com/igorgentil/PLSQL/blob/master/SCRIPT_DIM_TIME.sql

Much better!

Enjoy!

Date Dimension - Oracle

In this post, I will create a script that I use quite on multiple clients, and is used in all Data Wharehouse that I developed, it is the Date Dimension.

"A data warehouse is a subject-oriented, integrated, time-variant and non-volatile collection of data in support of management's decision making process." (INMON, 1990)

Therefore, DW is time-variant, then the time is a constant in our projects. Nothing better than having a script in hand that will help you a lot in creating your models.

The scripts were created using Oracle 11gR2 database.

Let's start!

DROP TABLE DIM_TIME_DATE;
CREATE TABLE "DIM_TIME_DATE" 
(
"SK_DATE" INTEGER, 
"DATE" DATE, 
"NR_MONTH" INTEGER, 
"DS_MONTH" VARCHAR2(36 BYTE), 
"AB_MONTH" CHAR(3 BYTE), 
"ID_MONTH" INTEGER, 
"NR_QUARTER" INTEGER, 
"DS_QUARTER" CHAR(2 BYTE), 
"ID_QUARTER" INTEGER, 
"DS_YEAR" CHAR(4 BYTE),
"ID_YEAR" INTEGER,
"LOAD_DATE" DATE
);

With our table created, we will insert the data into it.

DECLARE
V_START DATE := TO_DATE('01/01/1950','DD/MM/YYYY'); --ALTER HERE YOUR START DATE
V_END   DATE := TO_DATE('31/12/2015','DD/MM/YYYY'); --ALTER HERE YOUR END DATE

BEGIN

EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT =   ''DD/MM/YYYY'''; -- ALTER HERE YOUR DATE MASK
--EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_LANGUAGE = ''BRAZILIAN PORTUGUESE'''; -- ALTER HERE YOUR REGION LANGUAGE
EXECUTE IMMEDIATE 'TRUNCATE TABLE DIM_TIME_DATA';

WHILE V_START <= V_END 
LOOP
INSERT INTO DIM_TIME_DATE
VALUES 
(
  --SK_DATE
  TO_NUMBER(TO_CHAR(V_START,'YYYYQMMDD')), 
  --DATE
  TO_DATE(V_START,'DD/MM/YYYY'),
  --NR_MONTH
  TO_NUMBER(TO_CHAR(V_START,'MM')),
  --DS_MONTH
  TO_CHAR(V_START,'Month'),
  --AB_MONTH
  TO_CHAR(V_START,'MON'),
  --ID_MONTH
  TO_NUMBER(TO_CHAR(V_START,'YYYY') || TO_CHAR(V_START,'Q') || TO_CHAR(V_START,'MM')),
  --NR_QUARTER
  TO_NUMBER(TO_CHAR(V_START,'Q')),
  --DS_QUARTER
  'T'||TO_CHAR(V_START,'Q'),
  --ID_QUARTER
  TO_NUMBER(TO_CHAR(V_START,'YYYY') || TO_CHAR(V_START,'Q')),
  --DS_YEAR
  TO_CHAR(V_START,'YYYY'),
  --ID_YEAR
  TO_NUMBER(TO_CHAR(V_START,'YYYY')),
  --LOAD_DATE
  TO_DATE(SYSDATE,'DD/MM/YYYY')
);
V_START := V_START+1;
END LOOP;
COMMIT;
END;
/

Guithub file:
https://github.com/igorgentil/PLSQL/blob/master/SCRIPT_DIM_DATE.sql

Our dimension is created, populated and ready to use.

We can expand it more, there are several time objects that we can explore, such as week number of year, weekday, weekend, at last, we can create a really large big dimension and caters to various management issues. But as I explained, this dimension will meet most of your projects.

Soon I'll post a script to create a timetable dimension. It's that simple.

Until next.