Microsoft Launches new RAD Designer for the Cloud: PowerApps

For many many years, Microsoft has provided light weight tool sets for building pseudo applications.  These have included Microsoft Access, Lightswitch, Silverlight, InfoPath Forms, and various other technologies targeted to developing line of business applications without the need of “custom” development.

The latest platform was announced today – PowerApps is a brand new cloud based platform that enables organizations to develop applications that leverage cloud services such as Office 365, Dynamics CRM, SalesForce, DropBox, etc.  and present data through rich user interfaces already responsive for various device form factors.  

There isn’t much information out yet but it looks like the service is based on Project Sienna which has some more extensive documentation. 

Here are some typical applications you could build with this platform.


Catalog Browser Applications


Content Browser Applications

Read More

Latest Power BI Desktop Update Provides New Support for Multidimensional SSAS, SAP HANA, Azure Data Lake, and Marketo

The latest version of Power BI Desktop provides support for key new data sources. 

The new data sources include:

  • Direct query support for Azure SQL, Azure SQL DW and SQL Server on premise
  • Direct query support for SSAS multidimensional models
  • Direct query support for SAP HANA
  • Integration with Azure Data Lake
  • Integration with Marketo

These are still in preview or beta and in most cases cannot be published yet to the Power BI service.  However, given the pace of releases with Power BI expect these to be production ready very quickly.

Read More

Power BI Desktop Now Supports Importing Excel and SSAS KPIs

One of the key missing features in Power BI is support for target or key performance indicators.  Microsoft just announced and provided in preview support for consumption of existing KPIs from Excel.  You cannot create KPIs directly in Power BI Desktop just yet.

Here are the key steps to enable this new feature.

Step #1: Download the latest version of Power BI Desktop

You’ll need the latest version of Power BI Desktop to leverage the new feature.  You can download it here.

Step #2: Enable Preview Features in Power BI Desktop

Under Options –> Preview Features, the Use KPIs from Power Pivot or SSAS Tabular.


Step #3: Create an Excel Power Pivot Model with a KPI

Using Power Pivot, create a KPI.  In my sample, I create a KPI for average life expectancy for the US to compare with the actual ALE for each county. 


Step #4: Import Excel Workbook Contents

In order to pull in your KPI, you have to import Excel Workbook Contents.  Note: if you try to go through the usual “Get Data” option it doesn’t pull in KPIs, just tables and columns. 


Step #5: Display Your KPI

Once you have your KPI loaded, you can display it in a Table, Matrix, Card, or Multi-Row Card. 


Read More

Power BI Desktop Now Supports Live Query to Azure SQL and SQL Server

Power BI Desktop now supports Live Query to two new data sources – Azure SQL and SQL Server on premise.  Previously, the only supported Live Query data source was SSAS on premise.

This is an important improvement for a key reason I noted in a previous post – there is 250 MB on Power BI Desktop files when you publish them to the Power BI service.  If the data source isn’t “live”, Power BI Desktop fetches the data and saves it within its file format.  If you run a query that requires more than 250 MB to be cached then you cannot publish the Power BI Desktop file.

In order to leverage this new features, you will need the latest Power BI Desktop application installed.   In addition, you have to turn on the feature under Options –> Preview Features.


Now when you query these data sources, you’ll see the following dialogue box:


If you select DirectQuery, the query executes against the live database instead of caching the data locally. 

The key impact is that you have a direct connection to the database so you can do queries against large datasets and still publish the Power BI Desktop file to the Power BI Service because data is no longer cached locally. 

Read More

Admin Reporting and Management Enhancements Coming to Office 365

There are some exciting new enhancements coming to Office 365 for the Administrators managing their tenants.  The new Admin Center provides a more intuitive an consolidated list of common functions.


Key flows that have been improved are provisioning users, resetting passwords, and managing licenses.

In addition there is a new set of usage dashboards built into the the new Admin Center that provide usage stats for email, OneDrive files and SharePoint content.



Coming soon will also be the Office 365 Content Pack for Power BI.  This will push all your Office 365 usage data to Power BI for advanced dash-boarding, slicing and combining with other data sources in your organization.


Harnessing this data could allow you to see who in your organization is adopting the service and who needs some support. 

Read More

What is Machine Learning and Predictive Analytics? A Real World Example

Azure Machine Learning is Microsoft’s machine learning studio.  It provides a workbench for analysts to perform data analysis including applying predictive analytics and machine learning algorithms.  

One of the key uses of Machine Learning is finding correlations in data and using the relationships between different indicators to provide predictive power.  Here is an example scenario I built in Azure ML.

The Scenario

I found a dataset that describes a set of Community Health Status Indicators by county for the United States.  It provides a set of health rates such as homicide, cancer, obesity, suicide, etc.  In addition, it provides a set of demographic indicators such as the size of the county, the population density, the poverty rate and the population breakdown by race.

Creating a Dashboard

I created a Power BI Dashboard that summarizes some of the key indicators. 


While this an interesting dashboard, it doesn’t tell us what factors influence key metrics like Average Life Expectancy and with dozens of potential indicators it’s not clear which ones really are key drivers and which are less important. 

Finding Predictors with Azure ML

What if we could determine the indicators that predict Average Life Expectancy?  We could then understand the factors that impact this key metric and put them on our dashboard. 

Using Excel, I pulled the several indicator files together into a single CSV file that combined all the possible indicators together.  I then loaded this file into Azure ML Studio.


I then use the Project Columns module in Azure ML to pick out a number of potential columns that could impact Average Life Expectancy. 


Which Features Have Predictive Power?

Azure ML provides a number of methods for analyzing features and determining which of them have a strong predictive relationship with the indicator you are trying to predict. 

One of the modules in Azure ML Studio is the Filter Based Feature Selection which provides a method for filtering the number of columns based on statistical analysis.  You set the target for your prediction (in this case Average Life Expectancy) and the module goes through your list of features and finds the ones with the strongest correlations. 

In reviewing the output, here are some of the features that have the strongest correlation with Average Life Expectancy.


ALE is obviously the top feature since it is the one we’re trying to predict.  Features such as the number of people under 18, the poverty rate, lung cancer rate and so on seem to be the best candidates for predicting Average Life Expectancy.

How Predictive is our Model?

In order to test the predictive power of our model, we need to apply some algorithms to see if we can use the columns we selected to make an accurate prediction of Average Life Expectancy.  Azure ML Studio provides a number of industry standard algorithms for such analysis.  In this case, because we are trying to predict a variable value (e.g. could be any number) this lends itself to using regression algorithms which try to determine the equation that can provide a predicted Average Life Expectancy value based on our set of features.  Using machine learning, the algorithms try a number of feature combinations using different weightings to try to find the best fit equation that aligns to the actual results from the dataset.  We can then test the accuracy of the equation using our dataset as well.

In order to training dataset and a testing dataset, we can split our original list of 3142 rows in half, using 50% for training the dataset and using 50% for testing and evaluation.    In Azure ML, you can use the Split Data module to do exactly this.  We can use Linear Regression as our algorithm and feed it through the training model to calibrate our algorithm using machine learning.  Once this has been done, we can then score and evaluate the model to test its predictive power.


When you run this model, you get the following results in the evaluate model.


The model is a reasonably good but not excellent predictor of Average Life Expectancy.  If you look at the Coefficient of Determination, the closer this value is to 1, the better the predictive power.  In this case a 0.60 is a reasonably good score – a 0.90 or greater would be considered excellent.    If you look at the Error Histogram, this is very illustrative – this shows the error variability.  In this experiment, 48% of the results were within 0.0014 – that’s very good for an Average Life Expectancy of between 70-80 years old.  Another 30% were off by less than a year. 

However, there are a few outliers in the data where the algorithm was off by more than 3 years.

Revising Our Dashboard

What does this analysis tell us?  A few important conclusions are worth noting.

The first is the key factors that impact Average Life Expectancy seem to be:

  • Births with Mothers Under 18
  • Poverty
  • Lung Cancer
  • Low Birth Weight
  • Premature
  • Births with Mothers Under 40
  • % Black Population
  • Very Low Birth Rate
  • Infant Mortality
  • % White Population

If we’re interested in Average Life Expectancy than having these on our dashboard would provide a good explanation. 


In addition, we could use the predictive model to forecast Average Life Expectancy where the data is missing as long as we have these other factors.   Using Azure ML, you can turn your experiment into a web service whereby you would submit the input columns and the service will generate the predicted value based on the model.  This turns your experiment into an engine that can be harnessed to process future data as it arrives.

Read More

Latest Azure, Cloud and Enterprise Visio Stencils Just Released

Microsoft has just released version 2.3 of their visio stencils for Azure.  You can download the package here.


The new release contains some updated symbols for some of the latest Azure services including:

  • Azure IOT Hub
  • Azure Datalake
  • Azure VPN Gateway
  • Service Fabric
  • Azure DNS
  • Azure Load Balancer
  • Resource Group

In addition, they have now added Microsoft Intune and System Center symbols.


Read More

Power BI Has a 250 MB File Size Limit

If you try to use Power BI Desktop to create a dashboard, Power BI imposes a hard limit of 250 MB as the maximum file size.  In my scenario, I had several 500 MB csv files that I used to create a dashboard.  Power BI Desktop works fine but when you try and publish to Power BI you are stopped because the Power BI Desktop file is more than 250 MB.


I tried moving the files to Azure Blob Storage but that won’t work either because when you run the query to load up the files, it downloads the content back into your local Power BI Desktop file.

The only way around the limit seems to be to use a data source that queries in real time (“Live with direct query”) instead of being cached through Power BI Desktop.  The options currently are Azure SQL, Analysis Services Tabular databases on premise, Microsoft Stream Analytics, SQL Azure Data Warehouse or Spark on HDInsight.  However, the ONLY option for a live connection in Power BI Desktop is SSAS and most recently in preview SQL Server and Azure SQL.

Read More