Analytics 001: Power BI walkthrough

All the consultants out there are talking about deriving more insights from your “data” and your own top management are also singing in unison to move towards a “data-driven” future. No doubt, “Qlik” and “Tableau” had definitely been brought up in these conversations. You must be thinking, “Seriously? These software are gonna bring change to my company? Oh please!”.

Well I had that exact same thought. So I decided to explore these Business Intelligence (BI) tools further, starting with Microsoft Power BI. Why? Because it is free!

What is Power BI?

Power BI logo

Check it out from the official site:

I would say it is Microsoft’s attempt to compete with the two leading BI tools by leveraging on its strengths in Office Suite and Cloud Services. My first impression of Power BI was pretty good. The start-up is fast, the UI is similar to a typical Office software, and making changes to the dataset is also smooth. If you are a seasoned Excel user, then learning to use Power BI should be a breeze for you. As an absolute noob, you might have to go through the tutorials offered by Microsoft and hangout in the Power BI forum.

However, after using the tool for a while, I come to realise that all the hype over “Self-Service” BI does not equate to ease of use or intuitiveness. The tools merely combined some basic data extraction and manipulation with the ability to create graphs and charts. It is definitely not designed for the ordinary users who do not have an inkling of how data set can form relations, perform table join operations, and how the data can be presented objectively. Even though it is not necessary, having some foundation in data science will certainly help.

Cloud, Desktop, and Mobile

The Power BI desktop client is the application designed primarily to interact with the data and build reports. Power BI also provides a cloud service that can perform the required BI analysis, although the main objective of this service is to publish and share any reports that you have built. The mobile application is designed for users to read the published reports from the cloud using their mobile devices.

So for someone who would like to explore the analysis capabilities of this tool, installing the desktop client is a must.

Report Building Process

This is generally the sequence of actions for building a new report from scratch. I have yet to figure out how to create a report development workflow that will apply existing report templates to new or updated data sources. The following are performed with Power BI Desktop.

  1. Connect to Data Source
    In my case, I worked with a couple of Excel spreadsheet. You may consider connecting directly to databases. After connecting, the tool will extract/query all the available data tables.
  2. Modify the Data
    The data table may not be in a desirable format to produce any useful visualisation. Use the Query Editor to manipulate the data without modifying the source. It provides a number of useful functions, it processes the familiar Excel formula language (DAX or Data Analysis Expression), and also can execute your custom scripts on the data.I noticed that every change performed on the data is recorded as a layer (similar to Photoshop). You may go up and down the stack and modify or remove any layers you wish. This comes in handy when you are trying to explore/clean up your data but do not want to commit the changes until you are confident.
  3. Relationship View
    One of the three views that Power BI desktop provides. This allows you to create relationship between different data tables (outputs from step 2). Note that relationships only makes it easier for you to find information across tables pertaining to specific records using related fields between tables. It is not a join operation and it will not provide any benefit that a table join otherwise will provide. (Table joins should be performed at step 2).
  4. Data View
    I honestly feel that this view provides the same functionality as Query Editor, which makes me wonder what I should do with this view. My guess is for any further modification of the data that you failed/forgotten to perform in step 2, you may perform it in this view.Some the things you can do to your data includes filtering, data transform, data transpose, changing data format, data processing using DAX, aggregation, table joins, adding and removing fields and records, etc.
  5. Report View
    When you have finally gotten all your data prepared, the report view will assist you to create visualisations from the data and assemble it into a report, that can be published on the cloud service.In the report view, all that you should be doing is to match data and present them using the variety of charts. You should not attempt to manipulate the data within the report view. Of course, even if you are not prepared, you can revisit any of the previous steps and make the necessary adjustments.
  6. Publish to Cloud
    And after all your hard work, you may publish the report to the cloud service and share your creation with others. You may also choose to modify the Phone view so that the report may be accessed from a mobile device using the Power BI mobile app.
  7. Interactive Visuals
    What I like about Power BI is the interactive visualisations that automatically filter values and changes the charts dynamically as you click on it. You are also able to drill down into deeper details of the data.

Additional Helpful Tips

The following information may get into the details of using Power BI. These tips were very helpful when I was learning to use Power BI, and I will just park them here for future reference.


Data Preparation

Data Modelling


IT Infrastructure 005: ADFS, Custom Sign-On Page

Once you have your ADFS up and running, you can go on to customise the sign-on page (default URL is https://servername.yourdomain/adfs/ls/IdpInitiatedSignOn.aspx ). It will be helpful for you to first understand how the page is constructed.

login screen

ADFS default sign-on page. Source

How it works

Residing on the ADFS server, there is a group of resources called the web theme. The various components within the web theme determines the look and feel of your sign-on page, and therefore you have to identify and modify those resources that you wish to change.

Unlike previous versions of ADFS, where the page is hosted on an Internet Information Server (IIS) and the resources can be easily modified, with the new ADFS version 3, you will need to make use of Powershell cmdlets to make all the changes. Firstly, you will need to export the web theme from the system, next you will edit the theme resources to your liking, and finally import the changes into the system.

There is a family of Powershell cmdlets for you to modify the sign-on page without any coding, but if you would like to have more freedom over the customisation, take note of the following web theme resources:

The following are brief descriptions of the scripts required. View them on github.

This is the Javascript file that will be executed every time the sign-on page loads. This script allows you to modify HTML elements of the sign-on page. Use developer tools in your browsers to study the HTML file of your sign-on page and identify the elements you would like to change. In my example, I have removed certain elements that I do not want my users to see, as well as added my own welcome message for the page.

This is the Cascading Style Sheet (CSS) file used by the server to format your sign-on page. It is divided into three segments of styles, the theme styles, the common styles, and the form factor styles. You should study the file carefully before editing the styles. Based on Microsoft Technet, it is a best practice not to modify the theme styles as they are used across all pages. In my example, I have changed the colour of the buttons, as well as hidden the Microsoft copyright.

Instead of keying in the Powershell cmdlets for every single changes, you can create a Powershell script that can automate this process for you. The following is a simple script that I have been using. Whenever I modify any of the resources, I can conveniently run this script to import those changes into the system.

### This is a powershell script to update ADFS sign-on portal.
### Create this directory "c:\sso_theme\" and place this script in it.
### To run, please type the following command in powershell:
### & "c:\sso_theme\runtheme.ps1"

### Please make sure to first set the active theme as "custom1" by running the following commands:
# New-AdfsWebTheme -Name custom1 -SourceName default ##clones a new theme named "custom1" from the default theme
# Set-AdfsWebConfig -ActiveTheme custom1 ##sets the theme "custom1" as the active theme

### You can also export the theme to modify:
# Export-AdfsWebTheme –Name custom –DirectoryPath c:\theme

### You can freely modify style.css, onload.js, the logo and the illustration of the sign-on portal
### Once done, the following commands will update the theme:
Write-Host "Updating theme custom1 ..."
Set-AdfsWebTheme -TargetName custom1 -StyleSheet @{path="c:\sso_theme\style.css"}
Set-AdfsWebTheme -TargetName custom1 -Logo @{path="c:\sso_theme\logo.png"}
Set-AdfsWebTheme -TargetName custom1 -Illustration @{path="c:\sso_theme\image.png"}
Set-AdfsWebTheme -TargetName custom1 -AdditionalFileResource @{Uri="/adfs/portal/script/onload.js"; path="c:\sso_theme\onload.js"}
Write-Host "Customisation completed!"

Additional Information:

IT Infrastructure 003: ADFS, WID or SQL Server?

ADFS Configuration DB

ADFS does require a database to store configuration data. If you are not maintaining too many federation trusts, you actually do not require much storage. Of course, it is wise to set your system up to be able to accommodate any new federated applications in future.

WID or SQL Server?

Use WID for small scale deployment as it is much more convenient and easier to maintain. However, if your deployment is large scale, then SQL Server is the way to go.

There are two features of ADFS that are related to SAML, which you may find unfamiliar. These features are only supported by implementing Microsoft SQL Server, and in my opinion, would not be relevant to most use cases.

  1. SAML Artifact Resolution – This feature allows SAML requests and responses exchanges by your ADFS to use artifact ( you can think of it as a link, or a reference to the actual data), instead of including all the data within the messages. Is this essential? It depends on the arrangement you had made with your relying parties. If the applications that they are providing do not require such a feature, then it is not necessary. However, it is always a good practice to plan for the future.
  2. SAML/WS-Federation token replay detection – This feature prevents anyone from using the “Back” or “Refresh” button in the web browser to reload the completed authentication page in order to login to the application multiple times. Is this essential? To protect the privacy of your users, this is a good feature to have. However, I always believe it is more effective to educate the users not to use shared devices to login to sensitive applications, to prevent others from trying out such hacks.

Of course, there are other differences. In general, SQL Servers can support more trust relationships and provides high-availability clustering, at a level which WID cannot achieve.

However, SQL Servers definitely cost more to implement due to the requirement of additional servers as well as SQL software licensing. To set up SQL Server clustering is also rather complicated. And if the SQL Server and ADFS servers are on separate machines, then it becomes a requirement to ensure the availability of the network between these two components.

More information below:

IT Infrastructure 002: Microsoft ADFS

I was assigned to oversee a migration project from ADFS 2.0 to 3.0, yet I have no clue about it. Technet articles are very informative but they are full of Microsoft technical jargon and as a beginner I did not find them very useful. It took me a while to wrap my head around the concepts with the help of other tech blogs. If you are totally new to ADFS, but have been hearing this term buzzing around your workplace, I hope to provide you with a simple and concise description of this product, so that you will have sufficient knowledge to explore it further.

Looking for a definition?

ADFS stands for Active Directory Federation Service, a product of Microsoft and part of their enterprise software solutions. If you are already familiar with Active Directory (AD), understanding the concept of federation service should not be a problem.

If there is one simple definition you need to takeaway from here, it is this: ADFS, in essence, allows a user to login to multiple web applications using one single set of AD credentials, even if those applications are external to your organisation. Of course, those applications will have to be configured to maintain a federation trust with your organisation.

In other words, it provides users with a single sign-on (SSO) experience when accessing web applications. But does SSO imply that a user only have to login once, and all subsequent accesses to various web applications will pass through automatically? The answer is no. SSO only means that a user can use one set of credentials for all accesses, but whether the applications will prompt the users for multiple logins depends on the ADFS configuration.

Another point worth noting is that ADFS merely provides identity management. Access control to various resources is determined by the resource providers.

Why use ADFS?

Implementing ADFS provides the following advantages:

  1. Users login with their AD credentials, this prevents password fatigue. This also eliminates the need to maintain a separate account for each application.
  2. Applications providers, which may be external to our organisation, will not need to maintain the user accounts.
  3. A central portal to manage all our federated relations and settings with multiple web applications.

There are definitely more ADFS features, I am merely listing those which I found relevant to most organisations.

How ADFS works?

Imagine a scenario in which a user wants to access some web application that is external to the user’s organisation. How can this be achieved using ADFS?

There are two parties involved in this scenario.

  • First, the Resource Provider that hosts the web application, which the user wants to access (the terms Resource Provider/ Service Provider/ Relying Parties refer to the same entity).
  • Second, the Identity Provider that sits within the user’s organisation which can prove the identity of the user (also known as the Claims Provider).

ADFS therefore provides a mechanism for an Identity Provider to vouch for the identity of a user, so that a Resource Provider, who trusts the Identity Provider’s claim, will allow this user to gain access to the web application. It is that simple!

  1. A user attempts to use a web application external to the user’s organisation. Before that, the user’s identity needs to be verified.
  2. As the the external Resource Provider does not keep a record of the user’s identity, it requires another entity to verify the identity of the user. This role is fulfilled by the Identity Provider within the user’s organisation.
  3. The Identity Provider, upon request by the Resource Provider, will verify the user’s identity against AD records, and vouch for the user. Hence the term claim-based, because from the Resource Provider’s perspective, the user’s identity is whatever the Identity Provider claims to be.
  4. Once the Resource Provider receives a valid claim, it will allow access to the user.
  5. But before all these communications and exchange can happen, the Identity Provider and Resource Provider must both trust each other. Hence ADFS needs to be configured to establish this relationship, and this is also known as Federation Trust between the two parties.

This mechanism is brokered by the user’s web browser. The role of the federation server is to facilitate communication between the Identity Provider and the user. The identity requests and claims are delivered in SAML message format.

Find out more

More information can be found from the following resources:


20170219 Edit: Diagram link has broke. Will find a new ADFS diagram when time permits.