Why Mobile Reports and Power BI Reports aren’t a zero sum decision in SQL Server Reporting Services

There are two questions I’ve gotten for months.  And with the first public preview of Power BI reports in SQL Server Reporting Services, they’ve grown louder in the past week or so.

“Will we be able to view Power BI reports hosted in SSRS in the Power BI mobile app?” (Yes, we’re planning to support that scenario)

“If so, why should I still use mobile reports?” (Sigh)

I’ll admit, I understand one of the biggest driving factors around this question is not wanting to “bet on the wrong horse”.  And yes, a major reason people are concerned about doing that is because of, well, Microsoft and some decisions made at various points in the company’s history around certain products.  But I can only offer my opinion on this particular question and what I’d tell a customer if they asked me question two today.

Recently, Power BI added the ability to create mobile optimized layouts for reports.  But no one has been asking if they should still create mobile optimized dashboards now that they have this new functionality.  Why not?  Because they aren’t really an either/or proposition.  You have a dashboard to give you an overview of your business metrics, and then can dig into more detail by clicking a tile and jumping into a report.  In the context of SQL Server Reporting Services, it’s probably better to compare the mobile report use case to Power BI dashboards rather than Power BI Reports.

By doing so, it helps customers avoid the single biggest issue people run into today with mobile reports (and previously Datazen).  What’s the issue exactly?  Well, they’re trying to use it to build reports the same way they do with Power BI Desktop.  They want interactive reports that allow drag and drop, ad-hoc analysis and can handle hundreds of thousands of records in a data model they build on the fly.  They did this previously because they didn’t have an option to run Power BI reports on-prem.  Now that they will, that shouldn’t be an issue any longer and they can use the best tool for that purpose, which is Power BI Desktop.

Similarly, there are certain use cases today where it may be advantageous to build a mobile report as my “dashboard”, instead of just building it in Power BI Desktop directly.  Today, a few of these include –

– I need my mobile report to be available for my users offline and still be fully interactive.  Power BI reports are viewable offline, but have certain limitations.

– I like having the thumbnail view of my mobile reports on devices for my users to see a quick preview of what the report looks like, especially in combination with KPI’s I’ve created.

– I can add URL’s that link to other content from items on my mobile report, or add url parameters to pass with that as well.

– Some users have specifically told me they like the fact they can add content from different data sources to a mobile report without building another data model.  Why?  They can then filter across all of those data sources in a mobile report, which I know some folks have wanted to see in the dashboards in Power BI.

– I need to view the reports in a mobile phone browser vs. the app.  For on-prem customers, there are often additional challenges having users leverage native apps to view the content vs. a web browser on the device.  The Mobile Reports will look the same in a phone browser as they will in the mobile app, and for some customers, that’s pretty important.

Sure, there are other benefits I get from mobile reports as well, including the whole “design first” option.  But doing a “feature shootout” as of today misses the point – I see mobile reports as a complimentary item to Power BI Reports, just as they are a complimentary item to paginated reports.  They give users an additional way to meet their customer’s needs, and could play a huge part in a customer’s solution, or no role at all.  And since we’ve already stated that adding Power BI dashboards is not on the short term roadmap, I feel very comfortable telling users there is a lot of business value you can derive out of using both options with SQL Server Reporting Services for the foreseeable future.

Now, could that change down the road?  Could Power BI Desktop becomes the single tool used to create mobile and paginated reports in addition to what it does today?  After all, we stated in our blogpost last year that “We intend to standardize reporting content types across Microsoft on-premises, cloud and hybrid systems.”  Does that mean we could consolidate everything into one tool as well?  I guess, but there’s a pretty healthy debate you can have on whether its better to have one tool to do everything, or specialized tools for each report type.  Trust me, I’ve been in this debate many times already.  And it’ll really depend on what it always does – what do our customers think is the best option moving forward?

There you have it – one man’s opinion on the subject.  And I’m sure there are people who will read this and disagree completely.  Great – I wouldn’t have it any other way.  🙂

Feel free to let me know what you think in the blog comments, and have a great week!

Ten things you might have missed in the Technical Preview of Power BI Reports in SQL Server Reporting Services

image

Whew – this is my fourth (and final!) blogpost in four days around the Technical Preview for SQL Server Reporting Services.  It’s been a lot of work, but also a lot of fun putting these together.  For my final post, I wanted to touch on ten items you’ll see (and can try) in the preview to ensure you don’t miss them.

Before I do that, however, I wanted to take a moment to say “Thank you”.  This past week at PASS Summit 2016, and the reaction from the community to this entire preview, has been one of the highlights of my entire career.  I got to meet several of you for the first time at PASS, and as you learned very quickly, I’m not someone to sugarcoat things.  You all embraced this very unique preview and made the engineers who were on-site at PASS from the Reporting Services team feel like the rock stars they are.  That type of passion and energy is so infectious, again, I can’t thank you enough for letting the team know how excited you were with what we’ve delivered to date.  Our customers are the reason we fought so hard to bring this to you, and I promise, this is the just the first step.  We’re already back at it, working hard to bring you the first installable preview of this functionality as quickly as possible.

1. You can access your VM in Azure through a web browser

If you’d like other users to view and interact with the technical preview without remoting into the VM.  This is helpful if you’d like to show it to additional people in your organization, give users read-only access, etc.  Keep in mind, you’ll still need to do all creating/editing of Power BI Desktop reports on the VM directly using Remote Desktop.

To try this out, find the public IP address of the VM in Azure listed in the essentials section

image

Open a web browser on your local machine and type in the address/reports in the following format – http://8.8.8.8/reports (enabling https for the VM is something that is a little tricky to do, so I’ll have to decide whether that’s worth doing a future post around or not).  Enter your username and password for the VM, and you’ll be granted access to the report portal.
image

2. You can use embed the Power BI Reports just like mobile and paginated reports.

If you’d like to see your Power BI Report in an iFrame, you can add ?rs:Embed=true at the end of the report url.  For example, here is the embed url for the Sample Sales Report when connected directly to the VM via remote desktop – http://localhost/reports/powerbi/Sample%20Sales%20Report?rs:embed=true

image

3.  Mobile Reports and Power BI Reports both are available in the execution logs.

This is a common request for those folks interested in seeing when people ran certain reports and how often they did so.  If you go into the ReportServer database catalog via SQL Server Management Studio and run a query using the ExecutionLog3 SQL view, you’ll see log entries for both of these report types now show up –

image

4.  Direct url navigation is now available for KPI’s.

image

Users have so liked the new reporting services interface, they’d asked for an easy way to link to other content directly from the portal homepage so everyone can use the new portal as a starting point in their organization.  With this in mind, we added an additional option to KPI’s called “Direct Navigation”.  This allows you add a custom url as related content, just like you can in the portal currently, and simply bypass the current KPI pop-up action you get when you click on the KPI and go directly to the linked content.

For any KPI’s that you use this feature with, you’ll a little “link” in the upper right-hand corner of it so you can tell that it is enabled.  This will give the ability to create “dummy” KPI’s that you use just to link to outside content.

image

5.  You can turn report comments off for certain users by creating a custom role in SQL Server Management Studio and assigning it to them for folders/reports.

image

One piece of customer feedback we got when considering the comment feature was the need to give report owners the ability to restrict certain users from adding or viewing comments.  To accomplish that, we added new tasks for comments in Reporting Services that are assigned to security roles.  Security Roles in Reporting Services are managed through SQL Server Management Studio.  You can create a new role without these permissions and assign them to users accordingly by following the steps in this article.  (We’d recommend you hold off on changing the security roles for the Power BI reports in the Technical Preview, since we’re aware of an issue that was reported on another blog.)

6. You can favorite Power BI Reports just like any other report type

image

7. You can install another instance of Analysis Services on the same machine to have both Multidimensional and Tabular running simultaneously.

image

During setup, you get the option to run the VM with either Tabular or Multidimensional mode already running (along with demo Power BI content tailored for it).  However, if you really want to have both modes available on your machine, the developer edition installation files used for both the database engine and Analysis Services are located here on the VM – C:\SQLServer_13.0_Full

Simply add a new stand-alone instance of Analysis Services on the same machine and you’ll have both options available to build reports against.

8. You now have the option for a “List” view of your items in the portal

image

Simply toggle the layout option in the View menu in the portal to switch view types.

9.  A new, expanded context menu is available when you click the ‘…’ option for that item.

image

You’ll hear much more about items 8&9 in a future blog post on the Reporting Services team blog.

10. The Technical Preview VM will expire in six months.

Also note, the expiration date is six months from the date it was first made available in the portal.  Just be aware of this, and keep in mind you’ll probably want to migrate any content you put on there prior to this date.

And we’re done – finally, I can take a few hours to relax and watch the big Eagles/Cowboys game this evening.  I’m in such a good mood, I might not even care if the Eagles lose to Dallas.

Yeah, no, I’ll care – Go Birds!

Embed from Getty Images

How to run the Technical Preview of Power BI Reports in SQL Server Reporting Services on-prem using Hyper-V

hypervfinished

What a week!  With the announcement and release this week of the Technical Preview of SQL Server Reporting Services in Microsoft Azure, it’s been a whirlwind of activity and excitement.  And while people have generally been excited to go ahead and spin it up in Azure, there are still some folks who’d like to try out the preview on their local PC using Hyper-V.  Here’s how you can do just that.  I’ll also point out one big “gotcha” that I ran into doing it this way –

1.  Go through all the initial steps outlined in this blog post to get the machine up and running in your Microsoft Azure account.

2.  In Azure, find the Virtual Machine you just created and stop it by clicking the stop button.

stopvm

3.  Now, navigate to the resource group you created that contains the virtual machine.  You’ll see it setup several items, including two storage accounts.  Select the storage account that has “vhd” in the lengthy name, as this is where the virtual hard drives are stored.

image

4.  As you click into the storage account details, you’ll see two disk drives – one is labelled “dataDisk.vhd”, and the other is labelled “osdiskforwindowssimple.vhd”.  “osdiskforwindowssimple.vhd” is the one you need to download.

vhdinfo

5.  You now have a couple options – you can simply click the download button that appears when you select the vhd, or you can use a separate program that may help accelerate the download process (remember, the file is quite large).  These (free!) options include –

Microsoft Azure Storage Explorer
Azure Explorer from RedGate
AzCopy (advanced users only)

No matter which way you download it, the file will take awhile depending on your internet connection since it is 127 GB.  You might consider letting it run overnight like I did.

6.  Once your download is finished, you’ll need to setup a new virtual machine in Hyper-V to mount the virtual hard drive on.  This also means you need to have Hyper-V turned on in Windows.  With Windows 10, just follow the instructions in this walkthrough to do so.  If you have Windows 7, you can follow these instructions instead.

7.  Launch Hyper-V Manager on your PC to get started.  Select New Virtual Machine

newvirtualmachine

I’d recommend you name this new machine the same name you gave it in Azure, just for consistencies sake.  It isn’t required, but you might find it less confusing.  Hit Next

Choose “Generation 1” for this virtual machine.  Hit Next
image

You need to assign the amount of memory you’d like to make available to this virtual machine.  I’d strongly recommend assigning a minimum of 4 GB of memory to the virtual machine (remember, the machine we recommend in Azure has 28GB of memory), and really 8 GB (or more) is preferred.

image

For the purposes of this blog post, I am not going to assign a virtual network option for the machine.  This means I can’t access the internet from the VM (so Bing Maps won’t work if I choose a map visual), but I’m doing that to show you that yes, it can run entirely on-premises with no cloud dependencies.

image

Finally, I’ll select the virtual hard disk I downloaded from Azure to attach to the machine.

image

I’ll hit finish, and my new VM will show up in my list of virtual machines in Hyper-V manager.

image

8.  Here’s where the big “gotcha” is/was – if you right-click on the VM and hit “Start”, it will attempt to start and then fail with an error message saying “The Version Does Not Support This Version of the File Format” or something to that effect.  The issue is related to the fact we didn’t dismount the hard drive from the Azure VM (which I didn’t want to do, because I wanted to spin it back up again in the future in Azure).  To workaround this, you need to have Windows unmark the .vhd as a sparse file.  There are several ways you could accomplish this, but the easiest I found was with a program called Far Manager, which is free to download and use.

Once you’ve installed it, open the program and click on the “n” in the upper left-hand corner (don’t be scared of the GUI, it’s easy, trust me).

image

A window will pop-up showing all of your local hard drives – browse to the drive you vhd is on.

image

Select the vhd from the file list and hit ctrl-A.  A new menu will pop-up and you’ll see a box marked “Sparse”.  Uncheck that box and the click { Set }

image

It’ll take awhile to finish doing that (10-15 minutes in my case), but once it’s done, go ahead and try starting your virtual machine again.  You won’t get that nasty error any longer.

9.  It’ll take a few minutes to boot up and finish prepping.  When it’s finished, you can login with the administrator username and password you used when first creating the VM.  You’ll now be using Power BI Reports in Reporting Services entirely on-prem.

image

I’ll be back tomorrow with some tips and tricks you might not be aware to help you get the most out of this technical preview.  Until then, have a great Saturday!

Free custom map shapes for SQL Server Mobile Reports and Grab Bag Part 1

image

Happy Saturday to all!

As you may have noticed, I haven’t posted on my personal blog in some time (although I have been posting over at the SQL Server Reporting Services team blog fairly regularly).  If you’re wondering why, just watch the presentation Riccardo Muti gave at Ignite this past week, where he showed an early preview of publishing Power BI Desktop reports to SQL Server Reporting Services.  We’ve all been extremely hard at work on that, but with the (undefeated) Philadelphia Eagles off this weekend, I wanted to take some time to do a “catch-up” post where I touch on a number of topics.

1. There is a new version of the SQL Server Mobile Report Publisher now available for download.  I am planning on doing a post on the official team blog about this (and yes, it will list all of the changes that were included in the release), but I wanted to bring one item to your attention that was addressed in this release since this was something multiple customers asked us to address –

Dataset column names should be made more presentable before displayed in mobile report

Previously, if you had column names with underscores in your dataset, there was no way to remove those underscores or show a user friendly name for your report.

image

Now, when you create a mobile report, you’ll see that we now hide the underscores when presenting the column name in the report.

image

You’ll also need to install the latest cumulative update for SQL Server 2016 to make sure mobile reports you create and view on the server reflect this change as well.

2. A number of people had been looking for ways to more easily build custom brand packages for SQL Server 2016 Reporting Services.  A new third party tool is now available on CodePlex called the SSRS Branding File Editor that lets you use an Excel template to do everything around that process.  Folks who’ve tried it have let me know they’ve found it quite useful, so I’d encourage you to take a look.

3. We recently released an update for Report Builder (that I wrote the blogpost on) where we alluded to some improvements that were related to shared datasets.  These should help significantly with shared datasets built against SQL Server Analysis Services data sources for your Mobile Reports, especially if your datasets have parameters in the query.  However, because a few of these fixes also required some updates on the server, you won’t be able to take advantage of all of them until we have our next server update sometime later this year.

4. I pulled together some of the custom map shape files previously found on the sample Datazen dashboards, including the jumbo jet and stadium map files.  You can download these for use in your mobile report projects if you’re interested.  As usual, these maps aren’t officially supported by Microsoft in any way.

5. One of the pieces of feedback we’ve gotten recently is folks asking about the documentation for Mobile Reports and KPI’s in SQL Server 2016.  While the best place to get started for this is the MSDN documentation, it sounded like folks were looking for more advanced topics as well.  Feel free to leave me a comment if there is a specific topic or walkthrough you’d be interested in seeing a post on, or even having it added to the MSDN documentation.  I can’t promise I’ll be able to tackle every topic right away, but I’d definitely would like to.

Hmm – I wasn’t planning on focusing just on mobile reports this post, but that’s how it turned out.  I guess that means I’ll just need to do a part 2 of this post as well.  Until then, enjoy the rest of your weekend!

SQL Server 2016 Mobile Reports – Free Maps of the Week, Final Edition!

image

Here it is – the final post in the free maps of the week series.  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.

Hungary – Download
Iceland – Download
United Arab Emirates – Download
Sweden – Download
Singapore – Download
Serbia – Download
Panama – Download
Indonesia – Download
Israel – Download
Lichtenstein – Download
Kuwait – Download
Lithuania – Download
Luxembourg – Download
Macedonia – Download

Remember, these maps aren’t officially supported by Microsoft in any way.  It’s been fun doing these, and thanks to everyone who’s stopped by and grabbed some.  Enjoy!

How to use SSIS to enable oData and other data sources in SQL Server 2016 Reporting Services

Screen12

Ah, it’s been awhile since I’ve done one of my longer blog posts.  There’s one I’ve been itching to do for the last few weeks around SQL Server Integration Services.  If you follow this blog, you know I have a great affinity for SSIS.  A question came up a few weeks back around how could someone enable SSIS as a data source in Reporting Services, since the currently “documented” way is not only several years old, it is completely unsupported by Microsoft.

Well, a colleague of mine pointed out there was a way to do this using the Data Streaming Destination option in an SSIS package.  This option allows you to query the output like you would any other SQL Server view in Reporting Services.  Needless to say, I was eager to put together a walkthrough for folks so they could try this themselves.

To create a SQL Server Integration Services Project, you’ll need to make sure you have a program called SQL Server Data Tools  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 2016 is located here to download and use – https://msdn.microsoft.com/en-us/library/mt204009.aspx.

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

Screen1

I’m going to create a very simple project that exposes an oData data source, which isn’t available in Reporting Services natively (yet . .).  I’m using the good ol’ Northwind oData feed, but you could also use an oData feed you expose from a LightSwitch project, for example.

To do this, I’ll add a Data Flow Task to my SSIS project.

clip_image016Double click on the task to open the Data Flow Task tab. Here is where you will add your source and destination locations. First, I am going to use an oData source, which I’ll find under the “Common” items in my toolbox.  I can then drag that onto my Data Flow Task.

image

Double-click on the source to create a new connection to my oData source.  Click New and enter the information for the Northwind data feed –

image

Once that’s done, click OK and select the data collection to expose.  Since this is just an example, we’ll leave the Columns or Error Output tabs as is and hit OK to save this.

image

Now we can add the Data Streaming Destination item from the toolbox to the Data Flow Task

image
and connect it using the arrow so it looks like so.
Screen5
While we could change the columns we pass through or change the column names, we’ll leave everything as is for this example and simply publish the package by choosing “Deploy” under the Project menu.

Screen6

The wizard is pretty self-explanatory, but one thing to keep in mind is you need to have an SSIS Catalog already setup on your SQL Server instance.  Assuming you have that done, walk through the steps and deploy to your catalog in SQL Server.

Screen8

Open up SQL Server Management Studio and login to the SQL Server Instance you deployed your project to.  You should see it under your “Integration Services Catalogs” folder.
image
Now you’ll need to enable the “Allow inprocess” option on the SSISOLEDB linked server provider that’s setup in SQL Server.  Browse to the Providers folder under the Server Objects –> Linked Servers folder path
image

Right-Click on the provider and select Properties.  Simply check the “Allow inprocess” option and click OK to save.

image

Head the Start Menu on your server and open the SQL Server 2016 Data Feed Publishing Wizard.  Here’s where you’ll enter the settings to select your SSIS package and the SQL database you want to publish it to.  You can name the SQL view whatever you’d like.  Hit Publish to execute the wizard and create the new view.

screen10

If it’s successful, you should see the view now in the Management Studio when you browse the database.
image

But does it work in Reporting Services?  Let’s give it a try – I’ll setup my shared data source as I would any other SQL Server data source in Reporting Services.

image

Then, create a shared dataset from it in Report Builder and publish it to the server.  If it is working properly, you should be able to preview the dataset in the portal –
image

We’ll create a mobile report against the shared dataset.  There’s no issues doing this, and it recognizes all the columns properly from the oData source just like it was a SQL Server view.

image

Finally, I’ll publish the mobile report to the server and try running it in the Reporting Services web portal.  Success!

image

Keep in mind, the data connection could potentially be slow depending on the amount of data you’re accessing.

That’s it – you not only have a way to use an oData feed with Reporting Services now, but you have a way to use dozens of new data sources available from several third party providers.

image
I’ll be doing a blog post on this provider in SSIS whenever it is available

Now this was a fun blog post to do.  Thanks for reading and enjoy your weekend!

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

20160627_201100808_iOS

Hi all!

Sorry for the delay, but I’ve been on vacation with my family and haven’t had time to post any new maps until tonight.  Because of that, I’ve got an extra long list of free maps for you this week.  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.

Cuba – Download
Cyprus – Download
Czech Republic – Download
Egypt – Download
Turkey – Download
Thailand – Download
South Korea – Download
Slovenia – Download
Slovakia – Download
Saudi Arabia – Download
Romania – Download
Macau – Download
Monaco – Download
Montenegro –Download

Remember, these maps aren’t officially supported by Microsoft in any way.  I’ll be back with more free maps after the long holiday weekend here in the States.  Thanks!