Skip to content

Tag: Office 365

OneDrive, TwoDrive, ThreeDrive

Published December 1, 2015

I’m calling it ThreeDrive now.

The much ballyhooed “Next Generation Sync Client for OneDrive for Business rolled out with the Windows 10 November update. You’d be excused for not noticing, because it looks pretty much the same as the old OneDrive consumer client. In fact, it IS the new OneDrive consumer client as well but it supports OneDrive for Business too. It’s not obvious that it supports OneDrive for Business because as of this writing, it requires a registry key entry. If you sign up for the OneDrive for Business preview, you’ll get the new sync client and the instructions, but for convenience, the key is:

[HKEY_CURRENT_USER\SOFTWARE\Microsoft\OneDrive] – “EnableAddAccounts”=dword:00000001

Presumably, at some point that registry key will be switched on for everyone by an update. Once it has been added, when you open the OneDrive settings, you will now see an option to add a business account.

Once added, your Office 365 OneDrive will be synchronized using the same (more robust) engine as the consumer client, you’ll be able to do selective sync of folders, etc. Once you add your business account, you’ll see two sync “clouds” in your system tray, one blue, and one white. White is your consumer OneDrive, and blue your OneDrive for Business.

I’m pretty sure that this brings us to TwoDrive. You’ll also get (at least) two entries in your Windows File explorer, one for Personal, and one for your corporate OneDrive. If you connect multiple Office 365 accounts, you’ll get multiple blue cloud icons, and multiple explorer entries.

I don’t know why the consumer client icon is blue, but it is what it is. To me, white would help with understanding. We do however have a single sync client! Well, not so fast. If I need to sync SharePoint libraries from either on-prem or Office 365, I will still need the older OneDrive for Business sync client, with all of the same limitations. This is also true for the OneDrives contained in an Office 365 group.

Once that’s installed, by syncing a library, you’ll get another blue cloud icon that is indistinguishable from the icons created by the new sync client, and you’ll get an entry in File Explorer for SharePoint.

Everything should be working at this point. However, although I have gotten my head around this, I find it pretty confusing, and I work in this environment for a living. I know that I’m not alone, I recently spent about an hour with my friend and fellow MVP Marc Anderson helping him get his head around it, so it’s certainly not simple.

I am quite happy to see the new sync client, and the harmonization that it brings. I also know that the need for the old OneDrive for Business sync client will go away as the new client gains the ability to sync with SharePoint libraries and Group based OneDrives. I personally use cloud based storage solutions from a number of vendors, and they all have strengths and weaknesses. OneDrive is still the best deal out there, and it’s also the best solution for corporate sharing. I am however concerned about the complexity. I can imagine the following future conversation with a customer.

Me: You should really look at OneDrive for Business for offline access to your content

Customer: Oh, I have OneDrive already. Didn’t Microsoft just limit its storage capacity?

M: No – they had only turned on unlimited storage for a small test group. They just decided not to move forward with it as earlier announced. Besides, that’s only for the consumer OneDrive, not OneDrive for Business.

C: So they’re not the same thing?

M: No – OneDrive is a consumer product, and you log into it with a Microsoft account. OneDrive for Business is a business product, and you get it with an Office 365 business account. You need a corporate account to use it. It gives each person that uses it 1 TB of storage.

C: So if it’s they’re different things, why are they both called OneDrive?

M: I know. Never mind.

C: OK, so how do I get them both working?

M: Well, you have the November update for Windows 10, right? All that you need to do is to go into your OneDrive settings, and add your business account.

C: Oh, so they use the same sync client?

M: Yes. That’s fairly new. There used to be an exclusive OneDrive for Business client, but you don’t need it now.

C: OK. (adds the business account) So how do I work with it?

M: You see those two cloud icons in your tray? The white one is your consumer account, and the blue one is your business account.

C: I thought that it was one sync client. Why are there two icons?

M: That’s so you can see the two different repositories. They are both driven by the same sync engine. It makes sense.

C: OK, cool. And how do I access my files?

M: Just open up File Explorer. The one that says OneDrive – Personal is your consumer account. The one that says OneDrive – your company name is your business OneDrive.

C: So the business one is my personal OneDrive in Office 365?

M: Yes. I know… the term “Personal” is a bit confusing, but it is what it is.

C: That’s fine. Why is personal cloud icon blue in File Explorer but not in the tray?

M: I have no idea

C: OK – so how do I sync my Office 365 Group OneDrives?

M: Oh. Remember when I mentioned the old sync client? You’ll need that to sync those. The new client doesn’t support them yet, but it will.

C: I thought the old one doesn’t work very well?

M: It’s not as good, but it’ll do for this purpose.

C: OK, how do I install that?

M: You have Office installed, so you already have it. Just open up the OneDrive in your browser, and click on the sync icon.

C: OK (does it). So how do I know it’s working?

M: Open up your tray. See that you have another blue cloud icon? That’s the older sync client.

C: How do I tell the difference between this and the other one?

M: Just hover over the icon. The one that’s just called OneDrive for Business is the older engine. The one that contains your company name is the new one.

C: Am I going to get another icon for every OneDrive that I sync?

M: No – in this case, they all use the same icon. If you hover over and select open the folder, you’ll see what’s syncing.

C: OK. So where do I find my files?

M: Open up File Explorer. Under your two OneDrives, you’ll see a new entry for SharePoint. Click on that, and you’ll see your content.

C: What’s SharePoint?

M: *sigh*

I really quite like OneDrive, and what it can do. It’s even more valuable to me when the people that I interact with use it too, and after walking through this explanation over the past few days, I can see a few barriers to entry. I’d love to see this whole thing simplified.

12 Comments

Sharing Power BI Content with Office 365 Groups

Published July 23, 2015

The Power BI sharing story got a lot clearer this week with the changes in the service that go along with General Availability. These changes included the integration with Office 365 groups, which will in my opinion, be the preferred way to share Power BI content with others.

If you’re unfamiliar with Office 365 Groups, what you need to know is that Groups is not a product per se, but really an integration mechanism that binds together multiple elements of Office 365, and as of now, Power BI. When a group is created, a number of things happen – a distribution list is created in Exchange, a Site Collection is created in SharePoint containing that Group’s OneDrive, and an Azure Active Directory group is created for membership in AAD. Now, a Power BI workspace is created for that group as well.

How Power BI works with groups

If you’ve been working with the Power BI preview already, you are familiar with the personal workspace. This is the workspace that you see when you first log into the Power BI service, and until now, the only workspace that was available. Within the personal workspace, you can create datasets, reports, and dashboards. Dashboards can be shared to the personal workspace of other people within the organization, but now you can also switch to the workspace of an Office 365 Group. To do so, click on the Workspace selector in Power BI. Initially, it will be labelled “My Workspace”.

You’ll then be able to select from any of your Office 365 groups. All groups that you are a member of should appear here automatically, you don’t need to register them. Once selected, you’ll be working within the context of that group. If it’s empty, you’ll be prompted to add data, and if not, you’ll be taken to a default dashboard. Everything that you do at this point will be done within the context of that group, and will not affect your personal workspace. In addition, everything that you do here will be visible to all members of the group that use Power BI. There is no need to “share” anything.

Sharing to the personal dashboard vs sharing via groups

Groups represent a fundamental change to sharing in Power BI. The Personal Workspace is just that, personal. It is possible to share dashboards from here with colleagues, but the assumption is that you are the only person that may make changes. A Groups workspace turns that on its head, and assumes that everything is shared by default.

When you share a dashboard from the Personal Workspace, recipients can view the dashboard, and interact with the underlying reports. There is (currently) no mechanism to allow those recipients to make changes to those reports and dashboards. However, when working in the Groups workspace, any member of the group can make changes. Any changes made are also immediately visible to all other members of the group.

Update – 2015/09/26 – Groups can now share dashboards outwardly in the same manner as personal workspaces. Thanks Ajay for the comment.

Personal OneDrive vs Group One Drive

In its original incarnation, Power BI worked with Excel files stored in SharePoint Online document libraries, including OneDrive libraries. With this version, Power BI will refresh and render Excel workbooks with full fidelity as well, but now they MUST be stored in a OneDrive library. Each user receives a single OneDrive library through Office 365, and they may also have a OneDrive personal library. In addition, each group also has a OneDrive library, and these can be used as well. The way to use them is to connect to the workbook from within the Group’s workspace.

In order to connect to an Excel Workbook from the Personal Workspace, you click on “Get Data”, click the “Get” button in the Files section, and select from Local File, OneDrive – Business, or OneDrive Personal.

Selecting from Local File or OneDrive personal will import the contents of a workbook into a Power BI dataset. That dataset will be refreshable directly from OneDrive, or through the Personal Gateway if Local File was chosen. However, selecting OneDrive – Business will allow you to select your file, then give a further two options.

“Import” is the same process as OneDrive – personal, or local file – the date is imported from the workbook into the dataset. However “Connect” establishes a report connection between the Power BI service and the OneDrive file, allowing it to be rendered in the Power BI site through Excel Services.

Once this is done, the workbook will appear in the Reports section in Power BI with a small Excel icon beside it. Unlike other sources, no dataset or dashboard are created because the report is a self-contained entity.

The experience is quite similar within a Groups workspace, with one important difference – neither OneDrive-Personal nor OneDrive – Business are options.

Instead, we are presented with the Group’s OneDrive which makes sense given that we’re in the Group workspace. The group OneDrive is backed by Office 365 which means that it functions the same way as OneDrive – Business. Excel workbooks can either be imported or connected to.

Can we use Power BI with Team Sites like before?

As mentioned above, the original Power BI service rendered workbooks from any SharePoint Online document library. The new service works with OneDrive libraries only. This means that any workbooks that are currently stored in SharePoint Online and use Power BI features will need to be moved into Group based OneDrive, or personal OneDrive in order to be able to continue to take advantage of Power BI features. In other words, Groups are REALLY important to Power BI. The original Power BI for Office 365 service will continue to be available, but will shut down on December 31, 2015.

Sharing Externally

The V1 service allowed for the external sharing of workbooks through the external sharing facilities of SharePoint. However, due to licensing restrictions, the experience wasn’t optimal. If the data model was too large, the external user would not be able to open the workbook in a browser, and would instead be required to download it in its entirety in order to open it. This was because the external user would most likely not have a Power BI license. The V2 service allows users to share dashboards from their Personal Workspaces, and to collaborate fully in Group Workspaces, but there is currently no way to share Power BI content externally, or anonymously. This has been identified as a priority, but is not available yet.

I have no specific information about how this might be done, so I am free to speculate. I suspect that the Groups mechanism will be leveraged to accomplish external content sharing. At the moment, Office 365 groups do not allow for external members, but if they did, ths would solve the external sharing problem. I’m betting that this will be the approach.

Microsoft is betting a great deal on Office 365 groups, and Power BI is one of the first services to demonstrate this deep integration. If you’re already or will be invested in Power BI, I would strongly suggest that you get familiar with them.

5 Comments

The New Power BI Personal Gateway – Do I Need It?

Published July 14, 2015

Last week, Microsoft released the Power BI Personal Gateway. The Personal Gateway lets you keep dashboards created in the new Power BI Dashboard service updated with data from your on-premises data sources. This is important – nobody wants to manually refresh data all of the time. However, the service already updates many data sources updated automatically – when is this tool necessary? Also, there is already a refresh tool available for Power BI called the Data Management Gateway – what’s the difference between these two tools, and when would I use one versus the other? This post is an attempt to answer these and a few other questions.

To set the stage, we need to distinguish between the original Power BI service (V1) and the Power BI service released on July 24 2015 (V2 or Power BI Dashboards). The V1 service runs (or ran, depending on when you read this) as an add-on to Office 365. Among other things, this service allows Excel files with embedded Power Pivot data models to be used from Office 365. The Data Management Gateway can be connected to the service to keep those workbooks refreshed with data on a periodic basis. The new V2 version of the service removes the dependency on Office 365 and Excel. It allows users to connect directly to their data, and to use Power Query, Power View, and (essentially) PowerPivot to transform it, visualize it, and create dashboards from it. In this new model. Office 365 is simply a repository for Excel files, which become a source for both data and reports, depending on how they are connected.

In addition to refresh capabilities, the new Power BI (V2) service supports direct querying of on premises and cloud data sources. This is significantly different than data refresh. In a live connection scenario, dashboard interactions are sent back to the data sources in real time where they are executed, and the visualizations are updated through the service in real time accordingly. In a refresh scenario, a data model that exists in the Power BI service is updated from a source on a periodic basis. This refresh has been the job of the Data Management Gateway, and is also the job of the new Personal Gateway.

Architecturally, the two services can be viewed as follows:

With this in mind, let’s answer a few anticipated questions

Will I need the Power BI Personal Gateway to do live query of on premises data?

No. The Personal Gateway, like the DMG, performs a data refresh of a model that is stored within the Power BI service. Live queries are executed against on-premises data models, so in this scenario, the Personal Gateway plays no part.

If I have the DMG, do I need the Personal Gateway?

The answer to this is that it depends. Although related, the two products do different things. The DMG is responsible for keeping the data models contained within Excel workbooks and stored in SharePoint online up to date. The refresh process in this case is the equivalent of opening the Excel workbook, selecting the Refresh All Connections button, saving it back and allowing he service to update the model stored in the service. The Personal Gateway has no workbook to update, it only updates the service based model. Therefore, if you do need to keep workbooks refreshed in Office 365, you will need to use the DMG. However, if instead you upload your workbook to the new “V2” service, you will need to use the new Personal Gateway.

Can I install the Personal Gateway and the DMG on the same machine?

No. The Personal Gateway is really an evolution of the original DMG and uses the same underlying code base. The two are incompatible and cannot be installed on the same machine. An attempt to do so will result in the following error:

If I have the SSAS Connector, do I still need the Personal Gateway to refresh data?

Yes, The SSAS Connector is a service that is installed on-premises to allow the Power BI service to perform live queries on SSAS servers. In order to keep data in a Power BI model up to date from an on-premises data source, the Personal Gateway is necessary. However, it is not currently possible to install both the Personal Gateway and the SSAS Connector on the same machine. In fact, if you attempt to do so, you will receive precisely the same error as above. The SSAS Connector is another variant of the original DMG.

Do I need to use Power BI Designer to create a refreshable model in Power BI “V2”?

No. While Power BI designer is one tool for doing this, it is not the only one. Models refreshable from on-premises data can also be created by using the Power BI user interface and connecting to Excel workbooks.

Will any data model created in Excel or Power BI Designer work with the Personal Gateway?

(note – this answer has been updated from it’s original to correct some inaccuracies. Thanks to Derek Rickard for pointing this out)

No. In order for a model to be refreshable by the Personal Gateway, it must have been created from a refreshable data source. This is a similar to the DMG which could also refresh some direct on premises data sources, but the difference is that Power Query was required to refresh anything but SQL Server or Oracle data sources. In Excel, a model can be created using PowerPivot, Power Query, or by the selection of appropriate options when importing data.

The following data sources are currently supported.

  • SQL Server
  • Oracle
  • Teradata
  • IBM DB2
  • PostgreSQL
  • Sybase
  • MySQL
  • SharePoint List
  • File (CSV, XML, Text, Excel, Access)
  • SQL Server Analysis Services Tabular models
  • Folder
  • Custom SQL/native SQL

Do I need the Personal Gateway to refresh data sources from the cloud?

No. As with Power BI “V1”, cloud based data sources can be refreshed directly from the service, with no need for a gateway. However, if your model contains data sources from both on premises and the cloud, a gateway will obviously be required. Also, as mentioned above, Power Query must have been used to acquire the data. Supported cloud data sources are:

  • Azure SQL Database
  • Azure Blob Storage
  • Azure Table Storage
  • Azure HDInsight
  • Azure Marketplace
  • Dynamics CRM Online
  • Facebook
  • Google Analytics
  • Salesforce Objects/Reports
  • OData feeds
  • Web (HTML & Web APIs)

Do I need to be an Administrator to run the Personal Gateway?

No. This is a major departure from the DMG. The DMG installed as a service, which requires administrator level permissions to do. In addition, Configuration of data sources at the service level required special permissions. The DMG was designed to be run by administrators. The new personal BI “V2” is designed to meet the needs of both individual users, and enterprises, and correspondingly, the Personal Gateway can be run by anyone. I suppose that the word “personal” in the name should be a bit of a hint.

At install time, the system is interrogated to determine the current user’s permissions. If the permissions are sufficient, the Personal Gateway installs itself as a service, allowing full unattended operation. If permissions are insufficient, the gateway installs itself as an application. When installed in this manner, the application must be running in order for any refreshes to occur. Obviously, the user must also be logged in.

 

I’ll add more Q&A to this post as needed over the coming weeks. The coming release of the new Power BI service promises to be exciting. For more details, check out the Personal Gateway release announcement.

8 Comments

Ignite 2015 Impressions

Published May 12, 2015

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

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

4 Comments

The New Power BI – Now With Enterprise!

Published January 28, 2015

Yesterday Microsoft announced the next step in the evolution of Power BI. It’s getting quite a bit of attention, and rightly so for its aim of bringing Business Intelligence closer to users. Democratizing BI has always proved a challenge – it’s the realm of the gurus in the white coats that hold the keys to the data. Microsoft is aiming to accomplish this democratization through a combination of user focus, and as of yesterday, a drastic change in its pricing model. Power BI just went from about $40 per user per month, to free, or $9.99/user/month for advanced capabilities. That’s quite a drop, and arguably the biggest announcement from yesterday – it will have a massive impact. The detailed price breakdown can be found here.

However, all of the focus around personal BI is, in my opinion, missing a key component. Power BI and its components have always focused squarely on both personal and team BI solutions. That is to say the ability for a power user to model data, visualize it quickly and easily and to share it out with fellow team members. While that capability is certainly retained in the new Power BI, this new version contains the first appearance of enterprise grade BI in the cloud for Microsoft.

To fully understand this, it’s necessary to touch on the Microsoft BI stack as it stands today.

Microsoft BI On Premises

The On-Premises BI story from Microsoft may be confusing, and occasionally difficult to understand, but it is very powerful, and relatively complete. In a nutshell, the story is good from a personal, team and enterprise perspective.

On the enterprise side, there are products from both the SQL Server team, and the Office team. Data warehousing is served by SQL Server and ETL duties fall to SQL Server Integration Services (SSIS). Multidimensional analysis storage is served by SQL Server Analysis Services in both OLAP and Tabular modes, and Reporting is performed by SQL Server Reporting Services (SSRS). The SQL product line doesn’t have much on the client side for analysis apart from SSRS, but this slack is taken up by the analysis tools available in Excel, and through Performance Point services in SharePoint.

Indeed, SharePoint also provides a platform for SSRS via SSRS SharePoint mode, and for Excel based analytical workbooks connected to SQL Server and to SSAS through Excel Services.

On the personal BI side, that role has traditionally fallen to Excel. The pitfalls of importing data into Excel workbooks for analysis are well documented and don’t need to be discussed here, but the bulk of those issues were addressed with the introduction of PowerPivot several years ago. PowerPivot allows for massive amounts of data to be cached within the Excel file for analysis without any data integrity concerns. The addition in recent years of  analytic visuals (Power View, Power Map) and ETL capabilities (Power Query) have further rounded out the offering.

Taking that Excel workbook and sharing it brings us into the realm of Team BI. This is to say that the analyses are relatively modest in size, and of interest to a targeted group. These models may not require the rigour or reliability associated with enterprise BI models. Once again, the technology involved here is SharePoint. A user can take a workbook with an embedded PowerPivot model, share it through a SharePoint library, and other users can interact with that embedded model using only a browser. This capability requires PowerPivot for SharePoint, which is really a specialized version of SSAS, along with a SharePoint service application.

One thing to note about these seemingly disparate approaches is that a power user can build a Power Pivot data model with Excel, share it to a team via SharePoint, and when it requires sufficient rigour or management, it can be “upgraded” into SSAS in tabular mode. This common model approach is powerful, and is key to understanding Microsoft’s entire BI strategy. You can also see here that SharePoint straddles the two worlds of team and enterprise BI.

Moving to the cloud

The BI workload is one of the last Microsoft workloads to move to the cloud, and with good reason. Massive amounts of data present problems of scale, and security or data sovereignty concerns tend to keep data on premises. However, there is a very real need to provide BI to users outside of the firewall.

SharePoint is the hub of BI on prem, so it’s logical to assume that with SharePoint Online, it could continue to perform that function in the cloud. The big catch here is that on-prem, SharePoint is simply the display platform. In the enterprise scenario, users connect through SharePoint to the back end servers. This isn’t an option in the cloud, so enterprise BI was left off the table.

With the personal and team BI scenarios, data is cached in a Power Pivot data model, which could be supported in the cloud. When Office 365 moved to the SharePoint 2013 code base for SharePoint online, rudimentary support for embedded Power Pivot models was indeed added. Essentially PowerPivot for SharePoint “light” was added. I call it light for two major reasons. Firstly, data models could be no larger than 10 MB. Secondly, there was no way to update the data contained within the Power Pivot cache, outside of re-uploading the Excel workbook. This is still true without a Power BI license. The inability to refresh the data renders team BI almost useless, except in static data scenarios.

The first generation of Power BI changed all of that. With a Power BI license, it was possible to install a Data Management Gateway on premises that would connect to team BI workbooks in Office 365 and update them on a scheduled basis. Yes, the gateway had many limitations (many of which have been removed over time), but finally, the on-prem refresh story was solved. In addition, the model size limit was increased to 250 MB. However, we were still left with a number of problems or limitations.

  1. Daily data refresh schedule. Automatic data refreshes could be daily at their most frequent. Manual refreshes could be done anytime
  2. Capacity. The maximum size of a data model was increased to 250 MB, which is relatively small for enterprise scenarios. In addition, refreshes aren’t differential, which means that the entire model is re-uploaded on every refresh
  3. Data sensitivity/sovereignty.  The refresh problem was solved, but because the data is still cached in the workbooks, there can be reluctance to sending it outside of the corporate firewall
  4. Per User Security – Power Pivot data models have no concept of user security in a workbook (tabular models in SSAS do). Security is at the workbook level
  5. Cost. This initial cost of Power BI was $40 per user per month. A power BI license was required to interact with any workbook that had a data model larger than 10 MB. Considering that a full Office 365 E3 license was around $25 per user per month, this price tended to limit the audience for sharing.

All of this is to say that Power BI in its first (and as yet current) incarnation is suitable for personal and team BI only. There has been no enterprise cloud BI story.

Power BI V2

The announcements yesterday outlined the next generation of Power BI. Going forward, Power BI will be available as a standalone offering, at the price points offered above. Office 365 users will continue to be able to use it from Office 365, but Office 365 will no longer be required to use it. In it’s early days, Power BI was a SharePoint app, but a careful examination of URLs in the current offering quickly reveals that it’s actually two apps currently, both running on Azure (not in SharePoint).

If you’ve signed up for the new Power BI preview, you may notice that the URL is http://app.powerbi.com/…… so this move isn’t a big surprise.

With the new model, Excel is no longer the central container. Users connect to data and publish it directly to Power BI. Behind the scenes, the service is doing a very similar thing as what it does with Power Pivot models – it’s storing them in SSAS. In fact, the same limits still apply – 250 MB per model (at least for now) Excel can still be used, but now it is as a data source.

Visualizations are performed through Power Views, and data is acquired through Power Query. These are no longer add-ons, but available on their own through Power BI Designer. This decoupling is good for those that have not made an investment in SharePoint Online, or Excel.

These changes to the architecture and the cost are great news for adoption, but don’t address the needs of the enterprise. Except for one thing – The SSAS Connector.

image

One of the data sources available to the new Power BI is the SSAS data connector. This connector is a piece of code that runs on premises (it actually includes the Data Management Gateway). It acts as a bridge between the Power BI service, and an on prem SSAS server.

The biggest distinction worth noting is that with the gateway, data is NOT being uploaded to the service, it remains on prem. The way that it works is that when a user interacts with a visualization from the cloud, a query is sent to the SSAS server through the gateway. That query is run, and its results sent back to the user’s visualization, and the data is not persisted.

In addition, when the query is sent back to the SSAS it is run with the permission of the user making the request. This is accomplished through the EFFECTIVEUSERNAME feature in SSAS. This provides for full user level security, and since tabular models in SSAS can utilize per user security, we no longer need to rely on proxy accounts/document level security.

Finally, because the data is being stored in an on prem SSAS server, it can be refreshed automatically as often as desired. For the same reason, we have no capacity limits – you can grow your own SSAS servers as large as you like.

The SSAS connector removes most of the limitations that prevent cloud based enterprise Business Intelligence, and the new pricing model removes the rest. Certainly there are going to be feature limits in the near term, but it appears to me at least that the back of this thorny problem has finally been cracked.

6 Comments