Data templates in BIP

There is so much to talk about but what I haven’t discuss right now is extra functionality in Data Templates. Features that you can use to allow more complexity into your templates.

One of the advantages of data templates is the use of Report Triggers. These triggers will handle a specific sequence. So, the sequence of execution seems to be:

  • Before Report Trigger
  • Data Query (SQL statement)
  • After Report Trigger
  • Output Post Processor

Another thing, maybe not so important for all of you, is that you specify extra options into your data templates to debug better your report or to get more information about a report. These elements are called properties of a data template.

A data template consists of following elements:


&ltdataTemplate name="&ltname&gt" description="&ltdesc&gt"
   dataSourceRef="&ltdatasource&gt" Version="1.0"&gt
&ltproperties&gt
&ltproperty name="showControls" value="false"/&gt
&ltproperty name="online" value="true"/&gt
&ltproperty name="parameterColumns" value="3"/&gt
&ltproperty name="openLinkInNewWindow" value="true"/&gt
&lt/properties&gt
&ltparameters&gt
&lt/parameters&gt
&ltdataQuery&gt
&ltsqlStatement name="Q1"&gt
&lt![CDATA[select ID from dual]]&gt
&lt/sqlStatement&gt
&lt/dataQuery&gt
&ltdataStructure&gt
&ltgroup name="ROW" source="Q1"&gt
&ltelement name="ID" value="ID"/&gt
&lt/group&gt
&lt/dataStructure&gt
&lt/dataTemplate&gt

The default properties are present in bold. You find a list below that you can use to extend your report properties.

include_parameters – indicates whether to include parameters in the output.
Valid values:

  • True (default)
  • False

include_null_Element – indicates whether to remove or keep the null elements in the output.
Valid values:

  • True (default)
  • False

xml_tag_case – allows you to set the case for the output XML element names.
Valid values are:

  • Upper (default)
  • Lower
  • As_are (The case will follow the definition in the dataStructure section)

db_fetch_size – sets the number of rows fetched at a time through the JDBC connection. The default value is 500.

scalable_mode – sets the data engine to execute in scalable mode. This is required when processing a large volume of data.
Valid values:

  • On
  • Off (default)

include_rowsettag – allows you to include or exclude the Rowset Tag from the output.
Valid values:

  • True (default)
  • False

debug_mode – turns debug mode on or off.
Valid values:

  • On
  • Off (default)

Example of Use:
&ltproperty name=” include_parameters ” value=” false “/&gt

Oracle BI – Dashboard Prompts & BI Publisher

Situation:
One of the common questions that generally come up with regard to BI EE and BI Publisher is ‘how does one pass parameters to BI Publisher reports in Dashboards?’. One of the solution is to create a BI Publisher Report using HR-scheme as data source. Define a parameter for the BI Publisher report and then match this parameter name while assigning the presentation variable for the dashboard prompt.

Solution:
This method is generally the most used since most of the BI Publisher reports would have database as the data source.

We create a simple BI Publisher report with data coming in from HR-schema of the database.

Report-details:

  • Default Data Source: HR
  • Parameters per line: 1
  • Show Controls: unchecked

Overview BI Publisher Report:


First we will create a new data model, called DS_HR. SQL query will be:

Now add a parameter to the report. In our case, we will have ‘pDept’ as the parameter name.


Create a new RTF-template with following fields:


At least add a template to our report. Upload the template you’ve created before and create a new template name ‘TemplateHR’.


Save Report and test your BI Publisher report to be sure everything is OK.
Then go to the BI Answers and create a dashboard prompt on ‘department name’.

Note: The name of the presentation variable must exactly match that of BI Publisher parameter name.

We set default value to ‘IT’ and assign presentation variable to ‘pDept’.


Include this dashboard prompt and the BI Publisher report into the dashboards.


Final result in Oracle BI Dashboard:


When you checked ‘show controls’ in the BI Publisher report:


With this option enabled, you can choose different templates and output formats.


Column Aliases in BI Publisher


Problem:

If you create a dataset and build your SQL-query by Query Builder or type it manually, you have to watch out that you do not use column aliases like ‘FEED’.

See example below:

SQL-query:

select BI_TABLE.FEED as FEED,
      BI_TABLE.FEEDBACK as FEEDBACK,
      BI_TABLE.APPLICATION_ID as APPLICATION_ID,
      BI_TABLE.NAME as TYPE_NAME,
      BI_TABLE.DESCRIPTION as TYPE_DESCRIPTION
from HR. BI_TABLE;

XML-file:

Internet Explorer seems to perform a strict XML validation. He gives you following error:


Solution:

Replace all column aliases with names like ‘FEED’ and your problem is solved. See solution below:

SQL-query:

select BI_TABLE.FEED as COLUMN_01,
      BI_TABLE.FEEDBACK as COLUMN_02,
      BI_TABLE.APPLICATION_ID as APPLICATION_ID,
      BI_TABLE.NAME as TYPE_NAME,
      BI_TABLE.DESCRIPTION as TYPE_DESCRIPTION
from HR. BI_TABLE;

XML-file:


Note: You can use ‘FEED’ as name for database tables but remember, not for column aliases!!!

Oracle BI Publisher Date-type

You can only format dates when they are in the canonical date format. This format looks like: YYYY-MM-DDTHH24:MI:SS.

You have two options:
1. adapt the XML generation process to generate the date in the canonical format and use the format date functions of BI Publisher in the template.

2. adapt the XML generation process to generate the date in the format of the client (example: DD-MM-YYYY) and print this directly on your report.


Solution 1:

First, we create a new dataset. We give it a name and select SQL Query as Type. Then choose a ‘Data Source’ and check ‘Cache Result’.

We build a query by Query Builder in BI Publisher or type it manually. See below to our ‘Data Set’ -query.

Note: XML-Date is in the canonical format and can be imported directly in the template. Example below is based on a ‘sysdate’ and stored in a field called ‘FULL_DATE’.

DS_Query:

select BI_DATE.FULL_DATE as FULL_DATE
from HRM.BI_DATE BI_DATE

After we create a dataset, we generate an XML to build our RTF-template.

XML-type:

&lt?xml version=”1.0″ encoding=”UTF-8″ ?&gt
&ltROWSET&gt
   &ltROW&gt
      &ltFULL_DATE&gt2008-02-19T13:24:37.000+01:00&lt/FULL_DATE&gt
   &lt/ROW&gt
&lt/ROWSET&gt

Properties:

Load XML into MS Word (with Oracle BI Publisher Desktop). Import the field ‘FULL_DATE’ and edit the properties. We can choose different format-types in the drop-down box. Choose one for example: ‘d-MMM-yy’ or type your own format-type like ‘dd-mm-yyyy’.

Note: Field ‘FULL_DATE’ is a canonical format.

Results in Word:

Solution 2:

First, we create a new dataset. We give it a name and select SQL Query as Type. Then choose a ‘Data Source’ and check ‘Cache Result’.

We build a query by Query Builder in BI Publisher or type it manually. See below to our ‘Data Set’ -query.

DS_Query:

select BI_DATE.FULL_DATE as FULL_DATE,
   to_char(BI_DATE.FULL_DATE) as CHAR_DATE,
   to_char(BI_DATE.FULL_DATE,’dd-mm-yyyy’) as FORMAT_DATE,
   to_char(BI_DATE.DAY_DATE,’DD’) as DAY_DATE,
   to_char(BI_DATE.MONTH_DATE,’MM’) as MONTH_DATE,
   to_char(BI_DATE.YEAR_DATE,’YYYY’) as YEAR_DATE
from HRM.BI_DATE BI_DATE

After we create a dataset, we generate an XML to build our RTF-template.

XML-type:

&lt?xml version=”1.0″ encoding=”UTF-8″ ?&gt
&ltROWSET&gt
   &ltROW&gt
      &ltFULL_DATE&gt2008-02-19T13:24:37.000+01:00&lt/FULL_DATE&gt
      &ltCHAR_DATE&gt19-FEB-08&lt/CHAR_DATE&gt
      &ltFORMAT_DATE&gt19-02-2008&lt/FORMAT_DATE&gt
      &ltDAY_DATE&gt19&lt/DAY_DATE&gt
      &ltMONTH_DATE&gt02&lt/MONTH_DATE&gt
      &ltYEAR_DATE&gt2008&lt/YEAR_DATE&gt
   &lt/ROW&gt
&lt/ROWSET&gt

Load XML into MS Word (with Oracle BI Publisher Desktop). Because we have set our date types in the query, we just can import the available fields without editing the properties.

Results in Word:

Solution 3:

XML-Date is not a canonical date format. Second solution for this problem is similar to solution 2. Except that we don’t edit the SQL-query and generate XML.

Load XML into MS Word (with Oracle BI Publisher Desktop). Import necessary fields and edit properties.

Example field ‘Full Date’:

Choose ‘Word Properties’:

Then ‘Add Help Text…’

Type following statement by ‘Help Key (F1)’: &lt?format-date: FULL_DATE;’dd-MMM-yyyy’;’Europe/Brussels’?&gt

Note: ‘Europe/Brussels’ is the time-zone of your region.

And click ‘OK’ -> ‘OK’.

Disadvantage: you have to apply this solution to every field on the report. If you do it on query-level, you can import fields without editing them in the template.

Oracle Open World – Keynote Charles Phillips

This morning a Keynote was given by Charles Phillips, President of Oracle Corporation. In this keynote Charles Phillips gave us an insight on the vision of Oracle and the strategy in marketting this vision throughout it’s customers, partners and of course software offering.

Each Challenge was tackled by the different Oracle Offerings there are today.

CEO Challenge:
The CEO of a company needs to integrate different kinds of applications and once in the boardroom he needs to be able to point all noses in the same direction, the direction of ROI.

This Integration-challenge can be tackled by the new Application Integration Architecture offering of Oracle. Through a demo we saw how this challenge was tackled using Webcenter dashboard as a centralized view-point, JDeveloper as the integrated Development Environment and BPA which was used for the full round-tripping of business analysis and development.

Using this Oracle Technology offering, the AIA, the CEO can integrate his existing Siebel, SAP, Financials or CRM applications in a unified, uniform and more-over standardized way.

CFO Challenge:
The Chief Financial Officer has other concerns such as governance and risk management to keep in mind throughout the different processes in his/her company. How can Oracle offer a solution to this common problem, faced within every company that has a diversity of policies and rules to adhere to.

The Oracle Governance & Risk Competence Manager:
Through this manager the CFO has a one-stop view to control the different policies which are defined throughout the organisation. If a certain rule is being violated, the Manager will show up a red light and in 1 step the CFO can change/adapt the policy rules so the company’s back on track.
This manager uses a centralized content repository and enforces the policies at runtime using the Active Apps Policies Suite.


Another Security-stack being offerd by Oracle entails the Database Vault and Audit Vault to secure as well data as audit data in an Integrated Security Stack.


Engineering & Manufacturing Manager:
The engineering and manufacturing manager has a lot of challenges as well, such as keeping insight on the product stock and having the ability bto manage the entire product lifecycle without any delay.

Oracle offers ‘Oracle Agile PLM’ to tackle all of these requirements, the Oracle Product Lifecycle Management Technology Stack.


In the demo we saw that the manager had a centralized view on all the needed data using Webcenter dashboard and a Shared Content System.
The webcenter dashboard could be hooked up with third-party legacy applications through adapters and uses XML standards based messaging for this purpose.

Operations Challenge, Gaining Actionable Insight:
The operations challenge every administrator, manager is facing today: act when necessary!
If a glitch has occured during a business process lifecycle, a product is out of stock, an order hasn’t been processed correctly, if a given manager hasn’t updated his tasks-lists, in other words when a bottleneck is being introduced inside the company … we need to be able to act!



Through the usage of Enterprise Performance Management Workspace, BI Enterprise Edition and the Action Framework we can act upon these glitches immediatly.

Through the Enterprise Performance Management Workspace, we have a real-time view on the business processes in our company, and act when necessary.

The Action Framework gives us the possibility to interate transactions, historical data and tasks so were able to act correctly given we have all the needed data to provide with an accurate view on our current business.

Through the usage of Packaged BI Applications (Financial Package, Operations Package, CRM Package), the customer can purchase thed package which is measured for his/her company.

How can we manage these heterogenous environments?
Enterprise Manager Services Dashboard gives us the capability of monitoring and administrating the different services which are running inside our business.
The EMSD gives a follow-up on as well the business as the system metrics in our organisation.

Through this EMSD we can enable Real Application Clustering at runtime, without downtime.