ConnID in InfoMart?

[JTL]

I was looking, today, to see if I could find the ConnID in InfoMart, as I want to add it to some reports. Some of these reports are for 'preview dialling' so there's no strategy (as such) from which to attach the ConnID, so I would need to do some Agent Script or other development work in order to attach it in all scenarios as a KVP, so I'd rather look another way if possible!

Anyway, with some prompting, I found "something" in INTERACTION_FACT.MEDIA_SERVER_IXN_ID which Genesys describe as:

"The interaction ID, as reported by the interaction media server for the first call in the interaction. This ID might not be unique. In the case of voice interactions, the ID is the numeric version of the hexadecimal T-Server Conn ID. This field is not populated for multimedia."

Which sounds fine, but it is the numeric version, not the hex ConnID!

So:

1) is the proper ConnID (as per logfiles) stored in the GIM database anywhere, or

2) does anyone know how to convert the numeric version back to hex again?

I tried a binary to hex converter function built into SQL and it doesn't return the right ID

master.dbo.fn_varbintohexstr

Any ideas? :0

[blakshmikanth]

Please refer to blog post for this.

http://lakshmikanth.azurewebsites.net/link-connid-with-interaction-id-in-infomart/

[JTL]

Thanks Lucky... now just to see how I can use that in Business Objects universe, or in Interactive Insights reports

[blakshmikanth]

You need to create custom function to convert decimal to hex value.  I tried pasting custom code here but it was difficult to read it.

Refer to http://lakshmikanth.azurewebsites.net/how-to-get-connid-from-infomart-database/ for details.

[blakshmikanth]

hah...found it..

[code]

CREATE FUNCTION ConvertToBase
(
    @value AS BIGINT,
) RETURNS VARCHAR(MAX) AS BEGIN

    -- some variables
    DECLARE @characters CHAR(36),
            @result VARCHAR(MAX);
@base;

    -- the encoding string and the default result
    SELECT @characters = '0123456789abcdefghijklmnopqrstuvwxyz',
          @result = '';
  @base = 16;

    -- Convert it into hex
    WHILE @value > 0
        SELECT @result = SUBSTRING(@characters, @value % @base + 1, 1) + @result,
              @value = @value / @base;

-- Prefix 0
set @result = '0'+ @result;

    -- return our results
    RETURN @result;

END

[/code]

[PFCCWA]

I have quick unrelated question regarding Infomart.
does it require a stat server?
documentation suggests it does not...

[Flávio]

no. informart requires the data fom ICON and not from StatServer and that ICON is connected directly to the TServer.

so resuming...

TServer > ICON > Icon DB > InfoMart > InfoMart DB > Extract of report.

[JTL]

You're a star, Lucky... I'll see how I get on

Had to park this for a week as we had an unrelated major issue with reporting that needed urgent attention instead...

[JTL]

So the function works... but I'm struggling with the last bit, which is to create a Dimension for this in the Universe.

It doesn't like me calling the function from there...

[blakshmikanth]

Create view for this table and use it in Universe 

[JTL]

Ahh, that would be the easy way...

But as we already have a large number of custom reports, I wanted to try and add it as its own object in the universe so I didn't have to alter all the reports as well (unless I'm missing something).

A quick read of some BO forums, and it should be possible (with SQL 2005 and BO) to simply create the object in the universe and reference the function, in the form function_name(tablename.columname)

So:

dbo.converttobase(interaction_fact.media_server_ixn_id)

or to use fully qualified names, possibly something like:

GIM_81.dbo.converttobase(GIM_81.dbo.interaction_fact.media_server_ixn_id)

But it won't parse in the Universe Designer, and won't work in a report either.

I considered a Derived Table, and shift all of my reports to use that derived table instead, eg:

select *, dbo.converttobase(media_server_ixn_id) from interaction_fact

(or something similar) but as I said I'm trying to work around changing all of the reports which may need to use this ConniD. At the moment, if it was just a universe object, I could edit the report to add it. If I change the table for which the whole query is based, I'll have to redo each entire query.

Or do you mean create a table and do a join back to the INTERACTION_FACT table?

[blakshmikanth]

To use it database functions in BO, you need to edit .prm file, which is stored in data access folder. Refer to BO RDBMS guide for editing prm file.