Business Intelligence Technology

Enhancing Oracle BI towards Mobile Technology

2011-01-15T06:32:00.000-08:00

Today's Oracle Business Intelligence enhanced towards mobile world,which is more usable and easy get connecting with enhanced way on the daily reports and dashboards view on the required information.

Oracle Business Indicators is a business intelligence application that provides real-time, secure access to business performance information on the mobile device.

Oracle Business Intelligence Suite Enterprise Edition Plus and/or Oracle Business Intelligence Applications, so that users can view and interact with pre-defined and customized financial, human resources, supply chain and customer relationship management analytics and business intelligence alerts

Offers a highly intuitive user interface designed specifically for the mobile business user to browse, search and locate business performance metrics and view metric details in an engaging display.

RPD Development Process and MUD environment

2010-08-31T04:08:00.000-07:00

The OBIEE 11g development has a special feature so before going new version , would like to take how to implement the general rpd development process.This processes also refers to MUD (Multi-User Environment) environment setup.

The MUD environment is done by creating Projects in the repository file in the BI Administration Tool and then copying this repository file to a shared network directory. Developers can check out projects, make changes, and then merge the changes into the master repository.

The entire concept of MUD (MutliUser Development) revolves around objects called as Projects. Projects are subsets of objects based on logical fact table that can be assigned to individual users. So, the idea is to assign different projects to different users. Also, each of these projects can contain one or more Logical Fact tables. As soon as a logical fact table is included all the other dependent objects would automatically be part of the project.

General RPD Development Process

In the Admintool, Go to File/Multiuser/Checkout, choose the project you want to work on for checkout.
AdminTool will then create a local copy of the extracted project in your local directory under (C:\Negril\orainst\bifoundation\OracleBIServerComponent\coreapplication_obis1\repository) with the name you provided during the extract (say localfile).
AdminTool will also create a new file called original.rpd
Work on your local copy(localfile.rpd), and make sure you save and run Global Consistency Check frequently.
Once you are satisfied with your work, and there are no Errors in the Global Consistency Check, go to File/Multiuser/ and choose the Merge Local Changes option.
This will start a Merge process which will merge your modifications into the main rpd file, taking into account changes other Engineers might have introduced to the same objects you have modified. This is a three-way merge process which will compare data from the master rpd, with data in your work rpd, and in the original file described in step 3.
This step will bring the full rpd locally in your screen, and give you a lock on that file. Only one person can check in at one time, so it is important you do this quickly and release the lock.
In the new screen that pops up, if the Merge option is greyed out, it means you need to make some Merge decisions. For all objects you have modified or your team owns and you are confident your changes should prevail, choose the Modified option. For all other objects, choose the Current option.
After all decisions have been specified, you should be able to click on the Merge button at the bottom of the screen.
Once the Merge is done, check all your changes in the full rpd. If you are satisfied, run a Global Consistency check one more time.
If everything is fine (no Errors), choose File\Multiuser\Publish to Network.
If there are any errors related to your own changes/metadata that you cannot fix, choose Discard local changes. This will release the main rpd without any changes. You then need to talk to your colleagues to see what is the problem with your metadata. You will need to redo your work after this step.
This is why it is better you checkin your work periodically, like once a day at least, so you don't loose much work if you have to discard any changes. We recommend developers should check in their work EVERYDAY.
No matter what happens, do not go home with the full rpd lock still maintained by you. Somebody will call you at any time of the day (or night) to release it . Or worse yet, The Administrator can remove your lock, and you will loose all your work.

MUD Best Practice process

The MUD utility allows us to support a large number of parallel users, but everybody needs to follow the same process when using this utility. Here are the general guidelines :

1. Every user should extract a project for himself to modify, and then check it in from the same environment directly in the master MUD env.
2. No user should overwrite the extracted copy with another one modified in a different machine or folder
3. Users should refresh their local copy frequently, to minimize the conflict decisions taken by the Merge engine during checkins. I suggest once every two to three days.
4. Every user should have one no more then one extracted project in his machine at any point of time. Please do not extract more then one project into the same environment. This can cause confusion.
5. If you find any object in your extracted project that you don't think should be part of the project, please do not delete it. It will get deleted from the main rpd in that case. You can simply log a bug against Admintool, and provide the master rpd, and the project name.
6. If you have any doubt about the changes you made to your local copy, please delete it and start over again, by taking a new extract. This is much cheaper for the team in the long run.
7. Run a Consistency Check on your local project before merging your changes. You should fix all Errors reported by the Consistency Check. If you cannot fix an error, please consult with your colleagues. In the past few weeks, a large number of the issues we had with checkin/checkout process have been due to bad code in the master rpd. We can avoid most of the downtime in our development if every team commits not to check in buggy code into the master repository

OBIEE 101341 installation issues ?

2010-08-25T08:58:00.000-07:00

500 Internal Server Error
OracleJSP: oracle.jsp.provider.JspCompileException:
Errors compiling:C:\obiee\OracleBI\oc4j_bi\j2ee\home\application-deployments\analytics\analytics\persistence\_pages\\_default.java

java.lang.NoClassDefFoundError: com/sun/tools/javac/Main
Caused by: java.lang.ClassNotFoundException: com.sun.tools.javac.Main
at java.net.URLClassLoader$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
Exception in thread "main" Could not find the main class: com.sun.tools.javac.Main. Prog

Cause of the error :
Could not create the Java virtual machine.
Error occurred during initialization of VM
Could not reserve enough space for object heap

resolution:

Change the parameter heap value in the server.xml file to 1024m to 256m.

please execute the following steps:

1. In server.xml (from ORACLE_HOME\j2ee\home\config\) find the following setting:

2. Change it to a lower value (ex. 256M):

3. Restart oc4j

It works now.

If you see issues again with the same jdk1.6 file then try coping the jdk1.5 file and then restart the services.

OBIEE 11gR1 - New

2010-08-20T00:49:00.000-07:00

The best of OBIEE version we see is from OBIEE 11gR1,where integration happened with Oracle Weblogic server and basics changes in OC4J,but more changes in the architecture which shows using WebLogic Server, the J2EE application server that Oracle have adopted following the acquisition is part of BEA.

In OBIEE 10g, we have OC4J is and Oracle Application server is limited use from the OPMN services and where in OBIEE 11gR1 new release the use of more Oracle weblogic server includes OPMN, DMS and ODL are much more prominent in use.In this release we have new features added with Oracle Enterprise Manager and Oracle Fusion Middleware Control.

2010-08-20T00:18:00.001-07:00

OBIEE 11gR1 : Implementation Process

2010-08-16T01:41:00.001-07:00

2010-08-16T00:10:00.001-07:00

Implementing and Supporting OBIEE application with Upgradation from OBIEE 10g to OBIEE 11g version having new features

2010-07-03T02:09:00.000-07:00

Scope of OBIEE 11g :
In a large volume of loaded data,which needs rich-in performance and where BI Publisher and other databases added new features and upgraded versions are integrated.OBIEE 11g is enhanced with high quality to have end-user experience and application users.

Implementing and Supporting OBIEE 11g:

This new add versions with Oracle Essbase and Oracle OLAP data sources helps application more user-friendly and complete integration can be done with in the OBIEE 11g and supporting it.Good implemented services are available to the customers with in OBIEE 11g.

Upgration OBIEE 10g to OBIEE 11g:

Easily assisted with upgradation from 10g to 11g and with integration other OLAP datasources.

OBIEE Metadata Repository Implementation for OLAP

2010-06-25T03:51:00.000-07:00

As we know the OBIEE metadata implementation which is enabled for access to Oracle Database 11g OLAP Option data and calculations by using the Oracle OLAP 11g Analytic Workspace Manager Plug-in for OBIEE.

Initially, would like take you to understand process of creating metadata repository file.And this explains how the connections are established and the configuration implemented using admin tool.

Understanding OBIEE Metadata Concepts:

In a simple way to understand the proces of implementation of OBIEE metadata repository in order to use any BI end-user tool that depends on its own metadata layer (which is the case with OBIEE), the metadata repository will explain how queries should be constructed against the relational data sources with Oracle OLAP data. We can implement the metadata for different databases sources. Lets take the process of implemetation of OBIEE metadata repository

Process for bringing data into OBIEE : Firstly we need to be concentrated to setup the data source.
Dimensions
Physical Layer
Use File.> Import from Server with OCI 10g/11g selected (avoids need to change the Connection Pool later)
TNS Name = Data source name on Connection Pool dialog
TNS Name = polydata.world

This will take you to configure the data sources connections.
Connection Pool
· Require fully qualified table names on connection pool (needed for proxy account since it has only read access to tables)

Create Primary Keys
· Verify that primary keys were imported and identified in the tables. If not, add them.
· Right-click table, select Properties
· Select the Keys tab
· If primary key is not marked or the wrong one is selected, modify the keys accordingly.
Create the Physical Diagram
· Click Foreign Join button, then click Dimension table and drag to Fact table
· For various DATES in the fact table, copy the TIME dimension 3 times with different alias names, then join to 3 different dates in the fact table (e.g., withdrawn_date, enrolled_date, dropped_date)

Business Layer
· Use Complex Joins to join new items created in the Business Layer, if any
· Aggregate items that can be summed
· Use Rename Wizard to rename fields and tables
One table at a time (in on-line mode)
Rules must be ordered, or perform one rule at a time
Check "Use existing logical column as the source"
Enter the number 1 in the formula box
Presentation Layer
· Group columns with subfolders
Create new Presentation Table as "– [Name]" (that's hyphen space name)
Creates a subfolder under the parent folder
It is also possible to do this with "->" in the Description, but this is less desirable because it is not visible in Administrator unless you are looking at the column's Properties
For Administrator convenience, use icons to relate items in Presentation and Business Model layers
Denormalized tables
· Physical layer
Several subject-related denormalized tables may be pulled in together
Create an alias of each denormalized table.In the Physical Diagram, use a simple join from the original table to the alias
The alias becomes the "fact" table
· Business Layer
Delete columns from the "fact" table that are not used for measures (all but the join column in most cases)
· Presentation Layer
Delete alias table (assuming it does not contain measures)

Create different folders (subject areas) for different denormalized tables so that people don't try using them together. (While this would not be allowed in Answers, trying to do so returns an error message that is confusing for the users).

Repository Documentation Utilities
· BI Administration Tool > Tools menu > Utilities
· For more information search Help
Rename Wizard
· Rename columns
· Remove underscore character

· Change all column names so that first letter of each word is uppercase and rest is lowercase.
Generating and Deploying a Metadata Dictionary
· Web-based XML file of information in the repository file.
Generating Documentation of Repository Mappings
· Use this tool to document the tables, columns, etc in the repository file.
· Produces csv or tab-delimited file.
· Only columns that are in the Presentation Layer will be included in the report.
· Run the utility after ALL columns are brought to the Presentation Layer and after running the Rename Wizard.
· Run the utility before fields (ex. Keys, Extract Date, Maint Date) are deleted from the Presentation Layer.
Verify Data Type
Before moving tables to the Business Model or Presentation Layer verify that the Data Type is correct for the columns. OBIEE may define the column as a different data type than what was intended or used in the Warehouse.
Change the Date Data Type
Most date columns come into OBIEE with a Data Type of DATETIME which produces a date format that includes the date and the time.
To remove the time from the field change the Data Type to DATE. This change will push through from the Physical Layer through the Business Model and Presentation Layers.
Dates are brought in as DATETIME.
· DATETIME = 1900/02/25 00:00:00
· DATE = 1900/02/25
Columns defined as Number will come into OBIEE as DOUBLE which includes two decimal places. If this is incorrect for the column select INTEGER.
· DOUBLE = 19000225.00
· INTEGER = 19000225
Note: Column Properties which include Style, Column Format, Data Format and Conditional Format are applied in Answers.
Where are system-wide changes stored?
System-wide changes are stored in the Web Catalog and must be moved from development to production servers along with requests and dashboards. These files are stored in folders separate from requests and dashboards.
Here are the steps to create Hierarchies
Firstly,Right-click dimension table and select Create Dimension at the bottom of the shortcut menu.and then a Hierarchy is created using the name of the dimension table followed by the word Dim.A Grand Total level and Detail level will be created. The Detail level will contain all of the columns of the dimension. Create child levels using names that match your hierarchy. Right-click the Detail level, select New Object, Child level.
Create a child level below the lowest child level (this will contain the dimension key.) Move the dimension key to this level. Right-click this key column and remove the checkmark next to Use for drilldown. Move other columns to their respective levels. Delete any unneeded columns from any level.Select columns within each level, right-click and select New Logical Level Key. Select all columns as keys if you want to see them in drilldown.For any levels that should be skipped in the drill-down, select all columns in the level, right-click and remove the checkmark next to Use for drilldown. At level two check for a Detail key that was automatically created. (Right-click level two, Properties, click the Keys tab). Highlight and delete Detail Key.

Hierarchy Aggregation
In the hierarchy, starting from the top and moving down. At each level below the Grand Total level, double-click the level. In the field Number of elements at this level: enter 10 and increase in increments of 10 through all levels in the hierarchy.

Building Hierarchies With Columns In Different Tables
This first step is needed if columns for hierarchy reside in multiple dimension tables or a dimension table and fact table or because the data model contains only one dimension table and no fact table.
1)Create a new or second dimension table from an already existing table. 2)Create an alias of the table in the physical layer.3)Right-click on table in Physical layer, select New object > Alias.4)Join the new dimension in the Physical diagram. 5)Select the data model, right-click Physical Diagram > Object(s) and All Joins.6)Click the New Foreign Join button. Click the new dimension table, then click the Fact table.
7)If you are joining a new dimension table to itself then create joins between all of the keys in both tables.
8)Click the first key in the table on the left. Find the matching key in the table on the right. Hold down the CTRL key and click the next key on the left and the matching key on the right. Continue until all keys are identified in the Expression box at the bottom of the Physical Foreign Key window. Click the OK button.
9)A message “ a matching table key doesn’t exist…” will appear. Click Yes.10)Check in changes.11)Drag the new dimension (Alias) table from the Physical Layer to the Business Model.12)Select the data model, right-click Business Model Diagram > Whole Diagram.13)Use the Complex Join button between the two tables. No changes are made on the Logical Join window. Click OK. Close the Diagram Window.14)The new dimension table now has a dimension table icon.15)Check in changes. Save repository.16)Cleanup on tables

· Remove all “Fact” columns from the new dimension table.
· Remove all dimension columns that don’t belong in the “Fact” table.
Assuming an item in the newly created dimension A that needs to be used for a hierarchy in a different dimension B:
Select items from physical layer table corresponding to A and drag to the B dimension in the Business Model.
Expand Sources for B table (click + ) then double-click on the B table source.

Click the Add button on the General tab.
In the box below name click the B table then click the Select button. This creates a join between those two dimensions.

Then move Items that you want in the B dimension from the Physical layer to the A dimension Source name.
This forces a logical join from the physical join between the dimension table and Fact table.
Changes Applied Through Answers
Formatting Data Values
Column Properties which include Style, Column Format, Data Format and Conditional Format are applied in Answers. When a certain format is desired for a column everytime the column is used then system-wide changes can be applied. System-wide changes can be saved at the column level or the data type level.
A perfect example for a system-wide change is the GPA (Grade Point Average). The field is defined with a Data Type of DOUBLE which gives you two decimal places but you need three decimal places in Answers and Dashboards.
1. Select the GPA column in Answers.
2. Select the Column Properties button.
3. Select the Data Format tab.
4. Click the Override Default Data Format checkbox
5. Change the number of Decimal Places.
6. Click the Save button and choose "as the system-wide default for "Student.GPA"

Request Filters
Protect Filters Passed through Dashboard
On the filter in the Answers Request, select Filter Options button > Protect Filter.
This ensures that the filter used in the request is not lost or overwritten by another filter or dashboard prompt that may supersede the request.
This option is only available if a value has been specified in the filter. If the filter item is set to "is prompted" then the Protect Filter option if not available.

2010-06-01T22:42:00.000-07:00

Integrating OBIEE and Hyperion 10.1.3.3.3/4.1 new features

2010-03-28T00:35:00.000-07:00

Process of Downgrading Repositories OBIEE 10.1.3.3.3/4.1

2010-03-27T23:28:00.000-07:00

This process implemented when you work with multiple environments.If you have build a environment with 10.1.3.3.3 OBIEE version and having the same version rpd file.
When you upgrade your OBIEE with in different environment with higher version for eg 10.1.4.1 then you cant use the higher version with in lower version of the admin tool but this can be done as viceversa.

[nqs Error:36010] server version 183 can not read the newer version 184 of the repository
c:\oracle BI\server\repository\EnterpriseBusinessAnalytics.rpd

You will hit an error saying above.This shows OBIEE version compatability when you create a RPD file in OBIEE 10.1.3.4.1 and put this file in production at an OBIEE 10.1.3.3.3 server.

We can have nQUDMLGen.exe and nQUDMLExec.exe utilities for convert rpd version.

The conversion of .rpd file into .txt file(by nQUDMLGen.exe) only on the system where you have the higher version of Oracle BI installed it should be mostly on windows box.

OBIEE DAC and ETL Process

2010-03-04T21:53:00.000-08:00

Though we have discussed earlier about the optimizing the ETL process on the performance of the data load.I would like to take you now at OBIEE DAC and ETL process, this will be
explaining mainly on data extract from legacy apps through a data connection from the ETL tool ,mappings and process of loading in to the datawarehouse.

ETL (Extract, Transform and Load):

This section of the project goes a step further in elaborating the Data Warehouse concept which was described in the previous section. Here, we will see how the tool INFORMATICA is used to extract data from Source(s), transform it and then load it into the Target. Data transformation is done to eliminate any erroneous or redundant data. This ensures that only the correct data is loaded into the Target (OLAP), which will be used for analysis / reporting.

Informatica PowerCenter architecture is used to achieve the extract, transform and load of data. PowerCenter provides an environment that allows you to load data into a centralized location, such as a datamart, data warehouse, or operational data store (ODS). You can extract data from multiple sources, transform the data according to business

logic you build in the client application, and load the transformed data into file and relational targets. PowerCenter provides the following integrated components

PowerCenter repository. The PowerCenter repository is at the center of the PowerCenter suite. You create a set of metadata tables within the repository database that the PowerCenter applications and tools access. The PowerCenter Client and Server access the repository to save and retrieve metadata.
PowerCenter Repository Server. The PowerCenter Repository Server manages connections to the repository from client applications. It inserts, updates, and fetches objects from the repository database tables. It also maintains object consistency.
PowerCenter Client. Use the PowerCenter Client to manage users, define sources and targets, build mappings and mapplets with the transformation logic, and create workflows to run the mapping logic. The PowerCenter Client has the following client applications: Repository Manager, Repository Server Administration Console, Designer, Workflow Manager, and Workflow Monitor.
PowerCenter Server. The PowerCenter Server extracts the source data, performs the data transformation, and loads the transformed data into the targets.

Sources

PowerCenter accesses the following sources:

Relational. Oracle, Sybase, Informix, IBM DB2, Microsoft SQL Server, and Teradata.
File. Fixed and delimited flat file, COBOL file, and XML.
Application. You can purchase additional PowerCenter Connect products to access business sources, such as PeopleSoft, SAP R/3, Siebel, IBM MQSeries, and TIBCO.
Mainframe. You can purchase PowerExchange for faster access to IBM DB2 on MVS.
Other. Microsoft Excel and Access.

Targets

PowerCenter can load data into the following targets:

Relational. Oracle, Sybase, Sybase IQ, Informix, IBM DB2, Microsoft SQL Server, and Teradata.
File. Fixed and delimited flat file and XML.
Application. You can purchase additional PowerCenter Connect products to load data into SAP BW. You can also load data into IBM MQSeries message queues and TIBCO.
Other. Microsoft Access.

You can load data into targets using ODBC or native drivers, FTP, or external loaders.

Repository

The PowerCenter repository resides on a relational database. The repository database tables contain the instructions required to extract, transform, and load data. PowerCenter Client applications access the repository database tables through the Repository Server.

You add metadata to the repository tables when you perform tasks in the PowerCenter Client application, such as creating users, analyzing sources, developing mappings or mapplets, or creating workflows. The PowerCenter Server reads metadata created in the Client application when you run a workflow. The PowerCenter Server also creates metadata, such as start and finish times of a session or session status.

You can develop global and local repositories to share metadata:

Global repository. The global repository is the hub of the domain. Use the global repository to store common objects that multiple developers can use through shortcuts. These objects may include operational or Application source definitions, reusable transformations, mapplets, and mappings.
Local repositories. A local repository is within a domain that is not the global repository. Use local repositories for development. From a local repository, you can create shortcuts to objects in shared folders in the global repository. These objects typically include source definitions, common dimensions and lookups, and enterprise standard transformations. You can also create copies of objects in non-shared folders.
Version control. A versioned repository can store multiple copies, or versions, of an object. Each version is a separate object with unique properties. PowerCenter version control features allow you to efficiently develop, test, and deploy metadata into production.

You can connect to a repository, back up, delete, or restore repositories using pmrep, a command line program.

You can view much of the metadata in the Repository Manager. The Informatica Metadata Exchange (MX) provides a set of relational views that allow easy SQL access to the Informatica metadata repository.

Repository Server

The Repository Server manages repository connection requests from client applications. For each repository database registered with the Repository Server, it configures and manages a Repository Agent process. The Repository Server also monitors the status of running Repository Agents, and sends repository object notification messages to client applications.

The Repository Agent is a separate, multi-threaded process that retrieves, inserts, and updates metadata in the repository database tables. The Repository Agent ensures the consistency of metadata in the repository by employing object locking.

PowerCenter Client

The PowerCenter Client consists of the following applications that you use to manage the repository, design mappings, mapplets, and create sessions to load the data:

Repository Server Administration Console. Use the Repository Server Administration console to administer the Repository Servers and repositories.
Repository Manager. Use the Repository Manager to administer the metadata repository. You can create repository users and groups, assign privileges and permissions, and manage folders and locks.
Designer. Use the Designer to create mappings that contain transformation instructions for the PowerCenter Server. Before you can create mappings, you must add source and target definitions to the repository. The Designer has five tools that you use to analyze sources, design target schemas, and build source-to-target mappings:

Source Analyzer. Import or create source definitions.
Warehouse Designer. Import or create target definitions.
Transformation Developer. Develop reusable transformations to use in mappings.
Mapplet Designer. Create sets of transformations to use in mappings.
Mapping Designer. Create mappings that the PowerCenter Server uses to extract, transform, and load data.

Workflow Manager. Use the Workflow Manager to create, schedule, and run workflows. A workflow is a set of instructions that describes how and when to run tasks related to extracting, transforming, and loading data. The PowerCenter Server runs workflow tasks according to the links connecting the tasks. You can run a task by placing it in a workflow.
Workflow Monitor. Use the Workflow Monitor to monitor scheduled and running workflows for each PowerCenter Server. You can choose a Gantt chart or Task view. You can also access details about those workflow runs.

PowerCenter Server

The PowerCenter Server reads mapping and session information from the repository. It extracts data from the mapping sources and stores the data in memory while it applies the transformation rules that you configure in the mapping. The PowerCenter Server loads the transformed data into the mapping targets.

The PowerCenter Server can achieve high performance using symmetric multi-processing systems. The PowerCenter Server can start and run multiple workflows concurrently. It can also concurrently process partitions within a single session. When you create multiple partitions within a session, the PowerCenter Server creates multiple database connections to a single source and extracts a separate range of data for each connection, according to the properties you configure.

Connectivity

PowerCenter uses the following types of connectivity:

Network protocol
Native drivers
ODBC

The PowerCenter Client uses ODBC and native drivers to connect to source and target databases. It uses TCP/IP to connect to the Repository Server. The Repository Server uses native drivers to connect to the repository database. The Workflow Manager and the PowerCenter Server use TCP/IP to communicate with each other.

The PowerCenter Server uses native drivers to connect to the databases to move data. You can optionally use ODBC to connect the PowerCenter Server to the source and target databases. It uses TCP/IP to connect to the PowerCenter Client.

Database Connections

The Repository Server maintains a pool of reusable database connections for serving client applications. The server generates a Repository Agent process for each database. The Repository Agent creates new database connections only if all the current connections are in use.

For example, if 10 clients send requests to the Repository Agent one at a time, the agent requires only one connection. It reuses the same database connection for all the requests. If the 10 clients send requests simultaneously, the Repository Agent opens 10 connections. You can set the maximum number of open connections using the DatabasePoolSize parameter in the repository configuration file.

For a session, a reader object holds the connection for as long as it needs to read the data from the source tables. A writer object holds a connection for as long as it needs to write data to the target tables.

The PowerCenter Server maintains a database connection pool for stored procedure or lookup databases in a workflow. You can optionally set the MaxLookupSPDBConnections parameter to limit connections when you configure the PowerCenter service. The PowerCenter Server allows an unlimited number of connections to lookup or stored procedure databases. If a database user does not have permission for the number of connections a session requires, the session fails.

For pre-session, post-session, and load-stored procedures, consecutive stored procedures reuse a connection if they have identical connection attributes. Otherwise, the connection for one stored procedure closes and a new connection begins for the next stored procedure.

PowerCenter Metadata Reporter

You can use PowerCenter Metadata Reporter, a web-based application, to run prepackaged dashboards and reports against PowerCenter repository metadata. These reports help give you insight into your repository, which enhances your ability to analyze and manage your repository efficiently.

You can run PowerCenter Metadata Reporter from a browser on any workstation, even a workstation that does not have PowerCenter tools installed.

Repository Server Administration Console:

Use the Repository Server Administration Console to administer Repository Servers and repositories. A Repository Server can manage multiple repositories. You use the Repository Server Administration Console to create and administer the repository through the Repository Server.

You can use the Administration Console to perform the following tasks:

Add, edit, and remove repository configurations.
Export and import repository configurations.
Create a repository.
Promote a local repository to a global repository.
Copy a repository.
Delete a repository from the database.
Back up and restore a repository.
Start, stop, enable, and disable repositories.
Send repository notification messages.
Register and unregister a repository.
Propagate domain connection information for a repository.
View repository connections and locks.
Close repository connections.
Register and remove repository plug-ins.
Upgrade a repository.

Administration Console Windows

The Administration Console can display the following windows:

Console Tree. Repository Servers and managed repositories. The Administration Console displays a different set of Action menu items depending on which node you select in the Console Tree. You can also right-click a node to access the Action menu items.

The Console Tree contains the following nodes:

PowerCenter Repository Servers
Repository Server name
Repositories
Repository name
Connections
Locks
Activity Log
Backups
Packages

Main. The Main window displays details of the node you select in the Console Tree. For example, if you select a repository in the Console Tree, the Main window displays the properties of the repository, such as the status and start time.

The Main window displays results in the following views:

List view. Displays a collection of items that includes an icon and a label.
HTML view. Displays repository information as a dynamic HTML page. The Administration Console only displays repositories in HTML view.

Repository Manager

Use the Repository Manager to administer your repositories. The Repository Manager allows you to navigate through multiple folders and repositories, and perform the following tasks:

Manage the repository. You can perform repository management functions, such as copying, creating, starting, and shutting down repositories. You launch the Repository Server Administration Console to perform these functions.
Implement repository security. You can create, edit, and delete repository users and user groups. You can assign and revoke repository privileges and folder permissions.
Perform folder functions. You can create, edit, copy, and delete folders. Work you perform in the Designer and Workflow Manager is stored in folders. If you want to share metadata, you can configure a folder to be shared.
View metadata. You can analyze sources, targets, mappings, and shortcut dependencies, search by keyword, and view the properties of repository objects.

Repository Manager Windows

The Repository Manager can display the following windows:

Navigator. Displays all objects that you create in the Repository Manager, the Designer, and the Workflow Manager. It is organized first by repository, then by folder and folder version. Viewable objects include sources, targets, dimensions, cubes, mappings, mapplets, transformations, sessions, and workflows. You can also view folder versions and business components.
Main. Provides properties of the object selected in the Navigator window. The columns in this window change depending on the object selected in the Navigator window.
Dependency. Shows dependencies on sources, targets, mappings, and shortcuts for objects selected in either the Navigator or Main window.
Output. Provides the output of tasks executed within the Repository Manager, such as creating a repository.

Repository Objects

You create repository objects using the Repository Manager, Designer, and Workflow Manager Client tools. You can view the following objects in the Navigator window of the Repository Manager:

Source definitions. Definitions of database objects (tables, views, synonyms) or files that provide source data.
Target definitions. Definitions of database objects or files that contain the target data.
Multi-dimensional metadata. Target definitions that are configured as cubes and dimensions.
Mappings. A set of source and target definitions along with transformations containing business logic that you build into the transformation. These are the instructions that the PowerCenter Server uses to transform and move data.
Reusable transformations. Transformations that you can use in multiple mappings.
Mapplets. A set of transformations that you can use in multiple mappings.
Sessions and workflows. Sessions and workflows store information about how and when the PowerCenter Server moves data. A workflow is a set of instructions that describes how and when to run tasks related to extracting, transforming, and loading data. A session is a type of task that you can put in a workflow. Each session corresponds to a single mapping.

The goal of the design process is to create mappings that depict the flow of data between sources and targets, including changes made to the data before it reaches the targets. However, before you can create a mapping, you must first create or import source and target definitions. You might also want to create reusable objects, such as reusable transformations or mapplets.

Perform the following design tasks in the Designer:

Import source definitions. Use the Source Analyzer to connect to the sources and import the source definitions.

Create or import target definitions. Use the Warehouse Designer to define relational, flat file, or XML targets to receive data from sources. You can import target definitions from a relational database or a flat file, or you can manually create a target definition.

Create the target tables. If you add a target definition to the repository that does not exist in a relational database, you need to create target tables in your target database. You do this by generating and executing the necessary SQL code within the Warehouse Designer.

Design mappings. Once you have source and target definitions in the repository, you can create mappings in the Mapping Designer. A mapping is a set of source and target definitions linked by transformation objects that define the rules for data transformation. A transformation is an object that performs a specific function in a mapping, such as looking up data or performing aggregation.

Create mapping objects. Optionally, you can create reusable objects for use in multiple mappings. Use the Transformation Developer to create reusable transformations. Use the Mapplet Designer to create mapplets. A mapplet is a set of transformations that may contain sources and transformations.

Debug mappings. Use the Mapping Designer to debug a valid mapping to gain troubleshooting information about data and error conditions.

Import and export repository objects. You can import and export repository objects, such as sources, targets, transformations, mapplets, and mappings to archive or share metadata.

Designer Windows

You can display the following windows in the Designer:

Navigator. Connect to repositories, and open folders within the Navigator. You can also copy objects and create shortcuts within the Navigator.
Workspace. Open different tools in this window to create and edit repository objects, such as sources, targets, mapplets, transformations, and mappings.
Output. View details about tasks you perform, such as saving your work or validating a mapping.
Status bar. Displays the status of the operation you perform.

Overview. An optional window to simplify viewing a workspace that contains a large mapping or multiple objects. Outlines the visible area in the workspace and highlights selected objects in color.
Instance data. View transformation data while you run the Debugger to debug a mapping.
Target data. View target data while you run the Debugger to debug a mapping.

Loading Data:

In the Workflow Manager, you define a set of instructions to execute tasks, such as sessions, emails, and shell commands. This set of instructions is called a workflow.

After you create a workflow in the Workflow Designer, the next step is to add tasks to the workflow. The Workflow Manager includes tasks, such as the Session task, the Command task, and the Email task so you can design your workflow. The Session task is based on a mapping you build in the Designer.

You then connect tasks with links to specify the order of execution for the tasks you created. Use conditional links and workflow variables to create branches in the workflow.

When the workflow start time arrives, the PowerCenter Server retrieves the metadata from the repository to execute the tasks in the workflow.

You can monitor the workflow status in the Workflow Monitor.

Workflow Manager

The Workflow Manager consists of three tools to help you develop a workflow:

Task Developer. Create tasks you want to accomplish in the workflow in the Task Developer.
Workflow Designer. Create a workflow by connecting tasks with links in the Workflow Designer. You can also create tasks in the Workflow Designer as you develop the workflow.
Worklet Designer. Create a worklet in the Worklet Designer. A worklet is an object that groups a set of tasks. A worklet is similar to a workflow, but without scheduling information. You can nest multiple worklets inside a workflow.

Before you create a workflow, you must configure the following connection information:

PowerCenter Server connection. Register the PowerCenter Server with the repository before you can start it or create a session to run against it.
Database connections. Create connections to source and target systems.

Other connections. If you want to use external loaders or FTP, you configure these connections in the Workflow Manager.

Workflow Manager Windows

The Workflow Manager displays the following windows to help you create and organize workflows:

Navigator. Allows you to connect to and work in multiple repositories and folders.
Workspace. Allows you to create, edit, and view tasks, workflows, and worklets.
Output. Displays messages from the PowerCenter Server and the Repository Server. The Output window also displays messages when you save or validate tasks and workflows.
Overview. An optional window that makes it easier to view workbooks containing large workflows. Outlines the visible area in the workspace and highlights selected objects in color. Choose View-Overview Window to display this window.

Workflow Monitor

After you create a workflow, you run the workflow in the Workflow Manager and monitor it in the Workflow Monitor. The Workflow Monitor is a tool that displays details about workflow runs in two views, Gantt chart view and Task view. You can monitor workflows in online and offline modes.

The Workflow Monitor consists of the following windows:

Navigator window. Displays monitored repositories, servers, and repositories objects.
Output window. Displays messages from the Power Center Server.
Time window. Displays progress of workflow runs.
Gantt Chart view. Displays details about workflow runs in chronological format.
Task view. Displays details about workflow runs in a report format.

ETL Process Flow (Data Warehouse Console)

A set of ETL (Extract –Transform-Load) process that takes from the Siebel eBusiness application transactional data (OLTP) and creates the Siebel Data Warehouse tables.

The Data Warehouse Application Console a centralized console for the setup, configuration, administration, loading, and monitoring of the Siebel Data Warehouse and it is referred to as the Siebel relationship management (SRMW).

Siebel DAC was constructed using dimensional modeling techniques to allow for fast access to information required for decision making. Siebel DAC derives its data from Siebel operational applications, and uses Informatics’ data integration technology to extract, transform, and load data from the Siebel transactional database in to the Siebel warehouse.

Siebel Data Warehouse Console (DAC) Architecture components:

DAC Client: A command control interface for the data warehouse to allow for set up, configuration administration, and monitoring of data warehousing processes, and customizes SRMW ETL objects and processes.

DAC Server: Executes the instructions from the DAC Client .The DAC server manages data warehouse processes, including scheduling loading the ETL and configuring the subject areas to be loaded. It dynamically adjusts its actions based on information in the

DAC repository. Depending on your business needs, you might incrementally refresh the Siebel data warehouse once a day, once a week, once a month or on similar schedule.

DAC Repository: Unique to each warehouse, stores the metadata (Semantics of the Siebel Data Warehouse) that represents the data warehouse processes, used by DAC Client and server to configure and schedule ETL execution plans.

Informatica Server: When the server is loading or refreshing the Siebel data warehouse, it is recommended that the server be dedicated to that activity, however when the ETL server is not loading or refreshing the Siebel data warehouse you can use it for other purposes.

Informatica Repository Server: Accepts connections to the Informatica repository from Informatica server and client. Manages the Informatica Repository.

Informatica Repository: Stores the metadata related to Informatica workflows specifying data transformation and flow used by Informatica server in ETL Processing.

Informatica Client utilities: Tools that allow you to create and manage the Informatica Repository.

DAC Loading Process

Informatica server performs ETL using source-to-target mappings and transformations

Extraction – Source is Siebel Transactional database

Transformation – cleaning, translations, aggregation (facts)

Load – target is SRMW dimensional schemas.

Step 1:Load staging tables

Ø Compares data for incremental updates and populates S_ETL_I_IMAGE* tables

Ø Collects and consolidates data from the transactional system and writes to the SRMW staging tables.

Step 2: Transform Data

Ø Transform data in staging tables in preparation for load

Ø Computes calculated fields

Step 3: Load dimension and fact tables

Ø Generates and maintains warehouse primary keys

Ø Loads hierarchy tables

Ø Loads dimension and fact tables

PMCMD path is used by the DAC server to issue commands to the informatica server

to run the ETL workflows.

Select start >settings >control panel> system.

In the advanced tab of the system properties dialog box edit the PATH system variable

To include the path to the PMCMD command

Ø PMCMD is located in the bin directory of the informatica server

Ø Eg C;\Program Files \Informatica\Informatica PowerMart 6.2.1

OEM for siebel –Server\bin.

Informatica command is used by DAC server to intiate the worlflows in the informatica repository.

Operating System, Drivers, and Connectivity:

RUNNING ETL’s

Set system prefreneces

Ø DAC Client

Run expection reports

Run ETL execution plan

DAC server runs tasks based on the ETL phase and task precedence

Ø Most ETL tasks call informatica workflows

Ø Workflows are run in parallel on informatica server(s)

DAC Tasks

Tasks are stored in the DAC repository, they are 4 types.

Ø Informatica: Calls an informatica workflow used to process ETL

Ø External Program: Calls an operable program on the operating system where DAC server is running

Ø SQL File: Calls an SQL script in .XML or .sql format.

Ø Sterd procedure: Calls a stored procedure defined on the DAC repository’s defined databases.

Phases:

The DAC phases are from the High to low priority

Ø Image build: Manage change capture tables for incremental loads using DAC tasks and source dependent incremental (SDEINC) Informatica mappings.

Ø General: Loads internal tables with required data.

Ø Extract Dimension: Use source dependent Extract (SDE) Informatica mappings to extract data from siebel transactional tables and load dimension staging tables.

Ø Extract Fact: Use SDE mappings to extract data from siebel transactional tables and load fact staging tables, and SDE is used for full and incremental source data extraction.

ETL Processing tables:

Ø Internal tables:

These tables are located in the SRMW and assist in the load process and contain metadata for the data warehouse.

W_PARAM_G stores parameters and defaults used in ETL, W_RUN_ETL _S stores a record per every ETL runs with a status of Running when ETL is running and completed when ETL Finishes Successfully.

W_EXCH_RATES_G stores exchange rates.

Ø Change capture tables:

Stores information about the changes to data in transactional system

Exist in the Siebel trasactional DB.

Used during incremental updates of the data warehouse.

Each source (S_) table processed in ETL,

One type R (S_ETL_R_IMG_*) table

Type R change capture tables maintain a record of all data extracted to the warehouse, storing three fields

ROW_ID - Siebel row ID of record extracted to SRMW.

MODIFICATION_NUM - Number of times the data has been modified

LAST_UPDATED – Used to maintain Row Image table and the records are deleted from a Row Image table when they go beyond the prune days period, and ensure that records falling in the Prune days are not captured unless they have really been updated.

Ø One type I (S_ETL_I_IMG_*) table

Similar structure to Row Image tables, with one additional field

OPERATION – Reserved for future use and the naming convention is S_ETL_I_IMG_*

Ø One type D (S_ETL_D_IMG_*) table

Similar structure to Row Image tables, maintains a list of row identifiers that have been deleted from the transactional database and naming convention is S_ETL_D_IMG_*

Ø Staging tables

Ø Hierarchy tables

Runs the Cmd Line on Analytics servers

This script uses the following DAC cmd line commands.

dacCmdLine.sh startetl

dacCmdLine.sh etlstatus

DAC Client for restarting ETL or Monitoring ETL

• Once Logged in navigate to the Execute Screen as shown below:

DAC User Interface

Look at the color of the icon in the upper right-hand corner of the DAC client: Green-DAC Server is up and tasks are running, Yellow/Orange-DAC Server is idle (up but no tasks are running) and Red-DAC Server is not running..

DAC Views

Includes

1.Design

2.Setup

3.Execute

Design View:

Provides access to create and manage execution plans and related entities.

Top Pane contains tabs, each of which represents a component of the execution plan in decending order.

Bottom Pane contains child tabs that relate to the tab selected in the top pane.

Exploring a Task:

The Load into voice Fact task loads the invoice fact table after its data has been extracted by the Extract for invoice Fact.

Source and Target tables child Tabs

The main source table is the invoice Fact staging table populated by the corresponding SDE task.

Its main target table is the Invoice star’s fact table.

The load in to Invoice Fact task cannot load data before data has been extracted and loaded into its source-staging table.

It depends on the extract for Invoice Fact task.

Dependent Tasks Child Tab

Loads in to Invoice Fact is a dependent task for Extract for Invoice Fact, and it can be specified in either tab depending on which task you are working on …

Tables Tab:

W_INVOICE_F is a member of the INVOICE FACT GROUP

Table Groups Tab

Used mainly to create and edit table groups.

Subject Areas Tab

INVOICE FACT GROUP is a member of the all subject Areas subject area.

Execution Plans Tab

DAC is used to launch the schedule execution plans to execute the mappings that load the SRMW.

Full and refresh load execution plans include

Ø Exeception Reports:

Produces an exception report that checks the data integrity of the siebel transactional database.

Should be run before a load or update of the SRMW.

Ø Complete ETL

Performs a full or incremental load of the SRMW.

All subject Areas is a member of the complete ETL execution plan.

Load in to Invoice Fact task will execute whenever the complete ETL execution is run.

ETL Process with custom workflows:

Run whole ETL process in a single sweep for both siebel and external data sources because ETL_PROC_WID has to be shared.

Firstly need to follow steps where the Importing process from workflow to DAC.

Ø Create new workflows and sessions for the custom mappings

Ø Create new tasks in the DAC for custom SDE and SIL workflows.

Ø SRMW extension tables are defined as tables in DAC and included in respective table groups. Here custom fact and dimension tables must be added.

STEP 1 Import Proces Workflow to DAC:

In the Design View create a new task in Task Definations tab.

then go to Edit child tab, click the picklist icon in the Command for incremental Load and Command for Full Load fields to pick the workflow used with the custom mappings.

Next, Select Choose Command for Load dialog box, select the database connection to the database containing the Informatica repository.

Next, Select Importing Informatica Sessions dialog box, select the database connection to the database containing the Informatica repository.

STEP 2 Creating Indexes :

Ø Staging tables typically do not require indexes

Ø Create indexes on all columns ETL uses for dimensions and facts

ROW_WIDs of dimensions and facts.

INTEGRATION_ID

DATASRC_NUM_ID

Flags

Before going with creating Indexes, We need to identify the columns very carefully to put filter conditions.

Next, Define Indexes to improve query performance, using with standard repository objects.

Next, Register the new indexes in DAC

DAC server drops and recreates indexes during full load.

STEP 3 Custom Mapping Templates:

The custom mapping templates can be customized to populate SRMW extension tables and columns.

There are few custom tables and columns provided in the custom folder.

Ø CustomTemplateSDE_WaveDimension

Here, SDE mapping that populates the ATTRIB_01 column of the staging extension table W_WAVE_DSX with data from the DT_LAG_UNITS_CD column in S_DD_CAMPWAVE.

Ø CustomTemplateSIL_WaveDimension

SIL mapping that populates the ATTRIB_01 column of the target extension table W_WAVE_DX with Date Lag Units Code data from the staging extension table W_WAVE_DSX.

Ø CustomTemplateSDE_AssetFact

SDE mapping that populates the FK_01_ID column of the staging extension table W_ASSET_FSX with data from the OWNER_ACCT_ID column in S_ASSET.This is used as a foreign key to the parent dimension table, W_ORG_D.

Ø CustomTemplateSIL_AssetFact

SIL mapping that populates the FK_01_WID column of the extension table W_ASSET_FX with data from the ROW_WID column in W_ORG_D.

Dependency Report:

Reports dependencies between tasks that are executed for the selected entity

To generate a dependency report, select an entity and click the preview button in the Design View.

Ø Available for table groups, subject areas,and execution plans.

Setup View:

This provides access to set up DAC system properties, Informatica servers, database connections, and email notification.

DAC System Properties Tab is used to enter property values.

Database Connections Tab is used to check the DB connectivity and the OWNER type.

Resetting the SRMW :

To delete Refresh Dates and run a full load of the SRMW .

Ø Select Tools > ETL Management > Reset Data Warehouse

Ø Confirm that you want to perform a full truncate and load.

Execute View:

The Execute view provides access to run, schedule and monitor execution plans and also be run manually from design view.

• To kick off a new ETL ensure Complete ETL is selected and press the “Run Now” button displayed below :

Running and Viewing Exception Reports:

Exception Reports are from the DAC before executing the intial load of the data warehouse.

Ø Checks the data integrity of the Siebel transactional database

Ø Produces an exception report.

Ø Can be run either before or after the intial load.

Navigate to Anlaytics Administration and select Exception Reports to view the report.

Check for Run Now:

Here the important task is firstly to check the connectivity ,above there is an yellow colored pluging then you can go its Connected.If its in red in color, then its Disconnected.

Ø Verify the Informatica serice is running .

Ø Start the DAC client and log in to the DAC repository.

Ø Run the Compelte ETL execution plan and monitor its progess.

• Move to Current Run Tab and setup auto-refresh :

• You will see the ETL Running in the top Applet , if the ETL has failed you will see this in a failed state here :

Move to “Task Details” in Bottom Applet and select a state to monitor ( if dealing with a failed ETL you must look for the failed tasks here.

• For Error checking on failed Tasks scroll across to “Status Description” column and click on the ellipse to pull up details of the failed task.

• To Restart a failed task(s) Click on the ellipse in the “Status” column and change state from “Failed” to “Queued” then save and wait for refresh.

• For a failed ETL you must click start on the Top Applet to restart after you have completed the above steps.

Bulk Load

Bulk load is an option that dramatically improves performance during a full load of the SRMW.It allows the informatica server to bypass the database log when enabled.

And it disbles rollback capabilities for the database.

This is to set the bulk load option for a session in workflow manager, where it imporves the performance level of the DAC process. There is an option provided for bulk load under the workflow manager.

Setting the Bulk Load option in workflow Manager:

Ø Drag the workflow into workflow Designer workspace

Ø Right-Click the session and select Edit.

Ø Click the Targets tab in the Edit Tasks dialog box.

Ø Click the Properties tab.

Ø For the Target load type attribute, select Bulk.

Troubleshooting the Bulk Load Process

Ø If the load process fails: Check the logs for the issue, it pinpoints the place where exactly the process get failed. These problems encountered during the ETL session execution appear in the DAC server log and Informatica logs.

Ø Restart the Execution Plan :If the problem persists repeat the preceding steps until successfully loaded.

Select the failed execution plan in the Run History tab.

Next Click Start.

The Start process Restarts from the point of failure.

Pruning ETL:

Prune ETL is used for Reducing ETL Processing and to load a limited number of required star schemas and it reduces the ETL execution time thereby improve performance.

Ø The complete ETL execution plan populates the entire set of star schemas.

Ø Business requirements generally eliminate the need for data in one or more star schemas.

Ø Identify DAC objects associated with required tables and prune those that are unrelated.

Occurances:

If customer satisfaction is not going to be analyzed ,there is no need to run tasks used to load the survey tables.

Checking SIEBEL ANALYTICS through SIEBEL CRM APPLICATION

Log into CRM Siebel using your intranet ID:

Navigate to Site Map => User Administration => Employees and query for your own CRM ID

Using Intranet

then SUBMIT

When your Profile is selected clicks on the Responsibilities ellipse, and query for and add

The following Responsibilities:

When this is done you need to save the record and Logout clear your browser cache and Login again.

You will then see the following Screen tabs as you scroll along the top :

You can then navigate to Sales Analytics Screen, you should see similar to below:

Import Mapping VisibilityAccountParty

Store the VisibilityAccountParty.XML file from CMVC in your local directory C:/Analytics/VisibilityAccountParty.XML
Login to Designer
Open the Folder SDE
Click on Repository à Import Objects
Select the file as follows C:/Analytics/VisibilityAccountParty.XML
Select Mapping VisibilityAccountParty and Click on Add

Click on Import

Chose Replace Option
Click on Repository à Save
Login to Informatica Workflow manager
Open the folder SDE
Validate the workflows VisibilityAccountParty_Full and VisibilityAccountParty by dragging to the window on the right side and Right Clicking à Validate
Click on Repository à Save

UPDATE DAC Task

Login to DAC Repository
Go To Design View
Do a Query for Task - Extract for Account Party
Check the Truncate Always option as shown in the screen below.

Performance with ETL:

Load Performance

Siebel customers using the Industry Vertical versions of Siebel Analytics are loading

far more fields than customers using the horizontal versions of Siebel. The affect of

large numbers of records and the fact that these records contain far more fields can

significantly impact performance. In addition to loading the Industry Verticals field

mappings instead of the Horizontal field mappings, the number of unused indexes

being generated at load time can also impair performance.

Before loading the data warehouse, records must be read from the Siebel OLTP and

from the staging area where these records are temporarily stored. Once read these

records are then loaded into the data warehouse. Extensive analysis was performed

at the customer site to determine the bottlenecks occurring in the whole process.

The following table contains this analysis and will be used as reference in the

discussion to follow.

The table contains the analysis performed on one table that was taking several hours

to load. Conclusions based on analyzing this table were applicable to several other

tables at the customer site and may be used as a reference for your own data

loading analysis. The LENGTH is how long it took to load the records; RATE is the

number of records loaded per second. FF refers to flat file, while DB refers to

database. The No Xform indicates a loading of data with no mapping

transformations done on the data. This indicates the significant impact the

transformations have on loading the data.

Reading and writing to flat files sets a “best possible” base line as these are the

fastest to read from and write to. It gives a reference point for what might be

achievable with enough tuning to the database.

Table 1: Performance Statistics for loading table W_PERSON_D

LENGTH RATE NOTE

0:00:07 6670 No Xform, FF to FF (loading all fields as if Vertical)

0:02:30 5100 No Xform, DB to FF (loading all fields as if Vertical)

350 No Xform, FF to DB, no Index (loading all fields as if Vertical)

520 No Xform, FF to DB, no Index, using Bulk (loading all fields as if Vertical)

0:02:41 1650 Horizontal, DB to FF (loading fewer fields than vertical)

0:08:32 350 Horizontal, DB to DB, Index (loading fewer fields than vertical)

0:06:19 625 Horizontal, DB to DB, no Index (loading fewer fields than vertical)

0:02:19 900 Horizontal, DB to DB, no Index, Using Bulk (loading fewer fields than vertical)

0:00:00 650 Horizontal, DB to DB, Cluster Index, Using Bulk (loading fewer fields than vertical)

0:06:03 450 Vertical, DB to FF

0:10:04 225 Vertical, DB to DB, Index

0:07:22 350 Vertical, DB to DB, no Index

0:03:40 465 Vertical, DB to DB, no Index, using Bulk

0:00:00 Not Run Vertical, DB to DB, Cluster Index, using Bulk

0:03:40 470 Vertical, FF to FF

0:10:06 225 Vertical, FF to DB, Index

0:06:42 350 Vertical, FF to DB, no Index

465 Vertical, FF to DB, no Index, using Bulk

Not Run Vertical, FF to DB, Cluster Index, using Bulk

Looking at the Rate column in Table 1, we can see that the source system format

really had very little impact on the overall load. The following chart makes this much

easier to see. The Rate columns are very close for both types of sources no matter

what the configuration of the destination tables.

FF Flat File

DB-I Database Table

with Index

DB-NI Database Table

with No Index

DB-NIB Database Table

with No Index

using Bulk Load

DB-CIB Database Table

with Clustered

Index using Bulk

Load

100

200

300

400

500

Rate

FF DB-I DB-NI DB-NIB DB-CIB

Target System

Data Load of Verticals Tables

Source DB

Source FF

The Bottle neck is more apparent from observing the target system. The following

chart illustrates the major differences in the data load rates distinguished by the

target system, this time showing the comparison for loading both the Vertical

industry version of the tables as well as the horizontals version of the tables.

500

1000

1500

Rate

FF DB-I DB-NI DB-NIB DB-CIB

Target Systems

Load Comparison for Target Systems

Verticals Horizontals

This chart shows the major difference between the rate obtained loading the

horizontals version of the tables and the rates obtained from loading the verticals

version of the tables which have far more columns. The point is to also reveal the

difference in the target systems shown on the X axis for the horizontal and the

vertical systems.

NOTE: The Verticals load with a target destination of DB-CIB (DB Clustered Index

with Bulk Loading) was not performed ergo the rate of 0 in the chart.

It’s also interesting to note the impact of loading the tables when the target DB

contains an Index and when it doesn’t as well as the performance gains obtained by

using Bulk Load operations.

Note on Clustered Indexes: One might be tempted to drop all indexes except the

cluster index since rebuilding the cluster index would cause the database to

physically rearrange the data, which could take a long time. But, since the cluster

index is built on the ROW_WID, which is always sequential, the data is already

ordered and does not need to be reordered by the database. Loading the

W_PERSON_D table with the cluster index enabled caused the session to take about

45 minutes longer than without the cluster index, while rebuilding the cluster index

only took about 5 minutes.

No Xform: Look at a chart showing the load rates as distinguished by mapping or

transforming the vertical tables as opposed to doing no transformations on the

vertical tables. Observe the two columns showing Flat File as the destination type.

These clearly show the tremendous impact transformations have on the load rate.

The second two indicate data being loaded into non indexed tables. Here we see that

the transformations load is being masked or obscured by the load going to the non

indexed tables. This tells us that the bottleneck is the non-indexed tables and not the

transformations.

1000

2000

3000

4000

5000

6000

7000

Load Rates

FF - FF DB - FF FF - DB-NI FF - DB-NIB

Target Table Systems

Load Rates with Transformations vs. without

Transformations

No Xform

Xform

Recommendation:

All this data leads to the following conclusions and recommendations for the ETL

process.

1. The source is not the bottleneck for performance

2. Use the Horizontal mappings vs. the Vertical mappings whenever possible

3. Load to an un-indexed table using the bulk option whenever possible.

More Loading Performance Tuning:

Having identified the bottleneck in the process we can make several observations

and suggestions to reduce this bottleneck.

SQL Server Recovery Model:

Please read SQL Server Books Online and understand the recovery implications for

your specific database before switching recovery models.

The ETL for the data warehouse is a good example of how you can use Simple or

Bulk logged recovery models to gain performance. The recovery model can be

returned to Full Recovery after data loading completes. This not only increases

performance but reduces the required log space while maintaining server protection.

However, you should understand that while bulk logged recovery produces smaller

logs, it can make for larger log backups. You should also understand that returning

to Full Recovery mode from Simple Recovery mode requires a full offline backup.

Dropping and rebuilding indexes

For more information on rebuilding indexes, refer to TechNote #6 Index Creation

Performance on the Technical Champs Website.

The ETL Process provides place holders to add scripts for dropping and rebuilding

indexes before and after loading tables. It’s not necessary to drop and rebuild all the

indexes. In this case the following tables were identified as candidates for dropping

and rebuilding indexes.

• W_ACTIVITY_F

• W_CAMP_HIST_F

• W_MAP_DIM_M

• W_ORG_D

• W_PERSON_D

• W_REVN_F

• S_ETL_R_IMAGE

• S_ETL_I_IMAGE

Permanently dropping NULL indexes or indexes on unused columns

Eliminating unused indexes improves load performance as well as speeding

incremental loads when indexes are not dropped by reducing the overhead required

to load each record. Again TechNote #6 Index Creation Performance has more

details on finding and eliminating Null indexes. But you can also drop indexes if the

column is not used by Analytics or if the data warehouse keys (ROW_WIDS) only

have the default value “0”. For our customer we realized the following gains by

eliminating unused indexes.

Index

Type

Time to rebuild

All indexes

Time to rebuild

remaining indexes

Estimated

Savings

Dimension Tables 54 minutes 8 minutes 46 minutes

Fact Tables 5 minutes 2 minutes 3 minutes

Extract, Transform & Load (Informatica)

Eliminate unnecessary steps

Each Siebel customer should identify the sessions that do not need to be run and

should follow Siebel’s recommended practices to reduce overall ETL run time by

eliminating these steps from the out-of-the-box process.

All mappings and sessions with the suffix ‘DB2390’ are specific to OLTP tables on a

DB2 database running on IBM’s OS 390. These sessions should be disabled if you

are not loading from DB2 on the OS390

Tune remaining steps

Run Out of the Box and Custom steps in Parallel

The Out-of-the-box settings are to run several mappings in parallel. Normally,

custom mappings are run separately from these mappings partly because there is a

dependency with the standard mappings but primarily because it is difficult to run

objects in separate folders at the same time. The solution to this is to move the

standard mapping to the custom folder then run the standard and custom mapping

at the same time. It is important to document this modification so that it can be

replicated after an upgrade and should only be done when significant savings can be

realized.

Convert some lookups to persistent:

Additional mappings may be improved through the use of persistent lookups.

Several dimension mappings must perform a lookup to the target table to determine

if the record already exists, which can take some time if there are many records. A

persistent cache can be stored at the end of the session to be reused during the next

run, avoiding this expensive lookup.

This change only benefits refresh/incremental loads

Application Level Performance Parameters:

Use Bulk load option for SDE mappings and full load for SIL mappings.

Informatica is able to take advantage of SQL Server’s bulk data loading utility. The

default settings are to use normal loading. Performance drops dramatically when

bulk loading a table with indexes, but staging tables do not have indexes so it is safe

to set all SDE mappings to bulk load. However, indexes must be dropped when bulk

loading the data warehouse tables (during full loads only). Since updates take place

during incremental loads, it is not recommended to use this method for incremental

SIL mappings, use Normal load method instead.

Bulk Loading issues:

• For versions of Informatica PowerCenter/PowerMart prior to version 6, loading

NULLS into columns with not-NULL constraints caused errors. This is a known

issue (CR 39049). In the event you need to do this in releases prior to version

6, you must use normal loading as opposed to bulk loading or remove the not

null constraint for that particular column in the target table.

• Bulk Option errors out when target definition does not match the physical

definition such as when the order of columns being inserted is different than

the order of the columns in the database This was described on the

Informatica support site and the solution is to re-import the table definition

from the database. This can have the unintended effect of invalidating each

mapping that uses this target and since there is not a mass validate mapping

option (although there is one for mass validating sessions) this can be a

difficult process. There is no evidence that this problem has been resolved.

Increase DTM and buffer settings to twice default setting (24,000,000 and

128,000).

This allocates more memory to the Informatica server for ETL processing. The

standard settings are normally adequate but for particularly complex mappings or

particularly ‘wide’ records, Informatica may need additional resources assigned. This

usually does not have much effect but does not hurt, particularly on systems with

large amounts of memory available.

Storage System Constraints

The customer purchased a very large EMC SAN with 180G disk drives. While this

provided plenty of adequate disk space the speed of the I/O system suffered. The

customer would have realizes far better performance from a higher volume of

smaller disk drives.

The following test was performed on the customer’s system (A) to obtain the MB/sec

throughput on the SAN.

BACKUP DATABASE TO DISK = ‘NUL:’ WITH STATS=1

This was compared to the same test performed on a similar SAN (B) with more but

smaller disks and better disk slicing.

A) Customer SAN 99.417 MB/sec

B) Benchmark SAN 319.689 MB/sec

The Benchmark SAN was actually two years older than the Customer SAN but better

performance was gained by using smaller hard drives and better slicing of the

disks. The specific results below show that the benchmark configuration had more

data files than the customer’s configuration. For more details on Disk Layout, consult

Here are the specifics results for the test:

Customer System (A):

99 percent backed up.

Processed 46104 pages for database xxx, file 'A_primary' on file

1. Processed 5520112 pages for database xxx, file 'A_data1_f1' on

file 1. Processed 1320 pages for database xxx, file 'A_data2_f1'

on file 1. 100 percent backed up. Processed 12 pages for database

xxx, file 'A_log1_f1' on file 1.

BACKUP DATABASE successfully processed 5567548 pages in 458.767

seconds (99.417 MB/sec).

Benchmark System (B):

99 percent backed up.

Processed 13430888 pages for database 'P01', file 'data1' on file

1. Processed 13426680 pages for database 'P01', file 'data3' on

file 1. Processed 13434664 pages for database 'P01', file 'data5'

on file 1. Processed 13436648 pages for database 'P01', file

'data2' on file 1. Processed 13439904 pages for database 'P01',

file 'data4' on file 1. Processed 13434848 pages for database

'P01', file 'data6' on file 1. Processed 13434944 pages for

database 'P01', file 'data7' on file 1. Processed 13425312 pages

for database 'P01', file 'data8' on file 1. Processed 13444456

pages for database 'P01', file 'data9' on file 1. Processed

13441120 pages for database 'P01', file 'data10' on file 1.

Processed 13432112 pages for database 'P01', file 'data11' on

file 1. Processed 13417296 pages for database 'P01', file

'data12' on file 1. 100 percent backed up. Processed 1298 pages

for database 'P01', file 'log1' on file 1.

BACKUP DATABASE successfully processed 161200170 pages in

4130.729 seconds (319.689 MB/sec).

Deadlocking

The customer was having several occurrences of SQL Server deadlocking during the

ETL process largely due to problems with Informatica versions prior to version 6.x.

The following examples show detailed tracking, analysis and a proposed solution to

these deadlocking events. Please note that the best way to eliminate these issues is

to make sure you are using the latest supported version of Informatica for your

Siebel Analytics Database.

Deadlocking Quick Overview (from Books-On-Line)

“A deadlock occurs when there is a cyclic dependency between two or more threads

for some set of resources.

Deadlock is a condition that can occur on any system with multiple threads, not just

on a relational database management system. A thread in a multi-threaded system

may acquire one or more resources (for example, locks). If the resource being

acquired is currently owned by another thread, the first thread may have to wait for

the owning thread to release the target resource. The waiting thread is said to have

a dependency on the owning thread for that particular resource.

If the owning thread wants to acquire another resource that is currently owned by

the waiting thread, the situation becomes a deadlock:”

For more details and diagrams explaining deadlocking, consult SQL Server Books-

On-Line (BOL). Open BOL, use the menu Go �� URL and paste the following into the

URL field: acdata.chm::/ac_8_con_7a_8i93.htm

Deadlock incident example 1:

Consecutive deadlocks occurred within a span of 9 seconds on 10/15 20:53. The

update lock on resource KEY 5:740197687 points to a non-clustered unique index

OPB_RTSEQ.BLAH (SEQNAME), while the lock on resource RID: 5:1:8955 points to

table OPB_RTSEQ.

At 20:53:28.16, the server killed SPID 62 as the deadlock victim. The deadlock

chain consists of 3 nodes: SPID 63, 62 and 53:

Node SPID SQL Owned (locked)

Resource

Requested (wait

for) Resource

Blocked

1 63 SELECT SEQVAL FROM OPB_RTSEQ

WHERE SEQNAME = 'SESSLOGSEQ'

Exclusive RID

lock

RID: 5:1:8955:11

Shared KEY lock 62

2 62 UPDATE OPB_RTSEQ SET SEQVAL =

SEQVAL + SEQINCR WHERE

SEQNAME = 'SESSLOGSEQ'

Update KEY lock

KEY:

5:740197687:2

(350145c4a548)

3 53 UPDATE OPB_RTSEQ SET SEQVAL =

SEQVAL + SEQINCR WHERE

SEQNAME = 'SESSLOGSEQ'

Update KEY lock

KEY:

5:740197687:2

(350145c4a548)

Shared RID lock 63

At 20:53:33.16, server killed SPID 60 as the deadlock victim. The deadlock chain

consists of 3 nodes: SPID 54, 60 and 53:

Node SPID SQL Owned (locked)

Resource

Requested (wait

for) Resource

Blocked

1 54 UPDATE OPB_RTSEQ SET SEQVAL =

SEQVAL + SEQINCR WHERE

SEQNAME = 'SESSLOGSEQ'

Update KEY lock

KEY:

5:740197687:2

(350145c4a548)

2 60 UPDATE OPB_RTSEQ SET SEQVAL =

SEQVAL + SEQINCR WHERE

SEQNAME = 'SESSLOGSEQ'

Update KEY lock

KEY:

5:740197687:2

(350145c4a548)

Shared RID lock 53

3 53 SELECT SEQVAL FROM OPB_RTSEQ

WHERE SEQNAME = 'SESSLOGSEQ'

Exclusive RID

lock

RID: 5:1:8955:11

Shared KEY lock 54

At 20:53:35.66, server killed SPID 54 as the deadlock victim. The deadlock chain

consists of 3 nodes: SPID 55, 54 and 53:

Node SPID SQL Owned (locked)

Resource

Requested (wait

for) Resource

Blocked

1 55 UPDATE OPB_RTSEQ SET SEQVAL =

SEQVAL + SEQINCR WHERE

SEQNAME = 'SESSLOGSEQ'

Update KEY lock

KEY:

5:740197687:2

(350145c4a548)

2 54 UPDATE OPB_RTSEQ SET SEQVAL =

SEQVAL + SEQINCR WHERE

SEQNAME = 'SESSLOGSEQ'

Update KEY lock

KEY:

5:740197687:2

(350145c4a548)

Shared RID lock 53

3 53 SELECT SEQVAL FROM OPB_RTSEQ

WHERE SEQNAME = 'SESSLOGSEQ'

Exclusive row

lock

RID: 5:1:8955:11

Shared KEY lock 55

At 20:53:36.91, server killed SPID 55 as the deadlock victim. The deadlock chain

consists of 3 nodes: SPID 55, 54 and 53:

Node SPID SQL Owned (locked)

Resource

Requested (wait

for) Resource

Blocked

1 61 UPDATE OPB_RTSEQ SET SEQVAL =

SEQVAL + SEQINCR WHERE

SEQNAME = 'SESSLOGSEQ'

Update key lock

KEY:

5:740197687:2

(350145c4a548)

2 55 UPDATE OPB_RTSEQ SET SEQVAL =

SEQVAL + SEQINCR WHERE

SEQNAME = 'SESSLOGSEQ'

Update key lock

KEY:

5:740197687:2

(350145c4a548)

Shared lock 53

3 53 SELECT SEQVAL FROM OPB_RTSEQ

WHERE SEQNAME = 'SESSLOGSEQ'

Exclusive row

lock

RID: 5:1:8955:11

Shared lock 61

At 20:53:37.53, server killed SPID 61 as the deadlock victim. The deadlock chain

consists of 3 nodes: SPID 58, 61 and 53:

Node SPID SQL Owned (locked)

Resource

Requested (wait

for) Resource

Blocked

1 58 UPDATE OPB_RTSEQ SET SEQVAL =

SEQVAL + SEQINCR WHERE

SEQNAME = 'SESSLOGSEQ'

Update key lock

KEY:

5:740197687:2

(350145c4a548)

2 61 UPDATE OPB_RTSEQ SET SEQVAL =

SEQVAL + SEQINCR WHERE

SEQNAME = 'SESSLOGSEQ'

Update key lock

KEY:

5:740197687:2

(350145c4a548)

Shared lock 53

3 53 SELECT SEQVAL FROM OPB_RTSEQ

WHERE SEQNAME = 'SESSLOGSEQ'

Exclusive row

lock

RID: 5:1:8955:11

Shared lock 58

Analysis & Solutions

This problem has been fixed in Informatica version 6.0. The following Analysis and

Solutions were done on a system using Informatica version 5.2.

This series of deadlocks was caused by the lack of a clustered index and the way the sequence

value for SESSLOGSEQ was generated in and retrieved from the OPB_RTSEQ table.

Although the table OPB_RTSEQ has a unique index on column SEQNAME, the update

and select statements requesting locks at the index level and the row level are

causing the deadlock. This happens because the update statement was granted the

update lock on the key and is trying to obtain an exclusive lock on the row. At the

same time the select statement has the lock on the row and is trying to obtain the

update lock on the key. This contention can be alleviated by creating a clustered

index on column SEQNAME instead of the current non-clustered unique index. This

causes the lock grants and waits to go against the clustered index, which is the same

entry in the b-tree, thus eliminating the deadlock.

The update and retrieval of the value seems to be a two step process:

1. Update SEQVAL value, with an update lock on the index.

2. Retrieve the value, with an exclusive row lock.

The transaction isolation and lock change is inefficient. Without a major schema

overhaul, this process can be streamlined using the following update syntax:

Declare @SEQVAL int

UPDATE OPB_RTSEQ

SET @SEQVAL=SEQVAL=SEQVAL+SEQINCR

WHERE SENAME = ‘SESSLOGSEQ’

SELECT @SEQVAL

This code retrieves the sequence value in the same update transaction scope with

much greater efficiency thus providing higher concurrency, and also eliminating

deadlocks.

Deadlock incident example 2:

This deadlock occurred as the conflict between SPID 59 and SPID 54. SPID 59 is

blocked from its request for an update (U) lock on KEY (range) 5:1396200024:2 –

_WA_Sys_OBJECT_TYPE_ID_53385258 (which is in fact a table level lock due to the

lack of an index on the column OBJECT_TYPE_ID), because SPID 54 already has an

exclusive lock on it. In Node 2, SPID 54 is blocked from its request for an exclusive

lock on RID (row): 5:1:10213:7 because SPID 51 holds a shared lock.

Node SPID SQL Owned (locked)

Resource

Requested (wait

for) Resource

Blocked

1 59 SELECT A.OBJECT_LOCK_TYPE,

A.OBJECT_LOCKER_ID,

A.LOCK_TIME, B.SERVER_ID,

B.LAST_SAVED,

A.OBJECT_TYPE_ID, A.HOSTNAME,

A.PROC_ID FROM

OPB_SERVER_INFO B,

OPB_OBJECT_LOCKS A WHERE

A.OBJECT_ID = B.SERVER_ID

AND B.SERVER_ID = 10 AND

A.OBJECT_TYPE_ID…

Update KEY Lock

KEY:

5:1396200024:2

(1901ddf4e005)

Note: this is a

statistics record

Shared RID lock 54

2 54 DELETE FROM OPB_OBJECT_LOCKS

WHERE OBJECT_TYPE_ID = 41 AND

OBJECT_ID = 10 AND

OBJECT_LOCK_TYPE = 8 AND

OBJECT_LOCKER_ID = 2 AND

SUBJECT_ID = 0 AND

VERSION_ID = 0 AND LOCK_TIME

= '10/12/2003 20:47:38'

Exclusive RID

lock

RID: 5:1:10213:7

Exclusive KEY

losck

Analysis & Solutions

This problem has been fixed in Informatica version 6.0. The following Analysis and

Solutions were done on a system using Informatica version 5.2.

Microsoft Technical Champs for Siebel

The occurrence of this deadlock may be eliminated, or significantly reduced if table

OPB_OBJECT_LOCKS is properly indexed. It originally has indexes and dropping the

original indexes seems to eliminate some locking problems. However it is possible to

gain additional improvements by adding the following indexes. Test both

environments to make sure you are getting the best performance in situations like

this.

• Clustered Primary Key Index

• Non-clustered index covering OBJECT_TYPE_ID

Data warehousing: ETL vs ELT

2010-02-26T10:37:00.000-08:00

Datawarehouse : ETL vs ELT

Extract, transform and load (ETL) was considered the most effective way to load information into a data warehouse. Early data warehouses were not viewed as being capable of handling the extensive processing required to perform the complex transformations involved in the warehouse load process. Instead, third-party tools like Oracle Data Integrator and Informatica Corp. were leveraged to orchestrate data movement between source systems and the data warehouse. With the advancement in both hardware and data warehouse software technology, warehouse designers can now consider extract, load and transform (ELT) a viable option.

HYPERION ....! What's NEW ....?

2009-08-20T03:22:00.000-07:00

Do know the technology of Hyperion fastest reporting tools by Hyperion EPM and planning announces the advances of increasing competition in today’s marketplace requires that you optimize execution and deliver greater value to customers. As a result, it’s more important than ever for your business to turn gigabytes of information into insights that result in action and generate immediate business value. Oracle’s Hyperion Web Analysis delivers out-of-the-box, online analytical processing (OLAP) analysis, presentation, and reporting.

Difference between OBIEE and Hyperion (OBIEE plus) containing with in Hyperion.

Hyperion ia a Business Intelligence (BI) and Business Performanc Management (BPM ) Tool. Its a market leader in Financial Operational and Strategic Planning. It is having applications for Planning consolidation scorecarding reporting dashboards Analysis Workspace Master Data Management and Foundation.

ETL Tools is for Extracting source data Transforming as required and Loading into DW. it is first part of DW. Now large fact and dimension tables are in DW. If you have to generate crosstab report from these tables it will take very long time. So these DW tables are transformed into Cubes and stored in OLAP Server. Any Report Generation Analysis is done on these... Cubes are having pre agreegated values and hence Report Generation will be much faster.

OBIEE is integrated with Hyperion (OBIEE Plus) which will be soon in OBIEE 11g.

Professional Certification from TDWI

2009-04-28T00:15:00.001-07:00

CBIP Certification

TDWI offers the industry's only independent certification program available, the Certified Business Intelligence Professional (CBIP). CBIP, a test-based certification program, is offered in five key specialty areas.

ORACLE ENTERPRISE MANAGER 10g BUSINESS INTELLIGENCE MANAGEMENT PACK

2009-02-20T03:53:00.000-08:00

Oracle Business Intelligence Management Pack extends Oracle Enterprise Manager to provide a comprehensive, integrated management solution that helps you achieve high levels of performance and availability, and reduce the costs of managing your Oracle Business Intelligence Enterprise Edition applications.

Complete Solution:
Oracle Business Intelligence Management Pack is an integrated solution for ensuring theperformance and availability of Oracle Business Intelligence Enterprise Edition (OBI-EE).Using the pack, one may get proactive alerts on impending problems on the application,examine run-time statistics to troubleshoot and tune performance, compare configurationsacross different environments, keep track of configuration changes, and proactively enforceconfiguration policies in order to avoid configuration related problems. The pack may be usedwith other Oracle Enterprise Manager management packs and plug-in’s to achieve end-to-endmanagement of the entire application environment, including both Oracle and non-Oracledatabases, operating systems, storage and network devices.

Automatic Discovery
The first step in managing an OBI-EE environment is to establish a detailed inventory of theapplication components. Oracle Enterprise Manager collects detailed configurationinformation of all host systems and the installed software components across an OBI-EEsystem. Oracle Enterprise Manager can automatically introspect configurations that are alreadydefined, and then store them in Oracle Enterprise Manager’s integrated ConfigurationManagement Database (CMDB), which is the foundation of Oracle Enterprise Manager'sconfiguration management system.

Configuration Management
Once the configurations are captured into the CMDB, you may then use Oracle EnterpriseManager Configuration Management tools to track changes made to the configuration. Youmay get audit trail of configuration, or take a snapshot of the state of the system at a given pointin time. The tools also let compare between snapshots and the current state of the system,across different OBI-EE Servers, or different OBI-EE environments, helping you to quicklyand easily pinpoint any potential differences. These capabilities help keep the components in
your application environment synchronized and reduce "configuration drift". They also
simplify investigations into why components that are presumed to be identical may behavedifferently.

Proactive Monitoring and Alerting
As your OBI-EE applications are put into operation, Oracle Enterprise Manager continuouslymonitors key performance, usage and health indicators. If any anomaly is found, OracleEnterprise Manager alerts you to the potential problem using methods you decide, and escalatesthe problems to your colleagues if necessary. The metrics that are collected may also be usedto analyze the usage and performance of OBI-EE in order to make the proper tuning decisions.

Business Intelligence Articles

2009-02-20T03:05:00.000-08:00