Okay, for anyone who’s been following this blog since the beginning, this next post was something I’d been looking to do even before I left SAP to join Microsoft. And with the recent announcement regarding SAP HANA being available in Azure, I figured the time had finally come where I’d bite the bullet and get this scenario setup and working.
To get started, you have to make sure you have the following items (along with some other downloads we will make sure you get during the walkthrough) -
1. A Microsoft Live ID and a Microsoft Azure account -
2. An account on the SAP Community Network and one for the SAP Cloud Appliance Library -
3. Microsoft Excel Pro Plus (or Microsoft Excel 2010 with the Power Pivot add-in installed) - http://office.microsoft.com/en-us/business/office-365-proplus-business-software-FX103213513.aspx
Setting Up HANA in Azure
Amazingly, SAP has an easy walkthrough for this available on SCN and I could actually find it with a Bing Search. For those of you who’ve ever dealt with SCN before, you know this has NOT always been the case. But this link here - http://scn.sap.com/docs/DOC-28294 was exactly what I needed.
Step 1 – Download and install SAP HANA Client and SAP HANA Studio from here. For the purpose of this walkthrough, I am using the 64bit download of the Client/Studio - https://hanadeveditionsapicl.hana.ondemand.com/hanadevedition/
This is where I encountered one of the first hiccups in the process – you have to install the JAVA JDK PRIOR to installing SAP HANA Studio. Otherwise the installation will throw an error message. Installing just the Java Runtime Environment is not enough, you have to install the entire JDK (at least I had to anyways). You can download this from the Oracle website here - http://www.oracle.com/technetwork/java/javase/downloads/index.html
Once those items are installed on your machine (I simply accepted the default installation location and options during the wizards), you can provision your HANA dev environment in Azure.
Step 2 – Provision your instance of HANA in Azure
Go to https://cal.sap.com/
Choose the instance you will want to provision and choose “Try Now” – for this exercise, I choose SAP HANA developer edition 1.0 Rev 80 because I’m just testing some stuff and won’t be using this for production purposes.
Accept the Terms and Conditions (that no one ever reads completely because it’s always absurdly long)
Now it’s time to add your Azure account details. Open a new tab in your web browser and go to the Azure Management Portal and select the “Settings” option
Copy the subscription ID to your clipboard (I’ve obviously obscured mine)
Now jump back to your SAP Cloud Portal and go to the Accounts Tab. From here, you’ll add your Azure account so the HANA VM can be provisioned -
Give your Account a “friendly” name (I am using the term loosely) and hit next
Choose “Microsoft Azure” from the dropdown and paste the Subscription ID into the second box and hit Next
You can just hit next to jump through the next three screens at this point – there are no users you need to add, and there isn’t any financial information available. Once you hit Finish, a pop-up window will appear prompting you to download a management certificiate so SAP has permission to create the VM in Azure for you.
Download the certificate somewhere on your hard drive that you can easily access, then go back to your Azure tab. Assuming you are still sitting on the “Subscriptions” screen under settings, you can click the “Management Certificates” and jump to that screen. Here is where you will upload the certificate
Choose “Upload”, browse to the file you downloaded from the SAP site, then hit the checkmark.
It should take between 30 – 60 seconds to upload and confirm the certificate has been added.
Now jump back to your SAP Cloud Tab – you should see the following in your Accounts tab if the process completed successfully. The Cloud Provider ID should match your Subscription Name – if it does, you’re all set.
Now you can pick the solution you want to transfer to the Azure account you just setup. This next part is a little confusing. All this step does is allow you provision a VM, it doesn’t actually provision it for you.
So just select “Activate” for the HANA solution and it’ll change to “Create Instance”. Click on it to walk through the provisioning wizard
Give your instance a name again (this will be the name of the VM that is spun up in Azure)
You can’t choose any other region than West Europe, and your Azure Account will be pre-populated in the Account box, so just hit Next. The next screen gives you the VM size option (there’s only one currently) and pre-defines a number of endpoints. Just leave it as is unless you are comfortable re-defining these for your particular dev environment.
Now you get to set a password for the instance. This was a little frustrating because no password I ever use meets these very restrictive requirements, so I had to make up a brand new one which I am sure to forget at some point. Do this and hit Next.
Not exactly “simple” password rules (Zing!)
You can setup when you’d like the solution to suspend/terminate so it isn’t running all the time. The greedy side of me will tell you to change it to “Manually Activate and Suspend” so Microsoft makes more money. The benevolent side of me will say setup a schedule so you aren’t paying when you aren’t using it.
Hit Next Twice (step 5 is not applicable right now) and you’ll see the Summary of your options. Assuming everything looks good, hit “Finish” and your VM will be spun up in your Azure account.
It’ll take about 5 – 10 minutes or so to spin up (at least that’s how long it did for me). You can see when it is finished on either the SAP or the Microsoft Azure portal.
On the SAP Cloud Portal, the status is shown under the instances tab. If the Status icon is green, your VM is up and running successfully -
On the Microsoft Azure portal, choose the VM option from the left menu and if it is running successfully, you’ll see the status as “Running” for the VM
Once it’s running, you can check if it is actually working by going to the website it spins up on the VM automatically by putting in the IP address it was assigned in Azure as the web address. You can find the IP address in the Azure portal by clicking on the arrow next to the VM name
and choosing “Dashboard”
The IP address is then listed down the right hand side of the screen towards the bottom
Copy that address to your clipboard, open IE, and then paste it into the address bar. You’ll be taken to the following screen if it is running properly -
Congratulations – you have successfully setup a HANA instance running in Microsoft Azure.
Step 3 – Connect a data table in your HANA instance to your Power Pivot model in Excel
Assuming you installed the HANA client tools I mentioned earlier on the PC where you’ll be doing this, this should be pretty straightforward. You don’t even have to use HANA Studio if you don’t want to, but it’s probably a good idea to connect at least once so you can browse what’s available in terms of demo datasets/cubes. I am guessing you’ll want to upload your own data at some point as well, and you’ll need HANA Studio for that exercise when you are ready for that. So open HANA Studio, and select “New System” from the dropdown under the Systems tab on the left-hand side -
Next, add the Host/Server and User information for the HANA instance to make the connection. This information is all available for you on the web page on your HANA VM you browsed to earlier.
Hit Next then Finish (don’t change anything on this screen below)
If it connects successfully, you’ll see the following items now populated in your System tab -
As you can see, there are a number of catalogs out of the box that have demo data in them for sample applications that are provisioned as part of the instance. We’re going to use the catalog “SAP_HANA_EPM_NEXT” and return the Suppliers in our Power Pivot Model. Here’s where it gets a little tricky.
In the following blog post about connecting HANA and Power Pivot - http://www.bibabos.com/sap/connect-power-pivot-native-odbc-sap-hana/, the author correctly points out that you can’t use the ODBC connection in Power Pivot currently to make the connection. You have to do a workaround and leverage the ODBC connection in the data tab to create it, and then leverage that connection in Power Pivot. He creates the connection string in a little text file – I am going to do it a little differently, but either method gets you to the same place.
Go to the Control Panel for Windows and choose Administrative Tools.
Now choose the ODBC Data Sources (32-bit or 64-bit) – this choice depends on which version of the HANA Client Software you installed earlier (and as I mentioned, I am using the 64-bit version).
Select the System Data Source tab and choose Add
Choose the HDODBC driver. If you don’t see this driver as an option, you probably didn’t install the HANA Client software.
Hit Next then Finish. Enter the HANA Instance information in the new window that appears. The port is 30015 you need to use
Hit Connect, and enter the Username/Password for the connection
Click OK. If your information is correct, a message will appear saying the connection was successful.
Click OK twice and the connection information will be saved as an ODBC connection you can leverage again and again.
Open Microsoft Excel and create a new workbook. Once it opens, select the Data Tab and choose “From Other Data Sources”. You’ll need to choose “Microsoft Query” as the data source. Select the ODBC connection you created earlier and hit OK.
Re-Enter your username and password and hit OK
Assuming the information was entered correctly, you’ll get back a list of the entities available to choose from. This is NOT easy to browse through, but eventually you’ll find the following entity –
Once selected, click the arrow to bring all the fields over and just click through Next until you get to the final screen where you select “Return Data to Microsoft Excel” and then hit Finish.
After a few moments, you’ll be prompted for what you want to do with this data.
Select Properties, Go to the Definition Tab, then check the “Save Password” box
Now Export the connection file and save it locally -
Now open the Power Pivot window to leverage the connection file you just created. Select Existing Connections, then select Browse for More to grab the file you saved. Load it and hit Open -
You’ll see the connection string information loaded. Hit Next, and you’ll see the Query you built when creating the Excel connection.
Hit Finish, and the data will load into your Power Pivot model.
Now you can work with it like any other data source in Power Pivot!
Thanks for reading, and I’ll see if I can hook this up to a LightSwitch app at some point as well.