All good things must come to an end. It has been a long run with plenty of ups and downs. Getting Discoverer rolled out was one of my first tasks as I embarked upon my +Oracle E-Business Suite journey. Alas, we are saying our final goodbyes to Discoverer. This however opens new doors to address our reporting solutions. Over the past couple of years, we have installed Hyperion and OBIEE. These will address some of our needs, but there will still be the requirement for those day to day reports currently run from Discoverer.
This is why we are implementing BI Publisher as the new tool. Why did we decide to go this route? Anyone familiar with EBS, in this case 11i, knows that the Standard Reports for the most part look like they were designed to printed on a dot matrix printer and delivered by interoffice courier from the depths of the IT Department. This why Discoverer was so popular; we could create reports and Finance and HR could export them to excel and “manipulate” them. Now back to why BI Publisher.
Requirements
- Ability to export to Excel – Just like Discoverer
- Recognize EBS organizational security – HR data in particular
- Authenticate against EBS native login – No LDAP or any semblance of SSO
- Familiar UI – Minimize Change Managment
We looked at several tools before making the decision. While all had their benefits, BI Publisher met the requirements best. An additional benefit of BI Publisher, is that we can now schedule and deliver these reports on a predetermined basis. The biggest thing we give up with this approach is that our Functional Users lose the ability to create their own reports, and depending upon certain implementation decisions, they will be limited to the reporting instance refresh cycle. More on that later. If there is a need to get actual ‘real time’ data, we can run the report(s) using
+Oracle SQL Developer and provide upon request.
Now that we have selected BI Publisher, we need to do a few things. First we need to have our Functional Users identify the Discoverer and Standard Oracle reports that are critical to their LOB. Fortunately, about 90% of our Discoverer reports are custom SQL, so we don’t have to reverse engineer many of those delivered reports. Next we identify the Standard Reports that have XML templates available which the users might not have been aware of. Having all this in hand we can now move on to the next part of the process.
Setting up the Environment
Reporting against the transactional database can cause performance issues, so we will create a reporting database instance and create a DBLINK between it and the Production database. This will the database only and it will be refreshed on an agreed upon interval. Active Data Guard appears to be a good solution here. I encourage any contributions to this discussion. From the application perspective, a separate Concurrent Manager will be created to handle the BI Publisher report requests. This will prevent long running reports from queuing up in the existing Concurrent Managers and possibly causing performance issues with other processes. The Output Post Processor (OPP) will apply the XML template and produce the final output. Finally, you will want to install Oracle BI Publisher Desktop on your computer. This will be required to create your template(s). The application can be downloaded
here.
Creating the XML Template
Once this reporting instance and concurrent manager is created, we can begin to migrate the custom SQL from Discoverer to BI Publisher. The easiest way to do this is launch Discoverer Administrator and get the copy the SQL from the Business Area. You will obviously need this later. Things to be aware of:
- If your Discoverer reports are calling function from within Discoverer, you will need to make note of them as the SQL will need to be edited to include the function calls.
- Conditions will need to converted to parameters in or added the WHERE clause
- In my opinion, sorting is optional
In a nutshell, any Discoverer features used need to be documented and applied appropriately whether it be editing the SQL or when you configure the concurrent program. Lastly, since the Concurrent Program will be run from the Production database, the underlying SQL will need to be modified with the DBLINK. Obviously you’ll be looking at code similar to this: SELECT * FROM hr.per_all_people_f@reportingDB; Now that we’ve completed editing the SQL, the goal is to register it in EBS as a concurrent program, run the concurrent request and select the output type as XML. This will produce the XML template needed to create the BI Publisher report.
Create Your Report
Install Oracle BI Publisher Desktop you downloaded earlier. This adds a toolbar item to your Ms Office applications which is how we create the report templates. In the screenshot below, we see this in Word:
At this point we have everything necessary to create the report in BI Publisher. Here is a tutorial on:
Creating Reports in Oracle E-Business Suite Using XML Publisher to get you started.
Like this:
Like Loading...
Related
