Birthday Non-Blues

Birthdays...wow they just keep piling up. Once I past the magic half century I started to notice a few things about birthdays.

1. Things became less important than relationships - I mean how many new tools, ties, electronic gadgets do you really need? I have a bounty, so many 'things' that I had to build an addition to the storage shed we built last year. I'd rather have family and friends around an capture memory than any item.

2. Food tastes better - yes I know I have to watch my weight more now but really, since I have to watch it, every morsel I eat should be important and taste good.

3. Less money - Last year I changed jobs, from high pressure to low pressure. From management to technical (see my other posts to this blog). I took a cut in pay at a time when we had 3 kids in college (thanks to my understanding and supportive wife!) but it paid off in mental health and because we now commute together the difference in wages is down to just a couple of thousand dollars.

4. Spiritual Self - I cannot believe that I, a devout non-believer have found religion. Of course it is still a journey but the 'faith' component of my life is sure filling in holes that were in need of filling.

5. Music - I took clarinet lessons as a kid, dropped them in the 8th grade, picked it up again four years a ago, dropped it when I returned to school and now am taking voice lessons. I'll never be a great singer but it is so enjoyable that I do it just for that alone.

6. Short time - OK, when you cross the 50 bridge (I crossed it a little while back) you know that the light at the end of the tunnel is your exit. I get it, time is finite not infinite. There is a limited supply and every day waste will never be given back.

So live, love, and follow your passions.

DataStage and SAP BI 7 continues..

So, it has been a while but we are finally over the major hurdles of the transition from BW3 to BI7 (SAP Business Warehouse releases). To date here is a micro summary of the problems discovered along the way:
1. BW sends NULLS, BI doesn't (watch all those stored procedures that tested for NULL fail!)
2. BW was ASCII, BI7 is Unicode (DataStage needs NLS to work with this and it is buggy)
3. BW worked through a proprietary interface, BI uses RFC services. The RFC is a resource hog on the BI side and does not like to release connections when it is through. DataStage 7.5.1 wont get the OHREQUID,DATAPAKID, & RECORD unless you patch a variable on the BI side
4. Watch for special characters in the BI data set corrupting the data when it gets to DataStage, IBM is working on this one.
5. Study the process for Quelling a delta load after it fails, you are going to need it
6. Timing is everything. If you are initiating the load from the DataStage side, be prepared to wait a while and set the timeout variable high enough. Better yet, set it up so that the DataStage job is activated by the BI side, then all you have to worry about is permissions

OK- there were many more problems, most of them were just the steep learning curve I or the SAP team faced in the transition. It is mostly working now and if anyone wants to know more they can post something.

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....