Calculate the cache size

If you calculate the cache size requirements correctly and sufficiently, all cached data will remain in memory and not create files on the server.

Files will be created on the server when

1. The cache space requirements exceed the memory allocated for any of the transformations that create caches: Agg, Rnk, Lkp, Jnr, Srt.
2. Incremental aggragation is used with an aggragator transformation to store the finished aggragated values until the next run
3. Persistant lookup cache is selected to store the lookup caches until they are reused by the next lookup transformation.

Aggregate Cache Calculation
Cache	Calculation	Columns in Cache
Index	# groups [( column size) + 17]	Group-by columns.
Data	# groups[( column size) + 7]	Non group-by input ports used in non-aggregate output expression. Non group-by input/output ports. Local variable ports. Column containing aggregate function (multiply by three).*
* Each aggregate function has different cache space requirements. As a general rule, you can multiply the column containing the aggregate function by three.

Rank Cache Calculation
Cache	Calculation	Columns in Cache
Index	# groups [( column size) + 17]	Group-by columns.
Data	# groups [(# ranks *( column size + 10)) + 20]	Non group-by input ports used in non-aggregate output expression. Non group-by input/output ports. Local variable ports. Rank ports.

Joiner Cache Calculation
Cache	Calculation	Columns in Cache
Index	# master rows [( column size) + 16]	Master column in join conditions.
Data	# master rows [( column size) + 8]	Master column not in join condition and used for output.

Table 19-6. Lookup Cache Calculation
Cache	Calculation	Columns in Cache
Index	# rows in lookup table [( column size) + 16]	Columns in lookup condition.
Data	# rows in lookup table [( column size) + 8]	Connected output ports not in the lookup condition. Return port (for unconnected Lookup transformations).

For more information about each cache, see the separate sections in this chapter.

Cache Column Sizes

When you calculate the column size for each cache, include the size of the data and additional processing requirements.

Table gives the columns sizes for index and data cache calculations:

Column Sizes for Cache Calculations
Datatype	Aggregator, Rank	Joiner, Lookup
Binary	precision + 2	precision + 8 Round to nearest multiple of 8
Date/Time	18	24
Decimal, high precision off (all precision)	10	16
Decimal, high precision on (precision <=18)	18	24
Decimal, high precision on (precision >18, <=28)	22	32
Decimal, high precision on (precision >28)	10	16
Decimal, high precision on (negative scale)	10	16
Double	10	16
Real	10	16
Integer	6	16
Small integer	6	16
NString, NText, String, Text	Unicode mode: 2*(precision + 2) ASCII mode: precision + 3	Unicode mode: 2*(precision + 5) ASCII mode: precision + 9

Cache Calculator

Use the cache calculator for aggregator, joiner, rank, and sorter transformations to improve performance.

Select to calculate the total requirements for a transformation based on inputs. The cache calculator requires different inputs for each transformation. You must select the applicable cache type to apply the calculated cache size. For example, to apply the calculated cache size for the data cache and not the index cache, select only the Data Cache Size option.

The cache calculator estimates the cache size required for optimal session performance based on your input. After you configure the cache size and run the session, you can review the transformation statistics in the session log to tune the configured cache size.

Note: You cannot use the cache calculator to estimate the cache size for an XML target.

lookup SQL override for avoid text filed cache

The override sql needs to be in the session because of the performance issue.  If the sql only in the mapping, when create the lookup cache, powercenter will include the CHARGE_VAL_TXT in the order by (sorting). The follow sql in the session will avoid the issue.  This override is there to avoid cache.

pmrep ObjectImport conflict problem

When I do pmrep ObjectImport -i , try to import a folder to another informatica repository. I got a object conflicts error for a User-Defined Function. Originally I got control file from PowerCenter help. Then I did research find out you need add

<TYPEOBJECT OBJECTTYPENAME="User Defined Function" RESOLUTION="REPLACE"/>

or replace with all

 <TYPEOBJECT OBJECTTYPENAME="ALL" RESOLUTION="REPLACE"/>

 Hope this help

Lesson three creating a Pass-Through Mapping,Session and Workflow

In this lesson, create very simple mapping and execute by session and workflow.
Mapping is based on last lesson source and target definitions. Between source and target definition is source qualifier.

Source Qualifier
- When you add a relational or a flat file source definition to a mapping, you need to connect it to a Source Qualifier transformation. The Source Qualifier transformation represents the rows that the PowerCenter Server reads when it runs a sesson.
- Source Qualifier transformation can perform the following tasks:

• Join data originating from the same source database
• Filter rows when the PowerCenter Server reads source data
• Specify sorted ports
  
• Select only distinct values from the source
• Create a custom query to issue a special SELECT statement for the PowerCenter Server to read source data.

Session and Workflow for this lesson are very simple. Looks like most logic are in Mapping. Session just like diagram to link all Mapping. Workflow is just runtime instance of session.

Lesson Two, Source , Target Definitions and Target Tables

Creating Source Definitions:
In Source Analyzer, to create a source definition you have to have a source--Database , Flat File or XML.... So you can Import from source and edit it, but seems you can't create by your own. For edit, you can add , delete and update columns, and add some meta data to each table definition.

Creating Target Definitions:
In Target Designer, to create a target definition, you can drag and drop from source definitions table list or create table by your self. Nothing special just like create a normal table. But remember select correct database type.

Creating Target Tables:
In target Designer, you can create table in database and informatica SQL statement from your target table definitions.

Click Targets > Generate/Execute SQL.
In the File Name field, enter the SQL DDL file name.
Select the Create Table, Drop Table, Foreign Key and Primary Key options.
Click the Generate and Execute button.

So from what I did and see , it's simple to create source and target definition. Just like create new tables in database. One thing I don't like, after important source definition from database. The layout view is not well arranged like other database logic view. you have to rearrange them by yourself. So many software can do this job automatically, I don't know why PowerCenter Designer can't do this.

Lesson One, Respository Users, Groups and Folder

This lesson is pretty simple just show you how to connect to repository. My environment was already setup by my company's administrator. So I do need worry about install and setup repository server and service.

If you don't have environment, you have to install server and do configuration them by yourself. I tale looked that part, just need very powerful machine. So I just ignore that.

For this lesson, includes connect to a informatica repository (domain, server, port , username and password) , create groups and create a folder. Setup permission on that created folder. All these did in Informatica PowerCenter Repository Manager. Nothing special, everybody can under stand it easily.

And in this lesson also includes creating source tables and data for this tutorial. You need have a database and execute appropriate SQL file in PowerCenter Designer. I used smpl_ms.sql for SQL Server. This SQL file includes table’s schema and data. Informatica PowerCenter connects database target or source by ODBC. I installed MS SQL Server Express in my local box. It's free and seems ok, doesn’t eat too much resources compare to oracle. When you setup ODBC, server is "locahost\sqlexpress".

After SQL execute you can see few new tables with some data in sql server mater database.