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
c)       Download latest OBIEE 12c installers from here and here

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)
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.

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:


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:

Home screen look like below


Few Important OBIEE12c documentation links

Whole Middleware documentation can be found here

OBIEE 12.2.1 documentation

Installation of OBIEE 12.2.1

OBIEE 12.2.1 Tutorials

OBIEE12.2.1 Starting/Stopping services

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.
Login User: Provide user information as how you login to server (OS user details).
Login Password: OS user password.
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 2nd 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 3rd pane it is displaying what are views that report had and presentation attributes it has.
h)      Highlight any attribute on 3rd pane – in Properties pane (4th 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 !!!