Express Route Now Supports Power BI

Microsoft Express Route is an Azure service that allows you to create private network connections between your data center and Azure to improve performance and network reliability compared to going through the public internet.

Microsoft will now support Power BI through Express Route, enabling scenarios where Power BI needs to query or fetch data from on premise data sources through a high speed network connection.

Read More

Power BI Embedded Pricing Now Available

Microsoft has released pricing for Microsoft Power BI Embedded.  Power BI Embedded is the new version of Power BI targeted to application developers wanting to leverage the Power BI service directly in their applications.

Power BI Embedded

Unlike the regular Power BI subscription model which is based on the number of users, the new Power BI Embedded is based on the number of views.  Current pricing is $2.50 US / 1000 renders, e.g. 1000 views of a dashboard or report. 

Read More

Globally Distributed NoSQL Databases Coming to Azure DocumentDB

Microsoft has announced several improvements to the Azure DocumentDB service that allow it to act as a globally distributed NoSQL database.

Today we are pleased to introduce global databases for Azure DocumentDB. Global databases allow you to replicate data across multiple Azure regions to achieve low latency access with well-defined data consistency while offering clear availability SLAs. Global databases are designed to meet the needs of planet scale applications such as IoT systems serving globally distributed devices or internet scale web applications that deliver highly personalized experiences.


In addition to the addition of globally distributed databases, Microsoft is also providing a layer to enable support for MongoDB database drivers.  If you have an application that uses MongoDB today, you can move the database to Azure DocumentDB and your existing MongoDB drivers will work but with some added benefits that are provided by Azure DocumentDB.

These new features are currently in preview only.

Read More

Integrated Power BI Visualizations for Applications Coming Soon with Power BI Embedded

Microsoft announced that they will provide a version of Power BI that allows for integration of the Power BI service with custom applications.  The service will be called Power BI Embedded and will enable scenarios where application developers would like to harness the Power BI visualization service within their application stack. 

Power BI Embedded

Unlike the current subscription model which charges per user, Power BI Embedded will be charged on a per usage basis to enable application developers to manage their own users and to ease integration with Power BI as a pure service.  Pricing has not been released yet.

Read More

Row Level Security Coming to Power BI

Microsoft has announced that row level security will be available as part of the Power BI service with a preview version available in March.

This means that within your Power BI models, you’ll be able to define what users can see based on their roles and restrict access to data based on permissions.  For example, you could create a sales dashboard where each sales person can only see their own opportunity records.  You could create a dashboard for patient data that is restricted by customized views.

Read More

New SQL Server 2016 RC1 Released with SSRS Updates

Microsoft has just released RC1 of SQL Server 2016 and with it a number of SSRS enhancements.  Here are a few of the key updates to reporting services – see this blog post for the full list of enhancements.

Web Portal Replaces Report Manager

Leveraging the DataZen technology the acquired, Microsoft has been working on a new modern HTML 5 based web portal for displaying dashboards and reports.  It is now “feature complete” and replaces the old report manager.


One of the latest features in RC1 is search – you can enter in a query and be provided all the KPIs, reports and dashboards matching the search terms.

New Custom Branding

The new Web Portal can be branded to reflect your organization’s fonts, colours and logo. 


Easy Embedding of Reports into other Apps

All reports are now embeddable through an IFrame – for example, in order to embed into a SharePoint page.  You just add the rs:Embed=true query parameter to the end of the URL and it will strip away all of the SSRS chrome to make it optimized for inserting into another page.

Newly Modern Paginated Reports

SSRS reports no longer need to look like they were created in the mid-90s!  Styling is now much easier and is now modern design by default. 

Read More

Using Power BI to Visualize WordPress Blog Posts runs on WordPress, and I have been working with Power BI to show some analytics on my posting behavior.  Fetching the data can be done using a JSON plugin (I use the JSON API plugin but there is also now a new WordPress REST API plugin) which will provide all your blog posts as a JSON data source.  

Accessing WordPress Blog Posts

The first step is accessing the JSON data – in the latest version of Power BI desktop there is a dedicated JSON data source that you can provide the URL for your JSON feed and it will fetch the data into PowerQuery for further manipulation.  Here is what the results of my query look like in Power BI Desktop.


As a table, this just provides the count of the posts but not the posts themselves – not very interesting.  However, if I click on the List of posts then I can drill down into the JSON and fetch the list of posts themselves.

Each post is represented as a record.  You can convert this now to a table by clicking on the “To Table” function and then clicking on the expand icon to the right of column 1.  When you do this you are prompted to select the fields that you want to select as part of your table.


If you click Close and Apply this will load all your blog posts into a table. 

Adding Date Filtering and Drill Down

Each post as a posting date.  We can create a calculated table that represents all the possible dates and add in year, month, and day columns that are nicely formatted to support drill down by year, month or day.

Start by creating a calculated table by clicking on “New Table” with the following formula:

DateTable = CALENDAR (MINX(‘Blog Posts’, [date]), NOW())

This will create a table of all the possible dates from the earliest blog post to today.

Now we need to add a bunch of calculated columns to represent year, month, day, day of the week, etc.  Here are some formulas that you can use – each line is a calculated column that you add to the table.

DateTable = CALENDAR (MINX(‘Blog Posts’, [date]), NOW())

Day = Format([Date],”DDDD”)

DayofMonth = DAY([Date])

DayofWeek = WEEKDAY([Date],2)

ISOWeekofYear = IF ((([OrdinalDate] – [DayofWeek] + 10)/7) >= 53, 1, TRUNC(([OrdinalDate] – [DayofWeek] + 10)/7))

LongDay = Format(DateTable[Date], “dddd”)

Month = FORMAT([Date], “MMM”) & ” ” & [Year]

MonthDate = Date(DateTable[Year], DateTable[MonthofYear], 1)

MonthofYear = MONTH([Date])

OrdinalDate = DATEDIFF([Year]&”,1,1″,[Date],DAY)+1

QuarterofYear = ROUNDUP (MONTH([Date])/3,0)

Year = YEAR([Date])

In addition, you will need to change the sort column for columns such as LongDay to the integer or date representations so that your columns sort in the appropriate order and not in alphabetical order.

Once you have your date table set up, you can create a relationship between the date column in the blog posts table and the DateValue column.  Depending on the format of your original date column, you may need a calculated column that reflects the identical format so that the ids match.  In my case, I created a calculated column called “DateOnly” that has the same formula as the DateValue column and linked these together through the relationship management dialog.


Once you have this set up, you can now create date driven visualizations such as the number of posts per month or per day of the week!



Adding Metadata Filtering

One of the key features of a blog are tags and categories that act as metadata for filtering.  The JSON provides these within its structure so can we extract the data and use it for creating some dashboards by category or tag?

If we go back to our original Power Query, we can create a new table that contains a denormalized set of keys that provide the link from the blog post ID to the tag values (the same approach will work for categories, authors, etc.).   When we get the list of posts, we do the same as above and expand the records but in this case we only need two columns – the blog post ID and the tags.


Then, we need to expand the list of tags so that we can fetch the tag ID and the title of the tag for each record.


Our new table should look something like this:


Once we load up this as a table, we can now create some interesting dashboards based on the tag frequency.  For example, here is the frequency of posts per tag:


Here is the frequency of tag use by month as a visualization.


Here is a comparison of the use of the tag “Azure” with the use of the tag “Power BI” over time.


Once you have the data there are lots of possible ways to explore and analyze the data!

Read More

Power BI Custom Visuals Will be Sandboxed using IFrames

Microsoft released the ability to create custom visuals for Power BI a few months ago.  These custom visuals allow developers to build new visualization widgets for both private organization specific use or public use via the custom visuals gallery.  In the gallery today, there are more than forty different visuals that you can add to your Power BI dashboard.

image        image

In the current model, the custom visuals reside in the same code space as the main Power BI service which can lead to some interesting conflicts and errors when multiple organizations are providing their own JavaScript and CSS.  As Microsoft moves out of preview, they have now stated that custom visuals will be “sandboxed” which means that each codebase will execute in its own IFrame.  This will prevent collision of code coming from multiple developers and contain any bugs or errors in the visual.

Sandboxing runs your visual’s code in a low privilege iFrame with no domain. You will not be able to assume access to Power BI styles (CSS), Power BI fonts, or undocumented Power BI APIs. We will inject all the required interfaces into the iFrame, including your visual’s code, and then marshal data points and required settings across the iFrame boundary. Since the iFrame has no domain, you will not have access to cookies or local storage. Finally, how you debug your custom visual will change a little, which I’ll cover below.

Having worked with SharePoint add-ins and app-parts for years, which also use an IFrame container to render the widget, one of the key challenges with this approach will be ensuring the overall layout is fluid and responsive and works successfully on a mobile device.  This is a fundamental challenge with having all these IFrames on the page – each one has to be resized dynamically when the page layout changes. 

Read More

SQL Server 2016 is Coming to Linux!

Microsoft has just announced that SQL Server 2016 is coming to Linux!

SQL Loves Linux_2_Twitter (002)

Microsoft will be releasing the core database platform on Linux in preview now with an expected release in mid 2017.

There is no word on pricing just yet and it appears for now that SSIS, SSRS and SSAS are not part of the solution – just the core database will be provided.

Read More

Latest Version of Power BI Desktop Provides a Number of Enhancements

Microsoft has just launched the latest version of Power BI Desktop, the tool for designing reports and dashboards for the Power BI service.  The latest version can be downloaded here.

The list of new features and enhancements is too long to provide in full here – see this blog article for the complete list.  Here are a few of the highlights.

View Data Behind Visual and Export to CSV

You can now interact with any visual in Power BI and go into “focus mode” which maximizes the visual.  Within this mode, you can then view a split screen that shows the underlying data in a table format and export the table to CSV.

Plot Maps with Longitude and Latitude

You can now plot maps using raw Long and Lat data instead of a location text field.

Improved Support for KPIs

There are a number of new features related to KPIs including:

  • KPI consumption from SQL SSAS multidimensional models
  • Support for display folders when using SSAS exploration mode
  • New KPI Visual

The new KPI visual provides the ability to see a trend line over time as well as a current indicator.


Support for User Defined Hierarchies

One of the key missing features in Power BI that existed in Power Pivot in Excel, you can down create your own drill down hierarchies.  Similar to Power Pivot, you can create a hierarchy and the add fields from the model to the hierarchy to support drill up/down within your models.


Live Query Support for Oracle and Teradata

Similar to existing support for live query of SQL databases, Power BI will now support live query (e.g. not cached in the report) of Oracle and Teradata data sources.


Read More