BI Publisher - Webservices update to10g

BI Publisher 10g provides an update to its webservices from previous releases with the patch 9546699. All you have to do is (with OC4J and BI Publisher 10g running) to open the following URL:

http://mybipserver:9704/xmlpserver/services/PublicReportService_v11?wsdl

Where: mybipserver is the machine's name where OC4J is installed and where BI Publisher has been deployed. Save this file locally.

Once you get the WSDL file you can immediately test the services using SOAP UI (a webservices tester).

Start loading BI Publisher's WSDL into SoapUI and create a new project:

So for example, we can find out which Security mode is being used in a BIP installation. Simply invoke the service "getSecurityModel". This service does not require any parameter, therefore just click on the green button "play" .

This returns the security model employed in BI Publisher's environment: "BI_SERVER", which means the security model used is based and controlled by BIEE Server.

The next example will employ BI Publisher's web services which require parameter values: "login" and "getFolderContentsInSession". Invoke "login" and enter "Administrator" as user and password:

This returns the session string, known as BIP's session token:

Next, invoke the "getFolderContentsInSession" service and enter the string just obtained in previous step plus the folder path to find out its content: "/HR Manager".

Finally, click on Run (the green arrow button on the upper left hand side of the window) to obtain a list for reports and folders:

With this you now get a flavor of all the things you can do using BI Publisher's webservices. Of course you can start developing your web application using JDeveloper and BIP WSDL, but that will be the topic for new post soon.

Note: Based on BI Publisher's PM comments I have updated our post's original title.

BI Publisher 10.1.3.4.1 (build 1145)- latest patch - 9546699 (April 1st 2010)

More than a week ago Oracle BI Publisher PM team released a new patch 9546699 (on March 31st, but published in Metalink April 1st), which brings many bug fixes and product enhancements.

There is a long list of updates you can read in the readme file that comes in the patch. But for those of you interested in getting this patch applied in your environment I have compiled a step by step screenshot sequence (for Windows environment) which I hope can help you in this process. Here is how it goes:

I) Upgrade of BI Publisher Server

1. First, take a backup copy of $ORACLE_HOME/oc4j_bi/j2ee/home/applications/xmlpserver/xmlpserver/WEB-INF/xmlp-server-config.xml , where as usual $ORACLE_HOME refers to the location of your base directory for BI Publisher server installation.

2. Second, take a back up of BI Publisher's repository. Default location usually is /xmlp/XMLP:

3. In case BI Publisher's Scheduling feature has been enabled and configured ==> Also take a back up of such schema in the database used. Although as the upgrade will be performed on the application server side then there is no really need to perform this step, it is just as precaution.

4. You can now use Enterprise Manager for the rest of the steps : uninstall of the current version and the installation of the new version. Notice the port number is 9704 since the original installation of BI Publisher was part of BIEE installation and that is the default port number. Thus, invoke Enterprise Manager as follows : http://server_name:9704/em

Normally, the password is "oc4jadmin" but it could be something different. This password is defined during the first install of BI Publisher, so you need to get this value from whoever performed the first installation. Otherwise, if the password is not known then use google to obtain a process on how to reset the OC4J administrator's password. Here is a link from Mike Lehmann's : Reset OC4J admin password

4.1 Shut down BI Publisher.
4.2 Undeploy the BI Publisher application ("xmlpserver") from your J2EE application server. Here a screenshot from Enterprise Manager for OC4J:
4.3 Deploy the 10.1.3.4.1 (patch 9546699 ) xmlpserver.ear or xmlpserver.war to your application server. If you are using OC4J then here is the sequence of screenshots:

- Click on "Deploy"
- Select the EAR or WAR file to deploy:
-Enter the deploy application attributes:

In the third step simply accept defaults and Apply and Deploy. You get finally this:


4.4 Stop BI Publisher application (repeat as in step 4.1). This step is required as you will restore the backup files taken in step 1. and 2.

5. Copy the saved backup copy of the xmlp-server-config.xml file from step 1 to the newly created BI Publisher: $ORACLE_HOME/oc4j_bi/j2ee/home/applications/xmlpserver/xmlpserver/WEB-INF/ directory


6. Copy the repository (the complete XMLP directory and subdirectories from the back up folder, see step 2.) into $ORACLE_HOME/xmlp/


7. If your environment is 10.1.3.3.2 (03-April-2009, Patch 8398277) or earlier then perform the following two steps, Otherwise continue with step 8.

• Copy this file: ssodefaults.xml and place it in the following directory: $ORACLE_HOME/xmlp/XMLP/Admin/Security. Default values for properties can be updated depending on your configuration if SSO is implemented.

• Copy database-config.xml to the following directory: \XMLP\Admin\Scheduler. This file stores information about the database which will be used to store BI Publisher Scheduler's metadata tables.

8. Restart xmlpserver application or Application Server

9. Finally you can test your new patch by going to: http://server_name:9704/xmlpserver

Notice, down on the right hand side the BI Publisher's version 10.1.3.4.1 (build 1145). To see this version your application must be in DEBUG mode though. Alternatively, to see the build number from the login page you can view the page source (IE View --> Source) and Notepad will come up. You will see this line:

<meta name="Generator" content="Oracle BI Publisher 10.1.3.4.1 (build# 1145 on Fri Apr 09 16:26:02 GMT-05:00 2010)"/>

This indicates that the patch has been applied and now BI Publisher's version is 10.1.3.4.1 build 1145.

II) Upgrade of BI Publisher Desktop

Finally, there is one more upgrade to make. The BI Publisher's desktop should also be upgraded with the new xdocore.zip which, is shipped with the new patch. To apply it follow the next steps:

1. Using the just deployed new patch, get a copy of this file:

$ORACLE_HOME/oc4j_bi/j2ee/home/applications/xmlpserver/xmlpserver/WEB-INF/lib/xdocore.jar

2. Take a backup of the the following existing xdocore.jar files:

a)C:\Program Files\Oracle\BI Publisher\BI Publisher Desktop\TemplateViewer\lib\xdocore.jar
b)C:\Program Files\Oracle\BI Publisher\BI Publisher Desktop\Template Builder for Word\jlib\xdocore.jar

3. Paste the xdocore.jar file copied in step 1., into the following directories:

a)C:\Program Files\Oracle\BI Publisher\BI Publisher Desktop\TemplateViewer\lib\
b)C:\Program Files\Oracle\BI Publisher\BI Publisher Desktop\Template Builder for Word\jlib\

After this, BI Publisher Desktop has been upgraded and is in sync with the patch 9546699 just applied.

OBEE's... the official sub shop of Oracle BI?

There is a sandwich shop down the road from our headquarters that is frequented by the staff at BICG. Up until today, I never realized why the name of their establishment looked so familiar. They simply forgot to put an "I" in their name...

Identifying Business Processes:

Ralph Kimball in his newest book, “The Kimball Group Reader”, is a compilation of the articles that the Kimball Group has published since the original article in 1995. They have spent over 15 years working and gaining experience with Business Intelligence / Data Warehouse Courses. I selected one of his articles in his book to summarize because it is the heart of gathering requirements for a Business Intelligence / Data Warehouse Project.

The requirements for many Business Intelligence / Data Warehousing Projects are gathered by interviewing the business user and looking at his existing reports or his transactional system. By looking at the existing reports or the current transactional system it leads many times to inadequate or incomplete business requirements. Many of the existing reports were developed and built by someone usually using at tool such as Microsoft Excel to report on the existing transactional system. Also in many cases the transactional system may be used by different departments that use the same system to run different but similar business processes.

The article says that the Dimensional Model for the Business Intelligence / Data Warehouse Project should be built on the business process and not on the different usage of the transactional system or the reports built from the transactional system. One of Kimball’s core concepts is to identify the business process for the dimensional data model. The article says that a business process is “an event or activity that generates or collects metrics. These metrics are performance measures for the organization. Business Analytics inevitably want to scrutinize and evaluate these metrics by a seemingly limitless combination of filters and constraints. As dimensional modelers, it is our job to present these metrics in an easy-to-understand structure that responds quickly to unpredictable inquiries.”

The article points our common characteristics and patterns when identifying the business process for dimensional modeling. They are:

1.) Business processes are usually supported by an operational system.
2.) Business processes generate or collect unique measurements with unique granularity and dimensionality used to gauge organizational performance. Sometimes the metrics are a direct result from the business process. Other times, the measures are derivations.
3.) Business processes are frequently expressed as action verbs with their associated dimensions as nouns describing who, what, where, when, why, and how associated with the process.
4.) Business processes are usually triggered by an input and result in an output that needs to be monitored.
5.) Analysts sometimes want to drill across business processes, looking at the results of one process along the results of another. Drilling across is certainly viable if the dimensions to both processes are conformed.


Some of the major conclusions of the article are:

1.) Determining the core business processes is critical to establishing your overall framework of the dimensional models.
2.) The easiest way to determine these processes is by listing to the business users
3.) A dashboard is not a business process; it presents the results of numerous individual business processes.
4.) Technical considerations are very important for a Business Intelligence / Data Warehousing Project, but if the proper business requirements are not captured and modeled the success rate for the project is very low.

BI Publisher - Layout Builder preview in 10g

If you are a BI Publisher user of release 10.1.3.4.1 (Patch 9229122) then you would like to know that this release includes a preview of the Layout Builder (WSYWYG Editor) part of the official release 11g.
Only thing you would have to do is set one particular parameter in xmlp-server-config.xml located in the repository directory xmlp\XMLP\Admin\Configuration as follows:
After restarting OC4J this change will take effect. When you create or modify a report then you will notice that under the "Layout" section there is a new button "Layout Builder" available.

The template prepared by the Layout Builder will have extension .xpt and will be an XML file which contents will only be rendered properly in the Layout Builder tool. In addition to this, the tool will not read any other file type than .xpt.

I guess that if you want to edit the .xpt file outside the Layout Builder you could use any XML text editor and assign a schema or DTD to it, but that is the topic for another post in this blog :).

At this point I would like to make a disclaimer: the parameter is an undocumented feature in 10.1.3.4.1 release and thus is not supported by Oracle in this release.

Which Tool Should Be Used for Reporting?

My clients often wonder which Oracle tool they should use for reporting, building graphs, and generating dashboards. It’s a great question because, as anyone in this realm knows, there are many different options. This blog will touch on the three reporting tools I’m familiar with: Financial Reporting Studio (FRS), Web Analysis (WA), and Oracle Business Intelligence Answers (OBI).

One caveat here is that the bulk of my experience comes from the Oracle Hyperion side, so I have much more familiarity with FRS and WA, and definitely less so with OBI. So, this blog will be written from that perspective.

Below, I’ll give a synopsis of what each tool does, based on my experience, followed by my opinion of when each should be used.

Financial Reporting Studio
FRS is a great tool for producing regular reports, like a monthly reporting package. It allows you to create production level reports with a multitude of formatting options. FRS reports can be viewed in PDF or HTML format, from a client component or over the web. Reports can be gathered together in Books and batch scheduled to run at the frequency of your choosing, and then saved to a particular location or emailed out to a list of users.

FRS is not a tool for producing dashboards, is limited in its chart and graph functionality, and would not generally be used for ad-hoc reporting.

Web Analysis
WA comes from the Hyperion suite of products as the tool for building dashboards. In my experience, it’s most often used to create quick snapshots of data for management or executive level users. WA allows you to create multi-view looks at your critical business metrics, either in graphical or grid format. For example, a dashboard might include a line graph of sales by region in one quadrant, a bar chart of sales by VP in another quadrant, a pie chart of expenses by category, and a grid showing spending by department. WA includes traffic lighting as a feature, allowing you to highlight or color significant variances of data.

WA would not be used for production reporting.

Oracle BI Answers
OBI Answers is a tool for building both reports and dashboards from a variety of sources including relational and multi-dimensional databases. You can build similar dashboards to what WA offers and publish reports similar to what FRS offers. The entire OBI suite has pre-built modules by industry that allow for easier implementation depending on the particular business case.

While OBI Answers can use Oracle Essbase as a data source, there are some issues in doing so that prohibit the use when a particular hierarchy exists. This should be addressed in an upcoming release, but at this point, the OBI link to the Oracle Hyperion suite of products is very limited.

Which Tool?
So, which reporting tool should be used when? With the current releases and functionality available, here is how I would use them:

FRS – Use for regular production reporting such as income statements, operating expenses, headcount, and any other meaningful financial metrics from an Oracle Hyperion data source such as Essbase, Planning, or Financial Management. I generally don’t create charts and graphs using FRS because the options and functionality are fairly limited. But, if you have fairly straightforward and simple chart requirements, then FRS should work fine for you.

WA – I would use Web Analysis for producing grid and chart dashboards from an Oracle Hyperion data source. WA does a good job of incorporating “bells and whistles” that make a dashboard “pop”, providing important metrics quickly.

OBI Answers – OBI is a great tool to use to quickly build reports and dashboards from a data warehouse. In my opinion, this is the easiest tool to learn and use of the three. As I mentioned above, I don’t think it’s currently the right tool to use with Oracle Hyperion data sources, but that very well could change in the near future.

Going Forward
In future releases, I think that OBI will become the tool of choice for reporting and dashboarding, even for Oracle Hyperion data sources. Oracle is very good at creating synergies between their product lines, and while each individual application generally has their own set of tools, eventually, they converge to similar toolset technologies. I think as OBI gets more integrated with the Oracle Hyperion suite of products, it will become the tool of choice for reporting. In my opinion, it is easier to both learn and use compared to both Financial Reporting Studio and Web Analysis.

Vintages - Time x Time

One of the more unique conceptual challenges when designing an Essbase or Planning model can revolve around handling multiple Time-basis dimensions. Many model requirements need granularity beyond the standard observation date that is typically defined as being the Time Period in a model. Consider the following:
A customer has a portfolio of investments, each having several characteristics related to some measurement of time.

• The origination date of the investment
• The maturity date of the investment
• An expected length of the investment, based from which the origination or maturation date can be interpreted
• An activity date

A single Time dimension (or two time dimensions that split portions of time apart, i.e. separating Years from Months and Quarters) can not accurately capture metrics in the previous example like the performance over time of all investments that originated in a given period, or how much return at a certain age of an investment can be expected.

Multiple Time Dimensions
Assuming the standard Essbase or Planning time dimensions represent the observation date, additional time-style dimensions can be created to provide more granularity. In the previous example, a dimension indicating the age of an investment can be utilized to analyse performance of investments based on how long ago they were originated. Likewise, identification of the starting or ending dates of investments allows analysis based on similar origination or maturation dates.

How much is too much granularity?
That all depends on the analysis requirements for a model. Adding more detail to a model will increase its size and complexity, but will provide a more detailed picture of time's effect on activity. All decisions regarding expanding a model require careful considerations of the benefits of additional complexity.



Other Time Time appears multiple Times (Time beyond core modelling requirements)

• Scenario revisions

First pass, second pass, etc: These are essentially observations or snapshots of a process at a point in time. Depending on how many of these snapshots are required, it may be advantageous to have a separate dimension identifying when the snapshot was presented. Consider the benefits carefully of disposing of forecast revisions. Often, one key long term goal to improve forecast reliability is the ability to track variances between revisions over time.