O.B.I.E.E: 2015

OBIEE 12c Usage tracking attributes/columns

As part of OBIEE12c two new important columns are added to s_nq_acct tables which will aid in calculating temporary space occupation and to calculate response time.

Few main column details are as follow.

I was keep on waiting for field tota_temp_kb, we are frequently see high disk space usage and we have disabled logs. It was very difficult to identify culprit report. Now with this field we should be able to trace back to the report easily and tune the report.

Table	Column	Purpose
S_NQ_ACCT	TOTAL_TEMP_KB	Total temp space used by a query during execution of report.
S_NQ_ACCT	RESP_TIME_SEC	To measure time server took before it start fetching records. This is the response time that the end user would experience with few early records being displayed on dashboard, while server continues fetching more
S_NQ_ACCT	ECID	It is corresponds to ECID in biserver-diagnostic.log
S_NQ_ACCT	TENANT_ID	Tenant id used in multitenancy DB
S_NQ_ACCT	SERVICE_NAME	Service name used in multitenancy DB
S_NQ_ACCT	SESSION_ID	Corresponds to biserver session for use in analyzing user behavior by session
S_NQ_ACCT	HASH_ID	logical query hash id, joins to s_nq_db_acct.hash_id.
S_NQ_DB_ACCT	HASH_ID	This attribute to connect with usage tracking main table, joins to s_nq_acct.hash_id
S_NQ_DB_ACCT	PHYSICAL_HASH_ID	To tracing physical queries to the backend database used this attribute.
S_NQ_INITBLOCK	USER_NAME	Name of user for whom init block ran.
S_NQ_INITBLOCK	BLOCK_NAME	The name of the initialization block that was executed.
S_NQ_INITBLOCK	START_TS	The date and time that the initialization block started.
S_NQ_INITBLOCK	END_TS	The date and time that the initialization block finished. The start and end timestamps also reflect any time that the query spent waiting for resources to become available.
S_NQ_INITBLOCK	DURATION	The length of time it took to execute the initialization block.

For additional details – take a look at documentation http://docs.oracle.com/middleware/1221/biee/BIESG/usage_track.htm#BIESG211

OBIEE12c installation on windows7 machine

Much awaited OBIEE 12c is now available for general purpose use. Here in this post I will try to explain installation on windows 7 step by step.

FYI: My folders paths and ports are not pointing to defaults, if you copy paste they may not work in your environment, you have to relatively wherever applicable.

Installation has 3 phases

1) Pre-installation :Download installers

a) If you have java8 then you are good otherwise download from here

b) Download WebLogic 12 , if you don't have already from here

http://download.oracle.com/otn/nt/middleware/12c/1221/fmw_12.2.1.0.0_infrastructure_Disk1_1of1.zip?AuthParam=1445877562_a65223bd5ab7f7028511fefc921444d1

c) Download latest OBIEE 12c installers from here and here

http://download.oracle.com/otn/nt/bi/1221/fmw_12.2.1.0.0_bi_windows64_Disk1_1of2.zip?AuthParam=1445876405_c14c1c32080182f437d2408deb380ca6

http://download.oracle.com/otn/nt/bi/1221/fmw_12.2.1.0.0_bi_windows64_Disk1_2of2.zip?AuthParam=1445876435_3b0cadf150e3573017ca7a67a328873a

2) Installation phase, were we install each components (java, then weblogic, then OBIEE12c)
3) Running configuration

Installation phase

a) Install java 8 sdk (if you have already ignore this section)

http://download.oracle.com/otn-pub/java/jdk/8u65-b17/jdk-8u65-windows-x64.exe?AuthParam=1445903682_14ad6df8ed1bdd8b23b54aebfa4a178c

once you downloaded just execute the executable file.

b) Download and install Weblogic (if you have already weblogic 12.2.1 then ignore this section).

c:\Program Files\Java\jdk1.8.0_65\bin>java -jar C:\Users\srmalyal.ORADEV\Downloads\OBIEE12C\fmw_12.2.1.0.0_infrastructure_Disk1_1of1\fmw_12.2.1.0.0_inf

Launcher log file is C:\Users\srmalyal.ORADEV\AppData\Local\Temp\OraInstall2015-10-26_06-07-09PM\launcher2015-10-26_06-07-09PM.log.

Extracting files................

It will take you to following GUI

Here save response file for your future use.

c) OBIEE Installation: Extract the zip files and save the files as shown. Then invoke setup_bi_platform-12.2.1.0_win64.exe

It is better run this as administrator and check for compatibility mode.

If you have already visual force, it may prompt for repair, click with next.

4) OBIEE Configuration Phase : Run config.cmd to configure the OBIEE 12c

cd Oracle_home\bi\bin

C:\FMW122\Middleware\Oracle_Home\bi\bin>config.cmd

--it shows screen as below eventually takes you to GUI

Launcher log file is C:\Users\srmalyal.ORADEV\AppData\Local\Temp\OraInstall2015-10-26_06-35-08PM\launcher2015-10-26_06-35-08PM.log.

Starting Oracle Universal Installer

Checking monitor: must be configured to display at least 256 colors. Actual 4294967296 Passed

Checking swap space: must be greater than 500 MB Passed

Checking if this platform requires a 64-bit JVM. Actual 64 Passed (64-bit not required)

Log: C:\Program Files\Oracle\Inventory\logs\install2015-10-26_06-35-08PM.log

C:\FMW122\Middleware\Oracle_Home\bi\bin\..\..\user_projects\domains

Provide username and password and confirm the password

Don't enter any special characters in the schema prefix. If you enter any spl character it show following error.

"Prefix contains non-alphanumeric values , should not be more than 12characters."

Either pick default or customize according to your need.

Save the response file which will have all the details of your server where to launch what details.

Response file look like below.

Configure

Configuration

Components

BIEE

OBIPS (Port 8507)

Cluster controller (Main port 8508, monitor 8509)

Java host (Port 8510)

Scheduler (Main port 8511, monitor 8512, script 8513)

BIP

Domain

BI Domain Name: bi

Domains Directory: C:\FMW122\Middleware\Oracle_Home\bi\bin\..\..\user_projects\domains

Admin server (HTTP port 8500, HTTPS 8501, internal 8504)

Managed server (HTTP port 8502, HTTPS 8503, internal 8505)

Database

New schemas

Database type: ORACLE

Connect string: Database_host_name:1521:service_name

New RCU prefix: sm122c

Port Management

Port range

Port range start: 8500

Port range end: 8999

Application

SampleAppLite

Service Instance Key: ssi

Service Instance Limit: 1

Entry points

WebLogic Console

http://localhost:8500/console

Fusion Middleware Control

http://localhost:8500/em

BI Publisher

http://localhost:8502/xmlpserver

BI Analytics

http://localhost:8502/analytics

To Start All Services

C:\FMW122\Middleware\Oracle_Home\user_projects\domains\bi\bitools\bin>start.cmd -all

Screen look like below

BI_PRODUCT_HOME set as C:\FMW122\Middleware\Oracle_Home\bi\

ORACLE_HOME set as C:\FMW122\Middleware\Oracle_Home

Domain status; Using domainHome: C:\FMW122\Middleware\Oracle_Home\bi\bin\..\..\user_projects\domains\bi ...

Starting domain; Using domainHome: C:\FMW122\Middleware\Oracle_Home\bi\bin\..\..\user_projects\domains\bi ...

Initializing WebLogic Scripting Tool (WLST) ...

Eventually shows services are started

To Stop All Services

Cd C:\FMW122\Middleware\Oracle_Home\user_projects\domains\bi\bitools\bin

C:\FMW122\Middleware\Oracle_Home\user_projects\domains\bi\bitools\bin> stop.cmd -all

To manually starting important services

First set domain environment then start weblogic

Execute setDomainEnv.cmd

Cd C:\FMW122\Middleware\Oracle_Home\user_projects\domains\bi\setDomainEnv.cmd

Execute startWebLogic.cmd

Cd C:\FMW122\Middleware\Oracle_Home\user_projects\domains\bi\ startWebLogic.cmd

Once weblogic is started you will be able to access console screen.

http://localhost:8500/console

Starting bi server (managed server)

C:\FMW122\Middleware\Oracle_Home\user_projects\domains\bi\bin>setDomainEnv.cmd

C:\Oracle\Middleware\user_projects\domains\mydomain\bin\startManagedWebLogic.cmd bi_server1 http://localhost: 8500

EM screen look like below:

http://localhost:8500/em

To Start stop OBIEE 12c services from EM:

Click on Target Navigation (Tree Icon, upper left corner)

Click and expand Business Intelligence – click on biinstance

Screen look like below

It is same as 11g screen from availability start/stop the services.

OBIEE12c Log file locations

OBIEE 12c Presentation services log file location:

C:\FMW122\Middleware\Oracle_Home\user_projects\domains\bi\system_components\OBIPS\obips1\logs

OBIEE 12c Server services log file location:

C:\FMW122\Middleware\Oracle_Home\user_projects\domains\bi\system_components\OBIS\obis1\logs

AdminServer-diagnostic.log

C:\FMW122\Middleware\Oracle_Home\user_projects\domains\bi\servers\AdminServer\logs\AdminServer-diagnostic.log

bi_server1.log

C:\FMW122\Middleware\Oracle_Home\user_projects\domains\bi\servers\bi_server1\logs\bi_server1.log

OBIEE12c Configuration file location:

Root BI Configuration directories exists at C:\FMW122\Middleware\Oracle_Home\user_projects\domains\bi\config\fmwconfig\biconfig

OBIEE 12c BI Server configuration file location

C:\FMW122\Middleware\Oracle_Home\user_projects\domains\bi\config\fmwconfig\biconfig\OBIS\NQSConfig.ini

OBIEE 12c Presentation services configuration file location:

C:\FMW122\Middleware\Oracle_Home\user_projects\domains\bi\config\fmwconfig\biconfig\OBIPS\instanceconfig.xml

OBIEE 12c Java services configuration file location:

C:\FMW122\Middleware\Oracle_Home\user_projects\domains\bi\config\fmwconfig\biconfig\OBIJH\config.xml

OBIEE 12c Scheduler Services configuration file location:

C:\FMW122\Middleware\Oracle_Home\user_projects\domains\bi\config\fmwconfig\biconfig\OBISCH\schedulerconfig.xml

OBIEE 12c Cluster Services configuration file location:

C:\FMW122\Middleware\Oracle_Home\user_projects\domains\bi\config\fmwconfig\biconfig\OBICCS\ bi-cluster-config.xml

OBIEE 12c Metadata configuration file location:

C:\FMW122\Middleware\Oracle_Home\user_projects\domains\bi\config\fmwconfig\biconfig\metadata\config.xml

Log-on Screen look like below:

http://localhost:8502/analytics/

Home screen look like below

Few Important OBIEE12c documentation links

Whole Middleware documentation can be found here

http://docs.oracle.com/en/middleware/middleware.html

OBIEE 12.2.1 documentation

http://docs.oracle.com/middleware/1221/biee/index.html

Installation of OBIEE 12.2.1

http://docs.oracle.com/middleware/1221/core/BIEIG/GUID-04F89ACA-A2F1-4F18-8B35-BD131ACC62ED.htm#INSOA369

OBIEE 12.2.1 Tutorials

https://apexapps.oracle.com/pls/apex/f?p=44785:141:::NO::P141_PAGE_ID%2CP141_SECTION_ID:344%2C2507

OBIEE12.2.1 Starting/Stopping services

https://docs.oracle.com/middleware/1221/wls/START/overview.htm#START120

Migration from OBIEE11g to OBIEE12c
http://docs.oracle.com/middleware/1221/core/BIMIG/toc.htm

Stay tuned with next OBIEE12c posts .....

OBIEE and Working with Oracle Enterprise Metadata Management

Today I got a chance to explore on Metadata Management Tool and thought of sharing information with you on how to get started. As a prerequisite I assume you have installed already OEMM, if not take a look at this wonderful rittmanmead blog and especially on Why and How to use Oracle Metadata Management 12c. Part 1: Getting Started

Overview Section

Metadata Management is a very handy tool for developers or business users who wanted to know source lineage with minimal effort or wanted to perform an impact analysis. There are very few implementation will take care to store metadata information (How a metric calculated, what is a source of presentation attribute etc) and majority of times it involves manual work, by using this tool with very minimal effort we can achieve same thing and this can be expandable to any complex implementation easily.

With this tool we can achieve 1) Analysis to data warehouse and to source Lineage 2) Can perform impact analysis (how many reports exists with attribute "Order Detail" 3) Can add user comments to metadata ( How a measure being calculated or Why we are in doing this way only etc) 4) Can easily integrated with majority of databases and etls and has built in scheduler to schedule the jobs. 5) Visualize datamodels. 6) Visualize architecture diagram.

It has 2 major components – 1) Metadata Manager MM : This is kind of technical work where we configure about metadata and perform impact analysis or discover lineage or display Architecture Diagrams etc. Whereas 2) Metadata Explorer ME: This allow users to explore/search about metadata. In following sections I will explain in more detailed.

Working with Metadata Manager MM:

a) As soon as you login to MM you will see a "Welcome" page from left side pane (Repository Pane) , by using right click menu on Repository , choose "New" Folder and created a folder as per your need.

b) Highlight folder and click on new "Model" inside a newly created folder.

c) Provide a name to your datamodel – here I am saying "OBIEE" and select Import type as " …Enterprise Edition".
There are 3 types of OBIEE imports can be performed,

a. Oracle Business Intelligence (OBI) Answers : With this type , we can import catalog.

b. Oracle Business Intelligence (OBI) Server : With this type import we can import RPD – there is no direct way to import RPD directly first we have to convert RPD into UDML or XML using repository tools then feed it to MM.

c. Oracle Business Intelligence (OBI) Enterprise Edition: With this type of import we can import catalog as well as rpd-udml.

d) Now in "Import Setup" tab on "Create Model" pop-up provide all required information

Server URL: This is the url where analytics services are running.

File: UDML or xml file where RPD details are available

As previously said , we will not be able to feed whole RPD, we need to convert it to xml or udml.

For UDML file creation - $OracleBIHome$\server\Bin\nQUDMLGen.exe -U Administrator -P Administrator -R paint.rpd -O paint.udml -N -Q -8

In order to convert RPD as XML – use following command.

Run the bi-init.cmd from directory ORACLE_INSTANCE\bifoundation\OracleBIApplication\coreapplication\setup\bi-init then

Execute biserverxmlgen -R paint.rpd -P Admin123 -O paint.xml -N -Q -8

Rest keep them as per your need. And test connectivity by clicking Test link. If there are any errors then fix them.

e) Optionally you can configure "Import Schedule" , "Security" and "Triggers" tabs.
Perform Import operation, it will display import messages as shown below.

f) After import your will be able to view model "OBIEE" and double click on it, it will display 2^nd pane , i.e. Model Directory it displays all the objects from RPD and Catalog.

g) Highlight any report and double click on it (example "Contribution Waterfall" report) now in 3^rd pane it is displaying what are views that report had and presentation attributes it has.
h) Highlight any attribute on 3^rd pane – in Properties pane (4^th pane) you will be able to see that object properties.

Now coming to Utilizing Metadata Manager:

Typical datawarehouse look like below, from source layer we will bring data using ETL into warehouse layer from there using OBIEE analysis will create reports, to

Performing Lineage: Now let me walk you throw performing a lineage on following highlighted columns

Click on Trace data lineage , this will pop what is the scope is it in model or configuration.

Configuration is nothing but couple of models configured as one configuration, example – majority of times we will have source system – so create a model for source system, then if we have ETL's then DAC or Informatica or ODI may exists so create a model for them , and one model for OBIEE which includes RPD and Catalog, now with these 3 models create one configuration.

Now we can see its lineage – Once you expand each semantic layer by expanding – you will be able to see under that layer how it is changed or mapped. Example – once we expand BI Repository you will be able to see all 3 layers of RPD and how an attribute mapped.

Now let's take a look at Impact Analysis example:

Highlight any presentation attribute which you want to perform impact analysis

And it is showing – source to Presentation lineage.

Now let's learn about Metadata Management Explorer :

You can able to launch explored from tool bar menu , in web URL you will see something like "/MM/Explorer" and prompt you to select configuration, select one.

You will be able to view BROWSE objects based on the configuration you selected. Here in picture it is showing only Database and Oracle Business Intelligence as in my configuration only those 2 repositories are there. By using browse capabilities we will be able to view corresponding technology each and every object.

Here is one such sample – browse Oracle Business Intelligence –Tree view – expanded dashboards to see what are all the pages(Children) are there.

Now coming to Metadata Explorer Search tab: I see this is very powerfull tool as we can search for metadata by technology.

Here is sample search results are shown here.

Here are few search key words and expected results , in short make use of + or - symbols to restrict results.

To search for	Example	Result
Any words	sales order	Any result containing the word "sales" or the word "order"
Exact phrase	"sales order"	Any result containing the exact phrase "sales order"
All words	+sales +order	Any result containing BOTH the words "sales" and "order"
Exclude words	sales –order	Any result containing the word "sales" but cannot contain the word "order"
Wild card end	sale*	Any result containing part of a word beginning with "sale"
Parent and child	sales.order	Any result where the parent is named "sales" and the child is named "order". E.g., the attribute "order" contained within the entity "sales".
Exact name	.order	Any result only containing the word "order"
Object Type	type:Column	Any result which is of type "Column"
Property Type	property:name	Any result where the search criteria matches the name

Bottom line : With a minimal configuration effort we will be able to perform impact/lineage across all technologies.

Happy Sunday !!!