One of the interesting aspects that is often overlooked by Informatica integrators is the fact that each PowerCenter Integration Service actually listens on a single port. When Informatica PowerCenter server is installed it seeks to have the available/usable port range defined by the server administrator. This default range sits in the 6000’s (see the Informatica PC documentation for precise details), but if one is so inclined, one could change the default range.  Ultimately since most people accept the defaults the admin console is accesses on port 6001, etc.  The rub here is that when you create and start an Informatica PC Integration Service by default that service gets assigned a random port to listen on for incoming requests.  Typically the random port isn’t so random and usually the assignment is one of the lower ports on the range selected during the installation. But what if you always want to know the port one should reference for an Integration Service specifically created for single integration?  Don’t worry this is possible.

Many of those involved with Oracle Business Intelligence (OBIEE) know that a setting is configured when setting up the Informatica OEM component for the pre-built analytic applications.  Most of these implementers don’t bother to understand what is being accomplished when setting this specific custom property, ServerPort.  Regardless of the implementation or integration by accessing the Integration Service’s properties, and creating a Custom Property called ServerPort and assigning an available port value, the integration service when it starts up (or restarted after initial creation of this custom property) will be assigned this port value to listen.

Here are a few short steps to follow in order to configure and test so that you can see how it works and use on your next project.

Run Netstat -a -n from a command prompt before configuring the custom property to see the random port your service is listening on.

Assign the Custom Property and Available Port value and restart the Integration Service by disabling and enabling the Integration Service.

Run netstat -a -n to verify that your Integration Service is listening on the port you’ve configured with the Custom Property value for ServerPort.

The reasons behind using this Custom Property may vary but the value should be evident. Good Luck.

I think two of the most under appreciated parts of a Business Intelligence and Data Warehousing project are gathering the project requirements and creating the action plan of implementing the solution, often referred to as “Change Management”. Now, some groups deal with each of these techniques with their own methodology. Others see this as part of the normal project process, haphazardly hitting the high-level topics and driving to the finish line hoping that no one asks them for the directions on how exactly they got there. Because after all isn’t the goal to provide the solution and not every atomic step it took to get there?

I sit on the fence with that question because the answer really depends on each project and each client. However, there are several consultant groups that earn their dollar by solely providing Content Management professional services.

Read the full story

At the base of any good BI project is a solid data warehouse or data mart.  We can talk star schemas all day long and about the variation of snowflake versus but the main goal of this post is acknowledging the neglecting of the fact table as the foundation on which the star schema’s house resides.  I see way too often clients with poorly performing retrievals against their subject areas as I enter new engagements and work from the client’s existing infrastructure.  This is really unacceptable not only from a professional implementation perspective but also from one of query performance.  This post will look at what the default indexing of any fact table should be, why the default indexing is needed, why composite keys are good and bad, and why you should care.

I owe this post to my buddies Owen and Greg.  This one has been a long time in the writing and I have finally gotten around to putting fingers to keys.

Default Indexing Best-Practice

Short and sweet - all foreign key columns should have a non-clustered non-unique index. 

So what does that mean?

It means that a foreign key reference on a column to a table containing the primary key reference only provides the database engine with a point-of-reference to your dimension table.  It does nothing for how the data in the fact table is organized.  So, after you create a foreign key (FK) DDL statement be sure to create the non-clustered, non-unique INDEX as well.
Read the full story