Enterprise Data Management Gateway Coming to Power BI

One of the weak links in the hybrid Power BI solution is the Power BI Personal Gateway.  While it works as a bridge to provide data transfer from your on premise data sources to Power BI, this piece of software is a client software solution that installs on a workstation. 

For most enterprises, this is not an appropriate model – you don’t want your enterprise BI solution failing refreshes because Bob the data analyst installed the gateway on his laptop and then went on vacation.

Read the official gateway installation page and you’ll see what a weak link this arrangement is for enterprise level data refreshes:

On a laptop computer – In order for a scheduled refresh to occur, the Gateway needs to be up and running. Laptop computers are usually shut down or asleep more than they’re running. If you install your Gateway on a laptop, be sure to set your scheduled refresh times for when the laptop will be running. If it isn’t, the refresh will not be attempted again until the next scheduled refresh time.

On a desktop computer – Not many issues here. Just make sure the computer and the Gateway is running at your scheduled refresh times. Many desktop computers go to sleep, scheduled refresh cannot occur if it’s asleep.

The gateway is not highly available – it runs on a single client workstation with no ability to failover if that machine crashes.  In addition, if you change your password, it will also cause the refresh to fail until to update the password on your gateway.

Microsoft thankfully has just announced in an upcoming release an “Enterprise Gateway” that will hopefully run on a server using a proper service account.  Not many details have been released yet but this will hopefully provide some confidence that Microsoft’s support for hybrid BI scenarios is moving in the right direction.

Read More

Vision for Microsoft Business Intelligence Starting to be Integrated with SQL 2016

Over the past several years, MIcrosoft has had several reporting and business intelligence streams working somewhat in siloes – we have currently DataZen, Power BI, Excel Services, Power View, SSRS and various other dashboarding components in Microsoft’s other products (Visio, CRM, SharePoint, etc.).  Each tool provides different benefits and is targeted to different audiences.

With the latest announcements about SQL Server 2016, Microsoft is starting to provide a more cohesive and integrated vision that integrates their various reporting tools into a common platform.  Some of these key changes were announced at PASS this week and others have already been released as part of CTM versions of SQL Server 2016.

Power BI for the Cloud, SSRS for On Premise

Power BI will continue to be a 100% dedicated cloud service but SQL Server Reporting Services is being revamped to be the equivalent platform on premise.   You will be able to build SSRS reports and deploy them to Power BI and develop Power BI and publish them to SSRS.

SSRS will be Responsive

The new version of SSRS will be designed for responsive to support HTML 5 based browsers on phones and tablets.

One Mobile App for BI

Microsoft is going to provide a unified mobile experience based on DataZen technologies.  The mobile app will be able to deliver both Power BI and SSRS reports and dashboards through a single user experience.

Read More

SQL Server 2016 will Support R in the Database

Microsoft announced that SQL Server 2016 will support R analytics processing within the database as part of the new release.  Leveraging its acquisition of Revolution Analytics, a commercial distribution of the R language, SQL 2016 will allow you to run R scripts within TSQL.

To start using the new feature, you’ll need CTP3 for SQL Server 2016, Revolution R Enterprise 7.5.0 and Revolution R Open 3.2.2 for Revolution R Enterprise 7.5.0.

Read More

Creating a Custom Theme For SharePoint

One of the simple branding changes you can make in both SharePoint 2013 or SharePoint Online is to create a custom “composed look” that reflects your branding and fonts.  Creating a new theme is surprisingly easy and makes a big difference in the basic appearance of SharePoint.

A SharePoint “composed look” is a theme that combines the following into a package:

  • Colors
  • Fonts
  • Master Page
  • Background Image

SharePoint 2013 and SharePoint Online come with a set of themes such as this one:


Creating a new look and feel for your site involves uploading a custom color, font file and background image as needed to provide the user the ability to customize their site to reflect your branding.

Adding a Custom Color Palette

A color palette in SharePoint is an XML file that defines the HTML colors for each UI element on the page.  The easiest way to create an SPColor file is to use Microsoft’s free color palette tool.  The tool brings up the default color scheme and you can then supply a new set of colors.

Once you have a custom color file, upload it to the Theme Gallery in the 15 folder. 

Adding a Custom Font Scheme

A font scheme in SharePoint is an XML file that defines the font attributes for each UI element on the page. 


For standard web fonts such as Helvetica, Times Roman, Arial, Verdana, etc. you can just type in the name of the font into the typeface values.  It gets a bit trickier if you want to use a custom web font.  In order to do this, you need to upload the EOT, WOFF, TTF and SVG files to SharePoint and then reference them in the type face definition.

I used Google’s Open Sans as a custom font by uploading from GitHub the web font files into the SharePoint theme file.  Open Sans has bold, semi-bold and regular variations so you can use a single font family for headings, body text, etc. or you could use multiple fonts for each type of text in the UI.

In the typeface definition, you can provide also an image that shows a preview of the font – this image is used when showing the drop down menu for selecting the font scheme.  If you leave these entries blank, SharePoint just provides text content instead of a render of the text.


One important note: if you don’t get these references right, the entire theme won’t be loaded and you won’t see it available in the menu.  If you notice this, check the references for all your fonts and ensure the URLs have been supplied correctly.

Adding a Background Image

You can add a background image simply by uploading an image to the theme library and referencing it in your composed look.

Selecting a Master Page

Describing how to create a new master page is beyond the scope of this post, so let’s select from one of the existing master pages, either Seattle or Oslo. 

Putting it all Together with a Composed Look

Once you have your components ready, you create a new composed look in the Composed Look gallery.  This is a basic SharePoint list that references your theme components.  You can also specify the display order – I always put my custom themes’ display order as below 10 so they appear at the top of the list.


Once you have picked your theme, you’ll notice that your font scheme and color palette are selected by default. 

Now you have a custom theme that reflects your colors, fonts and background image. 

Apply Themes to Sub Sites

In order to apply your composed look to a sub site, you have two options.  The first option is to create the list item for the composed look in each sub site.  This is a pretty manual exercise but you could also save this as part of a site template potentially to save the manual steps.

An alternative approach is to use publishing sites.  One of the unique features of publishing sites is they have the ability to inherit their theme and master page from their parent site.  This is only available when you have activated publishing as a feature and use the publishing site template.


Read More

Power BI Now Supports Custom Visualizations!

Microsoft has just announced that you can incorporate your own custom visualizations into reports.  These custom visualizations can be completely private to your own organization or you can publish them out to the community through the new Power BI Visuals Gallery.


Visualizations are built using TypeScript using D3.JS as the core visualization framework.  The Power BI visualization framework has gone completely open source and is available on GitHub.  Here is a video to explain how to build a visualization.

Read More

Azure Search Now Supports Lucene Fuzzy Searching in Preview

Azure Search provides a cloud based text indexing engine – this blog uses Azure search for its search engine.  The underlying indexing engine is lucene.net wrapped in a cloud service.  The search query API is provided through a REST API and a .NET SDK.   

In the latest preview version of the Azure Search API, you can now execute Lucene queries that enable some new features for querying the search index:

  • Fuzzy Search: finds terms that are similar.  For example, “blue” is similar to “blue”, “blues”, and “glue”.
  • Proximity Search: finds terms that are in proximity but not right beside each other in a phrase.  For example, searching for “azure search” could return “azure search” but also “azure uses search” or “azure indexed by search” because azure and search are close to each other.
  • Term Boosting: allows you to boost one term over another in your results.  For example the search query “rock^2 music” will boost results containing the word rock higher than results containing the word music.
  • Regular Expression Search: allows for searching based on regular expression. 
  • Wildcard Search: allows for searching for terms that start with a specific query but then follow with multiple additional characters.  For example, “star*” would find documents containing “starlight”, “starship”, “starman”, etc.

In order to leverage these new APIs, you have to use the 2015-02-28 Preview API.  In addition, the .NET SDK doesn’t yet support this release, so you’ll have to construct your own REST calls.

Read More

Azure SQL Now Supports Full Text Search

Azure SQL now supports Full Text Search as part of the V12 platform.

Full-Text Search is now generally available in Azure SQL Database V12. For applications that manage and store character-based data in Azure SQL Database, Full-Text Search provides fast, rich search functionality integrated into the database platform. Full-Text Search is available in all service tiers in Azure SQL Database V12 through familiar TSQL syntax and Microsoft SQL Server tools. In addition to preview capabilities, the feature supports .xml doc types and self-service diagnostic experiences through XEvents. Users can easily troubleshoot and have visibility into issues such as indexing errors or unsupported doc types. This optimizes the search experience and brings it closer to the on-premises capabilities of SQL Server.

Full text search allows for indexing of text content with a SQL database, stored in traditional database records.  Columns such as char, varchar, nchar, nvarchar, text, ntext, image, xml, or varbinary(max) are supported for full text searching.  Once the text is indexing, searches can be performed using TSQL commands.

There are some limitations compared to the full SQL 2014 version:

  • No support for installation or use of third party filters, including Office and .pdf.
  • Customers cannot manage service settings for fdhost, all configurations are being managed by the service.
  • Semantic search, thesaurus and search property lists syntax is not yet enabled.

Read More

My First Office Mix Presentation Published!

I was invited to prepare a 45 video for the Collab 365 conference.  I designed a power point presentation on the topic, “Preparing the Shift to Office 365” and decided to use Office Mix as the tool to create the video and publish it to the web.  You can see the final result here.


Office Mix is a new PowerPoint add-in that allows you to take a PowerPoint presentation and turn it into interactive content.   It is currently in preview.   It allows you to take an ordinary PowerPoint presentation and add in audio, screen recording, full screen video and learning quizzes to create interactive presentations.  When you’re complete, you can publish your interactive presentation to the Office Mix web site and share it to either a private or public audience.  You can also publish your presentation to a video file as well. 

Downloading the Add-in

In order to use Office Mix, you will need an account (either a Live ID, Office 365, Facebook or LinkedIn account will work) and the Office Mix Add-in.  You download the add-in and install it on each client that needs to create content.

The one challenge with the download is that I found that the 64 bit add-in doesn’t seem to install properly.  In addition, I have not yet tried the add-in with Office 2016.

Adding Audio and Video

Adding audio and video is really easy.  You just insert the audio or video through the add-in and make sure that the settings are that the files play automatically when you hit the slide.  Office Mix converts these files into streaming files when published online or incorporates into your video file when published locally.

High quality audio and video definitely makes a big difference – given that the publishing process can handle up to 1080p HD video.  Split your audio and video files so that they are attached to each slide.

Publishing the File

There are two publishing channels – online and to video.  Publishing online converts your slides to a set of PNG files and uploads the embedded audio and video.  It’s a reasonably quick process – for my 45 minute video it took about 15-20 minutes.

For publishing to video, you choose the size and file format for your video.  Note that publishing to a 1080p video will take quite a bit of time and the file sizes are quite large.  My 45 video was more than 1 GB and took about 2 hours to render into the video file.

Test Your Transitions and Animations

When you publish to Office Mix, you may find that your transitions or animations don’t work as expected.  I found most basic animations and transitions worked but a few did not.  For example, I have a slide that wipes in a number of items and the wipe didn’t work in Office Mix, resulting in all of the items showing up at the same time. 

Overall, this is an exciting and very easy to use publishing technology.  In building my presentation, I found it very easily to assemble all the components and publish it to video and Office Mix.

Read More