Tag: Power BI

SharePoint and Power BI – Better Together

Published September 30, 2016

Ever since 2007, SharePoint has included Business Intelligence amongst its core workloads. There have been a variety of approaches to the workload over the years, but today those core workloads include Excel Services/Excel Online, PerformancePoint, SQL Server Reporting Services Integrated Mode, and Power Pivot for SharePoint.

Power Pivot for SharePoint and Excel Services go hand in hand and can really be considered as one of the main pillars, leaving us with three. If we quickly examine these three pillars in SharePoint 2016, it’s pretty easy to spot an emerging trend. Excel Services is gone from SharePoint 2016, its capabilities being added to Excel Online. Excel Online connects to, but does not run on SharePoint. PerformancePoint still exists in 2016, but it has received precisely 0 new features – it is identical to the version in SharePoint 2013, and remains a part of product for legacy reasons. For all intents and purposes, I consider PerformancePoint to be deprecated. SSRS Integrated mode has been greatly improved in 2016, but contains nowhere near the improvements that the Native Mode version of SSRS has in 2016.

At the same time, the past year has witnessed the spectacular rise of Power BI. Power BI is clearly the focus area for Business Intelligence within Microsoft for cloud based BI delivery. Last fall the SQL team announced that on-premises customers were not being ignored, and that SSRS was the platform for on premises BI delivery They also sketched out a roadmap that showed both platforms being able to deliver the same type of reports. In June 2016, the team delivered on a portion of this vision with SQL Server 2016 Reporting Service.

So where does this leave SharePoint in the Business Intelligence ecosystem?

In my opinion, it leaves it right where it should be – as an integrating platform, and NOT as a runtime platform as it has been in the past. SharePoint provides in context BI by connecting content to reports, and providing dashboards connected to multiple sources. In 2016, SharePoint connects to Excel Online to deliver Analytical reports. Excel runs with SharePoint now, not on it. SSRS Integrated mode still runs on SharePoint, but the investments in Native mode are a clear indication to me that this will be the direction here as well. Unfortunately, Sharepoint has been lacking tight integration with Power BI.

The recent Ignite 2016 conference was the first public appearance of the Power BI web part.

Figure 1: Insert web part dialog with Power BI web part

The Power BI web part works with Modern Sharepoint pages and is based on the new SharePoint Framework (SPFx), which means that it is completely client-side. Why does this matter to us? The fact that it is completely client side means that it will work both in SharePoint Online and on premises. Initially, it will only work with SharePoint Online, but that is because the SharePoint Framework is currently unavailable on premises.

To use the new web part, first create or edit a Modern SharePoint page. The Modern pages support the new Modern web parts. Click on a “+” icon to open the insert part control (Figure 1). Once inserted, add the report URL, and the page. The report page should immediately render within the context of the SharePoint page.

Figure 2: Power BI Report page rendered within a SharePoint page

Since the web part is rendering client side, the consuming user obviously needs to have access to the report. This means that the source report must have been shared with them through Power BI dashboard sharing, or the report is in a group within which the consuming user is a member. This latter case makes the most sense given that all Office 365 Groups will have a corresponding Modern Team site. Embedding the report within group pages should “just work”.

The devil is of course in the details, and all of these details are not yet available, but Given the number of questions that I have received over the past year about Sharepoint/Power BI integration, I expect that its existence will come as welcome news. Over time I would expect to see it picking up support for parameters and the ability to work with individual report items (this is speculation, but it makes sense). It’s also not much of a stretch to see how SSRS could make available a Modern web part that worked in the same fashion with on premises SSRSs. That web part could conceivably work both on premises an Online, bringing SSRS to SharePoint Online for the first time.

SharePoint is still very much a platform for integration and for Business Intelligence content delivery. SSRS and Power BI will be the de facto reporting engines for on-premises and the cloud respectively, and Sharepoint will be the dashboarding/integrating platform for both environments.

15 Comments

A Simplified Method of Working with SharePoint Data in Power BI

Published July 6, 2016

Although I typically advise against it, there are valid reasons to report on SharePoint list data directly. Power BI Desktop makes this data quite easy to access – you can use the built in connectors for SharePoint or SharePoint Online, or, due to the fact that any SharePoint list is available via OData, you can also use the OData data connector. Microsoft has recently made improvements to both methods, but the SharePoint connectors bring some significant usability advantages. One of these advantages is what I’m calling “summary columns”.

The Problem

Consider the following SharePoint list with different field types:

Connecting to this list with Power BI desktop and editing the query returns all of the list fields regardless of their visibility in the user interface. Assuming that we want to work with the above field values for analysis purposes, we can discard the fields that we don’t need, and reorder the remaining, leaving us with only these fields.

However, you can immediately see that we’ll need to do some work in order to get our data in a usable format.

The title field is simple enough – it’s value is immediately available, no issues there, and no changes are necessary. From the Name field, several columns are returned. Two are the ID of the name in the site collection’s user list. It would be possible to connect to the root of the site collection, retrieve the user list, and establish a relationship between the tables, but clicking the expand icon will allow Power Query to do a lookup for each ID and return the desired attribute, in this case, Name.

The same is true for the lookup field type – the column can be expanded in order to include any attribute of the source item. The field using managed metadata works in a similar fashion; it can be expanded in order to retrieve the text value of the managed metadata item. Link fields work the same way – the can be split into two columns, the link itself and the description. However, the field containing multiple values is a little different. The great news here is that it’s possible – previous versions of Power Query couldn’t work with multi value fields, but now the SharePoint data source supports it.

Multiple value field values are returned as a list. With list items, the expand icon will duplicate the entire record for each value on the list. This may or may not be the desired behaviour, but remember, this is Power BI. Everything can be aggregated.


Before	After

The conclusion to be drawn here is that in order to represent SharePoint list data that is using any sort of control more complex than text or number, we need to do some work. However, the good news is that someone (I’m not sure if it’s the Office Team of the Power BI team) has added a feature that makes this whole process much simpler.

The Solution

After connecting to your SharePoint list, edit the query. Instead of diving in and performing all of these manual transforms, select your multi value column(s) if you have any (this will make more sense momentarily). Select any rich text columns as well, and then scroll right to find a column named “FieldValuesAsText”. Select this column, then right click on it and select “Remove Other Columns”.

The FieldValuesAsText column is our magic bullet. It automatically converts most (rich text fields being the exception) of the more complex SharePoint data types to simple text that work well within Power BI. Simple click on its expand button, select the columns that you want to include in your analysis. I find it useful to deselect “Use original column name as prefix” as well. We are left with textual representations of our field data.

You will notice that the multiple value fields here have their values separated by commas. For multiple values, I tend to prefer the “raw” approach, which is why we retained the multiple value column above. We can still expand it and create a separate line for each value, and remove the column created by “FieldValuesAsText”.

Finally, you may have noted that the Rich Text field isn’t automatically converted. In order to extract useful text from it, we still need to use Power Queries transformation functions such as Replace Value, Trim, and Clean.

In a nutshell, if you’ve been frustrated by formatting or data type limitations when using SharePoint data in Power BI, have another look, and check out the FieldValuesAsText column. It will make everything a lot simpler.

11 Comments

SharePoint 2016 Team SItes and Groups – It All Comes Together

Published May 4, 2016

SharePoint is back. With a vengeance.

For the past few years, SharePoint has been relegated to a supporting role within Office 365. It even lost its identity a few years back, with the name “SharePoint” being replaced by the bland “Sites”. This has been exacerbated recently by the rise of Groups (referred to either as Outlook Groups or Office 365 Groups). If Groups are the way forward, what value can SharePoint provide?

A lot, as it turns out.

Office 365 Groups

I refer to them as Office 365 Groups, because they incorporate elements from multiple Office 365 products. However, they are also referred to as Outlook Groups, which is the name of the mobile app. The interesting thing is that if you scratch the surface of the Groups user interface in either OneDrive or OneNote, you can see pretty quickly that it’s really a SharePoint site, or more specifically a site collection. Just look at the URL.

In SharePoint, a Group is a specific type of site collection with a single document library for files (the OneDrive), and a library for other supporting files (including the Group OneNote). The difference is that a Group is what it is – it can’t be extended or modified to any significant degree. You can’t even access the All Content or site settings by adding “/_layouts/15/viewlsts.aspx” to the site URL (if you try, you get redirected to the main OneDrive view of the Group). SharePoint is really just there as the container. This can be frustrating, because as anyone that has worked with SharePoint knows, it can be much, much more.

The introduction of Groups initially caused confusion, particularly for users of SharePoint team sites, or Yammer. Was OneDrive replacing SharePoint (which is kind of silly… OneDrive IS SharePoint)? Did the new Exchange based conversations mean that Yammer was dead? Those questions have been hanging out there unanswered for quite some time.

Once I understood them, I came to really like Office 365 Groups. They bring together multiple tools into a single coherent location with a clear security boundary, and they are relatively simple to manage. One of the criticisms of Groups has been that there is no single central UI. Groups are manifested in Exchange, SharePoint, OneDrive, OneNote and Power BI, but there’s really no central starting point for a group. It’s like a city of suburbs in search of a downtown.

There have been more than a few detractors of Groups as well. Most of them relate to their immaturity. The Outlook conversations provide excellent email integration (obviously) but were not as full featured as Yammer in other ways. There have been several others, but the biggest complaint seems to me to be the fact that a SharePoint team site provides much more functionality than a simple OneDrive library. These factors have been a significant blocker for the adoption of Office 365 Groups.

That all changed with the Future of SharePoint event on May 4, 2016.

The New Team Sites

Team sites have been the traditional place for groups of people to work in the world of SharePoint. These sites would be decorated with web parts, both in and out of the box in order to augment their capabilities, and to provide a window into other team based content structures such as calendars, custom lists, reports, etc. Team sites have always seemed like the logical starting place for group data, and now they are.

Beginning in mid 2016, whenever a new group is created, a new team site will be created as well. Conversely, a new team site will create an Office 365 Group, with all of its components (OneDrive, OneNote, Mail address, Planner, Power BI Workspace). To be totally clear, this new style of team site is a SharePoint site collection, and not a subsite (or web), which means that its security details are bound to that of the group.

Yammer users may wonder what this means for the previously announced integration with both Groups and Azure Active Directory. Nothing was announced at the event, so this is pure speculation on my part, but I would have to assume that if there is to be a 1:1 correspondence between Office 365 Groups, and Yammer groups, that Yammer will be a part of this as well. Given SharePoint’s strengths, I can only assume that this will be the place that all non conversational Yammer content is stored (files, calendars, etc.

The new team site will intrinsically integrate many of the things that formerly needed to be added on later, and the new Office 365 connectors mean that many other content sources can be added with a minimum of effort.

External Sharing

While both SharePoint and Yammer have had external sharing for several years now, and Yammer now has external groups (with a lowercase g…), Office 365 groups have been restricted to members of the tenant’s Azure Active Directory. Therefore, if we now have a 1:1 correspondence between Groups and team sites, and we are also able to use Yammer as the conversations provider, Office 365 groups need to accommodate external users.

The good news is that soon, they will. Thanks to Wictor Wilen’s sharp eye, we can see in the Office 365 admin center that as of this writing, the infrastructure to support external access to groups has already rolled out. Coincidentally (or not), Yammer support of external groups also rolled out in the same timeframe.

The new SharePoint team sites, and their integration with Groups will give Office 365 that entry point that so many have been missing. It is exceedingly easy, and fast to get up and running with a usable site that is automatically integrated across the platform. When you create a Group, you not only have the AAD group, but a team site, a calendar, a distribution list, a conversation platform, a Planner Plan and a Power BI workspace. At the same time, it brings SharePoint back out of the shadows, and back in to the limelight.

SharePoint is back at the center of Office 365, and it’s better than ever.

2 Comments

Integrating Microsoft Flow with Power BI for Real Time Reporting

Published May 2, 2016

In addition to all of the obvious business benefits that Microsoft flow brings to the table, one of the things that initially struck me about it was how useful it would be for data acquisition purposes. The first thing that I did with it was to create a flow that queries weather stations from Weather Underground, and stores the data in SQL Azure, and uses Power BI to analyze the weather patterns.

I may blog about that solution in the future, but with the Future of SharePoint event rapidly coming up, my BI Focal fellow collaborator, Jason Himmelstein convinced me that there was something more interesting that we could do with this. How about near real time monitoring of Twitter conversations for the event? All of the pieces were in place.

We rolled up our sleeves, and in relatively short order, had a solution. Jason has written about the experience on his SharePoint Longhorn blog, and he has included the videos that we put together, so I can be a little less detailed in this post.

There are currently essentially three different technologies necessary to make this happen.

Microsoft Flow
SQL Azure
Power BI

Let’s go through each one.

SQL Azure

We could store our tweets in a variety of locations (CSV, SharePoint, Excel), and there are already a number of examples out there that demonstrate how to do this. The reason that we want to use a SQL Azure database is twofold. Firstly, Flow has actions for connecting and inserting data into it. That takes care of our storage requirement. The second, and most important part is that SQL Azure databases support DirectQuery in Power BI.

With DirectQuery, Power BI does not cache the data – every interaction results in a query back to the source, in our case the SQL Azure database. This has the effect of making the data available for reporting as soon as it has been delivered by Flow. That’s the theory at least. In reality, Power BI caches certain elements temporarily (dashboard tiles for example), but this is as close to real time as you can get in Power BI without writing data directly to t in the API. Reports are for the most part up to the minute.

You need an Azure subscription to create a database, and the process for creating it is documented in the following video.

[embedyt] http://www.youtube.com/watch?v=Qh-7OrSHimE[/embedyt]

We will be using the Twitter trigger with Microsoft flow, and it has several output variables. We want our table to be able to store the values of those variables in a table, so we use the following script to create that table.

CREATE TABLE Twitter
(
 id int IDENTITY(1,1),
RetweetCount int,
TweetText NVARCHAR(250),
TweetedBy NVARCHAR(100),
CreatedAt NVARCHAR(100),
TweetID NVARCHAR(50),
SearchTerm NVARCHAR(50)
);
Go

alter table Twitter add primary key (ID)

Once created, we are ready to fill it with tweets.

Microsoft Flow

The recently announced Microsoft Flow is a tool that allows users to automate and integrate processes from different data sources in the cloud. It is based on Azure Logic Apps, and is currently in preview, but already supports a wide variety of actions and triggers. You can sign up for, or access your flows at http://flow.microsoft.com.

Flows consist of two primary objects, triggers and actions. Most triggers, and at the moment all actions, are tied to a data connection. You can register your connections as you go, but you can also view and register them en-masse by selecting your person icon and selecting “My connections”.

Once registered, you can use “Browse” to start from a template, or you can go to “My flows” to start from scratch. That’s what we’ll do. To start, click on “Create new flow”, and you will be presented with the trigger selector.

Most of the available triggers are events, and the first 4 are special cases. The recurrence trigger allows you to schedule your flow. This is what I use for my weather gatherer – it just calls a web page every 5 minutes and passes the result into the next action. The external content source actions are in alphabetical order, so we just scroll down to the Twitter action and select it.

If you have already registered a Twitter account, it will be used by default. If you want to change it, or add a new one, just click on “Change connection”. It’s a good idea to use multiple Twitter accounts if you’re doing multiple queries to avoid running afoul of Twitter’s rate limiting. Finally, just enter the search term in the Query Text box. Any new post of that term on Twitter will launch the flow.

Next, we need to add the “SQL Azure – Insert Row” action. To do so, click on the “+” symbol, click add an action, then click “Load more” at the bottom. Scroll down and select the action.

Again, if you have a database registered, it will be selected by default. If you have multiple databases registered, or want to add more, click on “Change Connection”. Once you have the correct connection selected, you can click on the dropdown and select the correct table (the one created above”). Once selected, the fields will load in to the action.

Populating the fields is a simple matter of selecting the appropriate output variable from the Twitter trigger. The final field, SearchTerm, is used to distinguish between different Twitter searches. Each flow only triggers on one term, but we want to set up multiple flows. We manually enter the value here (in our case “FutureOfSharePoint”). Later, that will be used as a slicer in Power BI.

Once complete, give the Flow a name, click on “Create Flow”, and then “Done”. At that point, you really are done. That’s it, that’s all there is to it. You can query SQL Azure to check for data, and you can also use the information icon to check on the status of Flow runs.

All of these steps are well documented in Jason’s video below:

[embedyt] http://www.youtube.com/watch?v=kHwcR1sWDiY[/embedyt]

Power BI

We want to surface this data with Power BI. We can do this directly from the web interface, but we have a lot more options if we design the report with Power BI Desktop. The next step is to launch Power BI Desktop, Select “Get Data”, select “Microsoft Azure SQL Database” and press the “Connect” button. At this point, you enter in the details about the Azure SQL Server and database, and most importantly, select the DirectQuery option.

The import option will retrieve data from the SQL database and cache it in an embedded model within the report. Once published, the Power BI service can keep it refreshed, but no more than 8 times per day. This is contrasted with DirectQuery, where no data is persisted in the service, and every interaction results in an immediate call back to the data source. For frequent updates, this is what we need.

A word of caution here – we pay a significant penalty from a feature standpoint when using DirectQuery mode. Most of the functions in Power Query and many of the functions in DAX are unavailable to us in this mode. However, with this particular data set, these restrictions are an acceptable tradeoff for the frequent updates.

Again, Jason has done a great job explaining the steps required to build the reports and dashboards in the video below, so I am not going to repeat them here.

[embedyt] http://www.youtube.com/watch?v=U71stcLZoBc[/embedyt]

Once the report is published, you may want to present it to a wider audience. You can do that through dashboard sharing if your recipients have access to Power BI, or you can publish it anonymously. Given that this is Twitter data, it’s certainly public, and there is no harm in doing so.

To publish the report anonymously, simply open the report in the Power BI service, and select File – Publish to web.

You will then be presented with a dialog box that will give you both a link and an embed code for 3 different possible renditions of the report. Simply select the one you want to use and paste it into the ultimate destination. My report can be seen below, and I will likely update it from time to time to follow current events.

One thing to keep in mind about reports shared anonymously is that even though the report is using DirectQuery, the visuals are only updated approximately every hour. The above report will lag reality by about an hour.

You can see here the power of these tools working together. Flow is an easy to use but yet powerful integration tool. SQL Azure is a rock solid database available in the cloud to other cloud services, and Power BI allows for rapid insights to be built by Power users. No code was harmed in the building of this solution, but regardless, it’s still quite powerful.

From here, I can only see it getting better. My ask from the Flow team? A Power BI action that pumps data directly into a Power BI data model, thus eliminating the need for the Azure DB, and allowing for self updating visuals in Power BI, but that’s a topic for another day.

Power BI Analyze in Excel – The beginning of a beautiful thing?

Published April 8, 2016

One of the announcements made at the Microsoft Data Summit in this past March 2016 was the availability of Analyze in Excel. This feature allows an Excel workbook to connect to a data model that is stored in the Power BI service, and to use it to analyze the data contained within. With this approach Excel is not importing data, or at least it is not importing any more data than the query results. It is exactly like connecting data to SQL Server Analysis Services data sources, something that Excel users have been doing for years. Well, to be completely accurate, it’s not LIKE connecting to SSAS, it IS connecting to SSAS. The only difference is that in this case SSAS is in the cloud. This feature significantly enhances the utility of the Power BI Service, and is important for several reasons that may not be all that obvious. I’d like to walk through a few of them, but let’s start with the obvious.

Excel is a very powerful analytical tool

As nice, and as attractive as Power BI visuals are, Excel still rules the roost when it comes to doing advanced analytics. Excel has been doing this for years and is very mature. It supports features such as pivot tables, pivot charts, and drill through to data, where Power BI reports still do not. The lack of these features can be a blocker for Power BI on its own, but if the data models in Power BI can be analyzed with Excel, suddenly a move to Power BI is not an either/or decision – you can have it both ways. You can deploy models and reports into Power BI and take advantage of all the goodness there, but you can also connect with Excel when the deep analysis is needed. With Analyze in Excel, you can have it both ways.

A wider audience for your data models

Very often, the person that builds the data model is the same person that does the analysis. This is the nature of self-service analytics. In the past when the only multidimensional analysis tools were OLAP cubes and connected Excel workbooks, cube design was a specialized skill. The cubes were published and users would use them as is. The advent of the data model (Power Pivot) and self service analytics lowered the skills bar so that analysts could acquire data, model it and analyze it, making the entire process much quicker and responsive. However, this still puts the model out of reach for those with no data modeling skills or interest.

Analyze in Excel provides the best of both worlds. Analysts can build models and reports in Power BI, and users that need more in depth analysis can connect to them with Excel without having to reinvent the wheel. This in effect provides the same capability that PowerPivot for SharePoint does on premises. One model can now reach a much wider audience of users. This has many of the benefits of an on-premises SSAS deployments without all of the organizational overhead of getting them up and running.

Uses the Analysis Services OLEDB Driver

The connection that is made from Excel to the Power BI services uses the latest version of the classic Analysis Services OLEDB driver. This is the driver that has always been used to allow Excel (and other tools) to communicate with Analysis Services, and this new version has been updated in order to work with the cloud based SSAS service. In fact, in order to use the feature, you must first download and install the updated driver. Therefore, in theory, any tool that uses this driver should be able to communicate with Power BI models as if Power BI was one great big SQL Server Analysis Services server (because it is).

It really is Analysis Services in the cloud

The Power BI service itself is backed by tabular mode SSAS. Until now, it was necessary to go through the service to access it. Analyze in Excel is the first instance that I know of that a client application communicating directly with that SSAS instance. While this connection is really using the Power BI API, it does beg the question – can a fully Platform as a Service version of Analysis Services be very far away?

Claims based authentication and Power BI API

None of the products in the SQL Server suite currently supports claims authentication. This is true even for the yet unreleased SQL Server 2016. Even SQL Azure, a cloud based version of SQL Server, requires SQL authentication only (although Azure Active Directory authenticated databases are currently in preview). However, looking at the connection string contained in the ODC file used by the Analyze in Excel feature reveals some interesting things. Here’s one connection string:

<odc:ConnectionString>Provider=MSOLAP.7;Integrated Security=ClaimsToken;Identity Provider=AAD;Data Source=https://analysis.windows.net/powerbi/api;;Initial Catalog=xxxxxxx; ……..

The value MSOLAP.7 for the provider indicates that this is the next version of the SSAS OLEDB Driver. No surprises there, but this does hint at future compatibility (see SharePoint below). The value for Integrated Security, and Identity Provider (ClaimsToken and AAD) indicate that it is leveraging Azure Active Directory Claims authentication. We therefore have a version of SSAS that can use Claims based authentication. This isn’t available to on-premises installations, but given that the capability has been built, I imagine that it is not all that far away.

Finally, the Data source indicates that the Power BI API is being used to marshal all communication with the back end API service. I think that it is reasonably to conclude that any API for a PaaS based version of SSAS would be based on, or strongly resemble the Power BI API. They may even be one and the same.

Excel Online in SharePoint

As anyone that has set up PowerPivot for SharePoint can tell you, SharePoint supports the configuration of new OLEDB drivers. This support carries forward into the Office Online Server in the world of SharePoint 2016. Given that both SharePoint and OOS utilize claims based authentication, it should theoretically possible to create a workbook that uses the Analyze in Excel feature, store it in SharePoint, and have it work for multiple users from within a browser. I imagine that more plumbing is needed at this point, but it would be an interesting way of integrating Power BI in the cloud with SharePoint both on premises and Online.

Reusing the Excel Files, and Limitations

In the same vein as discussed with SharePoint, Power BI itself allows Excel files to be interacted with in the service in exactly the same manner that Excel Online does. Theoretically, one should be able to use Analyze in Excel to build a workbook, then connect it to Power BI and have it work for interaction. While it is possible to connect it, all interactions fail at the moment. It appears that the Power BI service (or the backing Office Online service) does not yet support the new OLEDB driver.

Another current limitation of this feature is that data sources using Direct Query (this includes SSAS sources) or sources created by the Power BI API cannot be used with Analyze in Excel. At least not yet.

Analyze in Excel is another useful tool in the Power BI arsenal, but as outlined above, I think that it’s a harbinger of even greater things to come.

7 Comments