making a drill down on an analytic view using MDX

[Former Member]

Hello, say for example I have the following analytic view

and would like to get the amount_sold per city per month_name, how to do this in MDX.

If you know how to do this on SQL/SQLScript, please feel free to share.

Thanks & Regards

Mohamed Ali

[rindia]

Hi,

First create the Attribute view on table EFASHION_KDD_FIRST_ and within it create hierarchy with YR at the top and below with month_name (if you want only 2 levels of drill down).

Create another Attribute view on table Location and create hierarchy with city and state name.

Now create an Analytic view and include only table ITEM and fact table with in data foundation and include attribute views in Logical join.

Note: You cannot add tables EFASHION_KDD_FIRST, Location in Analytic view as they are already used in attribute view.

After the view is activated, you can drill down using Excel MDX connector.

Click on YR to drill down to month_name and on state to drill down to city name.

Regards

Raj

[Former Member]

hi, thanks for answering but i am not using Excel. I am actually trying to get the data from the analytical view using MDX and then send it to the client side in json format using HANA XS engine to be, then presented using SAP UI5.

I am looking for something like this

(see this wiki example http://en.wikipedia.org/wiki/MultiDimensional_eXpressions)

SELECT

   { [Measures].[Store Sales] } ON COLUMNS,

   { [Date].[2002], [Date].[2003] } ON ROWS

FROM Sales

WHERE ( [Store].[USA].[CA] )

that i can use in my application.

so, for my case it can be something like

MDX

SELECT

   { [Measures].[AMOUNT_SOLD] } ON COLUMNS,

   { [CITY].[MONTH_NAME] } ON ROWS

FROM ANALYTIC_VIEW

in SQL this can be done, as far as new, this way:

so if i want to get the AMOUNT_SOLD per CITY per MONTH_NAME

i do the following

select CITY, MONTH_NAME, sum(AMOUNT_SOLD)  from _SYS_BIC."efashion/EFASHION_STAR_SCHEMA" GROUP BY CITY, MONTH_NAME;

But I am not able to do the same thing in MDX. I hope you see what I mean.

Thanks & regards

Mohamed Ali

[Former Member]

Hi Mohamed,

Why don't you just use an xsjs script to achieve this? You can have a the user choose the grouping via selections on your user interface screen. Take those selections and pass them to the server side xsjs script. Build an sql query based on user selections in the xsjs script and then return query results as a json back to the user interface. Then you can use the SAPUI5 methods to parse the json and display it as how you would like it formatted.

There is one other option which you may want to try is build a calcuation view that accepts input parameters. Build an odata service on top of the calculation view that can pass user parameters to the calculation view. You can also define the odata service to return a json result, which you can read easily using SAPUI5.

Kind Regards,

Sharan Chama

[Former Member]

Hi Sharan,

I  have already done something similar to that using HANA XS, SQL and SAP UI5 , but i would like to know how to  do that using MDX. I want to check what is the best solution in term of performance.

Thanks & Regards,

Mohamed Ali

[Former Member]

Hi Mohamed,

Ok I misread your earlier post, and your actual requirement is how to execute MDX statements in HANA SQL editor. Please see below -

-----SAMPLE MDX Statement

MDX SELECT

FROM [AN_PUR_DOC]

WHERE [MEASURES].[NETWR]

The above query yields the following result

Now copy the GUID value and go back to the editor of the same session and run the following query by replacing the above query with the below

MDX GET_CELLDATA <GUID>

This will result in the following -

I did not really create an advanced query, but this should give you an idea of how it works. As you can see from my prior query you do not need to specify the schema and name of the column view, just specifying the name of the analytic/calculation view is sufficient.

Also if you want to expose MDX data as a service you may want to look into the XMLA service. This service enables MDX queries/results to be passed between the application and HANA views.

Thanks,

Sharan

[Former Member]

Hi, thanks Sharan for your help and yes that's what I am looking for, now if I execute this MDX query:

MDX

SELECT

FROM [EFASHION_STAR_SCHEMA]

WHERE [MEASURES].[AMOUNT_SOLD];

where EFASHION_STAR_SCHEMA is a cube name (analytic view name)

and AMOUNT_SOLD is a measure name i get a SQL error that says : Cube EFASHION_STAR_SCHEMA could not be found. Do you know what is wrong?

Thanks

Mohamed Ali

[Former Member]

Hi Mohammed,

Keep MDX and SELECT on the same line.

I separated them in my above query onto different lines and it seems to throw an SQL error. I am not really sure why since the entire statement is a single query and also the developer guide shows with the lines separated.

For your error it seems to state the view could not be found. Is the user executing the SQL able to do a data preview on the Analytic view? Can you check if you have defined key attributes in the Analytic view?

Thanks,

Sharan

[Former Member]

Hi, Sharan

I did as you recommended, MDX and SELECT on the same line but the problem is not solved.

And yes the current user can do a data preview and sure I defined key attributes in the current schema like this:

Week_Id is the key attribute for the Efashion_kdd_first_view dimension

shop_id is the key attribute for the location dimension

color_code is the key attribute for the item dimension

I doubt maybe, i need to do something like set current shema or else before execution the MDX query do you make any such configuration before execution the MDX query?

Thanks & Regards

Mohamed Ali

[Former Member]

Hi Mohammed,

Can you check if you can access that Analytic View using Excel? If you are unable to access the analytic view using excel, then it probably has to do with the view missing from certain tables. Please execute the below statement if you are unable to access from excel -

update "_SYS_REPO"."SCHEMAVERSION" set db_schema_version = 16 where schema_type = 'bi'

After executing the above SQL statement, it is advised to reactivate all the views again. So reactivate your analytic view and then see if it is accessible from excel. If it is accessible after performing the above, then retry your MDX query.

However if it is already accessible via excel then I am not sure what else could be your issue. Also I have not done any configuration such as setting schema, in fact setting the schema just throws an SQL error for me.

You may want to open an OSS message or post to the HANA Dev Center as you might get further help from there.

Thanks,

Sharan

Message was edited by: Sharan Chama Actually there is a setting in your analytic view that needs to be set to true. The property is called "Multidimensional Reporting" and can be found in top right corner of the semantic view of your analytic view. If this setting is set to false, MDX reporting is turned off for the analytic view.