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.
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.
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.
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:
The automatic reports conversion tool moves all pl/sql logic into database package. Review database package.
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
In this example, we are going to change the logo of BI
1. Added the following to instanceconfig.xml file:
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:
7. Restart BI Services
Tuesday, October 8, 2013
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.
'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
Download: Oracle Business Intelligence Data Warehouse Administration Console 10.1.3.4.1 for Linux x86
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)
Download link: http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win32soft-098987.html
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
Download link: Oracle Fusion Middleware Repository Creation Utility 11g (11.1.1.6.0) for Microsoft Windows
10) Oracle Business Intelligence 11g Developer Client Tools (11.1.1.6.0) for Windows (32-bit)
Download link: Oracle Business Intelligence Developer Client Tool
Sunday, November 18, 2012
Dashboard - Google Map integration
OBIEE Google Map with multiple addresses or with the MarkerClusterer API.
It is based on blogs:
http://obiee101.blogspot.com/2009/03/obiee-google-maps-multiple-addresses.html
http://blog.guident.com/2009/12/integrating-obiee-and-google-maps-with.html
Working example can be found on Beyond University BI Demo
To use Google Maps you need to sign up for a key on Google Maps website. In those examples we use geocodes but you may use address or postcode but geocodes are much quicker.
All of that html/javascript code is quite simple and self-documented so it is very easy to change.
Tips:
Don't put too much code to Narrative View, create javascript functions in additional file and reference that in your Narrative view (i.e. ).
In javascript don't use single-line-style comments (// comment), use multi-line-style comments instead (/* comment */). When coping or modifying Answers with single-line-style comments you might end up with code placed in single line comment after your comment - some actions remove end of the line sign from Narrative View.
Simple OBIEE Google Map with multiple addresses.
Create an BI Answer query, go to Narrative view, check box next to "Contains HTML Markup" and type similar code in following sections.
In the Prefix part put (in the first line put your Google map key):
Now check if it works in the compound layout.
OBIEE Google Map with multiple addresses using with the MarkerClasterer API.
Create an BI Answer query, go to Narrative view, check box next to "Contains HTML Markup" and type similar code in following sections. Put beyondmap.js somewhere on the server.
In the Prefix part put (in the first line put your Google map key, in second line put path to beyondmap.js file on your server):
Now check if it works in the compound layout.
Get GeoCodes for postcodes using Google Maps API in OBIEE.
Create an BI Answer query, go to Narrative view, check box next to "Contains HTML Markup" and type similar code in following sections.
In the Prefix part put (in the first line put your Google map key):
Now check if it works in the compound layout.
It is based on blogs:
http://obiee101.blogspot.com/2009/03/obiee-google-maps-multiple-addresses.html
http://blog.guident.com/2009/12/integrating-obiee-and-google-maps-with.html
Working example can be found on Beyond University BI Demo
To use Google Maps you need to sign up for a key on Google Maps website. In those examples we use geocodes but you may use address or postcode but geocodes are much quicker.
All of that html/javascript code is quite simple and self-documented so it is very easy to change.
Tips:
Don't put too much code to Narrative View, create javascript functions in additional file and reference that in your Narrative view (i.e. ).
In javascript don't use single-line-style comments (// comment), use multi-line-style comments instead (/* comment */). When coping or modifying Answers with single-line-style comments you might end up with code placed in single line comment after your comment - some actions remove end of the line sign from Narrative View.
Simple OBIEE Google Map with multiple addresses.
Create an BI Answer query, go to Narrative view, check box next to "Contains HTML Markup" and type similar code in following sections.
In the Prefix part put (in the first line put your Google map key):
Now check if it works in the compound layout.
OBIEE Google Map with multiple addresses using with the MarkerClasterer API.
Create an BI Answer query, go to Narrative view, check box next to "Contains HTML Markup" and type similar code in following sections. Put beyondmap.js somewhere on the server.
In the Prefix part put (in the first line put your Google map key, in second line put path to beyondmap.js file on your server):
Now check if it works in the compound layout.
Get GeoCodes for postcodes using Google Maps API in OBIEE.
Create an BI Answer query, go to Narrative view, check box next to "Contains HTML Markup" and type similar code in following sections.
In the Prefix part put (in the first line put your Google map key):
Now check if it works in the compound layout.
Friday, May 4, 2012
OBIA Architecture
NOTE: It is recommended to set up all Oracle BI Application tiers in the same local area network.
Installation of any of these tiers over WAN may cause timeouts during ETL mappings execution on the ETL tier
Installation of any of these tiers over WAN may cause timeouts during ETL mappings execution on the ETL tier
Tiers
| Machine Summary | ||||
| Tier | machine name | User Name | IP Address | |
| 1 | OBIA DW Tier | OBIADW | oracle | |
| 2 | OBIA ETL Tier | OBIAETL | oracle | |
| 3 | OBIA Presentation Tier | OBIEE | oracle | |
| 4 | OBIA Client Tier | WIN | xxx | |
| Business Analytics Warehouse
|
|
| Machine Specification | |
| M5000 SPARC VII - Core Factor - 0.75
16 GB RAM 2 cores Sun Solaris 10 (64-bit) Disk 400 GB |
|
| Software | |
| 1. Oracle Database 11g EE 11.2.0.1 ( with patch 9739315 applied and 9255542 is bug not a patch) |
| ETL Server | |
| Machine Specification | |
| SPARC T3 - Core Factor 0.25
4 GB RAM 8 cores Sun Solaris 10 (64-bit) Disk 100 GB |
|
| Software | |
| 1. DB Client 11.2.0.1.0 for Solaris
2. DAC Server 10.1.3.4.1 Patch 12381656 applied 3. Informatica Server - Powercenter 9.0.1 Hotfix 2 4. Java Platform Standard Edition (Java SE) Development Kit (JDK) 6 (1.6.0_05) |
| ETL Client | |
| Machine Specification | |
| 3 GB RAM 2 quad cores
Windows XP Professional SP3 (32 bit) Disk 1 TB |
|
| Software | |
| 1. Oracle Business Intelligence Developer Client Tool (MS Win 32 bit) 11.1.1.5.0
2. Oracle Database Client 11g EE 11.2.0.1 3. Informatica Client - PowerCenter 9.0.1 Hotfix 2 4. Informatica Services - PowerCenter 9.0.1 Hotfix 2 5. DAC Client 10.1.3.4.1 Patch 12381656 applied |
| Presentation Server | |
| Machine Specification | |
| SPARC T3 - Core Factor 0.25
8 GB RAM 8 cores Sun Solaris 10 (64-bit) (Update 8+) Disk 50 GB |
|
| Software | |
| 1. Oracle Business Intelligence Enterprise Edition 11.1.1.5.0
NOTE: certified with Oracle DB: - Oracle 10.2.0.4+ - Oracle 11.1.0.7+ - Oracle 11.2.0.1+ 2. BI Apps 7.9.6.3 repository and Catalog deployed |
Wednesday, April 25, 2012
OBIA Best Practices
Best Practices and Important Notes
DAC Notes- To check the pmcmd command sent by DAC to Informatica to execute a task, click on the Status Description in the Task Detail window. We can run this from the Linux box (where the Informatica server resides)
- Use the Analyze Repository Tables (Tools > DAC Repository Management > Analyze Repository Tables) when it has been made a considerable change to the core metadata -> this helps to speed up the steps that follow
-
Micro-ETL:
- scheduled to run every couple of hours and it can be running even when users are using the OLTP system or running BI reports
Steps:
[] create a new Subject Area and add the workflows that we are interested in (i.e.: 1 fact or 1 dimension only) + Assemble
[] create a new Execution Plan and add the Subject Area created before. Tick Micro-ETL. This is VERY IMPORTANT as the process is this: when Micro-ETL is ticked, the new data added in the OLPT comes into DW even when the users are still logged on and run reports, so no truncate of the staging or target tables happens. Also, the indexes are not dropped. The next time the Incremental is run (involving these tables), it brings in everything from the last incremental run (in a proper way), with dropping and recreating indexes, just to make sure that nothing was
missed during the Micro-ETLs run and that the statistics are up to date.
Note: It does not make sense to Analyze nor to drop/create indexes in the properties of the execution plan, all we want is to bring in those new records (so ideally we want the Micro-ETL to run in a couple of mins only - fastest as it can)
[] Generate parameters and Save
[] Build and Run - Deploy to PROD
- The DAC client could be used to connect to many DAC servers.
- In terms of migrating all the changes from DEV to PROD: export the custom container only (Logical, no other tick boxes checked) and import it in PROD. Note that when DAC server was created, an empty DAC repository has been created. so we only need to bring in the changes (ie custom container, custom subject areas, custom execution plans etc) and we setup the parameters and the connections to use the PROD connection details.
Export DAC customisations from DEV: DAC > Tools > DAC
Repository Management > Export > tick only Logical -> Select
container > Export.
Import DAC customisations into PROD: DAC > Tools > DAC Repository Management > Import
Informatica Notes
- It is good practice to use versioning of the changes so check-in must be made under a specific revision.
- Before making any changes to the mappings, it is advisable to keep a copy of the original one. Export SDE > Save as XML and put it under a revision control system. This will ensure that if the SDEs get messed, we could always revert to the original by Importing it to the custom folder (address the conflicts with Replace)
- If we want to make add some conditions to the query only for the Full load, instead of updating the mapping (SDE), just change the Workflow query (Full) - as the mapping is used by both Workflows (incremental and full) and we only want the change in one of them so it does not make sense to create another mapping just to reflect our condition.
- Running Workflows directly from Informatica: when creating a Mapping as a copy of an existing one, right-click Mapping/Mapplet > Declare Parameters and Variables > always give an Initial Value for the params so that when running it in the Workflow Manager it will use the default values and it will not error.
In Informatica Workflow Manager: don't forget to replace the Parameters with the OLTP/DW actual values
- drag WF and on the task > right click > go to Properties
-- $Source Connection Value -> change to be
whatever we want to point out to (Use Object and select object from the
list)-- $Target Connection Value -> change as above
- NOTE there is always the option to revert to the original (Use Connection Variable)
- Edit Task, go to tab Mapping > Connections > make sure there are no variables used but the actual values are passed. Save and run the workflow
DEV NOTE: to see large mappings just right click on the grey area in the Mapping Designer and select Arrange All Iconic
OBIA Customisations
The out-of-the-box OBIA does not always fulfill all the requirements
that customers have, which is why in majority of the cases the DW, ETL
or the BI reports need to be customized.
The customisations can be implemented at three levels:
1. Reports/dashboards (BI Presentation Server side) - i.e.: filters, new reports, new dashboards using the already existing objects in the Presentation Catalog.
2. BI Server side (creating additional KPIs, measures, dimensions/hierarchies, aggregations, etc)
3. ETL/DW side (which then involve carrying the customisations further and working out the logic in the BI server and BI Presentation Server side)
The most complex work is carried out on the ETL/DW side as this involves bringing more columns/tables in the DW.
The ETL process is tightly coupled with the DW.
There are three types of customisations of the ETL:
If the data that populates the DW needs to be changed (i.e. the rows to be retrieved) - this is being done by adding filters or modifying the queries of the already existing SDEs/SIL/PLPs.
DW Steps -- these imply adding new columns to the DW tables (W_..._DS, W_..._D, etc.)
Informatica Steps -- these imply extending the mappings
1. Create the custom folders and make o copy of the original provided mappings (as a rule of thumb, never alter the original mappings that come out-of-the-box)
3. Modify SIL mappings
4. Modify SDE/SIL Workflows
DAC Steps
1. Register Table Columns in DAC
[] Design > Tables > query for the _DS and _D tables that we have altered to add the new column
- right-click > Import From Database > Import Database Columns > selected record only
- specify Database = DataWarehouse, then click Read Columns; then select the column from the list and click Import Columns -- expect to see the newly added
column in the Columns tab of this table. NOTE: if importing all columns, note that the properties of the imported columns might alter the properties of the
already existing columns (this is not something that we want... so in order to avoid this, only import the new columns)
2. Create Logical/Physical folders in the DAC that point to our new folders in the Informatica Repository
(N) Tools > Seed Data > Task Physical Folders > NEW > enter the same names (case sensitive) as the folders we created in Informatica (CUSTOM_SDE, CUSTOM_SIL)
(N) Tools > Seed Data > Task Logical Folders > New > create 2 folders: Custom_Extract, Custom_Load
The Physical folders are the actual Informatica Folders that DAC need to be aware of. the Logical folders are folders that DAC will be working with.
3. Map Physical to Logical folders:
(N) Design View > Source System Folders tab > New > then map each of the physical folders to the corresponding folders: CUSTOM_SDE to Custom_Extract; CUSTOM_SIL to Custom_Load and SAVE
4. Let DAC know to issue commands to execute the custom WFs in Informatica. Also, change the folder from which DAC will be using the tasks:
NOTE: Make sure that you change the command for both Incremental and Full load.
(N) Design > Tasks > query for the tasks having exactly the same names as the Informatica WFs
5. SYNCHRONIZE (right click on the task > Synchronize tasks)
6. create a new subject area and add only our tasks (both SDE and SIL)
7. Assemble Subject Area
8. Create a new execution Plan and add our custom Subject area created at step 6. Generate Parameters and specify the datasources. Note that the custom folders are being brought forward here
9. Build the Execution plan.
10. Run the execution plan
OBIEE Steps
1. Open the RPD in Admin Tool
2. In the Physical Layer, search for the _D table that we have altered in the DW and whose additional columns we wanted to expose in BIA
3. Right-click on the table and New Object > Physical Column
4. Drag the column in the Logical layer in all the dimensions/facts involved.
5. Drag the column in the Presentation layer to expose it to the users accessing the Presentation Catalog.
All tables created in the DW have to have these 4 mandatory columns:
Informatica Steps
same as Type 1 but with the exception that we need to create these tables from scratch and bring all the columns in.
also, we have to create all the workflows from scratch maybe!
DAC Steps
1. Register Table and Table Columns in DAC
[] Design > Tables > click New.
- right-click > Import From Database > Import Database Tables > selected record only
- right-click > Import From Database > Import Database Columns > selected record only
- right-click > Import From Database > Import Indices > selected record only
OBIEE Steps
1. Open the RPD in Admin Tool
2. In the Physical Layer, Import the tables in the Physical layer
3. Create the relations in the Physical Schema
4. Create the table (dim/fact) in the Business Model layer
5. Create the relations with the other objects in the BMM layer
6. Add the table (dimension/fact) to the Presentation layer to expose it to the users accessing the Presentation Catalog.
More on this: http://docs.oracle.com/cd/E10783_01/doc/bi.79/e10742/anyinstadmcustomizing.htm#i1027226
The customisations can be implemented at three levels:
1. Reports/dashboards (BI Presentation Server side) - i.e.: filters, new reports, new dashboards using the already existing objects in the Presentation Catalog.
2. BI Server side (creating additional KPIs, measures, dimensions/hierarchies, aggregations, etc)
3. ETL/DW side (which then involve carrying the customisations further and working out the logic in the BI server and BI Presentation Server side)
The most complex work is carried out on the ETL/DW side as this involves bringing more columns/tables in the DW.
The ETL process is tightly coupled with the DW.
There are three types of customisations of the ETL:
- Type 1 customisations - when adding additional columns from source systems
- Type 2 customisations - when add new facts/dimensions to the ETL (new tables in the DW) using prepackaged adapters
- Type 3 customisations - imply the use of the Universal adapter to load data from sources that do not have pre-packaged adapters.
If the data that populates the DW needs to be changed (i.e. the rows to be retrieved) - this is being done by adding filters or modifying the queries of the already existing SDEs/SIL/PLPs.
Type 1 Customisation Steps
DW Steps -- these imply adding new columns to the DW tables (W_..._DS, W_..._D, etc.)
- Alter table to create new column in the staging (_DS) table
- Alter table to create new column in the target (_DS) table
Informatica Steps -- these imply extending the mappings
1. Create the custom folders and make o copy of the original provided mappings (as a rule of thumb, never alter the original mappings that come out-of-the-box)
- Informatica Repository Manager:
2. Modify SDE mappings
- Create custom folders CUSTOM_SDE,CUSTOM_SIL
- Copy the SDE workflows (both FULL and Incremental) that we want to alter to CUSTOM_SDE folder -- if conflicts arise, click Resolve conflicts: REPLACE
- Copy the SIL workflows (both FULL and Incremental) that we want to alter to CUSTOM_SIL folder -- if conflicts arise, click resolve conflicts: REPLACE
- Informatica Designer:
- change mapping name: (Menu) Mapping > Edit > prefix with XX_
[] Target Designer
- drag and drop the target that we want to alter (the _DS table)
- go to Target (Menu) > Import from Database - search for the table having the newly added columns in the DW (the _DS table we have altered in the DW steps)
- resolve conflict with Apply to All tables, then click Replace
[] Mapping Designer
- checkout the mapping that we want to alter then drag and drop it to the working area
Note that now the column appears in the _DS table (as we have just imported the table). However, this column is not mapped to any OLTP column. It is the developer's job to map it from the source all the way through the mapplet/mapping. To do so, we open the mapplet (an object that encapsulates the logic of transformations) and look for the source table from which we want to map the column to the _DS column.
- drag the column from the source to the Source Qualifier (SQ) and drop it when the + sign is displayed. Then modify the name of the column to give it a proper significance (open the SQ, then go to Ports tab to change the name). In the Source Qualifier, we have to select the column to be brought from the table
- drag the column from SQ to X_CUSTOM expression
- drag the column from X_Custom across until reaching the OUT_Transformation
- Save, then if no conflicts occurred -> check in the mapping
NOTE: repeat the process of mapping the column throughout all the paths in the Mapping, following the X_CUSTOM placeholder's path (except of SA mapplets, as these are encapsulated objects which are used in other mappings) until reaching the final _DS object, so that we have a path of mapping this column all the way from the source table to the target (_DS) destination.
- drag and drop the target that we want to alter (the _DS table)
- go to Target (Menu) > Import from Database - search for the table having the newly added columns in the DW (the _DS table we have altered in the DW steps)
- resolve conflict with Apply to All tables, then click Replace
[] Mapping Designer
- checkout the mapping that we want to alter then drag and drop it to the working area
Note that now the column appears in the _DS table (as we have just imported the table). However, this column is not mapped to any OLTP column. It is the developer's job to map it from the source all the way through the mapplet/mapping. To do so, we open the mapplet (an object that encapsulates the logic of transformations) and look for the source table from which we want to map the column to the _DS column.
- drag the column from the source to the Source Qualifier (SQ) and drop it when the + sign is displayed. Then modify the name of the column to give it a proper significance (open the SQ, then go to Ports tab to change the name). In the Source Qualifier, we have to select the column to be brought from the table
- drag the column from SQ to X_CUSTOM expression
- drag the column from X_Custom across until reaching the OUT_Transformation
- Save, then if no conflicts occurred -> check in the mapping
NOTE: repeat the process of mapping the column throughout all the paths in the Mapping, following the X_CUSTOM placeholder's path (except of SA mapplets, as these are encapsulated objects which are used in other mappings) until reaching the final _DS object, so that we have a path of mapping this column all the way from the source table to the target (_DS) destination.
- Informatica Designer
[] Source Designer
- drag and drop the source table (_DS)
- go to Source (Menu) > Import from Database - search for the table having the newly added columns in the DW (_DS)
- resolve conflict with Apply to All tables + Replace
[] Target Designer
- drag and drop the target that we want to alter (_D)
- go to Target (Menu) > Import from Database - search for the table having the newly added columns in the DW (_D)
- resolve conflict with Apply to All tables + Replace
[] Mapping Designer: note that now the column appears in both the _DS and _D tables, however, we have to map it from the source all the way through to the target. To do so, we open the mapplet and look for the table from which we want to map.
- drag and drop the column from the source to the SQ qualifier until we reach the + sign. NOTE: sometimes it is not required to modify the SQL query as this could be auto-generated. In this case, making sure that the port exists in the SQ is enough.
- drag and drop the column from SQ to the next object following the X_CUSTOM path only.
- drag the column across following the X_CUSTOM path until reaching the Target table
- Save (ctrl+s), then if no conflicts occurred -> Check In the mapping
IMPORTANT: for dimension tables, in the Unspecified mapping, always link the UPDATE Strategy with the Target Definition Column we have just added. I.e.: if the column is string, then link it to ETL_UNSPEC_ST. These Unspecified SIL are present in all Dimension SIL Full workflows to ensure the outer join (ie still display values in the fact even when there is no row in the dim but there are values in the fact table)
- drag and drop the source table (_DS)
- go to Source (Menu) > Import from Database - search for the table having the newly added columns in the DW (_DS)
- resolve conflict with Apply to All tables + Replace
[] Target Designer
- drag and drop the target that we want to alter (_D)
- go to Target (Menu) > Import from Database - search for the table having the newly added columns in the DW (_D)
- resolve conflict with Apply to All tables + Replace
[] Mapping Designer: note that now the column appears in both the _DS and _D tables, however, we have to map it from the source all the way through to the target. To do so, we open the mapplet and look for the table from which we want to map.
- drag and drop the column from the source to the SQ qualifier until we reach the + sign. NOTE: sometimes it is not required to modify the SQL query as this could be auto-generated. In this case, making sure that the port exists in the SQ is enough.
- drag and drop the column from SQ to the next object following the X_CUSTOM path only.
- drag the column across following the X_CUSTOM path until reaching the Target table
- Save (ctrl+s), then if no conflicts occurred -> Check In the mapping
IMPORTANT: for dimension tables, in the Unspecified mapping, always link the UPDATE Strategy with the Target Definition Column we have just added. I.e.: if the column is string, then link it to ETL_UNSPEC_ST. These Unspecified SIL are present in all Dimension SIL Full workflows to ensure the outer join (ie still display values in the fact even when there is no row in the dim but there are values in the fact table)
4. Modify SDE/SIL Workflows
- Informatica Workflow Manager
- change workflows names: drag the workflow in WF designer > select
Workflow menu > Edit > Rename to
xx_SDE_/xx_SIL_.
- in the Properties tab, change the logfile name to be XX_.log.
- in Workflow Designer, on the session, right-click > Edit > General tab > Rename. Note that the task name is still the old one, this will be renamed when right-click on the session and specifying Open Task.
- in Workflow Designer, on the session, right-click > Edit > General tab > Rename. Note that the task name is still the old one, this will be renamed when right-click on the session and specifying Open Task.
- in Properties tab, Parameter Filename needs to point out to the new
folder where the workflow resides (CUSTOM_SDE.XX_SDE_.txt)
and the new name of the Workflow. Note that these param files are
generated from DAC and they need to be in synch with the Informatica
mappings/workflows.
- to make sure that the task is executing the SQL query that would bring in the column (as we have altered it in the mapping) we need to Open Task > go to Mapping tab > select the SQ mapplet > SQL Query > check the query... if it does not contain the column (it shouldn't at this phase) then click REVERT and this will bring in the column added.
- repeat for all tasks, sessions, workflows (both incremental and Full).
- Repository > SAVE
Important NOTE: After a Type 1 Customisation we need to run a Full Load first, so that DW is aware of the new columns/tables and will not leave the table in an inconsistent state (ie if running an incremental load then only the new rows would populate the newly added column and the rest of the rows will have a NULL value in the added column)
- to make sure that the task is executing the SQL query that would bring in the column (as we have altered it in the mapping) we need to Open Task > go to Mapping tab > select the SQ mapplet > SQL Query > check the query... if it does not contain the column (it shouldn't at this phase) then click REVERT and this will bring in the column added.
- repeat for all tasks, sessions, workflows (both incremental and Full).
- Repository > SAVE
Important NOTE: After a Type 1 Customisation we need to run a Full Load first, so that DW is aware of the new columns/tables and will not leave the table in an inconsistent state (ie if running an incremental load then only the new rows would populate the newly added column and the rest of the rows will have a NULL value in the added column)
1. Register Table Columns in DAC
[] Design > Tables > query for the _DS and _D tables that we have altered to add the new column
- right-click > Import From Database > Import Database Columns > selected record only
- specify Database = DataWarehouse, then click Read Columns; then select the column from the list and click Import Columns -- expect to see the newly added
column in the Columns tab of this table. NOTE: if importing all columns, note that the properties of the imported columns might alter the properties of the
already existing columns (this is not something that we want... so in order to avoid this, only import the new columns)
2. Create Logical/Physical folders in the DAC that point to our new folders in the Informatica Repository
(N) Tools > Seed Data > Task Physical Folders > NEW > enter the same names (case sensitive) as the folders we created in Informatica (CUSTOM_SDE, CUSTOM_SIL)
(N) Tools > Seed Data > Task Logical Folders > New > create 2 folders: Custom_Extract, Custom_Load
The Physical folders are the actual Informatica Folders that DAC need to be aware of. the Logical folders are folders that DAC will be working with.
3. Map Physical to Logical folders:
(N) Design View > Source System Folders tab > New > then map each of the physical folders to the corresponding folders: CUSTOM_SDE to Custom_Extract; CUSTOM_SIL to Custom_Load and SAVE
4. Let DAC know to issue commands to execute the custom WFs in Informatica. Also, change the folder from which DAC will be using the tasks:
NOTE: Make sure that you change the command for both Incremental and Full load.
(N) Design > Tasks > query for the tasks having exactly the same names as the Informatica WFs
5. SYNCHRONIZE (right click on the task > Synchronize tasks)
6. create a new subject area and add only our tasks (both SDE and SIL)
7. Assemble Subject Area
8. Create a new execution Plan and add our custom Subject area created at step 6. Generate Parameters and specify the datasources. Note that the custom folders are being brought forward here
9. Build the Execution plan.
10. Run the execution plan
OBIEE Steps
1. Open the RPD in Admin Tool
2. In the Physical Layer, search for the _D table that we have altered in the DW and whose additional columns we wanted to expose in BIA
3. Right-click on the table and New Object > Physical Column
4. Drag the column in the Logical layer in all the dimensions/facts involved.
5. Drag the column in the Presentation layer to expose it to the users accessing the Presentation Catalog.
Type 2 Customisation Steps
DW StepsAll tables created in the DW have to have these 4 mandatory columns:
- INTEGRATION_ID -- unique identifier of a record as in source table
- DATASOURCE_NUM_ID -- identifier for OLTP source
- ROW_WID -- surrogated key - unique ID for tables
- ETL_PROC_WID -- ID of the ETL process (stored in S_ETL_RUN (OLTP) and W_ETL_RUN (DW))
Informatica Steps
same as Type 1 but with the exception that we need to create these tables from scratch and bring all the columns in.
also, we have to create all the workflows from scratch maybe!
DAC Steps
1. Register Table and Table Columns in DAC
[] Design > Tables > click New.
- right-click > Import From Database > Import Database Tables > selected record only
- right-click > Import From Database > Import Database Columns > selected record only
- right-click > Import From Database > Import Indices > selected record only
OBIEE Steps
1. Open the RPD in Admin Tool
2. In the Physical Layer, Import the tables in the Physical layer
3. Create the relations in the Physical Schema
4. Create the table (dim/fact) in the Business Model layer
5. Create the relations with the other objects in the BMM layer
6. Add the table (dimension/fact) to the Presentation layer to expose it to the users accessing the Presentation Catalog.
More on this: http://docs.oracle.com/cd/E10783_01/doc/bi.79/e10742/anyinstadmcustomizing.htm#i1027226
Friday, February 24, 2012
Oracle DB 11g Editions
If you ever wondered which is the most appropriate Oracle DB you should be on, then here is where you could find this info:
http://www.oracle.com/us/products/database/product-editions-066501.html
http://www.oracle.com/us/products/database/product-editions-066501.html
Thursday, February 23, 2012
Upgrade RPD from 10g to 11g
on Windows: Command Line prompt: cmd>SET ORACLE_INSTANCE=C:\oracle\bi\instances\instance1\
where C:\oracle\bi is your middleware home.
cmd>C:\oracle\bi\Oracle_BI1\bifoundation\server\bin\obieerpdmigrateutil.exe -I C:\TEMP\demo10g.rpd -O C:\TEMP\demo11g.rpd -L C:\TEMP\demo11g.ldif -U Administrator
where C:\oracle\bi is your middleware home.
cmd>C:\oracle\bi\Oracle_BI1\bifoundation\server\bin\obieerpdmigrateutil.exe -I C:\TEMP\demo10g.rpd -O C:\TEMP\demo11g.rpd -L C:\TEMP\demo11g.ldif -U Administrator
Wednesday, February 22, 2012
New Release
Finally I'm at pace with the news: OBIEE 11.1.1.6.0 is out and can be downloaded from
http://www.oracle.com/technetwork/middleware/bi-enterprise-edition/downloads/bus-intelligence-11g-165436.html
This release delivers a BI platform specially designed to leverage:
• Oracle Exalytics hardware’s large memory, processors, concurrency, and other hardware features and system configurations.
• Enhancements to Times-Ten for Exalytics for analytical processing at in-memory speeds
• Dynamic user interface enhancements that complement large amounts of data to present business information in meaningful and compelling ways
• A new BI Server Summary Advisor for Exalytics for aggregate generation and persistence
• Essbase memory usage optimizations and concurrency improvements for Exalytics to deliver efficient distribution of processing
• BI Publisher performance, lifecycle, workflow and report creation enhancements
• New and enhanced Scorecard views and BI Mobile improvements
• Numerous Security, Management/Diagnostic and Lifecycle enhancements
• Certified BI and EPM Applications on Exalytics
The good news is that this release can be used with BIA 7.9.6.3
- Enjoy!
http://www.oracle.com/technetwork/middleware/bi-enterprise-edition/downloads/bus-intelligence-11g-165436.html
This release delivers a BI platform specially designed to leverage:
• Oracle Exalytics hardware’s large memory, processors, concurrency, and other hardware features and system configurations.
• Enhancements to Times-Ten for Exalytics for analytical processing at in-memory speeds
• Dynamic user interface enhancements that complement large amounts of data to present business information in meaningful and compelling ways
• A new BI Server Summary Advisor for Exalytics for aggregate generation and persistence
• Essbase memory usage optimizations and concurrency improvements for Exalytics to deliver efficient distribution of processing
• BI Publisher performance, lifecycle, workflow and report creation enhancements
• New and enhanced Scorecard views and BI Mobile improvements
• Numerous Security, Management/Diagnostic and Lifecycle enhancements
• Certified BI and EPM Applications on Exalytics
The good news is that this release can be used with BIA 7.9.6.3
- Enjoy!
Tuesday, February 21, 2012
I'm still here..
It's been so long since I haven't posted anything on my BI blog but hopefully I'm gonna fix this somehow. I've been side tracked by working on other projects but today I had a go on my virtual's machine BI and I have faced something that people were blogging /emailing about for weeks now: Firefox 10 does not work with OBIEE 11.1.1.5.0
Workaround:
1) Easiest is to revert to a previous version of Firefox.
2 ) Keep Firefox 10
- Type "about:config" into an address bar
- Right-click in the window and select New/String
- Name: "general.useragent.override"
- Value: "Mozilla/5.0 (Windows; Windows NT 6.1; rv:10.0) Gecko/20100101 Firefox/9.0"
- Refresh OBIEE login screen.. and tut-tut! it works
Solution
Oracle seems to have released a patch for this: patch 13564003
1) Easiest is to revert to a previous version of Firefox.
2 ) Keep Firefox 10
- Type "about:config" into an address bar
- Right-click in the window and select New/String
- Name: "general.useragent.override"
- Value: "Mozilla/5.0 (Windows; Windows NT 6.1; rv:10.0) Gecko/20100101 Firefox/9.0"
- Refresh OBIEE login screen.. and tut-tut! it works
Solution
Oracle seems to have released a patch for this: patch 13564003
Friday, February 25, 2011
Wednesday, February 23, 2011
BI Publisher Debugging
OPTIONS:
1. Add "debug" in the xmlp-config file -- restarting the app server (oc4j or tomcat).
For Enterprise Release the debug level can be set from Admin > Server Configuration UI. Setting value as “Debug” from the LOV turns on the debug mode. Restart the Application to make this change effective. This change sets the DEBUG_LEVEL property to “debug” in xmlp-server-config.xml configuration file. The location of xmlp-server-config.xml is under Repository (./XMLP/Admin/Configuration). The debug log will be available in server log.
2. Create file xdodebug.cfg and save under c:\program files\java\jdk\jre\lib
content of file:
LogLevel=STATEMENT
LogDir=c:\temp
1. Add "debug" in the xmlp-config file -- restarting the app server (oc4j or tomcat).
For Enterprise Release the debug level can be set from Admin > Server Configuration UI. Setting value as “Debug” from the LOV turns on the debug mode. Restart the Application to make this change effective. This change sets the DEBUG_LEVEL property to “debug” in xmlp-server-config.xml configuration file. The location of xmlp-server-config.xml is under Repository (./XMLP/Admin/Configuration). The debug log will be available in server log.
2. Create file xdodebug.cfg and save under c:\program files\java\jdk\jre\lib
content of file:
LogLevel=STATEMENT
LogDir=c:\temp
Friday, October 29, 2010
OBIEE 11g Start/Stop Services - boot.properties
When starting/stopping the Managed Server or Admin Server (WebLogic),
./startManagedWebLogic.sh bi_server1 http://hostname:7001
./startWebLogic.sh
the user is prompted to enter username and password
Instead, you can enable auto login using a boot identity file. A boot identity file contains
user credentials for starting and stopping an instance of WebLogic Server. An Administration
Server can refer to this file for user credentials instead of prompting you to provide them.
Because the credentials are encrypted, using a boot identity file is more secure than storing
unencrypted credentials in a startup or shutdown script. If there is no boot identity file when
you start a server, the server instance prompts you to enter a username and password. The boot
identity file can be different for each server instance in the domain
A) To configure the boot.properties file for the Managed Server, perform the following steps:
1. Browse to
$MIDDLEWARE_HOME/user_projects/domains/bifoundation_domain/servers/bi_server1/security and edit the boot.properties file
$ vi boot.properties
2. Modify the file to reflect the following and save it:
username=admin_username
password=admin_password
3. Browse to
$MIDDLEWARE_HOME/user_projects/domains/bifoundation_domain/bin
./startManagedWebLogic.sh bi_server1 http://hostname:7001/console
B) To configure the boot.properties file for the AdminServer (WebLogic), perform the following steps:
1. Browse to
$MIDDLEWARE_HOME/user_projects/domains/bifoundation_domain/servers/AdminServers/security and edit the boot.properties file.
$ vi boot.properties
2. Modify the file to reflect the following and save it:
username=admin_username
password=admin_password
3. Browse to
$MIDDLEWARE_HOME/user_projects/domains/bifoundation_domain/bin
./startWebLogic.sh
Note that the boot.properties file is identified and you are not prompted for a username and
password. The server is in the running mode. Nagivate to boot.properties and notice that the content of the file is now encrypted.
./startManagedWebLogic.sh bi_server1 http://hostname:7001
./startWebLogic.sh
the user is prompted to enter username and password
Instead, you can enable auto login using a boot identity file. A boot identity file contains
user credentials for starting and stopping an instance of WebLogic Server. An Administration
Server can refer to this file for user credentials instead of prompting you to provide them.
Because the credentials are encrypted, using a boot identity file is more secure than storing
unencrypted credentials in a startup or shutdown script. If there is no boot identity file when
you start a server, the server instance prompts you to enter a username and password. The boot
identity file can be different for each server instance in the domain
A) To configure the boot.properties file for the Managed Server, perform the following steps:
1. Browse to
$MIDDLEWARE_HOME/user_projects/domains/bifoundation_domain/servers/bi_server1/security and edit the boot.properties file
$ vi boot.properties
2. Modify the file to reflect the following and save it:
username=admin_username
password=admin_password
3. Browse to
$MIDDLEWARE_HOME/user_projects/domains/bifoundation_domain/bin
./startManagedWebLogic.sh bi_server1 http://hostname:7001/console
B) To configure the boot.properties file for the AdminServer (WebLogic), perform the following steps:
1. Browse to
$MIDDLEWARE_HOME/user_projects/domains/bifoundation_domain/servers/AdminServers/security and edit the boot.properties file.
$ vi boot.properties
2. Modify the file to reflect the following and save it:
username=admin_username
password=admin_password
3. Browse to
$MIDDLEWARE_HOME/user_projects/domains/bifoundation_domain/bin
./startWebLogic.sh
Note that the boot.properties file is identified and you are not prompted for a username and
password. The server is in the running mode. Nagivate to boot.properties and notice that the content of the file is now encrypted.
Friday, August 20, 2010
OBIEE log files and where to find them
OBIEE Logs are generated by the following components
1) BI Presentation Server
2) BI Server
3) BI Javahost
4) BI Scheduler
1) Presentation Server log (helpful to for presentation server login issues and Answer/dashboard related issues) and it can be located at ORACLEBIDATA/web/log/sawlog0.log
You can increase the logging on this server to get a detailed logging to troubleshoot the SSO and integration issues by modifying the logconfig.xml file
2) BI Server Logs:
- NQServer.log can be located from the path ORACLEBI/server/log/NQServer.log.
helpful with BI Server Start up issues and Subject areas loaded and the datasources connectedfrom within the RPD.
- NQQurey.log can be located from ORACLEBI/server/log/NSQuery.log
contains the BI Server query operations. Traces user actions.It can also identify if the query is hit from the cache or results obtained are straight from the database and also the actual time it took to execute the query.
It is possible to increase the logging for the NQquery.log via the RPD Security, or via the initialization blocks or by setting the LOGLEVEL Variable from the Answers 'Advanced' tab.The session variable LOGLEVEL overrides a user's logging level. For example, if the Oracle BI Administrator has a logging level set at 2 and the LOGLEVEL is set at 1 in the repository initialization block then the Oracle BI Administrator's logging level will be set at 1.
3) BI Javahost logs - for identifying the issues with the charts and graphs generated by java from answers/dashboard reports; diagnosing the issues with EPM and OBIEE integration with custom authenticator. Located at: ORACLEBIDATA/web/log/javahost/jhost0.log
4) BI Scheduler.log is useful for diagnosing the issues with delivers/scheduler.
To increase the logging on Scheduler, Set Debug flag to true in the Scheduler Job Manager configuration window.
- located at OracleBI/Server/log directory.
(source: Metalink Note 1069199.1)
1) BI Presentation Server
2) BI Server
3) BI Javahost
4) BI Scheduler
1) Presentation Server log (helpful to for presentation server login issues and Answer/dashboard related issues) and it can be located at ORACLEBIDATA/web/log/sawlog0.log
You can increase the logging on this server to get a detailed logging to troubleshoot the SSO and integration issues by modifying the logconfig.xml file
2) BI Server Logs:
- NQServer.log can be located from the path ORACLEBI/server/log/NQServer.log.
helpful with BI Server Start up issues and Subject areas loaded and the datasources connectedfrom within the RPD.
- NQQurey.log can be located from ORACLEBI/server/log/NSQuery.log
contains the BI Server query operations. Traces user actions.It can also identify if the query is hit from the cache or results obtained are straight from the database and also the actual time it took to execute the query.
It is possible to increase the logging for the NQquery.log via the RPD Security, or via the initialization blocks or by setting the LOGLEVEL Variable from the Answers 'Advanced' tab.The session variable LOGLEVEL overrides a user's logging level. For example, if the Oracle BI Administrator has a logging level set at 2 and the LOGLEVEL is set at 1 in the repository initialization block then the Oracle BI Administrator's logging level will be set at 1.
3) BI Javahost logs - for identifying the issues with the charts and graphs generated by java from answers/dashboard reports; diagnosing the issues with EPM and OBIEE integration with custom authenticator. Located at: ORACLEBIDATA/web/log/javahost/jhost0.log
4) BI Scheduler.log is useful for diagnosing the issues with delivers/scheduler.
To increase the logging on Scheduler, Set Debug flag to true in the Scheduler Job Manager configuration window.
- located at OracleBI/Server/log directory.
(source: Metalink Note 1069199.1)
Thursday, August 19, 2010
How to create WebLogic / OC4J / All as Windows Services
Steps to Start WebLogic as a Windows Service:
1. Go to $%WEBLOGIC_DIR%\wlserver_10.3\server\bin where WEBLOGIC_DIR is the home directory of weblogic installation.
2. Create a file called createSvc.cmd with following values:
(please make sure you mention the right domain name, path and admin server instance name)
echo off
SETLOCAL
set DOMAIN_NAME=base_domain
set USERDOMAIN_HOME=C:\app\product\10.3.3\mt_1\user_projects\domains\base_domain
set SERVER_NAME=AdminServer
set PRODUCTION_MODE=true (or False if you are in DEV mode)
set JAVA_VENDOR=Sun
set JAVA_HOME=C:\Java\jdk1.6
set MEM_ARGS=-Xms256m -Xmx512m
call "C:\app\product\10.3.3\mt_1\wlserver_10.3\server\bin\installSvc.cmd"
ENDLOCAL
3. create a backup of InstallSvc.cmd (because we are going to alter the default service name to be something more meaningful: Oracle WebLogic)
4. Edit InstallSvc.cmd
- search for 'beasvc'
- replace
%WL_HOME%\server\bin\beasvc" -install -svcname:"beasvc %DOMAIN_NAME%_%SERVER_NAME%"
with the meaningful chosen service name:
%WL_HOME%\server\bin\beasvc" -install -svcname:"Oracle WebLogic %DOMAIN_NAME%_%SERVER_NAME%"
5. Execute createSvc.cmd script from same folder C:\app\product\10.3.3\mt_1\wlserver_10.3\server\bin\
- This should create service with name of "Oracle WebLogic base_Domain_Adminserver" in the registry under HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services
6.You can now start and stop the service from the Services control panel window.
NOTE: if you want to delete the win service created, in the command line, type:
> sc delete < SERVICE name>
(reference: http://www.sadikhov.com/forum/index.php?showtopic=118351)
More on this: http://download.oracle.com/docs/cd/E14571_01/web.1111/e13708/winservice.htm
OC4J as a Windows Service
Download JavaService.exe from http://javaservice.objectweb.org/
Pre Requisite: JDK 1.5 plus should be present on the machine.
Instructions
ALL
http://obieetalk.com/obiee-11g-auto-start-all-windows-services
1. Go to $%WEBLOGIC_DIR%\wlserver_10.3\server\bin where WEBLOGIC_DIR is the home directory of weblogic installation.
2. Create a file called createSvc.cmd with following values:
(please make sure you mention the right domain name, path and admin server instance name)
echo off
SETLOCAL
set DOMAIN_NAME=base_domain
set USERDOMAIN_HOME=C:\app\product\10.3.3\mt_1\user_projects\domains\base_domain
set SERVER_NAME=AdminServer
set PRODUCTION_MODE=true (or False if you are in DEV mode)
set JAVA_VENDOR=Sun
set JAVA_HOME=C:\Java\jdk1.6
set MEM_ARGS=-Xms256m -Xmx512m
call "C:\app\product\10.3.3\mt_1\wlserver_10.3\server\bin\installSvc.cmd"
ENDLOCAL
3. create a backup of InstallSvc.cmd (because we are going to alter the default service name to be something more meaningful: Oracle WebLogic
4. Edit InstallSvc.cmd
- search for 'beasvc'
- replace
%WL_HOME%\server\bin\beasvc" -install -svcname:"beasvc %DOMAIN_NAME%_%SERVER_NAME%"
with the meaningful chosen service name:
%WL_HOME%\server\bin\beasvc" -install -svcname:"Oracle WebLogic %DOMAIN_NAME%_%SERVER_NAME%"
5. Execute createSvc.cmd script from same folder C:\app\product\10.3.3\mt_1\wlserver_10.3\server\bin\
- This should create service with name of "Oracle WebLogic base_Domain_Adminserver" in the registry under HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services
6.You can now start and stop the service from the Services control panel window.
> sc delete < SERVICE name>
Pre Requisite: JDK 1.5 plus should be present on the machine.
Instructions
1. cmd prompt and cd to the folder where the javaservice.exe resides
2. javaservice -install "Oracle BI: OC4J Service" "C:\Program Files\Java\jdk1.6.0_11\jre\bin\client\jvm.dll" -XX:MaxPermSize=128m -Xmx512m "-Djava.class.path=C:\app\oracle\product\10.1.3\bi_1\oraclebi\oc4j_bi\j2ee\home\oc4j.jar" -start oracle.oc4j.loader.boot.BootStrap -description "Oracle BI Oc4J Service"
3. now you can net start the service
NOTE: step 2) needs to be configured according to your system settings (i.e.: java jdk path, oracle BI path)
Subscribe to:
Posts (Atom)
