In this article, let us see what a staging area is, its types and the reason to have one in your data warehouse.

Ok, what is a stage area?
It is that part of a data warehouse where data is stored physically on disks or files (or whatever)… but as an intermediate step before loading the data warehouse / data marts. It is where activities like cleansing, de-duplication, etc take place. It is like a pit stop for a racing car before reaching the destination.

Some characteristics of the staging area are

1. accessible to and owned by ETL / DW team
2. OLAP / reporting teams do not have access to it
3. indexed very little
4. ETL developers are usually free to create / drop tables, controlled though (by the architect or modeling team)

Types of staging areas:

1. Persistent staging - stage data is not deleted, if you want to maintain history.
2. Transient staging - stage data is deleted after each ETL load

Most data warehouses have one or more staging areas, the types being either persistent or transient or both.

But should you really have a stage area? Can’t you do without it? After all these days, ETL tools are more capable of handling more data in memory fully.

Is staging necessary or is it an overhead?

Continue Reading »

Estimation of ETL effort is not always fun. (as with any estimation).

There are several ways of estimating the effort needed to complete an ETL job. Work Breakdown Structure (WBS) is popular. And so is Function Point Analysis (FPA).

But the most widely used is the one that factors in complexity based on the understanding of things like source, target, resources on project, etc.

Though I haven’t really seen anyone use this method to perfection, it is a good place to start with. Some people argue against this method, but I see this as a complementary option to whatever method you have.

So, here is a list of points that I think would be useful when you do any ETL effort estimation. I have grouped it under 5 heads: Source, target, transformations, resources, other.

Source based:

1. No of different sources & types
2. Incremental extraction needs
3. Profiling of data sources
4. Cleansing / de-duplication dirty data sources
5. Availability of documentation / transition of knowledge of source data
6. Access control & management, if needed
7. Data volumes for unit testing

Continue Reading »

Recently I was working with a few DB2 UDB 9.5 tables and discovered that decfloat datatype columns were not recognized even if I had checked the option to recognize unsupported datatypes as varchar.

I mailed someone who interacts closely with the product group in SAP - Business Objects.

Continue Reading »

Here is a query that you can run on the BODI repository to fetch the job schedule details.

Select upper (al_lang.NAME) as jobname,
upper (al_sched_info.sched_name) as schedule_name,
al_sched_info.start_time as start_time,
al_sched_info.host_name host_server
from di_edw.al_lang al_lang full outer join di_edw.al_sched_info al_sched_info on al_lang.guid = al_sched_info.job_guid
where active = 1
and al_lang.object_type = 0
and TYPE = 0
and al_lang.object_key =
(SELECT MAX (object_key)
FROM di_edw.al_lang l
WHERE l.NAME = al_lang.NAME AND l.object_type = 0 AND l.TYPE = 0)
ORDER BY 1, 2

Recently we migrated an entire DW module from Oracle to DB2. And our ETL was done using Business Objects Data Integrator.

Below is a sequence of steps that we followed to migrate the entire solution (including data structures, ETL code and universe)

Assumption:
–>All estimates done for 4 DB2 databases, 3 federated systems, 62 tables, 33 indexes, 6 DI jobs, 110 data flows, 151 transformations, 16 SQL transforms, 34 SQL() scripts, 220 objects in 23 classes and 2 reports.
–>Add 20% time as buffer for unexpected issues.
–>Some tasks can be executed in parallel, while some have strong dependency on completion of previous tasks.

Work breakdown of migration and rough estimation:

1. Initiation & planning : 40 Hrs
Establish objectives and goals
Requirements gathering
Establish scope
Plan resources
Identify development environment

2. Assessment : 40 Hrs
Assess tool and technology needs
Assess and understand technical requirements
Establish technical guidelines
Finalize tools and development environment

Continue Reading »

BO Data Services is a new offering from Business Objects.

Well, it looks like a new bundling and not a new product by itself.

BO Data quality + BO Data Integrator = BO Data Services.

I tried to gather some information regarding this. Here is what I have found so far.

Continue Reading »

Looks like traditional data warehousing will soon be taken over by a sea of changes.

(Click on underlined words to open the links)

Integration of OLTP with DW? Read about it here.

Here is a discussion on new approaches in DW.

If you want to know Werner’s opinion on the future of Business Objects Data Integrator (BODI), see here.

Appears like BO Data quality + BO Data Integrator = BO Data Services. A discussion on it here.

And is Informatica the next acquisition target? Some say maybe… some say no.

If you are an ETL developer, you would be handling sources like Oracle, DB2, flat files, etc.

What if your source was a website? Or a Yahoo search result? And what if you could play around with multiple websites, blogs, flickr, custom searches… mash it up together, edit and transform it to create your own version of what you want to read.

Welcome to Yahoo Pipes!

Continue Reading »

In the last few days I have been particularly keen on knowing (just knowing.. not exploring in detail) the capabilities of SAP BW to develop an Enterprise Data Warehouse (EDW).

And this link was pretty useful.

As of now, I really don’t have a clear understanding of SAP BW. But I understand that it is

1. proprietary.
2. not that great with non-SAP environments.
3. expensive.
4. not flexible enough.

If you know more about the capabilities of SAP BW to develop an EDW, please do let me know.

Hmmm….. sounds interesting.

There was this news some weeks ago.

Now, there’s more to it. Read more at this link.

Oracle bought Hyperion. Now SAP wants BO. What will Microsoft do ????

Whats the future of Cognos or Informatica?

Will there be cosolidation in the business intelligence space? Looks obvious.

If you are working on Business objects tools, will SAP buyout help you? Will SAP (strong player in ERP) be keen on BI? Does it make sense?

Okay. For better reading on this topic, see these links.

SAP buys Business Objects - good news for IBM

SAP To Acquire Business Objects: A Complicated Merger