Diving with Sway (or What I Did on my Autumn Vacation)
Saturday, November 7, 2015

Although I threaten to do so in the description of this blog, I rarely talk about diving. I think that the last time that I did so was about 3 years ago. However, I recently encountered an intersection of my vocation and my avocation that I thought was worth sharing.

In September/October this year, my wife and I went on a liveaboard dive trip down the entire Sea of Cortez (or the Gulf of California to you folks in the USA). A liveaboard is a cruise of sorts where you live on the boat and go diving multiple times per day. You get to see some amazing things that would otherwise be unavailable.

I like to document my dives using mostly photos and simetimes video.  The GoPro given to the Office MVPs last year (thanks, Microsoft!!!) at the MVP Summit has increased my use of video. Ultimately I return from these dive trips with a large collection of pictures and video, which I share out via Flickr, Facebook, OneDrive and YouTube. The problem is, these collections are disconnected, and while i try to tag and title the items, none of these collections really tell the story.

Thats where Sway comes in.

I’ve had access to Sway for a while, and while I did dabbble in it a bit originally, I didn’t really “get it”. I had a lot of video this time, and the trip warranted more “narration” than a simple collection of pictures could provide. I decided to give Sway another shot, with (I think) good results. You can see for yourself below. Click on the “Made with Sway” icon at the bottom left to open in full screen.

I found the Sway was exactly the right tool for this task. I was able to bring together diverse media elements from the trip, and organize them in ways that suited the narrative (not always chronologically). Adding the narrative itself completes the picture, and the presentation can stand on its own. I think, to sum it up, that Sway adds context to content.

Sway is an online tool. There are native clients available for Android, iOS ans Windows 10, but you are always working with online content – there are no files stored locally. Creation is simple. There are a number of preconfigured layouts which can be tweaked to some degree, and content addition is a simple matter of dragging and dropping from a set of cloud based repositories. You can share (and collaborate) at any time, and when ready, you can publish it on docs.com, where it will be available in galleries, search engines, etc.

In addition to travelogs, I can see this having great usefulness in the education space. I don’t think it will be replacing PowerPoint anytime soon – they serve different purposes. I would say though that in the absence of a presenter, I would likely rather have access to a sway than a PowerPoint deck – the Sway can do so much more on its own for explanations.

Finally, Sway is free. You’ll need a Microsoft account (either consumer or organizational) to use it, but you can simply point your browser at sway.com and get going on it. Give it a shot! I think you’ll like it. I do, and I think I’m going to dip into my back catalog to create more. WHen I do, I’ll be posting them in my diving collection on docs.com.

The Last Days of PerformancePoint
Wednesday, November 4, 2015

An alternate casting for “Silicon Valley”..

Last week, at the SQL PASS summit, Microsoft publicly unveiled their Business Intelligence Reporting Roadmap to widespread critical acclaim. The vision is, in my opinion comprehensive, and complete. It’s probably the most comprehensive articulation of vision that we’ve seen in the Microsoft BI world in 10 years. It spells out the investments being made in Reporting Services, the integration of Datazen, and the importance of Power BI in the overall structure. There is one BI technology that is rather conspicuous by its absence


PerformancePoint isn’t mentioned in the roadmap at all. As far as I know, it wasn’t mentioned during the PASS summit either. I haven’t heard it mentioned by anyone at Microsoft since the Ignite conference last May, when Bill Baer confirmed that it would be a part of SharePoint Server 2016. Of course, as I mentioned in my post at the time, inclusion doesn’t mean that the product has a future. Although it will be included, its for backward compatibility reasons – there are absolutely no new investments in the product, it’s exactly the same as it was in SharePoint 2013.

This has happened to multiple products at Microsoft, Silverlight and InfoPath are the most recent examples. Once a product is ignored, the next step is often not an announcement of its demise, its just allowed to slowly fade away into obscurity. They go out with a whimper, not a bang. In my opinion, this is exactly what has been happening with PerformancePoint, but really, you don’t need to take my word for it. The section header from the roadmap blog post says it all.

“Reporting Services is our on-premises solution for BI report delivery”

Reporting Services… not PerformancePoint. I do realize that there is still one use case that can only be served by PerformancePoint, and that is Scorecards. Scorecards are rolled up KPIs, and no other product in the BI stack does this out of the box. However, with the release speed of the Power BI team, I can only conclude that scorecards will only be a matter of time.

At this point, I would strongly dissuade anyone from using PerformancePoint for any new projects. If you have an existing investment in PerformancePoint, you might want to start thinking about alternate methods of delivering that capability.

What’s Happening to Excel Services in SharePoint 2016
Saturday, September 26, 2015

Excel Services is dead – long live Excel Online.

With the release of the first public preview of SharePoint Online, and the release of this TechNet article, the news rapidly went out that Excel Services was dead. And while this is technically true, there is very little cause for concern. Unfortunately, this has led to quite a bit of misunderstanding, some of which can be cleared up by reading more of the article than the parts in bold, and others by digging around a bit. The bottom line here is, there is nothing to worry about.

The first point to make is that everything that you know and love about Excel Services lives on. While Microsoft IS removing Excel Services, what’s it’s doing is reducing both complexity and confusion by moving the relevant capabilities of Excel Services to the Office Online Server. When Excel Services was first introduced in SharePoint 2007, it allowed (among other things) for Excel workbooks to be rendered and interacted with in a browser without the need to use and Excel client. When Office Web App server was introduced in SharePoint 2010, it also allowed for workbook rendering in a browser, along with all of the other Office file formats. It also allowed browser editing of those files. What it didn’t do was allow for data connections and interactions with pivot tables, etc. For this, we still needed Excel Services. In fact, in SharePoint 2013, this required an additional configuration step of turning off XLSX file rendering for OWA.

Moving the necessary capabilities to Office Online Server (this is the name for Office Web App Server) is simply a matter of consolidation and clarification, and I welcome it. However, the new server name is confusing for people, and it leads to the second big misconception, which is that Microsoft is forcing us to use Excel in the cloud to make all this work. Let me make is clear – that is not the case.

When the deprecation article was published that talked about this move to the Office Online Server, the Office Online Server was not yet available in preview (it has been released since). This fact, combined with the name “Office Online Server” led many to the conclusion was that the Office Online services would be required for browser rendering of Office documents. The important word to note in the name is “server” as opposed to “services”. The server can in fact be installed in your domain, and in fact, must be for those data connections and interactivity capabilities. I’m not sure that it’s even possible to bind an on-prem SharePoint farm to Office Online Services, but I could see that as being an interesting scenario

While this fact was not always clear, Microsoft has in fact been doubling down lately on their on-prem BI strategy – Look no further than the BI investments in SQL Server 2016. SharePoint remains the primary deployment platform for these BI investments moving forward.

How to Set an Hourly Refresh Schedule with Power BI
Thursday, September 3, 2015

One of the advantages of Power BI “V2” over “V1” is the ability to refresh from data sources more frequently than once per day. Daily was the maximum in the first version of the service, and continues to be the maximum for the free version. If you have a Pro license however, refreshes can be performed more frequently. Unfortunately, it isn’t immediately obvious as to how this is done.

If we navigate to one of our datasets in Power BI, and select “Schedule Refresh” we’re presented with the user interface for setting this up (assuming that it’s a refreshable source). First we turn on scheduled refresh, and then next, we select our frequency.

Wait – there’s no “hourly” option in the dropdown. Didn’t I just say that it can be more frequent than daily? It can. You just need to be more explicit about it. The way to enable refreshes more granularly than daily is by explicitly adding the times for the refresh using the “Add another time” link.

Therefore, in order to have true hourly refresh, you will need to add 24 refresh times for the data source. That’s not all that appealing, is it? Unfortunately, 8 is all you’re allowed. I can only assume that this was by design in order to dissuade people from overtaxing the system when it isn’t required. You do get refreshes more frequently than daily, but hourly isn’t an option either.

Therefore, the minimum amount of data latency that can be achieved by using refresh with Power BI is, as of this writing 24/8 = 3 hours. Of course, this does not apply to direct connected sources.

Working with Excel Files in Power BI V2
Tuesday, August 11, 2015

In the beginning, Excel was at the center of Power BI. In the “V1” version of Power BI, the entire product was all about enabling analytics in the cloud, and Excel/Excel services was the delivery vehicle for those analytics. The authoring tools were all Excel add-ins, and the service revolved around updating data models in Excel, and allowing you to work with data models greater than 10 MB. Power BI “V2” changed that focus completely, completely removing that dependency on Office 365 and Excel. However, all of the Excel goodness that was within the “V1” product is still there, it just may have moved around a bit, and it works a little differently now.

In the early days of Power BI V1, I wrote up a post about the limitations of the product, specifically the file size limitations. Reviewing this article recently, I was actually struck by how little has changed. The maximum data model size remains 250 MB. In fact, 250 MB is the maximum size of any data model in Power BI, whether or not it originated in Excel. What has changed, rather drastically is the means of enabling this 250 MB limit for an Excel file, which we’ll get to below.

Let’s have a look at some of the major differences between Power BI “V2” and Power BI “V1” as it pertains to Excel.

More than just pretty face

In V1, Excel was almost always used as a means of presenting data. It was possible to use Excel as a data source, but the presentation of that data would inevitably be through another Excel file.

In V2, Excel is far more commonly used as a data source. The Power BI Designer, or the web interface can connect to Excel files, and then import the data into data models stored in the service. Native Power BI visualizations are then used for data presentation. These Excel files can be local, in OneDrive, or in OneDrive for Business. If the files are in OneDrive, or OneDrive for Business, the data models can be automatically refreshed when the source workbooks change. However, we can continue to work with Excel as a presentation mechanism.

If the Excel file is stored in OneDrive for Business (as opposed to OneDrive), you can connect the Power BI interface to the workbook in place. Once connected, the workbook can be viewed and interacted with through Excel Services right within the Power BI interface. To do this, from the dashboard, select “Get Data”, choose Files, select the OneDrive for Business option, select the desired workbook, and finally, select the “Connect” button”. You will then be presented with two options.

Importing data from Excel vs connecting to a workbook in place

Note that these two options will ONLY appear if the repository for the workbook is OneDrive for Business. Selecting “Import” will import the data contained in the file into a service based data model. This is the operation that will occur for all other repositories, and it uses Excel as a data source. Selecting “Connect” however connects to the workbook in place, and it adds the workbook as a report to the Power BI user interface.

Excel Workbook in the Power BI section

The workbook appears in the Reports section with a small Excel icon beside it. To view the workbook in Excel Services, click on the ellipsis to the right of the name, and select View. The workbook should load in a new window in full fidelity, and allow interaction.

No artifacts are created in the Datasets or Dashboards sections, the workbook is a self-contained unit. This is important because personal sharing can only be done through Dashboards. Therefore, Excel Services based reports can only be shared through Office 365 Groups.

Office 365 Groups

One of the biggest changes that Power BI “V1” users will notice (and need to deal with) is the fact that in order to work with Power BI in the same manner, Excel workbooks MUST be stored in a OneDrive for Business repository. With “V1”, a workbook could be stored within any SharePoint Online repository, and enabled for use with Power BI. Therefore, all “V1” users will need to move these workbooks into OneDrive repositories before the “V1” service is deprecate (Dec 31, 2015).

Every Office 365 user gets a OneDrive repository by default, and these repositories work just fine for personal use, but most current users that are using SharePoint Online will have workbooks in shared libraries in a collaboration environment. Office 365 Groups also each have their own OneDrive for Business repository, and all group members have access to that repository. Power BI V2 fully supports Groups, so this is the logical place to store all of the “V1” workbooks for collaboration purposes.

Connecting to a workbook in a Groups OneDrive is identical to the process above, but first, you need to navigate to the Group’s context in the Power BI UI. This is done by clicking on the My Workspace button (and not entirely obvious).

Groups Selector

Simply select the group and you will be working in that Group’s context.

Increased File Size

One of the biggest benefits of the original Power BI “V1” was the ability to work with workbooks that contained data models larger than 10 MB. The way that this was done was by “enabling” the workbook for Power BI as I outlined in this article. The enablement mechanism no longer exists, but the benefits are still there. The process of connecting a workbook outlined above intrinsically enables the workbook for Power BI and increases the maximum model size from 10 MB to 250 MB. In addition, the workbook can also be opened and interacted with directly from OneDrive. It is also possible to share that workbook with others in your organization, but in order to open those large workbooks in a browser, those users will need a Power BI licence.

Workbook Refresh

The ability to refresh workbooks stored in the cloud from data stored on-premises was, at the time of its introduction, the most important feature of Power BI. Each workbook would be enabled for refresh through an administrative interface, and if the data source matched a registered data source, the service would call an on-premises Data Management Gateway, which would facilitate the refresh of the workbook. This worked, but was somewhat difficult to get set up. Power BI “V2” has simplified the process tremendously. This does however mean that the process has changed.

Most refreshes are performed on a Dataset, but as mentioned above, an Excel report is self-contained, so the refresh options are available from the report itself. Simply click on the ellipsis to the right of the report and select “Schedule Refresh”.

Report refresh options

The first time this is done, you will need to enter the Data Source credentials. Once entered these will be used for subsequent refreshes. This will also need to be completed before the report can be refreshed on demand. You can also schedule the refresh time here.

If the data source is a supported cloud source, no further steps will be necessary. If it is on-premises, then it is necessary to install the Power BI Personal Gateway. This does not require administrative permissions to run (although it’s best if you do), nor does it require any special permission on the service side. The Personal Gateway is meant to be just that – personal and easy to use.

Hopefully this covers most of the major differences of working with Excel workbooks in V2 of the service. It looks like a big change, but most things are still possible, and some things significantly enhanced.