obiee intallation step by step

Page | 1

Oracle Business Intelligence 11g

Advanced Workshop

PTS Delivered Workshop

Student’s Lab Book

PTS: Oracle Business Intelligence 11g Advanced Workshop

PTS Workshop: BI11g Advanced: Installing OBIEE 11g Page | 2

Authors

Jignesh Mehta Naresh Nemani

Oracle Corporation World Headquarters 500 Oracle Parkway Redwood Shores, CA 94065 USA Worldwide inquiries: Phone: +1 650 506 7000 Fax: +1 650 506 7200 www.oracle.com Oracle is the information company Oracle is a registered trademark of Oracle Corporation. Various product and service names referenced herein may be trademarks of Oracle Corporation. All other product and service names mentioned may be trademarks of their respective owners. Copyright © 2010 Oracle Corporation All rights reserved.

Page | 3

Table of Contents

Lab 1: PTS OBIEE 11g Advanced Workshop Setup ........................................ 4

Lab 2: OBIEE 11g Installation and Configuration for the Workshop.............. 5

Lab 3: Advanced Reporting, Master Detail Linking ....................................... 31

Lab-4: Deploying SampleApp RPD and Webcat on BI 11g ........................... 40

Lab 5: Building Report with BI Publisher ...................................................... 54

Lab 6: Working with Action Framework ........................................................ 95

Lab 7: Building Advanced Hierachies ......................................................... 112

Lab 8: Creating Scorecards and KPIs ........................................................... 131

Lab 9: Working with XML API ....................................................................... 149

PTS Workshop: BI11g Advanced: Workshop Environment Page | 4

Lab 1: PTS OBIEE 11g Advanced Workshop Setup

Oracle BI 11g High Level ArchitectureOracle BI Domain

WebLogic Domain

Oracle BI System Components

Supporting Database Schemas (Created by RCU)

Admin Server Managed Server

Node Manager

Admin Console

Enterprise Mgr

Action Service

Web Service SOA

BI Office BI Publisher

BI Plugin Security

BI Server BI Scheduler BI Presentation Server BI Java Host Cluster Controller

OPMN

What is not installed/available during this Workshop:

Since we are simplifying workshop environment with XE database, following

objects are not installed.

- Data Mining Objects

- Spatial Data Sets

- Essbase Components

You can download and install full scale SampleApp objects from OTN after the

workshop, as we will be covering all the topics for learning even though some of

the objects are not feasible to install during workshop.

PTS Workshop: BI11g Advanced Page | 5

Lab 2: OBIEE 11g Installation and Configuration for the Workshop

2.1 Database Installation and Configuration

This section will guide you to install Oracle database XE on your local

machine. We will be using XE database during the workshop to minimize memory

and space requirements because XE database is good fit for learning purpose. In

practice, you can install Oracle Database of enterprise or standard edition to take

full advantage for data types like spatial data or features like OLAP. If you have XE

database already installed on your machine, you can skip to section 2.1.2, else

please continue to install XE database.

Pre-requisites:

• Only Windows platform of 32 bit OS is supported for this workshop. If you

have 64 bit OS, make sure to have XP 32 bit mode available.

• Minimum of 4GB RAM required, 3 GB RAM would work but, system would

be slow.

• Do not use any directory name with space while installing/working with

Oracle DB and BI11g.

• Labs are designed to give you experience with BI11g installation and new

features, which should enable you to install and learn from full scale

SampleApp on enterprise database. Labs are optional during workshop, as

we will be covering material with great extent on new features and

infrastructure.

2.1.1 Installation of XE Database

You can download XE database from OTN site using following url. XE DB

Download:

http://www.oracle.com/technetwork/database/express-

edition/downloads/index.html

Download XE Universal Edition: (Make sure you download and install universal

edition) http://download.oracle.com/otn/nt/oracle10g/xe/10201/OracleXEUniv.exe

You can learn more about XE database: XE DB Info:

http://www.oracle.com/technetwork/database/express-edition/overview/index.html


Once downloaded, start XE database installation process by double clicking exe

file.

Continue by clicking Next, Accept Agreement and click next again:


Accept default setting for installation folder and continue by clicking Next.

Make sure to enter password as Admin123 . We will be using the same password

for database installation as well as BI11g installation for simplicity and minimized

configuration.


Click install to complete the installation.


Once installation completes, XE database starts automatically and by default will

be enabled as a service on windows. Click finish with enabled check box for

launching database home page.

Default port for XE database is 8080, you can lauch database home page from

Program Files menu as well: http://127.0.0.1:8080/apex/


Login with username sys and password Admin123 to verify installation.

Logout to continue with next section.

2.1.2 Configuring XE Database for BI11g Installation

Open command prompt and type > sqlplus sys as sysdba

Enter password when prompted: Admin123

We will need to change sessions and processes parameter values for XE database

for leveraging it for BI11g installation. Run following two commnads to display

default values that we will modify.

SQL> show parameters sessions

SQL> show parameters processes


Let’s change sessions value to 250 and processes value to 250 as well by running

following commands.

SQL> alter system set sessions=250 scope=spfile;

SQL> alter system set processes=250 scope=spfile;

Exit from sqlplus by typing exit at sql prompt. We have now configured XE

database with more number of sessions and processes to be usable for BI11g.


2.2 Installing BI11g Metadata Schema

BI 11g is now standardized with Oracle fusion middleware and hence leverage

database for metadata storage just like any other middleware product. This section will

guide you to install BI11g metadata schema on XE database you just installed and

configured. Same process can be leveraged to install metadata schema on other

supported databases as well.

If you have not done already, please download BI11g installation files and

Metadata installation files. Please make sure you download following files:

Download page:

http://www.oracle.com/technetwork/middleware/bi-enterprise-edition/downloads/bus-intelligence-11g-

165436.html

Download for Windows 32 bit only version of four zip files for BI installation and

one zipfile for RCU (Repository Creation Utility) Installer as shown below:

Unzip four zip files of BI installation into single parent folder and unzip RCU zip file

into different parent folder. We will first install metadata schema using RCU.


From unzipped folder for RCU, run rcu.bat file to start the utility: rcu.bat file is

located under RcuHome\BIN dir

RCU utility will launch the GUI based wizard to guide you through the steps. Click

Next to continue.


Select create and click Next

Make sure database type is Oracle Database and enter following information to

continue. Host Name: localhost Port: 1521 Service Name: XE Username: sys

Password: Admin123

Since we are using developer database like XE, RCU shows warning message

that we can ignore for workshop purpose. Click ignore to continue.


Click Ok to continue once RCU validates and initializes database configuration.

Make sure to select Business Intelligence Schema on selection page. This will

also selects MDS schema automatically. Makes sure you selection is exactly what

is show in the image below otherwise you will run into installation issues since we

are using XE for workshop purpose.

Keep Create a new Prefix to DEV.


Click next to create schema and the click OK once RCU shows schema creation

prerequisites check completed.


Enter password for both the schema as Admin123 and click Next to continue.

Click Next again to have RCU create the selected schemas

Click ok to continue creation of tablespace.


Once tablespace created, click OK to continue.

Now RCU will create required schema objects within the tablespace just created.

Click Create and continue


Click close to finish RCU wizard once schemas are created successfully.

Make a note that we have used Admin123 as a password for all the

users/schemas so far and we will continue to use the same password. We will

need this password for BI installation as well to let BI installer knows where the

metadata schemas located and what is the password to connect to the schema.

Next section, we will install BI11g.


2.3 Installing BI11g

From unzipped folder for BI installer, double click on setup.exe to launch BI11g

installer. BI 11g, again, just like other Oracle products, uses standardized Universal

Installer. (Note: If you are on win 64 bit OS, run setup.exe from

<BISHIPHOME>\Disk1\install\win32\setup.exe).

Click Next to continue with installation process once Installer comes up.


We will use simple installation type so that BI 11g Installer will install and configure

all the required components with default settings. Make sure to select Simple

Install and click Next to continue.


Click Next to continue, once prerequisite is checked on next screen.

Select installation directory. Please type C:\BI11g and continue. Installer will

create new directory if does not already exists.

Enter weblogic as a username and Admin123 as a password to continue. This is

default administrator user for BI environment which includes weblogic server and

BI systems.


Keep default components selected and continue to installation.

Now we will provide information on database schema where we installed BI

metadata. Enter connect string as localhost:1521:XE

BIPLATFORM Schema is DEV_BIPLATFORM


BIPLATFORM Schema password is Admin123

Click Next to continue.

Uncheck the box for receiving updates through email and continue. Click Yes when

prompted for conformation on not providing email address.


Click Install to have BI 11g installation process started.

Installation process takes time to complete full installation and configuration. Since

we are installing on a local machine, minimum of 3 GB RAM is required to have


installation completed successfully. Take a lunch and come back to see installation

completed! ☺

Click Next when configuration is done.


You can save the information on text file if you want and click Finish to lauch BI in

a browser.


Once browser comes up with Business Intelligence 11g login screen, you can

validate the installation by login with username weblogic and password

Admin123.

Open QuickStart Dashboard from Dashboard drop down to see sample reports.


Following are URLs for the BI environments that we will use throughout the

workshop.

a. BI Analytics: http://localhost:9704/analytics

b. Enterprise Manager for BI: http://localhost:7001/em ( weblogic /

Admin123 )

EM shows availability of the components and allows for managing BI environment.

Logout once you verified the EM login.

c. Weblogic Server: http://localhost:7001/console/login/LoginForm.jsp

Username / password: weblogic / Admin123


You can manage weblogic domains and security providers for BI through weblogic

server control Administration Console. Logout once you verified domain

information.

d. Program Files -> Oracle Business Intelligence -> Administration Tool

Administration Tool allows for building and managing business model for the

BI environment. We will explore Admin Tool later in a workshop.

Note: This concludes BI11g installations. Since we are using local machine based

installation, before you shutdown your machine, please make sure that you

shutdown BI services gracefully by selecting Stop BI Services from Program Files

-> Oracle Business Intelligence Menu. When you need to use BI environment

again, make sure XE database is running, and then start BI services from menu

again. You have to supply weblogic as user name and Admin123 as a password

during startup and shutdown BI services.


Lab 3: Advanced Reporting, Master-Detail Linking

Goal: Create Analyses, Master-Detail linking and Custom Groups.

Solution: Create an Analyses report with pivot table, bar graph. Format and

modify the compound layout components for master detail linking and add

selection steps for grouping data.

Instructions:

1. Access Oracle Business Intelligence Presentation services from the

browser.

2. Enter the following in the browser http://localhost:9704/analytics

User name: weblogic

Password: Admin123

3. In the Home page, click Analysis in the Create section to navigate to the Analysis Editor

4. In the Select Subject Area pop-up, click Sample Sales Lite. The Sample Sales Lite subject area appears in the Subject Areas pane in the Analysis Editor.

5. Select the following columns from the Sample Sales Lite Subject area.

Double click on each column to appear on the criteria section.

Table Column

Products LOB

Times Per Name Year

Base Facts Revenue

Offices Organization

6. Verify your criteria same as the below image

7. Click on the Results tab visible on top left corner. Remove the Table view

click on the X icon as shown below from the compound layout.


8. Add a pivot view to the compound layout as shown in the below image.

9. Click on the Edit icon (pencil) to modify the pivot table

10. Under the layout section drag LOB from Rows to Column area, Click on the

sum icon on the Rows area and select after to display grand total.


11. Click Done now your compound layout should be similar to the below

image

12. Click on Criteria tab , select the Per Name Year properties and choose

column properties

13. Select the Interaction tab, For Value choose Send Master Detail , Enter

MDY03 for channel


14. Go to Column Format tab, Check Custom Headings and rename the column

to Year click ok.

15. Click on the Results tab, Add a Bar (graph) view to the compound layout

16. Click on Edit icon (pencil) to modify the graph view

17. Under layout section, Drag Year to Sections area and select Display as

slider. Drag the LOB on top of Vary Colors By (Horizontal axis). Your

Layout should be similar to the below image.


18. Click on the graph properties

19. Check zoom options for X and Y axis, enter MDY03 for event channel.

Click OK and click done.


20. Graph view should be similar to the below image

21. Click on the preview icon, to confirm the master-detail events. Click on the

year column from the pivot table and below graph should display the

corresponding year and their results.


22. Expand the Selection Steps section, Add a new step for Organization create

a new group as shown in the below image.

23. Create a Sales group select the first three organization as shown in the

below image and click ok.

24. In step 3 then select Members


25. Select Remove Action and choose the first three organizations and click ok.

26. Create another group Development similar to Steps 22, 23, 24 and 25.

Select the last three organizations in this group. After creating this group

remove the last three organizations, Verify your steps with the below image.

27. Save Your Analysis as Organization Revenue under My Folders.

28. Click on the preview icon the results should be similar to below image. You

should able to zoom in/out on the graph. The other cool feature is you can

change the view of the pivot table from the dashboard no need to edit it from

analyses. Drag the year or Organization column on top of the revenue. The

results are aligned based on your customization.


Lab 4: Deploying SampleApp RPD and Webcat on BI 11g

4.1 Move SampleApp Files

This section will guide you to install SampleApp BI Application. The process

is design to help you understand and experience BI deployment structure and

manageability. We will deploy SampleApp web catalog, rpd and related

configuration files including users migration. Make sure you have shutdown your

BI Services before continue with this step, we will start BI services later in the

section

4.1.1 SampleApp Webcat

Please unzip Student_SampleApp\SampleApp_webcat.zip into following folder:

C:\BI11g\instances\instance1\bifoundation\OracleBIPresentationServicesCompon

ent\coreapplication_obips1\catalog

4.1.2 Sample XML Data Source

Copy Student_SampleApp\Files_To_Copy\SAMP_XML_TARGETS_F.xml into:

C:\BI11g\instances\instance1\bifoundation\OracleBIServerComponent\coreappli

cation_obis1\sample\SampleAppFiles\Data

4.1.3 Resource Files for SampleApp

Copy all the contents within Student_SampleApp\Files_To_Copy\analyticsRes into:

C:\BI11g\instances\instance1\bifoundation\OracleBIPresentationServicesComp

onent\coreapplication_obips1\analyticsRes


AnalyticsRes folder contains all the custom files (custom images, writeback

templates) that are consumed by SampleApp dashboards and reports. If some of

the subfolders already exist within analyticsRes folder on your system, increment

their content with the files from this SampleApp deployment.

4.1.4 Copy currency configuration file

Copy file Student_SampleApp\Files_To_Copy\userpref_currencies.xml into: (override existing file if any)

C:\BI11g\instances\instance1\config\OracleBIPresentationServicesComponent\

coreapplication_obips1

FYI, this file contains currency code setup examples for SampleApp currency showing reports

4.1.5 Copy and Edit Configuration Files

Copy file: NQSConfig.ini file from Student_SampleApp\Files_To_Copy folder into

C:\BI11g\instances\instance1\config\OracleBIServerComponent\coreapplication

_obis1

Open File in notepad and make changes mentioned below, save file and close it:

C:\BI11g\instances\instance1\config\OracleBIPresentationServicesComponent\

coreapplication_obips1\instanceconfig.xml

Make sure to add ‘red’ tags into the file, within the existing <ServerInstance> tag : <ServerInstance> <LightWriteback>true</LightWriteback> ... <ps:Catalog xmlns:ps="oracle.bi.presentation.services/config/v1.1"> <ps:UpgradeAndExit>false</ps:UpgradeAndExit> <ps:UpdateAccountGUIDs>UpdateAndStartNormally</ps:UpdateAccountGUIDs> </ps:Catalog> </ServerInstance>


4.2 Create Database Contents for SampleApp

Open a command window; go to the directory Student_SampleApp\DB_Objects

Type sqlplus to connect to XE database with system privileges:

> sqlplus sys as sysdba

Enter password: Admin123

Run the script BISAMPLE_USER.SQL located in the same directory.

Once executed, verify that you can successfully connect as BISAMPLE user:

> sqlplus BISAMPLE/BISAMPLE

> exit;

From command prompt from the same DB_Objects directory, run following

command:

> imp BISAMPLE/BISAMPLE@XE file=BISAMPLE_10g.dmp FULL=Y

We completed creating schema and data needed for SampleApp BI Application.


4.2 Deploy Resources, Users, RPD and, Webcat

4.2.1 Deploy SampleApp Resources

Note: If you have BI installed in any other directory than C:\BI11g then, open rpd in

offline mode (password: Admin123). Open Manage -> Variables menu and make

sure that repository variable BI_EE_HOME has correct BI install dir. Save the rpd

and close the Admin Tool.

Start BI Services from Program Files -> Oracle Business Intelligence -> Start BI

Services. Provide username weblogic and password Admin123 when prompted.

Login onto http://localhost:7001/console using your Administrator credentials

created during platform install. Navigate to deployments area.

Click on the Install button in the deployments area to install a new web application.

(If Install button is not enabled, click on Lock & Edit on the left frame).


In the Install Application Assistant dialog, provide the path and click next.

C:\BI11g\instances\instance1\bifoundation\OracleBIPresentationServicesCompon

ent\coreapplication_obips1

This should give the option to select analyticsRes as a valid application to deploy.

Select analyticsRes and click on Next.

In the next screen, choose the default option „Install this deployment as an

application and click Next.


Choose bi_server1 as the deployment target and click Next.

In the next screen, under the Source Accessibility section, choose: I will make the

deployment accessible from the following location’.

Click Finish.

In the deployment screen, confirm that the new application called analyticsRes is

now available.

Click on „Activate Changes‟.


Once this completes, start the deployment called „analyticsRes‟.

4.2.2 Import FMW Users

This step is required in order to have most of the SampleApp features to work.

Make sure the FMW import happens properly on your environment. Open the

weblogic Administration console:

http://localhost:7001/console (username/password: weblogic / Admin123)

From home screen, click on Security Realms link.

Then drill on myrealm.


Go to the migration page tab. Go to the Import tab.

In the 'Import Directory on Server', point to the directory

Student_SampleApp\FMW_Users_Definition

For example:- C:\Student_SampleApp\FMW_Users_Definition.

Click on Save

This should import all the SampleApp users into your environment. To confirm it,

click on Users and Groups tab.

Verify that you can see the new users like abell, etc created now. You should be

able to login using any of these users in OBIEE.


4.2.3 Deploying RPD and Webcatalog

Login to the EM administration screen using the url http://localhost:7001/em

Expand 'Business Intelligence' node on the left and choose Coreapplication.

Now click on the Deployment tab on the right. Next, go to the Repository tab.

Click on 'Lock and Edit Configuration' to enable you to change the default settings.

In the 'Upload BI Server Repository' section, click on the Browse button and locate

the SampleApp.rpd from directory Student_SampleApp.

Enter Admin123 as the password (this is the offline password for SampleApp.rpd)

Keep on the same configuration screen to update webcat selection

Change the catalog at the end of the path to SampleApp in a catalog text box:

$ORACLE_INSTANCE/bifoundation/OracleBIPresentationServicesComponent/$C

OMPONENT_NAME/catalog/SampleApp

Click on Apply (top right corner of screen)

Visit the Capacity Management on the same page and uncheck the Cache box.


Click on 'Activate Changes

To restart BI components to take effect of changes we made, Expand 'Business

Intelligence' node on the left and choose Coreapplication.

Click on the Overview Tab,

Click on blue button to Restart under the Manage System category, middle of

screen, Click yes on dialog box to confirm the move. Wait for message that

confirms successful restart.


Following screen shows SampleApp dashboard of index page, this page allows

you to navigate to various reports/dashboard pages to explore more about OBIEE

11g features and its explanation on how to implement such feature.

BI 11g Home page:

BI 11g SampleApp’s Index Dashboard:


4.3 Configuring BI Publisher Data Sources

1. Login to sqlplus as sysdba from a command window and execute following

statement to unlock hr schema that we will use later for BI Publisher Lab.

> sqlplus sys as sysdba # password: Admin123

> alter user hr identified by hr account unlock;

2. Login to BI Analytics as a weblogic user and Click on Administration link on top

right of the page, you will be presented with BI Presentation Server Administration

UI. Click on Manage BI Publisher link within BI Publisher section.


3. Click on JDBC Connection link under Data Sources section.

4. Click on Add Data Source button and enter following information for first data

source that connects to hr schema:

Data source Name: HR Driver Type: Oracle 10g

Replace connect String after @ sign with: localhost:1521:XE

Type in username/password as: hr / hr

Click on Test Connection to verify and then click on Apply button.


5. Lets add one more data source that connects to BISAMPLE schema. Click on

Add Data Source button and enter following information for the data source that

connects to BISAMPLE schema:

Data source Name: BISAMPLE Driver Type: Oracle 10g

Replace connect String after @ sign with: localhost:1521:XE

Type in username/password as: BISAMPLE / BISAMPLE

Click on Test Connection to verify and then click on Apply button.

You can see Publisher Report by visiting Dashboard index 6.1 Published

Reporting:


Lab 5: Building Reports with BI Publisher

5.1 Creating a Data Model

In release 11g, Oracle BI Publisher introduces the Data Model Editor, a graphical

user interface for building data models within the BI Publisher interface. It enables

you to perform the following tasks:

• Create Data Sets - Access data from a wide range of sources: RDBMS,

OLAP, WebServices, BI Analyses, XML files, Excel and others.

• Query data – Build SQL or MDX queries to extract data from relational or

multidimensional (OLAP) data sources.

• Structure data – Define master-detail relationships between data sets to

group data at multiple levels to optimize document generation.

• Aggregate data – Create group level totals and subtotals.

• Customize data – Modify data field names to conform to business terms and

reporting requirements.

• Create calculations – Compute data values that are not stored in the

underlying data sources that are required for reporting.

• Advanced tasks – Define parameters and lists of values (LOV), triggers, and

other advanced elements as required by reports and report users.

In this topic, you are guided to create Data Model based on SQL query using the

JDBC connection you defined in the previous topic. Also, you will learn how to add

parameters and LOVs to the data model.

Defining Default Data Source, and Output Options

5.1.1. Note: Before starting the creation of Data Model, you will first create a folder to save

all your objects such as reports, data models and so on.

In the Home page, click Catalog link under the Browse/Manage section.

In the Catalog page that is displayed, select the My Folders node, click the New

icon on the tool bar.


Select Folder from the drop-down menu to create a new folder.

Enter Learn as the name of the folder, (optionally enter a description), and click

Create.

You can see that the folder gets listed in My Folders.

5.1.2. Now, you can invoke the Data Model editor in one of the following ways-

• From the Home page Create section, select Data Model.

• From the Catalog page, click New icon and select Data Model from the drop-

down menu.


5.1.3. Observe the Data Model Properties page that is displayed on the right.

Select a Default Data Source (that points to the OE schema in your database).

Optionally, add a description to the data model.


Note:

• Ensure that in the XML Output Options, Include Parameter Tags is checked,

and Include Empty Tags for Null Elements and Include Group List Tag check

boxes are not checked.

• For defining the Default Data Source, you can also edit the default demo data

source to point to the HR schema in your Database, and use it.

• Also, note that on the Properties page you can set many options in addition to

specifying a default data source. For example, you can specify DB default

package, and DB fetch size (the data engine’s default is 300), XML output

options based on what XML tags you require in the resulting XML output.

5.1.4. To save the data model with a name, click Save As icon found at the top right corner

of the page.

Navigate to the folder that you want to save the data model object ( Learn in this

example), enter an appropriate name for the Data Model , and click Save.


5.2. Creating a SQL Query Data Set with Parameters and LOVs

1 From the Data Model task pane on the left, select Data Sets.

2 From the Data Set drop-down menu, select SQL query as the type of data set.

3 In the Create Data set -SQL dialog box that appears, enter an appropriate

name for the data set, accept the default data set option (as you have already


defined it in the Data Model), and click Query Builder to create a query.

4 The Query Builder window appears displaying the HR schema objects on the

left side.

Click DEPARTMENTS and EMPLOYEES tables to add them to the Model

canvas on the right.

5 Now, define a join between these two tables as follows:

Click the box beside DEPARTMENT_ID column in the DEPARTMENTS table.

Similarly click the box beside DEPARTMENT_ID column in the EMPLOYEES

table.

(These boxes, when marked for joins turn light gray.)

Also note that a fine line joining the tables appears in the Model canvas.


4.2.

6

Select the following columns from the tables (by selecting the check boxes

beside the column names):

-Select DEPARTMENT_NAME from the DEPARTMENTS table

-Select FIRST_NAME, LAST_NAME, HIRE_DATE, and SALARY columns

from the EMPLOYEES table

(See the screen below)


7

Click the Conditions Tab . Change the column names and remove "_" to have

more appropriate aliases. Change the aliases for the columns as below:

• DEPARTMENT_NAME to DepartmentName

• FIRST_NAME to FirstName

• LAST_NAME to LastName

• HIRE_DATE to HireDate

• SALARY to Salary

8. Also, type "IN (:P_DNAME)" in the Condition field for the department name

column, as shown in the screen below . This will define a parameter

P_DNAME on the department name column.

Note: The IN condition is being used here for the parameter to accept All or


Multiple values for the department name column.

The aliases and display names can be changed in the Data Model page, either

in the Diagram or in the Structure too:

9. Click Results to see how the query results appear in Query Builder.This will

prompt you to enter a department name for the parameter, enter Sales and

click OK.


View results. Click Save to save the query.

10. This takes you back to the Create Data set -SQL dialog box, and observe that

the query you created is reflected in the SQL Query area.

Enter an appropriate name such as Emp_DataSet, and click OK to add this

data set to your data model.


11. A message asks whether you want to create a bind variable. Click OK. to

create the parameter

Note that the parameter created is listed on the left under Parameters node in

Data Model:


Every time you make changes to the Data Model (changes to the data set,

adding parameters or any other changes), click Save icon (found at the top

right corner) to save the Emp_DM data Model .

12. In the Data Model pane, click List of Values node. In the List of Values pane

click the + sign to Create new List of Values.

Select the following Options:

• Enter DepName as the Name of the LOV

• Select SQL Query as the Type from the drop-down list

• Ensure that HR is selected as the Data Source

• Click Query Builder to define the query that returns department names

for the LOV

Save the Data Model.


13. In the Query Builder page, select DEPARTMENT_NAME from the

DEPARTMENTS table. Click Save.

Click Save to save the Data Model. The query for the LOV should look like

this:

14. Now, set the properties for the P_DNAME parameter to use this LOV:

Select P_DNAME parameter listed under the Parameters node.

Enter Default value as * (the character star) , and select Menu as the

Parameter Type (the default parameter type is Text).


15 Ensure that DepName is selected as the List of Values for the parameter.

Ensure that Multiple Selection, Can select all, and All Values Passed

options are selected.

(These options allow you to select all / multiple values for the department.)

Also, change the Display Label to an appropriate one like- Department:

Click Save to save the Data Model.

16 You can edit the query manually further to concatenate Last Name and First

Name columns as Employee Name. Edit the query to look like this:

Select DEPARTMENTS.DEPARTMENT_NAME as "Department Name",


EMPLOYEES.FIRST_NAME || ' '||EMPLOYEES.LAST_NAME as "Employee

Name",

EMPLOYEES.HIRE_DATE as "Hire Date",

EMPLOYEES.SALARY as Salary

from OE.DEPARTMENTS DEPARTMENTS,

OE.EMPLOYEES EMPLOYEES

where DEPARTMENTS.DEPARTMENT_ID=EMPLOYEES.DEPARTMENT_ID

and DEPARTMENTS.DEPARTMENT_ID IN (:P_DNAME)

Click OK and save the data model.

The query should look like this in Query Builder:

Note: Once you edited the query manually, you will not be able to edit in the

Query Builder.


5.3 Viewing the XML Output and Saving the Sample Data

1. You may have observed that In the current version of the BI Publisher, you can preview

the XML data for the data model.

Click the XML icon (found at the right top corner of the page), to see the XML output for

the data model you defined in previous topic.

Note that All is selected for the Department parameter (as this is the default value ).

Select All for the number of rows, and click Run to see the XML data output for all the

departments:

( A portion of the XML data is displayed here in the screen)


2. To save this as sample data, click the Open Menu drop-down list icon, and select Save

as Sample Data.

You can see that the sample.xml is listed in the Sample Data section of the Data Model

( as shown below):

Note: It is very important to save sample data for a data model, else when creating

Layouts, the previews do not appear correctly.

You can select various values (multiple/ single) for Department to see the data. You can


also restrict the number of rows that you want to see:


5.4 Creating a Report

In this topic, you learn to create a report using the Data Model that you defined in

the previous topic. You also create a simple layout using Layout Editor, and edit

parameters.

Note: The steps in this topic are all continuous, so do not close any window or

logout from BI Publisher unless you are asked to do so.

1. If not logged in, login into BI Publisher, and go to My Folders> Learn folder in Catalog

pane. Click New (found at the top left corner of page) to see the drop-down menu and

click Report to create a new report.

2. Navigate to My Folders> Learn. Select Emp_DM data model and click Open.


It displays the options to create, use a shared template, or upload a report layout.

Select Blank (Portrait) under the Basic Templates section to create a simple report

layout.


Note: You can define a Layout for the report at the time of creating report, or create it at

a later point of time. Creating report layouts using the Layout Editor is covered in

detail in the next topic.

3. This opens Layout Editor, with a blank page:


4. Click Insert > Data Table from the menu to insert a data table in the layout page.

5. Select and drag the elements in the following order from the Data Source pane to the

[Drop a Data Item Here] area of the table:


• DEPARTMENT_NAME

• EMP_NAME

• HIRE DATE

• SALARY

As you drag an element, position each successive item directly to the right of the

previous element and release to create each column, so that the data table, after

adding all the columns it looks like this:


6. Click the Save icon found on the top right corner to save this layout.

Enter Default Layout as the name of the layout, and click Save.


7. This brings you back to the Layout Editor.

Click the Preview icon drop-down list (highlighted in the screen) to preview the data in

Interactive format (this is also the default format).

Note: You can perform sorts and selection interactively here. Close the Interactive

Viewer.


8. .Click Return in Layout Editor to return to the Edit mode of the report.

Observe the various links on this page that will help you edit the data model,

parameters, properties, and layouts for the report. You can also display the layouts as

Thumbnails or as a List.

Note: Parameters link is displayed, only when the data model you selected for a report

has parameters defined.

9. Click Save icon (found at the top right corner of the page) to save the report. (You can

also click Save As icon ).

Navigate to My Folders> Learn. Enter Employee Salaries By Department as the

report name and click Save.


10 Click the Parameters link and it shows the details for the parameters. You can make

changes to the parameter values. Also, you can opt to show or not to show the

parameter in the report.

Clear the Show check box not to show the parameter.

Note: The report parameters are edited here not to show them in layouts. (This is not

mandatory, but this is being done here to depict all the data in the layout graphs that

you will create in the next topic.)


5.5 Building a Layout Using Layout Editor

Release 11g of Oracle BI Publisher introduces an online Layout Editor, a design

tool to create and publish report layouts from within BI Publisher interface. The

Layout Editor provides an intuitive drag-and-drop interface for adding common

components to your report layout from within your web browser. The Layout Editor

uses the sample data in the data model to immediately populate your layout

components with report data during design-time. It also provides many advanced

features to fully customize your reports.

The Layout Editor provides a new Interactive output type in addition to output types

like PDF, RTF, Excel, PowerPoint, and HTML. Interactive output enables

lightweight interactions with the report data from within the browser such as Excel-

like filtering and sorting of tabular data with fixed headers and footers

In this topic, you will design a layout with graphs and using the Layout Editor, for

the Employee Salaries by Department report that you created in the previous

topic.

Launching the Layout Editor and Adding a Grid Layout

1. Click Return to return to the Edit mode of the report. (If the report

Now, click Add New Layout link to create a new layout for the report.


2. The Layout Editor is invoked, and displays the available Basic and Shared templates

(templates for report layouts) to choose from.

It displays the options to create, use a shared template, or upload a report layout.

Click Blank (Portrait) from the Basic Templates section to manually design the layout.


Note: It is a good practice to use Pre-built templates for reports because:

• They encourage consistent look and feel for layouts

• They make report authors much more productive by taking care comment of

elements like headers and footers.

However, here a blank portrait is being used to show how to create a template from

scratch.

3. This shows blank page (portrait) in Layout Editor . Click Layout Grid to insert a grid.

In the Insert a Layout Grid screen, enter 2 as the number of rows and 2 as the number

of columns for the grid. Click OK.


(You will add various report components such as charts, tables, and pivot tables in the

grid rows and columns in the next subtopics.)

Note: Using Layout Grids to control pixel perfect placement of objects in the layout is a

best practice.

4. The layout grid with 2 rows and 2 columns is added:

Now, merge the cells in the second row. Use <CTRL> key to select both the cells in the

second row of the grid, (when the cells are selected, the color turns yellow) click Join

Selected Cells option available in the Layout Grid menu.


5.6 Working with Charts

1. Select the first cell in the first row of the grid layout, and click Chart from the Insert menu in

the ribbon.

This action inserts a chart template with prompts as shown below:


2. From the Data Source pane on the left-

• Select and drag the SALARY element to the Drop Value Here prompt in the empty

chart

• Next, select and drag the DEPARTMENT_NAME element to the Drop Series Here

prompt.


This would populate the chart with some sample data.


3. Next, change the Properties of the SALARY element in the chart to average the value.

• To do this, first select the SALARY item in the chart, then click the drop-down menu

next to Summation under Formula, and select Average from the drop-down menu.

The chart changes to reflect the average salaries by department.

You can leave the default style for the chart or select from the supported chart styles.


To add a title for the chart, first, Add a row above this chart in the grid layout. Select any of

the grid cells in the first row, and click the Add a Row Above icon.

Now select the cell right above the chart, and click Insert tab , and then click Text Item.


4. Next, add a title for the chart.

Double click the text item to select it, then click again to edit it.

• Delete the default text and type Average Salary by Department as the Title.

• Select the text and choose Tahoma as font, 12 as the font size, and Bold as the

style.

• Center the title by clicking the Center Alignment icon.

5. Click Save As icon in the toolbar, and save the Layout as Departmental Salaries.


6

Return to complete the layout in the Layout Editor.

Now, select the cell beside the bar chart that you have inserted. Follow the above steps to

insert a Pie chart.

The steps are briefly listed here:

1. Select the cell beside the chart that you have inserted, and then click the Insert tab.

Click Chart.

2. To change the chart type to a pie chart, expand the Chart Types menu.

3. Select the Pie chart to insert an empty pie chart with prompts in the grid cell.

4. To populate your pie chart: From the Data Source pane, select and drag SALARY

to Drop Value Here, and select and drag DEPARTMENT_NAME to Drop Series

Here

5. Add Percentage Salary by Department as the Title.

6. Use Tahoma as the font, 12 as the size, and Bold as the style. Align the title in the

center.

7. Click Preview icon, and select PDF from the drop down list to preview your chart :


The preview should now look like this:

Click Save icon in the toolbar to save the changes to the Departmental Salaries layout.

5.7 Adding a Data Table to the Layout

1. Select the lower row (which has the cells merged).

Click Insert tab. Click Data Table to insert the data table component into the layout.


2. Select and drag the elements in the following order from the Data Source pane to the

[Drop a Data Item Here] area of the table:

• DEPARTMENT_NAME

• EMP_NAME

• HIRE DATE

• SALARY

As you drag an element, position each successive item directly to the right of the

previous element and release to create each column. After you insert all the columns

notice that a Grand Total row is automatically inserted .


3. Click Preview in PDF icon in the toolbar to preview the layout -

Click Save to save the Departmental Salaries layout.

Note: You can edit the layout that is saved in one of the following ways:

1. Click Actions and select Edit Layout in the view mode of the report

or

2. Open the report in Edit mode (click Edit link below the report in catalog page)

and select the layout to be edited in the Layout section, and click Edit (pencil

icon)


Lab 6: Working with Action Framework

6.1 Creating an Action Metadata

In this section we will create new Action to navigate to external web content that

can be accessed by clicking on action link from a report. Also, later we will create

another action on the fly to navigate to a detail report from a master report. These

are the example and process to develop actions and attaching an action to a

report. Similarly, once can create action to invoke web service and/or attach similar

action to report or scorecard, etc. Once you have full scale sample app installed as

mentioned on OTN, you can experiment with more options for an action.

1. Click on New -> Action link under Actionable Intelligence from BI Menu.

2. Action selection criteria shows options for action type, Navigation or Invoke.

Select Navigate to a Web Page action.


3. Enter following information for web navigation action:

Enter http://finance.yahoo.com/q?s=orcl as a URL and clicking in Define Parameter.

Edit Parameter Name and Prompt to ‘Ticker’. You can add ORCL or any other

ticker as a default value or leave it blank. Optionally, one can pass analysis column

value as a parameter for action. In our example, we do not really have ticker as

analysis column, therefore we will keep it available for user to enter the value.

4. Create folder called PTS Lab under shared folder to save the action.

5. Give action a name ‘Get Stock Price’ and click Ok.


6. Now we will add action to an existing report. Open dashboard from index page

and go to Brand Analysis dashboard of index 2.1

7. Edit Dashboard page by clicking on Edit menu from dashboard properties link.

We will add newly created action to Product Line Analysis report.


8. Mouse over the top right corner for the report container (Master Detail 1) in the

middle column to see edit report menu item and click on Edit Analysis.

9. Once Report editor – Analysis Design windows opens, select criteria tab.

Criteria Tab shown here:


10. Click onColumn Properties of Product to add action to it.

11. Select Interaction Tab and select Drill+Action Link.

12. Select green + icon to add action.


13. Click on second icon of folder to open catalog and select action we just

created.

14. Click Ok to save action with report.

15. Save the report and go back to index 2.1 Prodcut Analysis dashboard.

16. Click on Total Product values. Click on Get Stock Price Action link.


17. Enter valid ticker of your choice and click execute to see the action!

6.2 Creating an Conditional Action for a Report Navigation

In this section, we will create another navigation action that helps user to navigate

to a detail reprort. This action can be conditional if needed. We will add condition

to check on revenue, if revenue is below certain number, we can guide user to

navigate to detail report for further analysis.

1. From Brand Analysis Dashboard page, go to Edit dashboard page and edit

Report of middle column – Master Detail 1. Go to Criteria tab and select column

properties for Reveny Analysis column. Select Interaction Tab which shows

previsously added Action to the colmn.

Now we will add one more action here.


2. Click on first Green ‘+’ sign to add new action to the column. Once New Action

Link box comes up, select first icon for Action field to create new action instead of

selecting any existing from a catalog. Pick Navigate to BI Content option.

3. Now, from catalog browser, select the Prodcut Detail report within Shared

Folder\2. Functional Examples\Dashboards\2.1Simple Demo Dashboard\

Click ok.


4. We will condition for this action to be visible. Lets select Conditional radio

button, which will enable editing block for condition.

5. Select filter icon to add condition on Revenue Analysis column of the report.

Notice that either you can select colmn from existing report or you can even select

any other column from available subject area folders. We will select Revenue

Analysis for our condition.

Add condtion Operator to ‘is less than’ and Value to 5000000 , i.e. Action will be

shown only if revenue is less than 5 Million. Click Ok (3) to go back to Criteria.


6. Make sure to save the report. Now, visit the Brand Analysis dashboard again

and click on different revenue column to see conditional action in action!

Clicking on revenue number more than 5M, Newly created action is not visible:

Clicking on revenue number less than 5M, navigate to detail action is visible:


Once clicked on Prodcut Detail action link, Product Detail dashboard is shown for

furhter analysis. Notice that the conditional action we created is attached to the

report and not saved as action by itself in catalog. Therfore, creating action as

metadata and saving it as a catalog object allows you to leverage same action in

multiple reprots instead of creating one at report level. There cold always be a

reason and need for specific reprot related action during ad-hock reporting and that

sould be served in a similar way we created action here.

Also, while you are on Product detail dashboard, also notice different prompt

options on the left colmn that talks about few more new features about how to set

dynamic column display and langage selector.


6.3 Scheduling an Agent

Agent in a Action Framework is very much same as iBot creation in a 10g

environment. Agent allows one to schedule report refresh or delivery or more

generically, creating alert condition. Agent can also be used to execute an action

when alert condition occurs or at predefined schedule. We will create simple agent

to generate alert condition and look at the details and report that alert delivers.

1. From New menu, select Agent under Actionable Intelligence category.

Selecting Action takes you to BI Delivers component to define agent’s parameters:

On a General tab, select Priority to Normal and Run As to Specified User to

weblogic. This could be a default setting based on logged in user.


2. Lets set the alert’s schedule. Select Schedule Tab.

Make sure Enabled is checked; this setting allows you to enable or disable

particular agent as required. Set Frequency to Once for our lab purpose and set

Start time to about 7 to 10 min after the current time on your system so that once

we are done with agent definition, we can see the alert. Notice that you can select

frequency of occurance and set the end time as well, if needed. Also, notice the

enhacement on selecting a acalendar type by clicking on Default icon near the

start filed; this allows for selecting different time zone for scheduling purpose

instead of relying on BI server or Database server time only.

3. Select condition tab. Similar to conditional action, agent can also check

condition on a given fequency to send alert or take an action. We will create

unconditional agent at this time, make sure to select Do not use condition.


4. On Delivery Content tab, Select Content as Analysis. Click on Browse button to

select a report that agent will deliver as a part of alert condition. Select

/Shared Folders/3. Analysis and Dashboards/Answers Queries/Order Status

Also, select Fromat to PDF and Delivery as ‘Delivery as attachement.’


5. Leave default Recipients to weblogic user. This tab also allows you to enable an

agent to be available for other to subscribe to and if subscriber can customize

agent. Leave the defailt settgin for this tab and click on Destination Tab.

6. Select Home Page and Dashboard as a User Destination criteria on Destination

Tab. This will send alert notification on user’s dashboard/homepage. Uncheck the

option for Devices since we will not send any notification toany devices for the

workshop lab. Each user can enter the devices information and also can define the

default device for him/her to receive notifications. Alert can also be sent to

predefined devices based on priority. One can also select System Services check

box to refresh the cach for a report if needed.


7. Visit Actions tab. We will not any action to our agent. But, optionally, one can

add action to be executed when agent execution completes. Also, this tab allows to

define different set of actions to be executed for conditional agent. Leave the tab

with defaults and click on Save buttin to save the definition of an agent.

8. Save the agent in PTS Lab folder and give it a name Order Status Agent.


9. Once alert agent is executed based on start time you defined, you will see alert

notification on Home page and Dashboard.

Clicking on Alert shows, details as well as attachment we defined during the agent

creation.


Lab 7: Building Advanced Hierarchies with Admin Tool

Business Intelligence Administration Tool is the modeling tool for the data

modeling to support BI presentation services. This lab will help you gain hands on

experience with building Business Model and Presentation Catalog. We will

expand the modeling exercise to practice on how to build hierarchies, specifically a

new feature in BI11g of out of the box support for skipped level and ragged

hierarchies. Lab instruction will guide you to build business model off the existing

physical layer objects, which then will be use to build a report and reflect on how

modeling impacts the reports.

7.1 Working with Business Model and Mapping Layer

Since our BI services are running, we will open repository file in a online mode.

Once we make changes to existing repository and build a new business mode, we

can refresh the metadata in a presentation service to start using changes to build

new reports.

1. Open BI Administration from Program File -> Oracle Business Intelligence

menu. Either select File -> Open -> Online or from tool bar click on ‘blue’ folder

icon to open repository file in a online mode.

Enter Repository password and weblogic user password as: Admin123. Because

we are it is online editing, server administration user name and password are also

required. For offline editing, you would only need repository password.


2. Once login, you will see the existing modeling information:

3. Right click on white space in a Business Model and Mapping layer to create a

new model. Give it a name PTS Workshop and click OK.


4. We will leverage existing data sources to build new mode. Expand Sample App

Data and its Catalog in a Physical layer to see BISAMPLE schema tables. Drag

and drop D10 Product (Dynamic Table) and D20 Product (Ragged Hier) from

BISAMPLE schema on physical layer to PTS Workshop Business Model. Make

sure to drop the table on top of PTS Workshop model.

Also drag and drop D60 Customer Table as well from BISAMPLE physical layer to

PTS Workshop model in BMM Layer.


5. Right Click on PTS Workshop Business Model and select New Object -> New

Logical Table. Give it a Name Facts and Click OK.

Drag and drop F10 Billed Rev -> Revenue column from physucal layer onto Facts

logical table we just created.


6. Double click on Revenue Logical column that we just added to set the

aggregation rule to Sum and click OK, so that if there are any hierarchies, BI

server can do aggregation as one looks at revenue data at any higher level than

most detail level. Aggregation rule allows you to define different option on how you

want particular fac to be roll up in a hierarchies (exmpl: sum, avg, min, max, etc).

7. Right click on PTS Workshop Business Model and select Business Model

Diagram to create a star schema of our logical data model.

Rearrange the tables in a diagram window so that all the objects are easily visible.

Click on join icon from a tool bar to create a join between dimensional tables

and fact table. Click onjoin icon and then click on Customer table first, then click on

Fact table. Click OK when relationship window pops up. Repeat this for both the

product dimenstion table as well.


Final Diagram should be similar to as shown below:

Close the diagram window to go back to three layer designer mode.


8. Drag and drop the PTS Workshop Business Model folder onto Presentation

layer, which will create a presentation subject area based on the model we have.

Click Save for our changes to be saved. Click on Yes, if prompted for consistency

check. BI Administration tool allows for constancy check to makes sure there are

no errors in logical diagrams.

If error or warning found, Tool will display a check report and may suggest best

practice to solve some of the issues. Click OK on check report completion box. If

you see any error or warning, you should fix it before moving on.


7.2 Enhancing Business Model and Building Hierarchies

So far we imported objects directly from physical layers and build a small star

model in BMM layer. In this section we will be enhancing BMM objects for PTS

Workshop Model by renaming few objects and creating hierarchies.

1. BI Administrator Tool provides renaming wizard to make changes to large set of

objects at once. If you need to rename few objects in BMM layer then, we can

simply edit the particular object.

Right click on following objects one by one and select option to rename. Change

the name as mentioned here for both the logical Product tables:

Product Desc -> Product and Type -> Product Type

Your screen should look as below with changes in highlighted:


2. To build a generic hierarchy on Level Based Table, right click on Product

(Dynamic Table) and select the option Create Logical Dimension -> Dimension

with Level Based Hierarchy. Tool will generate default hierarchies with auto

generated total and detail levels. It will also add all the column of the table into

hierarchy, which we will modify soon to make right set of column at different levels.

3. We want to build Product Hierarchy with following levles.

Total -> Brand -> LOB -> Type -> Product

Right click on Total level and select New Object -> Child Level

Type Name as Product Brand and Number of elements at this level to 4. Click OK.

Drag/Drop Brand and Brand Key columns from total level onto Brand level.


We will create rest of levels in the same way with following information:

+ Right click on Product Brand level and select New Object -> Child Level

Name: LOB Number of elements at this level: 6

Drag/Drop LOB and LOB_Key from Total level onto LOB level

+ Right click on LOB level and select New Object -> Child Level

Name: Product Type Number of elements at this level: 9

Drag/Drop on Product Type and Type_Key from Total onto Product Type level.

+ Double click on Product Type level and click on green ‘+’ to add child level since

detail level was automaticaly already created.

Select Detail level already exists and click OK.


+ Double click on Detail level and verify the settings:

Name: Products Number of elements at this level: 12

Drag/Drop Product and Product_key columns from Total onto Products level.

+ Delete Attributes and Sequence columns from detail level and delete all the

columns from Total level.

+ Right click on each of following one by one for the levels to have key defined. We

need to provide information about the key that BI server wil lleverage to drill down

to next level. We already have the key columns at each level, we just need to

make it visible for the hierarchy levels.

- Right click on Brand_Key and Select it as New Logical Key Column

- Right click on LOB_Key and Select it as New Logical Key Column

- Right click on Type_Key and Select it as New Logical Key Column

- Double click on Detail level and unde key tab, select both the column.

Your screen should look like as shown below for newly created Hierarchy.


4. Now we will create new hierarchy very similarly but, for un-balanced data set.

Right click on Product (Ragged Hier) and select Create Logical Dimension ->

Dimension with Level Based Hierarchy


Administration Tool created new hierarchy similar to the one we created before.

Double click on new hierarchy for Product (Ragged Hir). Enable check box for

Ragged and Skipped Levels for this hierarchy. The data in the physical table have

missing level data or some leaf nodes are not at the same level as others, making

the data set a un-balanced hierachical data. In previsouse version of BI, we

needed to create mapping table and lot more work to be able display un-blanaced

hierarchy. With 11g, its is just matter of setting the properties for such hierachy and

BI server will manage the data set for displaying properly.

5. Repeat the steps for creating levels for Brand, LOB, Type and Products, similar

to what we did for the previsouse hierarchy. In this hierarchy, we do not have other

than key column for lab purpose but, you could have more column to be displayed

by hierarchy rather than key values. But, for now, this will be fine to inderstand

hierarchy. Once you have created both the hierarchy, the screen will look like as

shown below:


6. Save all your changes, if asked Click Yes on consistency check. If check comes

back with any error or warning, please fix that before moving forward.

7. Drag and drop newly created hierarchies from BMM layer to corrosponding

presentation layer table for PTS Workshop subject area:

Save your work. You can close the administration tool now. We will create two

different reports showing both the hierarchies to see the result and effect of our

modeling.


7.3 Building Reports based on Hierarchies

1. Log into BI Analytics web interface using weblogic username, password

Admin1213. From Home page view, click on New menu and select Analysis:

2. Select PTS Workshop subject area to bulid a new report off the model we just

created:


3. Analysis design pane will be shown with Subject are onjects we created in

presentation layer within admin tool. If you do not see PTS Workshop subject area

or any of the object within it, you can refresh server meta data by clicking on

refresh icon shown below:

4. Lets first create a report based on generic/banalced hierarchy. Drag and drop

columns from Product (Dynamic Table) -> Hierarchy and Revenue from Facts on

to right side for selected columns view. Click on Result tab to verify the data.


5. You can edit the Title field if you like and then save the report in a PTS Lab

folder, give it a name: Level Hierarchy Example

6. Lets create one more report based on ragged hierarchy. Select New Analysis

again and select PTS Workshop subject area. This time select hierarchical column

from Product (Ragged Hier) and revenue from fatcs table.

7. Clcik on Result tab to verify the data. Change the Title if you like to and then

save the report under PTS Lab folder with name: Ragged Hierarchy Example


8. You can place both the report on a sashboard side by side to see the difference.

Lets place both the reports on My Dashboard. Click on a Dashbaords menu and

select My Dashboard.

9. Click on Edit Dashboard link to design the My Dashboard page.

You can drag and drop both the reports on to dashabord page or can create new

column on dashboard page to display report side by side.


10. Save the dashboard and click Run icon to see the reports.

You can expand different levels for both the reports to see displaying of

level/balanced or unbalanced hierarchies.


Lab 8: Creating KPIs and Scorecards

8.1 Creating KPIs

A KPI represents the result of a business measure (Product Sales or Operational

Costs, for example) evaluated against a target for that measure. You can use KPIs

to assess the performance of the strategic objectives and initiatives that form levels

of organizational strategy. Although the actual and target values for KPIs may exist

in the database referenced by the subject area you can also calculate these values

using formulas or by entering a literal value.

KPIs have these characteristics:

• Have measurable values that usually vary with time

• Can be evaluated using targets to quickly determine a score and performance

status

• Can be compared over time for trending purposes that enable you to identify

performance patterns

To create a KPI:

1. On the global header, select New and then KPI. In Oracle Scorecard, click the

New icon in the "Scorecard Documents pane" or the "Catalog pane" and then

select KPI.


2. Select the SampleSales subject area. The KPI wizard appears.

Select the Actual and Target Values:

a. In the Actual Value drop-down list, click on the function (fx) icon an copy the

following expression

FILTER("Base Facts"."1- Revenue" USING ("Time"."T05 Per Name

Year" > '2007'))

b. In the Target Value drop-down list, click on the function (fx) icon an copy the

following expression

FILTER(avg(ago("Base Facts"."1- Revenue",1)) USING ("Time"."T05

Per Name Year" > '2007'))

c. Select the Enable trending option. The Compare to prior and Tolerance fields

allow you to compare KPIs for prior periods and indicate an increase or decrease

in performance.

d. In the Compare to prior drop-down list, expand the Time folder and select

"Time"."T02 Per Name Month" to compare with the prior month.

e. Enter 2 in the Tolerance field and select % Change from the drop-down list. The

tolerance can be entered as a percent or an actual number that represents a

difference (change) from the prior period. In this case, a tolerance of 1% is

acceptable before the value is considered to be worsening or improving.

f. The General Properties pane should look like this:

Click Next.


3. The Dimensionality pane allows you to aggregate KPI values, target values, and ranges, using dimensions in the subject area. You can set specific values which essentially filter the results, a process called pinning. For this exercise, all dimensions should have the “Not Pinned” value.

Add the following dimension as per the below image.

Click Next to Threshold Screen.

4. The Thresholds pane allows you to indicate whether high, low, or target values are desirable; to specify ranges and status symbols for performance; to create or assign actions based on KPI status, and so on.

a. The Goal section provides a drop-down list that indicates a specific level of performance. Ensure that the defaults, High Values are Desirable, are selected.

b. The Threshold section allows you to specify numerical values or measures that separate performance levels. In the first drop-down list, which is associated with the WARNING status, select the function image fx icon and copy the following expression

(Ago( FILTER("Base Facts"."1- Revenue" USING ("Time"."T05 Per Name Year" > '2007')) , 1 ) * 1.05 ) * 0.90

c. In the second drop-down list, which is associated with the CRITICAL status, select the function image fx icon and copy the following expression (Ago( FILTER("Base Facts"."1- Revenue" USING ("Time"."T05 Per Name Year" > '2007')) , 1 ) * 1.05 ) * 0.80


d. Your thresholds should be like below:

4. Click the Finish button and save the KPI as Revenue KPI under my folders.

Open the KPI from the home page to see the results. Your results should be

similar to below image.

4. .Save the KPI in PTS Lab Folder with the name: Sample KPI 1.


8.2 Create a Watchlist

Create a KPI watchlist to view and investigate the performance of KPIs that may

not have been created in Oracle Scorecard, to measure the progress of strategic

objectives or initiatives. Add KPIs to the watchlist by dragging and dropping them

from these locations in Oracle Scorecard:

• The "Catalog pane"

• The "Scorecard Documents pane"

• The "Strategy Tree pane" or "Initiatives pane"

The data is displayed for each KPI in the watchlist is an immediate reflection of its

performance. This includes:

• Current status

• Trend status indicating if performance has increased, decreased, or remained the

same, and if any changes are desirable

• Current and target values

• Variance value and percent by which the current value deviates from the target.

• Change value and percent identify how much the current value differs from that

from the previous period.

The number of KPIs in each performance range you defined is also summarized in

the header of the watchlist.


To create a watchlist :

1. From the global header, select New and then KPI Watchlist , or In the

"Scorecard Documents pane", click the new icon, and select KPI Watchlist.

The "My KPI Watchlist Performance tab" will display.

2. Select the KPIs to add to the watchlist from the pane on the left and drag them

to the Label column. This will display the "Add KPI dialog", change the KPI name

displayed on the watchlist (optional) or specify the dimension values to use for

KPIs that are dimensioned. Add KPI just created from PTS Lab folder.


3. Hit the Save button and Name your watchlist, save it under PT Lab folder.

Your watchlist will now be saved in the OBIEE Catalog and be available to drag

onto any dashboard page.


8.3 Create a Strategy Tree

Strategy Tree Views provide an alternative, detailed view of complex strategic

objectives that require tiers of lower level child objectives. Strategy Tree Views

enable you to hone your focus on multi-layered strategy objectives.

For example, if a high-level strategic objective for "Decrease Support Turnaround"

is one of fifty in a Strategy Tree, use the Strategy Tree Map of his objective to only

view the strategic and performance details of this objective and its supporting child

objectives, such as "% Repeat Incidents", " % Incidents Closed < 2 days", and " %

Incidents Closed < 7 days" as shown in the following figure.

To create a Strategy Tree View:

1. Open an appropriate scorecard. You can find the sample Scorecard by typing

Sample Scorecard in the Home Page’s search dialog. From the home page select

Edit Scorecard. This will bring up the Sample Scorecard in the Scorecard

application.

2. In the "Strategy Tree pane" on the left, select any of the high-level strategic

objective and :

a. Click the View Strategy Tree button in the pane, or

b. Right-click and select View Strategy Tree.

8.4 Create a Strategy Map

Strategy maps provide an alternate view that you can print of the hierarchies of

strategic objectives that are required to achieve your corporate, or domain-level

strategy. Strategic objectives are aligned by perspective and any correlations and

cause and effect relationships between objectives are shown.

Strategy maps display the following information for strategic objectives or KPIs

where appropriate:

• Overall performance state indicated by the symbols used to represent

performance.

• Performance trend, indicating if performance has improved, remained the same,

or declined

• Actual value as a number and percent

• Trend graphs depicting previous performance levels


To create a Strategy Map:

1. Open an appropriate scorecard. You can find the sample Scorecard by typing

Sample Scorecard in the Home Page’s search dialog. From the home page select

Edit Scorecard. This will bring up the Sample Scorecard in the Scorecard

application (in edit mode).

2. In the "Scorecard Documents pane", click the New button and select Strategy

Map. The Scorecard editor: Diagram tab opens, displaying the provided or custom

perspectives that you are using to align strategic objectives and initiatives.


3. Build the map as follows:

• Add strategic objectives or KPIs by dragging them from the "Strategy Tree

pane" and dropping them in the appropriate perspective.

• Move objectives to form the required structure.

• To connect strategic objectives with other objectives, lower-level child

objectives, or objectives from other Strategy Trees by right-clicking and

selecting Add Related Objectives.

• To indicate dependencies and correlations between objectives, click the

Casual Linkage button, select one of the objectives, and then the second

objective. These objectives will display on the Cause and Effect map.

• To delete an objective from the map, right-click the objective and select

Remove Objective From Strategy Map


8.5 Create Cause and Effect Map

Cause and Effect diagrams enable you to illustrate how perspectives and strategic

objectives interact to support your corporate strategy. Create Cause and Effect

diagrams to provide a holistic and dynamic view of your corporate strategy and

depict causal relationships to better understand the implications of future strategic

changes.

To create a Cause and Effect diagram: 1. In the "Scorecard Documents pane", click the New icon on the header and select Cause and Effect diagram. The Scorecard editor: Diagram tab is displayed.


2. Build the map as follows:

• Drag KPIs and/or Objectives from the strategy Pane on the left onto the

empty canvas.

• Drag KPIs/Objectives from the from the strategy Pane, and drop them onto

specific objectives already displayed on the diagram. This will define a

cause/effect linkage between the Drag source and drop target.

• You can also create cause/effect linkages on the Strategy Map diagram

defined early in this document.


3. Optionally click Details to specify a map owner, summarize the purpose of the

map, and attach related documents.

4. Save or name and save the map.


8.6 Create a Custom View

Custom View enable you to present varying degrees of information about key

strategic objectives, core performance indicators, and important initiatives on a

background of your choice, such a company logo, an interactive Flash application,

or a custom diagram. Create custom views to define highly personalized

presentations of business and strategy data.

To create a Custom View diagram:

1. 1.In the "Scorecard Documents pane" select Objects, and then Custom View.


2. Specify the background. You can use a custom image, such as your corporate

logo, or an SWF application.

3. Drag and drop strategic objectives, KPIs, or initiatives from the "Strategy Tree

pane", "Initiatives pane", or "Catalog pane" to the page.

• Take objects off the view by right-clicking and selecting Remove.

• Modify objects on the fly by right-clicking and selecting the View\Edit

Objective option.

• Place strategic objectives connected or pertaining to those on the view by

right-clicking and selecting Add Related Objectives.


4. Specify the type of information to display for each object by selecting it and any

option from the Name list:

• Name - Full name

• Status - Current performance status or level (Warning, for example)

• Trend -If performance has improved, declined, or remained the same

• Actual value - Current value

• Target value - The desired value

• Variance - Value by which the actual value differs from the target

• % Variance - Percent by which the actual value differs from the target

• Change -Value by which the actual value differs from that in a previous

period

• Change% -Percent by which the actual value differs from that in a previous

period

5. Click the Properties icon and browse to the image or the application file on the

"Custom View Properties dialog" that displays.


6. Save and Name the view.


Lab 9: Working with XML API

9.1 Introduction

Oracle Business Intelligence 11g provides utilities to create a generic, XML-based

representation of the Oracle BI repository metadata, using the Oracle BI Server

XML API. This XML file version of the repository can be used to programmatically

modify the metadata. The Oracle BI Server XML API can be used for metadata

migration, programmatic metadata generation and manipulation, metadata

patching, and other functions.

It enables you to perform the following tasks:

• Migration Changes – Automatically apply changes to connection pools and

other parameters so an RPD can be migrated to another environment.

• Create XML Patch – Compare an RPD to a prior version, and generate an

XML patch of the differences.

• Apply XML Patch – Apply an XML file containing a patch to an offline RPD, or

to a repository in a running BI Server.

• Extract XML metadata – Generate all the metadata from an RPD into an XML

file. You can then use any program or manual editing to alter the RPD or

extract metadata from it.

• Execute XML to create RPD – You can execute an XML file that represents an

entire model, in order to generate an entire RPD.

• Execute XML to alter an RPD – You can execute an XML file containing a

finite list of metadata objects to a binary RPD. This is executed the same way

as patching. Objects can be added, deleted or overwritten.

Prerequisites

1. Installed and working 11g Administration Tool.

2. To follow the optional steps to test the command line interface, you also

need a running BI Server dedicated to this exercise. You will change the

running RPD, so you cannot share this BI Server with other users.

3. SampleApp_test.rpd saved in your Repository folder:

<home>\instances\instance1\bifoundation\OracleBIServerComponent\corea

pplication_obis1\repository

4. This exercise assumes you are comfortable with developing in the

Administration Tool and testing your work in a BI stack sandbox. It only

describes new 11g steps in detail.


9.2 Connection Pool Replacement Overview

To use the XML patch technique to alter connection pools for migration from

test to production:

This sample will start with a test RPD, and create an automated process for

modifying it for production. You will then apply the connection pool change as a

patch.

The connection parameters for the two connection pools in the Sample Application

before and after the change are as follows:

Environment

Relational Source XML Source

Data

Source

Name Userid Password Data Source Name

Test orcl_test user_test password_test VALUEOF(BI_EE_HOME)/XML_data/Forecast

Production orcl_prod user_prod password_prod VALUEOF(BI_EE_HOME)/fcast_data/Forecast


9.3 Generating the Connection Pool Replacement Patch

To create the patch you will apply to test RPDs before migration to

production:

1. From within the Admin Tool, open the test RPD, SampleApp_Test.rpd. Use

“welcome1” as the repository password throughout this exercise.

[WARNING: due to the new multi-instance infrastructure in 11g, you can no

longer double-click the RPD file. If you do, the administration tool will open,

but the environment variables for your instance will not be applied, so many

features relating to the file system and other processes will fail.]

2. Open the first connection pool that needs to be changed, “1 – Sample App

Data”.“SampleApp_Relational”.


3. Alter the connection pool to the Production parameters.

a. Data Source Name = orcl_prod

b. User name = user_prod

c. Password = password_prod

4. Open the second connection pool that needs to be changed, “1 – Sample

App Xml Data”.“SampleApp_XML”:


5. Alter the connection pool to the Production parameters:

Data Source Name = “Valueof(BI_EE_HOME)/fcast_data/Forecast


6. Save the RPD as a new name, SampleApp_alt_cp.rpd.

7. File > Compare


8. Select the original SampleApp_test.rpd as the RPD to compare to your

open, modified SampleApp_alt_cp.rpd


9. Note the two connection pools you changed are listed. Click “Create

Patch…” to create an XML file containing the alterations to the connection

pools. Save it in the Repository directory as

“SampleApp_patch_conpool_to_prod.xml”.


10. The generated XML is as follows:

<?xml version="1.0" encoding="UTF-8" ?>

<Repository xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<DECLARE>

<ConnectionPool name="SampleApp_XML"

parentName=""1 - Sample App Xml Data""

parentId="3023:84" parentUid="2160747198"

id="3029:1158" uid="2160747199"

password="D7EDED84BC624A917F5B462A4DCA05CDCE256EEEEEDC97D5A

C4D07C3A079829F" timeout="300" maxConnDiff="10" maxConn="10"

dataSource="VALUEOF(BI_EE_HOME)/fcast_data/Forecast" type="Default"

reqQualifedTableName="false" isSharedLogin="false"

isConcurrentQueriesInConnection="false" isCloseAfterEveryRequest="true"

xmlRefreshInterval="4294967295" outputType="xml" bulkInsertBufferSize="0"

transactionBoundary="0" xmlaUseSession="false" multiThreaded="false"

supportParams="false" isSiebelJDBSecured="false">

<Description></Description>

</ConnectionPool>


<ConnectionPool name="SampleApp_Relational"

parentName=""1 - Sample App Data""

parentId="3023:85" parentUid="2160747204"

id="3029:1159" uid="2160747205"

user="user_prod"

password="D7EDED84BC624A917F5B462A4DCA05CDCE256EEEEEDC97D5A1

50C61FBEB9092AF80FAEC9819DD51771A41425D03FB466B55BB9F03BCF643

9"

timeout="4294967295" maxConnDiff="10" maxConn="10"

dataSource="orcl_prod" type="OCI10G" reqQualifedTableName="false"

isSharedLogin="true" isConcurrentQueriesInConnection="false"

isCloseAfterEveryRequest="false" outputType="xml"

bulkInsertBufferSize="131072" tempTablePrefix="TT" transactionBoundary="1000"

xmlaUseSession="false" isSiebelJDBSecured="false">

<Description></Description>

</ConnectionPool>

</DECLARE>

</Repository>

Notes:

1. The two connection pool objects you altered appear here. You can

“execute” this file against a test version of the RPD to apply the connection

pool parameters for the production environment.

2. You will have to guarantee that test RPDs will always have the same

parentName for the databases, in order for the connection pools to be

applied in the right place in the model (yellow highlight). If you cannot

guarantee the name of the database object will never change, you will also

have to make the corresponding XML change in the parentName in the

connection pool object each time you plan to execute it.

3. You can leave the parentId and parentUid (green highlight) in the XML if you

can guarantee the parent database object will always have the same id and


Uid. However, keep in mind that editing operations such as deleting and

recreating the database object will result in a new id and Uid even if the

name is kept the same– in the case where changes such as this are

possible, you should delete the parentId and parentUid from the XML and

let the BI Server regenerate them based on the parentName at execution

time.

4. You can leave the id and uid for the connection pool object (gray highlight)

in the XML if you can guarantee that the connection pool id and uid will not

change during development and test edits. However, you will usually want

to delete them. That way, test RPDs that contain changes to the Ids will still

have the patch properly applied based on the object name. New ids and

Uids will be automatically generated.

5. The source database user, password and data source are specified here –

these are the actual changes that you are trying to propagate.

6. An administrator can edit this file manually to update the attributes if

necessary.


Applying the Connection Pool Replacement Patch

To apply the patch to a test RPD before migration to production:

1. Execute the XML file against the test RPD to create the production RPD.

You should execute the XML using the –B (baseline) option, which will apply just

your patch XML objects to the test RPD.

In a DOS window (or UNIX equivalent), perform the following commands,

substituting the path to your Oracle BI EE installation for <home>. First, change

directory to point to your repository directory, which contains your RPDs and your

XML patch:

> cd <home>\instances\instance1\bifoundation\OracleBIServerComponent\

coreapplication_obis1\repository

Next, execute the XML patch:

> <home>\Oracle_BI1\bifoundation\server\bin\biserverxmlexec -I

SampleApp_patch_conpool_to_prod.xml -B SampleApp_test.rpd -P welcome1 -O

SampleApp_prod.rpd


2. From inside the Administration Tool, open the new RPD this created,

SampleApp_prod.rpd. Note that the connection pool has been changed to

the production values.

This same method of making the change to a new RPD, then comparing to the

original and making a patch that can be applied automatically, can be used for any

set of objects in the RPD. The patch can also be applied to a running BI Server

system using the biserverxmlcli.exe.


Oracle Corporation World Headquarters 500 Oracle Parkway Redwood Shores, CA 94065 USA Worldwide inquiries: Phone: +1 650 506 7000 Fax: +1 650 506 7200 www.oracle.com Oracle is the information company Oracle is a registered trademark of Oracle Corporation. Various product and service names referenced herein may be trademarks of Oracle Corporation. All other product and service names mentioned may be trademarks of their respective owners. Copyright © 2010 Oracle Corporation All rights reserved.

obiee intallation step by step

Documents

g advanced workshop

oracle database xe

xe database

oracle database of enterprise

workshop environment

g page

workshop students lab

g installation