Category Archives: Cloud Computing

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.

How To Load Data Directly Into the Excel (Power Pivot) Data Model

In a recent post, I discussed that while Power BI sites allow for data models up to 250 MB, the size of the worksheets portion of any workbook cannot exceed 10 MB. This is because of the fact that while the data model is passed to Analysis Services for processing, the worksheet itself is still subject to the hard 10 MB limit imposed by Excel Services. It is therefore vital to keep a minimum amount of data in the workbook.

The best way to minimize the workbook size is by storing the data exclusively in the data model. There are a number of data import methods in Excel/Power Pivot, including the new Power Query. Doing this is more obvious in some than others.

Power Pivot Import

Prior to Excel 2013 and/or Power Query, importing into the model exclusively was almost default behaviour (yes it was possible to create models from Excel data, but that wasn’t the most common use case). You would invoke the Power Pivot add-in by clicking on the tab, click the manage button, and you would be taken to the Power Pivot management window where you could import data from a wide variety of sources

image

This approach still works just fine. Data imported using this method will be brought directly into the model, which is what we want to achieve. However, Excel 2013 now supports the data model by default, and allows data to be imported to it through several approaches. The Power Pivot editor is included, but is not enabled by default, so it is not immediately obvious that it is an option.

Excel Import

With Excel 2013, you can now use native Excel functionality to get data into the model. The most familiar way to do this is through the regular Excel data import interface. You click on the Data tab in Excel, choose “From Other Sources”, and select your data source.

image

Once done, and the connection is selected, you are presented with the Data Connection Wizard. There is a subtle change to the wizard in Excel 2013 when compared to previous versions, the addition of “Enable selection of multiple tables”.

image

Selecting this option will of course allow you to import multiple tables simultaneously, but what it will also do is to automatically add those table to the data model for you. However if you don’t select this option, there is still an opportunity to add to the data model. After clicking next and creating a connection, you are presented with the Import Data dialog.

image

At this point, if you only selected one table, you can select the option to add it to the model here. If you had chosen the multi table option earlier, this option would be greyed out and selected – you have no choice. However, the most important part of this dialog is the “Select how you want to view this data” section. By default, Table is selected. If it is left selected, data will be imported directly into the model, but it will also be imported as a table into the worksheet. This is a BAD THING. Yes, it’s what users are accustomed to, but instead of just storing the data in a nice tiny compressed data model, you’re also storing it in a big fat worksheet, and it won’t take much data to exceed your 10 MB Office 365 cap. In addition to that issue, by doing this, you also limit yourself to about a million rows of data (Excel limit) instead of the hundreds of millions of rows that the model can handle.

Any of the other options will allow you to import directly into the data model, bypassing the worksheet altogether. These options are your friends. Use them.

Power Query Import

The latest, and most impressive method of importing data into Excel is Power Query. A description of Power Query is beyond the scope of this article, but it allows for relational drill down, complex transformations, nested queries and a host of other options in addition to a wide variety of new data sources (Facebook, Hadoop, etc). It’s also the ONLY way to consume data served up by the on-premises  Data Management Gateway component of Power BI. It provides a very wide array of data import options to the end user.

However, one of the problems with the tool is that it tends to promote the importing of data into worksheets, which is a practice that in my opinion should be strongly discouraged. It is certainly easily possible to avoid this behaviour, as I’m going to demonstrate shortly, but it does require that the user be aware of the importance of this. Importing to the worksheets should be an option, not a default as it currently is. My concern is that far too many people will build a large fancy report that winds up being over the 10 MB workbook limit and publishing it to Office 365, only to have Power BI fail because it’s too large, and then give up in frustration.

To import data directly into the model with Power Query, first click on the Power Query tab in Excel, and then select your data source. After entering the server/database specifics and your credentials if necessary, you are presented with the Power Query Editor dialog.

image

 

After performing any necessary transformations, you are ready to import your data. To do so simply click the Done button. You will then be returned to Excel, which will immediately import your data into a worksheet, and open up the Query Setting window. So – what happens when the data source exceeds 1,048,567 rows of data? Pretty much what you would expect – you receive an error. A pretty explicit one at that.

 

image

In this case, the data is not brought into the worksheet, but the query is still defined, so the model can still be successfully populated by clicking the “Load to data model” link. However, if the source data does fall within Excel’s parameters, it will be brought in to the worksheet.

image

In order to load it into only the data model, we must first deselect the Load to worksheet slider, and then click the Load to data model link. (Note: I have no idea why these are two different control styles). The first option can be selected as the data is loading, so you don’t have to wait for the load to complete. If you select the first and not the second, the data is not loaded anywhere. This is a perfectly valid situation. With Power Query, you can base queries on other queries, or append/merge queries. By doing this, you can load only the end result, and not the intermediate queries.

Once done, the worksheet will display the “Load to worksheet disabled” message in place of the result set.

image

However, opening the Power Pivot management window will display the imported data, and you can work with the model.

In summary, Power Query brings many new capabilities to the data loading and transformation process. However, with great power comes great responsibility. Unless Microsoft makes a change to the default behaviour of the Power Query import process, I’ll be telling anyone that listens to make sure that they always turn off the “Load to Worksheet” option if they’ll be publishing to Office 365.

TechNet Radio Community Corner Interview

Last November, I was interviewed by Sr. Technical Evangelist John Weston on the MVP program, Office 365, Cloud Computing, Business Intelligence, and how these things all tie together. The entire interview was conducted online using Lync online, available in Office365. It’s now online, and can be seen below.

You can see other Technet Radio episodes by visiting the Edge site here