Thursday, October 4, 2018

Good Excuses

Every now and then I had a post like this to justify my lack of new posts on my blog. This time I hit the record: 5.5 years! But I have a super good excuse: I am now managing these products :) the very same products I started blogging about 8 years ago.

I'm no longer a consultant, no longer an architect, I'm still Oracle products' biggest fan and I still totally love what I am doing in the OBI space.

In the next post (hopefully not due to be published in 5 other yrs) I will write more about OTBI, Data Visualisation, OAC, Next-Gen Analytics and the other cool stuff I co-create with my super intelligent Product Management team-mates.



Friday, November 15, 2013

EBS Reports to Publisher

As a starting point please read Converting Reports from Oracle Reports to Oracle BI Publisher in Release 10.1.3.3
 
If you want to convert *.rdf Oracle Reports then you need to install Oracle Reports Designer 9i or later on the same machine where you will do the conversion (you can find that in full installation of Oracle Developer Suite)
 
Check Java configuration by executing in command line:
java -version
The conversion utility requires JDK version 1.1.8 or later. If you see an older version of java then amend your "Path" system variable to point into correct java folder (assuming there is a valid version of JDK in your main java folder, for example in Program Files). Apply changes, open new cmd window and try again. If that doesn't work than you might need to execute "C:\Program Files\Java\jdk1.6.0_21\bin\java.exe" instead of "java"
 
Check available libraries in BI Publisher folder, for example in C:\oracle\OracleBI\oc4j_bi\j2ee\home\applications\xmlpserver\xmlpserver\WEB-INF\lib\
Some libraries might have slightly different names than described in documentation (on my environment there was collection.jar and xmlparserv2.jar instead of collections.zip and xmlparserv2-904.jar)
 
Copy all reports into one folder and execute conversion command similar to this:
java -classpath C:\oracle\OracleBI\oc4j_bi\j2ee\home\applications\xmlpserver\xmlpserver\WEB-INF\lib\xdocore.jar;C:\oracle\OracleBI\oc4j_bi\j2ee\home\applications\xmlpserver\xmlpserver\WEB-INF\lib\collections.jar;C:\oracle\OracleBI\oc4j_bi\j2ee\home\applications\xmlpserver\xmlpserver\WEB-INF\lib\aolj.jar;C:\oracle\OracleBI\oc4j_bi\j2ee\home\applications\xmlpserver\xmlpserver\WEB-INF\lib\xmlparserv2.jar 
oracle.apps.xdo.rdfparser.BIPBatchConversion
-source C:\projects\reports2bipublisher\source
-target C:\projects\reports2bipublisher\target
-oraclehome C:\oracle\DevSuiteHome_1
 
 
In output folder look for a log file that contains issues/warnings during the conversion and a log of unconverted objects from the Reports definition file (RDF).
After the automatic reports conversion with the tool you will still need to perform the post conversion tasks, which include deployment of the PLSQL package, manual layout adjustment, additional conditional formatting and calculations for the RTF Template, etc.
 
Here is a list of typical manual tasks required for RTF Template:
  1. PLSQL Format Triggers – In Oracle Reports you can use PLSQL format triggers to format data in the reports. The utility creates a placeholder for the all PLSQL format triggers but without adding any functionality. You need to write code in the BI Publisher RTF Template to enable the formatting trigger functionality.
  2. Color Coding of the Layout - You need to add all color coding manually.
  3. Number/Date Formatting - The BI Publisher report uses its default data formats and not the formats in the original report. Items need to be assigned formats manually.
  4. Images – Images are not converted. You need to manually add the images
 
The automatic reports conversion tool moves all pl/sql logic into database package. Review database package.
  1. Formula columns are converted into package functions - that usually works fine. Remove unnecessary calls to package functions by placing that logic into query. That simplifies report and improves performance.
  2. Placeholders are converted into global package variables - that won't work. Remove all global package variables. Replace that logic with simple function returning single value which you could call directly from sql query.
  3. Review RDF triggers. Most of that functionality is not required in BI Publisher and those procedures could be removed.
  4. A RDF validation triggers for parameter form are converted into database package but those are not used anymore and should be removed from that package.
  5. Names of functions' parameters might require changing. Example of bad conversion (that parameter has the same name as table column and it needs to be changed):
    RDF function:
    Function get_desc return varchar2 is
      v_desc varchar2;
    begin
      select description
      into v_desc
      from organization
      where organization_id = :organization_id;
      return v_desc;
    end;Database package function:
    Function get_desc(organization_id in number) return varchar2 is
      v_desc varchar2;
    begin
      select description
      into v_desc
      from organization
      where organization_id = organization_id;
      return v_desc;
    end;
 
 

Thursday, November 7, 2013

OBIA Configuration SQLs

EBS Configuration SQL

 
1) Determine NLS_LANG
 
SELECT * FROM V$NLS_PARAMETERS WHERE PARAMETER IN ( 'NLS_LANGUAGE', 'NLS_TERRITORY', 'NLS_CHARACTERSET' )
 
2) Determine up Currency code
 
Select currency_code from gl_set_of_books where consolidation_sob_flag='Y'
 
3) Get SOB  - To Limit ETL to a set of books
 
select set_of_books_id, name
From gl_set_of_books;
 
 
4) Limit $$INitial Extract Date
 
SELECT PERIOD_YEAR,
       PERIOD_NUM,
       to_char(START_DATE, 'YYYYMMDD')
from   GL_PERIOD_STATUSES
where  set_of_books_id = '289'
and    period_year between 2002 and 2009
and    application_id = 101
and    adjustment_period_flag = 'N' order by period_year, period_num;
 
5) Determine Code Combination for a Chart of account and list natural accounts within this COA  
 

This query gives us a list of the code combinations that make up an COA and we need to find the flex_value_set_id for the natural account, enter the sob as a parameter to this query
 
 
Column Name Segment Name Natural Account Flex_Value_Set_Id
Segment1 Organisation

Segment2 Cost Centre

Segment3 Account Y 1007947
Segment4 Analysis

Segment5 Output

Segment6 Public Services

 
 
SELECT sob.name sob_name,  sob.set_of_books_id sob_id,  sob.chart_of_accounts_id coa_id,  fifst.id_flex_structure_name struct_name,  ifs.segment_name,  ifs.application_column_name column_name,  sav1.attribute_value balancing,  sav2.attribute_value cost_center,  sav3.attribute_value natural_account,  sav4.attribute_value intercompany,  sav5.attribute_value secondary_tracking,  sav6.attribute_value GLOBAL,  ffvs.flex_value_set_name,  ffvs.flex_value_set_id
FROM fnd_id_flex_structures fifs,  fnd_id_flex_structures_tl fifst,  fnd_segment_attribute_values sav1,  fnd_segment_attribute_values sav2,  fnd_segment_attribute_values sav3,  fnd_segment_attribute_values sav4,  fnd_segment_attribute_values sav5,  fnd_segment_attribute_values sav6,  fnd_id_flex_segments ifs,  fnd_flex_value_sets ffvs,  gl_sets_of_books sob
WHERE 1 = 1
AND fifs.id_flex_code = 'GL#' AND fifs.application_id = fifst.application_id AND fifs.id_flex_code = fifst.id_flex_code AND fifs.id_flex_num = fifst.id_flex_num AND fifs.application_id = ifs.application_id AND fifs.id_flex_code = ifs.id_flex_code AND fifs.id_flex_num = ifs.id_flex_num AND sav1.application_id = ifs.application_id AND sav1.id_flex_code = ifs.id_flex_code AND sav1.id_flex_num = ifs.id_flex_num AND sav1.application_column_name = ifs.application_column_name AND sav2.application_id = ifs.application_id AND sav2.id_flex_code = ifs.id_flex_code AND sav2.id_flex_num = ifs.id_flex_num AND sav2.application_column_name = ifs.application_column_name AND sav3.application_id = ifs.application_id AND sav3.id_flex_code = ifs.id_flex_code AND sav3.id_flex_num = ifs.id_flex_num AND sav3.application_column_name = ifs.application_column_name AND sav4.application_id = ifs.application_id AND sav4.id_flex_code = ifs.id_flex_code AND sav4.id_flex_num = ifs.id_flex_num AND sav4.application_column_name = ifs.application_column_name AND sav5.application_id = ifs.application_id AND sav5.id_flex_code = ifs.id_flex_code AND sav5.id_flex_num = ifs.id_flex_num AND sav5.application_column_name = ifs.application_column_name AND sav6.application_id = ifs.application_id AND sav6.id_flex_code = ifs.id_flex_code AND sav6.id_flex_num = ifs.id_flex_num AND sav6.application_column_name = ifs.application_column_name AND sav1.segment_attribute_type = 'GL_BALANCING' AND sav2.segment_attribute_type = 'FA_COST_CTR' AND sav3.segment_attribute_type = 'GL_ACCOUNT' AND sav4.segment_attribute_type = 'GL_INTERCOMPANY' AND sav5.segment_attribute_type = 'GL_SECONDARY_TRACKING' AND sav6.segment_attribute_type = 'GL_GLOBAL' AND ifs.id_flex_num = sob.chart_of_accounts_id AND ifs.flex_value_set_id = ffvs.flex_value_set_id -- comment the next expression to show all books-- currently it show the info for the site level set profile option value-- and    sob.set_of_books_id = nvl(fnd_profile.value('GL_SET_OF_BKS_ID'),sob.set_of_books_id)AND sob.name = 'Progress UK'ORDER BY sob.name,  sob.chart_of_accounts_id,  ifs.application_column_name;
 
 
 
This next query returns all of the natual accounts for the flex_value_set
 
SELECT DISTINCT FND_FLEX_VALUES.FLEX_VALUE_SET_ID, FND_FLEX_VALUES.FLEX_VALUE, FND_FLEX_VALUES_TL.DESCRIPTION,substr(FND_FLEX_VALUES.COMPILED_VALUE_ATTRIBUTES,7,1) AS Account_Type-- FND_FLEX_VALUES.COMPILED_VALUE_ATTRIBUTES,FND_FLEX_VALUES.SUMMARY_FLAG
FROM FND_FLEX_VALUES, FND_FLEX_VALUES_TL, FND_ID_FLEX_SEGMENTS, FND_SEGMENT_ATTRIBUTE_VALUES
WHERE FND_FLEX_VALUES.FLEX_VALUE_ID = FND_FLEX_VALUES_TL.FLEX_VALUE_ID AND FND_FLEX_VALUES_TL.LANGUAGE ='US'AND FND_ID_FLEX_SEGMENTS.FLEX_VALUE_SET_ID =FND_FLEX_VALUES.FLEX_VALUE_SET_ID AND FND_ID_FLEX_SEGMENTS.APPLICATION_ID = 101 AND FND_ID_FLEX_SEGMENTS.ID_FLEX_CODE ='GL#' AND FND_ID_FLEX_SEGMENTS.ID_FLEX_NUM =FND_SEGMENT_ATTRIBUTE_VALUES.ID_FLEX_NUM AND FND_SEGMENT_ATTRIBUTE_VALUES.APPLICATION_ID =101 AND FND_SEGMENT_ATTRIBUTE_VALUES.ID_FLEX_CODE = 'GL#' AND FND_ID_FLEX_SEGMENTS.APPLICATION_COLUMN_NAME=FND_SEGMENT_ATTRIBUTE_VALUES.APPLICATION_COLUMN_NAME AND FND_SEGMENT_ATTRIBUTE_VALUES.SEGMENT_ATTRIBUTE_TYPE ='GL_ACCOUNT' AND FND_SEGMENT_ATTRIBUTE_VALUES.ATTRIBUTE_VALUE ='Y'and FND_FLEX_VALUES.FLEX_VALUE_SET_ID = '1007947'-- Ignore Summary Accountsand FND_FLEX_VALUES.SUMMARY_FLAG = 'N'-- Only Revenue Accounts-- and substr(FND_FLEX_VALUES.COMPILED_VALUE_ATTRIBUTES,7,1) NOT IN ('A','E','L','O') ;

Tuesday, October 15, 2013

OBIEE 11g customise

The information related to style and skin can be found under u01/app/oracle/product/obiee1111/ORACLE_BI1/bifoundation/web/app/res  >> sk_blaf/b_mozilla_4

In this example, we are going to change the logo of BI

1. Added the following to instanceconfig.xml file:

/u01/app/oracle/product/obiee1111/instances/instance1/bifoundation/OracleBIPresentationServicesComponent/coreapplication_obips1/analyticsRes


before



2. http://verisklinux:7001/console. navigate to Deployments > Install. Deploy analyticsRes from

/u01/app/oracle/product/obiee1111/instances/instance1/bifoundation/OracleBIPresentationServicesComponent/coreapplication_obips1 (see screenhsots)

Activate Changes

 
 
 
 
 


 
 
 
 
 
 
 
 

 
 
 
 
 
 
 

 
 
 
 
 
 
 
 
 

 
 
 
3.Start AnalyticsRes. make sure that its status changes to Active.

4. To test my deployment, added test.txt to the analyticsRes folder and access it via
    http://verisklinux:9704/analytics/test.txt

5. Copy the oracle standard skins to my deployment folder
cp "/u01/app/oracle/product/obiee1111/Oracle_BI1/bifoundation/web/app/res/sk_blafp/" /u01/app/oracle/product/obiee1111/instances/instance1/bifoundation/OracleBIPresentationServicesComponent/coreapplication_obips1/analyticsRes/sk_verisk

6. Edit instanceconfig.xml and add the following:
    verisk

7. Restart BI Services

Tuesday, October 8, 2013

Efficient idea on how to implement a report that shows the impact of column (at all levels) on reports in OBIEE PS:

http://www.kpipartners.com/blog/bid/151016/Build-OBIEE-Reports-For-Impact-Analysis-Data-Lineage

Monday, March 18, 2013

Suspension

I haven't posted here in a very long time; not because I am not into OBIEE anymore but because I have taken the role of BI Lead Consultant in CapGemini FS and this translates into hardly having any time for blogging anymore. I have tones of articles that I could write about and I come across so many interesting situations that would be worth sharing but unfortunately time is so limited that probably I will post only once in a while

'Till free(er) times.

OBIA Software Requirements

Certification

Software Downloads on eDelivery

The software requirements for OBIA can be mainly be downloaded from edelivery.oracle.com or otn.com:
 
edelivery.oracle.com
 
  • Product Pack:     Oracle Business Inteligence
  • Platform:             Linux x86-64 / Microsoft Windows (32-bit)

Business Analytics Warehouse Tier   (Red Hat Linux)

1) Oracle Database 11g Release 2 (11.2.0.1.0) for Linux x86-64

otn.com
 

Download:

2) JDK 1.7.0+  ( http://java.sun.com/javase/downloads/index.jsp )

3) Oracle DB 11g R2 Client (11.2.0.1.0) for Linux x86-64 (source: otn.oracle.com)

Download:
 

4) Informatica PowerCenter Services - Powercenter 9.0.1 Hotfix 2

 

5) DAC Server 10.1.3.4.1 

 
 

6) DAC Patch 12381656

      Download link: www.metalink.oracle.com
 

7) Oracle Business Intelligence, v. 11.1.1.6.0 - for Red Hat Linux x86-64 (64-bit)


 

8) Repository Creation Utility

 
 
 

ETL Client Server  (Windows 32 bit)

1) Oracle Database 11g Release 2 Client (11.2.0.1.0) for Microsoft Windows (32-bit)

 

2) JDK 1.6.0_29+ 

 

3) Informatica Client

   Download link:  Informatica Client 9.0.1 Disk 1
 
  
 

4) Informatica Services

 

5) DAC Server and Client

 
 

6) DAC Patch 12381656

Download link: www.metalink.oracle.com
 

7) Oracle Business Intelligence 11g (11.1.1.6.0) for Microsoft Windows (32-bit)

 
 

8) Oracle Business Intelligence Applications 7.9.6.3 for Microsoft Windows

 

9) Repository Creation Utility

 
 

10) Oracle Business Intelligence 11g Developer Client Tools (11.1.1.6.0) for Windows (32-bit)