OraNA :: Oracle News Aggregator

I found an interesting article today in amongst my Google alerts. It related to a topic of conversation at the recent TDWI conference in May about the issue of adhoc query performance within data warehouse environments. The article, on the whole, was very good (in my opinion) and included comments by Oracle's vice-president of database marketing, Willie Hardie.

The full article in Enterprise Systems is available here.

The article examines the reasons why business users feel their queries are taking too long and what steps companies are taking to try to improve query performance. The basic reasons for poor query performance was given as being down to two issues:

• success of pervasive BI - so more users are running adhoc queries
• more data - users want access to more and more data, both in terms of level of detail and time span.

I would add a third reason, which is increasing sophistication. Simply presenting users with reports that show revenue and expenses for the latest month, quarter and year to date are not adequate in today's highly competitive environment. Business users want to know about trends - this period vs last period, this year vs last year, this period vs the same period last year, like-for-like, shares, ranks, forecast, customer segmentation, market basket analysis. The list goes on and on and on.

Here is a direct quote from the article:

......The RDBMS -- or, more specifically, the Oracle RDBMS -- is an unmatched analytic workhorse, Hardie argues. Oracle is one of the biggest data warehousing players in the business, he points out, and the Oracle database powers some of the largest DWs in existence.

"The Oracle database is proven to be the fastest database out there for both transactional systems and data warehousing systems, across all scales, from small to extremely large systems. You ask any Oracle customer out there and they'll all give you the same answer: Oracle is the fastest database out there on the market right now," he claims.

What I would add is the Oracle Database is the only database with embedded multidimensional OLAP, which is fine tuned for adhoc query performance, query scaleability and, most importantly, calculation power. As I stated above it is no longer about simply what is happened in the last trading period, BI analysis is now all about comparisons, trends and KPIs (calculations) at aggregate levels with the ability to drill right through to the lowest level of detail.

As the article quite clearly acknowledges there is a trend to opening up the corporate data warehouse to more and more users, which means query performance and scalability are becoming increasingly important. Only Oracle Database has specific built-in optimisations, such as OLAP and data mining, to meet these growing requirements.

For those of you new to Oracle OLAP Option and Oracle Data Warehousing you can get more information from these links:

Oracle OLAP Option on OTN
Oracle OLAP Option Forum on OTN
Oracle OLAP Option Wiki
Oracle OLAP Option on Oracle.com
Oracle Data Warehousing on OTN
Oracle Data Warehousing on Oracle.com
Optimised Warehouse Initiative

I’m not sure how much significance we should attach to it (in terms of it being new news), but there’s an updated product roadmap document for OWB now available on OTN. Apart from the details we all know about such as OBIEE support in the next release, ODI knowledge module support and so on, there’s a fairly clear statement towards the end of the document that says that OWB and ODI will eventually merge:

“Short Term Strategy

Oracle purchased Sunopsis in October 2006 and re-branded it as Oracle Data Integrator (ODI). ODI’s mission is to enhance the Oracle Fusion Middleware offerings, which require broad support to heterogeneous sources and targets. Warehouse Builder’s mission continues to be to support the Oracle Database. Today, ODI and OWB are released separately. For the short term, both products will continue to deliver stand-alone releases.

Although released independently, both products are currently being developed with a common goal of integrating with each other and, ultimately, merging into a single product. In the next release, for example, OWB will support the knowledge module framework developed by ODI. And in future releases, both products will share the same user interface framework and will be able to launch each other processes.

Long Term Direction

Oracle is committed to protect the investments customers have made in both Oracle Warehouse Builder and Oracle Data Integrator. In a future release, Oracle will deliver a unified data integration product that protects customer investments in both products.”

To me this is pretty significant as it’s the first time I’ve heard anyone say, officially, that ODI and OWB will eventually merge. I know it’s common sense that this would be the case (architecturally and in how they are both used, they’re so similar to each other), but up until now, like Oracle OLAP and Essbase, the message from Oracle has been that they are different products that serve different use cases. As this was clearly untenable this to me is a welcome change of message, and it’ll be interesting to see how the two products converge over time.

In the previous postings in my Oracle BI Apps series, I’ve looked at the BI Apps architecture, installation and configuration, and performing the initial data load. As one of the key benefits of the BI Apps is its extensibility, one of the first things I’d want to know if I was a customer is exactly how you go about extending it. Oracle have a published methodology for adding new data into the packaged data warehouse both in terms of new rows, new columns and even new data sources, but as this is all based around the Informatica ETL tool, that Siebel originally did an OEM deal with and Oracle now support at least until they can get ODI up to speed, the first thing you’re really going to need to know is how to work with Informatica. The good news is that if you’re familiar with Oracle Warehouse Builder the two tools are actually fairly similar, at least in terms of the developer GUI, and so if you’re comfortable with one, as I am, you’ll quickly pick up the other.

So what I thought I’d do then, before diving into the methodology used for extending the data warehouse, is run through an end-to-end example of building some Informatica Mappings, creating a workflow and using it to load some data into a set of Oracle tables. The data and scenario I’m going to use is actually from our OWB class that we teach, where at one point we load data from a set of normalized tables and file into a set of staging tables - this will allow me to compare building the scenario in OWB and then in Informatica.

Informatica PowerCenter comes in both server and client versions, with all of the design work being carried out in the client. To start off the project then, I connect to the PowerCenter Repository Manager and create a new folder in my repository, which performs a similar function to a project in OWB.

You can see in the repository all of the folders containing mappings to take data out of the various Oracle BI Apps source systems - Oracle E-Business Suite, Peoplesoft, SAP, Siebel - plus any custom ones you add. It’s worth noting at this point that Informatica PowerCenter, as provided by the BI Apps, is licensed only for loading the BI Apps data warehouse, you can’t use it for anything else, so what I’m doing here is only just for learning, you can’t adopt PowerCenter as your general ETL tool unless you go out an license it for general use.

Once you create your folder, you then switch over within the same application to the “Source Analyzer”. This is a feature of the Repository Manager, like the Oracle Module wizards in OWB, that connects to a set of relational tables and imports the metadata into the repository. This seems to work off of ODBC rather than native Oracle connections, however when mappings run later on they can be configured to use OCI in the same way that OBIEE lets you. Suffice to say that connecting to non-Oracle sources is very easy, much easier than OWB or even ODI where you have to locate, and then fiddle around with specific JDBC drivers before you can work with these sources in a project.

Once you’ve brought your sources in it’s a similar process to bring in your target tables, again this is done via ODBC with the ability to switch to OCI during the mapping phase.

Flat files are also pretty straightforward to bring in, with an Excel-style import wizard that performs much the same function as the OWB one.

Of course it’s at this stage that you start to notice a few differences between Informatica and OWB. With OWB, the wizard that steps you through file importation is building an SQL*Loader control file, whereas of course the Informatica equivalent is setting up some metadata that drives an internal Informatica file reading process, there’s no SQL*Loader being used. Also, in OWB you often take these file definitions and turn them into external tables whereas there’s no real concept of this in Informatica. The mapping process is even more different, but I’ll get on to this in a moment.

Once you’ve imported in your sources and targets, you can review the list of tables in the Repository Manager.

Now that we’ve got some data in place, it’s time to do the first mapping. In this one, I’m taking data from three product source tables, joining it and loading it into a staging table. In OWB, the mapping would look like this:

The equivalent in Informatica, using the Informatica Designer application, would look like this:

A few points to note. Firstly, it’s actually very similar to OWB; each operator has input and output mappings, you drag and drop sources and targets onto the palette and then connect them using lines and transformation operators. On first glance, OWB has a richer set of transformations out of the box (there’s no equivalent of name and address matching, match-merge and so on) whilst Informatica has better out of the box support for alternative data sources and targets such as XML, CDC, HTTP and so on. Data sources in Informatica mappings are accessed through a “Source Qualifier” object that lets you filter the data before it goes into the mapping (removing the need for filter operators just after objects), and interestingly joins only seem to support two inputs whereas of course in OWB you can add several.

When I started thinking about joins it got me thinking about how, under the covers, Informatica is doing the data integration and loading. With OWB, the database does all the integration using SQL selects, inserts, joins, table functions, merge commands and so on, whereas in Informatica you’ve got a separate hub engine that does the work. Now in some ways that seems to offer advantages - you can just drag and drop any data sources and targets together and the Informatica Integration Service just gets on with working out the optimal, row-by-row way to get data from A to B. With OWB, you’ve got to be careful about the SQL that you’re getting OWB to generate which offers advantages and disadvantages - in the hands of an expert you can built some pretty fast, efficient integration, but it’s fairly easy to get OWB to generate some pretty hairy SQL if you’ve got multiple extract and load stages in a single mapping. I suspect with Informatica the environment is more controlled, more predictable but looses some of the capability to produce super-fast ETL routines that you could potentially get with OWB. I’ll have to see how things transpire once we start using the tool on more projects.

So once you’ve pulled all your mappings together it’s time to create a workflow, which in OWB terms is a process flow. Creating a workflow is pretty similar to performing the same task in OWB, you start off by adding tasks to the workflow process, connect them to the relevant data sources (this is where you can switch to an OCI connection) and string them all together.

Then it’s just a case of pressing the “Go” button and checking that the workflow has executed correctly. In OWB you’d use the Control Center Manager to do this, in Informatica you’d use the Workflow Monitor, in my case most of the processes have run but one of them, that uses a flat file, has failed.

Taking a look at the error log, I can see that any flat files obviously need to be in the /infa_shared/src_files directory.

After copying the file to the correct location the process then executed correctly.

So, that’s a simple set of mappings taken place. Of course there’s a lot more to Informatica than just this, but I think now we’re ready to use the tool in earnest to start customizing the BI Apps data warehouse. More on that in a couple of days.

In the previous postings in my Oracle BI Apps series, I’ve looked at the BI Apps architecture, installation and configuration, and performing the initial data load. As one of the key benefits of the BI Apps is its extensibility, one of the first things I’d want to know if I was a customer is exactly how you go about extending it. Oracle have a published methodology for adding new data into the packaged data warehouse both in terms of new rows, new columns and even new data sources, but as this is all based around the Informatica ETL tool, that Siebel originally did an OEM deal with and Oracle now support at least until they can get ODI up to speed, the first thing you’re really going to need to know is how to work with Informatica. The good news is that if you’re familiar with Oracle Warehouse Builder the two tools are actually fairly similar, at least in terms of the developer GUI, and so if you’re comfortable with one, as I am, you’ll quickly pick up the other.

So what I thought I’d do then, before diving into the methodology used for extending the data warehouse, is run through an end-to-end example of building some Informatica Mappings, creating a workflow and using it to load some data into a set of Oracle tables. The data and scenario I’m going to use is actually from our OWB class that we teach, where at one point we load data from a set of normalized tables and file into a set of staging tables - this will allow me to compare building the scenario in OWB and then in Informatica.

Informatica PowerCenter comes in both server and client versions, with all of the design work being carried out in the client. To start off the project then, I connect to the PowerCenter Repository Manager and create a new folder in my repository, which performs a similar function to a project in OWB.

You can see in the repository all of the folders containing mappings to take data out of the various Oracle BI Apps source systems - Oracle E-Business Suite, Peoplesoft, SAP, Siebel - plus any custom ones you add. It’s worth noting at this point that Informatica PowerCenter, as provided by the BI Apps, is licensed only for loading the BI Apps data warehouse, you can’t use it for anything else, so what I’m doing here is only just for learning, you can’t adopt PowerCenter as your general ETL tool unless you go out an license it for general use.

Once you create your folder, you then switch over within the same application to the “Source Analyzer”. This is a feature of the Repository Manager, like the Oracle Module wizards in OWB, that connects to a set of relational tables and imports the metadata into the repository. This seems to work off of ODBC rather than native Oracle connections, however when mappings run later on they can be configured to use OCI in the same way that OBIEE lets you. Suffice to say that connecting to non-Oracle sources is very easy, much easier than OWB or even ODI where you have to locate, and then fiddle around with specific JDBC drivers before you can work with these sources in a project.

Once you’ve brought your sources in it’s a similar process to bring in your target tables, again this is done via ODBC with the ability to switch to OCI during the mapping phase.

Flat files are also pretty straightforward to bring in, with an Excel-style import wizard that performs much the same function as the OWB one.

Of course it’s at this stage that you start to notice a few differences between Informatica and OWB. With OWB, the wizard that steps you through file importation is building an SQL*Loader control file, whereas of course the Informatica equivalent is setting up some metadata that drives an internal Informatica file reading process, there’s no SQL*Loader being used. Also, in OWB you often take these file definitions and turn them into external tables whereas there’s no real concept of this in Informatica. The mapping process is even more different, but I’ll get on to this in a moment.

Once you’ve imported in your sources and targets, you can review the list of tables in the Repository Manager.

Now that we’ve got some data in place, it’s time to do the first mapping. In this one, I’m taking data from three product source tables, joining it and loading it into a staging table. In OWB, the mapping would look like this:

The equivalent in Informatica, using the Informatica Designer application, would look like this:

A few points to note. Firstly, it’s actually very similar to OWB; each operator has input and output mappings, you drag and drop sources and targets onto the palette and then connect them using lines and transformation operators. On first glance, OWB has a richer set of transformations out of the box (there’s no equivalent of name and address matching, match-merge and so on) whilst Informatica has better out of the box support for alternative data sources and targets such as XML, CDC, HTTP and so on. Data sources in Informatica mappings are accessed through a “Source Qualifier” object that lets you filter the data before it goes into the mapping (removing the need for filter operators just after objects), and interestingly joins only seem to support two inputs whereas of course in OWB you can add several.

When I started thinking about joins it got me thinking about how, under the covers, Informatica is doing the data integration and loading. With OWB, the database does all the integration using SQL selects, inserts, joins, table functions, merge commands and so on, whereas in Informatica you’ve got a separate hub engine that does the work. Now in some ways that seems to offer advantages - you can just drag and drop any data sources and targets together and the Informatica Integration Service just gets on with working out the optimal, row-by-row way to get data from A to B. With OWB, you’ve got to be careful about the SQL that you’re getting OWB to generate which offers advantages and disadvantages - in the hands of an expert you can built some pretty fast, efficient integration, but it’s fairly easy to get OWB to generate some pretty hairy SQL if you’ve got multiple extract and load stages in a single mapping. I suspect with Informatica the environment is more controlled, more predictable but looses some of the capability to produce super-fast ETL routines that you could potentially get with OWB. I’ll have to see how things transpire once we start using the tool on more projects.

So once you’ve pulled all your mappings together it’s time to create a workflow, which in OWB terms is a process flow. Creating a workflow is pretty similar to performing the same task in OWB, you start off by adding tasks to the workflow process, connect them to the relevant data sources (this is where you can switch to an OCI connection) and string them all together.

Then it’s just a case of pressing the “Go” button and checking that the workflow has executed correctly. In OWB you’d use the Control Center Manager to do this, in Informatica you’d use the Workflow Monitor, in my case most of the processes have run but one of them, that uses a flat file, has failed.

Taking a look at the error log, I can see that any flat files obviously need to be in the /infa_shared/src_files directory.

After copying the file to the correct location the process then executed correctly.

So, that’s a simple set of mappings taken place. Of course there’s a lot more to Informatica than just this, but I think now we’re ready to use the tool in earnest to start customizing the BI Apps data warehouse. More on that in a couple of days/

Oh yeah, another one of those posts that will drag on over a couple of daze! This one comes out of some mail exchanges with one of our Oracle University trainers, Leta, who is revamping some of the class notes for Publisher. Leta has been working on the data template section for the EBS notes and found some holes in our doc! Shock, horror - yes, Leslie (our doc writer) is not infallible! Leslie assures me that the holes were left on purpose just to see if you folks out there are actually reading the documentation :0)
Leta was particularly looking at the XML data source we support in the data template. Its not badly written up in the documentation but there is some information missing and you have to connect the dots a bit. The saving grace is a full example later in the chapter - always useful for the light bulb moment when things click
Back to the point ... data templates can be used to access databases, multiple databases in fact - thats another post thou. Today its accessing XML datasources directly. Maybe you have a servlet that can serve up XML upon request or a static XML file that's dropped into a folder on a server periodically. Either way you can pull this data in via a data template. Yes, I know you can already do that by just calling it as a datasource in an Enterprise report. But what data templates bring to the table is the ability to merge your XML data with other XML data or with a sql based XML extract.
If you navigate to our documentation and look up the Data Templates section you'll find the somewhat incomplete section on XML data sources. To take the example given
<xml name="empxml" expressionPath=".//ROW[DEPTNO=$DEPTNO]"> <url method="GET" realm="" username="" password="">file:///d:/dttest/employee.xml</url> </xml>
This would be put under the dataQuery section alongside or instead of a sqlStatement if needed.
To explain the tags and attributes:

• name - name given to this datasource and referenced in the grouping structure.
• expressionPath - a means to link to an existing query. Assume the XML that will be generated from the file is:

    <?xml version="1.0" encoding="UTF-8" ?>
   <ROWSET>
    <ROW>
     <NAME>Donald OConnell</NAME> 
     <FIRST_NAME>Donald</FIRST_NAME>
     <LAST_NAME>OConnell</LAST_NAME>
     <SALARY>2600</SALARY>
     <ANNUAL_SALARY>31200</ANNUAL_SALARY>
     <FED_WITHHELD>8736</FED_WITHHELD>
     <JOB_TITLE>Shipping Clerk</JOB_TITLE>
     <DEPARTMENT_NAME>Shipping</DEPARTMENT_NAME>
     <MANAGER>Kevin Mourgos</MANAGER>
    </ROW>
    <ROW>
     <NAME>Douglas Grant</NAME>
     <FIRST_NAME>Douglas</FIRST_NAME>
     <LAST_NAME>Grant</LAST_NAME>
     <SALARY>2600</SALARY>
     <ANNUAL_SALARY>31200</ANNUAL_SALARY>
     <FED_WITHHELD>8736</FED_WITHHELD>
     <JOB_TITLE>Shipping Clerk</JOB_TITLE>
     <DEPARTMENT_NAME>Shipping</DEPARTMENT_NAME>
     <MANAGER>Kevin Mourgos</MANAGER> 
     </ROW>
   <ROWSET> 

  and I have a query

  select DEPTNO, DEPT_NAME
  from depts 

  I can use the expressionPath to link the two datasources

  .//ROW[DEPTNO=$DEPTNO] 

  is basically saying in SQL speak select * from empxml where .//ROW/DEPTNO = :DEPTNO -> from the sql query
• url method - how should we retrieve the data with a POST or GET call
• url realm - authentication name - this is seen when you log into an online resource sometimes. The popup will ask for your usr/pwd and will list the realm to be accessed.
• url username - does the URL Im calling need a username passed
• url password - does the URL Im calling need a password passed
• url value - the url to access the XML e.g.file:///d:/dttest/employee.xml gets an XML file from the directory 'dttest' on the disk. http://tim.oracle.com/getXML calls a URL on a web server to fetch the data

There is an example of how this all hangs together in the doc, to save some effort here it is with some comments:

<?xml version="1.0"
encoding="WINDOWS-1252" ?>
<dataTemplate name="Employee Listing" description="List of Employees" version="1.0">
 <parameters> - Defines a single parameter for the Department Number - with default of 20:
   <parameter name="p_DEPTNO" dataType="character" defaultValue="20"/>
 </parameters>
 <dataQuery>
  <sqlStatement name="Q1">
   <![CDATA[SELECT DEPTNO,DNAME,LOC from dept order by
deptno]]>
  </sqlStatement>
  <xml name="empxml" expressionPath=".//ROW[DEPTNO=$DEPTNO]">-
Defines name - and link to DEPTNO in Q1
   <url method="GET" realm="" username="" password="">
     file:///d:/dttest/employee.xml</url> -
Defines url for xml data
   </xml>
  </dataQuery>-
 <dataStructure> - The following section specifies the
XML hierarchy for the returning data:
 <group name="G_DEPT" source="Q1"
  <element name="DEPT_NUMBER" value="DEPTNO" />
  <element name="DEPT_NAME" value="DNAME"/>
- This creates a summary total at the department level based
- on the salaries at the employee level for each department:
  <element name="DEPTSAL" value="G_EMP.SALARY"
function="SUM()"/>
  <element name="LOCATION" value="LOC" />
  <group name="G_EMP" source="empxml">
   <element name="EMPLOYEE_NUMBER" value="EMPNO" />
   <element name="NAME" value="ENAME"/>
   <element name="JOB" value="JOB" />
   <element name="MANAGER" value="MGR"/>
   <element name= "HIREDATE" value="HIREDATE"/>
   <element name="SALARY" value="SAL"/>
  </group>
 </group>
 </dataStructure>
</dataTemplate>

So, if you have the need to merge XML with SQL - now you know how to do it!

Not much changes in Birmingham.

It seems like an eternity ago that I was at university there - but after only a couple of hours and a fine Balti in a BYOB restaurant, I felt like I'd never left.

Anyway, enough of my weekend! Let's get back to XSL-TEXT templates.

Excluding XSL-FO, there are 3 different types of XSLT script templates available in BI Publisher. These are currently not greatly documented in the BIP User Guide. However, the difference between them is the format of the output created by the XSLT script i.e.:

XSL-HTML	A XSLT script to transform an XML document into a HTML file
XSL-XML	A XSLT script to transform an XML document into another XML document
XSL-TEXT	A XSLT script to transform an XML document into a TEXT file

Note: The XSL element used to set the output type is:



Generally, it is a lot simpler to use an RTF or eText template to create the report layout - and I would ALWAYS recommend using an RTF or eText template where possible for this reason.

However, there comes a time when it may be easier to write XSL and XPATH directly rather than live with any shortcomings of the RTF or eText template type.

One such example of this is to add blank lines and additional formatting logic into eText templates.

In a previous blog on R12 Payments and eText templates, I built the following text file using an eText template:



Here is a XSL-TEXT template to build the same text file using the same R12 Payments XML data source:



A simple XSLT example... however, the point is that I can reference any XSL element, XPATH or extended SQL function directly. I have complete control over the layout of the text file being created.

Here are the source files:

Sample XML Data File
XSL-TEXT template

I built and tested this example using the BIP Enterprise Server (but this works equally well from Oracle E-Business Suite.)

To test this example using the BIP Server:

1) Create a new report and register the XML file as a data source:



2) Upload the XSL-TEXT layout template:



3) View the final transformed output:

I just noticed today that it’s more or less five years to the day since I started writing this blog. It’s been an interesting five years both personally and professionally for me; I gave my first conference presentation more or less five years ago, soon after I started chairing the UKOUG BI & Reporting Tools SIG, I worked for six years or so learning the ropes at a consultancy for whose support and encouragement I’ll always be grateful, and eventually I went on to spread my wings start a consultancy of my own along with Jon Mead. Of course, over the same five years the world of Oracle BI & DW has gone through an amazing amount of changes, with Oracle 10g and 11g released, the “Drake” version of Discoverer, the “Paris” release of OWB and of course Oracle then going on to acquire Siebel and Hyperion and adopt their technologies at the future platform for BI in the “Fusion Middleware” world.

Looking back at previous versions of the blog, my first actual website was a hand-coded affair that I put together to host user group presentations, code I’d written and put into the public domain and some general links and articles I’d put together.

I then experimented a bit with blogger.com until I settled down to use Radio Userland, a blogging platform developed by Dave Winer and related to the Manila platform that Oracle used up until recently to host their own blogs.

After running with Radio Userland for a while I switched to Moveable Type, which co-incidentally is the platform Oracle now use for their own staff blogs, possibly because it’s possible to host the underlying database on Oracle. An issue I had with Moveable Type at the time was that every time you published new page whole swathes of the site needed to be republished, and the bigger the site got the longer this took, occasionally failing when the hosted server we shared ran out of resources at a key moment. I don’t know if this is still how Moveable Type works now, I doubt it though as given the size of sites like blogs.oracle.com this’d hardly be practical now.

This was the initial Moveable Type look…

… which I then updated a year or so later to give it a more “BLAF” look and feel.

Towards the end of 2005 the problems I was facing with Moveable Type were getting a bit too much, partly because of the page republish issue, partly due to licensing changes and partly due to the cumbersome hand-coded templates I was still using, and so towards the start of 2006 I moved the site to Wordpress and CSS on which it still runs today. Around a year and a half ago we switched the domain from rittman.net to rittmanmead.com and over the past year I’ve been joined on the blog by fellow colleagues Jon Mead, Borkur Steingrimsson and Peter Scott.

So, I hope the site has been useful and entertaining for all of you over the past five years. It’s been an interesting and rewarding period for me (even when I nearly lost it all about two years ago) and thanks to everyone who’s provided feedback, added comments or who I’ve otherwise met directly or indirectly through the blog.

Quick tidbit today from Hok Min, the new RTF uber meister. It came out of a question with someone trying to use the 'split-by-page-break' command in a bit of a funky way. They had the following code:

<?if:CF_LPN_FLAG='Y'?>
 <?split-by-page-break: ?>
<?end if?>

Its a nice enhancement candidate but its not going to work. We are expecting the split-by-page-break command inside a loop not as part of a condition. Say, you're looping over departments (DEPT) and want a page break when the department changes. You'd have something like

<?for-each:DEPT?>
...
...
...
<?split-by-page-break:?>
<?end for-each?>

This would get out engine to insert a page break into the document before the occurrence of a new department. To get the original code above to work you physically need to insert a page break into your template. Kinda screws the WYSIWYG layout a bit but you get the desired functionality i.e. a conditional page break. For a sample RTF and XML click here

In a couple of recent articles, (here, here) I’ve given an overview of the Oracle BI Applications and stepped through the installation and configuration of Oracle BI Applications Fusion Edition (7.9.5). In this posting I’ll be walking through the initial load of the Oracle Business Analytics Warehouse, using Order Management data taken from Oracle E-Business Suite 11.5.10.
The first step in loading data into the BI Apps data warehouse is to log in to the DAC Console and create a new Source System Container to hold your DAC Repository objects. When you set up the DAC repository you import a number of source system containers into it, these contain details of the subject areas, mappings and so on that relate to a particular source system and release version. In my case, I imported the Universal container and the Oracle 11.5.10 container as this was my only data source, the new container I set up was therefore based on the 11.5.10 one.
Once you define your new container, you can take a look at the subject areas that it supports:

Each subject area shows the fact tables that it contains, whilst the Tables, Task Groups and Tasks folders below the subject areas set out the ETL tasks that are available for this data source.

The DAC Console also shows the various predefined Execution Plans that you can use to load data into the warehouse. The execution plans roughly correspond to one or more subject areas and can load the whole, or a part, of the source data into the warehouse.

In my case though I want to create my own, new, execution plan to bring in a selection of the subject areas. I create the new execution plan and set it to load all data into the warehouse, as opposed to incrementally extract and incrementally load, which is the usual way you’d refresh the warehouse.

Now I select the subject areas that I want to include in the plan. Not all of the subject areas (at least in my installation) seem to work properly, I suspect I need to bring in more Source System Containers for some of the areas to work properly. In my case I selected a number of subject areas around sales and orders using the subject area selection dialog.

Once your selection is confirmed you then need to generate a list of the relevant parameters, so that you can enter the connection details for the data warehouse, your E-Business Suite database and the filesystem location for the setup and reference files.

Now it’s time to take the list of subject areas and use it to generate the list of actual ETL tasks and task groups. This stage can take up to 10 or 20 minutes to complete for the selection I’ve made, on other runs its taken an hour or so when I’ve got several source systems (Siebel, Oracle 11.5.9, Peoplesoft etc) to generate mapping requests for.

And that’s it. Now your mappings have been requested and subject areas selected, all it needs now is for you to run the list of mappings and wait for it to complete - for my selection of mappings the ETL took around four hours to complete.

Once everything’s run, you’re almost at the point where you can start analyzing your data. Before you do that though, you firstly need to set a few things up at the server end so that you can see your data. The first involves setting the values for a few static variables in the BI Administrator tool, to tell the BI Server what user account and TNSNAMES entry to use when connecting to your data warehouse, like this:

and then you have to unzip the Web Catalog files, that contain your dashboards and reports, to the Web Catalog directory under /OracleBIData/web/catalog. Once you’ve done this, you can log in as a user who has access to, in this case, some order management reports and take a look at the data you’ve just loaded in.

So now that we’ve done an initial load and checked that the dashboard works, the next step (at least for me, as a developer) is to start thinking about how we can customize the BI Apps data warehouse and bring both new columns and rows into existing warehouse structures, and how we can bring entirely new staging tables, facts and dimensions into the warehouse data model. Before we can do this though, we need to know a bit more about Informatica, and that’s the subject of the next blog posting in this series.

Dear Consultants, Developers and all readers,

Here is an interesting thought and let me know if it chimes well with you.

I believe the following idea if implemented could give a significant advantage and boost your marketability.

Now, everybody has a resume or a CV, well ofcourse,this is the first step you execute when you step out of college.

All of us store our resume in a more or less standard Doc format, so how about having an online version of your resume?

Sounds interesting?

This online version would be just a single web page having your resume content. You can either put a summary of your resume or complete details if you like.

So what will a single page resume serve for?

When I suggest someone to start their own blog, I see lot of enthusiasm but no follow through. Few of the main reasons cited is “Don’t know what to write about” or simply “Have no time” or “Is it worth investing the time?”

Creating a single page blog is just the beginning and helps in breaking the inertia. Once you create your resume blog, the web-url becomes your signature URL and you can put this URL back on your resume or when you update your CV on job sites like Monster or Dice.

Appsbi.com started as a professional journal and it has become modestly huge than I orginally thought of. But it did not happen overnight. No big success happens overnight unless you have a Genie in a bottle. But interestingly all successes appeart to be an overnight success, isn’t it?

Start small, take the first step and do it now

Appsbi.com has tremendously helped me for my consulting career and is now helping as a marketing engine for InfoCaptor

Similarly, I believe that creating your own blog will help you in one way or the other, if not today then sometime in future.

The blog that you will create will act as one of the building block in your career.

Now let us attack the negatives or the holding back thoughts

“Don’t know what to write about”

Just make it your online professional journal. Start by putting up your resume online and slowly add more pages. Don’t feel pressurised to add more contents. Just add what comes naturally to you. Don’t worry about things like “is it worth posting”. Even if you have a small piece of code or found a new technique, just post it.

You should keep personal stuff out and stick with work related things. Better have a seperate personal blog.

“Have no time”

It would take 5 minutes to create a blog and add your resume to it. Then add a weekly recap of your work related things to it. This would take another 15 minutes. If weekly is too much then stick with bi-weekly or monthly. Is the initial 5 minutes and 15 minutes / week or /month asking too much?

“Is it worth investing the time?”

Do you call 15 minutes/month investment of time? You will eventually know if it is worth the time if you stick to it.

So where do you begin?

Create a blogger account http://www.blogger.com , pick appropriate blog name and add your first post right now within 5 minutes.

Blogger is a free service by Google.

Another free service similar to blogger is to create an account at http://wordpress.com/

Let me know if this is a good idea and if you really followed through send me your blog url by commenting to this post.

Just a quick one from me today as I am of to sunny Birmingham for the weekend.

I've blogged a lot recently about ETEXT Templates and how they are used in BI Publisher to generate text files for EDI and EFT:

ETEXT Part 1

ETEXT templates are a great way of creating simple fixed or delimited text files and the Oracle Payments team have delivered loads of predefined ETEXT templates for Payments processing.

However, they are not the only way to generate text files from BI Publisher!

It is also worth considering XSL-TEXT templates.

I've found that XSL-TEXT templates are really just XSLT scripts that transform the XML into text output only.

Note:
xsl:text is actually an element or command of XSLT to write literal text strings, entity references and #PCDATA to the output file.

If you are scratching your head over what XSLT is... then, a great reference to get you up and running quickly is the W3schools tutorial.

In my next post, I'll walk through some examples of XSL-TEXT templates - but first, I have to go and join the queues of traffic trying to leave London. No fun at 5pm on a Friday night!

The past year has been a very exciting and busy one for Rittman Mead with new projects starting in the UK, Europe, Canada and the United States. We’re now in a position where we’re looking to add another consultant to our team, someone who shares our passion for Oracle business intelligence and producing world-class solutions for our customers.

We’re looking for a senior consultant who has several years experience with Oracle Warehouse Builder, Oracle Discoverer and the “traditional” Oracle BI tools, and who is now involved in projects working with new technologies such as Oracle BI Suite Enterprise Edition, Oracle Data Integrator and the Oracle BI Applications. Like us, you typically spend your time doing consulting, development and training, either internally within your company or externally with another consultancy. You enjoy spreading the word about Oracle’s BI and DW tools and you’re likely to have presented at user group events in Europe or around the world. As much of our work is advising clients on a one-to-one basis, you need to have excellent written and spoken English communication skills and be able to work on your own as well as part of a team.

Around 50% of our work is UK-based with the rest being in Europe or in North America and the Middle East. Because of this it’s best if you actually like traveling and we typically plan for around 50% to 75% of our time to be away from home, with the remainder of your time either working locally or from your home. Our preference would be to take someone on who is already UK-based, or potentially based in the EU, however we are also thinking about expanding operations in the USA and would welcome applications from “over the pond”.

Our aim at Rittman Mead is to build the company that the best Oracle BI & DW consultants and trainers would want to work at, and as such our models are companies such as Fog Creek Software, Miracle, Pythian and Kimball University. We’ve all got young families so we try to make allowances for family commitments, however expect the hours to be long and your commitment to be big. In return, we offer a competitive salary, a share in the company, an opportunity to work with the best people in the industry, a rapid acceleration of your Oracle BI knowledge and sponsorship to present at events such as Oracle Open World, ODTUG, Collaborate and the UKOUG Conference.

If you’re interested or would like to discuss the opportunity further, drop me a line and send me your CV/resume and we can then discuss things further.

Up until recently, our office installation of the Oracle BI Applications has been based around the 7.9.4 release, and as 7.9.5 is now out and comes with support for Peoplesoft HR 8.9 and 9.0, Siebel CRM 8.1 and Informatica 8.1.1, I thought I’d give this new release a try. Our existing setup was also running on Oracle Linux, but as we effectively need our Oracle BI environments to be running on Windows so that we can run them in a single VM on a Mac, I thought I’d try installing all the various products on Windows this time.

The virtual machine I set up to hold all the software was built using VMWare Fusion 1.1.3 and I created the VM using Windows XP Service Pack 2. As the iMac I’m hosting the VM on has got 4GB of RAM I allocated 2GB to the virtual machine, altered the name of the VM host from the strange name VMWare gives it to “biappsvm”, installed the Microsoft Loopback Adapter, set the virtual memory settings to operating system managed and then starting installing the software.

Before installing anything else, I first installed the Java JDK 1.5, making sure I installed it into “C:\Java\jdk1.5.0_15? rather than the default “C:\Program Files\Java\jdk1.5.0_15? which later on breaks the DAC installation, which tries to call Java from a batch file but would otherwise fail because of spaces in the application path (first top tip there). Once I installed Java I then installed an Oracle database (10gR2, in case there were any surprises with 11g compatibility) followed by Oracle BI Suite Enterprise Edition 10.1.3.3.3, the latest release.

At this point I had an Oracle database and the base Oracle BI software installed. Now normally you’d have a copy of Oracle E-Business Suite (11.5.8, 11.5.9, 11.5.10 or R12) handy to use as your data source, but in my case I had a subset of the 11.5.10 vision database with enough data to support Order Management analytics which I then imported into my Oracle database, using the transportable tablespace feature, so that I had some source data to play around with. After getting the data set up, I was ready to install the Oracle BI Applications.

Installing the Oracle BI Apps 7.9.5 is fairly straightforward, like all the ex-Siebel Analytics products, with the software being installed (in a simple, one-box architecture) into the same directories as your OBIEE software, like this:

Once you select the install directories you can then pick the products you want to install.

Now one thing I did find a bit confusing was matching all these product options to (a) this list of nine products that 7.9.5 appears to comprise of as listed on OTN and (b) the list of subject areas that I then could select from when working with the BI Apps admin tool (the DAC, or “Data Warehouse Administration” tool); however I then came across this spreadsheet that’s also hosted on OTN that matches the product options you get with the installer, the new packaging that’s being used for 7.9.5 (which is now officially called “Oracle BI Applications Fusion Edition (7.9.5)) which seems to simplify the product bundles, the subject areas in the DAC and the corresponding ETL execution plans which at least gives you a way to work out precisely what you’ve licensed.

Once the BI Applications are installed, you need to create a database account to hold the DAC metadata and the Informatica repository, and another one to hold the data warehouse that the BI Applications create and then populate. It seems to me that at least as this stage, you just create the data warehouse account using the normal connect, resource and create view priviledges, any tuning in terms of materialized views and so on is something you do once the initial set of data is loaded (although the load routine does seem to know about bitmap indexes, for example). Once all of the relevant database accounts are created you can install the next stage of the software, the Informatica ETL server.

Of course Informatica being part of the BI Applications is richly ironic as Oracle have spent the last five years or so rubbishing Informatica, and all row/hub-based ETL tools in favour of their own ELT-based data integration tools, OWB and now ODI. For us though being introduced to Informatica is quite an interesting prospect as (a) most of the banks and financial institutions in London seem to use Informatica for their integration, hence it’s useful for your CV and to broaden our skillset, and (b) we’re just curious what the competition have been getting up to. Informatica itself comes in Client and Server components, the server element gets installed first which sets up a “domain” first, a sort of “super-server” that co-ordinates, like the Dalek Supreme, all the other Informatica servers:

Then you create the initial repository - one thing to note here is that I initially followed the defaults and created a repository called “PowerCenter”, however the BI Apps installation guide assumes that you name it “Oracle_BI_DW_Base” which is then the repository name it connects to from the DAC and is the name of the backed-up repository, containing all the BI Apps ETL mappings that you restore later on. When you create this repository then, name it “Oracle_BI_DW_Base” rather than the default that the installer uses and your life will be a lot easier later on.

After you’ve created the repository you then create the Integration Service (I think this roughly equates to the Control Center in OWB terms) and then apply a service pack to the whole installation. One tip I’ve got here is to test at all times that the Informatica service comes up (and stays up) after each stage of the installation - I found that in one instance, the service came up and stayed up before the service pack was applied, but then always failed after the service pack was installed and I tried to access the web interface. I reinstalled all of the Informatica software again and next time it worked, but this is something I’d check actually works after you do the install before you move on to the next stage of the install and then find doesn’t work. I also found that the Informatica service also failed to start if you had the service on automatic start on Windows and the database was also starting up, my solution here was to set the Informatica service to manual start and start it after everthing else had completed its initialization.

Something that’s new in the 8.x release of Informatica is a web-based console that you can use for administering the server and the repository. Using this console, you then pick up the backed-up Oracle_BI_DW_Base repository and restore it over the empty one you created a minute ago, this then brings in all of the source-specific and file-based reference data mappings that the DAC, which you’ll configure in a moment, will call upon to load the data warehouse.

Once you know Informatica is up and running stably, the next thing is to configure the DAC tool. Bizarrely, you have to go to the Hibernate (a set of object-persistence packages) website and download the Hibernate tools and then copy a set of JAR files to the DAC installation, once you’ve done this though (and made sure you installed the JDK to a directory with no spaces in the name) you should be able to start the DAC client. On the first run it prompts you to create a connection to the DAC scheme you created earlier on in the Oracle database, then it offers to create the (empty) DAC metadata tables in the schema - these can co-exist with the Informatica repository tables in the same database schema.

Using the DAC tool, the first task you need to complete is to import the required source application list into the repository. There’s an application for each source that you can (automatically) bring into the BI Apps data warehouse, in my case I selected the Universal (required for all the sources, plus custom sources) and the Oracle 11.5.10 applications, pressed OK and then waited an hour or so whilst it brought in all the application execution routines.

Finally you need to set up your data connections in both the DAC repository and the Informatica Repository (making sure you name them identically), and finally you have to tell the DAC client which DAC server to connect to, and which Informatica servers will be running it’s ETL routines.

Assuming everything is set up and working OK (I’ve only listed out the highlights of the install steps here, the rest are detailed in the Installation and Configuration Guide) you should then be in a position where you can start your initial load into the data warehouse. More on that in a few days.

Why Build a Data Warehouse?
For decades, companies have been spending millions of $$$’s on systems to streamline and automate the execution of the business plan. Over time, these systems have become more and more integrated within a companies operations and between business partners. However, due to the granularity of the data stored in […]

Read more >>http://www.infopurple.com/why-build-a-data-warehouse

Business Intelligence Overview

True value of Business Inteligence comes from recognizing and harnessing the power of technology to help change the way you do business. After all – unless there are real decisions made with the information provided by a data warehouse, all you have is a data warehouse, not a decision support application.

By utilizing information and insight gained from your data warehouse and analysis tools, The goal should be to help you keep track of the KPI (key performance indicators) in your business, and help you put into action changes that will improve your business.

The Business Intelligence Life Cycle http://www.infopurple.com/the-business-intelligence-life-cycle

Now the dust has settled a bit and I have gotten some things working that I always wanted, its time to talk about the new look. Better, yes? We have moved to a new blogging platform.

OK, so the look and feel is not so important, its the content right? Same old content but with some nice bells and whistles. Check out the bar on the left - if you're reading this via email, apparently about 600 of you, click here to see what Im talking about

1. You get to see pics of where I live and work - not quite the view from my office window but not far away


2. Much better search capabilities, 'Search All Blogs' will invoke the Oracle Enterprise search across all blogs. Yesterday, searching for 'bi' or 'xml publisher' did not find links to this blog .... ahem! Today we're top of the list.
   The other search box is for Google to scour over the content here.


3. Useful links - I'll be expanding this for sure. Lots of comments recently about there not being a single source that pulls all the BIP content together!


4. Recent comments - not sure how useful this is and it needs some work on the formatting ...


5. Recent Posts - nuff said!


6. Top Tags - finally we have tagging! The top tags are a little skewed right now as I have been trawling back through the 300 odd posts tagging and re-categorizing them. Im about a third of the way through - bear with me. Should be useful thou, if your looking for something in particular.


7. Categories - the new platform supports multiple categories and hierarchical ones at that. I have tried to break things down without going to crazy - the numbers next to the categories are again skewed as I work through articles. Have put a shout out to uber blogger Steven for slogging through the reformatting of the hierachy to make it readable!


8. Publisher Blogs - no blogroll right now, this is a list of the latest blog articles about 'BI Publisher' or 'XML Publisher'. Google provides a neat RSS feed of the blog search I set up and WebRSS allows me to be lazee and formats into a list for me. I have not worked out how to exclude yours truly from the list yet! Want to subscribe to the RSS feed from the Google search? hit the RSS icon. Get your article on the blog!


9. Subscriptions - RSS is there of course and you can still get the daily email of the latest article via Feedburner.

There it is, standard stuff for most of you bloggers out there but we are finally catching up. If you have ideas for more 'stuff' let me know.

OK, we all love a clever piece of code to do something a little bit tricky, but has everyone lost sight of the benefits of simplicity? My case study for the day, a thread on the Oracle forums in which various all_objects, user_objects, connect by, MODEL, xmltable, and pipelined function techniques are suggested for generating a [...]

One cool but confusing feature of the underlying standard to our RTF templates that is XSL-FO, is that you can create sub-templates and import them into your main template. This allows you to 'objectize' (yes another new word for the blog) your templates, do you have some common feature of all your templates? Rather than replicate that into every template, put it in one place and import it into the main templates. I have talked about that in prior articles. What's even cooler is that within your sub-template you can have multiple pieces of re-usable 'code'. Think of having a sub template with a header and footer section that can be pulled in. Now the slightly confusing bit - each of these pieces are themselves called 'templates'. You can find yourselves having fantastic conversations about your main template calling the 'header' template from the sub-template, you don't? Well I do, maybe they are with myself but I have them! Those of you that have gotten into the sub templating world will know the command to import a template:

<?import:[template URI]?>

then you can call a specific template within the main template:

<?call:Header?>

What you may not know is that you can call the templates conditionally. Before we get to that thou, a note. You can not conditionally load the sub-template file i.e.

<?if:x=1?>
 <?import:[template URI]?>
<end if?>

will not work. The import calls can not be executed at run time they are like pre-processor commands, like 'h' files in C. With that said you can then conditionally call a template within that imported sub-template. There has been a question on the forum from Todd and I have had a similar conversation with Pat and the gang at Emulex recently. Emulex have a good use case for conditional template calling. They have developed an RTF Purchase Order template that generates terms and conditions on the back of each page. They are now moving out of just using the PO for their domestic market and need it to work in an international arena. Translations aside, they just need a different set of terms and conditions for the international market version of the PO. They are already importing a T&Cs template - all they need to do is create a domestic and international T&C template within the sub-template. Their XML has a tag in it identifying what type of PO is being generated, so they can have something like:

<?import:file:///d:/temp/tctemplates.rtf?>
<?choose:?>
  <?when: .//TYPE=1?>
   <?call:TC1?>
  <?end when?>
  <?when: .//TYPE=2?>
   <?call:TC2?>
  <?end when?>
  <?otherwise:?>
   <?call:TC3?>
  <?end otherwise?>
<?end choose?>

This would handle 3 possible T&C templates in their sub-template.

Todd had a good comment, why are the 'call' statements not closed with an 'end call'. I did not have a good answer. Suffice to say, we handle it for you - a good if inconsistent, feature. If you want some example code, get it here.