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:

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.
Color Coding of the Layout - You need to add all color coding manually.
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.
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.

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.
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.
Review RDF triggers. Most of that functionality is not required in BI Publisher and those procedures could be removed.
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.
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.