Posted on 29 August 2011
Traditionally a slowly changing dimension (SCD) is created via some ETL tool, whether it be SSIS, OWB, ODI, or Informatica. The concept is fairly straightforward – capture important business changes to the metadata for each records within the dimension. For example, A Sales Rep dimension may have a rep’s name, region of sales responsibility, etc. If the rep’s sales region changes at any point in time, it may be important to the business and their analytics to capture when the point-in-time when this change occurs so that analytics can slearly show sales for the rep when s/he was in new region vs the previous region as well as both regions together. This can only be accomplished correctly using a SCD. This post doesn’t go into more detail about the different types of SCD. For that you can clearly check out the concepts exacerbated by Inmon or Kimball, etc.
Recently an opportunity came up to develop a SCD for an ETL process was solely written in PL/SQL. Without using some of the industry ETL tool that I mentioned above this can pose a significant challenge to most. After some Google searching I found only a handful of articles and blog posts that have even remotely broached this technical topic.
References:
How can you get expert guidance on how-to implement and troubleshoot Oracle GoldenGate?
To answer that question and many more, I invited John P. Jeffries, author of Oracle GoldenGate Implementer’s Guide, the first book written on the GoldenGate technology, to chat with the ArtOfBI.com Podcast.
Oracle GoldenGate is the real-time data integration tool that enables continuous data availability by capturing and delivering updates of critical information as the changes occur. It can also provide continuous data synchronization across heterogeneous environments.
On this Podcast
Just listen to the podcast and you’ll know that this is the first time I am interviewing a guest on the ArtOfBI.com podcast. John was more than polite in taking time out of his busy schedule to talk to us about GoldeGate, his experiences, his new book, and sharing information. Thanks again John, we’ll see you at OOW 2011.
Got Something to Talk About Related to Business Intelligence or Technology?
We are currently building our schedule for interviewing authors, bloggers, product developers, and C-Level folks. If you happen to be doing something new or ground-breaking with Business Intelligence, Data Warehousing, or EPM we would like to interview you or someone from your team about what you’ve achieved. You can email me directly at christian [at] artofbi [dot] com and we’ll get something on the books.
This Podcast (Issue #1)
The podcast can be played from the media enclosure above (Just press play). All feedback is of course very welcome.
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.
