Using Informix IDS 10.0 as Data Source for SQL 2K Cube

[jwm68]

Greetings:

I am a n00b trying to create a new SQL Server 2000 Analysis Services Cube using Informix IDS 10.0 as the data source. The trick is that the raw data needs to stay in IDS -- no ETL to get the data into SQL Server.

I have installed the Informix client stuff on my SQL Server computer, but when I try to use the "normal" way to create a cube, I am having problems. Here is what I am doing:

1. Right-click "Data Sources" folder under the new AS database I just created -- select "New Data Source".
2. Select "Informix OLE DB Provider".
3. Type in connection info, and click "Test Connection". The test succeeds!!!!!
4. Save it.
5. Right-click "Cubes" folder under the new AS database -- select "New Cube", then "Wizard".
6. When I am supposed to see a list of tables from my newly created data source, I see just an error that says AS cannot connect to IDS.

I am confused by the fact that AS is apparently either using different info or a different method to connect to IDS than what it used when I clicked "Test Connection" when I created the data source.

Hopefully, someone has some past experience getting these 2 products to "play nice".

Any assistance would be greatly appreciated.

-Joe

 

[johnherman]

Does your connection account have SELECT access to the tables on Informix?

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[jwm68]

Thanks for chiming in, John.

There is only one account for Informix, with access to everything, as far as I know. I have used that same account to connect via Server Studio JE and CREATE, SELECT, INSERT and UPDATE. Maybe I will create a second account and explicitly give it the proper access to the table in question, if you think that will help.

-Joe

 

[johnherman]

No. No need for another account.

You may notice that the Test Connection strongly resembles the Test Connection button in ODBC Data Sources and I suspect they work the same.

I'm just going to ramble a few things.
- Is the SQL Server account a member of OLAP Administrators?
- Have you tried building a cube (start with a dimension) without the wizard (perhaps the wizard only works with SQL Server data sources)?
- Can you talk to Informix using the Microsoft "Linked Server" concept? (i.e. SELECT from table in SQL Server instead of Analysis Services)
- Have you checked Microsoft online?

http://msdn2.microsoft.com/en-us/library/ms175609.aspx

- Have you checked with MS Developer Network and Knowledge base?

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright