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: https://powerbi.microsoft.com/en-us/

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.

General

Data Preparation

Data Modelling

Visualisation

Advertisements

IT Infrastructure 001: Hosting Services

This is a piece I have written for my workplace sometime back. The department was having difficulty communicating the options available for business units to host their applications and enterprise software. In the end, we decided to keep to 4 simple options, presented to the business units as services that they can choose from.

A wide range of hosting services had emerged over the years, driven by advancement in technology and increasingly demanding business needs in a competitive environment.

As a result, implementing a new application is no longer a simple task. Aside from designing the functionality of the application, businesses will also have to decide on a hosting service to host the application. The choice of hosting service will have a critical impact on the performance, sustainability and the overall costs of the implementation.

From a technical point of view, all hosting services provide the following foundations to ensure the hosted application runs smoothly:

  • Datacenter facilities
  • Servers and Hardware
  • Networking
  • Management of the above

The IT Infrastructure of a company should strive to provide their knowledge and expertise to help businesses make better informed decisions. To do this better, the team can consider the following classification of all hosting services available in the market, when they are organising and presenting information to their business partners.


On-Premise Hosting Service

The business will provide a space on-premise to install the datacenter facilities and hosting resources. Hardware, network and management will be provided by IT Infrastructure team or any partner vendors. The business will have full control over the application.

Key Characteristics:

  • Consumption of physical business space
  • Leverage on the skills and competency of internal IT Infrastructure team for servicing
  • Quality of performance heavily dependent on the scale of initial investment

Co-Location Hosting Service

The service provider will house all of your hosting resources within a commercial datacenter. Hardware, network, and management will be provided by IT Infrastructure team or any partner vendors. The business will have full control over the application.

Key Characteristics:

  • Enjoy economies-of-scale by sharing industry-grade, commercial datacenter facilities
  • Leverage on the skills and competency of internal IT Infrastructure team for servicing
  • Quality of performance heavily dependent on the scale of initial investment

Managed Hosting Service

All of the computing resources, such as hardware and network, as well as the datacenter facilities, will be leased from the service provider. The service provider will also provide management service. The business will have full control over the application.

Key Characteristics:

  • Obtain Service Level Guarantees (SLG) from service provider
  • Leverage on the technical expertise of service provider for support
  • Quality of performance dependent on the agreement with service provider

Cloud Hosting Service

All of the computing resources, such as hardware and network, as well as the datacenter facilities, will be provided by the service provider. The service provider will also provide management service. The degree of control that the business will have over the application depends on the Cloud service model subscribed.

Key Characteristics:

  • Obtain Service Level Guarantees (SLG) from service provider
  • Leverage on the technical expertise of service provider for support
  • Quality of performance heavily dependent on product standard offered by service provider
  • Flexibility to scale application up or dow
  • Utility model (only pay for what you used)

More information available from the following resources.