In this tutorial, I will explain how to connect SAP HANA data via MDX for multidimensional analysis in Microsoft Excel PivotTables.
1) Open MS excel. In the data tab, select (from other source) as shown in the picture.
2) Select (From Data Connection Wizard).
3) Select (Other/Advanced) in data connection wizard and SAP HANA MDX Provider in data link properties . Click Next to provide the connection details
4) In the connection tab, provide host name of HANA database and Instance number. Provide HANA Credential details and test the connection.
5) Select the Schema from the HANA database. Choose the Analytic views and click next.
6) Give the description and freindly name for the report and click finish.
7) Select the Pivot table report format as the output.
8) The output of the HANA Analytic view with Product level heirachy in MS Excel. Choose the required Measure and Dimesions.
You can drag and drop dimensions and measures from the PivotTable Field List onto the boxes below to have them show up as Column, or Row labels, filters or values. If you just click on the checkboxes, values will go to Values, non-time dimensions to Row Labels, and time dimensions to Column Labels by default. Your data will be inserted into the PivotTable directly from your SAP HANA server over a live connection