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!

Conflict? What Conflict?

Sometimes you just have to RTFM - as in "Read the free manual". If you choose to do a Google search instead, chances are you'll be redirected to the free manual or a blog post that references the free manual. If you choose to do a search on My Oracle Support (I'm so used to calling it Metalink), you'll probably find a note that regurgitates contents from the free manual and/or makes a reference to the free manual. (Sometimes, as an added bonus, the same information is repeated in different free manuals). So, effectively, you end up Ring TFM!


Oracle eBusiness Suite comes with several seeded concurrent programs that have incompatibilities defined. The Conflict Resolution Manager (CRM) does it's thing to ensure that no conflicting programs run simultaneously. If you don't create your own conflict domains (a conflict domain is an abstract representation of the groupings used to partition your data), then you are limited to the seeded "Standard" conflict domain, which basically means that the incompatibility is global. However, to maximize the concurrency in a multiple organization environment, you can set up conflict domains for your operating units.


For more information, RTFM - 

Oracle E-Business Suite Multiple Organizations Implementation Guide Oracle E-Business Suite System Administrator's Guide - Configuration

Incentive Compensation Payment Batch Action LOV

This one didn't take too long to figure out, but I'd like to share it just the same.

The Maintain Payment Batches screen in Oracle Incentive Compensation has a drop down for actions to be performed on selected batches.

The customer's requirement was to restrict the Pay action to a few select users. 

The poplist data is dynamically populated using  the base query :

SELECT * FROM 

(select lookup_code, meaning,decode(lookup_code , 'REFRESH','1','FREEZE','2','UNFREEZE','3','DELETE','4','PAY','5') ordactions 

 from cn_lookups 

where lookup_type = 'PAYMENT_BATCH_ACTION') QRSLT 

WHERE (lookup_code in ('DELETE','REFRESH','FREEZE','UNFREEZE','PAY')) 

ORDER BY ordactions ;

To remove any of the actions from the poplist for a responsibility, all you have to do is exclude the corresponding function from that responsibility.

And voila!

For a list of functions corresponding to the actions, look for functions that start with CN_PMT_PAYRUN.