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 »

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.

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

Hi,
Today had an issue in production. Right after I moved my job modification to production. To aggravate things, the record count was the highest I had seen in some time! 3 times the average count!

I had to tune the account receivables fact job. I did some changes to the job based on
1. Index modification
2. A few structural changes in DI for better performance
3. Caching lookups, table comparisons whenever small
4. Parallel loading at some places rather than sequential.
5. Other changes

This job had 2 target tables. AR and AR line table. And AR Line had an integrity constraint that AR be loaded first so that the foreign keys are already populated. I didnt know about this constraint.

I went ahead and modified the job such that all data flows that load AR run in parallel with ones that load AR Line table. Clever move, only if that integrity constraint had not been there.

You might ask me “what happened to a process called testing?”

I did test it. And successfully. Wow! How?

The data in my test environment and QA environment only had data upto April 30th. And these records had already been loaded into the AR table.

When I ran my job, it only did updates and no inserts. So, it looked fine. And passed the constraint.

The issue would have occurred only when AR Line was populated first with a new record that was not loaded in AR first.

I should have deleted the records in AR table and AR Line table for atleast one case and tried loading it as an entirely insert load. That might have caught the error.

Today, had to revert the job modification. Talk about proper testing and any tester worth his salt will not appreciate this!

Well, you have to learn from your mistakes. I love to learn from my stumblings!

select dataflow_name, ROW_COUNT, to_date(START_time,’yyyy.mm.dd hh24:mi:ss’), to_date(end_time,’yyyy.mm.dd hh24:mi:ss’), trunc((EXECUTION_TIME/3600))’ Hr ‘trunc((EXECUTION_TIME - trunc((EXECUTION_TIME/3600))*3600)/60)’ Min’ as execution_time
from ALVW_FLOW_STAT
where OBJECT_TYPE = ‘Oracle Loader’
And dataflow_name =’&DF_NAME’
order by end_time desc