How to connect to CMS database in Informix

building semantic layer
1

Guys can you please help me out in doing this.

We have data stored in CMS database in Infomix and used to dump the data to SQL server tables.Now the client want to create a univese (6.x) on Informix CMS database.
Can some one help me in creating a connection to get the data from Informix?

Thanks in advance.
Praveen

nukalapr (BOB member since 2004-10-29)

2

You’ve completely confused me. The CMS does not exist in 6.x. Are you trying to build a universe in Designer 6.x against a XI CMS database? If so, don’t waste your time. The CMS database stores data in binary format, so you can’t read by running SQL queries against it.

MichaelWelter :vatican_city: (BOB member since 2002-08-08)

3

Hi Michael Welter,

I am sorry for confusing you saying CMS , this not related to XI version .We have a database in Informix called “CMS” used for phone availability and attendance service. The data from this CMS is dumped into SQL server; from SQL server we created a universe. And the client wants to get the data directly from Informix.

My question is how can we get the raw data directly from Informix but not via SQL server?

Thanks
Praveen

nukalapr (BOB member since 2004-10-29)

4

Can you connect to it using an ODBC connection?

MichaelWelter :vatican_city: (BOB member since 2002-08-08)

5

I am not sure how to connect using ODB.What driver should i use.

nukalapr (BOB member since 2004-10-29)

6

Sorry for an irrelevent topic, but Michel, there is a way to use SQL queries and get information from XI CMS. Use the query builder in business objects XI to query the CMS tables using simple SQL.

SELECT TOP 100 FROM CI_INFOOBJECTS WHERE SI_KIND = 'Webi'

PJ

pj820 (BOB member since 2005-10-07)

7

Hi Praveen,

Any updates on this?

I am looking forward to connect to Informix through CR XI and BV. Any tips ralated to connectivity will really help.

Regards,
Sachin J.

Sachin J :india: (BOB member since 2005-09-23)

8

You need to read the compatibility guide.

BO/Crystal can use ODBC, so Informix should not be a problem.

Have you tried?

Steve Krandel :us: (BOB member since 2002-06-25)

9

Hi Steve,

I have gone through the platforms and installation guides and have found that I can connect to Informix Dynamic Database 9.3/4.

I want to know about other undocumented problems, if any one have faced so far.

Forum topics search provides lot many inputs related to development issues, which is great.

Regards,
Sachin J.

Sachin J :india: (BOB member since 2005-09-23)

10

Hi All,

We use a ODBC connection to informix (actually the same CMS, nothing to do with BO’s CMS, database). The driver we use is an Openlink generic ODBC driver. Everything works fine except refreshing Deski documents via infoview.

We can build reports in webi, refresh the deski reports on the server. All using the same user credentials. If we refresh a deski report in infoview there is simply no response and the report hangs.

For us this is a problem because we can not schedule any reports using the CMS database.

Any solutions ? Different ODBC driver ?

mterlaak (BOB member since 2006-10-23)

11

Hi,

I take it you are talking about avaya CMS the telephony system? If you we have put business objects v6.51 over CMS r12/13 with great sucess. We used the openlink ODBC drivers and it performs just fine I would be interested in what reports you are looking at running

kevin_ross100 (BOB member since 2007-03-15)

12

Hi kevin,

Indeed Avaya CMS on an informix/solaris database. We are reporting all kind of campaign statistics. Performs just fine except when running desktop intelligence reports via infoview, or scheduling deski reports. Then the odbc connection hangs. I think this is caused by several instances trying to log in using the same user credentials.

Any experience using Webi / Infoview, or paramaters on the server ?

If we don’t solve this we will have to do extracts to a SQL server.

We also have trouble finding “wrong party” information in the database.

mterlaak (BOB member since 2006-10-23)

13

Sorry but I only have the desktop client installed here so dont have any experence with Webi / Infoview. We have multiple users loggin on under the same user name maybe 3 at the most not sure what happens with more than that!

This database item you are trying to find “wrong party” what does it relate to i.e do the agents press a button on their phones to indicate it was a wrong party? I have the full database instruction manual from avaya so can find most things!

kevin_ross100 (BOB member since 2007-03-15)

14

Thx Kevin,

We contacted Avaya and it seems the “Wrong Party” is stored in the outbound part of Avaya and only in the campaign tables.

mterlaak (BOB member since 2006-10-23)

15

Hi There,

Thanks for this post, we are looking at completing a similar project, integrating data from several sources into historical reports. Did you create a universe for CMS or is there one on the market for sale?

Would anyone be interested in collaboratively sharing the costs of having a universe built, sharing their companies universe or similar?

Thanks

Richard

richard.e.morton (BOB member since 2007-11-26)

16

Richard,

Welcome to B:bob:B!

A universe cannot be built on the CMS database, as the data is stored in binary format, and, therefore, is not readable via SQL. You can use the Query Builder tool, or SDK, to access the data in the CMS Database.

MichaelWelter :vatican_city: (BOB member since 2002-08-08)

17

Hello,

Any one have an idea how could I get database schema for CMS database in Informix of Avaya.
I want data of currently active(logged in) agent list with their states.

Appreciate any kind of help.

shahamit (BOB member since 2008-04-17)

18

Welcome to B:bob:B!

You can’t read the CMS database, so it won’t do any good to know the schema. You can see active sessions in CMC - Servers - CMS.

MichaelWelter :vatican_city: (BOB member since 2002-08-08)

19

Again about accessing Avaya CMS Informix database.
We have Informix 2000 and there is an issue with grouping.
Looks like he is not supporting grouping for expression.

For BO generates this SQL:
SELECT
year(row_date),
sum(acdcalls)
FROM hagent
WHERE row_date=today-10
group by year(row_date)

Which is falling with “syntax error”

Only one way to is to use ordinal number:
SELECT
year(row_date),
sum(acdcalls)
FROM hagent
WHERE row_date=today-10
group by 1

But I dont know how to force BO to generate that kind of SQL :hb:

travian :czech_republic: (BOB member since 2007-12-05)

20

In webi and desktop there’s a file, informix.prm that has a parameter to set:

GROUPBYCOL=YES

I think a restart is required

Patrick

Patrick McDonough (BOB member since 2002-08-20)