Importing and exporting in Informatica Powercenter

When you work with Informatica Powercenter you probably have a development and production repository. This means you will have to transfer workflows form one repository to the other. You can use the copy function, but that doesn't work very well.

Instead you can use the import and export function. This works pretty good, but be sure to use the import and export function in the repository manager. The XML created by the repository manager is far more extended then the one in the workflowmanager.

But its not all milk and cookies.I often noticed that after importing my connections where gone. Second, the folders for the sources in the development repository are different then the folders in the production repository (i.e. for management reasons). I my case, we had less folders in the production repository so we had to fit some sources to different folders. This was a bit tricky.

I used the following trick to speedup the process:

• Edit the XML using a good editor.
• Find the connectionstring and alter it to the correct production stringname.
• Find the foldernames and alter those to the correct production foldername.
• Safe the XML and start your import again.

i.e.

Bronnen" DESCRIPTION ="" NAME ="Products" OBJECTVERSION ="1" OWNERNAME ="rd" VERSIONNUMBER ="1">

Bronnen" description="" name="Products" objectversion="1" ownername="rd" versionnumber="1">

Keeping track of table structures, its all in the name!

When developing table structures for datawarhousing, datamarts or any other purpose where you need an relational data model, it is often that we lose track of the combinations that are made. Especially when the number of tables in our database grows. To manage this problem we must pay attention naming the tables, views, cubes, etc.

First give your table the name of the project you are working on. For example: I am working on a product database project where I manage my production. I start with prefix prd_ or even better, I create a scheme called "Production". Then I describe the table purpose and the data it contains. In the productcase i'm loading data about products to my datawarehouse (DWH), so I use the following name: "Production.Product".

My attributes get similar names using short prefix which identifies the attribute.

Last I identify my foreign key and give it the name of the foreign table and then the name, so when I’m in a visual program working with the data, I still know which relations there are. Also it improves the management of the database and its applications. See the following example:

CREATE TABLE [Production].[DWH_Product](

[pk_Product_ID] [int] IDENTITY(1,1) NOT NULL,

[Product_Name] [dbo].[Name] NOT NULL,

[Product_Number] [nvarchar](25) NOT NULL,

[Product_SubcategoryID] [int] NULL,

[Product_ModifiedDate] [datetime] NOT NULL

)

CREATE TABLE [Production].[DHW_Productcategory](

[pk_ProductSubcategory_ID] [int] IDENTITY(1,1) NOT NULL,

[fk_DWH_Product_ProductID] [int] NOT NULL,

[Productcategory_Name] [dbo].[Name] NOT NULL,

[Productcategory_ModifiedDate] [datetime] NOT NULL

)

SQL: Getting just the datepart from a datetime

A nice little trick:

If you have a datetime field which has a date and a time in it, and you ony need the time, you can use the following code to cast it to a date only value:

select cast(floor(cast(getdate() as float)) as datetime)

First the code casts the datetime into a float. Then it rounds off the float leaving just the date. Then it casts the float back to datetime.

This trick is useful for SQL 2005. SQL 2008 has a function for this issue.

SSRS: Alternate colors for grouped rows issue

I came across a bit of a problem. I want to use alternate colors for rows grouped by department. I found three methods to realize this:

1. Using a piece of code determining if the row is even or not and based on that piece of code setting the background color. (http://olsv.blogspot.com/2009/08/alternating-background-colors-for-odd.html)

2. The second one is using the following expression in the background properties: =iif(RowNumber(Nothing) Mod 2, "Green", "White"). (http://msdn.microsoft.com/en-us/library/ms157328.aspx)

3. The last one is the same as the previous one, only the expression is different: =IIf( RunningValue (Fields!FieldName, CountDistinct, Nothing) MOD 2, Color1, Color2). (http://weblogs.sqlteam.com/joew/archive/2008/08/20/60691.aspx)

All methods work fine and the rows even and odd get alternate colors grouped by department....but… here is the problem: The report has an calculationfield. I sort my tablix on that field. The ordering is correct, but it colors get mixed up. They are set on the non-ordered set.

So I understand that SSRS first sets de background colors and then sorts the tablix, which leaves me with a bit of a problem.

I will post the fix when I find the solution.