Auditing Arrives in Preview to Power BI

Microsoft today announced the addition of new auditing capabilities within Power BI.  The new features are only available in preview to United States customers for now.

For regulated organizations, being able to audit and analyze who has viewed dashboards or reports is an important requirement.  The new auditing features record every user view, export or change to data within Power BI.  The auditing data is available to export or can be viewed within the Office 365 Security and Compliance Portal.

Image result for office 365 security and compliance portal

Read More

Power BI Embedded Pricing Simplified as Service Becomes Generally Available

Power BI Embedded is a new service available to developers or independent software vendors to allow them to embed Power BI dashboards into their applications.  Unlike the traditional Power BI per user subscription licensing, Power BI Embedded is based on consumption of reports by any user accessing the application.

Power BI Embedded

Microsoft has changed the pricing model as the service becomes generally available.  In the original pricing model, the price was per render of a dashboard or report.  1,000 “renders” was priced in preview at US $2.50.  In the new pricing model, pricing is now per “session”, e.g. each unique user connecting to a unique report.   Each of these sessions is now US $0.05 for a maximum 60 minute session.  If your application has 10 reports and a user accesses all 10 reports, that’s $0.50 charged.

The advantage of this new pricing model is easier methods for calculating the consumption costs – it was difficult to figure out how many renders of a dashboard were needed for an application and on average how many might be consumed by an end user.  With this model, it becomes easier to predict based on the number of users sessions you have in your application.

In addition, Microsoft has made development of reports free with an Azure subscription.  Developers can access their own reports for dev/test at no cost – only end user consumption is charged.

Read More

New Power BI Solution Templates Provide End to End Data Marts

Microsoft has announced today that they are going to provide a series of “Solution Templates” as part of the Power BI service.  The first solution templates are based on Dynamics CRM and Salesforce and feature an end to end solution for extracting data out of the underlying data source, transforming it into a reporting database and then using Power BI to visualize the data.

A Power BI solution template includes three components:

  1. The ability to get data out of the source application with an initial full load and subsequent incremental loads (ie, just new and changed records) to get data into a datamart or data warehouse,
  2. A pre-configured database and Power BI model with extensible schema and calculations for measures and KPIs pertaining to the domain, and
  3. Quick start tools like a default set of Power BI reports that work out of the box and can be extended.

Solution Templates leverage either SQL Server on premise or Azure SQL in Azure to provide the operational data store. 

Read More

Power BI Increases File Size Limit from 250 MB to 1 GB

Microsoft has announced that they are increasing the file size limit for files to 1 GB from 250 MB.  This limit impacts any data source which is not direct query enabled.  It also impacts any Power BI Desktop file that is published to the Power BI service.


We know many of you are doing analysis that involves large amounts of data and have large data models that you want to put into Power BI to share with others. After hearing your requests and seeing what you want to accomplish, we increased the file size limit for both Excel workbooks and Power BI Desktop files to 1 GB.

When you publish a PBIX file from Desktop, publish an Excel file to Power BI service, or upload an Excel or PBIX file through Get Data in the service, you won’t get a file size limit message unless the file is greater than 1 GB. Additionally, the data will continue to refresh as long as the data model size does not grow beyond 1 GB. After that, refreshing the dataset will give an error message letting you know your dataset is too large, and it will fail to refresh until you filter the data model back down under 1 GB.

Read More

Power BI Q&A Support for On Premise Data Sources Coming Soon

One of the most powerful features in Power BI is the natural language based Q&A feature.  It allows end users to pose questions and receive reports, dashboards and answers using natural language queries.  The Q&A query makes suggestions to the end user on what to ask as well as automatically displays the appropriate chart or graph based on the type of data.  End users can also pin any question to their dashboard.


The Q&A support is going to be extended to on premise data sources through the Enterprise Gateway.  Starting with SQL Server 2016 Analysis Services Tabular Models, now in preview, end users will be able to use the Q&A feature to access data on premise in the same as currently supported with cloud data sources.  Additional support for on premise data sources such as SQL Server, Oracle, SSAS, etc. is coming soon.

Read More

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

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

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