Category Archives: Cloud Computing

The New Power BI – Now With Enterprise!

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.

Power BI Data Management Gateway 1.4 – Where is it heading?

I received a notice from my main Power BI tenant last night that a new version of the Data Management Gateway was available. The previous (1.2) version contained some very significant changes so I was understandably eager to have a look. I installed it, observed a relatively attractive setup interface, then opened the release notes to find out what else was new. Only four items were listed (from the release notes).

  • Unified binary that supports both Microsoft Azure Data Factory and Office 365 Power BI services
  • Refine the Configuration UI and registration process
  • Azure Data Factory – Azure Ingress and Egress support for SQL Server data source
  • Office 365 Power BI – Bug fixes

I had already observed number two, the new setup experience. Bug fixes, while absolutely necessary, aren’t necessarily something to write about, but I think that the other two items are. While they may not have immediate impact, my bet is that they will in very short order.

The key point here is that the gateway now supports the Azure Data Factory. There are many, many things that the data factory enables (Hadoop anyone), but the one that I feel is most relevant to Power BI today is the ability to connect directly to on premises data sources. That’s not quite how it’s been done until now.

Power BI for Office 365

In the context of Power BI as we’ve come to know it today, on-prem data refreshes are handled by the Data Management Gateway. On a periodic basis (daily at most) the service contacts the gateway, which in turn reruns all relevant queries. The resultant data is then uploaded to the service.

The service in turn packages the data and updates the host Excel workbook, and the model is transferred into a back end analysis server. Every transaction goes through the host Excel workbook.

Power BI Dashboards

If you’ve had a chance to see the preview of Power BI Dashboards, you may have noticed that it is not dependent on Office 365 or Excel at all. When you add a data source, you take the date and add it to a cloud based data model directly (presumably backed by SQL Server Analysis Services). All visualization work against these models, with one very important exception. If you connect to a SQL Server Analysis Services Data source you are actually connecting directly to a model hosted on an on-prem SSAS server in real time.

SNAGHTML5339485

How is this done? The connection is made through the “Analysis Services Connector”, which is a separate bit of software installed on prem to facilitate connection between the Power BI Dashboards service and the On-Prem SSAS server. It’s available directly from the dashboards portal.

image

After installing it, a process that establishes for dashboard and SSAS credentials, it can be reconfigured by running the “Power BI Analysis Services Connector” tool.

image

However, installation also adds another piece of software to the host machine. The Microsoft Data Management Gateway. This version of the DMG establishes the connection between the SSAS server and the Power BI service in real time. Up until now, the DMG didn’t work this way, so which version is it?

image

Until now, the most recent version of the DMG was 1.2, so this Dashboards preview contained a glimpse into the next generation Data Management Gateway that provided some intriguing new capabilities.

Coming Together

Checking into the latest version of the Data Management Gateway from Office 365, we see:

image

This version is newer that that included in the Dashboards Preview, and presumably includes everything from it. The key phrase in the release notes to me is therefore “Unified Binary”. One gateway to rule them all, if you will. Does this mean that we’ll be able to connect to on-prem data in real time from Office 365 as well as from the Power BI preview? I don’t know how, but I bet that the building blocks are now there.

The latest version may not include support for any new data sources, or any new bells and whistles, but it’s likely worth setting up for new capabilities that will hopefully show up sooner rather than later.

Using the SSIS OData Source Connector With SharePoint Online Authentication

Last week, Microsoft released the OData Source for Microsoft SQL Server 2012 . What is it? It allows SQL Server Integration Services (SSIS) to use an OData feed as a first class citizen data source in the same manner as SQL Server, Oracle, etc. Until now it was necessary to code OData connections using the script object.

This matters to those of us in the SharePoint world because any SharePoint list data can be expressed as OData.

I’ve written before about how SharePoint data can be extracted into a data warehouse using SSIS and the SharePoint List Source and Destination Adapters, available from CodePlex. These adapters plug in to SSIS and wrapper the SharePoint SOAP web services, and therefore do not need to be installed on a SharePoint server. We have used them for years, and they work very well, however, they are a CodePlex project, and therefore not fully supported.

These CodePlex adapters have more recently been bumping into another limitation. While SOAP web services are supported in Office 365, the adapters don’t support the Office 365 authentication mechanism, which effectively renders them useless. The OData services require the same authentication, but the the new OData Source supports it. It’s also an official Microsoft product, and is fully supported.

There is, however a trick to getting it working. Once you install the OData Source, you open up SQL Server Data Tools, open an SSIS project, and add or edit a data flow task. In the SSIS Toolbox,  You should see the OData Source.

image

Drag the tool on to the design surface, and double click to configure it. You’ll first need to configure an OData Connection Manager, and you’ll do that by clicking the New button.

image

Give the connection a name. The connection will be common to all lists and libraries within a site, so something based on the name of the site is likely appropriate. The Service document location is the OData endpoint. It takes the form of the URL of the site, along with the suffix /_vti_bin/listdata.svc. If the connection is on premises, you can use Windows Authentication, but if it is Office 365, you must use a stored name and password.

image

If you are using Office 365, and you click Test Connection at this point, you’ll receive an error “Test connection failed –> The remote server returned an error: (400) Bad Request.”

image

This is due to Office 365’s “unique” authentication mechanism. In order to authenticate to Office 365, you must first select the “All” button in the toolbar, and set the value of “Microsoft Online Services Authentication” to true.

image

This option may not be available to you. If the Online Services Authentication option is disabled, or greyed out, as it was for me when I first tried to use it, it’s because a prerequisite is missing. In order to authenticate to Office 365, the machine must have the SharePoint Server 2013 Client Components SDK installed on it.

Once the client components are in place, and the option is selected, the data source should be able to connect to the source, and the connection manager can be closed. Lists are exposed as Collections, so if you want to work with list data, you can then select the list from the list of Collections.

image

At this point, the data source will act like any other SSIS data source, you can select and transform columns at will. More importantly, this will help you get SharePoint data both on-prem and in the cloud into a central data warehouse.

Using Multiple WordPress Blogs with Azure Web Sites

This will be one of my more “meta” posts. Blogging on WordPress discussing blogging on WordPress.

In addition to this blog, my company, UnlimitedViz has a number of active bloggers, The Data Queen, and The Data Model chief among them. We all use WordPress for this purpose, primarily because of its ease of use, and ecosystem of useful add-ins. It’s a PHP application that is (normally) backed by MySQL. UnlimitedViz is a Microsoft shop, so these tools are relatively foreign to us, and Azure is Microsoft’s cloud platform. Notwithstanding that, Azure is an excellent platform choice for us to deploy WordPress, due to its low cost, its WordPress support, and our existing investment in the platform.

I had originally deployed my WordPress blog on Azure back in early 2011 when Azure was really just a rudimentary Platform as a Service product, and wrote about it here. However, I was cheating a little bit, customizing a stateless worker role in as stateless a manner as I could, but it still came back to bite me, and ultimately, I moved my blog over to Rackspace, and then back to Azure, but in an Azure Virtual Machine (IAAS).

When Azure web sites were announced, with direct support for WordPress, I was intrigued. Creating one allows you to create a corresponding, hosted MySQL database (hosted by ClearDB). Unfortunately, each Azure subscription is restricted to a single MySQL DB, and I needed to host multiple blogs with one subscription. I finally got around to looking into this recently, with happy results.

WordPress supports multisite blogs, and the Azure team has some good guidance on how to enable this. However, upon trying this, I quickly determined that this would be a non-starter for us. The WordPress multisite option isn’t supported by some plugins, requires a single master administrator, and requires that all blog owners use the same set of plug ins. Now if there is one word that could describe every UnlimitedViz team member, it’s independent.

What I needed was a way to support multiple WordPress instances with one subscription, one database, and a minimum of administrative overhead. Luckily it’s relatively easy. The trick is to use different table names in the MySQL database for each blog. Below is a step by step example of how to do this.

To begin, all of our blogs are domain oriented, not path oriented. As an example whitepages.unlimitedviz.com vs blogs.unlimitedviz.com/whitepages. In this example we’ll add a new blog with the URL newblog1.unlimitedviz.com to our Azure subscription. The process for creating the first blog is identical to the addition of a new one, with the exception that a new database is created instead of connecting to an existing one.

Step 1 – Add a new Azure Web Site

From the Azure management portal, navigate to Web Sites, select New, Web Site, From Gallery.

image

Scroll down to the bottom of he gallery, and find WordPress. Select it, and press the next arrow.

image

Next, fill in the information about your new blog. The deployment settings will be used by PHP to communicate with MySQL, and will be largely invisible after initial setup. Your new blog will also have a .azurewebsites.net domain. We will substitute (or add) our own later. For now, our new blog will be newblog1.azurewebsites.net, will use an existing database, live in the North Central US data center, and use our corporate subscription.

image

When ready, click the next arrow. If I was creating the first (and only) database, I would be able to give it a name and create it here. As it is, we can select our existing database, agree to ClearDB’s terms of service, and select the “done” check mark.

image

At this point, the web site will be created. Once done, it is possible to navigate to the URL, and set up WordPress, but we need to make an additional modification first.

Step 2 – Specify the Table Prefix

In order to tell this particular WordPress instance which tables to use in the database, we need to modify the wp-config.php file in the web root. How do we do this? We have a few options. We could use FTP to download the file, edit it, and send it back up (FTP settings are under the Dashboard tab for the web site).

image

We could also use GIT, but as I’m unfamiliar with it, I’ll let more GIT friendly folks sort that out as they wish. My preferred option is to use Webmatrix, which allows the direct editing of Azure Web Sites. Webmatrix is available from the bottom tools ribbon wherever a web site is selected.

image

If Webmatrix has already been installed, it will launch, and if not, you’ll need to install it first. Your first option will be its operation mode, either direct, or off-line. We will select direct.

image

Next, we double click on the wp-config.php file. This will open it in the editor. Now we scroll down to the line for $table_prefix, and edit it as appropriate, in our case “newblog1_”.

image

Finally save the file and close Webmatrix. Now when the WordPress configuration wizard is run, it will create table in the database that are prepended with “newblog1_”, and use them thereafter. The configuration wizard runs whenever WordPress can’t find the specified tables in the database

Step 3 – Configure WordPress

Next, we navigate to our URL, where the WordPress configuration wizard will go ahead and complete our setup. In our case, we navigate to newblog1.azurewebsites.net, and fill out the form.

image

When ready, click on the “Install WordPress” button. Once done, we log in and start building out the blog. That’s really all there is to it from the blog perspective. However, there’s likely one more important thing that we need to do.

Step 4 – Activate Your Own URL

In all likelihood, you don’t want to use azurewebsites.net in the domain of your new blog. We could use a DNS alias on our DNS to reroute traffic, but Azure won’t answer any requests that it isn’t expecting. We must first register our custom domain with the Azure web site, and this is only possible with Shared or Standard web sites. New sites get created in free mode, so we need to first switch the compute mode. Keep in mind that we’re switching away from free, so charges will accrue.

To switch modes, from the Azure Management Portal, click on the web site in question, then click the Scale tab. Under general, select on either “Shared” or “Standard” (shared is cheaper), and click save at the bottom of the screen.

image

Once you accept the disclaimers, the mode changes, and we can add our domain. However, before we do, we need to make a DNS change. Azure won’t allow you to add just any domain, it needs to know that you own it. To do so, you need to add an alias (CNAME) entry that points from a verification subdomain (awverify) to a verification subdomain of our web site. In our case, the entry is awverify.newblog1.unlimitedviz.com and it points to awverify.newblog1.azurewebsites.net.

image

Honestly, this is the biggest pain of the entire exercise. The effect of the change is not immediate, and after making the change, you may want to take a break for a while. According to the UI, there are also apparently other aliases that can be used for this purpose, but this is what works for me.

At this point, we can add the domain to the Azure web site. To do so, we open the Azure admin portal, and open the definition for the web site. Next, we click on the Configure tab, scroll down to the domain names section, and click the “manage domains” button.

image

In the dialog that pops up, enter your custom domain name

image

If your verification alias was properly set up, and all is well, you will receive a green check mark status indicator. If not, it will be a red x, and you will need to fix the problem. It could be a misspelled name or just no verification result. However, if all is well, make note of the IP address for your A records, and click the check button to save the configuration.

Finally, we add an A record to our DNS that points our custom domain newblog1.unlimitedviz.com to the IP address noted just above.

image

That’s it. Taking this approach, we can have multiple, independent WordPress blogs sharing a single Azure subscription and a single MySQL database.

image

This is the current setup for all of the UnlimitedViz blogs. There are, however a couple of caveats, that you should be aware of.

Caveat Emptor

Once you go ahead and allow WordPress to run its configuration wizard, it creates its table in the database. If you remove the web site, the underlying tables remain in the databases. This is either good or bad, depending on your perspective. If your website gets deleted, the data persists, and its simple to connect back to it, just like SharePoint. However, if you need to clean out the database, it’s pretty much impossible.

Thus far, I haven’t found any good way to manage the database directly. However, I haven’t looked very hard, as I haven’t needed to, and that’s a good thing.

The one thing that you should certainly be aware of if you’re going to be doing this to any sort of scale, is that the MySQL database that is created automatically is restricted to 20MB of total size, and that’s a limit that you will run into fairly quickly. I certainly never saw any indication of this limit while I was building the environment, but then, I never read any of the terms and conditions. Really, who does? The good news is that it can be upgraded.

The day after moving our blogs to this platform, I received an email from ClearDB stating that I was near my storage limit, and should consider upgrading. The email didn’t indicate how this could be done, so I navigated to the ClearDB site. Since there was a login button I used it, and entered the email that I use for my Azure subscription. Unfortunately my password didn’t work. Creating a MySQL database creates a ClearDB account, but I have no idea what password it uses. Using the “Lost Password” worked, but I was still  unable to log in. Finally, I logged a support issue with them using the provided support form. Very quickly, my account was enabled for “direct login” which is what was necessary, and allowed me to upgrade the database to a greater capacity. The plan that I opted for was $9.99/month, and allows up to 1 GB of space, which is plenty.

There was one other bump that I had to overcome. I was migrating existing blogs, and to do so, I used the export and import features that are a part of WordPress. The export feature downloads an XML file with all of your blog content. Supporting images are not included (they are downloaded at import time), but the file can still be rather large. The first step in the import process uploads the XML file, and then brings it back into the database. The problem is that by default, the maximum upload size for WordPress is 2MB, and I had two that were larger.

The way to get past this is to increase the maximum upload size. In Azure, this can be done through the addition of a configuration file in the web root. The file needs to be named “.user.ini”.

Open your web site using WebMatrix, right click on the site name, and select “New File”. Select TXT as the type, and name it “.user.ini”. Double click on it to open, and add the following line to it:

upload_max_filesize = 50M

image

After saving, you should be able to upload files up to 50MB. It may be necessary to restart the site through the admin portal.

I’ve been quite liking the performance and the stability of this new setup, and I recommend it highly for this sort of requirement.

Installing Office 365 App : Sorry, there was something wrong with the download

I recently ran into a bit of trouble installing the Nintex Workflow for Office 365 app to our main Office 365 tenant. Every time I tried to add the app from the store, I would receive the above error, or “Sorry, something went wrong with adding the app”.

image

With some great help from Nintex support, I was able to sort this out, but when I tried to repeat the process for writing this article, I also received the error:

The app package does not support the culture en-CA specified for installation

Yes, being Canadian has a few disadvantages, but not many. This is one of them. We do have a slightly different English than our cousins to the south.

I tested this on a few other apps and ran into similar problems with them. The problem appears to be a mismatch between the local settings for your tenant, and those explicitly supported by the app. You can work around these problems during installation by selecting en-US as the language. You do this during the app addition process. However, you first need to remove the failed addition.

Hover over the failed app, and click on the ellipsis that appears. Next, click on the ellipsis in the “about” box that pops up, and select remove.

image

Once it is remove, again begin the process of adding the app. When you come to the trust screen, click on the “SHOW LANGUAGE OPTIONS” link. From there, select English (United States). My default was English (Canada).

image

Once done, and you click the “Trust It” button, your app should install normally. I am told that this is a bug – I’m not sure if it’s on the Office 365 side or the vendor side, but it should get fixed. In the meantime, hopefully this workaround helps someone.