DataStage and SAP extraction The Open Hub

So you are about to migrate (finally) from that ancient BW datawarehouse to the new spiffy SAP Netweaver enabled one you say? Well, thank goodness you have all your extracts in DataStage so migration should be pretty straight forward, right? Not so fast!

We started our migration almost a year ago. I was new to everything, new job, new software (both DataStage and SAP), new people. My first assignment would be to assess what the lelev of work would be in migrating our BW extracts to BI 7. I started with the Ascential (err...IBM) manuals for the SAP BW Pack. That's where I ran across this little tidbit..."During the initial install of the Ascential PACK for SAP BW you will be prompted to choose either the Open Hub Extract stage (3.5 or greater) or the Extract stage for supported versions less than 3.5 (less than 3.5). The
Open Hub Extract stage and the Extract stage cannot exist on the same machine."

Did you get that last line, "cannot exist on the same machine?" So, that's the first gotcha. You have to have a license to install this on another server. Fortunately, or DataStage (err. ..IBM Webshere) representative were great and they provided us with a time limited copy to help in the transition.

So, Lesson 1 - you need a separate server and license.

Of course the same is also true for your client install! So, you will need a second client install (on a different machine) so that you can migrate and test your DS jobs.

Lesson 2 - you need a separate place to put the client software if you intend on maintaining your existing code while you migrate.

I partially solved this second item by installing the client software on the new server and then just remoting in. But if you have several developers this can be a problem.

Next - DataStage and that pesky SAP GUI (along with passwords)

Getting Technical - DataStage and SAP BW

Let's say you have an SAP Business Warehouse and you want to get data out of it on a regular basis without going through the extract and FTP route. That's where a tool like DataStage (from IBM) comes in.

DataStage is an ETL tool (formerly form Ascential) that has as an option a connector capability to SAP or to SAP's Business Warehouse. Where I work our dependent data mart (MS SQL 2005) receives about 90% of its data from the SAP Datawarehouse. We don't use DataStage to place dat a back into BW (yet) so this will only be about downoading.

What am I going to write about?
My job for almost a year now has been to migrate our existing SAP BW to DataStage to SQL implementation from an SAP BW vs. 3 to and SAP BI vs. 7 implementation. I am fortunate that those that did the original work (BW 3 to DataStage to SQL) had done their homework pretty well.

This multipart posting will be about the work I had to do during the migration (still ongoing) and the learning curve/pactches/ and discoveries along the way. My hope is that someone else facing this type of migration will benefit from my experience.

Technical issues:
Our existing BW is BW 3: The new platform is BI 7 running on an IBM Linux server (12 CPU) somewhere in Europe. Our DataStage is version 7.5.1.A running on a Windows 2003 server. We are using the SAP BW pack version 4.2.1.

My next post: Why you cannot run both BW 3 and BI 7 DataStage on the same server...

Working for a living

I've had a few jobs in my life, worked in a hospital, computer operator, various programming jobs, systems analysis, entrepreneur, CEO, CIO, business broker, various consulting gigs, systems architect to name a few. Now I am a DBA (officially a data warehouse lead) at a multinational.

You'd think after that stint as a CEO I would be onto other things but I did a reevaluation and came to the conclusion that I really liked getting my hands on systems, solving problems and learning new technology. Plus, I really don't travel that much on business anymore which really fits what I want to be doing really well.

So I am going to start interspersing a few musings of discovery of a more technical nature in with these blogs. Specifically I am going add some of those 'ah ha" moments that I have run across in my new job. There are a lot of people out there with a lot more specific knowledge on some of the tools I am using/learning but I often am left to my own devices to solve problems (even after extensive googling!).

So here's what I am working on right now and I'll start posting what my discoveries are as I can.

My current project involves migrating the ETL processes from a SAP BW 3 to DataStage to MS SQL 2005 data warehouse project from BW 3 all the way to BI 7. To give you an idea of scope of the data, we daily transfer about 4m rows in 20 - 40 DataStage extract jobs.

My current nightmare involves the pesky DataStage Server 7.5.1.A Open Hub Extractor interface so my next post will focus on a few insights. Stay tuned....

New Job Saves Carbon

I have been at my new job for six weeks now (and am loving it!). In my old job I had to commute 52 miles south five days a week and 52 miles back north to get home (with occasional trips to Boston, another 92 miles each way). My wife meanwhile commuted 50 miles east every morning with a return trip back each night. Together we put over 200 miles on our cars every day!

I was able to cut my carbon output in the summer by riding my Kymco Scooter which gets 75 mpg. Of course, if it was raining or after September that option was out. It works out that between the two of us we used about 45 gallons of gas a week (my car gets about 25 and hers 20).

Well with the new job I now go to almost the same place as she does (4 miles apart) so we have been driving my car and riding together almost every day. We also discovered a back road route that only takes 10 minutes more in driving but is 10 miles shorter (plus we drive slower and now get 27 mpg on my car!). Our fuel consumption has dropped to only 15 - 16 gallons a week! That's 30 gallons less and a lot less carbon.

So why this rant? Well, it's about that old idea, car pooling. What ever happened to car pooling? It works and it saves. Granted my wife an I are the perfect couple for car pooling but still, why not resurrect the program for car pooling but this time put some teeth into it. Maybe make it a state program with tax incentives? Why not offer a special speed pass/ fast lane that lets car poolers ride free on toll roads?

Car pooling is not easy, it takes effort to find someone, to coordinate every day with car use, etc. but it could make a tremendous difference.