Using external variables in HANA Analytical views via MDX/HDODBC

[Former Member]

We are trying to pass "VAR_DATA_SERIES_ID" from Excel, Access or SQLServer in following query:
SELECT "DATA_SERIES_ID",

"PRODUCT_GROUP",
"DATE",

min("MIN_VALUE") AS "MIN_VALUE"
FROM
"_SYS_BIC"."SANDBOX/AN_TMP_PRAMOD_SEGMENT"
('PLACEHOLDER' = (
'$$VAR_DATA_SERIES_ID$$', 'S10_ALL'))
WHERE
("DATA_SERIES_ID" IN ('S10_ALL'))
GROUP BY "DATA_SERIES_ID",
"PRODUCT_GROUP"
,"DATE"
=> Is this possible?
I am following the same logic that I would in SQLServer proc. For Example:
<......Declarations ..>
....
..
WHERE DATA_SERIES_ID = @SEGMENT  ---(SEGMENT is the variable passed from excel cell 'Sheet1'!A3 or Column value in Access or SQL server.
<..................>

Is it possible that I say =:X and receive X from external source?

Any help will greatly be appreciated.
Thanks
Pramod

[mert_karakilic · Accepted solution]

To add a bit more background to Pramod's question...

We have an anlaytic view which expects an input variable (not mandatory). We are making a call to this view via ODBC from Excel and providing the input variable value S10_ALL in this case. However, the returned resultset shows that HANA completly ignores the parameter provided. If I open the view in HANA Studio and when I provide the value when prompted, it filters just fine.

We are trying to figure if this passing on parameter while calling a view is implemented or not and if it is implemented, how do we pass this parameter? We tried it with tables, anlaytic views, procedures (which is not a way to go), etc.

Thanks for your help.

[lbreddemann · Accepted solution]

Sounds as if the PARAMETER condition wasn't handed over to SAP HANA.

This is not something that ODBC does. But depending on how you do the ODBC access from MS Excel/MS Access the query might get parsed through the Jet DB engine before handed over to the ODBC driver.

Can you share some example VBA code (I assume) that demonstrates the problem?

[Former Member · Accepted solution]

Thanks Lars, it is great talking and seeking your helping hands again!

Attaching file with all steps and code. It is so simple in SQLServer - I wish I could replicate in HANA - life would be so easy 🙂

Thanks a million.

Pramod

ExternalVariable.txt.zip

[lbreddemann · Accepted solution]

Ok, this took a while... you know Q4... busy busy busy...

What works for me is this.

I created a user defined function that returns a table:

create function get_users (sel varchar(30)) RETURNS  table (user_id bigint, 
                                                            user_name nvarchar(256), 
                                                             total_user_cnt integer, 
                                                             sel_user_cnt integer)
language SQLSCRIPT AS
begin
declare total_user_cnt integer;
declare sel_user_cnt integer;

    select count(*) into total_user_cnt 
                    from users;
    select count(*) into sel_user_cnt 
                    from users
                    where user_name like '%'||:sel||'%';

    return select user_id, 
                  user_name, 
                  :total_user_cnt as total_user_cnt, 
                  :sel_user_cnt as sel_user_cnt
    from 
        users
    where 
        user_name like '%'||:sel||'%';

end;

Note, this is not necessary - you could also use the parameters syntax for calculation views.

Now, I added the data source (ODBC --- not the MDX driver!) to Excel (I simply ignored the pesky "data truncated" error that might come up).

Next, I defined a named field "FILTER" that I reference in the VBA code.

Sub Button1_Click()
Dim FILTER As String
FILTER = Sheets("Sheet1").Range("FILTER")

With ActiveWorkbook.Connections(1).ODBCConnection
                    .CommandText = "select * from get_users('" & FILTER & "')"
                    .Refresh
End With
End Sub

And that's really it.

[Former Member · Accepted solution]

Hi Lars, would you please let us know versions of HANAODBC and Excel you are using. Because my excel is refusing connection at 32bit and 64bit.

Are you using MSQuery?

Thanks

[lbreddemann · Accepted solution]

Yes, I am using the MSQuery from Excel with the 32-Bit driver.

Which error do you get?

[Former Member · Accepted solution]

Subscript out of range and connection refused error when using ODBC. Without Command button it works with MSQuery.

[lbreddemann · Accepted solution]

This is not a ODBC issue - the HANA server actually declined the connect attempt.

Make sure the connection information are correct.

See for how you can check the ODBC connection.

[Former Member · Accepted solution]

I know that. Same thing works if I use the same connection string in MSQuery directly. It happens when I invoke the query with commend button within Excel and not MSquery.

[lbreddemann · Accepted solution]

Hmm... in that case we need to dig deeper.

Can you share (via email) your XL sheet with me?

[Former Member · Accepted solution]

Boss, yourr e-mail box is full so attaching here.

Please see 3 excels attached:

a. ConsumeAnalyticsView: Using MDX to get analytical view. – Works (change extension to .xlsx)

b. SQLServerConnect: This is test excel. Works fine where stored procedure and table is in SQLServer. (change extension to .xlsm)

This is the scenario we want to replicate for HANA. In this excel I changed connection string properties and also modified the command VBA but it will just not work.

As I mentioned earlier MSQuery worked and now works for HANA SP9 – not for SP10. I tried calling the MSQuery from Command button but the same issue. It seems that Excel accepts MDX only and not ODBC while MSQuery has some MSAccess components that allow to use ODBC.

Thanks

Pramod

Note: I changed the excel extensions to .txt

SQLServerConnect.txt.zip

ConsumeAnalyticalView.txt.zip

[lbreddemann · Accepted solution]

Nope,  email works fine!

Anyhow, in your Excel I see that you're using the MDX driver - I don't know how to work with the ODBO driver in  VBA and that's not what you did with SQL Server.

For the ODBC connection the following steps just work for me:

1. enable the ODBC driver to work with OLE DB-ODBC-access:

Setting the ODBC parameter SHOW_CATALOGS to TRUE is important!

2. In Excel add the data source via connection wizard:

3. Click through until you get this:

4. Via the Properties button, you can now edit the query text:

Don't click on "Edit Query" - this just never worked for me.

But as the command text can now be edited, e.g. with the VBA code already posted, this approach works for me.

Hope that gets you "across the line".

Cheers,

Lars

[Former Member · Accepted solution]

OK!! Wow! SHOW_CATALOG:TRUE did it.

Thanks!! You are magnificent.

[Former Member · Accepted solution]

Hi Lars,

it's great to see OLEDB is still working in SP10. my last test was in 91, and now 110 is out, so it would be nice to confirm that it has not been depracated.

thank you for all the HANA support here.

rgds,

greg

[Former Member · Accepted solution]

The solution works for Excel 13 (Office 365) + HDODBC32 + HANASP09. It has not worked for SP10 for me so far. May be there is some setting in HANA that I do not know.

[lbreddemann · Accepted solution]

I doubt that any server settings would lead to problems here.

Generally, I am not aware of any deprecations of the ODBC driver capabilities.

[Former Member · Accepted solution]

ODBC 32 bit works with SP10. It was my mistake.
All is well.

THANKS. Great help.

[Former Member]

Thank you Thank you!!
Lars, you made my day. We will use this asap.

Thanks again.

Pramod