Transcription
Connecting to Alteryx via ODBC to SAP HANA DatabaseLandscape:1. SAPBPCHANA - BPC Client2. HANA Studio & SAP GUI3. Alteryx DesignerStep 1: Connect to ODBC Driver on computer that has Alteryx Designer.a. Login to the Alteryx Designerb. Search for “ODBC” on your desktopc. Choose “ODBC Data Sources (64-bit)”d.e. A window will appear. Go to “System DSN” tab and click “Add” to add a new ODBC connection toyour HANA databasePage 1 of 13
f. Choose “HDBODBC” as the driver to set up a data sourceg. Click “Finish”Note: SAP HANA Client has to be installed on the machine that has Alteryx Designer as theHDBODBC Driver is within the installation files of HANA Clienth. Key in a data source name and descriptioni. Key in your Server and Port of your SAP HANA Databasej.After inserting all necessary details, click “Connect”Page 2 of 13
k. Key in your username and password to access your SAP HANA Database. Press “OK”l. If connection is successful message “Connect successful!” will be displayed.m. Connection via ODBC complete. This connection will be used in Alteryx Designer to import datafrom SAP HANAPage 3 of 13
Additional Notes: If unsure of server address and port, you can logon to your HANA Studio and rightclick on yourSystem- Properties and Logon.1. Go to “Database User Logon”--- ”Hosts Used to Connect”2. This will be your server address and port number to connect to for ODBC connection above.Page 4 of 13
Step 2: Connecting Alteryx Designer to the SAP HANA ODBC Connectiona.b.c.d.Open Alteryx Designer and create new workflowDrag “Input Tool” to the canvasDouble click “Input Tool” to access the configuration settingClick on the drop down buttone. Go to - Other Databases - ODBCf. Choose ODBC Connection that is created in Step 1g. Key in your username and password for your SAP HANA DBPage 5 of 13
h. You will be able to connect to SAP HANA tables and queries successfully. Choose your tables, use visualquery builder or sql editor to access the database.i. Press “OK” to import specific tables or queries.j. Visual Query Builder can be used to build your query against HANA Viewsk. You may drag and drop specific views, select only selected fields and rename the column fields. [Youcan also join few views together with primary and foreign keys]Page 6 of 13
l.The output will be displayed after running the workflowPage 7 of 13
Step 3: Getting BW Cube Data into HANA via manual importa. To view BW Cube data in HANA, cube settings need to be set to be able to output to External HANAViewb. Log into your SAP GUI BW on HANAc. Go to transaction code RSA1 to view which cube that you want to import into HANA.d. Take note of the tech.name - /CPMB/SKITS7Ze. Go to transaction code RSDCUBE to change the settings of the cubef. Key in tech name of the cube - EditPage 8 of 13
g. Check “External HANA View reporting”h. Click “Save”i.After saving, click “Tick” if there are no errors.j.Activate the cubek. Now go to HANA studio, go to Quick View perspective.l. If there is no Quick View perspective in HANA studio, please go to Window - Show View- QuickViewm. Under Quick View - Click ImportPage 9 of 13
n. Click SAP HANA Content- SAP Netweaver BW Modelso. Then click “Next”p. Key in your server credentials [Username and password is SAP GUI credentials]q. Click “Test Connection” to test your connectionPage 10 of 13
r. Connection should be successful.s. Press “OK”t. Press “Next”u. Select your target systemv. Click “Next”Page 11 of 13
w. Select the BW objects that you want to addx. Browse for the package that you want to save your BW object undery. Click “Finish”Page 12 of 13
z. A job will run to import the BW objects into HANA.aa. Wait for it to be completed. Once done, you can access BW object data into your Alteryx Designer[Usually the views auto-generated are under “ SYS BIC” catalog in HANA studio]Page 13 of 13
Note: SAP HANA Client has to be installed on the machine that has Alteryx Designer as the HDBODBC Driver is within the installation files of HANA Client h. Key in a data source name and description i. Key in your Server and Port of your SAP HANA Database j. After inserting all necessary details, click “Connect”
