Tag Archives: Cloud

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

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.

The White Pages are now Running on Windows and SQL Azure

Ever since I started this blog, I’ve hosted it internally on our premises. Part of the reason for this was that I wanted to have full control over what was going on with it, and I wanted to work in a familiar environment. For me, that was of course the Microsoft stack. While SharePoint has excellent blogging features, made even better by the Community Kit for SharePoint: Enhanced Blog Edition, my feeling is that its feature set is more applicable to an inside the firewall deployment. Also, if I were to use SharePoint for this purpose, I’d be constantly distracted by the desire to improve upon it.

What I needed was a platform that was focused on blogging, and that I wouldn’t wind up tinkering with too much. I settled on WordPress, which seemed to be very well supported, and quite good at what it did. WordPress had direct integration with Windows Live Writer, and had apps for the iPhone, Blackberry, Android, and now Windows Phone 7.

WordPress natively runs on PHP and MySQL, and typically runs in Linux environments. However, since IIS supports PHP and MySQL runs on Windows, it is possible to get it running in my “familiar environment”. Normally doing this sort of thing is a bear, but by using the Web Platform Installer from Microsoft, the installation was a breeze. All that was necessary was to run it, and select WordPress as a desired application. The installer then took care of downloading PHP, MySQL, WordPress, and integrating them all together. After answering a few account and password questions, I was up and running, and have been ever since.

The one drawback of this approach was that I was hosting it myself, and therefore always concerned with reliability and uptime. More importantly it has been sharing a server with other applications, and more than once has gone down because another system needed a reboot, crashed, or something. A hosted environment was obvious, and since I’ve been exploring the Azure platform lately, I thought I’d see what was involved.  One of the advantages of the MVP program, which I’m newly a part of is that you are allocated a certain amount of Azure computing hours, so off I went experimenting.

Happily, one weekend later, this blog has been transitioned to a high speed, and highly available platform, that most importantly, I don’t have to maintain. Not only that, but I’ve been able to take MySQL out of the picture completely, and I’m using a SQL Azure database as my data store. I had several false starts right away, and I’m going to document the approach  that I took and post it here shortly, but for now, I’m pretty happy with the results.

Hello Azure!

Integrating SharePoint On Premises With BPOS and Exchange Online: Part 1–Outbound

If you’ve ever set up a SharePoint Farm, you’ll know that one of the first things that you need to configure is Outgoing Email. The way that you typically do this is to specify the name of an Exchange (or any other SMTP server) in your organization, a from and reply to address, and you’re done. Occasionally there are problems because the Exchange (or other) server does not allow relaying, and that’s easily remedied with a quick server configuration.

If you’re using BPOS, you not only have Exchange online, but you have SharePoint online. Outgoing email is already automatically set up for you. Just set an email alert for yourself and your will receive an email when anything changes. However, what about organizations that have elected to use hosted Exchange or BPOS, but also still maintain an on-premises SharePoint farm?

The good news is that it can be done. The bad news is that it’s not as simple as before when everything was on premises. This article will attempt to walk through the required steps.

This example uses SharePoint 2010 but the same is true (I think) for SharePoint 2007.

1. Setup SharePoint Outgoing Mail

As before, go to Central Administration, navigate to system settings, and click the “Outgoing E-Mail Settings” link.


There are two major things to note here. Firstly,the Outbound SMTP server is NOT one of the Online Services servers. What is it? Well,it doesn’t exist yet – we’ll get to that in step 3. The reason for this is that to deliver mail to any of the Online Services servers, you need to authenticate, use SSL, and use a non standard SMTP port. Unless I’m missing something, I don’t see any of those options here. (You can find complete instructions on relaying messages to BPOS and Exchange Online here). What we therefore need to do is to set up our very own SMTP server that can relay these messages for us.

The other thing to take note of here is the “From address”. When working internally, this doesn’t normally matter, you can give it any old fake name and off it goes. This is not true here, if this email address is not valid online, mail will not be delivered. We will add this address later.

2. Update DNS

If you haven’t already done so, you’ll need to add DNS entries for the SMTP server that will use the same IP address as this SharePoint server. In this example I’m using an internal domain, but you’ll also want to add another one externally if you’ll be configuring incoming email. all that will matter is that it resolve to the same machine.

3. Add the SMTP Service

Complete instructions on how to do this (as well as configuring mail for SharePoint generally) here. I will therefore not go into any detail except to point out that if you’re running on Windows versions prior to 2008, the installation procedure is slightly different. The end result is pretty well the same. In fact, I don’t think that this feature has been updated in about 10 years, it still requires the IIS6 admin interface.

4. Add Proxy User to Online Services

Unless you want all of your automated emails appearing as if they originated with an actual user, you’ll want to use a proxy user. The down side is – you’re going to pay a license for this user. Of course, given the cost of hosted Exchange, that’s not a big deal, but it would be nice if this wasn’t required.

If you’re reading this because you have BPOS or Hosted Exchange, you already know how to do this, so I won’t spell it out here. Just remember that this user needs to be the same as that specified in the first step.

5. Configure the SMTP Service

Open up the “Internet Information Services (IIS) 6.0 Manager” from the administrative tools group on the server. You should see your server as a node – open the node, and you’ll see the SMTP server. If it’s not already started, start it. Then right click on the server and select properties. When the properties box comes up, select the delivery tab. It should appear like the following:


If you haven’t already guessed it, we will be using all 3 numbered buttons.

1 – Outbound Security:


This is where we enter the credentials for the proxy user. Exchange Online checks to see if the “From” user and the authenticated user are one and the same. If they’re not, it rejects the message, which is why we need to have the proxy user, and to be careful about the “From” field in step 1.

TLS encryption is basically SMTP’s way of saying SSL, so this screen covers that off as well.

2 – Outbound Connections


This is the screen where we get to specify the non standard SMTP port used by Exchange Online. Use 587.

3 – Advanced


I have no idea why this is any more advanced than either of the other two screens, but no matter. This is where you enter the Online Services SMTP server as a Smart Host. You can also enter a masquerade domain (the “real” mail domain), but it’s optional.

When you’re done, click OK. Then, click the access tab. Click the Authentication button to ensure that Anonymous  access is selected (it is by default). Then, click the relay button.