How to embed Power BI Reports into the Microsoft Identity Manager Portal

About seven years ago at a conference in Los Angeles I attended I remember a session where a consultant from Oxford Computer Group gave a presentation on integrating Quest Identity Manager (now Dell One Identity Manager) with the Forefront Identity Manager Portal. I’ve recently had a requirement to do something similar and Carol pointed me in the direction of her experiments with doing something similar based off inspiration from that same presentation/session.

Well it is now 2017 and FIM and SharePoint have all moved through a few versions and doing something similar has changed. So now that I’ve got it working I thought I’d share how I’ve done it, and also to solicit any improvements. I’ve done this with SharePoint 2013.

Overview

In this post I’ll detail;

  • Publishing a Power BI Report
  • Creating new Microsoft Identity Manager Navigation Bar Resources
  • Embedding as an IFrame the published Power BI Report in the Microsoft Identity Manager Portal so that it appears like below

Pre-requisites

Obviously to follow this verbatim you are going to need to have a Power BI workspace and a Power BI Report. But you could embed any page you want to test it out.

You’ll also need;

Publish a Power BI Report

In Power BI select a Report you are looking to embed in the MIM Portal. I selected License Plans under Reports from my Power BI Worksapce.

From the File menu select Publish to Web.

Select Create embed code.

Copy the link to your report somewhere where you can retrieve it easily later. Don’t worry about the HTML line or the size.

 

SharePoint Designer

Download and install with the defaults SharePoint Designer 2013 from the link above. I’m using the 64-bit version. I installed it on my Development MIM Portal Server. I’m using 2013 as my MIM Portal is using SharePoint 2013 Foundation (with SP1).

Once installed start SharePoint Designer and select Open Site.

Enter the URL for your MIM Portal and select Open.

Note: In order for SharePoint Designer to successfully load your MIM Portal Site, the URL you provide above must be in your SharePoint Alternate Access Mappings. If it isn’t you will probably get the error “The server could not complete your request. For more specific information, click the Details button.”

And in your Windows Application Event Log Event ID 3 – WebHost

WebHost failed to process a request.

 Sender Information: System.ServiceModel.ServiceHostingEnvironment+HostingManager/42194754

 Exception: System.ServiceModel.ServiceActivationException: The service '/_vti_bin/client.svc' cannot be activated due to an exception during compilation.

 

Select Microsoft Identity Management, then All Files. You should then see a list of all the files in the MIM Portal website.

Locate the aspx folder, right click on it and select New => Folder. Create a new folder under the aspx directory named ‘reports’.

Right click on your new Reports Folder and select New => ASPX. Create an aspx file named reports.aspx.

Repeat to create another aspx file named report.aspx.

 

Click on the Reports.aspx file form the main pane and put the following contents in it overwritting everything else. Select Save.

<%@ Page Language="C#" %>
<html dir="ltr">

<head runat="server">
<meta name="WebPartPageExpansion" content="full" />
<title>Reports</title>

 window.open("report.aspx",target="_self")


</head>
<body/>
</html>

Click on the report.aspx file and replace the contents with the following and select Save.

Replace <yourreportlink> in https://app.powerbi.com/view?r=<yourreportlink&gt; with your Power BI link.

<%@ Page masterpagefile="~masterurl/custom.master" Title="Reports" language="C#" inherits="Microsoft.SharePoint.WebPartPages.WebPartPage, Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" meta:progid="SharePoint.WebPartPage.Document" UICulture="auto" Culture="auto" meta:webpartpageexpansion="full" %>
<%@ Register Tagprefix="SharePoint" Namespace="Microsoft.SharePoint.WebControls" Assembly="Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %> <%@ Register Tagprefix="Utilities" Namespace="Microsoft.SharePoint.Utilities" Assembly="Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %> <%@ Import Namespace="Microsoft.SharePoint" %> <%@ Register Tagprefix="WebPartPages" Namespace="Microsoft.SharePoint.WebPartPages" Assembly="Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>

<asp:Content ContentPlaceHolderID="PlaceHolderTitleBar" Visible="true" runat="server">
</asp:Content>

<asp:Content id="content1" ContentPlaceHolderID="PlaceHolderMain" runat="server">
https://app.powerbi.com/view?r=

</asp:Content>

MIM Portal Navigation Resources

Now we need to create the MIM Portal Navigation Resources to link to our new files.

In the MIM Portal Select Navigation Bar Resources. Select New.

 

Provide a Display Name, Description and select Next. Ignore Usage Keyword for now. More on that later.

Make the Parent Order 8 to have it at the bottom of the Left Nav bar. Order is 0 as this is going to be our Group header. Select Next.

Provide the path to the Reports.aspx file  ~/IdentityManagement/aspx/reports/reports.aspx Select Next.

Provide the Localised Display name, select Finish and then Submit.

Repeat, this time for linking to ~/IdentityManagement/aspx/reports/report.aspx and name it Licensing Report or whatever makes sense for your report. Also make the Order 1 so it nests under Reports.

Perform an IISReset.

Refresh you MIM Portal Page and you should see your new menu items on the left Navigatin Bar at the bottom.

Click on Reports and your Licensing Report will auto-magically load. Same as if you click on Licensing Report. You can now add as many reports as you need. And change which report you want to be the default by updating the Reports.aspx file in SharePoint Designer.

You will probably also want to limit who see’s what reports. You can do that through Usage Keywords and Sets etc. By default as described here the reports will only be visible to Administrators.  Details to get you started on changing who can see what can be found here.

Let me know if you have any improvements.

 

Follow Darren Robinson on Twitter

How to use a Powershell Azure Function App to get RestAPI IoT data into Power BI for Visualization

Overview

This blog post details using a Powershell Azure Function App to get IoT data from a RestAPI and update a table in Power BI with that data for visualization.

The data can come from anywhere, however in the case of this post I’m getting the data from WioLink IoT Sensors. This builds upon my previous post here that details using Powershell to get environmental information and put it in Power BI.  Essentially the major change is to use a TimerTrigger Azure Function to perform the work and leverage the “serverless” Azure Functions model. No need for a reporting server or messing around with Windows scheduled tasks.

Prerequisites

The following are the prerequisites for this solution;

  • The Power BI Powershell Module
  • Register an application for RestAPI Access to Power BI
  • A Power BI Dataset ready for the data to go into
  • AzureADPreview Powershell Module

Create a folder on your local machine for the Powershell Modules then save the modules to your local machine using the powershell command ‘Save-Module” as per below.

Save-Module -Name PowerBIPS -Path C:\temp\PowerBI
Save-Module -Name AzureADPreview -Path c:\temp\AzureAD 

Create a Function App Plan

If you don’t already have a Function App Plan create one by searching for Function App in the Azure Management Portal. Give it a Name, Select Consumption Plan for the Hosting Plan so you only pay for what you use, and select an appropriate location and Storage Account.

Register a Power BI Application

Register a Power BI App if you haven’t already using the link and instructions in the prerequisites. Take a note of the ClientID. You’ll need this in the next step.

Configure Azure Function App Application Settings

In this example I’m using Azure Functions Application Settings for the Azure AD AccountName, Password and the Power BI ClientID. In your Azure Function App select “Configure app settings”. Create new App Settings for your UserID and Password for Azure (to access Power BI) and our PowerBI Application Client ID. Select Save.

Not shown here I’ve also placed the URL’s for the RestAPI’s that I’m calling to get the IoT environment data as Application Settings variables.

Create a Timer Trigger Azure Function App

Create a new TimerTrigger Azure Powershell Function App. The default of a 5 min schedule should be perfect. Give it a name and select Create.

Upload the Powershell Modules to the Azure Function App

Now that we have created the base of our Function App we’re going to need to upload the Powershell Modules we’ll be using that are detailed in the prerequisites. In order to upload them to your Azure Function App, go to App Service Settings => Deployment Credentials and set a Username and Password as shown below. Select Save.

Take note of your Deployment Username and FTP Hostname.

Create a sub-directory under your Function App named bin and upload the Power BI Powershell Module using a FTP Client. I’m using WinSCP.

To make sure you get the correct path to the powershell module from Application Settings start Kudu.

Traverse the folder structure to get the path to the Power BI Powershell Module and note the path and the name of the psm1 file.

Now upload the Azure AD Preview Powershell Module in the same way as you did the Power BI Powershell Module.

Again using Kudu validate the path to the Azure AD Preview Powershell Module. The file you are looking for is the Microsoft.IdentityModel.Clients.ActiveDirectory.dll” file. My file after uploading is located in “D:\home\site\wwwroot\MyAzureFunction\bin\AzureADPreview\2.0.0.33\Microsoft.IdentityModel.Clients.ActiveDirectory.dll”

This library is used by the Power BI Powershell Module.

Validating our Function App Environment

Update the code to replace the sample from the creation of the Trigger Azure Function as shown below to import the Power BI Powershell Module. Include the get-help line for the module so we can see in the logs that the modules were imported and we can see the cmdlets they contain. Select Save and Run.

Below is my output. I can see the output from the Power BI Module get-help command. I can see that the module was successfully loaded.

Function Application Script

Below is my sample script. It has no error handling etc so isn’t production ready, but gives a working example of getting data in from an API (in this case IoT sensors) and puts the data directly into Power BI.

Viewing the data in Power BI

In Power BI it is then quick and easy to select our Inside and Outside temperature readings referenced against time. This timescale is overnight so both sensors are reading quite close to each other.

Summary

This shows how easy it is to utilise Powershell and Azure Function Apps to get data and transform it for use in other ways. In this example a visualization of IoT data into Power BI. The input could easily be business data from an API and the output a real time reporting dashboard.

Follow Darren on Twitter @darrenjrobinson

 

 

 

 

Leveraging the PowerBI Beta API for creating PowerBI Tables with Relationships via PowerShell

If anyone actually reads my posts you will have noticed that I’ve been on a bit of a deep dive into PowerBI and how I can use it to provide visualisation of data from Microsoft Identity Manager (here via CSV, and here via API). One point I noticed going direct to PowerBI via the API (v1.0) though was how it is not possible to provide relationships (joins) between tables within datasets (you can via PowerBI Desktop). After a lot of digging and searching I’ve worked out how to actually define the relationships between tables in a dataset via the API and PowerShell. This post is about that.

Overview

In order to define relationships between tables in a dataset (via the API), there are a couple of key points to note:

  • You’ll need to modify the PowerBIPS PowerShell Module to leverage the Beta API
    • see the screenshot in the “How to” section

Prerequisites

To use PowerBI via PowerShell and the PowerBI API you’ll need to get:

How to leverage the PowerBI Beta API

In addition to the prerequisites above, in order to leverage the PowerShell PowerBI module for the PowerBI Beta API I just changed the beta flag $True in the PowerBI PowerShell (PowerBIPS.psm1) module to make all calls on the Beta API. See screenshot below. It will probably be located somewhere like ‘C:\Program Files\WindowsPowerShell\Modules\PowerBIPS

Example Dataset Creation

The sample script below will create a New Dataset in  PowerBI with two tables. Each table holds “Internet of Things” environmental data. One for data from an Seeed WioLink IoT device located outside and one inside. Both tables contain a column with DateTime that is extracted before the sensors are read and then their data added to their respective tables referencing that DateTime.

That is where the ‘relationships‘ part of the schema comes in. The options for the direction of the relationship filter are OneDirection (default), BothDirections and Automatic. For this simple example I’m using OneDirection on DateTime.

To put some data into the tables here is my simple script. I only have a single IoT unit hooked up so I’m fudging the more constant Indoor Readings. The Outside readings are coming from a Seeed WioLink unit.

Visualisation

So there we have it. Something I thought would have been part of the minimum viable product (MVP) release of the API is available in the Beta and using PowerShell we can define relationships between tables in a dataset and use that in our visualisation.

 

Follow Darren on Twitter @darrenjrobinson

Simple reporting from the FIM/MIM Metaverse to PowerBI using the Lithnet FIM/MIM Sync Service PowerShell Module

I have a customer that is looking to report on FIM/MIM identity information. The reports they are looking for aren’t overly complex and don’t necessarily justify the need the full FIM/MIM reporting infrastructure. So I spent a few hours over a couple of days looking at alternatives. In this blog post I give an overview of using the awesome Lithnet FIM/MIM Sync Service PowerShell Module recently released from Ryan Newington to do basic reporting on the Microsoft (Forefront) Identity Manager Metaverse into PowerBI.

I’ll briefly show how to leverage the Lithnet FIM/MIM Sync Service PowerShell Module to extract Person objects and their metadata (based on a search filter criteria) from the MIM/FIM Metaverse and output to a file for PowerBI.

I cover;

  • Building a query
  • Executing the query
  • Filtering the results for output to a file (CSV)
  • Importing to PowerBI as a dataset, creating a report showing results in a Dashboard

First up you’ll need to download and install the module from https://github.com/lithnet/miis-powershell

Using the FIM/MIM Sync Service PowerShell Module to query the Metaverse

What operators you can choose on your attribute types (boolean, string, integer, reference etc) in the Metaverse Search function in the Synchronisation Service Manager you can also perform using the Lithnet FIM/MIM Sync Service PowerShell Module.

By creating a search with multiple criteria in the Metaverse Search you can filter the results from the Metaverse.

As shown below you can see that we get 302 results.

So let’s import the Lithnet FIM/MIM Sync Service PowerShell Module, create a filter execute it and look at the results. As you’d expect we get the same result. Excellent.

Remember that using this PowerShell automation module, the backend is still the WMI interface behind the Synchronisation Service Manager. This means you can’t for example create a query filter using “greater than/less than” if you can’t do it in the UI.

Take my Twitter FriendsCount attribute of type Number/Integer as an example.

I can’t create a query filter that would return results where FriendsCount > 20,000. I can only use the IsPresent, IsNotPresent and Equals.

On a sidenote the PowerShell error message will give you a hint at what operators you can use as shown below.

However, if you try and use StartsWith for an Integer attribute the search will execute but just return no results. My tip then is define your query in the Metaverse Search GUI and when you get what results you want/expect, create the equivalent query in PowerShell and validate you get the same number of results.

Final note on query filters. Multiple criteria are an AND operation filter, NOT OR.

Let’s do something with the results

Now that we have a query sorted let’s do something with the results. The result set is the full attribute list and values for each associated object that matched our query from the Metaverse. That’s way more info than what I and probably you need as well. So iterate through the results, pull out the attribute values that we want to do something with and export them as a CSV file.

What to do with the output ?

For this overview I’ve just chosen the local file (CSV) that I exported as part of the script as the input dataset in PowerBI. https://app.powerbi.com

On the right hand side I’ve chosen the columns that were exported to the CSV and they appear in the main window.

Click Pin to Live Page. You’ll be prompted to save the report first so do that then I choose New Dashboard for the report. Click Pin live.

I can then refine and get some visual reports quickly using text based queries using keywords from the dataset columns. Like Top 10 by number of friends from the dataset.

Create a couple of queries and pin them to the Dashboard and the data comes to life.

Summary

The Lithnet FIM/MIM Sync Service PowerShell Module provides a really easy way to expose information from the Metaverse that may satisfy many reporting and other requirements. Taking the concept further it wouldn’t be too complex to export the data to an Azure SQL DB on a schedule and have the results dynamically update on a PowerBI Dashboard.
The concept of exporting data for reporting is just one practical example using the tools. Huge thanks to Ryan for creating the Lithnet tools and publishing to the community. Keep in mind the tools disclaimer too.

Here is the sample PowerShell.

Follow Darren on Twitter @darrenjrobinson

Big Dater – Finding the biggest cheaters with Power BI

Hacking of commercial enterprises is never a good thing and in no way would I condone the dubious logic that was the basis behind the hack and release of the Ashley Madison database. There was no doubt a lot of personal and commercial damage caused.

But with every one of these hacks, there is opportunity. The hack has arguably done more for marriage fidelity than Fatal Attraction and has also given us a chance to relook at data security practices, personal data storage and password management practices. For me it’s a good chance to bust out one of this new tools in the BI suite Power BI on a good sized, real world dataset.

If you don’t want to run through the technical just scroll down to the results section below.

Power BI

Power BI comes in both a desktop and a hosted service model. I’m going to use the desktop model which is available here. It is a powerful BI tool which operates over PowerQuery, the data processing and transformation engine introduced in a previous blog. The tools are built for scale and so are not limited by those annoying little 1 million row limits of Excel. That’s a good thing because there is a lot of data in the Ashley Madison dataset.

Preparing the Data

The data can be downloaded from a number of places. I chose a torrent file sharing link with a set of zipped up files with an encryption key that matches the one that the hackers published to avoid tampering. The data comes as number of .dump files which are the tables from a mySql database, a set of .csv transaction files and a collection of other internal files. All up about 10GB of zipped up data to be explored.

The .dump files are mySQL dumps. These are a CREATE TABLE statement followed by lots and lots of INSERT statements. That’s great if you want to reconstruct the database (and if your prepared to wait a really long time to insert them all, but we don’t really need all the complexity of a relational database, flat files are just what we need. So I wrote a script to parse the INSERT statements and turn it back into a flat CSV file (with a little bit of finesse to get the right quote style for PowerQuery)

The daily transaction CSV folder is much easier, just trim off the titles and concatenate the contents into one great big CSV file. I’ve written a small c# script (attached) to concatenate the data and do a bit of cleaning along the way (it looks like the hackers, in their haste didn’t flush some files properly so there are a few truncated lines). Once the data is together it makes one great big 2.5GB AllTransactions.csv file with 10 million transactions!

The code is available here

Now, we get our first glimpse at the magnitude of the data available.

File Type Size (GB) Rows Columns Contents
amino_member.dump mySQL dump 12.7 36,399,119 61 All member details
am_am_member.dump mySQL dump 9.5 31,971,589 48 Some member details
member_details.dump mySQL dump 2.3 36,416,003 12 Member details summary table
aminno_member_email.dump mySQL dump 1.7 36,397,897 8 Member emails and notification they have signed up for
member_login.dump mySQL dump 4.2 36,400,530 5 Every account, username and password hash
Transactions folder Daily CSV 2.4 9,692,318 19 Every credit card transaction, who made it and where from
(partial credit card details)

Transactions

There has been a lot of discussion online about the actual validity of the database since it appears there is some irregularity about the makeup of population of users. There is a heavy bias of males to females (which might be expected) and the females appear to not be distributed properly, suggesting that he female population was actually manufactured by paid employees. To avoid that discussion, I’m going to look at the one part of the dataset that doesn’t lie. Money! The Ashley Madison dataset includes every credit card transaction since inception in a daily csv file.

Start the Power BI application and launch the GetData wizard. This has a large number of different data source options but we are going with plain old .csv to load the AllTransactions.csv file.

This starts the PowerQuery editor and gives the first look at the data. There’s plenty of juicy information in here but gladly the full credit card number is truncated!

So now is the opportunity to pre-process the data for analysis. Choose Edit and then in the PowerQuery editor use the Choose Columns button. First we’ll pick the interesting columns for analysis.

Since we are in development mode, and the data set is so large, it’s worth trimming the data set to keep it manageable. Use the “Keep Rows button” to keep just 100,000 rows then make a few changes.

PowerQuery is great for importing data from many sources and dealing with large data sets. The code development experience is pretty ordinary so I recommend using PowerQuery to trim up and filter out unnecessary data to make it as small as possible and leave the complex stuff for a later (see DAX below). Trim up the data like this:

  1. Date column is actually a DateTime so rename it DateTime. Then use the Date button to add a “DateOnly” column, and a “Day”, “Month” and “Year” column.
  2. There are a number of failed attempts to charge against a credit card, these are identified by a not null value in the ERROR_CODE column. So trim out those rows and drop the column
  3. The credit card transactions come in 4 types:
    Authorizations – Submit credit card details to take up the free membership offer (Ignore these ones)
    Settlements – Commit previous Authorization (Add these ones)
    Purchases – Buy/Renew a membership outright (Add these ones)
    Credits and Chargebacks – The wife found out, so quickly back out! (Subtract these ones)
  4. Use the “Add Custom Column” to add a new column called “Value” which represents the actual value of the transaction using an if statement to add just the Settlements and Purchases and subtracting the refunds.
  5. Add a UserId column that will help identifying unique users. There’s plenty of personally identifiable information which can be combined to form an effective “UserId”. I have used these FIRST NAME, CARD ENDING and ZIP columns which will give a reasonable approximation for a unique User Id so choose “No Separator” and name the column UserId. Select the columns and hit the Merge Column button.
  6. Finally remove all unused columns including all that nasty personal and credit card information from the data set so we can safely publish.

The Advanced Query button shows the Power Query code that was generated by the import wizard and transformation steps

Now “Close and Load” the data into Power BI.

Currency

One of the nice challenges in the data set is the Amount column is recorded in the native currency of the credit card. So if we want to do any reporting across different countries then that number needs to be normalised to a single currency such as the $US. This is easy if we use the Excel spreadsheet created in my previous blog here Use Excel, PowerQuery and Yahoo Finance to manage your Portfolio. Download the Excel workbook from that blog and open it.

First we need a data source that links CountryCode to CurrencyCode, theres plenty available. I chose this one http://fxtop.com/en/countries-currencies.php . One of the really powerful features of PowerQuery is the ability to directly parse a web page into tables which can be imported into a PowerQuery table using the “From Web” data source.

Open the http://fxtop.com/en/countries-currencies.php page.

Now using PowerQuery tool bar rename the Country column to CountryCode and Split the Currency column by the first 3 characters

Drop all the other columns we don’t need and finally, to retrieve the Rate from yahoo you need the currency in the form of AFNUSD=X so create a new Custom Column called Symbol.

Which generates this table

Paste the Symbols into the Symbol column of the Portfolio tab then hit Refresh Data and you’ll end up with a table like this with the latest currency rates populated from Yahoo Finance in the “Last Trade Price Only” column.

Add one extra row in the table to handle those records with no country set and assume they are US dollars

Save that as currencies.xlsm and then back to Power Query to use the Get Data -> Excel to import it

And import the Portfolio table from the Excel workbook.

Remove the columns other than CountryCode and Rate.

Now we will be using this table to do a significant large join to bring currency rate data onto the transaction table. To do this efficiently we can help the join performance by providing a primary key on the table. There is no GUI wizard feature to do this so open the Advanced Editor and add this line to set a Primary key on the CountryCode column to be used in the join engine.

  #"Indexed Table" = Table.AddKey( #"Renamed Columns", {"CountryCode"}, true) 

Here’s the currency query:

Global Data

Given that the site was a global phenomenon, we want to get some global perspective on the data so let’s upload some global reference data from here https://countrycode.org/ which has some useful country stats.

Import the page using New Source->Web feature in Power Query.

Use the PowerQuery GUI to

  1. Rename the GDP column to and Split it on the first space ‘ ‘
  2. The create a new Multiplier column by parsing the Million and Billion words into numbers using an if statement

    #”Added Custom” = Table.AddColumn(#”Changed Type1″, “Multipler”, each
    if [GDP.2]=”Million” then 1000000 else
    if [GDP.2]=”Billion” then 1000000000 else
    if [GDP.2]=”Trillion” then 1000000000000 else 1),

  3. Split the ISO CODES column by the first ‘/’ and rename the first column CountryCode
  4. Remove all the unnecessary columns
  5. And add an Index on CountryCode for the join performance

The table should look like this

Here’s the generated global query:

https://gist.github.com/petermreid/23e64049b0aa270a1240

Use Close and Apply to run all the queries

Relationships

At this point the data sets are all completely independent. On the left hand side of the Power BI Desktop is a button to switch to Relationship mode.

Power BI will do a reasonable job of guessing the relationships but may need a little help. If not already there, create a one-to-many relationship with the transaction table by CountryCode by dragging and dropping the CountryCode column from the “many” fact table to the “one” dimension tables. (The use of the multidimensional terms used in SQL Server Analysis Services is no mistake as we will see later)

Data Typing

Now switch into Report mode

There’s a bit of additional data type coercion that needs to happen to help Power BI make the right choices when presenting the data. For each of the geographical entities use the Data Category menu to assign the correct type of geographical data.

This will put a small globe icon next to each and will be useful in the mapping process.

Do the same for the currency fields setting to Data Type currency.

DAX

Now it’s time to generate some new columns of data that join across related tables. This is the first opportunity to use DAX (or Data Analysis eXpressions) which is a real pleasure. This is the query language under PowerPivot and DAX has great debugging, helpful error handling and intellisense. On the transactions table use New Column

to add the following columns

Quarter = "Q" & transactions[Qtr]  -- the year quarter string
Value US = transactions[Value]*RELATED('currency'[Rate]) -- the value in $US

(Note the use of the ‘RELATED’ keyword to traverse the join relationship to the currency table)

Value US per Capita(M) = IFERROR(transactions[Value US]/RELATED('global'[POPULATION])*1000000,0) -- the value in $US per million people

(Note how the variables don’t have to follow normal syntax rule, spaces and punctuation are fine)

Value US per GDP(M) = IFERROR(transactions[Value US]/RELATED('global'[GDP])*1000000,0) --the value in $US per million GDP
					

Report

Now the fun part, build a report! I did this

And this

And this

And this

Now we can start to really see the data. Don’t forget the transactions query is limited to 100,000 rows which is not even all of the first year of operation. Ashley Madison, by any measure was already a success having brought in $US3 million in revenue, with 39,000 paying customers!

Scaling Up

Once happy with the result, open up the transactions query

and remove the Kept First Rows step so the full data set is processed.

Now Close and Load the Power Query editor and go make a cup of tea. The full data set is over 2GB and 10 million rows so it’s going to take a while. My i7 8GB SSD laptop takes 20 minutes to process the data.

While that is working take a look at the Task Manager. The Microsoft Mashup Evaluation container is Power Query engine doing its work. But interestingly under the Power BI hood is SQL Server Analysis Services! What we are doing while using the Relationship editor and the Report builder is creating a “cube” which is then embedded in the Power BI .pbix file.

Results

Now with all the data we see the real success story that is Ashley Madison

  • $US240M in total revenue and on target to hit $US100M in 2015 alone (the hack only captured up to Q2 2015)
  • 1M paying customers and climbing rapidly

So the burning question which country spent the most on cheating per capita? It’s as simple as clicking on the largest red circle or clicking on (sorting by) Value US per Capita in the table.

The Vatican! It has 10 paying customers with 59 separate transactions totalling US$1303. We can even see that the initial transactions occurred in 2009, then a period of inactivity and then a return to paid up member status in 2013, 14, and 15. It’s a little bit unfair to label the Vatican since the population is so small it could be a statistical error, but given 1% of residents were paid up members, there’s certainly something interesting there. The other interesting cases are Palau, Falkland Islands and Bermuda.

Publishing

Publishing a report is as easy as pressing this button

The report is publised to here. It isn’t a static set of graphs and figures, it is the full cube which means all of the richness of the Power BI Desktop is available including filtering and drill downs on each of the report elements. Clicking on a circle in the map filters all other elements on the page with that category.

However, the published report is only available to licensed users.

Summary

This is some very powerful tooling at the early stages of development. Power Query needs some work but DAX is great so use Power Query to reduce the data down and use DAX to do the more complex stuff.

The Power BI Desktop Report Builder is primitive but getting better and there is a release every every month. It’s lacking some of the things you’d expect like colour control and font size control on text, z-order control on report elements and snap to grid layout control, theres some room for improvement before the tool is ready to take on the big players.

But thats just the thing Power BI isn’t a multi million dollar BI platform, its a desktop editing and SaaS publishing platform for BI, much like the way I use Word to create this blog which is then published to the Internet. Its another example of cloud computing at its best. A very capable tool once only available to enterprises put in the hands of everyone. This is a disruption for over priced software vendors and consultancies in the business intelligence space.

If you want to have a go at doing some more data analysis I’ve saved the anonymised Power BI files (with no personal information in it) here. The ‘transactions’ data set is the one used in this blog, the ‘members’ dataset has a much richer set of demographics information including sex, preferences, height, weight, dob, date joined etc. I’ll leave it to the reader (or the next blog) to find some interesting insights in there.