Expressions so slow IBM advised dropping to native SQL

[GoWestGw]

Fellow Data Manager Cognoids from a frustrated DM guy!

Fed up with Data Manager performance we got an IBM consultant on site last week for a healthcheck.

The summary is pretty much that Data Manager is struggling to do the Transform part of ETL and that we should write our functions in SQL-Server.

Any DM guy using the product in the UK for the last 5-10 years will use MakeBKey and CleanCode functions, from an ex-Cognos consultant.  Well these functions, which call Cognos functions, cause DM performance to slow to a snails pace at present.

We are running on Windows with loads of RAM and decent CPUs, reading and delivering to SQL-Server 2005.  Performance would indicate 200k rows per minute, when we were getting 100k rows per minute 10 years ago!

Why oh why did they not deliver a true 64-bit application, oh and invest in the product as it seems the same as it's always been.  It's almost as if IBM have given up the ghost on the product and want us to go elsewhere.

This current Cognos site and others are concerned if Data Manager is the product to use going forward.  Such a pity we are thinking this way as it was a great product and shock horror we are considering moving onto SSIS.

Anybody know what's the issue with functions in thous latest release of Data Manager.  Oh it's no different in 8 or 10, performance is dire either way.

EG a build with 10 columns and 5 derivations using just a CleanCode embedded in a MakeBkey user function took 43 minutes to deliver 2million rows. After adding 5 more derivations referencing a MakeBkey function with 5 parameters (but not delivering them) the run time increased to 93 minutes.  The build with no functions takes 3 minutes.

[MFGF]

Any DM guy using the product in the UK for the last 5-10 years will use MakeBKey and CleanCode functions, from an ex-Cognos consultant.  Well these functions, which call Cognos functions, cause DM performance to slow to a snails pace at present.

Whoa!  I'm a UK guy using DM since it was DecisionStream 6.0, and I don't use MakeBKey and CleanCode.  I don't recognise them as inbuilt functions, so I can only assume they are UDFs, most likely cobbled together by my old friend RJ?

If that's the case, they were written a long, long time ago, and probably need revisiting to see if they are suitable and optimal based on current product functionality.  I know Data Manager isn't the be-all and end-all, but it does seem a little unfair to bemoan IBM for functions they haven't built directly and are not in control of.  Mind you, bemoaning IBM is fun, I admit, and great sport for a Thursday afternoon :)

I'd be interested to see how those functions work - there may well be better ways to achieve what they do - possibly within Data Manager or alternatively by pushing the work to the database?  It's difficult to know without more knowledge though.

Best regards,

MF.

[cognosjon]

RJ??

Surely you don't mean the legend that is Mr Roger Jackson?

[MFGF]

The very same. :)

[cognosjon]

I had the pleaure of working with Roger for just over a year. Top bloke and I'm sure he won't mind me saying it, but he's crazy with it......

Pleased to say I still use some of his User Defined Functions and they tick over quite nicely.

[MFGF]

The man is a complete genius and a legend in his own right.  Just don't ask him a question unless you have two hours to spare listening to the answer! ;)

[cognosjon]

Just don't ask him a question unless you have two hours to spare listening to the answer! ;)

You certainly know him very well!!!!   ;D

[MFGF]

You certainly know him very well!!!!   ;D

Yep!  I also know a clever bloke called JC too, but not quite as well as Mr RJ :)

MF.

[cognosjon]

hang on a cotton picking minute........

would you happen to own a smart car? and would you also happen to know of Number 2 that works with Mr JC?

[MFGF]

hang on a cotton picking minute........

would you happen to own a smart car? and would you also happen to know of Number 2 that works with Mr JC?

A smart car as in the Smart brand?  Or a car which is smart.  The former is definitely untrue, the latter... well, it's a matter of opinion.  Someone told me recently my car was hot! :)  Does that count?

So, put it all together...  Initials MF... knows Data Manager... (or at least pretends to!) Knows RJ... Knows JC...

Who taught your Data Manager course, by the way?

[cognosjon]

Crispen Dry????

[MFGF]

Him?  Nah - he's a complete muppet! ;)