The Central Management Server (CMS) Repository

The content of the Business Objects Enterprise (BOE) system consists of the physical files and the metadata information about the physical files.
For a Crystal Report, the physical file as well as the metadata about the file should exist in the BOE system. The Crystal report is stored as a file on File Repository Server (FRS) with an extension of .rpt . The Metadata information such as report name, type, report ID, path, etc is stored as an InfoObject in the CMS Repository.
I will discuss about the CMS repository in this article.
The CMS Repository Database Tables
The CMS metadata is physically stored on a database as InfoObjects.   There are six tables, the purpose of which is given below.

Sno	Physical Table Name	Purpose
1	CMS_VersionInfo	Contains the current version of BOE.
2	CMS_InfoObjects6	Each row in this table stores a single InfoObject.  This is the main table in the repository.
3	CMS_Aliases6	Maps the user alias(es) to the corresponding user ID. For example, a user may have both a Win NT alias and an LDAP alias. Regardless of the number of aliases a user may have, in the Business Intellengence .Platform each user has only one user ID. The map is stored in a separate table to enable fast logins.
4	CMS_IdNumbers6	The CMS uses this table to generate unique Object IDs and Type IDs. It has only two rows: an Object ID row and a Type ID row. The CMSs in a cluster use this table when generating unique ID numbers.
5	CMS_Relationships6	Relationship tables are used to store the relations between InfoObjects. Each row in the table stores one edge in the relation. For example, the relation between a Web Intelligence document and a Universe would be stored in a row in the WebI – Universe Relation table. Each relationship table has these columns: Parent Object ID, Child Object ID, Relationship InfoObject ID, member, version, ordinal, data.
6	CMS_LOCKS6	This is an auxiliary table of CMS_RELATIONS6.

The Central Management Server(CMS) repository tables cannot be queried directly. Query builder is the tool to be used to retrieve Business Objects metadata information using the virtual tables such as ci_systemobjects, ci_infoobjects and ci_appobjects.
In the forthcoming posts, I will discuss on query builder and file repository server.

Building dashboards using Xcelsius with SharePoint data

Do you know – Xcelsius dashboards can be built using SharePoint data?

Figure 1: Xcelsius dashboard built using SharePoint data

Pre-requisites

• Xcelsius for SharePoint server (XSP) installs a web part container into the Sharepoint web part library.
• After installation and configuration, the web part container can be added to the Web Part Gallery within SharePoint and any user with the appropriate rights can add the web part container to a page within the site.

Xcelsius Web connectivity Parameters to use

• SharePoint Parameter: Used to define parameters or properties in Crystal Xcelsius models that a user can change in the SharePoint environment
• SharePoint Provider: Used to give data to another Web Part published in SharePoint
• SharePoint Consumer: Used to get data from another published Web Part in SharePoint (a SharePoint List or another Crystal Xcelsius model)
• Use the File -> Export -> SharePoint option to generate the .swf file with SharePoint functionality.
• Once generated, you will be able to add and configure the SharePoint Web Part with the .swf file to publish the model

Building the Dashboard

• Add the Xcelsius document to the SharePoint document list
• Copy the shortcut to the uploaded document
• Go the SharePoint page
• Click on Modify Shared web part
• Add a web part
  • From the web part-gallery list select Crystal Xcelsius web-part
  • Use Modify Shared Web Part option
  • In the Xcelsius Visualization source, specify URL (paste the shortcut you copied from the document list) and apply
• It is possible to customize models Real-time – Change chart type, title etc when defined as SharePoint Parameter
  • Use Modify Shared web part at run time

• Add a SharePoint List, say “Sample” as a web part in the same page
• In the Xcelsius web part configuration menu, “connections” option is enabled once a list is added in the page
• In the connections, specify consume list from “Sample”
• All Sharepoint lists and Providers in the same page will be listed in the connections option.
• In order for a Xcelsius model to consume data from Sharepoint Provider, there should be two models deployed in the same page – one for the provider and one for the consumer
• The visuals in the swf file with the consumer will change based on the data from the provider once connection is set

Something I learned about Oracle Database 11g RMAN restore

Last weekend (it was saturday night), I needed to restore a Development database from a old backup. I never did a RMAN restore before until last saturday. As the saying goes, “necessity is the mother of invention”. Though it is not really an invention (rman is there for a long time), for me, I learned to know about RMAN restore last week.
Our DBA was not available on Saturday. I needed to test few things on the Development and for that I need to restore a backup that was taken earlier couple of months ago. So I did the follow the procedures to restore the database using RMAN.
This could be a basic thing all the DBAs know about. But for me this is not something I do everyday. So this was new to me.
First, I ran the “shutdown immediate” command to shutdown my development database. Then I followed these steps to restore the database from a older backup taken by RMAN. Database was running on the Redhat Enterprise Linux Machine and the database version was 11.1.1.6.0.
$ rman
…
RMAN> list backup;
List of Backup Sets
===================
…….
I got the TAG details from here.
……
RMAN>  restore datafile ‘/u02/oradata/OIMTST/system01.dbf’ from tag = ‘BEFORERECON’;
…
RMAN>  restore datafile ‘/u02/oradata/OIMTST/sysaux01.dbf’ from tag = ‘BEFORERECON’;
….
RMAN> restore datafile ‘/u02/oradata/OIMTST/undotbs01.dbf’ from tag = ‘BEFORERECON’;
…
RMAN> restore datafile ‘/u02/oradata/OIMTST/users01.dbf’ from tag = ‘BEFORERECON’;
…
RMAN> restore datafile ‘/u02/oradata/OIMTST/oimtst4_tspace_01.dbf’ from tag = ‘BEFORERECON’;
…
RMAN> list backup of controlfile;
…
RMAN> restore controlfile to ‘/u02/oradata/OIMTST/control01a.ctl’ from tag = ‘TAG20100820T112653′
…
RMAN> quit
Recovery Manager complete.
$
Copying the Control Files:
============================
cd /u02/oradata/OIMTST  # The conrol files are located here.
cp control01a.ctl control01.ctl
cp control01a.ctl control02.ctl
cp control01a.ctl control03.ctl
$ sqlplus / as sysdba….
SQL> startup
ORACLE instance started.
Total System Global Area 1073131520 bytes
Fixed Size                  2151248 bytes
Variable Size             264244400 bytes
Database Buffers          801112064 bytes
Redo Buffers                5623808 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL>
SQL> alter database open resetlogs;
Database altered.
SQL>
Hurray!!!! It is success!!!
This was my first restore using RMAN. I knew the concepts earlier, but I didn’t really restore a database like this before. I thought of sharing this knowledge.
We will meet in another post. Until then

Read More about Oracle Database 11g

Impact Analysis on Source & Target Definition Changes

Changes to Source and Target definition will impact the current state of the Informatica mapping and this article list the possible changes at Source and the Target with impact.


Updating Source Definitions:When we update a source definition, the Designer propagates the changes to all mappings using that source. Some changes to source definitions can invalidate mappings.
Below table describes how the mappings get impacted when the source definition is edited:

Modification	Result  of the source after modifying the source definition
Add a column.	Mappings are not invalidated.
Change a column Data type.	Mappings may be invalidated. If the column is connected to an input port that uses a Data type incompatible with the new one, the mapping is invalidated.
Change a column name.	Mapping may be invalidated. If you change the column name for a column you just added, the mapping remains valid. If you change the column name for an existing column, the mapping is invalidated.
Delete a column.	Mappings can be invalidated if the mapping uses values from the deleted column.

Adding a new column in the existing source definition:

• When we add a new column to a source in the Source Analyzer, all mappings using the source definition remain valid.
• However, when we add a new column and change some of its properties, the Designer invalidates mappings using the source definition.
• We can change the following properties for a newly added source column without invalidating a mapping: 1. Name
  2. Data type
  3. Format
  4. Usage
  5. Redefines
  6. Occurs
  7. Key type

If the changes invalidate the mapping, we must open and edit the mapping. Then click Repository > Save to save the changes to the repository. If the invalidated mapping is used in a session, we must validate the session.
Updating Target Definitions:
When we change a target definition, the Designer propagates the changes to any mapping using that target. Some changes to target definitions can invalidate mappings.
The following table describes how the mappings get impacted when we edit target definitions:

Modification	Result  of the source after modifying the target definition
Add a column.	Mapping not invalidated.
Change a column Data type.	Mapping may be invalidated. If the column is connected to an input port that uses a Data type that is incompatible with the new one (for example, Decimal to Date), the mapping is invalid.
Change a column name.	Mapping may be invalidated. If you change the column name for a column you just added, the mapping remains valid. If you change the column name for an existing column, the mapping is invalidated.
Delete a column.	Mapping may be invalidated if the mapping uses values from the deleted column.
Change the target definition type.	Mapping not invalidated.

Adding a new column in the existing target definition:

• When we add a new column to a target in the Target Designer, all mappings using the target definition remain valid.
• However, when you add a new column and change some of its properties, the Designer invalidates mappings using the target definition.
• We can change the following properties for a newly added target column without invalidating a mapping:

1. Name
2. Data type
3. Format
If the changes invalidate the mapping, validate the mapping and any session using the mapping. We can validate objects from the Query Results or View Dependencies window or from the Repository Navigator. We can validate multiple objects from these locations without opening them in the workspace. If we cannot validate the mapping or session from one of these locations, open the object in the workspace and edit it.

Re-importing a Relational Target Definition:
If a target table changes, such as when we change a column data type, we can edit the definition or we can re-import the target definition. When we re-import the target, we can either replace the existing target definition or rename the new target definition to avoid a naming conflict with the existing target definition.

To re-import a target definition:

• In the Target Designer, follow the same steps to import the target definition, and select the    Target to import. The Designer notifies us that a target definition with that name already exists in the repository. If we have multiple tables to import and replace, select apply to All Tables.
• Click Rename, Replace, Skip, or Compare.
• If we click Rename, enter the name of the target definition and click OK.
• If we have a relational target definition and click Replace, specify whether we want to retain primary key-foreign key information and target descriptions

The following table describes the options available in the Table Exists dialog box when re-importing and replacing a relational target definition:

Option	Description
Apply to all Tables	Select this option to apply rename, replaces, or skips all tables in the folder.
Retain User-Defined PK-FK Relationships	Select this option to keep the primary key-foreign key relationships in the target definition being replaced. This option is disabled when the target definition is non-relational.
Retain User-Defined Descriptions	Select this option to retain the target description and column and port descriptions of the target definition being replaced.

To know more about Target Definition

Output Files in Informatica

The Integration Service process generates output files when we run workflows and sessions. By default, the Integration Service logs status and error messages to log event files.

Log event files are binary files that the Log Manager uses to display log events. When we run each session, the Integration Service also creates a reject file. Depending on transformation cache settings and target types, the Integration Service may create additional files as well.

The Integration Service creates the following output files:

Output Files

Session Details/logs:

• When we run a session, the Integration service creates session log file with the load statistics/table names/Error information/threads created etc based on the tracing level that have set in the session properties.
• We can monitor session details in the session run properties while session running/failed/succeeded.

Workflow Log:

• Workflow log is available in Workflow Monitor.
• The Integration Service process creates a workflow log for each workflow it runs.
• It writes information in the workflow log such as
  • Initialization of processes,
  • Workflow task run information,
  • Errors encountered and
  • Workflows run summary.
• The Integration Service can also be configured to suppress writing messages to the workflow log file.
• As with Integration Service logs and session logs, the Integration Service process enters a code number into the workflow log file message along with message text.

Performance Detail File:

• The Integration Service process generates performance details for session runs.
• Through the performance details file we can determine where session performance can be improved.
• Performance details provide transformation-by-transformation information on the flow of data through the session.

Reject Files:

• By default, the Integration Service process creates a reject file for each target in the session. The reject file contains rows of data that the writer does not write to targets.
• The writer may reject a row in the following circumstances:
  • It is flagged for reject by an Update Strategy or Custom transformation.
  • It violates a database constraint such as primary key constraint
  • A field in the row was truncated or overflowed
  • The target database is configured to reject truncated or overflowed data.

Note: By default, the Integration Service process saves the reject file in the directory entered for the service process variable $PMBadFileDir in the Workflow Manager, and names the reject file target_table_name.bad. We can view this file name in session level.

• Open Session – Select any of the target View the options
  • Reject File directory.
  • Reject file name.
• If you enable row error logging, the Integration Service process does not create a reject file.

Row Error Logs:

• When we configure a session, we can choose to log row errors in a central location.
• When a row error occurs, the Integration Service process logs error information that allows to determine the cause and source of the error.
• The Integration Service process logs information such as source name, row ID, current row data, transformation, timestamp, error code, error message, repository name, folder name, session name, and mapping information.
• we enable flat file logging, by default, the Integration Service process saves the file in the directory entered for the service process variable $PMBadFileDir in the Workflow Manager.

Recovery Tables Files:

• The Integration Service process creates recovery tables on the target database system when it runs a session enabled for recovery.
• When you run a session in recovery mode, the Integration Service process uses information in the recovery tables to complete the session.
• When the Integration Service process performs recovery, it restores the state of operations to recover the workflow from the point of interruption.
• The workflow state of operations includes information such as active service requests, completed and running status, workflow variable values, running workflows and sessions, and workflow schedules.

Control File:

• When we run a session that uses an external loader, the Integration Service process creates a control file and a target flat file.
• The control file contains information about the target flat file such as data format and loading instructions for the external loader.
• The control file has an extension of .ctl. The Integration Service process creates the control file and the target flat file in the Integration Service variable directory, $PMTargetFileDir, by default.

Email:

• We can compose and send email messages by creating an Email task in the Workflow Designer or Task Developer and the Email task can be placed in a workflow, or can be associated it with a session.
• The Email task allows to automatically communicate information about a workflow or session run to designated recipients.
• Email tasks in the workflow send email depending on the conditional links connected to the task. For post-session email, we can create two different messages, one to be sent if the session completes successfully, the other if the session fails.
• We can also use variables to generate information about the session name, status, and total rows loaded.

Indicator File:

• If we use a flat file as a target, we can configure the Integration Service to create an indicator file for target row type information.
• For each target row, the indicator file contains a number to indicate whether the row was marked for insert, update, delete, or reject.
• The Integration Service process names this file target_name.ind and stores it in the Integration Service variable directory, $PMTargetFileDir, by default.

Target or Output File:

• If the session writes to a target file, the Integration Service process creates the target file based on a file target definition.
• By default, the Integration Service process names the target file based on the target definition name.
• If a mapping contains multiple instances of the same target, the Integration Service process names the target files based on the target instance name.
• The Integration Service process creates this file in the Integration Service variable directory, $PMTargetFileDir, by default.

Cache Files:

• When the Integration Service process creates memory cache, it also creates cache files. The Integration Service process creates cache files for the following mapping objects:
  • Aggregator transformation
  • Joiner transformation
  • Rank transformation
  • Lookup transformation
  • Sorter transformation
  • XML target
• By default, the DTM creates the index and data files for Aggregator, Rank, Joiner, and Lookup transformations and XML targets in the directory configured for the $PMCacheDir service process variable.

Read More about Informatica

Checking on Oracle Fusion Applications

Sun Blogger Vijay Tatkar wrote in his blog about the eight technology innovations mentioned by Larry Ellision during his Oracle Open World Keynote speech during last week. Nearly half of them were Sun Hardware related (such as Exadata, ExaLogicSun ultraSPARC t3 etc). Here is the list:

1. Fusion Apps
2. Unbreakable Linux Kernel
3. Solaris Express 11
4. unltraSPARC t3 chip
5. mySQL 5.5
6. exadata
7. exalogic
8. Java 7 and 8

Since the beginning, I am always interested to know more about Fusion Apps, mainly out of curiosity. Oracle Fusion Applications were formally introduced during the Oracle Open World last week (during Open World 2010). As per Oracle Release, this was one of the major innovation or next big thing for Oracle. As you are aware, Fusion Middleware Products were released already. Now, it is time to talk about the Fusion Applications.
You may be already aware; I started my IT career as a Web Developer in a small web hosting company. I used to write perl CGI code and hosting them on Linux Servers running with Apache Web Server and mySQL Database. I got bored (or I wanted a change, I am not sure!) with that job and then moved into the Unix System Administration. I worked as a Sun Solaris Admin and HP Unix Admin for some time. Then I worked in both Peoplesoft System Administration for nearly 7 years and currently working in an Oracle Identity Management (which is part of Oracle Fusion Middleware products) project for nearly past one year.
So, the question is “now what?” And how can we develop the knowledge for Fusion Apps Administration.
I am not sure when Fusion Apps will be deployed full-fledged instead of the other ERP Applications like Peoplesoft. I don’t think it will be near soon, but may be after few years, Oracle may buy in customers who are going to do a new implementation of some ERP Applications.
You know what, Fusion middleware for Fusion Apps is like PeopleTools for Peoplesoft Applications. PeopleTools Technology is the abstract layer on top of which all the Peoplesoft Applications run on. PeopleTools was originally built on C and C++ and finally evolved into a Java Technology. However I still feel some of them are C++ code. While Fusion Middleware is more Java and J2EE apps, I believe Fusion Apps will be more J2EE apps than Peoplesoft. I need to spend little bit more time on implementing a Fusion Middleware and an application. As of now, I only worked on Identity Management Product Sure and little but of Oracle Portal Technologies.
For an IT Infrastructure Administrator like me (who mainly works on Oracle Server Technologies), I think understanding the Fusion Middleware Stack will be important.
Talk to you later. Until then

Read More about Oracle Fusion Applications

Informatica Power Center performance – Concurrent Workflow Execution

What is concurrent work flow?
A concurrent workflow is a workflow that can run as multiple instances concurrently.

What is workflow instance?
A workflow instance is a representation of a workflow.

How to configure concurrent workflow?

1) Allow concurrent workflows with the same instance name:
Configure one workflow instance to run multiple times concurrently. Each instance has the same source, target, and variables parameters.
Eg: Create a workflow that reads data from a message queue that determines the source data and targets. You can run the instance multiple times concurrently and pass different connection parameters to the workflow instances from the message queue.

2) Configure unique workflow instances to run concurrently:
Define each workflow instance name and configure a workflow parameter file for the instance. You can define different sources, targets, and variables in the parameter file.
Eg: Configure workflow instances to run a workflow with different sources and targets. For example, your organization receives sales data from three divisions. You create a workflow that reads the sales data and writes it to the database. You configure three instances of the workflow. Each instance has a different workflow parameter file that defines which sales file to process. You can run all instances of the workflow concurrently.

How concurrent workflow Works?
A concurrent workflow group’s logical sessions and tasks together, like a sequential workflow, but runs all the tasks at one time.
Advantages of Concurrent workflow?
This can reduce the load times into the warehouse, taking advantage of hardware platforms’ Symmetric Multi-Processing (SMP) architecture.
LOAD SCENARIO:
Source table records count:  150,622,276

To know more about Informatica Power Center