Using Oracle's Parallel Execution Features

Using Oracle's Parallel Execution Features

Parallel Features The full list of Oracle parallel execution features currently includes the following Parallel Query Parallel DML Parallel DDL Parallel Data Loading Parallel Recovery Parallel Replication How Parallel Execution Works Operations That Can Be Parallelized Oracle can parallelize operations that involve processing an entire table or an entire partition. These operations include: SQL queries requiring at least one full table scan or queries involving an index range scan spanning multiple partitions. Operations such as creating or rebuilding an index or rebuilding one or more partitions of an index. Partition operations such as moving or splitting partitions CREATE TABLE AS SELECT operations, if the SELECT involves a full table or partition scan. INSERT INTO . . . SELECT operations, if the SELECT involves a full table or partition scan. Update and delete operations on partitioned tables Parallel Query Parallel query is the most commonly used of Oracle's parallel execution features. It was the first parallel execution feature to be developed by Oracle and was introduced in Oracle Release 7.1 as the Oracle Parallel Query Option (PQO). Parallel execution can significantly reduce the elapsed time for large queries, but it doesn't apply to every query. To parallelize a SELECT statement, the following conditions must be met: At least one of the tables is accessed through a full table scan, or an index is accessed through a range scan involving multiple partitions. If the execution involves a full table scan, the statement must contain a PARALLEL hint specifying the corresponding table, or the corresponding table must have a parallel declaration in its definition. If the execution involves an index range scan spanning multiple partitions, the statement must contain a PARALLEL_INDEX hint specifying the corresponding index, or the corresponding index must have a parallel declaration in its definition. The following two sections explain how the degree of parallelism is chosen for a SELECT statement and discuss restrictions on the use of the parallel query feature. Setting the Degree of Parallelism Once Oracle decides to execute a SELECT statement in parallel, the degree of parallelism is determined by following precedence rules: Oracle retrieves the DEGREE and INSTANCES specifications from the definition of all tables and indexes involved in the query and chooses the highest values found for those settings. Oracle checks the statement for a parallel hint. If such a hint is found, the hint overrides the degree of parallelism obtained as a result of the previous step. You can use the PARALLEL and PARALLEL_INDEX hints to specify the degree of parallelism for a SELECT statement. You can use theNOPARALLEL and NOPARALLEL_INDEX hints to ensure that parallel execution is not performed. Example alter table emp parallel (degree 4);select degree from user_tables where table_name = 'EMP'; select count(*) from emp; alter table emp noparallel; SELECT /*+ PARALLEL(emp,4) */ COUNT(*) FROM emp; Parallel DML Data Manipulation Language (DML) operations such as INSERT, UPDATE, and DELETE can be parallelized by Oracle. Parallel execution can speed up large DML operations and is particularly advantageous in data warehousing environments where it's necessary to maintain large summary or historical tables. In OLTP systems, parallel DML sometimes can be used to improve the performance of long-running batch jobs. Deciding to Parallelize a DML Statement When you issue a DML statement such as an INSERT, UPDATE, or DELETE, Oracle applies a set of rules to determine whether that statement can be parallelized. For UPDATE and DELETE statements, the rules are identical. INSERT statements, however, have their own set of rules. Rules for UPDATE and DELETE statements Oracle can parallelize UPDATE and DELETE statements on partitioned tables, but only when multiple partitions are involved. You cannot parallelize UPDATE or DELETE operations on a nonpartitioned table or when such operations affect only a single partition. Rules for INSERT statements Standard INSERT statements using a VALUES clause cannot be parallelized. Oracle can parallelize only INSERT . . . SELECT . . . FROM statements. Examples alter session enable parallel dml; insert /*+ parallel (emp_big,4,1) */   into emp_big select * from emp; commit; alter session disable parallel dml; Parallel DDL Parallel DDL works for both tables and indexes, whether partitioned or nonpartitioned. For nonpartitioned tables and indexes, only the following types of DDL statements can be parallelized: CREATE TABLE...AS SELECT CREATE INDEX ALTER INDEX...REBUILD If you're working with partitioned tables and indexes, the scope of Oracle's parallel DDL support broadens. The following statements can be parallelized for partitioned tables and indexes: CREATE TABLE...AS SELECT ALTER TABLE...MOVE PARTITION ALTER TABLE...SPLIT PARTITION CREATE INDEX ALTER INDEX...REBUILD PARTITION ALTER INDEX...SPLIT PARTITION Not all tables allow these operations to be executed in parallel. Tables with object columns or LOB columns don't allow parallel DDL. Example create table big_emp parallel (degree 4)   as select * from emp; CREATE INDEX emp_ix ON emp (emp_id)    TABLESPACE ind    STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0 MAXEXTENTS 20)    PARALLEL (DEGREE 4); Parallel Data Loading Oracle's SQL*Loader utility loads data into Oracle tables from external files. With some restrictions, SQL*Loader supports the loading of data in parallel. If you have a large amount of data to load, SQL*Loader's parallel support can dramatically reduce the elapsed time needed to perform that load. Initiating Parallel Data Loading SQL*Loader supports parallel loading by allowing you to initiate multiple concurrent direct path load sessions that all load data into the same table or into the same partition of a partitioned table. Unlike the case when you execute a SQL statement in parallel, the task of dividing up the work falls on your shoulders. Follow these steps to use parallel data loading: Create multiple input datafiles. Create a SQL*Loader control file for each input datafile. Initiate multiple SQL*Loader sessions, one for each control file and datafile pair. Example SQLLOAD scott/tiger CONTROL=con1.ctl DIRECT=TRUE PARALLEL=TRUE SQLLOAD scott/tiger CONTROL=con2.ctl DIRECT=TRUE PARALLEL=TRUE SQLLOAD scott/tiger CONTROL=con3.ctl DIRECT=TRUE PARALLEL=TRUE SQLLOAD scott/tiger CONTROL=con4.ctl DIRECT=TRUE PARALLEL=TRUE Note that the commands here should be executed from four different operating system sessions. The intent is to get four SQL*Loader sessions going at once, not to run four sessions one at a time. For example, if you are using the Unix operating system, you might open four command-prompt windows and execute one SQL*Loader command in each window. Another important thing to note here is that you need to use the direct path in order to perform a load in parallel, as explained in the next section. This is achieved by the command-line argument DIRECT=TRUE. Parallel loads are not possible using the conventional path option. Parallel Recovery Parallel recovery can speed up both instance recovery and media recovery. In parallel recovery, multiple parallel slave processes are used to perform recovery operations. The SMON background process reads the redo log files, and the parallel slave processes apply the changes to the datafiles.  In a serial recovery scenario, the SMON background process both reads the redo log files and applies the changes to the datafiles. This may take a considerably long time when multiple datafiles need to be recovered. However, when parallel recovery is being used, the SMON process is responsible only for reading the redo log files. The changes are applied to the datafiles by multiple parallel slave processes, thereby reducing the recovery time. Recovery requires that the changes be applied to the datafiles in exactly the same order in which they occurred. This is achieved by single-threading the read phase of the recovery process by the SMON process. SMON reads the redo log files and serializes the changes before dispatching them to the parallel slave processes. The parallel slave processes then apply those changes to the datafiles in the proper order. Therefore, the reading of the redo log files is performed serially even during a parallel recovery operation. Specifying the RECOVERY_PARALLELISM Parameter The RECOVERY_PARALLELISM initialization parameter controls the degree of parallelism to use for a recovery. You can override that setting for a specific situation by using the RECOVER command's PARALLEL clause. A value of or 1 indicates serial recovery, no parallelism will be used. The RECOVERY_PARALLELISM parameter setting cannot exceed the PARALLEL_MAX_SERVERS setting. Example RECOVER TABLESPACE tab PARALLEL (DEGREE 4); RECOVER DATABASE PARALLEL (DEGREE DEFAULT); Parallel Replication Oracle provides replication mechanisms allowing you to maintain copies of database objects in multiple databases. Changes are propagated among these databases over database links. The SNP (snapshot) background processes perform the replication process. For large volumes of replicated data, parallel propagation can be used to enhance throughput. With parallel propagation, Oracle enlists multiple parallel slave processes to propagate replicated transactions using multiple parallel streams. Oracle orders the dependent transactions properly based on the System Change Number (SCN). During parallel propagation, you can see multiple connections to the destination database. You enable parallel replication propagation at the database link level. A database link is created for a particular destination database. When you enable parallel propagation for a database link, Oracle uses multiple parallel slave processes to replicate to the corresponding destination. Enable Parallel Replication Propagation To enable parallel replication propagation from the SQL*Plus command line, you need to use the Oracle built-in package DBMS_DEFER_SYS. Execute the DBMS_DEFER_SYS.SCHEDULE_PUSH procedure for the destination database link, and pass the desired degree of parallelism as the value for the parallelism argument.  Example for SQL*Plus EXECUTE DBMS_DEFER_SYS.SCHEDULE_PUSH (- DESTINATION => 'por1.world', - INTERVAL => 'SYSDATE+1/24', - NEXT_DATE => 'SYSDATE+1/24', - PARALLELISM => 6); This example sets the degree of parallelism to 6 for propagating to the "por1.world" destination database. How Parallel Execution Works Oracle divides the task of executing a SQL statement into multiple smaller units, each of which is executed by a separate process. When parallel execution is used, the user's shadow process takes on the role of the parallel coordinator. The parallel coordinator is also referred to as parallel execution coordinator or query coordinator. The parallel coordinator does the following: Dynamically divides the work into smaller units that can be parallelized.    Acquires a sufficient number of parallel processes to execute the individual smaller units. These parallel processes are called parallel slave processes. They also are sometimes referred to as parallel execution server processes, parallel server processes, parallel query slaves, or simply slave processes. The most common of the terms, parallel slave processes and slave processes, are used throughout this book.   Assigns each unit of work to a slave process.   Collects and combines the results from the slave processes, and returns those results to the user process.   Releases the slave processes after the work is done. The Pool of Parallel Slave Processes Oracle maintains a pool of parallel slave processes for each instance. The parallel coordinator for a SQL statement assigns parallel tasks to slave processes from this pool. These parallel slave processes remain assigned to a task until its execution is complete. After that, these processes return to the pool and can be assigned tasks from some other parallel operation. A parallel slave process serves only one SQL statement at a time. The following parameters control the number of parallel slave processes in the pool: PARALLEL_MIN_SERVERS  Specifies the minimum number of parallel slave processes for an instance. When an instance starts up, it creates the specified number of parallel slave processes. The default value for this parameter is 0, meaning that no slave processes would be created at startup. PARALLEL_MAX_SERVERS Specifies the maximum number of parallel slave processes that an instance is allowed to have at one time. The default value for PARALLEL_MAX_SERVERS is platform-specific. It takes time and resources to create parallel slave processes. Since parallel slave processes can serve only one statement at a time, you should set PARALLEL_MIN_SERVERS to a relatively high value if you need to run lots of parallel statements concurrently. That way, performance won't suffer from the need to constantly create slave processes. You also need to consider how to set PARALLEL_MAX_SERVERS. Each parallel slave process consumes memory. Setting PARALLEL_MAX_SERVERS too high may lead to memory shortages during peak usage times. On the other hand, if PARALLEL_MAX_SERVERS is set too low, some operations may not get a sufficient number of parallel slave processes. The Degree of Parallelism The number of parallel slave processes associated with an operation is called its degree of parallelism . Don't confuse this term with the DEGREE keyword. They aren't exactly the same thing. In Oracle, the degree of parallelism consists of two components, the number of instances to use and the number of slave processes to use on each instance. In Oracle's SQL syntax, the keywords INSTANCES and DEGREE are always used to specify values for these two components as follows: INSTANCES: Specifies the number of instances to use DEGREE: Specifies the number of slave processes to use on each instance INSTANCES applies only to the Oracle Parallel Server configuration. Unless you are using OPS, the value of INSTANCES should be set to 1; any other value is meaningless.  Level of parallel execution The degree of parallelism used for a SQL statement can be specified at three different levels: Statement level  Using hints or the PARALLEL clause Object level Found in the definition of the table, index, or other object Instance level  Using default values for the instance Oracle determines the degree of parallelism to use for a SQL statement by checking each item in this list in the order shown. Oracle first checks for a degree of parallelism specification at the statement level. If it can't find one, it then checks the table or index definition. If the table or index definition does not explicitly specify values for DEGREE and INSTANCES, Oracle uses the default values established for the instance. Specifying the degree of parallelism at the statement level You can specify the degree of parallelism at the statement level by using hints or by using a PARALLEL clause. PARALLEL and PARALLEL_INDEX hints are used to specify the degree of parallelism used for queries and DML statements. However, DDL statements that support parallel execution provide an explicit PARALLEL clause in their syntax. SELECT /*+ PARALLEL(orders,4,1) */ COUNT(*) FROM orders; Specifying the degree of parallelism at the object definition level You can specify the degree of parallelism to use for a table or an index when you create it. You do that by using the PARALLEL clause of the CREATE TABLE and CREATE INDEX statements. You also can specify a PARALLEL clause when you alter a table or an index. ALTER TABLE order_items PARALLEL (DEGREE 4); When you specify DEGREE and INSTANCES values at the table or index level, those values are used for all SQL statements involving the table or index unless overridden by a hint. Specifying the degree of parallelism at the instance level Each instance has associated with it a set of default values for DEGREE and INSTANCES. The default DEGREE value is either the number of CPUs available or the number of disks upon which a table or index is stored, whichever is less. Oracle will use the instance-level defaults whenever the keyword DEFAULT is used in a hint or in a table or index definition. Oracle also will use the instance-level defaults when there are no hints and when no degree of parallelism has been specified at the table or index level.

Ref: http://www.akadia.com/services/ora_parallel_processing.html

Parallel Query: Automating Parallelism

Parallel Query: Automating Parallelism

Oracle Database Tuning

Oracle Database Tuning
(and Other Performance Issues)

Objectives

• Identify how poor system performance results from a poor database system design.
• Familiarize with how database tuning focuses on identifying and fixing underlying flaws.
• Familiarize with typical objects that can be tuned.

---

TUNING APPLICATION DESIGN

Effective Table Design

• Poor table design always leads to poor performance.
• Rigid adherence to fully normalized relational table guidelines can also result in poor physical performance.  These inefficiencies result from:
• requiring too many joins.
• failure to reflect the normal access paths for data.
• Queries with large numbers of columns that come from multiple tables can cause performance to suffer because several tables must be joined.
• Design options include:
• Denormalizing 1NF, 2NF and 3NF solutions.
• Creating small summary tables from large, static tables that stores data in the format in which users ask for the data - this avoids joins where data are often requested and the data doesn't change very often.
• Separating individual tables into several tables by either vertical partitioning and/or horizontal partitioning.

Distribution of CPU Requirements

• An Oracle database that is CPU-Bound (limited by CPU resources) as opposed to Wait-Bound (waiting on disk writes of some type) is one that's effectively designed.  This means that other resources are not limiting the database.
• Schedule long-running batch query/update programs for off-peak hours; then run them at normal priority.
• Store data in its most appropriate place in a distributed computing environment - this distributes computing CPU requirements from one server to another.
• Use the Parallel Query option to distribute processing requirements of selected SQL statements among multiple CPUs if they are available.

Effective Application Design

• There are two principles to follow here: 

1.           limit the number of times that users access the database, and

2.           coordinate the requests of users for data.  This requires you to know how users tend to access data.

• Try to use the same queries to handle similar application data requirements; this will increase the likelihood that a data requirement can be resolved by data already in the SGA.
• Use snapshots, which are non-updatable views of part of a database that can be distributed to support typical managerial querying.
• Create stored procedures, functions, and packages and compile them to eliminate run-time compilation. The parsed version may exist in theShared SQL Pool.

1.           The SQL Text for all procedures, functions, and packages can be viewed in the TEXT column of the DBA_SOURCE view.

2.           These objects (procedural code) are stored in the SYSTEM tablespace so you need to allocate more space to it -- usually double its size.

---

TUNING SQL

• Most SQL tuning requires the DBA to work with an application developer.
• Most improvement in database processing will come from tuning SQL.
• The key to SQL tuning is to minimize the search path that a database uses to find data.  For the most part this requires the creation of appropriate indexes that the database engine will use to find data.

Indexes

• An Index enables Oracle to locate a row according to its physical location in a datafile by going to the correct file, finding the correct block, thenfinding the correct row within the block.  Taken together, these values are like relative record numbers in a relative file.
• The File ID portion of ROWID can be compared to the FILE_ID column of the DBA_DATA_FILES view to determine the file to which an index belongs.
• A query with no WHERE clause normally results in a full table scan, reading every block in a table.
• A query for specific rows may cause use of an index.
• The index maps logical values in a table (key columns) to their ROWIDs which enables location of the rows directly by their physical location.
• You may index several columns together to form a concatenated index.
• A concatenated index is only used if its leading column is used in the query's WHERE clause.
• Consider the following example:

CREATE INDEX City_state_zip_ndx
ON Employee (City, State, Zip)
TABLESPACE Indexes;

SELECT *
FROM Employee
WHERE State = 'NJ';

• The index is NOT used because the WHERE clause value of State does not match the leading column (City) of the index.
• This example would only add overhead because the index would be maintained, even if it's not used.
• The index should be recreated with proper ordering of the component fields in the index if the query is executed often.

Ordering Data

• While row ordering does not matter in relational theory, it is important to order rows as much as possible when tables are initially created, e.g. when you are porting a system into Oracle from another platform or DBMS.
• Ordered rows may enable Oracle to find needed rows while minimizing the data blocks that are retrieved where users execute queries that specify ranges of values (recall the BETWEEN operator in SQL for range of value queries).
• Consider the following example which will require fewer data blocks to be read if the records are physically ordered on the Empno field.

SELECT *
FROM Employee
WHERE Empno BETWEEN 1 and 100;

·        You can physically sort table rows by SELECTing them to another file with use of the ORDER BY clause, then truncating the original table and loading the rows back into the original table. 

Clusters

• We covered indexed clusters in an earlier module. 
• Another type of cluster, the hash cluster, stores rows in a specific location based on its value in the cluster key column.
• Every time a row is inserted, its cluster key value is used to determine which block to store the row in.
• This enables hashing directly to data blocks without use of an index.
• The hash cluster is only used with equivalence queries - where the exact value stored in a column is to be found.

Explain Plan

• The EXPLAIN PLAN command shows the execution path for a query and stores this information to a table (PLAN_TABLE) in the database.  You can then query the table.  Example:

EXPLAIN PLAN
SET Statement_id = 'TEST'
FOR

SELECT *
FROM Employee
WHERE city > 'Y%';

• The query above is not actually executed; rather the plan for execution is stored to the PLAN_TABLE.
• Your account must have a PLAN_TABLE in your schema.  The script to create this table is UTLXPLAN.SQL and is located in the$ORACLE_HOME/rdbms/admin subdirectory.
• Query the table to produce the output that shows the execution path.

SELECT LPAD(' ',2*LEVEL) || operation
       || ' ' || options ||
       ' ' || object_name Path_Plan
FROM Plan_Table
WHERE Statement_id = 'TEST'
CONNECT BY PRIOR Id = Parent_id
    AND Statement_id = 'TEST'

    START WITH Id=1;

Path_Plan ----------------------------------- TABLE ACCESS BY ROWID EMPLOYEE   INDEX RANGE SCAN CITY_ST_ZIP_NDX

• The output shows that data will be accessed by ROWID through an index range scan of the named index.
• Alternatively, you can also use the SET AUTOTRACE ON command in SQL*Plus to generate the explain plan output and trace information for every query that you run.
• Evaluate the output by ensuring that the most selective (most nearly unique) indexes are used by a query.

---

TUNING MEMORY USAGE

You can use the Oracle Enterprise Manager software to analyze usage of memory by Oracle’s various memory caches.

• The dictionary cache in memory is not directly sized or tuned as it is part of the Shared SQL Pool.
• These memory areas are managed by the LRU (least recently used) algorithm.  You set the Shared SQL Pool size with theSHARED_POOL_SIZE parameter. 
• If your Shared SQL Pool is too large, then you are wasting memory. 
• The Hit Ratio measures how well the data buffer cache handles requests.

Hit Ratio = (Logical Reads - Physical Reads) /
                    Logical Reads

• A perfect ratio is 1.00 - all reads are logical reads; of course, this is generally impossible to obtain since it indicates that all the data that a system user will ever need to access is stored in the SGA.
• On-line transaction processing applications should have Hit Ratios in excess of 0.90.
• If processing for the Hit Ratio is within tolerance, you need to check to see if you can reduce the size of the Shared SQL Pool and still maintain a good Hit Ratio.
• Add the following to the INIT.ORA file.

 DB_BLOCK_LRU_STATISTICS = TRUE

• Shutdown the database and restart it.
• The system dictionary table SYS.X$KCBCBH maintains memory statistics.  One row is maintained for each buffer in the buffer cache.  You can query this information to determine how many buffers are not being used.
• Use the following query to determine how many cache hits (the COUNT column) would be lost if you reduced the number of buffers (the INDXcolumn).

SELECT Sum(Count) Lost_Hits
FROM Sys.X$Kcbcbh
WHERE indx >= New_Number_Of_Buffers;

(NOTE: You supply the value in the WHERE clause)

• If you have lost hits, the system will require additional physical reads - the Hit Ratio for this new number of data buffers is:

Hit Ratio =
  (Logical Reads - Physical Reads - Lost Hits)
              / Logical Reads

• Since running the database in a statistics gathering mode will slow it down due to the additional overhead, you should comment out theDB_BLOCK_LRU_STATISTICS parameter after you have finished tuning and restart the database.

---

TUNING DATA STORAGE

Defragmentation of Segments

Fragmented tables with multiple extents will slow down query processing.  This can also slow down the storage of new records because the database may have to dynamically combine free extents to create a new extent large enough to meet the storage parameters of the object where data are being stored.

• We know that a segment is created to hold data associated with a new object (index or table) when an object is created.
• The space allocated is used unless the segment is released (dropped) or truncated (tables only).
• It would be best if each segment was composed of a single large INITIAL extent - compute the size for the initial extent such that it is large enough to handle all of a segment's data.
• Use the DBA_SEGMENTS data dictionary view to determine which segments are comprised of ten or more extents.

SELECT Tablespace_name TSName, Owner,
    Segment_Name SNName,
    Segment_type SNType, Extents,

    Blocks, Bytes
FROM Sys.DBA_Segments;
 

TSNAME OWNER SNNAME    SNTYPE EXTENTS BLOCKS BYTES DATA   DBOCK LONGTIME  TABLE  1       15     61440 DATA   DBOCK MAGAZINE  TABLE  1       15     61440 DATA   DBOCK MATH      TABLE  1       15     61440 DATA   DBOCK WORKERANDSKILL CLUSTER 2 30    122880
 

• To see the size of the individual extents for a segment, query the DBA_EXTENTS view.  Supply the type of segment you desire (TABLE, INDEX, CLUSTER, ROLLBACK, TEMPORARY, etc.).

SELECT Tablespace_name TSNAME, Owner,
    Segment_Name SNNAME,
    Segment_Type SNTYPE, Extent_id EID,
    File_id FID, Block_id BID, Bytes, Blocks
FROM Sys.DBA_Extents
WHERE Segment_type = 'segment_name'
ORDER BY Extent_id;

TSNAME OWNER SNNAME     SNTYPE EID FID BID Bytes Blocks
DATA   DBOCK SYS_C00890 INDEX  0   4   137 61440 15 DATA   DBOCK SYS_C00891 INDEX  0   4   152 61440 15
 

• If a segment is fragmented, you can rebuild the object into a single segment by using the proper size for the storage parameters.  Export the data for the segment, recreate the object, then import the data into the INITIAL extent.

Defragmentation of Free Extents

A Free Extent is a collection of contiguous free blocks in a tablespace that are unused.

• If a segment is dropped, its extents are deallocated and become free, but these extents are not recombined with neighboring free extents.
• SMON periodically coalesces neighboring free extents only if the default PCTINCREASE for a tablespace is non-zero.
• The ALTER TABLESPACE tablespace_name COALESCE command can be used to force the combining of free extents.
• Your readings will list a number of scripts available in Oracle to test whether or not free space needs to be coalesced.

Identifying chained Rows

• Chained rows occur when a row is updated and will no longer fit into a single data block.
• If you store rows that are larger than the Oracle block size, then you will cause chaining.
• Chaining affects performance because of the need for Oracle to search multiple blocks for a logical row.
• The ANALYZE command can be used to determine if chaining is occurring.

ANALYZE TABLE Table_Name
LIST CHAINED ROWS INTO Chained_Rows;

• The output is stored to the CHAINED_ROWS table in your schema.  The CHAINED_ROWS table needs to first be created by executing theUTLCHAIN.SQL script in the $ORACLE_HOME/rdbms/admin directory.
• If chaining is prevalent (all chained rows are listed), then rebuild the table with a higher PCTFREE parameter.

Increasing the Oracle Block Size

• Oracle support different block sizes, but the most common block sizes used are 4K and 8K.
• Installation routines default to different block size values.  For example, with our version of Oracle in a LINUX environment the system defaults to 8K if you do not specify the block size when you create the database.

o   Using the next higher block size value may improve performance of query-intensive operations by up to 50 percent.

o   Problem:  You must rebuild the entire database to increase the block size.

o   Improvement comes because the block header does not increase significantly leaving more space in a block for transaction information and for data rows.

Bulk Deletes: The TRUNCATE Command

• Deleting all the rows in a table will not save any space because the segment for the table is still allocated all of the extents beyond those the first one that was allocated by the INITIAL parameter.
• Deleting all rows can also result in UNDO errors because the bulk delete causes a very large transaction – can lead to overwrites and theSnapshot Too Old error message..
• The TRUNCATE command resolves both problems, but you need to realize that this is a DDL command, not a DML command, so it cannot berolled back.
• The TRUNCATE command is the fastest way to delete large volumes of data.

TRUNCATE TABLE Employee DROP STORAGE;

TRUNCATE CLUSTER Emp_Dept REUSE STORAGE;

• The above command deletes all Employee table rows and the DROP STORAGE clause de-allocates the non-INITIAL extents.  The second example command is for clusters.

WebUtil: How to Read an Excel file into an Oracle Form

Description of included files (listed below):
Read Excel to Forms.dox – This document in Microsoft Word 2003 format
create_planets.sql – script to create the table used by this sample code
planets_ole_excel_read.fmb – sample form which demonstrates the concept
planets.xls – Excel (version 2003) sample spreadsheet

Setup steps:
Log into your database and run the “create_planets.sql” script
Ensure your environment is properly configured to run WebUtil. This document does not cover this configuration – there is plenty of information available in the OTN forums as well on the Internet that covers configuring WebUtil so I will not included it here.
Place “planets.xls’ some place on the client computer. I have modified Oracle’s original form to display a “File Select dialog” rather than hard code the file name and location in the code as Oracle demo did.
Ensure you have configured the Forms Builder to allow running a form from the Forms Builder. This document does not cover how to perform this configuration.
Run the form from the Forms Builder or on the client computer and push the “Read from Excel” button. Choose the ‘planets.xls’ file from the “Select Client filename to Open” dialog and the data will be read from the Excel spreadsheet into the form. You can then push the save button in the toolbar to commit the data to the table.
Here is the code behind the “Read from Excel” button with some explanatory comments:
DECLARE
application Client_OLE2.Obj_Type;
workbooks Client_OLE2.Obj_Type;
workbook Client_OLE2.Obj_Type;
worksheets Client_OLE2.Obj_Type;
worksheet Client_OLE2.Obj_Type;
worksheet2 Client_OLE2.Obj_Type;
cell Client_OLE2.OBJ_TYPE;
args Client_OLE2.OBJ_TYPE;
cell_value varchar2(100);
num_wrkshts NUMBER;
wksht_name VARCHAR2(250);
eod Boolean := false;
j integer := 1;
v_fName VARCHAR2(250);
BEGIN
-- Get the name of the file to open
--v_fName :=
'D:\MyDevelopment\Forms\Samples\WebUtil\Read_Excel\planets3.xls';
-- My Way: Use a File Open Dialog to let the user select the file.
v_fName := WebUtil_File.File_Open_Dialog(
directory_name => 'C:\'
,File_Filter => null
,Title => 'Select Client filename to Open.'
);

-- Make sure the user selected a file
IF ( v_fName IS NOT NULL ) THEN
-- The following sets up communication with the excel spreadsheet
-- --------------------------------------------------------------

-- Open the OLE application
application := Client_OLE2.create_obj('Excel.Application');
-- Keep the application hidden
Client_OLE2.set_property(application,'Visible','false');

workbooks := Client_OLE2.Get_Obj_Property(application, 'Workbooks');
args := Client_OLE2.CREATE_ARGLIST;

-- Open the selected File
-- ----------------------
Client_OLE2.add_arg(args,v_fName);
workbook := Client_OLE2.GET_OBJ_PROPERTY(workbooks,'Open',args);
Client_OLE2.destroy_arglist(args);

worksheets := Client_OLE2.GET_OBJ_PROPERTY(workbook, 'Worksheets');

-- Get number of worksheets
-- ------------------------
num_wrkshts := Client_OLE2.GET_NUM_PROPERTY(worksheets, 'Count');
worksheet := Client_OLE2.GET_OBJ_PROPERTY(
application,'activesheet');

--Go to the first record
go_block('planets');
first_record;

-- Loop through the Block and create a new row if needed.
loop
If :system.record_status <> 'NEW' then
create_record;
end if;

-- Exit when the last row of the spreadsheet is reached.
exit when eod;

-- Loop through the spreadsheet and get cell values
for k in 1..3 loop --3 fields per record
-- You have to know fields there are
args:= Client_OLE2.create_arglist;
Client_OLE2.add_arg(args, j);
Client_OLE2.add_arg(args, k);
cell:= Client_OLE2.get_obj_property(worksheet, 'Cells', args);
Client_OLE2.destroy_arglist(args);
cell_value :=Client_OLE2.get_char_property(cell, 'Value');

-- Check for End of Data…
if upper(cell_value) = 'EOD' then
eod:=true;
Message('End of Data');
exit;
end if;

-- Copy the value from Excel to the Forms block item
-- This is how the Oracle example copied values
/*if k =1 then
:dept.deptno:=cell_value;
end if;

if k =2 then
:dept.dname:=cell_value;
end if;

if k =3 then
:dept.loc:=cell_value;
end if;
*/

-- This is my way; which is more efficient and less code
copy(cell_value,name_in('system.cursor_item'));
next_item;

end loop; --for

j:=j+1;
end loop; --main loop

-- Release the Client_OLE2 object handles
IF (cell IS NOT NULL) THEN
Client_OLE2.release_obj(cell);
END IF;
IF (worksheet IS NOT NULL) THEN
Client_OLE2.release_obj(worksheet);
END IF;
IF (worksheets IS NOT NULL) THEN
Client_OLE2.release_obj(worksheets);
END IF;
IF (worksheet2 IS NOT NULL) THEN
Client_OLE2.release_obj(worksheet2);
END IF;
IF (workbook IS NOT NULL) THEN
Client_OLE2.release_obj(workbook);
END IF;
IF (workbooks IS NOT NULL) THEN
Client_OLE2.release_obj(workbooks);
END IF;
Client_OLE2.invoke(application,'Quit');
Client_OLE2.release_obj(application);
ELSE
Message('No File selected.');
message(' ');
RAISE Form_Trigger_Failure;
END IF;
END;

Oracle BI Tutorial - Getting Started with Oracle BI Publisher 11g

Getting Started with Oracle BI Publisher 11g

This tutorial contains the following sections:

	Purpose
	Time to Complete
	Overview
	Prerequisites
	Getting Started with BI Publisher
	Logging in to Oracle BI Publisher and Navigating the Home Page Browsing the Catalog and Viewing the Reports Setting Preferences Configuring the Data Sources Types of Supported Data Sources Defining a JDBC Connection Configuring File Data Location Defining a JNDI (Java Naming and Directory Interface) Connection
	Creating a Data Model
	Defining Default Data Source, and Output Options Creating a SQL Query Data Set Viewing the XML Output and Saving the Sample Data
	Creating a Report
	Building a Layout Using Layout Editor
	Launching the Layout Editor and Adding a Grid Layout Working with Charts Adding a Data Table to the Layout Formatting the Data, and Working with the Page Layout Features
	Scheduling a Report
	Confirming that the Scheduler is Configured Defining Delivery Destinations Creating Scheduled Report Jobs Managing Job History and Jobs
	Summary
	Resources

Purpose

This tutorial covers how to get started with Oracle BI Publisher 11g to create simple reports based on Oracle Database. Also, it teaches you how to create report layouts using the Layout Editor online, and how to schedule reports.

Time to Complete

Approximately 1 hour.

Overview

Oracle BI Publisher is a strategic enterprise reporting product from Oracle that provides the ability to create and manage highly formatted reports from a wide range of data sources. Oracle BI Publisher is available as part of the Oracle BI Enterprise Edition Suite and also as a stand alone reporting tool. Oracle BI Publisher report formats can be designed using Microsoft Word or Adobe Acrobat, the tools that are easy to use, and most users are already familiar with. Oracle BI Publisher also allows you to create reports from different types of data sources such as Oracle Database, Files, BI EE, Web services, and Discoverer.

Release 11g of Oracle BI Publisher has enhanced, easy- to-use user interface (UI), many enhanced features, and newly introduced salient features such as - Data Model Editor - a graphical user interface for building data models within the BI Publisher interface; and Layout Editor - a design tool that enables you to create report layouts within the BI Publisher interface.

In this tutorial you will be guided to create simple reports in BI Publisher based on Oracle Database. You will also be guided to work with the Layout Editor to create the layouts for the reports.

Prerequisites

Before starting this tutorial, you should:

1.	Have access to Oracle BI Publisher 11g or installed BI Publisher 11g as stand alone or as part of Oracle BI Enterprise Edition 11g. Also, it is recommended to use the following versions of borrowers - Internet Explorer 7.0 (or above), Mozilla Firefox 3.6.3 (or above).
2.	Have access to an Oracle Database11g (or 10g) with the sample schemas HR and OE installed and unlocked. The exercises in this tutorial use the HR and OE schemas included in the Oracle Database.
3 .	Have executed the Repository Creation Utility ( RCU) scripts in Oracle Database 11g. This should be done before installing Oracle BI EE. RCU creates the BI Publisher scheduler repository. Note: BI Enterprise Edition (BI EE) installer comes with these out of the box RCU scripts, that create the necessary schemas and the repositories in the Database for various components of BI EE including BI Publisher. You need an Oracle 11g Database to run these RCU scripts. However, you can establish a JDBC connection and create data models and reports through BI publisher using both 10g and 11g Databases.

Getting Started with BI Publisher

This topic guides you to login to BI Publisher and get started. It has the following subtopics:

• Logging in to BI Publisher and Navigating the Home Page
• Browsing the catalog and Viewing reports
• Setting Preferences

	Logging in to Oracle BI Publisher and Navigating the Home Page
	Browsing the Catalog and Viewing the Reports
	Setting Preferences
	Configuring the Data Sources
	Types of Supported Data Sources
	Defining a JDBC Connection
	Configuring File Data Location
	Defining a JNDI (Java Naming and Directory Interface) Connection

Logging in to Oracle BI Publisher and Navigating the Home Page

 

1 .	Enter the URL for BI Publisher in the browser window, which is of the format- http://: /xmlpserver/. For example- http:/localhost:9704/xmlpserver/ This opens the Oracle BI Publisher Enterprise login page-
2 .	Login as a user with BI Administrator privileges. When you login to BI publisher, the Home page is displayed-
3 .	The Home page is a task-oriented, centralized workspace combined with a global header, allowing access to Oracle BI Publisher objects, their respective editors, and help documentation. Observe the various sections of the Home page ( greatly enhanced in 11g release to enable the users get started quickly with BI Publisher) : For example you can see - A Create section on the left that has options to help you create reports, data models, and other objects. A Browse/Manage section on the left to help you browse and manage the catalog, jobs, and job history. A Get Started section on the left to help you get started with BI Publisher, with links to various BI Publisher tools, Online Help, and Oracle Technology Network. A Recent section on the right, personalized to each user so that the users can open/view the reports that have been accessed recently (when you login initially, you may not see any objects listed in the Recent section). Also, try the enhanced Search capability that enables you to search for objects by type.
4 .	On the upper right side you can see the links for the Home page, Catalog, drop-down lists New, and Open. Note: All these features make the navigation within BI Publisher easy for report developers and users.

Browsing the Catalog and Viewing the Reports

1 .	Follow the steps listed here to browse the catalog and open a sample report (predefined). Click the Catalog link in the Browse/ Manage section ( or Click the Catalog link found on the right side of the Home page).
2 .	The Catalog page is displayed as a tree structure on the left side of the page with the details on the right. See that the objects such as reports, data models are organized into folders. Note: The Samples folder found in Shared Folders contains a set of predefined sample reports. You will open one of these reports.
3 .	Also see the tool bar with New drop-down menu that enables you to create new reports, scheduled jobs, data models and so on. Note that the tool bar also has other options such as copy, cut, paste, and other options that can be used when creating or managing the catalog objects such as data models, and reports.
4 .	Navigate to the Human Resources folder in Samples. (This displays all the objects in the Human Resources folder.) You can also search for a report (or other objects) using some key word and view it. For example, in the Catalog page, select the Reports check box from the Search drop-down list, and enter a key word in the Text field beside it. In the example below, the search results in a list of reports that have "Sales" in name.
5 .	Click Open link below the Salary Report.
6 .	The Salary Report is opened in view mode. The report data is displayed using the Default layout. All the predefined layouts for the report are displayed as different tabs. You can also view the report using any of the supported formats. Click View icon displayed on the top of the report viewer towards the right. (Highlighted in the screen below).
7 .	Also, note the parameters defined in the report -Department and Employee, that display drop down lists for department names and the employees working in the corresponding departments. For example, when you select Finance as the Department, and the Employee list shows the employees working in Finance department.
8 .	You can click Financial Style, and Pivot Table tabs to view the data in report using these predefined layouts. The following screens shows data for the Finance department using Financial Style layout: The followings screen displays data for the Sales department using Pivot Table layout.

Setting Preferences

1 .	To set the Preferences , Click on the link Signed in as , and select My Account from the drop-down list.
2 .	This displays the Preferences screen with the General, My Group tabs/ links as shown in the screen below: You can set the Report Locale, UI Language, TIme Zone, and Accessibility Mode in the General Preferences section. Report Locale- A locale is a language and territory combination (for example, English (United States) or French (Canada)). BI Publisher uses the report locale selection to determine the template translation to apply , the number formatting and date formatting to apply to the report data. .UI Language- The UI language is the language that your user interface displays in. The language that you selected at login will be selected as the default. However, you can choose from the languages that are available for your installation through this option. Time Zone - Select the time zone to apply to your reports. Reports run by you (this user) will display the time according to the time zone preference selected here. Accessibility Mode- Setting this to "On" will display the report catalog in a tree structure that is accessible via keyboard strokes Note: If running BI Publisher integrated with a Security Model other than BI Publisher, these preferences may be inherited from the other security model and you will not be able to change these values.
3 .	Click the My Groups tab to view a list of the application roles to which you are assigned. You cannot modify this list. To change your password, click the Password tab of the My Account dialog. Note: Password tab is visible only when you use BI Publisher Security.

Configuring the Data Sources

Types of Supported Data Sources

Oracle BI Publisher supports various types of Data Sources such as JDBC data sources (Oracle Database, and other Databases), XML Files, BI EE Reports (Answers), JNDI data sources, OLAP data sources, Fusion Application Data sources, Web Services, HTTP data sources, and Discoverer.
Before you create a data model based on these data sources, you need to configure a connection to these data sources first. In this topic, you will learn how to configure a JDBC connection to an Oracle Database, and also you learn how to define a File data source.
This topic has the following subtopics-

• Defining a JDBC Connection
• Configuring File Data Location
• Defining a JNDI Connection

Defining a JDBC Connection

1.	To define a JDBC connection click the Administration link found on the right side of the BI Publisher page. This displays the BI Publisher Administration page as shown below (Observe the Data Sources section in the screen below):
2.	Click the JDBC Connection link found under the Data Sources section in the Administration page.
3.	This displays the Data Sources page. In the JDBC section, click Add Data Source to create a JDBC connection to your database.
4.	In the Add Data Source page that is displayed, enter the details as given below - Data Source Name - OE Driver Type - Select a driver type to suit your Database (for example, you can select Oracle 10g or Oracle 11g to suit your Database). Database Driver Class - oracle.jdbc.driver.OracleDriver (Define a driver class to suit your Database) Connection String - Provide the database connection details. For example hostname:port:sid. User name - OE (Database user name) Password - OE (Database user password) Do not click Apply or Cancel after defining the above details. Click Test Connection. Note: To continue with this tutorial exercises, the sample schemas OE, and HR should be installed in your Oracle Database as mentioned in the Prerequisites. Else, you will not be able to define this JDBC connection.
5 .	If the connection to the database is established, a confirmation message is displayed indicating the success. ( As shown in the screen below). Then click Apply.
6 .	You can see this newly defined connection (OE) in the list of JDBC Data Sources.
	Note: You can also set up the the default connection demo to point to OE schema in your database, as the samples use this connection. Then the demo connection can be used as the default data source for this tutorial too.

Configuring File Data Location

1.	BI Publisher ships with a repository of various sample files and reports. These are available in BI Publisher home. To define a File data source, click the File tab in the BI Publisher Administration page. Click the demo files link to update the file location. ( If you want to create a File data source with a different name, you can click Add Data Source. )
2.	Define the top level directory which has all the sample files (repository). ( Using this demo files data source, users can access all the files in the top level directory and also it's subdirectories).

Defining a JNDI (Java Naming and Directory Interface) Connection

BI Publisher supports connecting to a JDBC data source via a connection pool. Using a connection pool increases efficiency by maintaining a cache of physical connections that can be reused. When a client closes a connection, the connection gets placed back into the pool so that another client can use it.
Note: JNDI Connection concept is briefly introduced here, as you need to first set up the connection pool in your application server and access it via Java Naming and Directory Interface (JNDI). Then, you can define the JNDI connection in BI Publisher by entering the required fields in the JNDI connection definition page, so that BI Publisher can utilize the pool to establish connections.

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
	Creating a SQL Query Data Set
	Viewing the XML Output and Saving the Sample Data

Defining Default Data Source, and Output Options

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.
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.
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, the Include Parameter Tags is checked, and the 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 OE 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.
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.

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 OE 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.
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 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 OE 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.

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:

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.)

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
	Working with Charts
	Adding a Data Table to the Layout
	Formatting the Data, and Working with the Page Layout Features

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 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 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.

Working with Charts

5.	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:
6 .	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.
7 .	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 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.
8 .	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.
9 .	Click Save As icon in the toolbar, and save the Layout as Departmental Salaries.
10 .	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: Select the cell beside the chart that you have inserted, and then click the Insert tab. Click Chart. To change the chart type to a pie chart, expand the Chart Types menu. Select the Pie chart to insert an empty pie chart with prompts in the grid cell. 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 Add Percentage Salary by Department as the Title. Use Tahoma as the font, 12 as the size, and Bold as the style. Align the title in the center. 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.

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 to save the Departmental Salaries layout. Note: You can edit the layout that is saved in one of the following ways: Click Actions and select Edit Layout in the view mode of the report or 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)

Formatting the Data, and Working with the Page Layout Features

Layout Editor enables you perform many formatting tasks such as -

• Change the back ground, text colors, and other text attributes
• Apply number formats
• Define groups/totals/ subtotals, and sorts
• Apply conditional formats
• Define page layout using headers, footers and page breaks.

Note: Most of the above formatting tasks can be done using the options in the menu ribbons. The most commonly used formatting properties are found in the context sensitive menu ribbons on the tool bar.  For more fine grained properties, you can use the property pane on the left.

1 .	Changing Background, Text colors, and other Attributes : In the table select a column header, for example DEPARTMENT NAME. From the menu ribbon, change the properties as below: Font to Arial, Font Size to 10, and Font Weight to Bold.
2 .	Click Background Color icon from the properties listed in the Appearance section. Select a color (light blue is selected in the example here) from the Color Picker screen and click OK. Similarly, click the Font color icon (highlighted in the screen) to change the text color for this column header. Select a color (like Dark blue) from the Color Picker. Change the background and text colors for all the column headers in-line with the DEPARTMENT_NAME. Hint: Use the and keys to select the remaining columns headers, and change the background and text colors to the same colors you used for the DEPARTMENT NAME column header. Similarly, select the column footers (totals ) and change the colors . Note that the Properties pane (on the left) changes accordingly: Similarly, apply same formats as above to the footer row (Totals row).
3 .	Change the all the column (data cells) fonts to Arial with size 10. Click Save to to save the Departmental Salaries layout. The table should appear like this after the above formatting changes:   You can also modify the other properties such as Text Alignment. Ensure that the data in the numeric columns - such as SALARY is justified to the right .
4 .	Adding Number Formats: You can add appropriate formats to the numeric or date columns such as SALARY, YEAR, HIRE DATE etc. To change the format for SALARY, click anywhere below the column header in the data area to select SALARY column. From the Data Formatting drop down list, that is displayed in the ribbon, select $1,234.57 (Currency) as the format. Similarly, select the same format for the Salary Total (last row). Click Save icon to save the changes to the Layout. Now, adjust the decimal places to the right. Click Move Right twice to remove the decimals in Salary column and the Totals. The table should now look like this (observe the formats for Salary column):
5 .	Similarly, click HIRE DATE column and apply the appropriate date formats: The table looks like this:
6 .	Defining Groups/Totals, and Sorts: In the table you can add groups, totals, and sorts. Click the data in the DEPARTMENT NAME column to select the same, and select Group Above from the Grouping drop-down list that appears in the Column menu. Note: Instead of using menu options, you can also drag DEPARTMENT NAME out of the table and drop it just above the table to add a repeating group. A total for the grouped column is automatically added at the end of the group in the table: Change the font, style, background and text colors as appropriate for DEPARTMENT NAME from the menu options in the ribbon. The table should appear like this after you defined a group on DEPARTMENT NAME column, and applied appropriate formats: Note: Every time you make changes, do not forget to click Save and save the changes to the template.
7 .	Click Preview in HTML icon to see the data in HTML. Scroll down to see the table:
8 .	You can also define sorts on the columns in the table. You can define an ascending or descending sorts on columns. Select the data in EMPLOYEE NAME column and click Ascending Order (A-Z) icon in the Sort menu. The products are arranged in alphabetical order. A portion of the data table, the data for the Purchasing department is shown below, note that the data is sorted by Employee Name: (You can also sort by salary, in which case a numeric sort is applied as appropriate.)
9 .	Applying Conditional Formats: Conditional formats help you to highlight the data in fields based on a condition. For example, you may want to do a pay revision to reduce attrition rates, and may want to see which employees are earning very good salaries, and which employees are not earning so well (say less than 3000 USD). To apply simple conditional formats on SALARY select the data in the column and from the Conditional Formatting menu, click Highlight.
10 .	In the Highlight screen that appears, select is less than or equal to from the Operator drop-down list. Enter 3000 as the Value. Set Bold as the Text style. Click the box beside the Background Color field. In the Color Picker, select Red as the background color. Click OK . This takes you back to the Highlight definition dialog, preview how conditional format looks like and click OK.
11 .	Define two more conditional formats as given below: 1. One with the Operator - is greater than, and enter the Value as 9000. Apply Green as the back ground color, and set Bold as font style. (See the screens below). Click OK. 2. Also define a conditional format with the Operator - is between , and enter the Values as 3000 and 5000 . Apply yellow as the background color, and Bold as font style. (See the screen below). Click OK. Click Save icon to save the Departmental Salaries template.
12 .	Click Preview in HTML icon to see the data in HTML ( a portion of the table data is shown below, note that all the three conditional formats that you defined are applied on the SALARY column) . Note: Some of the salient features of Layout Editor that are helpful in creating report templates are briefly covered here. If you have time, try experimenting with other components , and other features such as section, pivot table, gauges, images, and Group Left with subtotals.
13 .	Working with Page Layout Features: In the layout that you have created with the report components such as charts and table, you may want to add page breaks after each component, and in the table after each year. The next few steps guide you to add page breaks, headers , and/or footers to the report layout. In the grid layout , select the second row which has charts. Click Insert, and select Page Break from the Page Elements section in the menu.   Similarly, add a page break in the next row before the end of the section for DEPARTMENT NAME (group). Click inside the table just before the end of the section. Select Insert> Page break from the Page Elements section in the menu. (This inserts a page break after each department data in the table.)
14 .	To see if the page breaks are inserted at right places, preview the layout in PDF. Click Preview in PDF icon. Notice the page breaks in the PDF file: Scroll down to see the second page. Note that a page break is inserted after each department data:
15 .	To insert page/report header and footers, click Page Layout. This displays all the menu options for Page Layout in the ribbon. Click Report Header. A report header (place holder) is inserted into the layout. Insert a Text Item in the header, and double click the text item to edit it. Enter Employee Salary Analysis by Department as the report header. Apply appropriate formats for the report header. Preview the output in Interactive viewer. Observe that you can interactively select a department to view the data. Also, when you click the interactive ' v ' shaped icon for each column you can see the MS Excel like, interactive filtering and sorting features.
16 .	Similarly click Page Footer to insert a footer in the layout. An icon for the page footer at the end of the grid. Insert elements such as page numbers, simple text, time and date into these columns. An example is given here: First, select the page footer, and insert a Grid of a single row with 3 columns. Insert text items into each of these columns. Edit the first column in the Grid in the page footer item so that the text is in the format - " of >. Double click this text item in the first column, and click Page Number icon from the Text menu in the ribbon Beside the page number icon that is inserted into the Page Header, type "of " Now, click Page Total icon from the Text menu. Similarly, insert simple Text such as "Confidential" in the second Grid column, and Date and Time in the third column of the footer. The footer should look like this: The report with the headers and footers defined should look like this in PDF: Note: If you have time, you can experiment with various elements such as gauges, images etc.

Scheduling a Report

This topic guides you through the following subtopics:

• Confirming that the scheduler is configured
• Defining Delivery Destinations
• Creating Scheduled Report Jobs
• Managing Job History and Jobs

	Confirming that the Scheduler is Configured
	Defining Delivery Destinations
	Creating Scheduled Report Jobs
	Managing Job History and Jobs

Confirming that the Scheduler is Configured

Oracle BI Publisher Enterprise enables you to schedule reports, and deliver the executed output to various destinations. BI Publisher Scheduler is configured as a part of Oracle BI Enterprise Edition installation process. However, ensure that the scheduler is configured properly, before you start scheduling the reports.

1 .	Log in (if not logged in) to BI Publisher (Web) as a user with BI Administrator privileges. Click Administration link (found at the right top corner of the BI Publisher page): In the Administration page, click the Scheduler Configuration link in the System Maintenance section
2 .	In the Scheduler Configuration page that appears, click Test Connection in the Database Connection section to check if the scheduler is configured properly. This should display the following message indicating the connection was successful: Now, you are ready to schedule your reports.

Defining Delivery Destinations

Oracle BI Publisher enables you to setup different delivery destinations such as - Email, Printer, Fax, WebDAV, HTTP, and other destinations, so that you can deliver your reports to these destinations. This topic guides you how to set up your Email and WebDAV destinations using BI Publisher Administration page.

1 .	In the BI Publisher Administration page, ( you need to login to BI Publisher with BI Administrator privileges to access this page) you see a list of destinations in the Delivery section. Click Email link in the Delivery section.
2 .	In the Email configuration page that appears, Click Add Server.
3.	In the Add Server page that appears, Enter an appropriate name for the mail server in the Server Name field Enter the Email server in the Host field Enter the values in the following fields if applicable— General field: Port Security fields: User Name and Password Click Apply. Important Note: You must enter a unique name for each server regardless of the type (printer, fax, email, WebDAV, or FTP).
4.	Similarly, you can add a WebDAV server as one of the delivery destinations. Click WebDAV link in the Administration page, and click Add Server. Enter An Appropriate name in the Server Name field, and Host for the WebDAV server Enter the following fields if applicable— General fields: Port Security fields: Authentication Type (None, Basic, Digest) and Encryption Type (None, SSL). Proxy Server fields: Host, Port, User Name, Password, Authentication Type (None, Basic, Digest)

Creating Scheduled Report Jobs

 

1 .	Login (If not logged in) to BI Publisher and go to My Folders> Learn folder in Catalog. Click Schedule link found under the Employee Salaries by Department report. Note: If you do not see the Schedule link it is likely that you do not have Scheduler privileges or that the Scheduler is not configured properly.
2 .	This displays the Schedule Report Job page. In the General tabbed page, you can select report name and parameter values (select All as the value for the Department report parameter).
3 .	Click Output tab to define the output options. Ensure that "Use Bursting Definition to Determine Output& Delivery Destination" check box is not selected. Select the following options: Enter DeptSal as the Name, Select Departmental Salaries from the Layout drop-down list, and select an appropriate output format as PDF Accept default values for locale, calendar, and select an Appropriate time zone from the drop-down list Ensure that the Save Output check box is selected
4 .	Also, ensure that the Make Output Public option is not selected (if you select this option, all users can see the output of your scheduled report) , and Save Data for Republishing option is selected ( so that you can later republish the report with a different layout and output types). You can also add a delivery destination for the scheduled report. In the Destination section, select Email as the Destination Type and click Add Destination. This shows an Email section below the Destination section. Add the mail ids in the To, CC, and Reply To fields as required, add a Subject to the mail, and type the message. Note: For adding destinations for scheduled reports, you should have first configured them in the Administration page. You can define multiple outputs for a single scheduled job and deliver each to separate destinations. For example, you might want to deliver a PDF output as an attachment to an email to a list of managers. You might also want to deliver the Excel output of the same report to a content management server (via WebDAV) at the same time.
5 .	Click Schedule tab to define the scheduling options. Select Run Now option to run the job immediately. Select the Frequency as Once from the drop-down list. You can also add Email Notification options, (as you have already defined Email delivery destination). Click Submit to submit the schedule.
6 .	Enter Employee Salaries by Department-Sched as the Job Name and click Submit again. A message is displayed indicating that the job is submitted successfully. Click OK.
7 .	Scheduling a Report Job to run Weekly: Follow the steps 1 – 3 listed above to create this job. In step 4, click Schedule tab, and select the following options— Select Weekly from the Frequency drop-down list Ensure that you have entered option to run the report Every week Select Monday to run the report on every Monday In the Start and End fields enter the start and end dates for the report, and make sure that the time is 8 am Click Submit to submit the job, and after specifying a name for the job click Submit again. Select some of the different Frequency patterns available for scheduling the jobs. Note that the Specific Dates option allows you to create a completely custom pattern to meet whatever calendar requirements you have.

 

Managing Job History and Jobs

 

1 .	Click Open (found at the top right corner of the page) and select Report Job History. The Report Job History page displays information about running and completed report jobs. You can see the report job that you created in the list as shown in the screen below. ( Note that the job status indicates success). Click the job name link ( Employee Salaries by Department -Sched) . You can also enter appropriate Search criteria to search for reports or search for the job history for a specific report. Observe the various search criteria that you can use.
2 .	General job information and other job execution details are deplayed for the specific report. Click DeptSal (in the Output Name column) , to see the scheduled report output using the Departmental Salaries layout that you created. Click Return in BI Publisher. Note: You can also click the Republish option to republish the scheduled job with a different layout and output options.
3 .	Similarly, click Report Jobs to see the Manage Report Jobs page. This page page displays information about jobs that are scheduled for future dates, and also recurring report jobs. You can pause, resume, edit, or delete report jobs from this page.
4 .	Click in the column beside a job name to select that job .Click Pause to suspend the job. (Observe that the job status changes to Paused.) If you want to resume the same, click Resume. Similarly, click Delete if you would like to delete the job.

Summary

In this tutorial, you have learned how to:

• Configure Data Sources
• Create Data Model based on SQL query
• Create and view simple reports
• Build layouts using Layout Editor
• Schedule Report Jobs
• Manage Job History and Jobs