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.
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.
No comments:
Post a Comment