OBIEE 12C RPD Merge

Problem statement:

How to do rpd merging for existing Bi-Apps modules with the new module?
Eg: If we have an existing environment dashboards/reports with Bi-Apps modules like Finance, Proc & Spend, and if we have to add new module dashboard/report like Manufacturing Analytics.
As the environment is the existing environment we already have the rpd with Finance, Proc&Spend metadata in the rpd. If we want to add Manufacturing metadata to the existing rpd follow the below solution.

For merging the rpd we can follow the below processes.

1. Using the Merge option available in Admin Tool.
2. Manually doing the rpd merging

Solution:

We have the below 3 rpd’s available.

RPD-1: This is the OOTB RPD which will come after installation/configuration of BI-Apps.
RPD-2: This is the RPD which contains only the metadata for Manufacturing Analytics which is extracted from RPD-1.
RPD-3: This is the existing RPD which has Finance, Proc &Spend metadata.
For merging we have to add/merge the RPD-2 metadata with the RPD-3. As an output we will have the RPD with Finance , Proc & spend and Manufacturing modules metadata in single RPD.

1. Using the Merge option available in Admin Tool:

As a general scenario to merge two rpd we have use the merge option.
To use the merge option we have to create one new empty/dummy rpd as shown below.

When we use the merge option we will face below issues
It will take time to open next step

There is a long list to define the merge strategy

Once we gave the merge strategy the rpd is failing with below error.

The merge option most of the times does not working for the rpd which is of bigger size. So we can follow the manual process to merge. This is not a oracle standard but it’s just a work around.

Manually doing the rpd merging.

If we are doing it manually we need to follow the steps below.
1. Physical layer changes
2. BMM layer changes
3. Presentation layer changes
4. Adding required variables/initialization blocks.

Step-1: Working in Physical Layer
In this below is the steps followed.
1. Finding the new tables which is only related to Manufacturing and not presented in existing Finance and P&S modules.

Create joins between the facts and dimension

2. If the table is common to Finance, P&S and Manufacturing analytics then we need to check the joins between facts and dimensions.

Steps-2 : Working in BMM layer.
After completing the merging in the Physical layer we need to merge the Object in BMM layer. So to do that we have manually find out the logical tables and Hierarchies which is there for the manufacturing. After copying the logical tables we need to check the LTS and Hierarchy for the logical dimension table.
Copying the dimension tables:

Checking related LTS/joins:

Copying the related Hierarchies for the dimensions:

Step-3:
After completing the changes in Physical and BMM layer we need to add the subject areas related to Manufacturing module to Finance , P&S Modules merged RPD.
Copying related subject areas for Manufacturing Module:

Step-4
If any of the dependent variables are there we need to create that variables in merging RPD.

Once this is done we need to check the consistency for error. This will show the error if any of the table or column is not mapped or joined properly.
Once every thing is done we need to deploy the rpd and the check the data in the reports/dashboards.

OBIEE 12 Admin tool OCI Connectivity

Connecting to the Admin tool Online : Find the Port

netstat -plnt

Find the nqsserver ports --- 9514 was the candidate for OBIEE RPD Connectivty online

tcp        0      0 :::9514                     :::*                        LISTEN      24868/nqsserver
tcp        0      0 :::9515                     :::*                        LISTEN      24868/nqsserver


Anyway Moving on TNS Setup.......

12c is using a local TNS in Online Mode and Offline Mode and not using the TNS_ADMIN on a windows machine.

However you can view the data in the tables within the RPD( Weird huh) as it using the network tnsnames.ora


Anyway goto the OBIEE Administration tool installation location and paste a tnsnames.ora file.

Steps: goto the OBIEE Administration tool installation location

[OBIEE Installation Directory]\Oracle_Home\network

Create a Directory called admin

Then within that paste a valid tnsnames.ora file.

Restart the BI Administration tool and everything works as it should ie creating tables from DB.

ODI 12C and OBIEE 12C Integration with SOAP WSDL

I have attached a link to the step by step instructions at the end of this post on how to integrate SOAP WSDL from ODI to OBIEE so you can start ibots or do various other tasks within OBIEE from ODI without the need of a dedicated ODI agent on the OBIEE Server. This solution was developed in 12c.

Business Case

One of our customers wanted OBIEE reports to be mailed after the completion of a data load in ODI. We know there are different methods of achieving this. But we did not want to deploy an ODI Agent onto the OBIEE Server in the Corporate Environment.

That is the need and here is the solution we came up with – Using WSDL & SOAP approach to build bridge between the two most popular Oracle technologies in Data Warehousing world.

Solution Technology

We used the two following technologies that are made available as part of ODI and OBIEE while building the solution

WSDL (Web Service Definition Language) is often used in combination with SOAP and an XML Schema to provide Web services over the Internet. A client program connecting to a Web service can read the WSDL file to determine what operations are available on the server. Any special datatypes used are embedded in the WSDL file in the form of XML Schema. 

SOAP (Simple Object Access Protocol) is a protocol specification for exchanging structured information in the implementation of web services in computer networks. It uses XML Information Set for its message format, and relies on application layer protocols, most often Hypertext Transfer Protocol (HTTP) or Simple Mail Transfer Protocol (SMTP), for message negotiation and transmission

Solution Overview

The solution enables us from ODI to remotely login to OBIEE and initiate an iBot and log out. Following are the high level steps

   1.     Use OdiInvokeWebService to login to OBIEE with SOAPImplementation URL  

   2.     The above call creates a response file with the OBIEE Session Id

   3.     Use a Simple mapping to move this file content into a database table

   4.     Create a variable with sub string and in string operations to extract the OBIEE session id

   5.     Use OdiInvokeWebService to initiate an iBot with the above session id and iBot name to        mail the required report to recipients

   6.     Use OdiInvokeWebService with the same session id to log off from OBIEE

We have created an ODI package to orchestrate the above steps which executes the above steps in a seamless chain.

Deploying/upload rpd in OBIEE 12.2.1.0 / 12c server

Unlike in 11g, em does not have any option for deployments of rpd, it is all now uploading repository to a specific service instance. we should be using weblogic scripting command “uploadrpd’ to upload repository to Oracle BI Server.
You execute the utility through a launcher script, data-model-cmd.sh on UNIX and data-model-cmd.cmd on Windows. You can find the launcher script at the following location:
<Oracle_Home>/user_projects/domains/bi/bitools/bin
>wlst.cmd
Syntax:
uploadrpd -I <RPDname> [-W <RPDpwd>] -SI <service_instance> -U <cred_username> [-P <cred_password>] [-S <hostname>] [-N <port_number>] [-SSL] [-H]

• I specifies the name of the repository that you want to upload.
• W is the repository’s password. If you do not supply the password, then you will be prompted for the password when the command is run. For security purposes, Oracle recommends that you include a password in the command only if you are using automated scripting to run the command.
• SI specifies the name of the service instance.
• U specifies a valid user’s name to be used for Oracle BI EE authentication.
• P specifies the password corresponding to the user’s name that you specified for U. If you do not supply the password, then you will be prompted for the password when the command is run. For security purposes, Oracle recommends that you include a password in the command only if you are using automated scripting to run the command.
• S specifies the Oracle BI EE host name. Only include this option when you are running the command from a client installation.
• N specifies the Oracle BI EE port number. Only include this option when you are running the command from a client installation.
• SSL specifies to use SSL to connect to the WebLogic Server to run the command. Only include this option when you are running the command from a client installation.
• H displays the usage information and exits the command.

Example: data-model-cmd.sh uploadrpd -I <RepositoryName.rpd> -SI ssi -U weblogic -S server.example.com -N 9502 -SSL

Note: Using “uploadrpd”, we can upload an Oracle BI repository in RPD format and cannot upload a repository composed of MDS XML documents.
The online rpd in the server is with name liverpd.rpd_n (n is integer with increased number) and can be found at: <Oracle_Home>\user_projects\domains\bi\bidata\service_instances\ssi\metadata\datamodel\customizations
You can log into analytics and see the subject area contents of new rpd with changes affected.
You can also download current online rpd from server which acts as offline rpd on which you will be developing your changes or for any diagnose purpose
syntax: downloadrpd -O RPDname [-W RPDpwd] -SI service_instance -U cred_username [-P cred_password] [-S hostname] [-N port_number] [-SSL] [-H]

rpd will be saved under the same location where you executed above command..

Unable to login as user after LDAP configuration in OBIEE 12c Weblogic Console

Problem Statement:

When creating the Agents in OBIEE 12C, the agents get created successfully but while running the Agents as recipients, they get failed with the message “The user does not have impersonation privilege”.

In OBIEE 11G the LDAP was configured at rpd level and the impersonation and Agents were working fine with no flaws but, when the same was implemented in OBIEE 12c as a part of upgradation from OBIEE 11G to OBIEE 12C we are facing the impersonation problem, which is leading to the Agents failure, nor we can login in as the users apart from Weblogic and Administrator.

Applied Solution: As analyzed, the cause for the above error would be the LDAP configuration in rpd. OBIEE 12c LDAP should be configured in console unlike 11g at rpd level.

Hence, to overcome this we tried implementing LDAP at Console level rather than at rpd level and followed the steps as mentioned in the oracle document to create the LDAP Authentication in weblogic console. The steps followed are as below:

In Providers added a new Provider as ADAuthenticator:

For ADAuthenticator changed the control Flag: SUFFICIENT

Configured Connection details:

Configured the users details:

Configured the group details:

Statics Groups and Dynamic Groups:

In General:

For ‘’Default Authenticator’’ provider ‘’Control Flag’’ changed to OPTIONAL.

After configuring we are able to see the user in ‘Users and Groups’

In EM:
We had configured the Identity store provider:

In EM we are able to see the users to add to the Application roles:

The user’s list was visible in the console and EM. After restarting the server when we tried to login to Analytics page with the users mentioned above we are still facing Authentication error which is avoiding us to login and we are unable to run the Agents as well.

We further tested whether this issue is related to a specific user or to all the users and realized that this was with all the users, and we also checked that the password for the users should not contain any special character.

After checking the logs, we identified that there were some inti blocks for Authorization where user is getting the groups from E-Business Suite DB which were to disable and re-deploy the rpd, but still the issue continues.

Further we assigned the Global Admin role to one of the LDAP user and tried to login to EM which failed again.

We confirmed that all the LDAP configuration and the related variables were disabled from the rpd level. We tried to login by changing the group membership searching to limited and level to 5, but still we are facing the same issue.

As suggested by oracle, the below parameters were misconfigured and we re-configured it but the issue still remains:
principal – cn=jsmith,cn=users,dc=us,dc=xyzcorp,dc=com.

Group Base DN:- DC=vm,DC=oracle,DC=com
User Base DN:- cn=users,DC=vm,DC=oracle,DC=com

OBIEE 12C NEW UI & FEATURES

Many new features has been added in the OBIEE 12C including new views, new UI level functions, column properties, Powerful visual analyzer tool etc.,

• Let us see some of the new visualizations available in 12C:

• All Views are enhanced in such a way that we can 'right click' on all graphs & other views to sort, drill, keep the column or remove the column from the view.

• Under 'Column Properties'-->Data Format properties--> “Scale for % (x 100)” option is added that  Specifies whether to multiply data by a 100 to display it as a percentage when setting the properties of a column.

New View Added:

• A new View called 'Heat Matrix' view has been included which represents the two-dimensional data. This is well suited for analyzing large amounts of data and identifying outliers.

    You can find the values by 'Roll over' on the cells. Or you can make it as display always.

New UI 'R Analytics' Functions:

• One of the great advantage of OBIEE12C is availability of new set of functions related to 'R Analytics'  added in the 'edit formula' tab.
• 'R' is a programming language and software environment for statistical computing and graphics. The 'R' language is widely used among statisticians and data miners for developing statistical software and data analysis.
• To work with 'R Analytics' functions we have to setup 'R' in the environment.
• The 'R Analytics' function set includes

Cluster, Evaluate Script, Outlier, Regr, Trendline functions.

New Time series Function:

• A new time series function called 'Forecast' has been added.

Adding Your Own Data to Analyses:

• You can include dimensions and measures from external data sources in your analyses. The external data is loaded to the database, but is not part of the BI metadata catalog.
• You can create an analysis that include only data from an external source, or you can blend dimensions and measures from the external data source with dimensions and measures defined in the BI metadata catalog.
• The external data must be stored in a Microsoft Excel format spreadsheet file. You import the data from the spreadsheet file into the BI EE database as an external data source.
• You then add columns from the external data source to your analyses. When the data in spreadsheet file changes, you can refresh the BI EE database with the changes.

• Also we can add external data source like excel file through the 'Add Data Source' option available.

Visual Analyzer

• This is a completely new powerful visualization application introduced in OBIEE12C.
• VA provides greater flexibility to Rapidly create rich analyses through drag-and-drop configuration of new visual grammar.
• Using VA we can build the reports from the sources OBIEE repository and also from external content.

BI Server New Features

• Logical Level Sequence Numbers for Time Dimensions for faster time series calculations. And to improve the query time.
• DISPLAY, SORTKEY Syntax Supported in the SQL ORDER BY Expression
• Oracle Database FAN & Connection Failover Supported by Oracle BI Server

Following New Command Line utilities are added

• Download Repository
• Upload Repository
• List Connection Pool
• Update Connection Pool
• Rename Application Role
• Delete Application Role
• Rename Users
• Delete Users
• List Repository Variables
• Update Repository Variables

Generate Fragmented Aggregates in Aggregate Persistence

• The aggregate persistence functionality has been enhanced to generate fragmented aggregates from a manually written aggregate specification. You can generate fragmented aggregates by adding a Where clause to the Logical SQL query's Create statement.

New Features for Oracle BI System Administrators

• We can Invoke WLST from a Single Location
• Oracle Home location re-defined and there is no middleware home now.
• OPMN is no Longer available in Fusion Middleware
• Oracle Web Cache Not Part of Fusion Middleware
• Moving From Test To Production is Carried Out in a Different Way
• New Commands For Process Control
• Managing Metadata In Business Intelligence Archive Files(BAR File)
• Single Enterprise Install. No separate 'Software only' and 'Simple Install' is available.
• Simplified Configuration
• Managing System Component Instances Using Commands
• Collecting Diagnostic Bundles

Migration from OBIEE 11g to 12c

• We can migrate the Oracle BI 11g metadata to 12c is a two step process, and is carried out by using the BI Migration Script (migration-tool.sh).
• The catalog and WebLogic Security configurations needs to be reconfigured/migrated like in OBIEE 11g.

New Features in Oracle BI Security

• BISystemUser and BISystem are removed.
• User GUIDs Removed
• Database Security Store
• Easier SSL Configuration
• Migrating Catalog Groups to Application Roles

New Features for Oracle BI Publisher 12c

• Generate Explain Plan from SQL Data Set
• Best Practices Information

OBIEE 12c All new features, Improvements

This version is another major release from Oracle after OBIEE 10g and11g. This version has many improvements, Enhancements, and advanced new features in installation, migration, analyses and dashboards, Oracle BI Mobile, Oracle BI Publisher, metadata repository development, system administration, integration and API, and localization.
Download link: http://www.oracle.com/technetwork/middleware/bi-enterprise-edition/downloads/business-intelligence-2717951.html
Documentation: http://docs.oracle.com/middleware/1221/biee/index.html

Highlights:

New Features in Presentation Services:

• Visual Analyzer: A brand new powerful visualization application in OBIEE Suite. VA provides greater flexibility to Rapidly create rich analyses through drag-and-drop configuration of new visual grammar. Using VA we can build the reports from the sources OBIEE repository and also from external content.
• Views Enhancements: New context menu (Right Click) on all graphs & other views to sort, drill.
• New Heat Matrix view type to represent the two-dimensional data. This is well suited for analyzing large amounts of data and identifying outliers.
• New Data Format properties “Scale for % (x 100)”: Specify whether to multiply data by a 100 to display it as a percentage when setting the properties of a column

New Features in BI Server:

• Logical Level Sequence Numbers for Time Dimensions for faster time series calculations. And to improve the query time.
• DISPLAY, SORTKEY Syntax Supported in the SQL ORDER BY Expression
• Oracle Database FAN & Connection Failover Supported by Oracle BI Server
• Generate Fragmented Aggregates in Aggregate Persistence
• Following New Command Line utilities are added
  • Download Repository
  • Upload Repository
  • List Connection Pool
  • Update Connection Pool
  • Rename Application Role
  • Delete Application Role
  • Rename Users
  • Delete Users
  • List Repository Variables
  • Update Repository Variables

New Features in Administration:

• Installation: Much faster and straight forward. And mostly, the configurations & folders directories are simple and clean.
• NO more OPMN, all services including OBI System & Java services are managed by WebLogic.
• Migrating from OBIEE 11g to 12c: We can migrate the Oracle BI 11g metadata to 12c is a two step process, and is carried out by using the BI Migration Script (migration-tool.sh). The catalog and WebLogic Security configurations needs to be reconfigured/migrated like in OBIEE 11g.
• BAR (BI Archive file): A new archive format which contains OBIEE Catalog, RPD and Security packaged in one file. The file will be created in current environment and imported in Target environment. The BAR file creation process has following options while creating it.
  • export all: To export everything including users and data sources are the same between two systems.
  • export without user folder content: Migrating the Catalog without the User’s Folders.
  • export without connection pool credentials: Migrating the upgraded RPD from one environment to another without connection Pool information. When Imported the BAR in Target environment, The RPD will be updated the new changed by not changing the Target System Connection Pool Information.
  • Syntax:
    • Creating the BAR File from Current Environment: 
      exportServiceInstance(domainHome, serviceInstanceKey, workDir, exportDir, applicationName=None, applicationDesc=None, applicationVersion=None, includeCatalogRuntimeInfo=false, includeCredentials=None)
    • Importing the BAR file in Target environment:
      importServiceInstance(domainHome, serviceInstanceKey, barfile)

New Features in Security:

• BISystemUser and BISystem Removed, No need to have BISystemUser in LDAP. The system user concept is now deemed “virtual” and is represented by the credential oracle.bi.system/system.user, for which the values are securely randomly generated by the Configuration Assistant.
• User GUIDs Removed: user GUIDs have been removed to make administration easier. GUIDs are replaced with user names. There is no longer any need to refresh GUIDs as part of lifecycle operations
• Database Security Store: Policy & Credential store is configured in database instead of the file System-jazn & etc.
• Easier SSL Configuration: Configuring SSL is much easier than earlier versions.