tracking down a property in the .ctg file (ISNULL '1')

[Dave2024]

Hello everyone, happy New Year to you and yours.

I've inherited a legacy Data Manager catalog (10.2.1) that works the bulk of our daily ETL.
We are running this on a Windows Server 2016 box and using SQL Server 2019 for the Catalog Content.

One of the users got into a detailed examination of their source, and the results in the data warehouse, and it seems that there is an 'order number' that is sent to us NULL, but appears in the DW with a value of 1.

Long story short, but an examination of the .ctg file shows for that element description::

LELEDEFINE +3 IFNULL '1'

Lovely, that explains where the '1' is coming from.
However, I can NOT locate where that property is set.
It is obviously not in the very simple SQL call from the source.
It is not a derivation in the "DataStream" or the "Transformation Model" step of that Fact delivery.

Where would I locate that property please?
I've examined the 'properties' of every one of those Job steps -> no joy.
It would seem logical that it's somewhere around the 'resulting columns' but I'm stumped here.

I rebuilt the Fact delivery from scratch, and that element definition does not appear in the resulting .ctg file -> and NULL comes through into my test delivery table so I am definitely missing something somewhere.

Any and all assistance is appreciated :)
-D.

[Dave2024]

Ok, so I found this.
For anyone interested, it's in the Table Delivery properties, under the Element properties.

It is NOT obvious, but if you double click in any Element item, under "Element Properties", a 'value if NULL' box comes up.
Trap and replace is handled there :)

[MFGF]

Hello everyone, happy New Year to you and yours.

I've inherited a legacy Data Manager catalog (10.2.1) that works the bulk of our daily ETL.
We are running this on a Windows Server 2016 box and using SQL Server 2019 for the Catalog Content.

One of the users got into a detailed examination of their source, and the results in the data warehouse, and it seems that there is an 'order number' that is sent to us NULL, but appears in the DW with a value of 1.

Long story short, but an examination of the .ctg file shows for that element description::

LELEDEFINE +3 IFNULL '1'

Lovely, that explains where the '1' is coming from.
However, I can NOT locate where that property is set.
It is obviously not in the very simple SQL call from the source.
It is not a derivation in the "DataStream" or the "Transformation Model" step of that Fact delivery.

Where would I locate that property please?
I've examined the 'properties' of every one of those Job steps -> no joy.
It would seem logical that it's somewhere around the 'resulting columns' but I'm stumped here.

I rebuilt the Fact delivery from scratch, and that element definition does not appear in the resulting .ctg file -> and NULL comes through into my test delivery table so I am definitely missing something somewhere.

Any and all assistance is appreciated :)
-D.

Hi,

One easy way to find where it lives might be to use the "Document Catalog" option from within Designer. Once you have the document (which is rendered as HTML), you could try searching to see where the syntax has been defined (if it has been entered as syntax).

If that doesn't work for any reason, then you're going to have to look through methodically. It sounds like you have been focusing on a specific Fact Build, and having no luck (the SQL queries and datastream/transformation model would be the first places to look). Are there any Derived Dimension elements in the Transformation Model, and have you checked these?
My hunch here, though, is that it's being set in your delivery module (where you are writing the contents of the build to a delivery target). You didn't specify what type(s) of delivery module(s) the build has, but my guess is it's a Relational Table Delivery Module. If you go into the properties of the delivery module(s) and look at Element Properties, you should see a "Value if null" property for each element. My guess is the element in question has a 1 in this property?

Failing that, I'd maybe look at the definition of any variables in the properties of the build. These can have an initial value set by an expression, but I suspect this is unlikely.
Are there any jobstreams in play? Procedure Nodes in a jobstream can fire off syntax, and/or SQL nodes can contain database syntax that can replace nulls. Again, I suspect this is very unlikely.

Let us know what you find!

Cheers!

MF.

[MFGF]

Ok, so I found this.
For anyone interested, it's in the Table Delivery properties, under the Element properties.

It is NOT obvious, but if you double click in any Element item, under "Element Properties", a 'value if NULL' box comes up.
Trap and replace is handled there :)

Ah. You beat me to it! Thanks for the update - glad you managed to find it!

Cheers!

MF.