How to use A SKU’s to try out Paginated Reports in Power BI without upfront cost or long term commitment

image

Since the Eagles game doesn’t start until later, thought I’d put this together for folks.

As many of you know, earlier this month we announced a preview of Paginated Reports in Power BI.  While folks were excited about this, there were those who were disappointed it was only available (for now) if you had Power BI Premium.

“I don’t have Premium (yet . . .),” they said,  “But I still want to try this out and use it.  How can I do so without committing to it for a month and paying $5000?”

The good news is, there is an easy way to do this.  See, as I’ve stated in previous interviews, you can try all the functionality out by spinning up an Power BI Embedded A SKU capacity, which is available to purchase through Microsoft Azure.  While normally A SKU’s are specifically used for embedding scenarios, there is no licensing restriction against using them internally if you’d like.  However, generally this makes little sense vs. purchasing a P SKU for most use cases, since each user would still need a Pro license to access the Power BI portal AND it’s almost $1000 more per month if you run it all the time.

But if you just want to try out the new functionality that is available only on Premium capacity, the big advantage of A SKU’s is you can stop/start them just like any VM in Azure, and you’re billed by the minute vs. having an initial upfront monthly cost.  This means you can spin up an A SKU, turn on the paginated reports capability in your Power BI portal, and start using it.  And when you’re done, you can go into Azure and pause the capacity until you want to use it again.  Since you’re only billed for the time it’s running, you can try this new functionality out for around 14 cents a minute! (this figure is based on my fuzzy math being done while my son is playing Fortnite right next to me, so forgive me if this is off by a few cents one way or another).  Let’s walk through how you’d go set this up yourself –

I have my own Power BI subscription (yes, I pay out of pocket for this), since I like to have access to the exact same experience as all of my users do (or I forgot to turn it off when I joined the team . . .).  In any event, I have a Pro license, but when I check the Admin Portal, I can see I have no Premium capacities right now.

image

Note another tab there which says “Power BI Embedded” – I don’t have any of those capacities, which you purchase in Azure, spun up currently either.

image

To get one of those going, I’m going to head to the Azure Portal and login with my credentials that I also use for Power BI.  Why? Because I’m the only user, so I’m also the admin.  Now, I can do a search for “Power BI Embedded”.  This will take me to the management page, which looks like this

image

I hit the “Create Power BI Embedded” button, which, if you haven’t signed up for an Azure account yet, you’ll be prompted to do so, and this includes a $200 credit for 30 days.  If you have signed up for Azure, you will skip this step (duh).

image

Now that I have that setup, I’ll go back to the portal and get back to the previous screen.  Here, I can click the button again to setup a new Power BI Embedded A SKU.  You need to select at least an A4 capacity size or higher to use Paginated Reports, so I’ll pick an A4 in my home location to spin up.  (NOTE: Paginated Reports are also supported in multi-geo scenarios, even during our public preview, so I could choose other regions outside my home region if I wanted to.)

image

Everything looks good, so I’ll hit create and wait for it to finish deploying.

image

If I check my Power BI Portal, I see that it is also showing as being deployed there under the Capacity Settings –> Power BI Embedded tab
image

Once it’s completed deployment, when I click on the capacity name, I’ll see I can manage this capacity just like I would any premium capacity in Power BI.

image

Under the Workloads, I see the two new preview workloads, and I’ll set Paginated Reports to “On” and assign 50% of the memory to that workload.  I can also try out the new Dataflows workload as well, but I’ll save that for another time.

image

After a few moments, I’ll see the message has changed from starting to Ready, and my workload is now ready to use.  I’ll assign all (one) of the workspaces for my organization to this new capacity.

image

Cool!  Now, I can upload my first paginated report to the workspace and view it.  I’m using a vintage Halo sample report, and it renders without a hitch.

image

But now I want to stop using the capacity and not get charged (since I finished this blog post).  No problem – I can go back to the Azure portal and just pause the capacity until I want to use it again.

image

When I’ve paused it, I can no longer view my paginated reports in Power BI, but they aren’t deleted or otherwise affected.  They’re still there waiting to be used again when the capacity is started up, and I can delete them if I need to.

image

And that’s it – in less than an hour, including the time it took to type this blogpost, I created a new Azure subscription, created my first Power BI “Embedded” A4 capacity, turned on the Paginated Report workload, assigned a workspace, uploaded and viewed my report, and then paused the capacity to stop the billing on it.  Whew!

Thanks so much for reading through the post today, and I hope you all take some time to try out the new paginated reports in Power BI Premium.  And if your organization doesn’t already have Power BI Premium, use this walkthrough to give it a try yourself!

Happy Thanksgiving (week)!

Adventure Works brand package now available!

image

Super short post today.

With the release of an updated test/demo Azure VM for Power BI Report Server on Monday, we thought it made sense to provide a new Adventure Works brand package for everyone to use in their demos/presentations to go along with it.  Feel free to download and use with Reporting Services 2016/2017 or Power BI Report Server – https://1drv.ms/u/s!Au6-0xX27UdgnOIapntXPaoPcqqOWQ

Thanks!

Tiggee, Batman and Power BI Premium

WP_20130922_114

On the eve of the Microsoft Data Insights Summit, I thought I’d finally write the story behind the picture I’ve included with this post.  How does this story relate to the recent announcement around Power BI Premium?  Well, I’ll leave that up to you to decide if it does, or if it’s simply a cute little story that involves my son Matthew (who has been bugging me to write about him on here).

A couple years ago, my son asked his sister Caitlin and I to play Monopoly with him.   We happily agreed, and went downstairs to the family room.  But we didn’t see the usual setup.  Instead, we were greeted with what you see in the picture.

“What the heck is this?” his sister asked.  “This isn’t Monopoly!”

“I know – now it’s AWESOME Monopoly!” he replied.

“Awesome Monopoly?  That sounds stupid.  I don’t want to play that.”

“C’mon, please – it’ll be fun.”

“No it won’t – it looks dumb.  I’m going back upstairs.”  With that, she turned and stomped out of the room, taking Happy Bear with her.

I winced.  This had played out many times before, and the ending had always been the same.  I glanced towards the kitchen, seeing if the tissue box was still sitting on the counter from an earlier incident.  But it wasn’t needed – he just smiled, sat down and started re-arranging Batman to make room for me.

“She’ll be back,” he said.  “You’ll still play with me, won’t you dad?”

“Um, sure.  Awesome Monopoly sounds, um, awesome,” I said, not quite sure what I was in for.

He tried to explain the rules , and I’ll admit, they seemed pretty confusing the first time he explained them to me.  I don’t remember everything he laid out, but one rule he mentioned during this initial explanation was if you rolled a 12 and landed on “Chance”, Batman got put in jail for a turn OR you had to draw a playing card.

“Buddy, I gotta be honest – I don’t fully understand some of these rules you’ve added.” I told him.  “Do you think we could make it a little less confusing?”

“What do you mean?  We haven’t even started yet.”

“Yeah, I know, but some of these rules . . .”

I didn’t want to push too hard, but at the same time, I couldn’t imagine the game going well if the Elf on the Shelf remained the banker for the entire game.

“Okay, new rule!  We can always change the rules if we decide they’re dumb.”

“Really?” I asked.

“Yep – that’s why it’s Awesome Monopoly.  We can keep making it more and more awesome together!”

I shook my head and laughed.  “Sure, pal, that sounds fair.  I’ll let you go first.”

We went a few rounds, me laughing and him asking me each time someone went how he could make it even more “awesome”.

“I’ll admit, pal, this is a lot of fun.”

“Yay – I knew you’d like it!  Can you go tell Caitlin how much fun it is?”

“Sure.”

I nodded, left the room, and came back with her having agreed to play after hearing how it great it was (and Happy Bear even came back too).

“Caitlin’s back – woohoo!”  He proceeded to run around the room and make sounds like a choo-choo train.  “Yayyyy!”

The game played out, and any time someone thought of a way to make the game even more awesome, we talked about it, tweaked the rules and kept making the game more and more awesome!  (My kids used to like to say awesome A LOT).

So what’s the moral of the story?  It’s either –

1. Batman cheats

or

2. Power BI Premium = Awesome Monopoly.

or

3. Chris needs better blog topics

If you’re coming to Seattle for the conference, I’ll see you next week!

Using URL parameters with Mobile Reports in Reporting Services

image

One area I’ve found folks looking for additional information recently has been how to use URL parameters with Mobile Reports in SQL Server 2016 Reporting Services.  This was a fairly popular feature in Datazen, so naturally people want to use it in SQL Server 2016 as well.

Quick background for folks who don’t know what URL parameters are in the context of mobile reports.  You can create a mobile report with parameters in SSRS 2016 so you and your report readers can filter your reports.  This is helpful when you have a large dataset, for example, so not all the records are loaded at once (If you aren’t familiar with how to write a shared dataset query with parameters in Reporting Services, you can learn more about that at this link – https://msdn.microsoft.com/en-us/library/dd239345.aspx).  Sometimes, you’ll want to provide those parameters as part of the URL for the report.  This can be useful if the report is a drill-through target from another report you’re viewing, for example.

First off, there is support for this feature.  You can find documentation on MSDN for it here – https://msdn.microsoft.com/en-us/library/mt772301.aspx

The article, however, only covers the basic scenario around using a dataset parameter.  Dataset parameters are passed on to the shared dataset that they were specified for when you created the mobile report. The parameter name must be specified as:

<Dataset Name>.<Parameter Name>

Below is a sample URL for supplying a value for the @Reqtype parameter of for an ExecutionLogs dataset:

http://rsserver/Reports/mobilereport/ExecutionLogs%20with%20Filter?ExecutionLogs.@Reqtype=Subscription

There are also parameters that are tied to the selection state of the control in the mobile report it was specified for.  These can be thought of as selection control parameters.  An easy way to find the name of the selection control to use in your URL is on the “Set drillthrough URL” screen in Mobile Report Publisher.

image

Like dataset parameters, the parameter name must specified in the following manner:

<Control Name>.SelectedItem
<Control Name>.SelectedItems

Below are sample URLs for specifying these:

http://rserver/Reports/mobilereport/ExecutionLogs%20with%20Filter?SelectionList.SelectedItem=Subscription

http://rsserver/Reports/mobilereport/ExecutionLogs%20Multi?SelectionList.SelectedItems=Subscription,Interactive

A special type of selection control in Mobile Reports is the Time Navigator control.  The time navigator control supports the following properties you can set in your URL –

– SelectedStartTime
– SelectedEndTime
– ViewportStartTime
– ViewportEndTime

The values for these properties are date time values formatted like this – “2017-02-15T00:00:00”.

Please Note: the “:” character needs to be URL encoded as “%3A” (browsers will usually do this automatically for you).

Now please read carefully the following item, because here’s where folks often run into trouble – if a user specifies a dataset parameter value via the URL and there is also a selection control wired up to supply values for the very same dataset parameter, a conflict occurs. The mobile report resolves this conflict by applying the dataset parameter value supplied via the URL (and discarding the initial selection control value). Note that this is true even as the user starts interacting with the report and chooses other selection values!

You can easily avoid this by supplying a selection control parameter via the URL instead of a dataset parameter.  The MSDN article is being updated to reflect this information as well, but keep this in mind when you are designing your mobile reports.

Finally, there is an item you can apply to your entire report via URL, and that’s to enable/disable showing the report title.

To disable the title, simply add “?title=false” to your URL, so it looks like the following –

http://rserver/reports/mobilereport/Claims%20Trends?title=false

If you want to show the title, just don’t add this to your URL.  Simple enough, right?

Hope this has been helpful for you and helped clear up a few things on this topic.  I want to give a special thanks to Andre M. from the engineering team for helping me with this blogpost.

How to insert a live Power BI report from SQL Server Reporting Services into a PowerPoint slide

image

Yay – the long awaited installable version of the Technical Preview of SQL Server Reporting Services is finally here.

Though very similar in functionality to what was first released on an Azure VM back in October, one item now officially supported that wasn’t previously is using https on your report server.  With this change, you can now use the Web Viewer add-in for PowerPoint 2013/2016 to surface live Power BI Reports from your server.  This is the same functionality already available for both paginated and mobile reports that I’ve walked through previously.  Here’s how you can try this functionality out as well with the Technical Preview –

1.  Go to the Insert Tab in PowerPoint.  You should see the Store Add-In in the ribbon.  Click it to open the App Store –
OfficeSnip
2.  A pop-up will open with the apps available in the store.  You can now run a search for the app you wish to use
image

Type Web Viewer into the search bar and hit Enter.  You see the Web Viewer app created by Microsoft, which is what we’ll use.

image

From here, you’ll need to enter the address for your Power BI report (and make sure you have https configured for your server!)  You need to use the “rs:embed=true” url pattern in the address, which we first introduced in SQL Server 2016.  Otherwise, you will see the following error –

When I use that address, it works just as one would expect in the preview –

image

You can also take advantage of row-level security in your report, which will flow through to the PowerPoint slide, by either using Kerberos or EffectiveUserName for your Analysis Services data source.

image

One suggestion is that you don’t use self-signed certificates with this feature – I have found the web viewer control doesn’t always work well with these (as anyone who saw my attempt to demo this at PASS can attest to.)

Thanks for reading and give this a try – it’s something we’ve heard from a lot of people wanting to take advantage of!

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