How Microsoft Forms, Flow and Power BI can reshape the classroom

image

If you read my last blogpost, you know I have two kids.  One is in middle school, while the other is in elementary school.  While certain things have gotten more modern for them compared to my time in school, many parts of their day to day activities have not.  For example, it always surprises me that my daughter is issued a laptop for the school year, but a large part of her testing is still done on paper.  The teacher then puts the scores in online, and if we want to see them, we have to remember to go and check a different website.  Sometimes the original test paper is never even returned.  It makes it hard to take action and help her focus her studies for the next test.

This is a major reason I was so happy to see Microsoft Forms released recently.  It allows teachers to make quizzes where students can answer questions and see right away what they got right/wrong and their score.   It’s incredible simple to get started –

1. Click “New Quiz”

image

2. Enter a Title (or optionally, a picture) and click “Add question” and which type of question

image

3. Enter the question details, marking things like which is the correct answer, whether it is required, etc.  Repeat until you’ve done creating questions.

image

4. Share it out to the students, or make a public link and share it with anyone!

image

If you’re thinking Google Forms does something similar, yes, I know.  But Google Forms doesn’t integrate with Microsoft Flow, while Microsoft Forms now does.  Now it’s possible to set up an e-mail alert to the parents/tutor/whomever and send the results when their son/daughter has finished the test.  Now before you scream “helicopter parent”, it’s important to keep this in context.  I wouldn’t expect to have the teacher do this for every test, or for every student.  But there are times where it could be quite valuable and a great way to drive action, vs. hoping that you go check/your kid tells you/etc.

To setup a new flow with Microsoft Flow, it’s really easy.  You open the Flow app from your Office 365 account and click “Create From Blank” –

image

Next, type “Forms” to see all the different items for Microsoft Forms you can trigger an action for –

image

After selecting that, I choose the form from the dropdown to create the Flow for –

image

I want to make it conditional based on the e-mail address entered for the first question.  I select “Add a condition” and the question I want to trigger the flow based on the student response –

image

Then I entered the condition parameter and value to match, and what to do if it matches –

image

I save the flow, and now I’ll get an e-mail when she finishes this quiz, along with her answers (if that is setup to be included).

image

This also allows me to do things like setup a Flow for inserting a new row in an Excel document when a student answers a question, etc.  That makes it easy to quickly analyze the results in Power BI and see the total points she got vs. total available points!

image

It took me less than a half an hour to create a quiz, create a flow, and view the results in Power BI.  I’m excited to see the potential there for students and teachers to take advantage of this.  Personally, I’m already thinking of some great new ways to I could see everyone use these tools and others from Microsoft to engage throughout the entire school year.

Thanks for reading!

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!

How to use SQL Server on Linux to host your Reporting Services catalog

image

Let me get this out of the way upfront before we get to the good stuff.

**This is not yet officially supported by Microsoft.  We will do an official post when it is**

There we go.

While you can’t run the front end of SQL Server Reporting Services on Linux, many folks would potentially like to host the backend catalogs on SQL Server on Linux.  I was wondering over the weekend if this worked quite yet, considering that SQL Server on Linux had just introduced SQL Server Agent support as of CTP 1.4.  So, thanks to Microsoft Azure and some spare time, I decided to give it a go.

First, I used the SQL Server vNext on Red Hat Enterprise Linux 7.2 image in Azure to setup my Linux VM.  This is the easiest way to get started by far, and there is a complete walkthrough how to set this up end to end here – https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-azure-virtual-machine

Search filter for SQL Server vNext VM images

I used PuTTY to connect to my Linux instance via the IP address, and I didn’t install the SQL Server Tools on the Linux box.

You’ll need to install the SQL Server Agent on the Linux box as well, which I did by following the steps here under the part titled “Install on RHEL” – https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-sql-agent.  Go ahead and do this right after you get SQL Server up and running and are still logged into the machine with PuTTY.

To confirm everything was up and running properly, I connected to the server using SQL Server Management Studio on my local PC.  Everything looked good so far.

image

Now, I had to setup my new SSRS instance on a separate machine.  I did this using an Windows Server 2016 Azure VM and simply installing the latest Technical Preview from January on it.  Next up, you need to set the database catalog location in the Reporting Services Configuration Manager.  Keep in mind, you’re limited to using SQL Server authentication to connect to the Report Server database in this scenario (and SQL Server on Linux in general right now).  Everything looked good until I got an error at the “Generating rights scripts” part of the config process for the new database.

I figured I was stuck here until I found this really old blog post from Adam Saxton about the error message I was getting.  The blog post itself wasn’t relevant (sorry Adam), but the very last comment in the thread WAS helpful from Carlos Shepardos.

“If you use a SQL alias to connect to the SQL Server server you have to ensure that the local computer is also able to resolve the SQL alias name via a DNS resolution request. If the local computer is not able to do this you get the error message shown above.

The easiest way to ensure the SQL alias name is resolvable to the IP address of the SQL Server is to create an A record entry in DNS or add a line to the local hosts file.”

So with that in mind, I went to my HOSTS file on the server and added an entry for my SQL Linux instance.  You can navigate the the HOSTS file on your RS server here – C:\Windows\System32\drivers\etc

image

I then used that name instead of my IP address for my SQL Server instance entry for Reporting Services, and the wizard finished without issue.

image

I navigated to my report portal, and it loaded just like you’d expect.

image

To test the SQL Server Agent, I created a simple report and dataset while also setting up some subscriptions and cache refresh plans.  Sure enough, they ran successfully and the jobs showed up as expected when I looked in SSMS as well!

image

As I mentioned earlier, this still isn’t officially supported quite yet, but I was able to use it without any issues in my (admittedly limited) testing.  Would love to hear about your experiences trying this scenario out as well.  Thanks for reading!

Use Analyze in Excel + Excel Camera to create PowerPoint magic

image

So this blog post falls into the “I just think this is cool” bucket.  Did you know Excel had camera functionality?  Well, I didn’t.  And this despite the functionality being there since 2003(!).  This isn’t there by default, but if you turn it on in your Quick Access Toolbar, you can take advantage of it.  What’s so special about this functionality in particular?

Basically, it allows you to take a picture of a collection of cells in your workbook.  I know, amazing, right?  Stay with me here – it’s a picture, but it’s a LIVE picture.  Here’s a simple example using Excel 2016:

My table in my Excel workbook looks like this –
image

If I highlight those cells and use the camera tool, I can do the following in my workbook (I normally wouldn’t make the picture this big, but wanted to emphasize it was a picture and not just a linked table) –

image

Now I’ll change the numbers to text.  When I do that, the picture updates automatically as well –

image

And since it is a picture, I can do all the normal things I could do to a picture in terms of formatting –

image

So could I do something like this with a Pivot Table?  You bet, including one using “Analyze in Excel” in Power BI for the data!  I tried this myself against a sample report I loaded in PowerBI.com.  I chose “Analyze in Excel” from the ellipsis –

image

Then created my Pivot Table against the data and used the camera tool as I did in my earlier example.

image

Any change I made in the Pivot Table is reflected in the picture –

image

This is nice, but what I really want is this live picture in a PowerPoint deck that gets updated when my data is updated in Excel.  Let’s give that a try.

After I’ve selected the range in Excel with my camera tool, in PowerPoint I can choose “Paste Special” and select “Paste Link” so I can paste it as a “Microsoft Excel Worksheet Object”.  This will allow the data to updated dynamically whenever I have new data in my workbook. (You can also add Pivot Charts to your PowerPoint presentations via ‘Paste Link’, and the data will also update dynamically for those as well!)

image

For example, if I change the “AccountCount” to say “SSRS Rules!”, it changes dynamically.

image

I can also add a hyperlink to the picture back to the original report in Power BI if I wanted to jump there quickly during a presentation to do additional analysis on the data.

image

Something to keep in mind – if you share the PowerPoint deck with a user who doesn’t have access to the original Excel workbook, they can still open and use the presentation with the static images reflecting the last time the data was updated.  I think this is valuable, since I know often the workflow at some companies is basically “Hey so and so, I need that slide deck for the meeting tomorrow.  Can you update the slides from the previous meeting and send it to me?”

Thanks as always for reading  – maybe you already knew this trick, but if not, hopefully it’ll save you some time in the future!

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!

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!