cfinlan

SQL Server Mobile Reports – Free Maps of the Week, Part 2

Hi everyone – as promised last week, I have a new set of free maps I’ve pulled together to share with all of you as we get even closer to the GA of SQL Server 2016.

Use the links below to download the zipped map files. You’ll need to unzip them and follow the directions here to use them in your mobile reports.

World Countries – Download
Europe – Download
Bosnia-Herzegovina – Download
Bulgaria – Download
Spain – Download

Remember, these maps aren’t officially supported by Microsoft in any way. But if you find these helpful, be sure to check back next week for even more free maps. Have a great rest of your holiday weekend (well, if you’re in the US, anyways)!

SQL Server Mobile Reports – Free Maps of the Week

Hi all – hopefully, you’re as excited as I am about the upcoming GA of SQL Server 2016 on June 1 and with it, the brand new version of Reporting Services. We’re finishing up the official release of the Mobile Report Publisher as well, and with that in mind, I wanted to introduce a new blog series I’m doing here. I’ve taken the time to put together some custom map files you can use for your mobile reports. I’ll post the files here each week or so. These will be free for you to download/share, use or repurpose however you’d like. I’ll post more as I get more created – these maps aren’t officially supported by Microsoft in anyway, so keep that in mind as you download and use them. I just thought you might find them valuable, and frankly, I needed to do a new blog post. It’s been awhile.

Use the links below to download the zipped map files. You’ll need to unzip them and follow the directions here to use them in your mobile reports.

Africa – Download
Argentina – Download
Asia – Download
China – Download

Thanks for reading, and enjoy the free maps. Be sure to keep following this blog to grab more maps in the future!

How to use custom thumbnails with Mobile Reports in SQL Server 2016

Happy Sunday folks!

In the new Reporting Services web portal, you see thumbnails for each of the mobile reports you’ve published to the server. Well, here’s a neat hack that allows you to update your mobile reports with custom thumbnails. Keep in mind, the normal disclaimer applies around the fact it isn’t officially supported and if you break something, you’re on your own, etc.

1. Download the latest version of the Mobile Report Publisher
2. Create your mobile report

3. Save it locally vs. publishing it the server. It doesn’t matter if you use either Excel data or shared datasets from your server.

4. Find the file location where you saved it locally.

Now change the file extension from .rsmobile to .zip

5. Double-click on the .zip file to view the contents

You’ll see two image files called thumbnail.png and thumbnail-phone.png that look like the following and were generated when I first created this report. These are the files you’ll want to replace, and you can replace one or both of them.

6. Now, pick new images to use. They need to be named the same as the ones with the file, and need to be in the .png format. Ideally, they’ll have the same dimensions as the default files, which are –

thumbnail.png file – 1150×555
thumbnail-phone.png file – 550×825

That isn’t a requirement for this to work, but it does mean your pictures might look a little distorted if they don’t match.

I chose one of my son’s favorite stuffed animal to use as the new thumbnail.png file.

Once I saved the file and renamed it, I simply dragged it into the still zipped folder and answered yes when prompted to overwrite the existing file. You see it has replaced the existing file, and the file size has increased significantly. Something else to be aware of if you do this for multiple files.

Now, I can rename the file extension back to .rsmobile and then upload the file to my Reporting Services server.

Once uploaded, it will automatically recognize it is a mobile report based on the file extension, and put it in the right category in the portal.

I’ll also now see the new thumbnail I added to the file in place of the one generated by the Mobile Report Publisher.

Here’s how it looks in My Favorites

And when I click the report tile, the report still renders as I’d expect in the browser:

One thing to keep in mind – if I were to edit this report and re-publish or upload it, new thumbnails will get generated and overwrite the custom one(s) I added to the file. So I’d have to do this each time I make changes to the report and re-save it. That’s why this is a hack though, right? 🙂

Hopefully you enjoy this and have some fun playing around with this concept. Have a great week!

What’s the deal with Excel and Power BI Desktop Files support in Reporting Services?

Hi all!

So one feature that was introduced in SQL Server 2016 RC1 for Reporting Services was the ability to store Power BI Desktop and Excel Workbooks files on the server. This feature generated a lot of buzz on social media, with people looking to understand exactly what this means vis a vis the roadmap announced in October of last year. With that in mind, I wanted to do a post that answered that question along with some others. Let’s do that now using the tried and true fake interview format!

Wow, this is so cool. I can now store Excel Workbooks and Power BI Desktop files in Reporting Services!

Yes you can! Though to be fair, this feature has been around for quite some time. We did add a few things, like calling these items out as separate report types, allowing you to add them as favorites and giving them a special icon.

That’s it?

Well, because they are resource files, you can could access them using the SOAP API. That might be valuable for folks to take advantage of.

Is this the integration you announced at PASS last year? Cause if this is all we’re getting, that’s pretty lame.

Nope, this is just a first step. The team wanted to have something for folks when SQL Server 2016 launched, but it doesn’t change our plans at all for adding full support for Power BI desktop files in Reporting Services.

Great – when is that Power BI desktop support coming? I want it yesterday!

We know, and we are as excited to bring that support to the product as you are to get your hands on it. We’re heads down on SQL Server 2016 currently, and it’s top feature to get added post RTM.

So you’re not going to tell us a specific date?

I don’t have one to give.

Fine – can you at least tell us what features will it include? Can I use custom visuals? Will Q&A work? Can I build dashboards like we can in the service?

Right now, the only thing we’re certain of is we’ll provide support to view and interact with the reports in a browser in Reporting Services. We’re not in the business of providing the bare minimum, however.

That’s pretty rich coming from the team that didn’t do anything with the product since –

Very funny. Were there any other questions you had?

Wait a minute – you didn’t mention you’d be adding full support for Excel Workbooks in Reporting Services on your roadmap, but you added special support for those as well. Does that mean – ?

Yes – well, probably. There’s broad support to do that, and it’s a popular idea with our customers. When those things are aligned, it usually means it will happen.

Great – when is it happening?

I dunno.

What??! You just said –

I said probably – there’s still some stuff that needs to be finalized, and I certainly wouldn’t formally announce something like that on a personal blog post on April Fools Day. If and when it’s official, you’ll be the first to know.

Are you saying this entire blog post is an April Fools Day gag?

No, absolutely not.

And there you have it. Hopefully this gives you some additional context around the feature and what we’re up to as we prepare for the official launch of Reporting Services in 2016. As always, have a great weekend!

Row level security options for Mobile Reports in SQL Server 2016

Welcome back!

With the latest release candidate of SQL Server 2016 now available for download, let’s take some time to review some options you have to implement row-level security for Mobile Reports. These options can also be used for paginated reports in Reporting Services, so you don’t have to do the same work twice.

If you’re using SQL Server 2016 (or Azure SQL database) as your data source, you can take advantage of the new, built-in row-level security functionality. My colleague, Patrick LeBlanc, does an excellent job walking you through how to get this setup in SQL Server 2016 in a blogpost here – http://patrickdleblanc.com/wordpress/?p=90, so I’m not going to redo all the steps he lays out nicely on his blog. Since the old report manager has been replaced by the sleek new report portal in RC1, I’ve added a new screenshot below to replace the one Patrick included –

If you’re using a version of SQL Server prior to 2016 as your datasource, you can achieve something similar by creating a view that filters out records based on the user name accessing it. Assuming I used the same dataset I used in the first example, my view would look like this –

SELECT Student, SchoolRep, Class, Grade

FROM dbo.Students

WHERE (SchoolRep = CURRENT_USER)

I also have the option to use row-level security from an Analysis Services data source. Here’s an example of how you could do this using a tabular AS model –

1. Setup the execution account in the Reporting Services Configuration Manager to run as an account of your choosing –

2. Grant this account admin permissions on your Analysis Services instance

3. For the data source you’ll be using for your mobile report, setup roles for your users to be mapped to.

Add users or groups to these roles –

And setup the filters for the roles accordingly. These are the records anyone assigned to that role will be limited to seeing.

In Reporting Services, when you setup the shared data source for Analysis Services, it’d be setup like this –

Note – Unless you have Kerberos setup in your organization, the Analysis Services and Reporting Services instance will need to be on the same server for this setup to work.

Regardless of which of these options I choose, I’ll always need to do the following steps to create and publish my mobile report –

1. Create my shared dataset in Report Builder or SQL Server Data Tools –

2. Save it to my RS server –

3. Create my mobile report using the SQL Server Mobile Report Publisher –

4. Hook up my shared dataset to the visual elements –

5. Preview It in the Publisher –

6. If everything looks good, I publish it to my server so people can view it –

And there you go! This is one of a series of articles I’ll be doing both on my personal blog and the Reporting Services team blog over the next few weeks to show you how to get the most out of investment in Reporting Services in SQL Server 2016.

Thanks for reading!

So refreshing–How data refresh works with Mobile Reports and KPI’s in Reporting Services

With the release of SQL Server 2016 CTP 3.3 and the latest version of the Mobile Report Publisher Preview both now available, I thought it would be a good time to review how data updates work for Mobile Reports and KPI’s in Reporting Services.

As I reviewed in a previous post, you need to create a shared dataset to use in your mobile report or KPI that you save on the server. This is similar to what you would do in Datazen, where you’d create a query against a data source on the server and then use it for either a dashboard or a KPI. However, the way data is updated for KPI’s using a shared dataset works a little differently than it does with Mobile Reports.

Let’s review the Mobile Reports scenario first – In Reporting Services, by default, the shared dataset will always be up-to-date with the latest data.

They’re live!

So if I create a query against a SQL Server table for my dataset, every time I run a mobile report, that query will execute and bring back the latest data. This might not be ideal for performance reasons if hundreds/thousands of users are potentially using that report frequently. So Reporting Services has an option to cache the dataset on a periodic basis – that way, the data is saved periodically on the Report Server and isn’t always going back against the live data source.

You need to setup caching in the old Report Manager in CTP 3.3

It’ll make sure everyone who runs the report is getting consistent results and response times are potentially faster. If you do this for your mobile reports, you won’t see any updates to your data until the cache expires and the query then executes against the underlying dataset again.

With KPI’s, this doesn’t work the same way – for your KPI’s to be updated with the latest data from your shared dataset, you must have caching turned on AND also have a cache refresh schedule enabled to preload the cache with fresh data.

Why does it work differently than Mobile Reports? Well, imagine if you have forty KPI’s on the home page of your portal. When you first load the site, if it worked the way it did for mobile reports, it would execute at least forty queries all at once to load the data for each KPI. And since you can setup multiple datasets for different elements of a KPI, you’d potentially be running over 100 separate queries just by going to the home page. Multiply that by several users doing it at the same time, well, you get the idea. So make sure you setup a cache refresh plan

Hopefully this helps you understand what your options are around refreshing your data for your mobile reports and KPI’s in SQL Server 2016. Thanks for reading!

How to create surveys in OneDrive and view the results in Power BI

So, this blog post is the result of the pestering request of one Jen Underwood, who I mentioned this to previously as something I’d done for some internal work at Microsoft. Apparently, a number of people aren’t aware there is an easy way to create surveys using OneDrive and Excel Online. It’s a great way to quickly create an anonymous survey that you can share a public link to. And because the results are immediately saved in an Excel document in OneDrive, you can use PowerBI to view those results as they come in! Here’s how you do it –

1. Sign up for OneDrive (duh).

2. Once you’ve signed up/in, go to the new menu. Here you’ll see the option to create a number of new documents, including an Excel survey. Choose that.

3. A new browser window will open and you can create your survey by giving it a title, description, and begin entering your questions by clicking on the little gear that appears as click in the area to enter.

4. The questions can be multiple choice, true/false, text responses, etc. You can also make them required or mark if they have a default answer.

Once you’ve finished, your question will appear in the list you’ve added to your survey in the order you create each one.

5. Continue adding questions until you are finished. You can move the order they appear in around at any time by simply hovering over a question, clicking it, and then dragging it to the place you wish it listed.

6. Once you’ve finished, if you hit “Save and View”, you can preview what your survey will look like for those using it.

If everything looks good, you can hit the “Share Survey” button, and a link will be generated that you can share so users can fill out the survey.

There, my survey is done. I invite you to fill it out here – http://1drv.ms/1SXqFt5

Now that my survey is finished, I’ll want to report on the results using Power BI. I can do that right from the Power BI site.

1. Go to PowerBI.com and login to your Power BI site (or sign up if you haven’t already).

2. Once you’ve done that, go to Get Data, and then choose Files

3. Choose OneDrive-Personal and select the survey you just created

And that’s it – you can now create your report to show the results as they come in from the survey. Simply design it, name it and save it.

And now with the new Power BI public embedding, everyone can see the results that come into my report. So fill out the survey here – http://1drv.ms/1SXqFt5

And view the results here – Survey Results

Thanks for reading, and I can finally tell Jen to quit bugging me to write this post. Smile

Solved: (500) Internal Server Error in Mobile Report Publisher

This is a quick blog post for a Friday, but one that might help some of you who’ve run into this issue.

Certain users have reported getting an error when connecting from the new Mobile Reports Publisher from a shared dataset they published in Reporting Services.

After doing some investigation, we were able to determine the issue comes up only when the data set had been published using SQL Server Data Tools. The issue does NOT appear when you are using a shared dataset you created and published in Report Builder. We’ve already fixed the issue for future releases, but for now, there are two ways to workaround this –

1. Use Report Builder to create the shared dataset vs. SSDT

2. Edit the shared dataset .rsd file using an XML/text editor. For the entry for the dataset in the file, change it from this –

<DataSet Name=””>

To something that doesn’t leave it blank. It could be this, for example –

<DataSet Name=”DataSet1″>

Then re-save the file to the server, and it will work without issue. Entering a value there won’t affect anything else around the dataset, including the name you assigned it when you save it. It just prevents the error from being thrown by the Mobile Reports Publisher around having a blank entry for the dataset name.

Hope this is helpful to anyone having this issue, and thanks for reading!

How to create Mobile Reports and KPI’s in SQL Server Reporting Services 2016 – An end-to-end walkthrough

It’s finally here – Microsoft SQL Server 2016 CTP 3.2, and with it, the introduction of Mobile Reports and KPI’s into SQL Server Reporting Services. These new capabilities are based on Datazen technology, and are the first step in bringing together the different report types in Reporting Services that was outlined during PASS in October. It’s also the first time I’ll be posting as a member of the Reporting Services team! Yes, I’ve moved over from the Datazen team to work with Riccardo Muti and drive the all-new Reporting Services experience we’re shipping in 2016.

And since the Mobile Report Publisher App just went live, I thought it’d be helpful to do a post where I walk you through the entire process – from how to setup a test/dev server with Reporting Services in Azure, to creating a report and KPI, and finally consuming those KPI’s and Reports in the Power BI app for iOS. So without further ado, let’s get to it!

Setup your server in Azure (this section assumes you already have an Azure account you can use.) –

1. The easiest way to get started is to select a VM which has the latest CTP already installed on it. You’ll be taken to the create screen –

I’ll use the “Classic” deploy model for this scenario, since it’s a single VM and I’m only using it for a short period of time. After I hit create, I’ll need to enter my machine details. I’ll leave the defaults for all items (since this is just for testing purposes), save for one –

I do want to open up the endpoints on my machine so I can connect to them with my app. So I want to click Optional Configuration –> Endpoints, and I’ll both the endpoints for http and https (I’ll only use http for this walkthrough, but using https instead of http is recommended for security purposes in production scenarios).

Once that’s done, I’ll hit create. My machine should be ready in about 10 minutes or so.

Once it’s done being created, I’ll connect via Remote Desktop by hitting the ‘Connect’ button on my VM dashboard and logging in using the username and password I created in the previous step.

Now that I’m connected, I need to do a couple things on the server to enable Reporting Services and do proper testing.

First, let me turn off IE Enhanced Security Configuration in the Server Manager so I can more easily browse around if necessary in Internet Explorer. This is available under Server Manager –> Local Server

I can click on “On” and turn off the enhanced security for now. I’d normally re-enable it when I’m finished my session, but I’m deleting the VM when I am finished in this scenario.

NOTE: If for some reason the Azure image is not version 3.2, you can download the latest SQL Server bits after the server has been setup and upgrade SQL Server before walking through this section.

With that complete, I can now setup Reporting Services by running the Reporting Services Configuration Manager. It looks like this in your App Menu on the server.

When I start it, I’ll connect to my default Report Server instance on the box and walk through the configuration steps.

I’ll leave the built-in virtual service account as is and hit next.

For the Web Service URL, I can see that isn’t yet configured. I’ll leave the default information. With that done, I hit ‘Apply’ to apply my selections so I reserve the port and URL on my VM.

Under Database, I’m going to connect to the local database on the server using my integrated credentials. You can leave the defaults for each option here and hit “Next” until the system completes the database creation process. When I’m done, it should look like the following –

The Report Manager URL needs to be configures as well, I’m also using the default settings here. I’ll hit Apply to save those and reserve the port on the VM for that address. I’ll get a success dialogue if that is done properly or not.

At this point I can hit Exit and start working with my new Reporting Services portal. Please note – my setup was very basic, and really only makes sense when you are doing this for test/dev purposes.

Since I’d like to use the Power BI iOS mobile app to view my reports and KPI’s, I need to enable Basic Authentication on my server. The article I linked to has more details, but the simplest way to set this up is –

1. Open RSReportServer.config in a text editor.

The file is located at <drive>:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER900\Reporting Services\ReportServer.

2. Add the RSWindowsBasic as an option under Authentication Types. It’ll look like something like this –

You can leave the other types listed there (NTLM, Negotiate, etc) and just add the Basic option in the format I show above. Save the file and restart the report server. When you’ve restarted, you can browse to the new report portal under the following location if you’re remotely logged into the server –

http://localhost/reports_preview

You’ll be taken the new home screen, which should look like this.

Now that we’ve setup our test server, let’s start building! To do so, we need to create a shared data source, where we’ll get our data from.

Create New Data Source

From Portal, select New -> Data Source

You’ll be prompted to go to the old Report Manager screen to enter data connection details.

Click the link in the pop-up window to switch screens and enter your data source information. Please note that currently, you can’t use an Analysis Services data source for your Mobile Reports and KPI’s. I’ll use a SQL Server data source for this example.

Hit Okay to save the data source. By default, in the new portal, the data source is unchecked from the list of items you see. Enable it by clicking the Display menu and selecting Data Source

You’ll now see the data source available in the new portal.

You’ll need to use an existing Reporting Services client tool to create the shared dataset. This walkthrough assumes you are using the Report Builder client app, available for download here.

Create New Dataset

Open Report Builder and choose the New Dataset option from the splash screen

Select the data source that you just created in the previous step. You’ll need to connect to the report server the data source is setup on before proceeding, otherwise it won’t appear as an option for you. You do that by hitting “Browse other data sources”

Typing the name of the server in that hosts your data source in the Name field in the following format –

After you hit Open, you’ll then be able to navigate to the data source you setup in the previous step.

Select it and hit Open. Then, create your dataset using Report Builder.

When complete, save the data source to the SSRS server.

You’ll then be able to use the data set for your KPI and/or Mobile Report. You can create multiple data sets against the same data source, and they can be re-used by different KPI’s and Mobile Reports without rebuilding it each time.

Now you can create KPI’s and Mobile Reports against these shared datasets.

Create New KPI

KPI’s can be created right from the server. Select “New KPI” from the dropdown menu –

You’ll be taken to the KPI creation screen. You have the option to manually enter values, or to use a shared dataset. Let’s change the “Value” entry from the default to one from my dataset. I click on the dropdown to change the Value from “Set manually” to “Dataset field”

Clicking on the ellipsis will bring up a window where I can select my dataset from

Then I can choose the value from the result in my dataset.

Please note – for the value, goal and status fields, I can only choose the result returned in the first row of my results. For the trend set, however, I can choose which column accurately reflects the values that reflect the trend set.

Then I hit “Create”, and my KPI will appear on my report portal!

Create New Mobile Report

To create a mobile report, you would download the new mobile report publisher. When you first open the tool, you’ll see a blank canvas where you can create your report where you either start with your visuals OR with your data. The experience is the same as you have with Datazen today in that respect.

If you start with your visuals first, sample data will be generated automatically that is tied to the report and will change dynamically as you change your visual selections.

You can see this by switching to the data tab.

To add your own data, click the “Add Data” button and select where your data is located. You can add local Excel data OR a shared dataset from your SSRS instance.

You can then change the data that your visuals are connected to

I can then return to the canvas and see the data has been hooked in to my visuals when I test the report.

In addition to the main layout, I need to create a phone layout for my phone users so the experience is optimized for that device type and proper thumbnails can be generated. I can do so by changing the layout dropdown in the upper-right hand corner to Phone and laying out my report. If I don’t remember to do this, the app will prompt me to do so.

I can also change my theme options just like I could in Datazen, by choosing one from the the upper right-hand menu. I’ll leave mine as the default theme for this report, however.

Once finished, I can save my report either locally, or to an instance of Reporting Services. To do so, I can hit the save button in the upper right hand corner

And then choose where I’d save my report to.

If I choose server, I’ll be prompted to save my report to a folder location on the server I got my data from.

After I’ve saved it, if I return to the portal, I can open the report and see my mobile report thumbnail.

And click it to open and interact with the report in my browser.

View KPI’s and Mobile Reports in the iOS App

The Power BI app now allows you to connect to your Reporting Services server to view your Mobile Reports and KPI’s. To get started, make sure you have the latest version of the Power BI app downloaded to your device.

Once downloaded, when you first open the app, you’ll be greeted with a welcome slide.

Hit the Sign In button to move to the app experience.

This is where you’ll add your Reporting Services server. To do so, select the Reporting Services button and enter your server details and the credentials you’ll use to access the server. You can also toggle whether the connection is using http or https. Again, https is recommended for all production scenarios.

Enter your information accordingly. You’ll need to make sure the URL follows the pattern I demonstrate below.

Click Connect. If it connects successfully, it will show the KPI and Mobile Reports screens you have access to on the server. You can swipe between the two options to view the different assets accordingly.

You may tap on any KPI or Mobile Report to bring it to full screen and interact with it accordingly.

When you enter the app in the future, you will see the server in the menu that you have setup. You can only have a single connection to Reporting Services at a time in the app.

The app will default to the KPI page as the first page you see in the future unless you are also connected to a Power BI instance. It will default to Power BI when both services are connected.

Whew! That was a long post, but hopefully it’ll help you get up and running quickly so you can try it this new functionality over the holidays. So Happy Holidays, and enjoy the all-new SQL Server Reporting Services Mobile Reports experience!

How to use SQL Server Integration Services and Azure to move data between Microsoft SQL Server and SAP HANA

This blog post falls into the category of “I’d better make sure I document somewhere how I just did all of this.” I really enjoy using SQL Server Integration Services, because it’s like a “Get out of Jail Free Card” for me. In the past, if all else failed, I knew I could always use SSIS to bring my data into a SQL Server database and build my reports against that with ease.

But did you know that SSIS can also be used to pull data OUT of SQL Server and into another database? This is something I rarely did, but since it seems some folks aren’t aware you can do this, I figured I’d flip the script and redo an SSIS job I’d put together. Instead of pushing data in a SQL database from SAP HANA, I’ll send the data the other direction for a change. So I put together this very long post that walks you through the following steps to show off a little bit of what you can do with SSIS –

– Spinning up a HANA database in Microsoft Azure
– Creating a table in SAP HANA
– Creating the SSIS package that moves the data between HANA and SQL Server (or Azure SQL database, in this example)

Let’s get started!

Step 1 – 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 an edition of SAP HANA developer edition because I’m just testing some stuff and won’t be using this for production purposes.

Accept the Terms and Conditions

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 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.

Ta-Da! It’s all done!

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)

Choose a region to deploy the server to from the dropdown. Your Azure account will be pre-populated in the Account box, then 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. Do this and hit Next.

You can setup when you’d like the solution to suspend/terminate so it isn’t running all the time.

Or simply bring it up or down as you need to manually.

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.

Part 2 – Creating a destination table in your SAP HANA database –

If you already know how to create a new schema and data table in SAP HANA, you can skip this section.

1. Go to the SAP HANA Web-based Development Workbench: Catalog on your HANA server. The web url will look like the following – http://yoursaphanadevboxaddress/sap/hana/ide/catalog/

2. Right-click on the Catalog folder to create a new schema – you can call the schema whatever you’d like. This is the equivalent of creating a new database in SQL Server.

3. When you’ve created the new schema, right-click on it in the left hand menu and choose “Open SQL Console”. This is where you’ll write the SQL script to create the table. I’m going to create a table based on the [Sales].[vStoreWithDemographics] view from the AdventureWorks database. The script I used was the following –

CREATE COLUMN TABLE “YOURSCHEMANAME”.”STORES”

(“NAME” NVARCHAR(50) null,
“ANNUALSALES” DECIMAL(16,2) null,
“ANNUALREVENUE” DECIMAL(16,2) null,
“BANKNAME” NVARCHAR(50) null,
“BUSINESSTYPE” NVARCHAR(5) null,
“YEAROPENED” INT null,
“SPECIALTY” NVARCHAR(50) null,
“SQUAREFEET” INT null,
“BRANDS” NVARCHAR(30) null,
“INTERNET” NVARCHAR(30) null,
“NUMBEREMPLOYEES” INT null)

Click the little run button to run it –

If successful, you should see a little note saying it ran with success

Part 3 – Create the SQL Server Integration Services package to bring your data in.

To create a SQL Server Integration Services Project, you’ll need to make sure you have a program called SQL Server Data Tools – Business Intelligence installed. It integrates with Visual Studio, but you don’t need Visual Studio already installed to use this program. If you do have Visual Studio installed, however, this will add new project types you can select. The version for SQL Server 2014 is located here to download and use – https://www.microsoft.com/en-us/download/details.aspx?id=42313.

To get started, select File – > New -> Project, then select the Integration Services Project option.

A new project will open, and you’ll see the following tabs –

I’ll need to create the proper data flow to move the information from one data source to another. SQL Server drivers are already available out of the box, but I need to make sure I have my HANA ODBC drivers installed on my development machine.

You can set up the ODBC connections once you’ve downloaded and installed the drivers from either SAP or a third party by going to Control Panel -> Administrative Tools and selecting that

then choosing the ODBC data source you wish to setup, which in this case would be SAP HANA. I usually install both the 32-bit and 64-bit drivers, just in case. You will either need to get these drivers from SAP, or some third parties offer them as well.

IMPORTANT! MAKE SURE YOU HAVE THE 32-BIT HANA DRIVER INSTALLED ON THE SYSTEM WHERE YOU HAVE VISUAL STUDIO INSTALLED – YOU CAN’T BUILD THE PACKAGE IF YOU ONLY HAVE THE 64-BIT DRIVER INSTALLED.

Once that is done, you can now setup both of your data sources in Integration Services.

In your SSIS toolbox on the left-hand of your screen, drag the Data Flow Task onto your Control Flow.

Double click on the task to open the Data Flow Task tab. Here is where you will add your source and destination locations. For SQL Server, I am going to use an OLE DB source, which I’ll find under the “Other Sources” in my toolbox. I can then drag that onto my Data Flow Task.

Double-click on the source to create a new connection to my SQL Server information by clicking New –

Then New again

I’ll be prompted to setup my SQL Server connection details. I’m using an Azure SQL Database as my source in this example, so I entered my details and hit Test Connection to make sure it was successful.

I can now select my table/view from my SQL database that I want to transfer to SAP HANA.

And I’ll select the columns I want to transfer. I didn’t bring over the BusinessEntityID (just because I wanted to show this) that existed in my SQL view, so I’m unchecking that column and then hitting OK.

NOTE: If you are selecting a view where one of the field types is Money, please note it is treated as a text field in HANA, which means you’ll get an error message if you try to bring it into the table that you scripted in an earlier step. I updated the view in my database to cast those fields as decimal, and this then worked as written.

I should now see the following item in my data task view. If there is no red X, it means I don’t have any errors and can proceed to setting up my destination. First I’ll rename it SQL Database to make it easier to remember.

Now I can setup my HANA database as my destination. I’ll add an ODBC destination to the data task

and rename it SAP HANA. I also moved it right under my data source item and dragged the blue arrow between the items to show the direction I am having the data flow go.

Now I add my HANA database connection information. I’m going to create a new ODBC Connection Manager with the HANA details I previously configured. All I need to do is select the existing information and hit OK.

It will connect to the HANA system and show me a list of tables where I can put the data. I’ll select the table I had previously created in my HANA database.

You can see that I still need to map the columns on the Mappings page. Because I had created the tables to match the names and data types, the system will automatically map these items for me. I hit OK to save this information.

As you can see, I don’t have a red X, so I can proceed to the moment of truth – testing the job to see it moves the data properly.

We’ll run a quick select statement in the HANA console to show there is currently no data in the table I am loading my data to.

Yep, it is still completely empty. Now let’s run our SSIS job and add the SQL data to this table. In Visual Studio, I can just hit the “Start” button to run the job and confirm it works properly

Yay – it worked!

To confirm, I’ll re-run my query in HANA and see the results. Yes, the data is there!

That’s it – all I’d need to do now is save my project and deploy the package to my SQL Server instance. If you’ve never deployed an SSIS package before, there is a good tutorial here to help you out –

https://msdn.microsoft.com/en-us/library/ms365338.aspx

This is a very, very basic example of what you can do with SSIS, and I encourage you to spend more time with it, as you could find yourself in a situation where you need this kind of freedom to meet your customers needs.

Hope this was helpful – thanks for reading!

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this: