Creating an OBIEE repository(initial setup)

This post is intended to those who are starting with OBIEE and may get stuck in some of the initial steps or may be a quick guideline for those with more experience with the oracle technology.

In order to create a repository and demonstrate the first few steps of the initial configuration of OBIEE 11g repository, you can use the Oracle BISAMPLE schema or the popular HR schema as your data source. The HR schema is already created when you install the Oracle database but it is in a 3-NF, it is not a dimensional data structure typically used in DW, then ideally you should use BISAMPLE which is in a dimensional form (optimal form for OBIEE) but you will need to install the BISAMPLE schema. In this post I will demonstrate in small details how to get the OBIEE sample installed and with the right privileges, since oracle by example does not show how to set up that schema, especially if you are doing it for the first time I guess it would be a good idea to share the basic steps.

To install it, you will need to download the zip file from here : http://www.oracle.com/technetwork/middleware/bi-foundation/obiee-samples-2216383.html

To unzip and check the consistency of the file, I suggest you use the 7 zip program:   http://www.7-zip.org/

Start it up and point it to the zipped file in order to unzip and run all the tests:

Select the file and test it:

Go to file menu select “7-Zip” option and select “extract here”:

You now have a dump file, you can use the “Data Pump” utility to run the “import” to the Database, the Data Pump Import utility is called by the impdp command.

The characteristics of the import operation are determined by the import parameters that you specify. These parameters can be specified either on the command line or in a parameter file, the import/export can be done on different levels such as schema, table, and tablespace or full; data pump usually is very fast for extracting, running over night loads, or replicating data across different databases.

Below you can see the dump file:

Using data pump is very straight forward; you can choose one of the 5 options/ parameters to perform your import, I won’t be spending too much time commenting on this since this subject can have a separate post, but below you can see the different ways of performing it by using the data pump utility along with the oracle reference  :

https://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_import.htm#SUTIL893

Just to cut a long story short, to perform that, go to command prompt and open  SQL plus and follow the steps below, make sure the database listener is running and follow the below steps to make sure you successfully import the schema, objects and data :

  • Set Sid
  • Connect as sysdba
  • Create a user
  • Grant the first basics privileges
  • Create directory
  • Grant database role permissions
  • Import the dump file

Steps:

1- Set the instance : set oracle_sid=orcl

2- Connect to sqlplus as sysdba :  sqlplus/ as sysdba

3- Run the DDL “Create user cmmd”: create user BISAMPLE identified by xxxxx;

4- Run the DCL “grant permissions”: grant connect, resource to BISAMPLE;

  • In your OS, create a directory to store the DMP file, linux/windows
  • I created the following :  C:\ORA_BKP\BISAMPLE
  • Point the DB to that dir by the following command:

create or replace directory DMPDIR as ‘C:\ORA_BKP\BISAMPLE’ ;

  • Grant the read/write to schema:

GRANT READ, WRITE ON DIRECTORY DMPDIR TO BISAMPLE;

Double check the data dictionary and grant permissions on roles to the database user:

You will need to grant several privileges to BISAMPLE, below is the list of the grants,

Since this is a DBA task, in corporate environments you will typically ask your fellow DBA to perform those procedures, unless you are a DW admin or have high privileges :

create user BISAMPLE IDENTIFIED by sys;

GRANT READ, WRITE ON DIRECTORY DMPDIR to public BISAMPLE ;

GRANT RESOURCE, CONNECT TO BISAMPLE ;

GRANT UPDATE ANY CUBE DIMENSION TO BISAMPLE ;

GRANT UPDATE ANY CUBE BUILD PROCESS TO BISAMPLE ;

GRANT IMP_FULL_DATABASE TO BISAMPLE ;

GRANT UNLIMITED TABLESPACE TO BISAMPLE ;

GRANT DROP ANY CUBE BUILD PROCESS TO “BISAMPLE” ;

GRANT CREATE ANY CUBE BUILD PROCESS TO “BISAMPLE”;

GRANT CREATE CUBE BUILD PROCESS TO “BISAMPLE”;

GRANT INSERT ANY MEASURE FOLDER TO “BISAMPLE”;

GRANT DROP ANY MEASURE FOLDER TO “BISAMPLE”;

GRANT CREATE ANY MEASURE FOLDER TO “BISAMPLE”;

GRANT UPDATE ANY CUBE TO “BISAMPLE”;

GRANT SELECT ANY CUBE TO “BISAMPLE”;

GRANT DROP ANY CUBE TO “BISAMPLE”;

GRANT CREATE ANY CUBE TO “BISAMPLE”;

GRANT ALTER ANY CUBE TO “BISAMPLE”;

GRANT CREATE CUBE TO “BISAMPLE”;

GRANT SELECT ANY CUBE DIMENSION TO “BISAMPLE”;

GRANT INSERT ANY CUBE DIMENSION TO “BISAMPLE”;

GRANT DROP ANY CUBE DIMENSION TO “BISAMPLE”;

GRANT DELETE ANY CUBE DIMENSION TO “BISAMPLE”;

GRANT CREATE ANY CUBE DIMENSION TO “BISAMPLE”;

GRANT ALTER ANY CUBE DIMENSION TO “BISAMPLE”;

GRANT CREATE CUBE DIMENSION TO “BISAMPLE”;

GRANT COMMENT ANY MINING MODEL TO “BISAMPLE”;

GRANT ALTER ANY MINING MODEL TO “BISAMPLE”;

GRANT SELECT ANY MINING MODEL TO “BISAMPLE”;

GRANT DROP ANY MINING MODEL TO “BISAMPLE”;

GRANT CREATE ANY MINING MODEL TO “BISAMPLE”;

GRANT CREATE MINING MODEL TO “BISAMPLE”;

GRANT EXECUTE ASSEMBLY TO “BISAMPLE”;

GRANT EXECUTE ANY ASSEMBLY TO “BISAMPLE”;

GRANT SELECT ANY TRANSACTION TO “BISAMPLE”;

GRANT EXECUTE ANY CLASS TO “BISAMPLE”;

GRANT EXECUTE ANY PROGRAM TO “BISAMPLE”;

GRANT IMPORT FULL DATABASE TO “BISAMPLE”;

GRANT EXPORT FULL DATABASE TO “BISAMPLE”;

GRANT FLASHBACK ANY TABLE TO “BISAMPLE”;

GRANT SELECT ANY DICTIONARY TO “BISAMPLE”;

GRANT DROP ANY OUTLINE TO “BISAMPLE”;

GRANT ALTER ANY OUTLINE TO “BISAMPLE”;

GRANT CREATE ANY OUTLINE TO “BISAMPLE”;

GRANT DROP ANY DIMENSION TO “BISAMPLE”;

GRANT ALTER ANY DIMENSION TO “BISAMPLE”;

GRANT CREATE ANY DIMENSION TO “BISAMPLE”;

GRANT CREATE DIMENSION TO “BISAMPLE”;

GRANT UNDER ANY TABLE TO “BISAMPLE”;

GRANT EXECUTE ANY INDEXTYPE TO “BISAMPLE”;

GRANT QUERY REWRITE TO “BISAMPLE”;

GRANT UNDER ANY VIEW TO “BISAMPLE”;

GRANT DROP ANY INDEXTYPE TO “BISAMPLE”;

GRANT ALTER ANY INDEXTYPE TO “BISAMPLE”;

GRANT CREATE ANY INDEXTYPE TO “BISAMPLE”;

GRANT CREATE INDEXTYPE TO “BISAMPLE”;

GRANT EXECUTE ANY OPERATOR TO “BISAMPLE”;

GRANT DROP ANY OPERATOR TO “BISAMPLE”;

GRANT ALTER ANY OPERATOR TO “BISAMPLE”;

GRANT EXECUTE ANY LIBRARY TO “BISAMPLE”;

GRANT UNDER ANY TYPE TO “BISAMPLE”;

GRANT EXECUTE ANY TYPE TO “BISAMPLE”;

GRANT ALTER ANY TYPE TO “BISAMPLE”;

GRANT CREATE ANY TYPE TO “BISAMPLE”;

GRANT DROP ANY DIRECTORY TO “BISAMPLE”;

GRANT DROP ANY MATERIALIZED VIEW TO “BISAMPLE”;

GRANT ALTER ANY MATERIALIZED VIEW TO “BISAMPLE”;

GRANT CREATE ANY MATERIALIZED VIEW TO “BISAMPLE”;

GRANT CREATE MATERIALIZED VIEW TO “BISAMPLE”;

GRANT ANALYZE ANY TO “BISAMPLE”;

GRANT EXECUTE ANY PROCEDURE TO “BISAMPLE”;

GRANT DROP ANY PROCEDURE TO “BISAMPLE”;

GRANT ALTER ANY PROCEDURE TO “BISAMPLE”;

GRANT CREATE ANY PROCEDURE TO “BISAMPLE”;

GRANT CREATE PROCEDURE TO “BISAMPLE”;

GRANT FORCE ANY TRANSACTION TO “BISAMPLE”;

GRANT FORCE TRANSACTION TO “BISAMPLE”;

GRANT ALTER DATABASE TO “BISAMPLE”;

GRANT SELECT ANY SEQUENCE TO “BISAMPLE”;

GRANT CREATE SEQUENCE TO “BISAMPLE”;

GRANT DROP ANY VIEW TO “BISAMPLE”;

GRANT CREATE ANY VIEW TO “BISAMPLE”;

GRANT CREATE VIEW TO “BISAMPLE”;

GRANT CREATE PUBLIC SYNONYM TO “BISAMPLE”;

GRANT DROP ANY INDEX TO “BISAMPLE”;

GRANT ALTER ANY INDEX TO “BISAMPLE”;

GRANT CREATE ANY INDEX TO “BISAMPLE”;

GRANT DELETE ANY TABLE TO “BISAMPLE”;

GRANT UPDATE ANY TABLE TO “BISAMPLE”;

GRANT INSERT ANY TABLE TO “BISAMPLE”;

GRANT SELECT ANY TABLE TO “BISAMPLE”;

GRANT COMMENT ANY TABLE TO “BISAMPLE”;

GRANT DROP ANY TABLE TO “BISAMPLE”;

GRANT BACKUP ANY TABLE TO “BISAMPLE”;

GRANT ALTER ANY TABLE TO “BISAMPLE”;

GRANT CREATE ANY TABLE TO “BISAMPLE”;

GRANT CREATE TABLE TO “BISAMPLE”;

GRANT CREATE ROLLBACK SEGMENT TO “BISAMPLE”;

GRANT UNLIMITED TABLESPACE TO “BISAMPLE”;

GRANT CREATE TABLESPACE TO “BISAMPLE”;

GRANT “CONNECT” TO “BISAMPLE”;

GRANT “RESOURCE” TO “BISAMPLE”;

GRANT “EXP_FULL_DATABASE” TO “BISAMPLE”;

GRANT “IMP_FULL_DATABASE” TO “BISAMPLE”;

GRANT “DATAPUMP_EXP_FULL_DATABASE” TO “BISAMPLE”;

GRANT “DATAPUMP_IMP_FULL_DATABASE” TO “BISAMPLE”;

GRANT “AUTHENTICATEDUSER” TO “BISAMPLE”;

GRANT “OLAP_DBA” TO “BISAMPLE”;

GRANT “OLAP_USER” TO “BISAMPLE”;

GRANT “SPATIAL_WFS_ADMIN” TO “BISAMPLE”;

GRANT “SPATIAL_CSW_ADMIN” TO “BISAMPLE”;

ALTER USER “BISAMPLE” DEFAULT ROLE “CONNECT”, “RESOURCE”, “EXP_FULL_DATABASE”, “IMP_FULL_DATABASE”, “DATAPUMP_EXP_FULL_DATABASE”, “DATAPUMP_IMP_FULL_DATABASE”, “AUTHENTICATEDUSER”, “OLAP_DBA”, “OLAP_USER”, “SPATIAL_WFS_ADMIN”, “SPATIAL_CSW_ADMIN” ;

You can either run the CTL commands on sql developer or on sqlplus command prompt,

In this case I used sqlplus:

Now make sure you exit from sqlplus, you will run the “impdp” command from the  command prompt(DOS), pass the parameters to call the datapump utility like the one below, adapt to your enviroment

Example:

“Command  schemaname /pass@orcl directory=Dirname dumpfile=FILE.DMP  logfile=FILE.log”

I passed this one:

impdp BISAMPLE/alice@orcl directory=DMPDIR  dumpfile=BISAMPLE.dmp logfile=BISAMPLE.log

The Import process runs:

Take a quick look at the new db schema that we have just created, after these series of steps we can create a new RPD:

That is it for now folks!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s