Powerful Productivity using Power BI and Office 365 – Part 1 in a series

Alright the long weekend is here (and sadly over by the time I finished this post)!  And since Monday is was a celebration of the worker here in the US and Canada, I wanted to do something special for this post.  Microsoft Office is the go to productivity suite for 1.2 billion people every day.  That translates to 1 in 7 people on the planet earth using Office to get things done, both personally and professionally.

It’s also a key part of every successful business intelligence implementation I’ve seen – and I’m not just talking about the ability to export to Excel.  In my previous life in shadow IT, the VP I supported used to hammer this point home to me.  He and his team lived in the Office suite day in and day out to do their jobs, so whatever I was providing to his team, I needed to always keep that in mind.  They had jobs to do that didn’t involve learning a complicated new report or app – if it wasn’t easy to adopt, show immediate value, and work well with things like PowerPoint, Excel and Outlook, then it was dead on arrival.

While I’d like to think I did this pretty well, looking back now, I can’t believe how much additional work I had to do simply to achieve the same results I could get just a few years later.  And I’m not talking about me being more skilled (or balder) – the level of productivity someone can achieve using tools like Office365 and Power BI is STUNNING.

You’re probably thinking – “Yeah, it’s “stunning” a Microsoft employee is telling us how amazing their new stuff is,” as you roll your eyes and switch to Jen Underwood or Rob Collie’s blog.  I’ll admit, that isn’t a terrible idea, but hear me out.  I just want to see what my old job would have been and how much more I could have accomplished.  I used to spend my days building apps, setting up databases, building reports, etc.  With the way things have been automated and simplified in Office 365 and Power BI, could they be done now entirely using those tools?

“OH MY GOD – HE’S NOT BLOGGING ABOUT DATAZEN!  THE PRODUCT MUST BE DEAD, etc.” – Thought by at least one person reading this blog post.

To this person I say, yeah, um, no.  No, the reason I am not including Datazen is because one of the rules I need to follow for this self-imposed challenge is that I can’t use IT to setup a VM for me.  I have to do everything in the context of Office 365 and Power BI for this particular challenge.

So, without further adieu, here’s a typical project request I might have gotten 5-6 years ago –

There’s a request in my inbox from someone whose team needs a line of business app built.  They need a simple app built to capture ten fields of data for a one-off project.  A couple temps will be entering the data for a week or two, and they’ll need to do it via a web interface.  There are some reports I’ll need to build off of the data, and I’ll need to show the final numbers in PowerPoint deck I provide back to them so they can add some additional commentary.

Requests like this I used to eat for breakfast (along with Honey Nut Cheerios, usually).  Let’s assume this was asked of me in 2009.  I probably completed the request this way (FYI – SharePoint wasn’t set up and available to me) –

– Ordered a virtual machine (VM) from IT then waited until it was finally provisioned for me.
– Installed SQL Server 2005/2008 on the VM to host the database
– Setup Internet Information Services (IIS) on the VM to host the website
– Built the app using ASP.Net maker (which I had purchased myself) to do these little forms over database projects.
– Deploy the app to the server from my local machine
– Built a couple reports for them in SQL Server Reporting Services using Report Builder.
– Used SnagIt to grab screenshots of the reports and app I could use in the PowerPoint deck.
– Shared the files via e-mail to the folks for them to add commentary and present out.

I can’t believe that’s really how I used to do a project like that then.  At least in 2011 I could have used Visual Studio LightSwitch and Power Pivot.  Let’s try redoing that in Office 365 and Power BI –

1.  Starting with Office 2013, Microsoft Access can be used to make line-of-business apps that you deploy to Office365 sites.  These can be accessed via the web by users to enter data that’s saved in a SQL Azure database that Microsoft provides (as long as you have no more than 1 GB of stored data).

So I can open my team site in Office 365 and select Site Contents –

Then I choose Access App, and enter the title of the app I want to build.

I’m then greeted with a webpage that says I’ve successfully created the app.  I can start adding tables and screens in Microsoft Access on my desktop to finish the application.

About 7 minutes later (no seriously, I timed it), I have created my table and form, and it is now available online, via browser, for folks to start entering data –

I also need to run reports.  No problem there, since if I look back in Microsoft Access on the Info tab, I am provided information around how I can connect to my database and run reports –

Once I’ve created the read-only connection, I can open Power BI and use the direct connect with SQL Azure functionality to connect to the database and create my dashboards and reports.  To get my connection information, I grab the details from Access for the read-only connection –

and then plug it into Power BI –

This allows me to create a report in a web browser against the dataset immediately and share it out with individuals or a group.

Now I can add the report to a PowerPoint slide by using some newish, built-in PowerPoint functionality (remember, I can’t use the default Snipping Tool in Windows because it isn’t in Office 365).

In Office 2013 and Office 2016 Preview, under the insert tab, I can insert a screenshot right from PowerPoint.

If I select Screenshot, I see all the open windows on my desktop, and I can simply select the Power BI browser window that’s open to insert the screenshot.  I then resize my screenshot so they can add commentary to the slide accordingly.
Once I’m finished my deck, I can save my PowerPoint deck either directly to an Office 365 site, or my personal OneDrive for Business site and share the file securely via a link in my web browser to approved users only.

Once folks get the link I send via Outlook and Exchange, they can review the deck together if they  wanted and even make the changes in real time.

So I was able to meet the goal of only using Office 365 and Power BI to complete the project.  How much more productive was I?  The amount of time I estimate it would have taken me to complete the project previously was –

– Minimum 2 weeks (336 hours) for a VM to be provisioned for this project.
– 2 hours to setup SQL server
– 1 hour to setup IIS
– 3 hours to build and test the app
– 2 hours to deploy the app to IIS and test (this never worked right away)
– 2 hours to build and test the Reporting Services reports
– 1 hours to build the slide deck and send via e-mail

So 348 hours minimum were needed to get everything done on my side, and this is assuredly being generous with the estimate.  How long did the same thing take using Office 365 and Power BI?

– 0 hours for VM setup (not required)
– 20 minutes to build, test and deploy app
– 0 hours to setup IIS
– 0 hours to setup SQL server
– 20 minutes to build report in Power BI
– 1 hours to build the slide deck and share the link for review

So I went from needing 348 hours to needing about 2 (let’s round up).  That’s a productivity improvement of over 150x!  Just think about that – this project went from something I had to do a fair amount of additional work around just to request the VM, do all the setup and configuration, etc. to something I could have back to them for the initial data entry piece within an hour of the original request.  Plus, the entire project can be done or reported on from every mobile device.  Yeah, that wasn’t a requirement for the project originally, but it certainly would be now, and that’s all included with no additional development work required on my part.

I really enjoyed doing this first post in this series – it’s easy to forget just how powerful the tools like Office 365 and Power BI are (I know I often take this stuff for granted) and how much value they bring to our customers.    I’ll do more of these now and again that I sprinkle in between my normal Datazen posts, which I know my dozens of loyal readers eagerly await each week.

Thanks for reading!