How to know RPD Password of any Repository deployed in server

Hi Friends,

Some way or other we overcome with multiple issues, where we are in need to know if by any means rpd password is set wrong in OBIEE 11g.

For 10g it was easy and we can easily bypass security by modifying NQSConfig.INI file. But this is not applicable for OBIEE 11g.

However we had the solution till OBIEE version 11.1.1.7 to get rep password through process as below: ( Only applicable till versions 11.1.1.7)

Pre-Requisite - You must have Admin Rights to access weblogic & OS to find it. You can not bypass security. :)

Step-1 Navigate to your ORACLE_HOME, then go to common/bin path under ORACLE_HOME, where you can get wlst.sh / wlst.cmd command to execute WLST.

wls:/offline>
(Note - You may ignore warning for insecure protocol)

Step-2 - Connect online with connect command with your weblogic admin credentials.

Format -
connect(“<weblogic_AdminUser>”,”<weblogic_AdminUser_Password>”,”<Adminserver_Hostname>:<Admin_Portno>”)

eg.
connect("weblogic","weblogic123","t3://HOST_NAME:7001")

You are now connected to wlst online :
wls:/bifoundation_domain/serverConfig>

Step-3 Execute listCred command online

Format -
wls:/bifoundation_domain/serverConfig> listCred(map="mapName", key="keyName")

wls:/bifoundation_domain/serverConfig> listCred(map="oracle.bi.enterprise", key="repository.SampleAppLiteBI0003")


But as mentioned earlier this is applicable till obiee 11.1.1.7 versions. What if we have version greater than 11.1.1.7.

While running ListCred command we get error as below:

Traceback (innermost last):
File "<console>", line 1, in ?
NameError: listCred
wls:/bifoundation_domain/serverConfig>

Reason:
Oracle has removed listCred wlst command and is no longer available

So what is the alternative ???

It is available Folkes... :)

Solution:

1) Log into to EM console, navigate to Weblogic Domain ->Domain Name ->System Mbean Browser
Under Application Defined Mbean go to com.oracle.jps ->Domain:<domain_name> -> JpsCredentialStore ->JpsCredentialStore
As shown in figure below:

2) Click Operation tab and select getPortableCredential






3) Enter both the parameters as shown
Enter map value in first text box & key value in second text box


4) Click on Invoke to get the password

You will get your password under Return Value Panel password field.

:)

Merry Christmas & Happy New Year to all.

:)





Weblogic console & OEM Fusion Middlewere Not opening in OBIEE 11g

Hi Friends,

Some days back i faced a weird issue in OBIEE 11g server, where all opmn components were up & analytics was logining fine. But as an admin i had to do manual repository migration through Oracle Enterprise Manager Fusion Middleware. Considering that you might be knowing the process of rpd migration through OEM Fusion middleware, i am mentioning here for the solution to the issue i faced.


Issue - Weblogic console & OEM Fusion Middlewere Not opening.

Major Identification - While validating Admin Server Logs from
$DOMAIN_HOME/servers/AdminServer/logs/AdminServer.log

[Note - To save time always search logs with respect to timestamp, there can be many other techniques]

On Validating the AdminServer.log, i get the error as below:
-------------------------------------------------------------------------------------------------------------------------------
####<Apr 19, 2015 1:21:09 PM EDT> <Critical> <WebLogicServer> <$HOSTNAME> <AdminServer> <main> <<WLS Kernel>> <> <> <1429464069767> <BEA-000386> <Server subsystem failed. Reason: java.lang.NumberFormatException: null
java.lang.NumberFormatException: null
at java.lang.Integer.parseInt(Integer.java:417)
at java.lang.Integer.parseInt(Integer.java:499)
at weblogic.ldap.EmbeddedLDAP.validateVDEDirectories(EmbeddedLDAP.java:1104)
at weblogic.ldap.EmbeddedLDAP.start(EmbeddedLDAP.java:242)
at weblogic.t3.srvr.SubsystemRequest.run(SubsystemRequest.java:64)
at weblogic.work.ExecuteThread.execute(ExecuteThread.java:209)
at weblogic.work.ExecuteThread.run(ExecuteThread.java:178)

-------------------------------------------------------------------------------------------------------------------------

Root Cause –
Server subsystem failed. Reason: java.lang.NumberFormatException: null

Error points to Embedded LDAP for Weblogic

Embedded LDAP file (From Path - $DOMAIN_HOME/servers/AdminServer/data/ldap/conf) - replicas.prop size was 0 byte.

Action Taken –
Renamed replicas.prop to replicas.prop_old & restarted admin services.

Result - Success :) ...
Admin Services started & new replicas.prop ( embedded ldap server file with 14 bytes in my case) file created. Weblogic Console & Oracle Fusion Middleware also started working fine.

Hope it helps all in need :)

Enjoy


Write Back Setting in OBIEE 11G

Write Back is the ability to enter values directly into a report and have those values written in the database & used in calculations and charts in the report.
Please follow the steps listed below to configure Write Back in OBIEE 11g.

A- Repository Level Changes in all 3 layers to set Write Back Column :

Step 1:Physical Layer - From Physical Table Properties Go to-> General tab dialog box. Uncheck Cacheable

Step 2: Business Model & Mapping Layer -  Logical Column Properties Go to-> General tab. Check Writable option.

Step 3: Presentation Layer - Presentation Column Properties Go to-> General tab -> Permissions -> Set permission -> Read / Write (Radio Button), on the User / Application Role which you want to authenticate for WriteBack feature.

Step 4Presentation Layer - Go to -> Manage-> Identity Manager -> Identity Management (Left pane) -> Select Application Role (to which you need Write Back Permission) -> Permission -> Query Limits (tab) -> Select Database (to which you need direct database execution rights) -> Change option from ignore to allow to the field named "Execute Direct Database Requests".

Now we are done with Repository level changes for WriteBack column. Let us proceed with other changes.

B- Changes in the File Level :

Step 1: Enable Write Back. Add the LightWriteback tag within  the serverinstance tag of instanceconfig.xml file  ( file path - $ORACLE_INSTANCE/config/OracleBIPresentationServicesComponent/coreapplication_obips1 )

<LightWriteback>true</LightWriteback>

If this entry already exists then no modifications required, else restart OracleBIPresentationServicesComponent for this change to be effective.

Step 2: WriteBack Template. You may give any name for the writeback.xml file. Here for example i am using the file name as writeback_sample.xml

Things to do before you start the Template:

  • Identify the columns that are to be referenced. 
  • We can use the column position or by column id in the XML definition. 
  • We must include both insert and update statements in the template.
Template to be placed in the path:
$ORACLE_INSTANCE/bifoundation/OracleBIPresentationServicesComponent/coreapplication_obips1/analyticsRes/customMessages

Write-Back Template example: Name - eg. writeback_sample.xml
=================================================
<?xml version="1.0" encoding="utf-8" ?>
<WebMessageTables xmlns:sawm="com.siebel.analytics.web/message/v1">
<WebMessageTable lang="en-us" system="WriteBack" table="Messages">
   <WebMessage name="WriteBack_Template_Name">
      <XML>
         <writeBack connectionPool="Connection_pool_name">
            <insert>INSERT INTO Customer VALUES('@1','@2',@3,'@4',@5)</insert>
            <update>UPDATE Customer set Address='@2' where Name='@1'</update>
<!-- Identify the columns that are to be referenced. We can use the column position like @1 for first column, or by column id as you see them in the XML definition. -->
         </writeBack>
      </XML>
   </WebMessage>
<WebMessage name="Template_Name">
      <XML>
         <writeBack connectionPool="ConnectionPool_Name">
            <insert></insert>
            <update>UPDATE Product set Prod_Description='@2' where Name='@1'</update>
         </writeBack>
      </XML>
   </WebMessage>
<WebMessage name="Template_Name">
      <XML>
         <writeBack connectionPool="ConnectionPool_Purchase">
            <insert>INSERT INTO Supplier VALUES('@1','@2',@3,@4)</insert>
            <update></update>
         </writeBack>
      </XML>
   </WebMessage>
</WebMessageTable>
</WebMessageTables>
<!-- We may also use multiple insert / update commands for same or different connection pools as well based on our need -->
==================================================
[ Note - We need to mention insert & update statements based on our requirement. Above insert & update code is only for sample purpose & content within <!-- #### --> are comments. If we do not want to include SQL commands within the elements, then we must insert a blank space between the opening and closing tags. eg. <insert> </insert> rather than <insert></insert>]

In the above xml file...
Important Tags -

  • WebMessage name - The name here will be used as WriteBack template name in analysis -> Table Properties -> Write Back.
  • Write Back connectionPool - Connection pool name for Write Back.
  • insert - Based on your requirement or leave blank ( if not required)
  • update - Based on your requirement or leave blank ( if not required)
Now we are done with changes in the files. Lets proceed further & know what changes we need to make in analysis for the writeback report...

C- Changes in Analytics :

Step 1Give Privilege to the Role for Write Back ( Administration -> Manage Privilege -> Write Back & add the role for writeback privilege). 

Step 2Now you need to edit Column Properties -> writeback -> Check 'Enable Write Back' of the field needed for writeback, from criteria tab.

Step 3: Now edit Table Properties view -> writeback & check 'Enable Write Back' & mention Template Name as mentioned in writeback file ( WebMessage name="WriteBack_Template_Name). You may also rename Apply, Revert & Done Button & change Button Position if required.

You are now done with all WriteBack settings. You may test the report for set writeback features.

OBIEE enterprise manager adf faces 60097


Today I faced one issue in newly built server while uploading rpd for migration through EM.

I noticed error from coreapplication as :


Then Navigated to MBean to try my luck for rpd uploading from there, but all parameters were showing unavailable Value.




I explored the solution to it & identified as file - <OBIEE_HOME>/user_projects/domains/bifoundation_domain/sysman/mds/partition1/ai/bi/fragments/mdssys/cust/user/weblogic/deployment.jspx.xml was causing the issue. I deleted it & proceeded with full bounce. 
[ Note - deployment.jspx.xml file will get created automatically after you navigate to coreapplication in enterprise manager. But take the backup of the file before deleting it. ]

The issue resolved then & coreapplication & MBean both working fine thereafter...


deployment.jspx.xml got created automatically after navigating to coreapplication from Enterprise Manager.



Math Functions in OBIEE

There are many science, research and development functions which are not much explored or used in business intelligence. Most of such functions belong to Math Functions. I am listing below all Math Functions.

Math Functions

Abs, Acos, Asin, Atan, Atan2, Ceiling, Cos, Cot, Degrees, Exp, Floor, Log, Log10, Mod, Pi, Power, Radians, Rand, RandFromSeed, Round, Sign, Sin, Sqrt, Tan, Truncate

As I am not working in any scientific or research & development project, so I decided to manually create such functions, to describe math functions. I then thought to randomly select some of trigonometric functions & test result through their graph, so I choose sin, cos and tan. But for trigonometric function I had to further make use of pi (you may use degrees as well).

The functions applied in my case for different functions is as below:
Sin - SIN((RCOUNT(1)-1)*PI()/4)
Cos - COS((RCOUNT(1)-1)*PI()/4)
Tan - TAN((RCOUNT(1)-1)*PI()/4)
Angle in Degree - CEILING(DEGREES((RCOUNT(1)-1)*PI()/4))

Where RCOUNT(1)-1 gives numeric value starting from zero & 1 in RCOUNT(1) is numeric symbolizing first column with distinct data & PI()/4 is used to multiply each count to pi/4 ( quarter of semicircle).
Math Function Test
Test Maths
Calculated Attributes
Angle in Degree
Sine Value
Cos Value
Tan Value
0
0
1
0
45
0.707
0.707
1
90
1
0
6.18986E+14
135
0.707
-0.707
-1
180
0
-1
0
225
-0.707
-0.707
1
270
-1
0
2.16235E+14
315
-0.707
0.707
-1
360
0
1
0
405
0.707
0.707
1
450
1
0
1.20485E+14
495
0.707
-0.707
-1
540
0
-1
0
585
-0.707
-0.707
1
630
-1
0
9.01976E+13
675
-0.707
0.707
-1
720
0
1
0
765
0.707
0.707
1
810
1
0
6.77417E+13
855
0.707
-0.707
-1







\





















So as verifying from the graph & tabular value, it is clear to make use of them, leaving tan pi/2 as an exception of giving a finite value. However still aviating, some more scientific calculus functions, which will make obiee full package to be used in scientific research & development projects.
J






To Get Age between two Days in OBIEE Report

For Getting Age between two dates we use SQL Query as :

datediff(d, startdate, enddate) as Age

However default date format in OBIEE is Timestamp. So you need timestamp function (Available under Calendar/Date Function Heading) to get required result.

Syntax:

TIMESTAMPDIFF(interval, timestamp1, timestamp2)

Where:
interval is the specified interval. Valid values are:

SQL_TSI_SECOND

SQL_TSI_MINUTE

SQL_TSI_HOUR

SQL_TSI_DAY

SQL_TSI_WEEK

SQL_TSI_MONTH

SQL_TSI_QUARTER

SQL_TSI_YEAR

timestamp1 and timestamp2 are any valid timestamps.

Example
For Number of Days between two days:
TimestampDiff(SQL_TSI_DAY, “FROM_DATE_COLUMN”, “TO_DATE_COLUMN”)

For Number of Days till current date:
TimestampDiff(SQL_TSI_DAY, “FROM_DATE_COLUMN”, CURRENT_DATE)

*You can also use this function for getting age difference for other time dimension attributes as week, quarter, month or year. All you need to do is change first attribute i.e 'interval' & other attribute's accordingly.

Creating Aggregate Tables in OBIEE


Steps for Creating Aggregate Tables in OBIEE

  1. Create all Dimension Tables, Fact Tables & Hierarchies, which are required to be aggregated.
  2. Go to ToolsàUtilities (from Administration tool menu bar), a dialog box will pop up with all available utilities.
  3. Select Aggregate Persistence Wizard, and then click Execute Button. (See Figure Below).


4.       Now Select appropriate path where you need to generate Aggregate Table SQL.

Note : Click Generate DDL file for first time generation of Aggregate Table. (See Figure Below)


  1. Click Next, to move to next page (Select Business Model & Measure Page)
  2. In Select Business Model & Measure Page, Select Appropriate Business Model & then select associated Fact / Measure.(See Figure Below).


     7.       Click next, to move to next page (Select Dimensions & Levels).

  1. Select appropriate level of dimension & check Use Surrogate Key.



9.       Click next, to move to next page (Select output Connection Pool, Container & Name).





  1. Click next, to move to next page (Aggregate Definition).
  2. Select I am Done (Radio Button).
  3. Click Next.



  1. Then Click Finish. Your Aggregate Table is Created Now & available at the path you specified in “Select File Location” Page.
  2. To view generated script move to - C:\Agg\
  3. Select The Aggregate Table Created & View the Code. Code for above process is mentioned below:

delete aggregates; /* Required only first time, so that any further aggregates can be deleted */
/*However if you create other Aggregate, you dont need it. */ 

create aggregates

"ag_FACTINTERNETSAL"

 for "SALES"."FACTINTERNETSALES"("ORDERQUANTITY")

 at levels ("SALES"."Time"."Year" using_surrogate_key )

 using connection pool "Adventure Works"."Agg CP"

 in "Adventure Works"."AdventureWorksDW2008";

  1. Now the script is ready, we run it using the “nqcmd.exe” utility in the /OracleBI/server/bin directory.

Steps for running Aggregate Script are mentioned below:

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\gaurav.mishra>cd\

C:\>cd oraclebi\server\bin (Click Enter)

C:\OracleBI\server\Bin>nqcmd.exe -u Administrator -p Administrator -d analyticsweb -s \Agg\agg.sql

[ Where : u (Admin User id)  p (Password)  d (analyticsweb)  s (script path) ]
-------------------------------------------------------------------------------
          Oracle BI Server
          Copyright (c) 1997-2009 Oracle Corporation, All rights reserved
-------------------------------------------------------------------------------


delete aggregates
delete aggregates

Statement execute succeeded


create aggregates

"ag_FACTINTERNETSAL"
 for "SALES"."FACTINTERNETSALES"("ORDERQUANTITY")
 at levels ("SALES"."Time"."Year" using_surrogate_key )
 using connection pool "Adventure Works"."Agg CP"
 in "Adventure Works"."AdventureWorksDW2008"
create aggregates

"ag_FACTINTERNETSAL"
 for "SALES"."FACTINTERNETSALES"("ORDERQUANTITY")
 at levels ("SALES"."Time"."Year" using_surrogate_key )
 using connection pool "Adventure Works"."Agg CP"
 in "Adventure Works"."AdventureWorksDW2008"

Statement execute succeeded


Processed: 2 queries

C:\OracleBI\server\Bin>

 
After getting successful aggregate script execution, you need to restart all BI Services.

Now Open your metadata Repository & you will see that new aggregate tables created and registered, and shown in red to show they’re aggregates.


Note : No change in presentation layer, as data is executed from logical table & physical table, so presentation layer remains unaffected.