OBIEE 12c Required Information: Services, Repository Deployments

Hi,

Below are some useful information about OBIEE 12C . Thank You.

Restart  Services:

  • Start/Stop/Status shell scripts – ‘…\12c_home\user_projects\domains\bi\bitools\bin’.
    Options are ‘status.cmd’, ‘stop.cmd’ and ‘start.cmd‘:

Example: [oracle@eld17 bin]$ ./status.sh

[oracle@eld17 bin]$ ./start.sh -i obis1,obips1

 

Main directories:

  • Directory structure has completely changed in 12c. Here are the most commonly used paths:
    • OBIEE Configuration files: <MW_HOME>/user_projects/domains/bi/config/fmwconfig/biconfig
    • ora: <MW_HOME>/user_projects/domains/bi/config/fmwconfig/bienv/core
    • Stop/Start scripts: <MW_HOME>/user_projects/domains/bi/bitools/bin
    • Logs: <MW_HOME>/user_projects/domains/bi/servers
    • Custom messages (previously AnalyticsRes): <MW_HOME>/user_projects/domains/bi/bidata/components/OBIPS/customMessages

 

Catalog Upgrade Pre-Requisite

 

  • Upgrading catalog to 12c is supported from 11.1.1.7 and 11.1.1.9.

 

Migration Script Location:

  • bi-migration-tool.jar…/user_projects/domains/bi/bitools/bin  (Please verify attached doc)

/d04/obiee12c/Middleware/Oracle_Home/bi/migration-tool/jlib  —  bi-migration-tool.jar

OBIS RPD deployment:

There is a command line tool to upload and download an OBIS RPD in 12c:

  • ‘data-model-cmd.cmd’ at ‘…/Oracle_Home/user_projects/domains/bi/bitools/bin.
  • We need to FTP our modified/required RPD from local system to above location and then, use below upload/downloadrpd process.

 

Example : [oracle@eld17 bin]$ sh data-model-cmd.sh downloadrpd -O liverpd.rpd -W Admin123 -SI ssi -U weblogic -P admin123

                    [oracle@eld17 bin]$ sh data-model-cmd.sh uploadrpd -I EIS_HR_liverpd.rpd -W Admin123 -SI ssi -U weblogic -P admin123

 

Every time, latest RPD will be created as liverpd.rpd_n (n is integer with increased number) and can be found at: <MW_HOME>\user_projects\domains\bi\bidata\service_instances\ssi\metadata\datamodel\customizations

 

Catalog Size:

  • In 12c, the size of the catalog cannot exceed 4.5GB by default. If the catalog reaches this size then some limitations appears, To remove this limitation or increase it, the following tags can be added in xml:

<catalog>

<ServiceInstanceQuota>

<Enabled>true/false</Enabled>

<SizeLimitMB>5120</SizeLimitMB>

</ServiceInstanceQuota>

<catalog>

 

Default RPD Location:

  • /<MW_HOME>/user_projects/domains/bi/bidata/service_instances/ssi/metadata/datamodel/customizations

 

Default Catalog Location:

  • /<MW_HOME>/user_projects/domains/bi/bidata/service_instances/ssi/metadata/content/catalog

Thanks,

Raghu Nagadasari

Advertisements
Posted in Uncategorized | Leave a comment

How to Downgrade Rpd from Higher to Lower Version:

Here I want to downgrade my Rpd from 11.1.1.6.6 to 11.1.1.5.0. If you want to downgrade Rpd Version from 320 to 308 also, you can follow the below steps.

Open below path of higher version:

D:\Oracle Business Intelligence Enterprise Edition Plus Client\oraclebi\orahome\bifoundation\server\bin

Please keep ur rpd in one path, example E:\ OKS1166.rpd

Then u want to put the higher version Bin location in Command prompt.(Not lower version)

Even Rpd (which Prd is going to open and where u r going to Save) path also not mind in Command prompt at Input and Out put locations ( -I E:\ OKS1166.rpd –O E:\OKS115.rpd) if u are place the higher version rpd in Higher version Bin Location.

D:\Oracle Business Intelligence Enterprise Edition PlusClient \oraclebi\orahome\bifoundation\ server\bin>nqgenoldverrpd.exe –P Admin123 –I E:\OldVersion.rpd –O E:\NewVersion.rpd –V 308

No Need to give Rpd path if Input file is already in required RPD Version BIN Directory.

 

 

Posted in OBIEE | Leave a comment

OBIA 7.9.6.3 Pre-requisites, System Configuration, Performance improvements (Server Side & Development Side), Query Performance Tuning, Maximum connections

Hi friends,

Today i got the below small requirement from my Client and i know its known post to everyone. Please correct me if anything wrong. Thanks to all.

OBIA Pre-requisites:

Oracle BI Applications Release 7.9.6.3

Oracle BI Enterprise Edition 11g (11.1.1.7.0)

Informatica Power Center Version 9.0.1 Hot fix 2

OBI Data Warehouse Administration Console (DAC) 10.1.3.4.1

Oracle database 11g Release 2

EBS R12.1.3

System Configuration:

Operation System: Linux (For better Security)

Maintain 2 Linux Servers (if possible) to install Servers of Informatica, DAC, OBIEE, and Database

Linux Server-1 : Database, OBIEE Servers

Linux Server-2 : Informatica, DAC Servers

Windows: Install all Clients Software’s on Windows machine (Informatica, DAC, OBIEE, OBIA)

For Servers:

RAM: 16 GB

Storage Space: 100 GB free space

For Clients:

RAM: 4 GB

Storage Space: 50 GB free space

Performance improvements (Server Side):

Performance is improved by using faster processors and additional memory, particularly in environments where users run multiple applications simultaneously.

Since a virtual environment is not native, there may be a possible performance impact when running Informatica products in a virtual environment. For best performance, Informatica recommends running in non-virtual environments.

 

BI APPS Performance Tuning (Development Side):

• The OBIA Version 7.9.6.x Performance Recommendations document provides good guidance on improving ETL performance

Notable recommendations include:

– In Informatica, use Custom Connections for long running mappings

– Allows you to set custom session parameters suitable for the requirements of the mapping

– Increasing the Commit Intervals

– Use Informatica 64-bit version

– Disable redundant bitmap indexes

– Wide table structure optimization (over 255 columns)-> move NULLs to the end

– Disable / Enable query indexes

– Joining Staging tables to lookup tables in Informatica Lookups

Query Performance Tuning

1. Partition Large Tables based on Fiscal Calendar or Operating Unit (based on client needs)

– Partition by Fiscal Month for Project Cost Line Fact and Project Revenue Line Fact

– Make sure the DAC is configured to handle indexing on partitioned tables using Custom Actions. Otherwise, Index Creation during the ETL WILL FAIL.

2. Use Materialized Views for creating custom aggregate tables, Hints in Rpd Level

– You can use the RPD to configure the MV as an aggregate logical table source or rely on MV query rewrites

– Integrate the refresh of the MV in the DAC Execution Plan as a Task Action or as a Custom Stored Procedure

– Register the MV as a table in the DAC and define as a related table to the original fact

3. For Oracle DBs, enable Star Transformations at the DB level or at the session level through the RPD Connection Pool.

Maximum connections:

By default in OBIEE Rpd (OracleBIApps.rpd) can gives 100 connections in Connection Pool and it allows up to 32767 Connections (max no. of connection pools).

Posted in OBIA 7.9.6.3 | 1 Comment

Tableau Reporting Tool.

As it is also one of the best reporting tool available in our IT market, please install and practice yourself in your local system. And Tableau 8.1 v has released recently and available as open source for 14 days trail version. And the whole 14 days, we are going through “Continue Trail” option instead of Activate for License while connecting to the tool.

As usual, it is also having Services always in Started mode, once we open our system.

Available in the below path:

http://www.tableausoftware.com

Please look into the pre-requisites for Tableau Software below.

http://www.tableausoftware.com/products/techspecs

We need to download “Tableau Server” and “Tableau Desktop” tools to work with multiple sources in Tableau Software.

Tableau Server Path:

http://www.tableausoftware.com/products/server/download

Tableau Desktop Path:

http://www.tableausoftware.com/products/desktop/download

And install the both tools as per available screenshots in the same path where downloading the files.

Before that, please download the “Tableau Public” version from the below path and practice with available source systems like .txt , .xls,….

http://www.tableausoftware.com/public/download

And you may need to enter few details at the time of completing your installations.

Once you install the Server it will give you one default path like below:

http://localhost:8000/

Enter the required details and COnnect to Data, then build reports like in Analytics of OBIEE.

Thanks.

Raghu Nagadasari

Note: If you need to detailed document like how to create reports and Dashboards, ping me here..

Posted in Tableau | Leave a comment

How to avoid Distinct in OBIEE 11g Reports.

Hi,

By default in OBIEE 11g repository, the distinct feature is enabled and reports doesn’t allowed duplicate records in reports level and it shows each record single time only.

But, some times Clients will asks about this kind of issues and the solution is below:

We have add one Unique ID column (any Id column) to the existing report from the same Subject Area to avoid/disable the Distinct feature which is enabled in Repository level in OBIEE 11g . It’s a default feature from Oracle and they are not supposed to give duplicates to the users in OBIEE 11g reports.  So, we have to disable the distinct feature manually if we need duplicates for any Single report in OBIEE 11g.

Steps:

  1. Login into the Analytics URL and enter required username, password details.
  2. OPen one report wchich is having duplicates in Source level.
  3. Open Criteria tab, then expand Table  from same Subject Area, then drag the “Unique Id(Customer Trx Id)” column and drop it into before the existing report columns.
  4. If you want to hide the newly added column in the report, just right click on new column, select Column Properties–>Column Format–> Hide.
  5. Click on Results tab, select Table view under Views list available in left side pane. Then, click on Edit button to edit the Table view.
  6. Keep the “Unique IdCustomer Trx Id” column at first (By default, the column will be added at last of the columns list. Then we will move backward the column from one place to another.)
  7. Click on Done, then Click on Save.
  8. Now, see the report results and notice that there are duplicate records.

Have a nice time.. 🙂

Thanks,

Raghu Nagadasari

 

Image | Posted on by | 1 Comment

OBIEE 11.1.1.7.1 Software Download

Go to the below link with ur registerd credentials like gmail etc..:

https://edelivery.oracle.com

Enter ur credentials

Image

Enter your credentials like below:

Image

Accept two license agreements and click Contunue:

Image

Select Product , Platform from Dropdown list then, click on Go :

Image

Choose the Redio Button which u want and Continue:

Image

Here, we can Download all the files related to the OBIA version..

Image

Enjoy…

Many Thanks..

Raghu Nagadasari

Aside | Posted on by | Leave a comment

OBIA 11.1.1.7.1 with ODI Data Loading Error in HR Analytics : Uncategorized exception during repository access

Hi Gurus,

We are working with OBIA 11.1.1.7.1 (with ODI11g instead of Informatica, DAC). We have one with Installation and Configuration. Now we are loading data for only HR Analytics module from EBS source system.  All the system setups and connections are good and data loading process is going smoothly. But, we just got an error like below:

oracle.odi.core.repository.UncategorizedRepositoryAccessException: ODI-10182: Uncategorized exception during repository access.    
Connection has been administratively destroyed because the pool got too many consecutive failed connection tests, and failures to obtain replacement connections from the DBMS. Please see the server log entries at that time for more details. Reconnect. java.lang.Exception: It was destroyed at Wed Jul 10 01:29:17 IST 2013

Image

Please find the error message in below screenshot.

Image

Please see the complete Error Message here.

oracle.odi.core.repository.UncategorizedRepositoryAccessException: ODI-10182: Uncategorized exception during repository access.
Connection has been administratively destroyed because the pool got too many consecutive failed connection tests, and failures to obtain replacement connections from the DBMS. Please see the server log entries at that time for more details. Reconnect. java.lang.Exception: It was destroyed at Wed Jul 10 01:29:17 IST 2013
at weblogic.jdbc.common.internal.ConnectionEnv.destroy(ConnectionEnv.java:616)
at weblogic.common.resourcepool.ResourcePoolImpl.destroyResource(ResourcePoolImpl.java:1887)
at weblogic.common.resourcepool.ResourcePoolImpl.reclaimReservedResources(ResourcePoolImpl.java:1539)
at weblogic.common.resourcepool.ResourcePoolImpl.disablePool(ResourcePoolImpl.java:1515)
at weblogic.common.resourcepool.ResourcePoolImpl.processFailCountExceededDisableThreshold(ResourcePoolImpl.java:1829)
at weblogic.common.resourcepool.ResourcePoolImpl.incrementResetFailCount(ResourcePoolImpl.java:1815)
at weblogic.common.resourcepool.ResourcePoolImpl.makeResources(ResourcePoolImpl.java:1322)
at weblogic.common.resourcepool.ResourcePoolImpl.makeResources(ResourcePoolImpl.java:1236)
at weblogic.common.resourcepool.ResourcePoolImpl.reserveResourceInternal(ResourcePoolImpl.java:460)
at weblogic.common.resourcepool.ResourcePoolImpl.reserveResource(ResourcePoolImpl.java:352)
at weblogic.common.resourcepool.ResourcePoolImpl.reserveResource(ResourcePoolImpl.java:339)
at weblogic.jdbc.common.internal.ConnectionPool.reserve(ConnectionPool.java:491)
at weblogic.jdbc.common.internal.ConnectionPool.reserve(ConnectionPool.java:384)
at weblogic.jdbc.common.internal.ConnectionPoolManager.reserve(ConnectionPoolManager.java:125)
at weblogic.jdbc.common.internal.RmiDataSource.getPoolConnection(RmiDataSource.java:454)
at weblogic.jdbc.common.internal.RmiDataSource.getConnectionInternal(RmiDataSource.java:538)
at weblogic.jdbc.common.internal.RmiDataSource.getConnection(RmiDataSource.java:498)
at weblogic.jdbc.common.internal.RmiDataSource.getConnection(RmiDataSource.java:491)
at org.springframework.jdbc.datasource.UserCredentialsDataSourceAdapter.doGetConnection(UserCredentialsDataSourceAdapter.java:162)
at org.springframework.jdbc.datasource.UserCredentialsDataSourceAdapter.getConnection(UserCredentialsDataSourceAdapter.java:133)
at oracle.odi.jdbc.datasource.LoginTimeoutDatasourceAdapter.doGetConnection(LoginTimeoutDatasourceAdapter.java:99)
at oracle.odi.jdbc.datasource.LoginTimeoutDatasourceAdapter.getConnection(LoginTimeoutDatasourceAdapter.java:62)
at oracle.odi.core.security.SecurityManager.retrieveUserGuid(SecurityManager.java:625)
at oracle.odi.core.security.SecurityManager.createAuthentication(SecurityManager.java:471)
at oracle.odi.core.security.SecurityManager.createAuthentication(SecurityManager.java:252)
at oracle.odi.repex.model.platform.SecurityService.login(SecurityService.java:22)
at oracle.odi.em.utils.AuthUtil.authenticate(AuthUtil.java:59)
at oracle.odi.em.utils.AuthUtil$EMAuthenticatedAction.execute(AuthUtil.java:182)
at oracle.odi.em.mbeans.impl.AgentRunTime.fetchMasterRepoId(AgentRunTime.java:436)
at oracle.odi.em.mbeans.impl.AgentRunTime.reInitializeJndiMasterRepoIdMap(AgentRunTime.java:86)
at oracle.odi.em.scheduler.ReinitializerBean.reInitializeJndiMasterRepoIdMap(ReinitializerBean.java:77)
at oracle.odi.em.scheduler.ReinitializerBean.reInitJndiMasterRepoIdMap(ReinitializerBean.java:65)
at oracle.odi.em.scheduler.ReinitializerBean.run(ReinitializerBean.java:42)
at java.util.TimerThread.mainLoop(Timer.java:512)
at java.util.TimerThread.run(Timer.java:462)

at oracle.odi.core.datasource.dwgobject.support.DataSourceDefinitionFactory.getDataSourceDefinition(DataSourceDefinitionFactory.java:72)
at oracle.odi.core.datasource.dwgobject.support.DwgConnectConnectionCreatorImpl.createDwgConnectConnection(DwgConnectConnectionCreatorImpl.java:80)
at com.sunopsis.dwg.dbobj.SnpSession.createDwgConnectConnection(SnpSession.java:3986)
at com.sunopsis.dwg.dbobj.SnpSessTask.getDefaultDwgConnectConnection(SnpSessTask.java:513)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.getExecutor(SnpSessTaskSql.java:2960)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.processTask(SnpSessTaskSql.java:2901)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2625)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:577)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:468)
at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:2093)
at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:1895)
at oracle.odi.runtime.agent.processor.impl.StartScenRequestProcessor$2.doAction(StartScenRequestProcessor.java:580)
at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)
at oracle.odi.runtime.agent.processor.impl.StartScenRequestProcessor.doProcessStartScenTask(StartScenRequestProcessor.java:512)
at oracle.odi.runtime.agent.processor.impl.StartScenRequestProcessor$StartScenTask.doExecute(StartScenRequestProcessor.java:1068)
at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:137)
at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)
at java.lang.Thread.run(Thread.java:662)
Caused by: com.sunopsis.dwg.SQLMasterReposException: Connection has been administratively destroyed because the pool got too many consecutive failed connection tests, and failures to obtain replacement connections from the DBMS. Please see the server log entries at that time for more details. Reconnect. java.lang.Exception: It was destroyed at Wed Jul 10 01:29:17 IST 2013
at weblogic.jdbc.common.internal.ConnectionEnv.destroy(ConnectionEnv.java:616)
at weblogic.common.resourcepool.ResourcePoolImpl.destroyResource(ResourcePoolImpl.java:1887)
at weblogic.common.resourcepool.ResourcePoolImpl.reclaimReservedResources(ResourcePoolImpl.java:1539)
at weblogic.common.resourcepool.ResourcePoolImpl.disablePool(ResourcePoolImpl.java:1515)
at weblogic.common.resourcepool.ResourcePoolImpl.processFailCountExceededDisableThreshold(ResourcePoolImpl.java:1829)
at weblogic.common.resourcepool.ResourcePoolImpl.incrementResetFailCount(ResourcePoolImpl.java:1815)
at weblogic.common.resourcepool.ResourcePoolImpl.makeResources(ResourcePoolImpl.java:1322)
at weblogic.common.resourcepool.ResourcePoolImpl.makeResources(ResourcePoolImpl.java:1236)
at weblogic.common.resourcepool.ResourcePoolImpl.reserveResourceInternal(ResourcePoolImpl.java:460)
at weblogic.common.resourcepool.ResourcePoolImpl.reserveResource(ResourcePoolImpl.java:352)
at weblogic.common.resourcepool.ResourcePoolImpl.reserveResource(ResourcePoolImpl.java:339)
at weblogic.jdbc.common.internal.ConnectionPool.reserve(ConnectionPool.java:491)
at weblogic.jdbc.common.internal.ConnectionPool.reserve(ConnectionPool.java:384)
at weblogic.jdbc.common.internal.ConnectionPoolManager.reserve(ConnectionPoolManager.java:125)
at weblogic.jdbc.common.internal.RmiDataSource.getPoolConnection(RmiDataSource.java:454)
at weblogic.jdbc.common.internal.RmiDataSource.getConnectionInternal(RmiDataSource.java:538)
at weblogic.jdbc.common.internal.RmiDataSource.getConnection(RmiDataSource.java:498)
at weblogic.jdbc.common.internal.RmiDataSource.getConnection(RmiDataSource.java:491)
at org.springframework.jdbc.datasource.UserCredentialsDataSourceAdapter.doGetConnection(UserCredentialsDataSourceAdapter.java:162)
at org.springframework.jdbc.datasource.UserCredentialsDataSourceAdapter.getConnection(UserCredentialsDataSourceAdapter.java:133)
at oracle.odi.jdbc.datasource.LoginTimeoutDatasourceAdapter.doGetConnection(LoginTimeoutDatasourceAdapter.java:99)
at oracle.odi.jdbc.datasource.LoginTimeoutDatasourceAdapter.getConnection(LoginTimeoutDatasourceAdapter.java:62)
at oracle.odi.core.security.SecurityManager.retrieveUserGuid(SecurityManager.java:625)
at oracle.odi.core.security.SecurityManager.createAuthentication(SecurityManager.java:471)
at oracle.odi.core.security.SecurityManager.createAuthentication(SecurityManager.java:252)
at oracle.odi.repex.model.platform.SecurityService.login(SecurityService.java:22)
at oracle.odi.em.utils.AuthUtil.authenticate(AuthUtil.java:59)
at oracle.odi.em.utils.AuthUtil$EMAuthenticatedAction.execute(AuthUtil.java:182)
at oracle.odi.em.mbeans.impl.AgentRunTime.fetchMasterRepoId(AgentRunTime.java:436)
at oracle.odi.em.mbeans.impl.AgentRunTime.reInitializeJndiMasterRepoIdMap(AgentRunTime.java:86)
at oracle.odi.em.scheduler.ReinitializerBean.reInitializeJndiMasterRepoIdMap(ReinitializerBean.java:77)
at oracle.odi.em.scheduler.ReinitializerBean.reInitJndiMasterRepoIdMap(ReinitializerBean.java:65)
at oracle.odi.em.scheduler.ReinitializerBean.run(ReinitializerBean.java:42)
at java.util.TimerThread.mainLoop(Timer.java:512)
at java.util.TimerThread.run(Timer.java:462)

at weblogic.jdbc.common.internal.ConnectionEnv.checkIfEnabled(ConnectionEnv.java:1128)
at weblogic.jdbc.wrapper.PoolConnection.checkConnection(PoolConnection.java:63)
at weblogic.jdbc.wrapper.Connection.preInvocationHandler(Connection.java:100)
at weblogic.jdbc.wrapper.Connection.prepareStatement(Connection.java:551)
at com.sunopsis.sql.SnpsQuery.getPreparedStatement(SnpsQuery.java:975)
at com.sunopsis.sql.SnpsQuery.getPreparedStatement(SnpsQuery.java:1016)
at com.sunopsis.sql.SnpsQuery.updateExecStatement(SnpsQuery.java:1850)
at com.sunopsis.sql.SnpsQuery.executeQuery(SnpsQuery.java:595)
at com.sunopsis.dwg.dbobj.generated.GeneratedSnpAgent.getObjectLst(GeneratedSnpAgent.java:789)
at com.sunopsis.dwg.dbobj.SnpAgent.getAgentByName(SnpAgent.java:153)
at com.sunopsis.dwg.dbobj.SnpDatasource.getSnpDatasourceByIConnectAndAgentName(SnpDatasource.java:50)
at oracle.odi.core.datasource.dwgobject.support.DataSourceDefinitionFactory.getDataSourceDefinition(DataSourceDefinitionFactory.java:60)
… 17 more
—————————-

We are new to ODI, so, we are unable to find the solution to resolve the issue. Please guide me on this.

Thanks in Advance.

Raghu Nagadasari

Posted in OBIA 11.1.1.7.1, ODI | 1 Comment