Thursday, January 12, 2012

Obtaining the Job Hierarchy with SAP BusinessObjects

BusinessObjects Data Integrator is a well-known market-leading, enterprise-class data integration tool part of the SAP BusinessObjects suite.

Many enterprises prefer for its prebuilt data integration capabilities for SAP source-specific enterprise applications, as well as Siebel, PeopleSoft, and Oracle. Furthermore, not many other products provide deeper metadata-based integration with the market-leader ERPs.

The BusinessObjects 6.5 Data Integrator.

As every ETL tool and  in order to accelerate the BI deployment, BusinessObjects Data Integrator  shows an intuitive graphic environment for ETL mapping, for easily managing, documenting and validating each design phase of an ETL job.

If your ETL environment is simple enough you don't need real job scheduler, and you can simply rely on the Cron or the Windows Scheduler. But, for istance, if you need to manage any dependencies between jobs, you often have to develop your own scheduler and managing tool.

If your BusinessObjects metadata repository is Oracle-based, then you're lucky - you can rely on the full capabilities of Oracle PL/SQL language.

In this article is shown how to obtain the jobs hierarchy with a simple query over the BusinessObjects metadata repository. The example refers to the BusinessObjects v. 6.5 and Oracle 11g:

SELECT   parent_obj_type   , parent_obj   , parent_obj_desc   , descen_obj   , descen_obj_type   , descen_obj_desc   , LEVELFROM   schemaname.AL_PARENT_CHILDSTART WITH    descen_obj_type='TABLE'    AND descen_obj='CURRENT_STOCK_STATS'  CONNECT BY    descen_obj=PRIOR parent_job    AND descen_obj_type=PRIOR parent_obj_typeWHERE    parent_obj='JOB'    ;

In this query we made use of the 'START WITH... CONNECT_BY...' Oracle clause, that allows the usage of recursion in order to build the desired hierarchy.

The Oracle 'START WITH... CONNECT BY...' clause.

This construct can be used with any table containing hierarchical data, for example any kind of parent->child, boss->employee or thing->parts relationships.
The root is specified by the 'START WITH...' and the father-child relationship by the 'CONNECT BY' keywords.

For other information, see:

No comments:

Post a Comment