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.