How to connect InfiniteInsight Explorer to MS Excel?

[RafkeMagic]

Hi,

I'm currently "testing" the functionality of SAP II and I wanted to work with Explorer on an MS Excel file. I created an ODBC connection for Excel (Win7 64bit & Excel 32bit - so I had to use the ODBC manager for 32bit DSN), but it seems this is not working in Explorer. It keeps throwing errors of type "Message from ODBC: Connection failed: [IM014][Microsoft]". It states that the "architecture of the driver and the application" are not complient for the given DSN. However I can't find any specifications with regards to which versions (Win, Excel, ODBC) are supported and which not? I guess from my experience the II Explorer works on 64bit DSN only. Can someone confirm this, please?

Kind regards,

Raf

[Former Member · Accepted solution]

Hello,

Architecture errors on odbc means forexample your II installation is 64bit but your odbc driver for excel is 32bit, you may want to check that,

[Former Member · Accepted solution]

Does anyone have a solution to this? My installation and System in 64bit, My Excel and ODBC driver is 32bit (so as to be compatible with other SAP products)... what should I do if I want to use II Explorer?

[Former Member · Accepted solution]

Hi Ria,

It is not possible to use II Explorer on Excel files.

Explorer only enables you to connect to databases (which must be ODBC connections.)

Explorer generates SQL code that is executed by your database. This is why it's not possible with Excel.

There might be a solution: In InfiniteInsight - Toolkit section, you can use the Data Transfer feature to transfer the content of your Excel file to a real database (can be SAP Hana, Sybase IQ, MySQL, SQL Server...).

Once the data will be in a table, there will be no issue using Explorer.

Kind regards,

Armelle

[Former Member · Accepted solution]

Hi Armelle

Thanks for reply and useful solution to convert the excel files. However, I think my issue relates to the connection that needs to take place.

I've now done the following to follow your suggestion:

1. Choose II Toolkit to transfer the data

2. Select the excel file

3. Output Store/Space: Data Type = Data Base

4. Choose Next and get the following error message:

"Message from ODBC: Connection failed: [IM002][Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"

I'm using Windows 7 Pro, SP1, 64bit

I tried converting a text file to a database using the above methodology and also had the exact same error.

Any ideas on what to do?

Regards

Ria

[Former Member · Accepted solution]

Hi Ria,

The problem is on step 3 : Excel file is not a "database" data type for InfiniteInsight.

If you need to read your Excel data, in data type, you have to select Excel file :

The Excel data type has been disabled temporarily in InfiniteInsight versions 6.5.x->7.0.SP0

If it's not available in your current version, I suggest an upgrade to version 7.0 SP1 or to Predictive Analytics 2.0 (aka PA2.0).

Armelle

[Former Member · Accepted solution]

Apologies, I wasn't explicit in my steps:

I did choose the Excel file for the input and then chose Data base for the output. Let me break down my steps again properly:

1. Go to II Toolkit

2. Select Perform a Data Transfer

3. Select Data Set Source: Data Type = Excel files; Choose folder with excel file, Choose the excel file as the data set

4. Describe data set: Analyze. Happy with descriptions and storage formats

5. Variable Selection: include all variables

6. Create Data Set Copy: Data Type = Data Base; select folder for output and output file name

7. Receive error message: "Message from ODBC: Connection failed: [IM002][Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"

Any ideas?

[Former Member · Accepted solution]

Hi Ria,

I believe you're using the default ODBC connexions that are displayed by Microsoft ODBC manager :

dBase or Access. 

The issue is that none of them are actually configured and they're not pointing to any database unless you configured it already.

If you click on Configure, it will not point to any database.

You will have to configure it (and it's better to do it from the System DSN tab) before being able to use it from InfiniteInsight.

If you were using another connexion, there is something wrong with its setting. You should also go to the ODBC Administrator and check the settings again and Test the connexion from this pannel.

I hope it will now work.

Armelle

[Former Member · Accepted solution]

You're quite right. When I do click configure it points to nothing.

Apologies if the following questions are not smart:

1. Do I need to configure two Data Source Names - one for the input and one for the output?

2. How do I correctly configure them?

I have tried googling and searching the SCN for more help on this and am still unsure of what I'm doing

Thank you so much for you help

Regards

Ria

[RafkeMagic · Accepted solution]

ok, how nice to "hide" this temporarily... it would have really helped me almost a year ago...

[Former Member · Accepted solution]

You need to configure only once your database connection, it will be used to read data from your database and write in the database.

To configure your database connexion, you first need to make sure that you have access to a database, either installed on your machine or available through the network.

Then, you need to install on your machine (no matter where the DB is) a driver to access data. Be careful, drivers are available as 32bit or 64bit. If your version of InfiniteInsight is a 64bit application, you need to install a 64bit driver (most likely).

Then, you will probably need information from your database administrator to configure the connexion. You will need a login/password, the machine IP address, ports or services that are used...

The configuration steps for Windows are decribed in this document for most databases: https://websmp103.sap-ag.de/~sapidb/012002523100019285892014E/ii_7001_condb_win_conf_en.pdf

In general, all SAP InfiniteInsight documentation is available there: SAP InfiniteInsight® 7.0 – SAP Help Portal Page

Good luck!

Armelle

[RafkeMagic]

Ok, so the documentation (which can be found here - scroll down to Explorer User Guides and take English for 6.5 SP5) states the following

however, the documentation does not state which ODBCs àre compliant with SAP InfiniteInsight?!

Did a little test on a Win8.1 pc with Office 2013 64bit installed. I created a 64bit ODBC connection to an Excel file and... surprisingly it worked immediately.

So next questions:

• is it the Win8.1 that did the trick?
• or the Office 64bit?
• or the combination of both?

That is a real bummer actually, as most of the clients I work at don't have anything higher than Win7 and are still running Office 2010 (if already) at 32bit.

As far as I'm concerned, I can continue with my tests, but I don't really feel comfortable telling my clients it won't work with Office 32bit (which is still needed for Business Explorer by the way)?!

[Henry_Banks]

Annoyingly, the PAM https://websmp104.sap-ag.de/~sapidb/011000358700001142582013E doesn't explicitly call out Explorer compatibility requirements, but only Social, Recommendation and Factory..

[RafkeMagic]

next error, when I try to "create" a new entity (in Explorer - on the Win8.1 with Office 64bit pc), I get the following error

Impossible to lock the model repository in Synth_training_via_odbc.

Reason: : [42S02][Microsoft][ODBC Excel Driver] The Microsoft Access database engine could not find the object 'KxLocks'. Make sure the object exists and that you spell its name and the path name correctly. If 'KxLocks' is not a local object, check your network connection or contact the server administrator.

This should not occur, please contact your designated support address.

where "Synth_training_via_odbc" is my ODBC connection.

I'm "assuming" that this missing object is simply not available in the "trial" version? (I also assume that the "Kx"-prefix refers to KXen?)