You are here: BI Publisher Displaying items by tag: BI PUBLISHER TRAINING
Displaying items by tag: BI PUBLISHER TRAINING

This article is in continuation to our last article Adding Parameters and List of Values

Make a copy of OrderDM as OrderDetailDM  by Clicking the ‘Save As’  icon.

Add a new SQL Data set using following SQL query for the Order Items data.

 

Update the Group G1 => ORDERITEM from the structure tab. 

Navigate to structure tab and click the output section to view the XML Structure.  There is no relationship between ORDERS and ORDERITEM Groups in XML.  These are Multipart Unrelated Data Sets


In real time scenario, we often need a master detail or parent child relationship between two data sets.  This relationship defined through data link between two data sets, where one dataset defined as master/parent and other as details/child data set and this is referred as Multipart Related Data Sets.

There are two ways to define data link. Elements Level Link and Group Level Link.

Element Level Links: The element level link creates the join on columns of one query to column of other query. This is preferred option of defining the master details relationship.

In this example, Orders is master and OrderItems is detail data set. Both are linked with OrderID. There are multiple Orders items for a given Order. 

Select click the >> icon on ORDER_ID element of ORDERS Dataset. This will open the action windows. Click on Create Link action, This will popup the Create Link box, select the ORDER_ID1 element from the list and click OK to complete the operation

This will create data link on OrderID column between Orders and OrderItems. Hover the mouse on i icon to view the relationship.


Navigate to structure tab and click the output section to view the XML Structure. The OrderItem appears one level down the Orders



View the sample XML to verify the master detail relationship. There are multiple ORDERITM nodes for one ORDER node.

 

Creating Subgroups

This is another way to regroup the data at higher level or add the required hierarchy to flat data.

Look into sql query from Orders data set.  It lists out the orders for the Customers. There is 1.M relationship between Customers and Orders. 

Lets add the higher group on Customer level but before this we need to add proper order by clause to sort the data on CUSTOMER_ID AND ORDER_ID.

Select the ORDER data set from the Diagram pane and click the  icon at CUSTOMER_ID element level. Click the Group by action link form the action popup. This will add a new Group with CUSTOMER_ID element at higher level.


Click the >> icon at NAME element level, from the action menu, click on Move selected element to Parent Group.

This moves the Customer Name to Customer Group. Update the Tag name from the Structure tab to make it more readable.

 


Observe the new hierarchy. There three level.


==>CUSTOMERS

  ============> ORDER

 =====================> ORDERITEM

 

Generate the Sample XML and review the XML hierarchy.



Thats all for now. will few more features about the Data Model in next article.

Happy Reportng.

This article is part of BI Publisher for beginners series. You will see many more articles on Data Model design, Template desing, Administration, Web Services, Scheduler. The contents are picked-up from BI Publisher Training documents from  Adiva consulting. If you have any question regarding these articles or training program, please contact This e-mail address is being protected from spambots. You need JavaScript enabled to view it .

Published in BI Publisher

Previous >>> Introduction to Data Model

The Data Model Editor is designed with a component pane on the left and work pane on the right. Selecting a component on the left pane launches the appropriate fields for the component in the work.


 

Click the Data Model icon from the Create menu.

 

 

Data Model Properties

Enter the following properties for the data model:

Description:  Description about the Data Model.

Default Data Source — Select the data source from the list. Data models can include multiple data sets from one or more data sources. The default data source you select here is presented as the default for each new data set you define

Oracle DB Default Package — If you define a query against an Oracle database, then you can include before or after data triggers (event triggers) in your data model. Event triggers make use of PL/SQL packages to execute RDBMS level functions. For data models that include event triggers or a PL/SQL group filter, you must enter a default PL/SQL package here. The package must exist on the default data source.

Database Fetch Size — Sets the number of rows fetched at a time through the JDBC connection.

Enable Scalable Mode — processing large data sets requires the use of large amounts of RAM. To prevent running out of memory, activate scalable mode for the data engine. In scalable mode, the data engine takes advantage of disk space when it processes the data.

Backup Data Source — If you have set up a backup database for this data source, select Enable Backup Connection to enable the option; then select when you want BI Publisher to use the backup.

XML Output Options

These options define characteristics of the XML data structure. Note that any changes to these options can impact layouts that are built on the data model.

•    Include Parameter Tags — If you define parameters for your data model, select this box to include the parameter values in the XML output file


•    Include Empty Tags for Null Elements — Select this box to include elements with null values in your output XML data.


•    Include Group List Tag —Select this box to include the rowset tags in your output XML data. If you include the group list tags, then the group list appears as another hierarchy within your data.


•    XML Tag Display — Select whether to generate the XML data tags in upper case, in lower case, or to preserve the definition you supplied in the data structure.

Attachments to the Data Model

•    Attaching Sample Data - The sample data is used by BI Publisher's layout editing tools. The Data Model Editor provides an option to generate and attach the sample data


•    Attaching Schema - The Data Model Editor enables you to attach sample schema to the data model definition. The schema file is not used by BI Publisher, but can be attached for developer reference.


•    Data Files - If you have uploaded a local Microsoft Excel file as a data source for this report, the file displays here


Creating Data Sets

1.0 On the component pane of the data model editor click Data Sets

 

 

Select the data set type from the list. For this exercise, select the “SQL Query” Data Set type.

 

Provide the Name and Select the Data Source from the drop down list. The Data Sources are defiend through “Administration =>DataSource=>JDBC Connection” UI

 

The SQL Query can by typed directly on SQL Query pane or it can be designed using Query Builder Tool.  Click the “Query Builder” button to invoke the Query Builder Tool.

 

 

Press the save button and we get the auto generated SQL query. The Query Builder Tool can be use to design simple and complex query but a experienced SQL query writer prefer to write query manually using SLQ PLUS, Toad or any other tool
 

 

Press OK to save the data set.

Save the data model by clicking the Save button. Select the proper folder and appropriate name for Data Model. 
 

 

Click on XML icon on top left panel and view and verify the Sample XML Data. It the Data Model is not saved, it will ask to save the Data Model first

 

 

Click the Run button to view sample XML Data or Return button to back to Data Model designer.

Check the following options from upper left corner

  • Export XML : option allows to export the sample xml data to file System. Exported XML required by World Template Builder for RTF template design.
  • Save As Sample Data : Option allows to save the Sample XML data within Data Model. This is mandatory step. It is required by Report Designer to create Report Definition.
  • Get Data Engine Log : Option provide the Data Engine log for debugging purpose. The log level can be set through Enterprise Manager (em) console.

 

Next >>>Let's Structure the Data

This article is part of BI Publisher for beginners series. You will see many more articles on Data Model design, Template desing, Administration, Web Services, Scheduler. The contents are picked-up from BI Publisher Training documents from  Adiva consulting. If you have any question regarding these articles or training program, please contact This e-mail address is being protected from spambots. You need JavaScript enabled to view it .
 
 

Published in BI Publisher
Saturday, 28 July 2012 09:54

OBIEE 11g Training

OBIEE 11g  (11.1.1.5.0) 

This course is designed for those professionals who are looking for Great career as Consultant in BI Technology. This course provides a solid foundation on OBIEE 11g. Participants will learn to design the Repository, Creating Answers/Analysis Requests, and Interactive Dashboard, Delivers, OBIEE 11g/Fusion Middleware Security and OBIEE-BI Publisher integration.

 Course Content

 PART I:  Overview
  • General Datawarehousing concepts
  • Dimensional modeling basics
  • Oracle Business Intelligence Application Overview
  • Describing the Oracle Business Intelligence Applications Architecture and components
  • BI Server
  • BI Answers
  • Interactive Dashboards
  • Delivers
  PART II: ADMIN TOOL AND RPD MODELING
  • Creating Repository Using Administrative Tool
  • Repository Basics
  • Building Physical Layer of Repository
  • Building Physical Model and BMM Layer of a Repository
  • Building the Presentation Layer of a Repository
  • Testing & Validation of a Repository
  • Calculations & Measures
  • Creating Dimension Hierarchies
  • Organizing Presentation Layer
  • Working with Initialization Blocks and Variables
  • Adding Multiple Sources
  • Executing Direct Database Requests
  • Working with Aggregates
  • Creating Time Series Measures
 PART III: DASHBOARD AND ANALYSIS
  • Creating Interactive Dashboards using Answers
  • Creating query and chart
  • Working with Filters
  • Working with Pivot Table
  • Building Views and Charts in Requests
  • Creating Interactive Dashboard
  • Configuring Interactive Dashboards and prompts
  • Configuring Guided Navigation Links
  • Using Partitions and Fragments
  • KPI & Scorecarding
  • Oracle BI for MS-Office
  PART IV: SCHEDULING AND DELIVERING
  • Sending Alerts Using Delivers   
  • Configure Scheduler Schema
  • Scheduler Configuration
  • Creating Sample Request and Adding to Interactive Dashboard
  • Creating and Delivering a Simple iBot
  • Creating and Delivering a Conditional iBot
  PART V: SECURITY FEATURES AND UPGRADE
  • Authentication & Authorization
  • Object & Data Level Security
  • Webcatalog Level Security
  • Creating Users, Groups & Webgroups
  • Upgrading RPD/WEBCAT from a earlier release
Published in Training

 Have you tired to migrate OBIEE 10g Repository and Presentation Catalog  to OBIEE11g ? As a part of OBIEE 11g uptake exercise for one of our client, We tried it and went pretty well. I thought, lets share with our blog readers..

Pre-Requisites
• Start up all the OBIEE 11g related services (BI Services and Web logic Server) and check that you can log in to Enterprise Manager,WLS Admin Console and OBIEE Answers.
Enterpise Manager   : http://localhost:7001/em
 WLS Admin Console : http://localhost:7001/em console
 OBIEE Answers/DashBoard   : http://localhost:9704/analytics 
• All the releventrelevant TNS names used in the 10g rpd connections are present in <OBIEE_MW_HOME>/Oracle_BI1/network/admin/tnsnames.ora file

 • Step-1

Navigate to <OBIEE_MW_HOME>/Oracle_BI1/bin/ua.bat . This batch file is the Upgrade Assistant wizard. Launch the wizard by double clicking on it.
Once this launches, it specifies the version(11.1.1.3.0) the upgraded rpd will be. Click NEXT

• Step-2

In this page select "Upgrade Oracle BI RPD and Presentation Catalog" radio button. Click NEXT


 

• Step-3

Here check the "Upgrade Repository (RPD)" checkbox.
Specify the location of the 10g RPD file.
Enter the Administrator credentials of the old 10g RPD file.
Specify the password for the upgraded 11g RPD and confirm it once again.

 

• Step-4

Specify the Weblogic Admin Console username and password. Port number is 7001
Click Next..

 


 

During the examination process, Upgrade Assistant checks for the following:
• The source directory exists.
• The source directory is readable and contains a file for upgrade.
Under the Status column, the word succeeded should appear. If instead, the word failed appears, inspect the log file for details.

• Step-5

Check the upgrade summary and click UPGRADE to start the upgrade process.
 

• Step-6

Once the upgrade process completes the process log output is available at <OBIEE_MW_HOME>\Oracle_BI1\upgrade\logs


 

The new rpd will be named as OldPaint_BI001.rpd and placed at the following path:
<OBIEE_MW_HOME>\instances\instance1\bifoundation\OracleBIServerComponent\coreapplication_obis1\repository.
As part of upgrade, security is also upgraded from 10g to 11g. This includes the Users and Groups.
Manual customizations to files like instanceconfig.xml are not upgraded and must be redone after upgrade. Same applies for nqconfig.ini, even though the nqconfig.ini parameters are fully controlled from the Enterprise Manager in OBIEE 11g.
 

Hope you will find this article useful during your upgrade process... Please write your feedback to   This e-mail address is being protected from spambots. You need JavaScript enabled to view it .

Published in OBIEE

The objective of this post is to explain the “sorting” capabilities available in the RTF. Many might already be aware of the standard BI Publisher syntax for sorting in the RTF template:

<?sort:VENDOR_NAME?>

 

 

The above construct sorts the data by the VENDOR_NAME in ascending order – an alphabetic sort is performed. However, what if the requirement was to sort on a number field. For example, if you want to sort by the VENDOR_NUMBER, then the above will produce incorrect results as an aphabetic sort would be applied to a numeric field. The following BIP construct can be used to specifc the order and the sort type:

<?sort:VENDOR_NUMBER;'ascending';data-type='number'?>

 

The above BIP construct performs a numeric sort on the vendor_number. That's rather simple to do. Let's say we would like to sort on a field but the field name is not known at design time. The field name exists in the xml data. Let's say the element P_SORT_BY specifies the field on which sorting must be done. So, if the value of P_SORT_BY is VENDOR_NAME, sorting must be performed on the field VENDOR_NAME. This can be acheived by using the following XSL:

<xsl:sort select=".//node()[local-name()=P_SORT_BY]" order="ascending" data-type="number"/>

Let's try to decipher the above xsl syntax. The above XSL has three parts – select, order and data-type. While order and data-type are self explanatory, the Xpath in the select actually does the magic. Let's try to understand this XPath in detail. The function node() returns an node. Specifying just node() will return all the nodes in the XML data. In order to filter, square brackets [] are used and the appropriate filter condition are specified inside. The call local-name() returns the name of the node. Therefore, the condition local-name()=P_SORT_BY will return all nodes whose name matches the value specified by P_SORT_BY.

 

The “order” and “data-type” fields can also use xdoxslt functions in them. Thus, if the sort type is known at run time, the following can be used to set the sort type at runtime:

<?xdoxslt:set_variable($_XDOCTX, ’sort_datatype’, value)?>

<xsl:sort select=".//node()[local-name()=P_SORT_BY]" order="ascending" data-type="{xdoxslt:get_variable($_XDOCTX, 'sort_datatype')}"/>

The above sorts by the field whose value is specified by the P_SORT_BY and the sorting type is decided based on the variable sort_datatype. Similarly, the order can also be obtained from a xdoxslt variable.
 

Published in BI Publisher

 This article is in continuation to my previous article, XML generation using BI Publisher Data Template.

PL/SQL support within Data Template separate the data extraction logic and XML generation in two separate layers. We can hook the PL/SQL logic to implement the Business rules, Data extraction or post processing logic. Through PL/SQL logic, we can push some of the complex data calculation and network intensive operations  to Database layer.

Let's discuss following sample Data Template.  The data template has associated default plsql package.

 

Default PL/SQL Package (emp_test1.pls)

CREATE OR REPLACE package emp_test1 as

DYNAMIC_WHERE_CLAUSE varchar2(3200);
P_DEPTNO varchar2(10);
P_SAL number;
P_HIRE_DATE date;
function BeforeReportTrigger return boolean;
function DeptGroupFilter (p_dept_number number ) return boolean;
function AfterReportTrigger (totalOrgsal number) return boolean;
END;
/
CREATE OR REPLACE
PACKAGE BODY emp_test1 AS
function BeforeReportTrigger return boolean IS
begin
IF (P_DEPTNO ='10') THEN
DYNAMIC_WHERE_CLAUSE :='D.DEPTNO =10';
ELSIF (P_DEPTNO ='20') THEN
DYNAMIC_WHERE_CLAUSE :='D.DEPTNO =20';
ELSIF (P_DEPTNO ='30') THEN
DYNAMIC_WHERE_CLAUSE :='D.DEPTNO =30';
ELSIF (P_DEPTNO ='40') THEN
DYNAMIC_WHERE_CLAUSE :='D.DEPTNO =40';
ELSE
DYNAMIC_WHERE_CLAUSE :='1=1';
END IF;
return true;
end;
FUNCTION AfterReportTrigger (totalOrgsal number) return boolean IS
BEGIN
 
INSERT INTO org_sal (report_date,total_sal_amount ) VALUES (sysdate,totalOrgsal);
commit;
return true;
END;
 
FUNCTION DeptGroupFilter (p_dept_number number) return boolean is
BEGIN
IF (p_dept_number='30') THEN
RETURN FALSE;
END IF;
RETURN TRUE;
end;
 
END;
/

PL/SQL logic should be implemented as single PL/SQL Package with following conditions.

  1. All the parameters define in Parameter section of Data Template should be define as parameters in plsql package.
  2. PL/SQL logic should be implemented as function and must return a Boolean value.
  3. The package should be define as defaultPackage attribute in Data Template Header.

In the above example, we see following three instance of PL/SQL call. Two dataTriggers and one Group Filter

-<dataTrigger name="afterReport" source="emp_test1.AfterReportTrigger(:ORG_TOTAL_SALARY)" /> 
 -<group name="G_DEPT" source="Q1" groupFilter="emp_test1.DeptGroupFilter(:DEPT_NUMBER)">
-<dataTrigger name="beforeReport" source="emp_test1.BeforeReportTrigger" />

1) Before Report Trigger

The position of first dataTrigger is before the dataStructure section in Data Template. It gets fire before the very first SQL query executes. In this example,  PL/SQL function  BeforeReportTrigger drive the DYNAMIC_WHERE_CLAUSE variable based on P_DEPTNO value. P_DEPTNO value will be available within plsql package as the process set the value from data template parameters to pl/sql parameters. That is the reason; we need to define the same set of parameters in PL/SQL as defined in data template. BeforeReportTrigger function set the DYNAMIC_WHERE_CLAUSE parameter calue as “D.DEPTNO =10”. Before executing the SQL query Q1, process replace the substitute variable DYNAMIC_WHERE_CLAUSE with the actual values and following query get fired.

              SELECT d.DEPTNO,d.DNAME,d.LOC,
              EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM from DEPT D, EMP E  where D.DEPTNO=E.DEPTNO
              and D.DEPTNO =10

PL/SQL parameter/s can be reference as substitute variables within SQL query and this feature allow us to design the dynamic SQL query based on the input parameters.


The before report triggers can be used to designing dynamic SQL queries or filter conditions using substitute variables,populating the temporary or global temporary tables for Oracle apps for complex data extraction process and then write the SQL query against these temp tables. 

2) After Report Trigger

The position of “afterReport” data trigger is after the dataStructure section and termed as after Report Trigger. These triggers get fire after the XML generation complete. In our example function “AfterReportTrigger” accept the ORG_TOTAL_SALARY  as input parameter and insert a record to ORG_SAL table. 
 
The purpose of after report triggers is to implement any post process logic as cleaning or deleting the temp tables or records, auditing activities.

3) Group Filter

The use of Group filter is to filter out the groups from the XML ouput, if the specified condition implemented in calling function does not match. If the PL/SQL function returns the false, the particular instance of group will not be the part of XML output.

In the example, DeptGroupFilter accept the dept_number element value as input. If the value is 30, it returns false and Group belongs to deptn0=30 will not be there in final xml.

I tried multiple before and aft er data triggers within the same data template and they executed sequentially, So I  think we can use multiple before and after dataTriggers. All these three call are simple PL/SQL functions and designed for some specific operations but there is no restrictions on nature of PL/SQL logic inside the function, just make sure it return the appropriate Boolean value.

Please feel free to ask any question on this topic and I will be happy to help you out.

Thanks
Raj.

Published in BI Publisher