Skip to Content

(I)nformix to (I)n-Memory – My B(I) Story


This blog is written in response to this blog. I use B(I) to manage or plan (I)nfrastructure requirements using Central Performance History(CPH) feature.

Should I talk about I in BI or W(e) in BW? Time travel query was not available when I began my career. So I don’t know when DSS changed to DW to BW to BI and then back to BW. oh, I forgot about EIS.

When did I start using BI concept first? A few ramblings…

  • Would (I)ndex section in books count as BI tool? The (I)ndex Section helps one to (I)dentify all critical words/phrases used in the book. If one is looking for details for a word or group of words used in the book, he/she can quickly review the Index section and then go to pages (Drill-back?) listed under that word/group of words. The Index section provides one-level hierarchy with drill-down feature. (well, it is probably not drill-down but rather drill-back?).
  • Would Phone book with yellow pages count as BI tool?
  • How about (I)ndexing system in the Libraries?

It seems BI concept is not something new; it appears all of us had been using BI informally for a long time before the term BI was coined.

(I)nformix to (I)n-Memory…

   “Informix and Oracle Billboard wars: The battle over Highway 101” – More details here.

/wp-content/uploads/2012/04/billboard_93053.gifIt was 1996. One year after I moved to Tampa, Fl from Jacksonville, Fl. I was working as Informix Database Administrator for arguably the largest telecom company. Luckily I was assigned to the first (official)Data Warehouse project. Initially the database size was ~100GB, yes Giga Bytes. With historic data for 3 years, the size was expected to be ~500GB, one of the largest DW systems in the USA at that time.

Photo Credit

ERwin.gifRemember ERwin, Entity-Relationship Data Modeling Tool from Platinum(acquired by CA in 1998)? Yes, we used ERwin to model our first DW project. All I knew at that time was data models of 1st, 2nd and 3rd normal forms. Star Schema was something new. Without a good understanding of Star Schema, we designed tables for master data and fact tables. I believe all of them fit in a page or two. Informix DSA(Dynamic Scalable Architecture) just was released. It had a few nice features. One of them was partitioning. We decided to use it for our DW project. It worked so well.

Informix recommended two different configurations: one for data load and another for reporting. So we had to bounce the DB instance before loading data over the weekend. And initially we struggled to get all data loaded on time. We wanted to load around 600+ records per second; but we could load only 30-40 records per second. We used home-grown ESQL/C application to load data from flat files downloaded from mainframe.

Even though our server(HP-9000) had 4GB(or 8GB?) of memory, we could allocate only 2GB to Informix. After struggling for 2-3 months, Informix decided to send an engineer from Advanced Technical Support group. She flew in from Atlanta, taught us Star Schema and fixed our configuration issue. All we needed to make was one parameter change to the DB. The performance gain we observed with that change was phenomenal!!!

Fast Forward to SAP BW!!!

Photo Credit

SAP.jpgFew years later, I started working on SAP-BW/Oracle project. My programming experience, DB knowledge and Data Warehouse exposure helped me pick up SAP-BW quickly. I literally read every page of for BW 2.1c to understand SAP’s Extended Star Schema. Looking back, it was a great investment of time and efforts. My primary responsibility was to support Oracle database.

I noticed no one from Basis was supporting BW team. And they were struggling to load data from flat files. I don’t remember how long data load jobs took. All I know is that it took very long time to load few thousands of records. Except SAP-BW, I had several years of experience in other layers – Unix, DB and DW. And by reading SAP manual, I gained a good level of theoretical SAP-BW knowledge. With that theoretical knowledge and analysis, I was able to conclude with a reasonable level of certainty that SAP-BW layer was not causing the slowness.

I continued my analysis; tried to understand how BW team was loading data. Since they were loading data from flat files, I didn’t have to worry about source system configuration, SQL tuning etc.

Finally there was Eureka moment; I found there were thousands of files in the directory from where they were loading data. Each file contained very few records, less than 50. They were opening, reading and closing hundreds of files to load few thousands of records. Opening/reading/closing multiple files was causing the performance problem I guessed. I mentioned this to BW team. After conference call, we decided to merge files at Unix level – Unix administrator didn’t believe this would help – and had BW team load data. Yes, it improved the performance significantly. This was my (I)ntroduction to SAP-BW.

(I)nfrastructure Management

Picture Credit

CPH.gifYou might have already guessed my role. I’m not front-end guy; I’m Basis Administrator with some BW knowledge. So at TechEd-’11, I attended Hands-On session on  Central Performance History(CPH). I came back and implemented CPH at my client’s site to manage/plan (I)nfrastructure requirements.

(I)n-Memory Database

Technology excites me so I’ve taken baby steps to stay current with the latest and greatest technologies. Here is the outcome of those baby steps:



Be the first to leave a comment
You must be Logged on to comment or reply to a post.