MS-Access and HANA Views

[Former Member]

I want to connect to HANA via ODBC from MS-Access. By doing this I can see TABLEs and write Access Queries to get data. But what I really would want is to connect to VIEWS (instead of Tables) - but Views do not show up in my ODBC list of "tables" to connect to. Is there any way to do this? (I have searched everywhere and my onw IT has no answers.)

Thanks

[Former Member · Accepted solution]

Here is another idea. Suppose I could send the output of a Calculated or Analytical View to a user table within HANA, then I could read that new table from MS-Access. Is there any way to redirect a View to write to a HANA-User created table?

Thanks

[former_member184768 · Accepted solution]

Hi Ira,

I think you can write a procedure / SQL statement with "Insert" into a User defined table in HANA with SELECT from the Calc / analytic view.

Alternatively you can write a procedure with CE functions to implement the functionality of Calc / Analytic view and then insert into a user defined table.

But, why do you need to build a calc / analytic view on "tables" and write to another "table". You can always use the underlying base table and fire select statement against it. Unless you have heavy summarization / aggregation to be performed and need the processed data to be inserted into another table. Still I am not very much convinced, but then I also do not know the exact requirement.

Regards,

Ravi

[Former Member · Accepted solution]

Thanks Ravi, for your quick response.

You are correct, that I have some complicated joins, filters, aggregations and find the Calc Views easier to build than SQL statements.

By any chance would you have a sample procedure with a single sample SQL statement. I have played with this a little but always seem to get the nomenclature on calling the tab;les & views wrong. I've struggled with this.

Thanks again.

[Former Member · Accepted solution]

What is wrong with this query:

SELECT MATNR from "pk-users"."pk-pricing"."CA_IJL_BASE_MM" into LUNDIJ.Z_GPT_TEST;

Both the Calc View and the table exist. I think the nomenclature around the view and table are wrong. The trees are like this:

Catalog-> LUNDIJ -> Tables -> Z_GPT_TEST

Content -> pk-users -> pk-pricing -> Calculation Views -> CA_IJL_BASE_MM

Thanks.

[patrickbachmann · Accepted solution]

Try

select * from "_SYS_BIC"."pk-users.pk-pricing.CA_IJL_BASE_MM" into "LUNDIJ"."Z_GPT_TEST"

-Patrick

[patrickbachmann · Accepted solution]

Actually sorry, I think it's more like this;

select * from "_SYS_BIC"."pk-users.pk-pricing/CA_IJL_BASE_MM" into "LUNDIJ/Z_GPT_TEST"

Basically look in schema _SYS_BIC under folder 'column views' to see the correct spelling of your views.

[Former Member · Accepted solution]

Thanks Patrick.

Any idea why I now get this error? The table is definately there.

"Could not find table/view LUNDIJ/Z_GPT_TEST in schema SYSTEM: line 5 col 72 (at pos 264)"

[patrickbachmann · Accepted solution]

It sounds like it's looking in the SYSTEM schema when it should be looking in _SYS_BIC.  Can you paste the exact line of code for line 5?

[Former Member · Accepted solution]

Just tried chaging LUNDIJ/Z_GPT_TEST to "LUNDIJ"."Z_GPT_TEST" and now get this error:

"feature not supported: INSERT/UPDATE/DELETE are not supported in the READ ONLY procedure/function: .."

maybe nomenclature for table is now correct - but for some reason the table is marked as read only? but it says procedure is read-only. Can I change that?

[Former Member · Accepted solution]

Line 5: select MATNR from "_SYS_BIC"."pk-users.pk-pricing/CA_IJL_BASE_MM" into "LUNDIJ"."Z_GPT_TEST";

[patrickbachmann · Accepted solution]

Ahhh, I was not sure if the INTO statement would work as I had not tried that. That could be the case.  Let's see what Ravi has to say.

[former_member184768 · Accepted solution]

Hi,

Following statement works fine for me:

insert into ravi.ZTEST_TAB_03 (B, C, A)

SELECT "B1", "B2", "A1" FROM "_SYS_BIC"."ravihana.testview/ZTEST_CALC_VIEW_03";

Here ZTEST_TAB_03 is a user defined table with columns A, B and C and these are filled from output of a Calc view ZTEST_CALC_VIEW_03 which has attribs B1, B2 and a key figure A1

Please use appropriate tables and columns from your user defined tables / calc views and try it.

Hopefully like this:

INSERT INTO "LUNDIJ"."Z_GPT_TEST" (MATNR)

select MATNR from "_SYS_BIC"."pk-users.pk-pricing/CA_IJL_BASE_MM";

Regards,

Ravi

[Former Member · Accepted solution]

Hi

From the error message it seems that you are using read only clause at the end of the procedure definition, you should remove that , read only procedures would not support DML opeartions. After that you can try as said by Ravi.Awaiting your feedback.

Thanks

Santosh Varada

[Former Member · Accepted solution]

Thnaks everyone, I appriecate all the suggestions. However, I still get the same error message. I now see down in the Properties box that Access Mode is set to Read only within the procedure and there is no option that allows me to change this. Perhaps I have to get IT to give me write security? Or is it really unchangebale?

[former_member184768 · Accepted solution]

Hi Ira,

Can you please post your complete procedure code. I am sure it can be corrected.

Regards,

Ravi

[Former Member · Accepted solution]

I was talking to my IT - pretty well seems to be security issue - and they aren't up to granting me the security. But just in case here is the entire procedure:

/********* Begin Procedure Script ************/

BEGIN

END

/********** End Procedure Script ***********/

INSERT INTO "LUNDIJ"."Z_GPT_TEST" (MATNR)

select MATNR from "_SYS_BIC"."pk-users.pk-pricing/CA_IJL_BASE_MM";

[former_member184768 · Accepted solution]

Well,

Your procedure code is empty as you wrote the INSERT statement outside the procedure body.

Can you please try the following

DROP procedure PR_INSERT_DATA;

CREATE PROCEDURE PR_INSERT_DATA(

  in v_matnr varchar(15) -- currently not used in procedure, for future use, if any

)

LANGUAGE SQLSCRIPT

AS

BEGIN

  INSERT INTO "LUNDIJ"."Z_GPT_TEST" (MATNR)

  select MATNR from "_SYS_BIC"."pk-users.pk-pricing/CA_IJL_BASE_MM";

END;

call PR_INSERT_DATA('');

Regards,

Ravi

[Former Member · Accepted solution]

Sorry, this web site is very sluggish for me - I think it is all the filters IT has. And copy & paste does not seem to work - so I end up retyping a lot of stuff. The code is actually inside the procedure correctly.

I executed the code you gave me in the SQL editor - here are the results - but not sure what it is supposed to do. I see a new procedure -> Table Types -> PR_INSERT_DATA - but not sure what this is.

Statement 'DROP procedure PR_INSERT_DATA' successfully executed in 27 ms 255 µs - Rows Affected: 0

Statement 'CREATE PROCEDURE PR_INSERT_DATA( in v_matnr varchar(15) -- currently not used in procedure, for ...' successfully executed in 95 ms 429 µs - Rows Affected: 0

[former_member184768 · Accepted solution]

.. Welcome to the world of HANA developers. I am sure if we keep in touch for few more days, you'll start advanced coding for yourself.

Well, I gave the code for creating the procedure and executing it.

The drop procedure statement drops any existing procedure with name PR_INSERT_DATA.

Second part creates the Procedure which takes MATNR value as input. Currently it doesn't do anything. The procedure code will insert the data to your table from the Calc view when you execute the procedure with the CALL statement.

You did not mention if that statement was executed successfully. Can you please try it. But before you execute that, can you please execute the following statement.

select count("MATNR") from "LUNDIJ"."Z_GPT_TEST";

Please run this after the call statement also to check if the data is actually inserted to the table.

Regards,

Ravi

[Former Member · Accepted solution]

Interesting!. I did not think to look at the Z table. There are over 5 million records there (since I ran the query a couple of times).

This is quite helpful. I think I can do something with this. I will play some more.

Thanks so much for your help!

[former_member184768 · Accepted solution]

Glad to know that it is working for you. Can you please close this thread, it has grown quite long enough.

Regards,

Ravi

[Former Member · Accepted solution]

Not sure how to close the thread. I clicked on Correct Answer on last note from you. Hopefully that closed it?

[Former Member]

Is there any way to submit a request to SAP to have them make Views accessible via ODBC?

[Former Member]

Hi Ira,

I can only imagine this is because HANA as of now haven't officially certified MS-Access to be used as a client and hence would not provide access to column views via the ODBC interface. Have a look at note 1577128.

I had a similar issue while connecting Cognos to HANA as part of a proof of concept exercise wherein only tables could be selected but no column views. As a work around we ended up creating database views under the schema (not column views) and that worked. However, as expected performance was poor as they wouldn't use the super fast olap, join and calculation engines of HANA.

Thanks,

Anooj

[Former Member]

Thanks a lot for responding, Anooj. I was afraid that the answer might be disappointing. I guess I will continue to work with the tables directly - which also does not allow use of the fast speed that the views provide.

Thanks, Ira

[henrique_pinto]

HANA does have ODBC support, just check C:\Program Files\SAP\hdbclient (if you have x64 clients installed).

For instance, BO Analysis Office for MS Excel uses it, and you can browse content views from it.

I suppose it does require some special navigation capability to see the Analytic/Calc Views as Data Sources that Cognos hasn't done yet.

But if it works for MS Excel/Word/Powerpoint, I'd expect it to work for MS Access as well. Strange.

ODBO is used by native MS Office 2010 Data Sources instead.

SAP Note 1577128 has ODBO twice as a supported client, I suppose it's a typo and one of them was supposed to be ODBC.

[Former Member]

Thanks Henrique,

I am able to connect to HANA via ODBC just fine. The issue is that I want to connect to HANA-VIEWs and I am only able to connect to HANA-TABLEs.

[henrique_pinto]

That's what I'm saying - in BO Analysis for MS Excel, using ODBC, you can see the analytic & calc views.

[Former Member]

Sorry, I misunderstood. Yes, I have been able to do that with Excel. I just can't see the views with Access ODBC.

[henrique_pinto]

I tested with MS Access 2010 and you're right, it only brings the tables.

I suppose it's related to the query MS Access does on the DB, it just asks for Tables (and potentially regular non-column views).

[henrique_pinto]

BTW, I reached the developers responsible for the note, and they've corrected it.

It now says:

SAP HANA additionally provides open interfaces#
* HANA provides the following standard interfaces for connectivity:
    -  ODBC
    -  JDBC
    -  ODBO
    -  SQLDBC

Source: https://service.sap.com/sap/support/notes/1577128

[Former Member]

Thanks for trying to help. It is appreciated.

[henrique_pinto]

One thing you could do is:

1) connect an Excel sheet to HANA view through BO Analysis for MS Excel (or ODBO);

2) create an Excel Data Source on MS Access and import (or link) it to an Access table.

It's not ideal, but it worked for me.

Make sure to select "Repeat Members" in BO Analysis for MS Excel, under the Analysis view, Components tab, and selecting your Crosstab, or else you'll get empty records in Access.

[Former Member]

Interesting idea. But the main reason I want to go to Access (rather than Excel) is due to volume of records/columns - bogs down and exceeds Excel limitations (even after changing Regsitry) so I was trying to bypass Excel.

Thanks

[henrique_pinto]

But what kind of analysis you want to do on Access that you could not get done with BO on top of HANA directly?

[Former Member]

I am a Pricing Analyst and we must maintain price records. Just for one simple example: we have about 150K skus with 25 price lists globally and even when I download a single price list with abt 25-30 columns Excel BO can't handle it. During a price increase we need to review all skus (not just select few or summary) but we may group certain products based on certain columns and apply various % increases - or we might even review afterwards and adjust indidviual skus. So we do lots of analytics similar to this on large volumes of data. Access is the perfect place to work this kind of stuff. Connecting to HANA Tables is so much better than our current manual method - but HANA-Views would really be out-of this world!

[henrique_pinto]

I've found some messages regarding this limitaton on the number of rows on AAO:

http://scn.sap.com/thread/1870824

http://scn.sap.com/thread/1970267

Apparently it's manageable.

[Former Member]

Well, I had already tried the Regedit method without much success. Although I can get over 500K cells, it still crashes with the amount of data I am after - as well as much slower than my Access Queries against HANA Tables. I will be fine with the Tables - just kept hoping that I would be able to use Views via ODBC.