You are here: Blog BI Publisher Exploring BI Publisher 11g : A simple report with DataModel Editor

Exploring BI Publisher 11g : A simple report with DataModel Editor

Written by  Raj
Rate this item
(11 votes)

If you have installed OBIEE 11g successfully, lets try to explore some of the new features and tools available with BI Publisher 11g.

Unlike 10g, where Report is a single entity and data sets are the part of Report definition, in 11g, Data Sets (which termed as Data Model in 11g) and Report definition are two separate entities.

Data Model, which drive/extract the xml data for report is the advance version of Data Template, So there is no Data Template in 11g, its all Data Model with a very nice Data Model editor to design simple to complex Data model. This support some new data source like, BC4J View Objects and EXCEL, which were not supported in earlier releases as data source.

 

 

Report definition includes the Data Model reference, Layout template and other report specific metadata required to render or generate the Report output.

For these many years, we are using RTF templates as main Layout Template, now there is new format available with 11g, it is xpt format, which looks like Oracle’s proprietary format. As claimed, it generates almost pixel perfect output and could be a good substitute for PDF Forms. A nice Report Designer is available to design these xpt reports.

As I mentioned earlier, the complete report consist of two main objects, Data Model and Report definition. Data Model should exist, before we start Report definition.

Before design the data model, make sure the required jdbc connection is setup through BI Publisher Administration UI. To access JDBC data source UI, click the Administration from the Top bar and select the Manage BI Publisher Administration from the Common Administration Page.

 

 

Open the Data Model editor by navigating to New=> Data Model.

Enter the description, default data source type. This is simple report and we are not using any PL/SQL logic, so leave the default package as blank.

The Data Model allows you to define the backup data source, in case the primary data source is not accessible, this can be define through Administration UI. I left it blank, as I am not using any backup data source.

If you have used Data Template, you might be remembering the next three properties. I have checked the Include Parameter Tags as I want Parameters elements to be part of XML Data. I also checked the “Include Empty Tags for Null Elements” as  I want to include the null elements for null column values.


The next property, “XML Tag Display” allows you control XML Tag case in XML data. I selected as upper to make sure all the elements appears as Upper case in XML Data.

The attachment section allows us to upload the sample XML data or schema, this will helpful to design and test the Layout Template. The Data model allows you to generate and upload the sample XML automatically.

Next Step is to define the Data Set. Select the Data Set tab from the left panel and Click the * icon from the right panel, this will list down the entire supported data source. We will use the SQL Query data source for our report.

Select the right data source. We can directly type our query or use the query builder to design the query. The Query builder is the same as we see in 10g release, so lets skip it and paste the query directly. I have added P_DEPT parameter to filter the data based on depart number. Save the query.

When we save the query with the bind variable, the process asked if we want to to create the Parameter for the bind variable. Press OK to create the Parameter
 

In right panel we see two rectangle boxes, which represent the data structure, One corresponding to Outer or top most group and the other one “G_1” corresponding to our SQL query.  It will list the entire columns as element within this group.

If we save model at this stage, this will generate flat xml with no hierarchy. Lets add one level hierarchy to represent Dept, Employee parent child relation. To create a group on DEPTNO, select the >> on the same line as DEPTNO, this will popup the option menu, select the “Goup by” option.  

This will add the Parent Group with DEPTNO as element.  Move the DNAME element to parent group as well by selecting “Move selected element to parent group”.   This will bring DEPTNO and DNAME element to parent group
 

Select the SAL element from G2 group and drag it to “Drop here for aggregation” section of Parent Group and select function has Summary. This will add the summary column (CS_1) for SAL element at department level to calculate the Department Salary

If you have observed, there are three tabs at top of dataset right panel.  Diagram, Structure and Code.

Navigate to the structure tab. This has three sections, Data Source, XML View and Business View. I think this concept came from OBIEE Administration. Where we have physical, logical and presentation layer.

Data Source represents the actual data structure source based on our source query.

XML View represents the actual XML TAG in final XML output. We are allowed to change the XML Tag name. Lets change the summary column CS_1 to DEPT_SALARY.

Business View, as name suggest represent the Display Name for that element and as per my understanding at this stage, It could be use to drive Report /Column titles/headers.  Lets change the CS_1 to Department Salary. We can change other Display Name as well.

If we navigate to Code tab, we can see how our Data Model Structure code looks like. It looks very similar to 10g Data Template Structure.

Event triggers are same as before and after Report triggers in Data Template. Lets skip for this exercise.
Flex fields are Oracle Apps specific so leave this as well.
Data Model has already created P_DEPTNO parameter for you, So lets create List of Value for P_DEPTNO parameter and associate the LOV to P_DEPTNO parameter.

After creating the DEPT_LOV, select the P_DEPTNO Parameter, change it type as Menu and associate the DEPT_LOV with it.


Save the Data Model.

It is time to test our hard work, so lets click the XML icon from the top right corner, just before the save icons.

Since this is just a test run, we can select the number of rows to execute. This is very nice feature and allows creating a sample xml data file, which can be uploaded automatically to Data Model. The sample xml file is required to design the Layout Template

Click the open menu available next to Return button and select the “Save as Sample Data” option to save the xml output as sample data.

I would push the Layout Template Design to next blog, till then just keep on exploring the Data Model designer; it has lots of new functionality, like expression builder,  very handy to create complex expression, which are not available through data template, handling XML Tags and Display Names, merging data from different Data Source like SQL query and Excel sheet.

So have fun with new Data Model designer, till we dive into Layout Builder to create pixel perfect Template with new xpt format.

Thanks.

Read 67480 times

8 comments

  • Comment Link rajnish kumar Friday, 20 September 2013 07:43 posted by rajnish kumar

    Hi, thanks for the blog... I am getting error, while generating xml.
    Error: XML Parsing Error: mismatched tag. Expected: .
    Location: http://vdixpe300ng:7001/xmlpserver/servlet/xdo
    Line Number 2, Column 580:
    can u plz resolve this problem..i really need it as soon as possible

    Report
  • Comment Link dawit Wednesday, 14 August 2013 20:21 posted by dawit

    Hi Dinesh, This is a browser problem if you upgrade ur IE to the latest version or use Fire fox it should work.

    Report
  • Comment Link Abid Wednesday, 20 March 2013 14:36 posted by Abid

    Hi, thanks for the blog... I am getting error, while generating xml.
    Error: XML Parsing Error: mismatched tag. Expected: .
    Location: http://vdixpe300ng:7001/xmlpserver/servlet/xdo
    Line Number 2, Column 580:
    Could you please tell me how to resolve the issue.

    Report
  • Comment Link Dinesh Wednesday, 28 November 2012 17:13 posted by Dinesh

    Issue I was facing due to Compatibility View settings in IE browser. make sure your Compatibility View is switched off ? In your IE browser go to Tools --> Compatibility View Settings and make sure all the checkboxes are unchecked.

    Report
  • Comment Link Jan Holub Friday, 05 October 2012 08:54 posted by Jan Holub

    to Friday, 31 August 2012 12:15 posted by Dinesh:
    It happens on my Internet Explorer 8.0 too. Tried & tested on Chrome and Firefox, works fine.
    Jan

    Report
  • Comment Link Raj Thursday, 06 September 2012 14:30 posted by Raj

    Dinesh,
    There is BI Publisher 10g to 11g Upgrade assistance Utility.

    Check the following section from BI Publisher User Guide.

    Upgrading Oracle Business Intelligence Publisher
    http://docs.oracle.com/cd/E14571_01/bi.1111/e16452/upgrade_bip.htm

    Report
  • Comment Link Sundar Wednesday, 05 September 2012 08:00 posted by Sundar

    Hi Raj,

    Can you give me some know-how or pointers about,
    moving 10g BI Publisher reports to 11g Bi Publisher?

    Thanks You
    Sundar K

    Report
  • Comment Link Dinesh Friday, 31 August 2012 12:15 posted by Dinesh

    Hi Raj, Thanks for the article. I ahve 1 problem. When I pressed sample xml data button , it did did not get any sample data. I am getting blank screen. I testing my connection and all seems to be fine. Any idea what to check if you don't get sample data listed ?

    Report

Leave a comment

Make sure you enter the (*) required information where indicated.
Basic HTML code is allowed.