Power BI Licencing Demystified

Well, that’s a pretty ambitious title.

Power BI is currently an add-on to Office 365 and requires SharePoint Online in order to work. In January 2015, a new version of the Power BI Service was announced that removes the dependency on SharePoint Online, but will continue to leverage it if it is available. At the same time, a complete overhaul to the licensing model was announced. The licensing changes were widely welcomed, but they do raise a number of questions as to what license will be required when. The new version of the dashboard will be launched in the second half of 2015, so for now, we are still dealing with the original licencing model. Update – Power BI for Office 365 pricing has been updated to reflect the new model pricing and is available now (May 2015)

Power BI v1 Licensing

Power BI v1 is an add-on to SharePoint Online in Office 365 that among other things, adds the ability for Excel Online to work with data models larger than 30 MB (originally this limit was 10 MB) up to 250 MB, and to be able to automatically update data models stored in Excel from cloud based data sources, or from on-premises data through the Data Management Gateway. In order to take advantage of this capability, the end user needs a Power BI license, and this license carried a cost of approximately $20/user/month. I have seen it reported in many places that the cost of Power BI was $40/user/month. Indeed, there was a Power BI SKU that cost approximately this much (it’s referred to as Standalone), but this SKU also included a license for SharePoint Online, so really, the licenses were one and the same. You either have a license for Power BI or you don’t. Details of the Power BI for Office 365 are here, and they have already been updated to reflect the new pricing.

If you found yourself in an organization that had some users with licenses, and some without, you may have discovered some interesting behaviour. The Power BI service always leverages workbooks stored in SharePoint document libraries. These workbooks are available to all Office 365 users, whether or not they have a license. Users without a license can’t use the mobile client, Power Q&A, the gallery view or schedule data refreshes, but they certainly can open the workbook and interact with it. Well, they can until they hit the data size limit. Beyond 30 MB, the unlicensed users will receive a message indicating that their license is insufficient to view that file in a browser. However they can always download a copy and work with it that way.

This is an important distinction to note, because in this scenario, a licensed user can Power BI enable a workbook and schedule a daily data refresh. Once that data is refreshed, the unlicensed user gains the benefit from the refresh, and can interact with the workbook in a browser if it is smaller than 30 MB, or in the Excel client if it is larger.

Power BI v2 Licensing

When Power BI v2 (or Power BI Dashboards) was announced in January 2015, a new freemium pricing model was introduced. Power BI was available in a standalone fashion (no longer shackled to SharePoint Online), and could be had for either free, or for $9.99/user/month for the Pro edition. The detail and differences for the two editions can be found here. In addition, because Power BI will also continue to work with SharePoint Online, there will also be a SKU for the “Standalone” version at $17.99/user/month. I find the term “standalone” to be highly confusing here because this is in fact a license that contains a SharePoint Online licence – pretty much the opposite of standalone, but I digress. The comparisons leave a number of unanswered questions, which I hope to answer here.

One of the new concepts introduced with this new model is the Data capacity limit. This limit bears explanation. It is a per-user limit and it is cumulative. Free users are allocated 1 GB and Pro users are allocated 10 GB. Previously, the only limit was per-model (file by file), and that limit was 250 MB, and there was no total capacity limit per user. This is a significant difference.

Another thing worth pointing out here is that the 250 MB model size limit still exists. As with the Office 365 service, no single model can be larger than 250 MB.

What do you do if your model is larger than 250 MB? This new version of Power BI will allow connections to on-premises data. At the moment, on-premises connections are restricted to SSAS tabular models only through the SSAS connector, but more are coming. On-premises data connections don’t count toward any of the capacity limits. However, on-prem connections will require a Pro edition license.

The per-user capacity limits are cumulative, which is simple enough to understand for one given user. A user with a free license could have 4 x 250 MB models and reach their limit. A Pro user would need to have 40 of the same model to reach their limit. However, what happens when a user shares a dashboard with another user? Since it is really just the connection that is being shared, and the models are still being created per-user, the consuming user will need to utilize their own storage, and therefore it will be counted against their limit. If I share my 250 MB model with you, it will count against your total capacity limit.

What happens when a Pro user shares content with a free user? There are two possible outcomes – it will either work, or not. If the data model utilizes any of the Pro only features, the free user will not be able to consume it. For example, if the workbook has been scheduled to receive updates more than once per day, receives data from on-premises sources (SSAS Connector, Data Management Gateway) or utilizes any Pro only features, the consuming user will not be able to access it.

There are still a few unanswered questions with this new model, and as they are addressed, I’ll try to keep this post up to date.

Ignite 2015 Impressions

I don’t normally do conference summaries, but Ignite was just so big, and there was so much information that I felt the need to record my thoughts around it, and decided to share. Ignite was very much cross product, which is in line with where Microsoft seems to be headed – a focus on the function, not the tooling. With around 24,000 people in attendance, the conference, and the logistical issues that it imposed was too big for my taste, but the amount of information was excellent, and I imagine that I’ll be digesting it for some time to come. For now, here’s how I interpreted it all.

Azure and Office 365

Cloud services are killing it.

Between Azure’s Platform as a Service (PaaS) and Infrastructiure as a Service (IaaS) and Office 365’s Software as a Service (SaaS), Azure Active Directory is already sporting over 450 million active users. Azure Active Directory is what is used by Office 365, and the accounts within are otherwise known as Organizational Accounts. It’s an important metric because I believe that the Microsoft strategy is to own identity online. It makes sense when you look at what they seem to be doing.

For years, they absolutely dominated operating systems. Nothing to this day has ever really touched them on the desktop, but Apple changed the base with mobile, and developers flocked there. Google tried to do the same thing to Apple, and has been quite successful, but not fully so. While Android is in the majority in the mobile space, iOS is still quite strong, and shows no signs of diminishing. Windows isn’t really a factor in mobile, but still dominates the desktop which remains significant (about 300 million units/year), and is a factor on tablets. Microsoft got flanked by Apple and Android, and is holding the fort, but not conquering any new territory.

Microsoft now seems to be focusing on cloud services, and they don’t care what platform is being used to consume them. I think that at the core of this strategy is cloud identity – whether it is consumer (Microsoft Account) or enterprise (Azure Active Directory). With this identity strategy, Microsoft is attempting to again change the base – to outflank both Apple and Google and make the operating system almost irrelevant. Every app they’re putting out now is usually for iOS first, then Android, then Windows Phone. The new Universal app platform likely means that they will come out for Windows (desktop, phone, whatever) at initial launch with iOS, but the bottom line is that an awful lot of effort is going into supporting all platforms all the time. If the apps work well across platforms, then the choice of operating system simply becomes one of personal preference, not of features. It gets marginalized, and Microsoft owns the back end service. That’s why I think that so much effort has gone into this strategy.

Another thing that I sensed at the show was that in the past, all of the talk around identity and federation (ADFS) was about bringing your on-premises identities into the cloud to support a few new services. Now, there seems to have been a real shift, and the reason for adopting ADFS is to bring the Azure Active Directory identities back down on-premises to where legacy applications can use them. It’s a subtle shift, but discernable.

One of the more interesting product introduced into Azure recently is Logic Apps. As far as I can tell, Logic Apps are the cloud manifestation of BizTalk, which is an excellent product with a steep learning curve. Logic apps remove the learning curve and allow you to quickly connect and flow data through multiple systems. The session on logic apps can be seen here:

SharePoint 2016

In the past, SharePoint announcements would warrant their own post, but now SharePoint is probably best seen as part of a greater whole. Details on SharePoint 2016 details were first announced at Ignite, and I feel that the most informative session was Bill Baer’s on Wednesday morning where he outlined the major architectural changes:

Not surprisingly, this release will be very much about hybrid Sharepoint/Office 365 scenarios. Some of the notable items from the talk are:

  • SharePoint Server 2016 Will require 64 bit Windows Server 2012 or Widows Server 10, and SQL Server 2014 SP1 as a minimum
  • Standalone installations are no longer supported. It will be possible to install SharePoint and SQL Server on the same machine, but full SQL Server will be required, and SQL Express will no longer be supported. This obviously raises questions about whether or not there will be a free SharePoint Foundation SKU with the next release.
  • PerformancePoint will in fact be included with SharePoint 2016. I doubt very much that there will be any investments in it at all, but it will at least be there. I’d view this as legacy support.
  • SharePoint 2016 will support SAML claims as a first class citizen. That means that it will be possible to login with Azure Active Directory credentials, and is an example of bringing cloud identities on prem. However, don’t trash that domain controller just yet, I’m sure that service accounts will still need to be NTLM – SQL Server needs it.
  • There will be a new Roles Based installation. It will be much simpler to install and maintain servers with specific roles such as web front end, search, etc. BI will be one of the roles.
  • There will be new boundaries. Content databases up to Terabyte sizes, 10 GB file size limit, list thresholds of much greater than 5000 items (although how much greater was not specified)
  • No more FIM. The user profile engine that we’ve all grown to….. deal with from SharePoint 2010 and 2013 is no longer embedded. The full Forefront Information Manager can be used, but the default profile import mechanism will be the good ol’ User import from SharePoint 2007.
  • Durable resource based links. Every object in SharePoint will receive its own resource based URL. That means that it can be moved around in the farm, and reference URLs will still work. This is like permalinks in WordPress.
  • While not final, a preview was shown of some operational reporting. This is primarily “speeds and feeds” type information that would interest a farm administrator, although simple usage reporting could be seen.
  • Integration with the Office Graph – see below section on Delve.

SQL Server 2016

The next release of SQL Server was announces at Ignite. Its chock full of new things, focused primarily at hybrid operation and analytics. One of the more interesting concepts in this version is the ability to “stretch” a database into the cloud. With this, you can take an on-premises database, and extend it into Azure SQL, specifying rules to determine which data goes where. Given that online storage is significantly cheaper than on-premises, this makes total sense, and they’ve figured out a way to make it work reliably. The overall SQL Server keynote can be found here:

I’m very interested in the analytics capabilities, and the session outlining the improvements to SQL Server BI is found here:

I found the following items particularly notable:

  • A comment was made during the BI session that Microsoft is “Super Committed” to SQL Server Reporting Services (SSRS). Hopefully this helps quell the naysayers. SSRS is receiving a major facelift in this version, bringing a modern design experience. In addition, the parameters pane has received a great deal of attention, adding, among other things, support for cascaded dropdowns.
  • Datazen is a visualization company based in Toronto that was recently acquired by Microsoft. There is a good demo of Datazen in the session, and I highly recommend watching it. It will be included with SQL Server 2016.
  • Datazen has KPIs. It also has “sub-KPIs”. I’m not sure about you, but that sounds a lot like a scorecard to me. This may sound the eventual (see the SharePoint section) death knell for PerformancePoint, given that that’s about all that it uniquely provides to the BI stack.
  • Tabular models in SSAS (and presumably PowerPivot) will support many-many relationships and a host of other new features.
  • Tabular models in SSAS and PowerPivot will have time intelligence built in. No longer will separate time intelligence tables be required. It’s an open question however as to how extensible they will be and when.
  • SharePoint will allow browser editing on PowerPivot embedded workbooks. Currently, you need to launch Excel to edit a PowerPivot embedded workbook.

Office 365 Groups

I attended the roadmap on Office 365 Groups:

(video unavailable as of posting – should be shortly)

During this session, the light really went on for me. Groups was (were? Not sure about the grammar on this…it’s a name) introduced last year and appeared to be a glorified distribution list with Sharepoint artifacts. However, its about to become the center of the Office 365 collaborative experience. It ties together Azure Active Directory objects, a SharePoint site collection, One Note, Skype, and OneDrive into a single cohesive, non-customizable experience. It currently uses Exchange exclusively for social conversations, but full Yammer integration is promised. No date was given for the integration, but my guess is that the target is early 2016.

The current User interface is limited – too limited for my own use at the moment, but during the demonstration, a rather useful interface was shown that is coming soon. You can access groups presently through the Outlook web client in Office 365. I’m running Office 2016 preview on my laptop, and there is a very nice interface contained there. There was chatter, particularly in the Yammer community about confusion as to what tool should be used when, but I think that the coming deep integration of Yammer into Groups will render this point moot’

The next UI, demonstrated in the above session looks really good, and offers a lot of benefits. There is also a mobile app coming very shortly for, you guessed it, iOS and Windows Universal, then Android.

One unanswered question from the show is whether Groups would be available on-premises.

Power BI

Power BI content was sort of sprinkled throughout the conference, without specific focus. There was a session on the new DAX features available in Power BI Designer that is worth a watch from a modeling perspective:

One talk that really impressed me was by Lukasz Pawlowski and Josh Caplan entitled Power BI for Developers:

They cover content packs are mentioned, real time analytics, and an in depth analysis of the “how old” app that went viral during Build.

It was also announced in the SQL BI session that SSRS will in fact be included in Power BI shortly, although little detail was provided. Finally, for development, the best place to get started is http://dev.powerbi.com.

It should also be noted that Power BI was at the center of almost any analytics discussion during the conference. This is by no means a little side project.

Delve/Office Graph

Delve is a newer product in Office 365 that provides insights around what content is relevant in an organization, and how people interact with it. It’s available directly from the app launcher in Office 365, and recently, user profiles have moved to the Delve application. It’s powered by the Office Graph, which in essence an advanced index that contains content from Exchange and SharePoint, and will very shortly, be extensible for multiple content types. The roadmap session for Delve/Graph is available here:

During the session, it was stated that “Delve is the evolution of Enterprise Search”. Given that all of the work on Delve and the Graph is coming from Oslo and the former team from FAST search, this just makes sense. One of the major announcements around SharePoint 2016 was that SharePoint 2016 content can be crawled by the Office Graph to provide both search results and Delve results in Office Graph. The reverse will also be true in that the on-premises crawler will be able to index Office 365 content for search results, but Delve and the Graph will remain in Office 365. The surprise here was that later this year, it will be possible to do the same thing with Sharepoint 2013 through a coming enhancement.

Much of this Graph goodness can also now be accessed through the new Office 365 Universal API:


tyGraph is our product that provides advanced analytics for Yammer. It had something of a coming out party at Ignite, and while we didn’t have a booth or any launch sessions, we were fortunate enough to have several folks, customers and thought leaders present talks that at least in part featured tyGraph. If you’re interested in analytics for your Yammer network, I recommend that you watch some or all of these sessions:

Enterprise Social, from “Ooh, Shiny” to Business Success – Melanie Hohertz, Cargill

The Microsoft Enterprise Social Journey: How We Did It – Chris Slemp, Microsoft

Gain Organizational Insights with Yammer Data Mining and Analytics – Steve Nguyen, Microsoft and Tammy Young Heck, EY

Yammer Mining: Dig in and “Listen” to What Your Big *Social* Data Is Saying – Richard diZerega, Microsoft

PerformancePoint Lives!

During the Microsoft Ignite conference in Chicago, Bill Baer delivered the first public details on the inner workings of SharePoint 2016. He was discussing how non-Office 365 services were implemented in SharePoint 2016, and made the following statement:

For capabilities such as PerformancePoint Services, we have brought those services forward into SharePoint 2016 by back porting them into the product itself.”

This is quite significant as it represents the first time that any statement has been made about PerformancePoint by someone at Microsoft in quite some time. There has been a fair bit of speculation as to whether the product would even be included in SharePoint 2016, for example here, here, and here. This statement should clear up any confusion for those heavily invested in PerformancePoint as to whether they will be supported into the new release. You can see it for yourself below, and if you can, I recommend watching the entire session – Bill did a great job. The PerformancePoint statement occurs at about 9:50.

However, just because it will be there, doesn’t mean that it has a bright shiny future. I suspect that it is being included for compatibility reasons only so that those with PerformancePoint can move forward to SharePoint 2016. In terms of new features, I believe that PerformancePoint, much like InfoPath is a dead end. Time will of course tell.

SQL Server Data Tools, BIDS, Visual Studio – What Do You Need?

The Business Intelligence design components of SQL Server have an identity crisis.

What I’m talking about are the tools that are used by designers to create BI objects in the SQL Server Business Intelligence stack, specifically Analysis Services (SSIS) OLAP cubes and tabular models, Integration Services (SSIS) ETL packages, and Reporting Services (SSRS) reports. These tools have always been bundled into a single product as part of the SQL Server distribution. The original incarnation of the tools was called Business Intelligence Development Studio, or just BIDS. It was originally introduced with SQL Server 2005, and was included on the SQL distribution media as an optional install component.

BIDS was a distribution of the Visual Studio shell, and a set of bundled project types for creating cubes, SSIS packages and SSRS reports. The original version was based on Visual Studio 2005, and subsequent releases of SQL Server stayed in step with more recent versions of Visual Studio. The projects were tied tightly to the Visual studio versions. If, for example you had Visual Studio 2010 already set up, installing BIDS would still install a different Visual Studio to support the projects. BIDS maintained its own identity. Installing BIDS is straightforward, you simply run setup from the SQL Server media, and select “Business Intelligence Development Studio”.

When SQL Server 2012 was released, a change was introduced. In addition to the projects required for tabular models in SSAS being added to the tools, they also received a new name. Henceforth they were to be known as SQL Server Data Tools (SSDT). As expected, the requisite level of Visual Studio was incremented and SSDT was based on Visual Studio 2010. The installation experience didn’t change fundamentally, the installation option just took on the new name.

So far so good. Products get renamed all of the time. Simply substitute SSDT for BIDS, and everything is pretty much as it once was.

SQL Server 2014 is the latest SQL Server version, and it introduced another major change. Data tools is no longer available from the SQL Server installation media.

This is where it gets very confusing. It was always possible to download BIDS or SSDT directly from Microsoft. These products don’t require a license to deploy and use, so they were freely available. This is also true with SQL 2014, but now, a download is the only way to get the tools. That’s simple enough. However, if you search for SQL Server Data Tools and download the version for either Visual Studio 2012 or Visual Studio 2013, you won’t find the projects that you were looking for. You’ll instead find projects for deploying databases and DACPACs. So what’s going on here?

As it turns out, a separate Microsoft team put out a separate set of VS project templates in the SQL Server 2012 timeframe that were also called SQL Server Data tools. Apparently there are a finite quantity of names.

The product that we originally knew as BIDS, and then SSDT was renamed one again for the SQL Server 2014 wave of products. Well, only sort of renamed. It’s now called SQL Server Data Tools – BI. Apparently this was intended to avoid confusion…. SSDT-BI is available for either Visual Studio 2012 or Visual Studio 2013. If you don’t already have a Visual Studio installed, it will install a VS shell for you.

The summary of all this is to say that if you want to build BI projects for SQL Server, you’ll need to have the right tooling for your target server, and that tooling is as follows:

Server Toolset Name Location
SQL Server 2008 R2 and below Business Intelligence Development Studio On SQL Media
SQL Server 2012 and 2012 SP1 SQL Server Data Tools On SQL Media
SQL Server 2014 SQL Server Data Tools – BI Download for Visual Studio 2012
Download for Visual Studio 2013

The complete current Data Tools product set is laid out and can be downloaded from here. The Data Tools team blog is here.

I hope that this helps to clear up some confusion. I can’t wait to see what they have in store for us in V.Next….

License Requirements for SQL Server Reporting Services in SharePoint Integrated Mode

As I am keen to point out every opportunity I get, when installing SQL Server Reporting Services in SharePoint Integrated mode, it is important to ensure that it is installed on a SharePoint server, and not on a SQL Server. It’s a bit counter-intuitive because the installation files are on the SQL Server media, and not the Sharepoint media. This causes confusion in a number of areas, but one question that I hear a lot is in the realm of licensing.

Technically, using any SQL Server component on any server requires a full SQL Server license for that component for each server. Running SQL Server Analysis Services and SQL Server Database on two different machines requires two different licenses. Seen that way, putting the SSRS Service on a SharePoint server would require a second license of SQL Server, which can be an expensive proposition. It also doesn’t make much sense, because it promotes bad design. Luckily, the SSRS service application is specifically exempted from additional licensing requirements. The following is taken directly from the Microsoft SQL Server 2012 SP1 license document. Section 2.5 states:

2.5    Running Instances of the Additional Software.

You may run or otherwise use any number of instances of additional software listed below in physical or virtual operating system environments on any number of devices. You may use additional software only with the server software directly, or indirectly through other additional software.

  • Business Intelligence Development Studio
  • Client Tools Backward Compatibility
  • Client Tools Connectivity
  • Client Tools SDK
  • Data Quality Client
  • Data Quality Services
  • Distributed Replay Client
  • Distributed Replay Controller
  • Management Tools – Basic
  • Management Tools – Complete
  • Reporting Services – SharePoint
  • Reporting Services Add-in for SharePoint Products
  • Master Data Services
  • Sync Framework
  • SQL Client Connectivity SDK
  • SQL Server 2012 Books Online


Notice that little bullet point “Reporting Services – SharePoint”? That’s the service application. Put simply, this means that in order to use SSRS in SharePoint Integrated mode, you’ll need to have at least one licensed SQL server, but you don’t need to run it on the SQL machine. In fact, according to the line “any number of instances” you can run it on as many SharePoint servers as you wish to take advantage of load balancing without incurring any additional licensing cost for SQL Server.