multisource IDT: connect BOE audit and csm database

building semantic layer
1

I created in the Information Design Tool (BO 4.1 SP6) a multisource data foundation. In this foundation I joined the BO CSM and Audit database.

Both databases have a different connection. Therefore 2 catalogs are created
Besides these 2 databases there is 3rd connection which point to our own database

Below the script which should join the Audit and CSM database.
@catalog (‘BOCSM’).“BOcsm_dev.dbo”.“CMS_InfoObjects7”.“SI_CUID”=@catalog(‘BOAUDIT’).“BOaudit_dev.dbo”.“ADS_EVENT”."Object_ID

When I validate this join I get an " invalid expression" error:
Types ‘NULL’ and ‘VARCHAR’ are not compatible for operator ‘=’

The column SI_CUID has a datatype varbinary and the column OBJECT_ID a varchar datatype. With CAST I tried to convert varbinary to varchar, but also this does not work.

How can I join the BOE Audit and CSM database in a multisource data foundation?

ebbengjo :netherlands: (BOB member since 2005-05-03)

2

As you’ve found out, varchar and varbinary are different data types.

When you say that using CAST does not work, have you tried casting varchar to varbinary?

3

When I convert varchar to varbinary I get “invalid datatype”.

At the moment I have a workaround. I created a view which converts the column CMS_InfoObjects7.SI_CUID to varchar. I am using this view instead of the table CMS_InfoObjects7 in the multisource DF.

ebbengjo :netherlands: (BOB member since 2005-05-03)

4

:slight_smile:

My next suggestion would have been a view. Quite why the works but the cast doesn’t, I’m not sure.