eBS BIP Bursting Control files presentation from STR Software

If you're still struggling with bursting control files in spite of  the detailed BIP documentation, here's a quick primer (and a plug for their software) from Brent Lowe of STR Software.


http://www.strsoftware.com/resources/generate-documents-from-my-erp/understanding-bi-publisher-bursting-control-files-in-oracle-ebs-r12/

Sortable Excel Output - Rows to Columns using RTF templates in BI Publisher

I had a math professor who said stuff like "The problem is always tough until you find the solution", which makes sense, if you don't think about it. 


I was tasked with solving what seemed like a tough problem. An existing BIP report used this XML structure to generate MS Excel output using an RTF template.


G_MAIN
  G_CUSTOMER
    CUSTOMER
    TOTAL_SALES
    G_PRODUCTS
       G_PRODUCT
           PCT_TOTAL
           SALES_AMT


The output from a BIP report needed to be changed so that it went from this

Customer	Total $		Breakdown by Product
		Product	% of Total	Sales $
Cust1	100,000.00	Prod1	25.00%	25,000.00
		Prod2	30.00%	30,000.00
		Prod4	45.00%	45,000.00
Cust2	200,000.00	Prod2	40.00%	80,000.00
		Prod3	25.00%	50,000.00
		Prod5	35.00%	70,000.00
Cust3	1,000,000.00	Prod1	30.00%	300,000.00
		Prod3	40.00%	400,000.00
		Prod4	15.00%	150,000.00
		Prod5	15.00%	150,000.00
Cust4	2,000,000.00	Prod1	20.00%	400,000.00
		Prod2	20.00%	400,000.00
		Prod3	20.00%	400,000.00
		Prod4	20.00%	400,000.00
		Prod5	20.00%	400,000.00

to this

Customer	Total $	Prod1		Prod2		Prod3		Prod4		Prod5
		% of Total	Sales $	% of Total	Sales $	% of Total	Sales $	% of Total	Sales $	% of Total	Sales $
Cust1	100,000.00	25.00%	25000	30.00%	30000	0.00%	0	45.00%	45000	0.00%	0
Cust2	200,000.00	0.00%	0	40.00%	80000	25.00%	50000	0.00%	0	35.00%	70000
Cust3	1,000,000.00	30.00%	300000	0.00%	0	40.00%	400000	15.00%	150000	15.00%	150000
Cust4	2,000,000.00	20.00%	400000	20.00%	400000	20.00%	400000	20.00%	400000	20.00%	400000

Getting the XML data to include all products, even ones that the customer did not purchase, was easy - good old data densification. In addition, an independent group of all products was required to display product names in the labels. Effectively, the XML structure changed (a little) to



G_MAIN
 G_ALLPRODUCTS
  G_CUSTOMER
    CUSTOMER
    TOTAL_SALES
    G_PRODUCTS
       G_PRODUCT
           PCT_TOTAL
           SALES_AMT



To get the XML rows to appear as columns wasn't too bad either. All that was needed was a <?for-each@cell:grpname?> directive and the BIP engine takes care of the rest.

 The RTF template looks like this:

Customer #	Total $	F PRODUCT		end
		F % of Total	Sales $	end
FECust1	9,999.99	F 0.00% 99.99		end  E

where F = <?for-each@cell:grpname?> , end = <?end for-each-group?> 

FE = <?for-each:groupname?>, E =  <?end for-each?>

Free X Windows Emulator to run Oracle Installer

After getting frustrated with 30-day trial licences for X windows emulators like X-Win32, Hummngbird etc, I googled "Oracle Installer xwindows emulator" and the first hit was to Andrew Fraser's blog post.  Nifty little DSL (Damn Small Linux) tool that makes installing Oracle products that much easier. Thanks Andrew!

The Digital Divide

I just finished reading "The Digital Divide: Arguments for and against Facebook, Google, Texting and the Age of Networking" by Mark Bauerlein. As I was reading the book, I kept coming back to the digital divide that is so prevalent in corporate IT where archaic processes are still preferred simply because "They work" or "It's how we do things here" or "Why bother with something new? That will involve learning and training". It amazes me that in this day and age "tried and tested" triumphs over "new and improved", even if it means slower turn-around time, more resources and consequently more dependence on those resources. It certainly makes those resources feel indispensable, but only until the process changes to be less resource intensive thus rendering them useless. As Thomas Friedman and Michael Mandelbaum wrote in "That Used to Be Us", skilled folks doing routine tasks can and will always be replaced by cheaper labor, automation or both. It's an interesting book, worth a read. 


Every business organization is challenged with integrating data residing in different database systems and every organization uses a different approach to overcoming this challenge. The "tried and tested" approach invariably involves extracting data from the source database, creating an ascii file, transferring the flat file to the target database server, and loading/transforming the flat file into the target database. There are several "new and improved" solutions that Oracle has available to handle data integration between a non-Oracle database and an Oracle database, such as Oracle Streams, Golden Gate, Oracle Data Integrator and Oracle Database Gateways. In this blog post, I dig into synchronous data integration using database gateways and heterogeneous services.


Oracle Database Gateways address the needs of disparate data access. In a heterogeneously distributed environment, gateways make it possible to integrate with any number of non-Oracle systems from an Oracle application.
DB gateways and the Heterogeneous Services (HS) component of Oracle DB, enable transparent data access to non-Oracle DBs using familiar tools (TOAD, SQL Developer, OAF, etc) to run queries against non-Oracle DBs. In most cases Oracle SQL can be used for querying non-Oracle DBs - very rarely will you need to use the HS passthrough feature to communicate with the remote DB in its own language. Applications can be developed  using a consistent Oracle interface for both Oracle and non-Oracle data sources.


How it works:

1. The client application sends a query using Oracle Net to Oracle Database.
   
2. Heterogeneous Services and the gateway converts the SQL statement into a SQL statement understood by the non-Oracle database system.
   
3. Oracle Database sends the query to the gateway using Oracle Net.
   
4. For the first transaction in a session, the gateway logs in to non-Oracle database system using a user name and password that is valid in the non-Oracle system.
   
5. The gateway retrieves data using non-Oracle database system SQL statements.
   
6. The gateway converts retrieved data into a format compatible with Oracle Database.
   
7. The gateway returns query results to Oracle Database, again using Oracle Net Services.
   
8. Oracle Database passes the query results to the client application using Oracle Net. The database link remains open until the gateway session is finished, or the database link is explicitly closed.

Oracle Database Gateway for ODBC (DG4ODBC) gives you the ability to connect to any non-Oracle ODBC data source. DG4ODBC, as the name suggests, works with an ODBC driver. The ODBC driver must be installed on the same machine as the gateway. Once you install and  configure the ODBC driver and the DG4ODBC gateway, you will need to modify the tnsnames.ora file on the Oracle DB to be able to connect to the ODBC data source. For more details on installation and configuration, RTFM - Oracle Database Gateway Installation and Configuration Guide, Oracle Database Gateway for ODBC User's Guide and Oracle Database Heterogeneous Connectivity User's Guide.


With DG4ODBC (and a robust design and lots of testing) , the "new and improved" approach will eliminate the need to create an ascii file, transfer the flat file to the target database server, and will speed up the loading/transforming the data into the target database. 


Make "new and improved" the new "tried and tested"! 

Densify your data!

Oracle SQL for analysis and reporting ROCKS!


Data stored in traditional OLTP systems isn't always easily usable in analytic reporting systems - the data is almost always geared towards operational reporting systems. Oracle SQL for analysis and reporting gives you options to take your operational data and convert it into meaningful information using analytic functions.


One of the frequent requirements in a reporting system is to fill in the gaps i.e. create data where it doesn't exist, but if it did, it would make the data more consistent and the reporting would be much easier. In BI speak, this is known as data densification - the process of converting sparse data into dense form.  


This is easier to explain with an example. Consider the following data set which is a result of the SQL query


select salesperson, period, item, amount
from items_sold
where salesperson = 'S1'
  and period between P1 and P3;


Figure 1

Sales Person	Period	Item	Amount
S1	P1	W1	100
S1	P1	W2	200
S1	P1	W4	400
S1	P1	W4	200
S1	P2	W3	200
S1	P2	W5	100
S1	P2	W1	20
S1	P3	W5	400
S1	P3	W2	200

The output sought is:

Figure 2

Sales Person	Period	Item	Total
S1	P1	W1	100
S1	P1	W2	200
S1	P1	W3	0
S1	P1	W4	600
S1	P1	W5	0
S1	P2	W1	20
S1	P2	W2	0
S1	P2	W3	200
S1	P2	W4	0
S1	P2	W5	100
S1	P3	W1	0
S1	P3	W2	200
S1	P3	W3	0
S1	P3	W4	0
S1	P3	W5	400

i.e. "Show me everything sales person S1 sold in periods P1, P2 and P3 even if he sold 0 amount of an item".


Step 1: Group the data by period and item to get the total amount 

select salesperson, period, item, sum(amount)
from items_sold
where salesperson = 'S1'
  and period between P1 and P3
group by salesperson, period, item;


Step 2: Densify the data - fill in the gaps using Partitioned Outer Joins


Assuming that the period values are stored in the periods  table -


select q1.salesperson, q2.period, q1.item, nvl(q1.amt,0)
from
(

select salesperson, period, item, sum(amount) amt
from items_sold
where salesperson = 'S1'
  and period between P1 and P3
group by salesperson, period, item

) q1
PARTITION BY (q1.salesperson, q1.item)
RIGHT OUTER JOIN
(
select period from periods
where period between P1 and P3 ) q2
on ( q1.period = q2.period )


So what did I just do? Basically, I took the query from step 1, created an independent sub set of rows using the PARTITION clause - effectively the set of all items S1 sold between P1 and P3


Figure 3

S1	W1
S1	W2
S1	W3
S1	W4
S1	W5

I outer join this subset with a static set of periods (using the inline view q2). Run the new query and you get the desired output as shown in Figure 2. 

Densify away!