Partitioning on W_GL_OTHER_F - 1

In our database, W_GL_OTHER_F table has more then 70 million records, and reports have already started hitting bad performance. Considering we will go live on more countries in our global OBIEE project, the number of records is getting higher day by day. According to Oracle Business Intelligence Applications Version 7.9.6 Performance Recommendations (BI_Apps796_Perf_Tech_Note_V5), we have decided to go on partitiopning over W_GL_OTHER_F table. I will try to write the all steps that we followed to help other people who are trying to do the same, as we had many questions before starting this process, and we could not find quick answers by just googling.

We are implementing the range partitioning with ACCT_PERIOD_END_DT_WID and also partitioning the data by quarterly. The first problem that we hit was how to partition the data by quarterly while the ACCT_PERIOD_END_DT_WID is a number field having populated by concatenating 3 values

M_CAL_CAL_WID || ACCT_PERIOD_END_DT|| IIF(ADJUSTMENT_FLG='Y', '999', '000')

M_CAL_CAL_WID is an ID of calendar being used, in our case it is 1003. And the last 3 digits has not much impact on partitioning. Like if accounting period end is 20101031 (31 Oct 2010) and if it is adjustment record then ACCT_PERIOD_END_DT_WID would be 100320101031999.  Therefore we came up with the solution that we will be loading this data into 4th Quarter of 2010, which can be defined as "partition PART_2010Q4 values less than (100320101232000)", see the below create table script for more detail, but itis simply that we do not need to comply with date formatting, it is a number in this column anyway, so we can play with it as we want.

Steps to be done in Datawarehouse Database
Rename the original table

rename W_GL_OTHER_F to W_GL_OTHER_F_ORIG;
  

Before running the create table ddl, execute the below query in database and change the partition range value accordingly.

SELECT row_wid
FROM w_mcal_cal_d
WHERE mcal_cal_name = 'YOUR CALENDAR'

Please use the value from the above query while defining the partition range. As in our environment the value is 1003 we have used 1003**** and replace this with the query result.

Create the partitioned table, using range partitioning by quarter;

Create table W_GL_OTHER_F partition by range (ACCT_PERIOD_END_DT_WID)
(
partition PART_MIN values less than (100320061232000),
partition PART_2007Q1 values less than (100320070332000),
partition PART_2007Q2 values less than (100320070632000),
partition PART_2007Q3 values less than (100320070932000),
partition PART_2007Q4 values less than (100320071232000),
partition PART_2008Q1 values less than (100320080332000),
partition PART_2008Q2 values less than (100320080632000),
partition PART_2008Q3 values less than (100320080932000),
partition PART_2008Q4 values less than (100320081232000),
partition PART_2009Q1 values less than (100320090332000),
partition PART_2009Q2 values less than (100320090632000),
partition PART_2009Q3 values less than (100320090932000),
partition PART_2009Q4 values less than (100320091232000),
partition PART_2010Q1 values less than (100320100332000),
partition PART_2010Q2 values less than (100320100632000),
partition PART_2010Q3 values less than (100320100932000),
partition PART_2010Q4 values less than (100320101232000),
partition PART_2011Q1 values less than (100320110332000),
partition PART_2011Q2 values less than (100320110632000),
partition PART_2011Q3 values less than (100320110932000),
partition PART_2011Q4 values less than (100320111232000),
partition PART_2012Q1 values less than (100320120332000),
partition PART_2012Q2 values less than (100320120632000),
partition PART_2012Q3 values less than (100320120932000),
partition PART_2012Q4 values less than (100320121232000),
partition PART_MAX values less than (maxvalue)
)
TABLESPACE XXXDW_DATA
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
nologging parallel
enable row movement
as (select * from W_GL_OTHER_F_ORIG);
/

Rename indexes on renamed table

ALTER INDEX W_GL_OTHER_F_71 rename to W_GL_OTHER_F_71_ORIG;
ALTER INDEX W_GL_OTHER_F_72 rename to W_GL_OTHER_F_72_ORIG;
ALTER INDEX W_GL_OTHER_F_73 rename to W_GL_OTHER_F_73_ORIG;
ALTER INDEX W_GL_OTHER_F_F59 rename to W_GL_OTHER_F_F59_ORIG;
ALTER INDEX W_GL_OTHER_F_F25 rename to W_GL_OTHER_F_F25_ORIG;
ALTER INDEX W_GL_OTHER_F_F36 rename to W_GL_OTHER_F_F36_ORIG;
ALTER INDEX W_GL_OTHER_F_F40 rename to W_GL_OTHER_F_F40_ORIG;
ALTER INDEX W_GL_OTHER_F_F5 rename to W_GL_OTHER_F_F5_ORIG;
ALTER INDEX W_GL_OTHER_F_M2 rename to W_GL_OTHER_F_M2_ORIG;
ALTER INDEX W_GL_OTHER_F_C70 rename to W_GL_OTHER_F_C70_ORIG;
ALTER INDEX W_GL_OTHER_F_F13 rename to W_GL_OTHER_F_F13_ORIG;
ALTER INDEX W_GL_OTHER_F_F11 rename to W_GL_OTHER_F_F11_ORIG;
ALTER INDEX W_GL_OTHER_F_F60 rename to W_GL_OTHER_F_F60_ORIG;
ALTER INDEX W_GL_OTHER_F_F61 rename to W_GL_OTHER_F_F61_ORIG;
ALTER INDEX W_GL_OTHER_F_U1 rename to W_GL_OTHER_F_U1_ORIG;

Create Global and Local indexes. Run the below statements to create indexes

CREATE BITMAP INDEX W_GL_OTHER_F_C70 ON W_GL_OTHER_F (LOC_CURR_CODE ASC) tablespace XXXDW_DATA LOCAL NOLOGGING;
CREATE BITMAP INDEX W_GL_OTHER_F_72 ON W_GL_OTHER_F (X_REFERENCE_8 ASC) tablespace XXXDW_DATA LOCAL NOLOGGING;
CREATE UNIQUE INDEX W_GL_OTHER_F_U1 ON W_GL_OTHER_F (INTEGRATION_ID ASC,DATASOURCE_NUM_ID ASC) tablespace XXXDW_DATA GLOBAL NOLOGGING;
CREATE BITMAP INDEX W_GL_OTHER_F_73 ON W_GL_OTHER_F (DOC_CURR_CODE ASC) tablespace XXXDW_DATA LOCAL NOLOGGING;
CREATE BITMAP INDEX W_GL_OTHER_F_71 ON W_GL_OTHER_F (DOC_STATUS_WID ASC) tablespace XXXDW_DATA LOCAL NOLOGGING;
CREATE BITMAP INDEX W_GL_OTHER_F_F11 ON W_GL_OTHER_F (COMPANY_ORG_WID ASC) tablespace XXXDW_DATA LOCAL NOLOGGING;
CREATE BITMAP INDEX W_GL_OTHER_F_F13 ON W_GL_OTHER_F (COST_CENTER_WID ASC) tablespace XXXDW_DATA LOCAL NOLOGGING;
CREATE BITMAP INDEX W_GL_OTHER_F_F25 ON W_GL_OTHER_F (GL_ACCOUNT_WID ASC) tablespace XXXDW_DATA LOCAL NOLOGGING;
CREATE BITMAP INDEX W_GL_OTHER_F_F36 ON W_GL_OTHER_F (POSTED_ON_DT_WID ASC) tablespace XXXDW_DATA LOCAL NOLOGGING;
CREATE BITMAP INDEX W_GL_OTHER_F_F40 ON W_GL_OTHER_F (PROFIT_CENTER_WID ASC) tablespace XXXDW_DATA LOCAL NOLOGGING;
CREATE BITMAP INDEX W_GL_OTHER_F_F5 ON W_GL_OTHER_F (BUSN_AREA_ORG_WID ASC) tablespace XXXDW_DATA LOCAL NOLOGGING;
CREATE INDEX W_GL_OTHER_F_F59 ON W_GL_OTHER_F (ACCT_PERIOD_END_DT_WID ASC) tablespace XXXDW_DATA LOCAL NOLOGGING;
CREATE BITMAP INDEX W_GL_OTHER_F_F60 ON W_GL_OTHER_F (LEDGER_WID ASC) tablespace XXXDW_DATA LOCAL NOLOGGING;
CREATE BITMAP INDEX W_GL_OTHER_F_F61 ON W_GL_OTHER_F (MCAL_CAL_WID ASC) tablespace XXXDW_DATA LOCAL NOLOGGING;
CREATE BITMAP INDEX W_GL_OTHER_F_M2 ON W_GL_OTHER_F (DELETE_FLG ASC) tablespace XXXDW_DATA LOCAL NOLOGGING;

And finally compute statistics on partitioned table

BEGIN
dbms_stats.Gather_table_stats(
NULL,
tabname => 'W_GL_OTHER_F',
CASCADE => true,
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO');
END;

Database part is done, we need to do DAC changes that I will try to post later. Actually for DAC part, the document from Oracle is more than enough, but I will repeat some steps to finalize the document. My team from offshore has done good job on delivering this solution, a big thanks to them.

Vertical Text Title in OBIEE Answers

We had a requirement that report needs to show column titles vertically in the Answers report. We used to have similar thing for downloaded excel reports by using "mso-rotate:90" in "Use Custom CSS Style" before. But this time requirement is for answer reports. Similarly by adding the following statement into column heading formatting part, we achieved to have vertically aligned heading in the report as seen below;

"{writing-mode: tb-rl;filter: flipv fliph;}"

 

 

Override Session Variable in Answers

If you wanted to override a session variable and disable cache hits, you would use a syntax like the following in Answers Advanced Tab
SET VARIABLE LOGLEVEL=4, DISABLE_CACHE_HIT = 1;

SET VARIABLE OU_ORG=128;

 

Performance Tuning Tips for OBIEE

1. implement caching mechanism
2. use aggregates
3. use aggregate navigation
4. limit the number of initialisation blocks
5. turn off logging
6. carry out calculations in database
7. use materialized views if possible
8. use database hints
9. alter the NQSONFIG.ini parameters

http://www.oraclebidwh.com/2010/02/performance-tuning-in-obiee/

OBIEE is not an ETL tool!

just a reminder for whom using OBIEE as Excel Extraction tool;

"OBIEE is not an ETL tool. It is NOT designed to handle large amounts of non aggregated data. It’s designed to handle dimensional structured data with sufficient aggregation tables."

Changing the Connection Pool from Answers

Keeping Leading Zero's in Answers

Nice way to keep leading zero’s when exporting to excel from within OBIEE:

Add “mso-number-format:\@” to the Custom CSS Style Options of the appropriate column in the OBIEE report.

Excel rotating style

mso-rotate:90

UDDI Connection in JDeveloper

 

In the Connection Wizard, provide a connection name and specify the UDDI inquiry endpoint URL. The syntax of this URL is:

http://ohs_host:ohs_Port/registry_context/uddi/inquiry

ohs_host and ohs_Port have the following definitions:

·         ohs_host is the address of the Oracle Application Server host machine.; for example, server07.company.com

·         ohs_Port is the HTTP listener port assigned to OHS

registry_context is context root used to access the target registry instance, such as "registry" or "registrypub"

How to export Security Group to create UDML

Here is a command to extract security objects from an OBIEE repository using the UDML command, mind the “-S” in the end, it handles to export Security part only;

C:\oracle\OBIEE\OracleBI\server\Bin\nQUDMLGen.exe -U Administrator -P xxx123 -R D:\ALL\repository\temps\yaz\050310_XXX_BAW_CS.rpd -O D:\ALL\repository\temps\yaz\tekin.udml -S

C:\OracleBI\server\bin\nqudmlgen -U Administrator -P Administrator -R C:\OracleBI\server\Sample\samplesales\samplesales.rpd -O C:\OracleBI\server\Sample\samplesales\samplesales.udml -S

nqudmlgen: This is the actual export command
-U: This is the user flag, in my case Administrator
-P: This is the password flag, in my case Administrator since I'm using sample sales
-R: Is the source repository. I am sourcing from samplesales.rpd on a Windows box
-O: Is the output file. I am populating a file called samplesales.udml in the same folder as the source
-S: Exports only security objects

Here is the command to import the objects

nqudmlexec -U Administrator -P Administrator -I C:\OracleBI\server\Sample\samplesales\samplesales.udml -B C:\OracleBI\server\Sample\samplesales\samplesales.rpd -O C:\OracleBI\server\Sample\samplesales\samplesales2.rpd

nqudmlexec : This is the actual import command
-U: This is the user flag, in my case Administrator
-P: This is the password flag, in my case Administrator since I'm using sample sales
-I: This is the input script. In this case, the input to this is the output from the first command
-B: This is the base repository. In this case, your base repository will be your target repository, the one where you want to migrate the users to.
-O: This is the output repository. This command makes a copy and applies the changes in the UDML to that copy. So you need to specify an output file. This is the file that will have the changes. The base file will not have the new users since it is just used to make this copy.

 Example;

EXPORT;

C:\oracle\OBIEE\OracleBI\server\Bin\nQUDMLGen.exe -U Administrator -P xxx-R D:\ALL\repository\temps\yaz\050310_XXX_BAW_CS.rpd -O D:\ALL\repository\temps\yaz\tekin.udml

IMPORT;

C:\oracle\OBIEE\OracleBI\server\Bin\nQUDMLexec.exe -U Administrator -P xxx -I D:\ALL\repository\temps\yaz\tekin.udml -B D:\ALL\repository\temps\yaz\050310_BAW.rpd -O D:\ALL\repository\temps\yaz\050310_BAW.rpd

How to delete item by UDML

How to delete item by UDML

We are complaining about “UDML works great but we cannot delete item by using UDML”. Here is how to delete;

1.       Copy the item you want to delete as UDML

DECLARE FOLDER ATTRIBUTE "XXX - BAW Customer Service".."- Task Created By"."Created By User Name" AS "Created By User Name" UPGRADE ID 2160852698 LOGICAL ATTRIBUTE  "Core"."Dim - Created By"."User Name" OVERRIDE LOGICAL NAME

            ALIASES ("User Name")

            PRIVILEGES ( READ);

2.       And change it as below with DELETE command, save it.

DELETE FOLDER ATTRIBUTE "XXX - BAW Customer Service".."- Task Created By"."Created By User Login";

3.       Run nQUDMLexec.exe, that’s it!

Length Semantics in DB Profile

If we describe any table in OBIEE environment, we can find that all the string column sizes are 4 times the actual size.

For e.g.; In table W_SUPPLIER_ACCOUNT_D

PAY_TERMS_CODE        VARCHAR (200)

PAY_TERMS_NAME        VARCHAR (320)

If we refer the same columns in Informatica and Oracle BI Repository, the column sizes would be  VARCHAR (50) and VARCHAR (80) respectively.

The reason behind this anomaly is that DB profile is set with Length Semantics to BYTE instead of CHAR.

i.e.; PAY_TERMS_CODE        VARCHAR (200) = VARCHAR (200 BYTE) = VARCHAR (50 CHAR)

So going forward whenever you extend any base table for adding a string column, specify the syntax as CHAR explicitly.

i.e.;  alter table W_SUPPLIER_ACCOUNT_D

    add X_VAT_CODE VARCHAR2(50 CHAR);

Purging RPD Cache

We can purge RPD cache without opening rpd online mode using “Oracle BI Presentation Services Administration”

TO_NUMBER in OBIEE

You can use CAST function to convert from one datatype to another datatype. and you can create a new logical column in your RPD , that use this function;

cast(MONTH("Fixed Assets Depreciation".DEPRN_RUN_DATE) as char)

Technorati Tags: OBIEE

iBot Job Scheduler Exit Code

We created conditional iBot request, and schedule it to run every morning. But when we examine the Job Scheduler log file, we noticed that there is “Exit Code” column, which is sometimes different from one run to another. Exit Code column apparently shows how many successful deliveries being done with that run.

The ExitCode of an instance is set to the number of successful deliveries. The count corresponds to the number of successful deliveries to devices, and there may be more than one device for each recipient of an iBot.

Oracle Null

These are not necessarily unexplainable idiosyncrasies. Rather, this is a list
of Null usage cases that may surprise me personally. Note: Null value is
spelled "Null" in here.


(1) Null doesn't count in aggregate function.
create table testnull (a number);
insert into testnull values (1);
insert into testnull values (2);
insert into testnull values (null);
select count(*) from testnull; <-- returns 3
select count(a) from testnull; <-- returns 2

create table test (name varchar2(10), value number);
insert into test values ('xx', 12);
insert into test values ('xx', null);
insert into test values ('yy', 123);
select name, count(*) from test group by name;
select name, count(value) from test group by name;

NAME COUNT(VALUE)
---------- ------------
xx 1 <-- would be 2 if select name, count(*) ...
yy 1


(2) Inserted null string converted to Null.
create table testnull (a varchar2(10));
insert into testnull values (null);
insert into testnull values ('');
insert into testnull values ('' || 'Hello');
insert into testnull values (null || 'Hello');
select dump(a) from testnull;

DUMP(A)
---------------------------------------------
NULL
NULL
Typ=1 Len=5: 72,101,108,108,111
Typ=1 Len=5: 72,101,108,108,111


(3) Where can Null be compared?
select decode(null, null, 'Null equals Null in DECODE') from dual;

DECODE(NULL,NULL,'NULLEQUA
--------------------------
Null equals Null in DECODE

Oracle SQL Reference says "In a DECODE function, Oracle considers two nulls to
be equivalent. If expr is null, then Oracle returns the result of the first
search that is also null."

Another place where Null can be compared is in range partition definition,
where MAXVALUE is greater than Null (Ref. J. Lewis "Practical Oracle8i",
p.241).


(4) [Related to (3)] Unique constraints.
create table test (a number);
create unique index unq_test on test (a);
insert into test values (null);
insert into test values (null); <-- No error.
You *are* able to insert another Null without getting ORA-1 (unique constraint
violated).

create table test (a varchar2(1), b varchar2(1));
create unique index unq_test on test (a, b);
insert into test values ('A', null);
insert into test values ('A', null); <-- Get ORA-1
truncate table test;
insert into test values (null, null);
insert into test values (null, null); <-- No error
So if all columns are null, the unique constraint will not be violated. If one
or more columns have non-null values, the constraint takes effect.


(5) Unknown OR True returns True, Unknown AND False returns False.
create table test (a number, b number, c number);
insert into test values (3, 4, null);
select 'Got it' from test where b < c or a < b; <-- returns 'Got it'
select 'Got it' from test where not (b > c and a > b); <-- returns 'Got it'

Source : http://yong321.freeshell.org/computer/OracleNull.txt

Action Link for OBIEE

Prerequisite : The security setup to reach OBIEE from E-Business Suite must be completed before creating Action Link (Action link screens enable to drill back into the transactional application screen from an Oracle BI request or dashboard).

Source : Metalink Note - 552735.1

In order to generate an Action Link you will first have to do the following:

1. Identify the Oracle E-Business Suite Application page/function that you want to link to. Obtain the function_id of that page and identify the querystring parameters required by that page. This will have to be done by going through Oracle E-Business Suite documentation.
2. Identify the Oracle E-Business Suite table that will support the parameters needed for the Oracle E-Business Suite function (page) that you want to build an Action link to and create this physical table in the Oracle E-Business Suite OLTP schema as an opaque view.

Here the OE_ORDER_HEADERS_ALL table was chosen because it is at the grain of the Order Header and supplies the HEADER_ID that we can use to join to the warehouse tables that contain the Order Header information. The function_id of the Oracle E-Business Suite page for Order Details and the parameters supported by that page were also identified from Oracle E-Business Suite documentation.

The Action Link URL is generated by calling the FND_RUN_FUNCTION.GET_RUN_FUNCTION_URL() function in the Oracle E-Business Suite Database Schema. For example:

SELECT
HEADER_ID,
fnd_run_function.get_run_function_url(
CAST(fnd_function.get_function_id('ISC_ORDINF_DETAILS_PMV') AS NUMBER),
CAST( VALUEOF(NQ_SESSION.OLTP_EBS_RESP_APPL_ID) AS NUMBER),
CAST( VALUEOF(NQ_SESSION.OLTP_EBS_RESP_ID) AS NUMBER),
CAST( VALUEOF(NQ_SESSION.OLTP_EBS_SEC_GROUP_ID) AS NUMBER),
'HeaderId='||HEADER_ID||'&pFunctionName=ISC_ORDINF_DETAILS_PMV&pMode=NO&pageFunctionName=ISC_ORDINF_DETAILS_PMV',
NULL) as ORDER_HEADER_ACTION_LINK_URL
FROM OE_ORDER_HEADERS_ALL


The parameters to the function are:

p_function_id in number,
p_resp_appl_id in number,
p_resp_id in number,
p_security_group_id in number,
p_parameters in varchar2 default null,
p_override_agent in varchar2 default null

Here p_function_id is the function_id of the page that you want to navigate to, the next three parameters pass the security context to Oracle E-Business Suite. The value of these session variables will be set by the initialization block described in sub-section "Creating Init Block for setting Oracle E-Business Suite Context". The fourth parameter is optional and is used if the page you are navigating to accepts parameters. In many cases if you want to navigate to a particular record on the page you are navigating to (Action Links typically do this), you will need to supply those querystring parameters here. The function call returns a URL to the desired function with encrypted querystring parameters.

The next step is joining this opaque view to the base fact table in the Data Warehouse schema. This join represents a join of tables in different database schemas and will therefore happen in the Oracle BI Server.

IMPORTANT: Ensure sufficient filters are applied when requesting any columns from this opaque view so that a small data set is returned to the Oracle BI Server to join with the results from the warehouse schema. For demo environments, where the EBS table contains only thousands of rows, filters can be ignored. However, customer implementations will typically contain millions of rows so appropriate filters are required.

Map into the logical and presentation layers

Map the URL column from the opaque view into the logical star where you want to create the Action Link. Then expose this logical column in the appropriate presentation catalog. Also ensure that the user who will be logging into Oracle E-Business Suite Applications is assigned access to the appropriate presentation catalogs. For example:

Oracle BI Answers Configuration

When including this Action Link column in a report, edit the column properties to indicate this is of type ‘Hyperlink’. That will automatically make this a clickable link in an Answers report. Further customization can be done to embed an image instead of the text.

Oracle BI Presentation Catalog Configuration

Ensure the user who will be logging into Oracle E-Business Suite Applications is set up in the presentation catalog as a user with the appropriate permissions. You can make the dashboard that you want to embed into Oracle E-Business Suite the default dashboard for that user. This will take the user directly to that dashboard when they click on the hyperlink in Oracle E-Business Suite.

Calling Bursting from XML Template

With the standalone BI Publisher, we have useful Bursting tool in order to split and send the reports to relevant people via email. However, when you are using standalone BI Publisher, you may need to connect E-Business Suite and need to initialize the session for multi-org, VPD, etc. to get the relevant data. In order to do that, you will need to use report triggeres to cal a PLSQL wrapper to initialize the session. With the help of data templates, we can easily call PLSQL functions.

There are 3 steps you need to follow up to be successfull on calling PLSQL function;

1. Add a defaultPackage declaration to the Data Template definition, like -
" Version="1.0">
2. Create a package with above name and add a boolean function to it. For my work I gave the function name as beforeReport.
3. The parameters used in the Data Template should be declared (same name/datatype) in the package specification.

<datatemplate name="DATA" defaultpackage="XX_OBIEE_UTIL" version="1.0">
<dataquery>
<sqlstatement name="ROW">
<!--[CDATA[SELECT * from XX_YOUR_TABLE a ORDER BY a.region, a.CUSTOMER_NUMBER ]]-->
</sqlstatement>
</dataquery>
<datastructure>
<group name="ROW" source="ROW">
<element name="RUNDATE" value="RUNDATE">
<element name="xxEMAIL" value="xxEMAIL">
<element name="CUSTOMER_NUMBER" value="CUSTOMER_NUMBER">
<element name="CUSTOMER_NAME" value="CUSTOMER_NAME">
<element name="PRODUCT" value="PRODUCT">
<element name="REVENUE_TYPE" value="REVENUE_TYPE">
<element name="REGION" value="REGION">
<element name="DM" value="DM">
<element name="CAE" value="CAE">
<element name="SECTOR" value="SECTOR">
<element name="CURRENT_NET_REVENUE" value="CURRENT_NET_REVENUE">
<element name="PREVIOUS_NET_REVENUE" value="PREVIOUS_NET_REVENUE">
<element name="VARIANCE" value="VARIANCE">
<datatrigger name="beforeReport" source="XX_OBIEE_UTIL.beforeReport()">
</datatrigger>
/datastructure></datatemplate>

XSL Formatting Objects

When you design BI Publisher Template for the reports, you will need XSL Formatting Objects to make nice reports, like making the cell red as you can see from the picture. The following link will give you many formatting properties that you can make use of for your design.

XSL Formatting Objects

Math and XSLT

The following link is for understanding the Math and XSLT that you may need to focus on when you build your BI Publisher templates.

Math and XSLT

DBMS_XMLGEN

DBMS XMLGEN is a PL/SQL package that allows programmers to extract XML data from Oracle database tables. It might be useful when you need a XML file to create XML publisher layouts. Here are 2 examples on how to use it;

SELECT DBMS_XMLGEN.getXML('SELECT * FROM emp') FROM dual;

or
DECLARE
ctx DBMS_XMLGEN.ctxHandle;
xml CLOB;
BEGIN
ctx := dbms_xmlgen.newcontext('select * from emp');
dbms_xmlgen.setrowtag(ctx, 'MY-ROW-START-HERE');
xml := dbms_xmlgen.getxml(ctx);
dbms_output.put_line(substr(xml,1,255));
END;
/