Skip to content

Welcome to the Power BI Reporting New User Guide

Table of Contents

Introduction

So you have decided to step up your data reporting capabilities. Maybe it's to leverage information / metrics that will improve project efficiency, enable a culture of data transparency, or proactively monitor progress in order to preemptively prevent catastrophe. Whatever your needs are, as we continue to shift more and more toward a data driven culture, Power BI is a powerful yet user friendly tool that will allow you to leverage all the power that your data has to offer.

This guide will walk through the basics of setting up and using Power BI for new users with little / no PBI experience. It will cover Power BI (PBI) licenses, set-up for desktop, data connection, data transformation, report building through visualizations, and how to share & publish your dashboards.

First Things First: Understanding Licenses in PBI

As you get into using PBI, it is important to understand what you can and cannot do for free. There are 3 types of Power BI licensure capability levels which we will briefly discuss below:

  • What can I do with Power BI for free?

    • Import from a data source (70+), build from all PBI visuals, export to the web and Excel
    • Power BI Desktop - a free application to connect, transform, and visualize your data
      • Download Power BI Desktop here: https://www.microsoft.com/en-us/download/details.aspx?id=58494
    • Great for doing your own analysis ;)

  • What can I do with Power BI Pro?

    • Share data, dashboards, and reports with other PBI users
    • Create app workspaces: develop and collaborate with co-works on PBI reports, automate and schedule a data refresh process

The third license is a Power BI Premium license, but this won't likely be needed unless you are distributing multiple reports on a very large, organizational scale and you would like to transition storage to the cloud.

TLDR - If you will need to share your reports and dashboards with some subset of end users, then you will need a Pro license. Otherwise, a free one should suffice.

  • Request a Power BI Free license from IT here: https://amw01109.ent.core.medtronic.com/esd/Items/Details?PackageId=117
  • Request a Power BI Pro license from IT here: https://amw01109.ent.core.medtronic.com/esd/Items/Details?PackageId=115 (Note: for the paid licenses, your manager will need to approve the costs associated)

Requesting any Power BI license from IT will also require you to complete a short training and provide a confirmation of your completion (I promise it's not too bad)

You will need to be logged onto VPN via pulse secure to access the Software Request Center

Getting Started: Let's get some data!

  1. Download the PBI Desktop application: https://www.microsoft.com/en-us/download/details.aspx?id=58494
  2. Open Power BI Desktop and sign-in with your Azure Active Directory (Office 365/ENT) account
    • This is usually your username@medtronic.com and your Medtronic password
  3. Select Get Data and search for a data source
    • Here's some sample data in the form of Excel files if you just want some data to play with https://docs.microsoft.com/en-us/power-bi/create-reports/sample-datasets#eight-original-samples
    • This page also includes some sample PBI reports for various industries
  4. Depending on the data source you select, you may need to provide additional credentials. Ensure that you have also been granted access to the data source
  5. Here is some more info about the different types of data sources that are supported: https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-data-sources
  6. One you have chosen a data source you can select the specific tables, containers, sheets, etc. that you need and select "load" to import the data
  7. You can also transform the data either before loading it or after the import has been completed using the Power Query Editor - which works very similar to the one in Excel if you are familiar with that. If not, no worries! That is what we will get into in the next section of this guide!

How to specifically get data from the DevOps Data Warehouse in Cosmos

  1. Click "get data" in the home tab from the data section of the top ribbon
  2. Select Azure Cosmos DB and click "connect"
  3. Enter the database URL
    • Find your Azure Cosmos DB account in the Azure portal (ours is currently called azdo-metrics-cosmos)
    • Go to "keys" and copy the DB URI, click "ok"
    • Choose the db and container you want (currently the azdo-events-db and historical-non-test-wi-area-path-iteration-path)
  4. Flatten the JSON if needed (covered in the beginning if the next section)

Tables and Source Data in the DevOps Data Warehouse

At this point we are not planning to make tables in Power BI. As long as our NoSQL DB is properly designed, we should be able to get by with very minimal/no data transformation and extraction using the power query editor in Power BI.

Raw source data will not be directly published into a Power BI workspace, but if we create some base reports then they may possibly be. The data warehouse data will most likely be imported from cosmos using the process outlined above.

Data Transformation: mold and shape potentially g@rb@ge data into something that doesn't terrify those who view your report (in my opinion, a huge superpower of this software)

(Note: If you are importing json data from cosmos db you will need to transform the data in order to flatten the json and view the individual fields - please refer to the microsoft doc here under the section "Flattening and transforming JSON documents" steps 1-3: https://docs.microsoft.com/en-us/azure/cosmos-db/powerbi-visualize#flattening-and-transforming-json-documents)

  1. Under the home tab in the top ribbon, select "transform data". This will open up the Power Query Editor.
  2. Here you have the option to change data types, filter your data, create a custom calculated column, and so much more. Basically anything you can think of to slice, makeover, trim, and clean-up your data so it is in a prettier state for the front end visuals
    • Learn more about all the cool transformations that are possible within Power BI here: https://docs.microsoft.com/en-us/power-bi/transform-model/
    • These are some of my most used transformations
      • Change a data type: https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-data-types
      • Find & replace: https://support.microsoft.com/en-us/office/replace-values-power-query-28256517-f1e9-4dc3-832f-45786e9cf721
      • Create a custom column: https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-add-custom-column
      • Split a column: https://support.microsoft.com/en-us/office/split-a-column-of-text-power-query-5282d425-6dd0-46ca-95bf-8e0da9539662
      • Remove errors or blank records: https://support.microsoft.com/en-us/office/remove-or-keep-rows-with-errors-power-query-67046a11-e9f1-4911-a3b1-e9d9fb9e8831
  3. You can also write and run your own queries - as the name implies
    • Power BI uses the Power Query M language whose syntax follows the structure:
      • "let ..... (encapsulate value composition and assignments) in .... (source)"
      • You can read more about the M language here: https://docs.microsoft.com/en-us/powerquery-m/

A note of warning - data manipulations done in the Power Query Editor may sometimes lead to unexpected results due to the way in which transformations are executed which is slightly different than a traditional query and may use fuzzy matching for joins, filters, etc.

Always ensure that you validate the results of the data transformations

The data and model view: ERDs and a baby SQL experience

Data View

The data view in Power BI allows you get a quick list of all the tables you have imported which may be from various data sources as well as all their columns - like a table of contents for all your base data

I find it very useful when I need to look up all the fields of a specific record using a primary key or GUID as well as for filtering to get a subset of the data for my own personal dummy checks and validation against what is being displayed in my various visuals.

For those of you with a SQL background, think of this an an area where you can get view data in a similar way to the results of an ORDER BY or WHERE claused query.

In the data pane, you can also create data groups & data categories, set aggregation types which Power BI uses for numeric fields, and format your data values in various forms. - Here is more info on some of the aforementioned capabilities - Data groups: https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-grouping-and-binning - Data aggregation: https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-aggregations

Model view

The model view is basically like a live ERD (entity relationship diagram) - If you're new to databases, learn what an ERD is here: https://www.lucidchart.com/pages/er-diagrams

The main use of the model view is to view which relationships are active and create new relationships between primary and foreign keys in a relational database model.

Here is a quick guide on creating relationships and using the model view UI: https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-create-and-manage-relationships

Users be warned - Power BI likes to auto-detect relationships in your data, which can sometimes be nice. However, it can also lead to a host of hard to trace issues which can break your report (speaking from experience and many hours of confusion). That being said, always make sure to check which relationships are active in your Power BI model and watch out for those pesky auto created ones - this will save you many tears.

Overview of the new model view: https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-relationship-view

Creating Visuals: The magic

Now that you've done all the yucky clean up work (unless you were lucky enough to get some pretty, pre-cleansed data), it's time to get into the most fun and satisfying part of using Power BI - creating the front end visuals for reporting. There is no better feeling to me than being able to watch your data come to life through visuals to tell an always interesting and often surprising story which will almost always change the way you work as well as your perspective lens. This is the power of data, people

There are a wide array of visuals that come pre-imported with Power BI desktop and their use cases depend on the structure of your data as well as the goals of your report. - Here is a high level list on some of the basic ones with links to in-depth docs on how to specifically use each one: https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-types-for-reports-and-q-and-a - You can also add filters to visuals: https://docs.microsoft.com/en-us/power-bi/create-reports/power-bi-report-add-filter#add-a-filter-to-a-visual and format them to fit your theme: https://docs.microsoft.com/en-us/power-bi/visuals/service-getting-started-with-color-formatting-and-axis-properties - Many also support drill downs into data hierarchies: https://docs.microsoft.com/en-us/power-bi/consumer/end-user-drill and drill throughs to other pages in your report:https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-drillthrough

You can also import cooler visuals from the store, however, Microsoft does require you to drop cash for most of these (yay corporations)

Creating measures to display calculations in a visual

Measures are like formulas which allow you to display calculated values based on data fields in your report and dashboard visuals. These can be very useful for mathematically calculated metrics and allow for a more dynamic usage of your source data, so I thought I would throw in a quick blurb about them here. Learn about working with measures: https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-measures

Publishing and Sharing a Report or dashboard

(Note: you must have a PBI pro license to share reports with others)

Reports and dashboards can be shared with others by users who have a Power BI pro license and have published them to a PBI workspace where others can view them.

A workspace is essentially a centralized location for collaboration. Read more about workspaces in Power BI here and the new vs. classic workspace: https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-create-the-new-workspaces

Reports and dashboards can be distributed in many different ways including being downloaded as .pbix files, shared via teams, etc.

You can enable different levels of sharing to different groups. You may also limit what viewers of your report & dashboard can see and do with its content and data for security purposes.

Here is some info about the various ways PBI files can be shared and the process: https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-share-dashboards

We have created a Power BI workspace where we intend to publish some reports and dashboards. To publish reports to that workspace you will need a Power BI PRO license and to talk to a member of the Energizers team.

How to access reports in the CRHF DevOps Power BI workspace:

  1. Go to URL https://medtronicprod.service-now.com/it/?id=mdtit_sc_cat_item&sys_id=633ef5461bda04104455631e6e4bcb9d
  2. Fill out the form by selecting your particular option from drop down and submit.
  3. Request will route to set approvers and access would be granted accordingly.
  4. Once they get access, user can login to app.powerbi.com
  5. Go to Apps option and access your APP.

Reports vs. Dashboards: for all you familiar with static reporting tools (currently being phased out because the future is automation) or those just confused by the nuances. I don't blame you. I was too

  1. The Report:

    • Fully interactive. You can filter report visuals and drill
    • Can vary display based on user
    • Ability to enforce RLS (row level security) so you can limit who can see which aspects of the data and report
    • Can have multiple pages

  2. The Dashboard:

    • High level day-to-day overview metrics that can come from multiple reports
    • Think of it as a landing page to ground yourself with KPIs
    • Created in the Power BI workspace on the web and refreshes automagically
    • Easy for different users to change the sizing appearance to suit their aesthetic needs
    • You can use a dashboard to get to a report

TLDR: Report = data deep dive Dashboard = quick view of key metrics

A Final Note

Power BI, just like anything in tech, is always always changing. Something that is pretty exciting given all the new features being implemented on a fairly rapid scale, which makes me very excited for the future

If you want to read about some of the latest features implemented in PBI, you can look here: https://powerbi.microsoft.com/en-us/blog/2021/06/

Please note that the instructions in this user guide have been updated as of July 2021

That being said, clearly we have not covered everything that this amazing software can do. If I tried, I would be here typing forever hanging off Microsoft's every word on new updates and feature additions.

However, this is why we love the internet! and I can say that as Engineers we have gotten pretty good at digging around for answers, especially with all the information we have available at our fingertips. So, I believe in all of your abilities to go find out about something you want to learn related to Power BI that is not in this guide

Sources

  • https://docs.microsoft.com/en-us/power-bi/fundamentals/desktop-what-is-desktop
  • https://dynamics.folio3.com/blog/difference-between-power-bi-pro-vs-free-vs-premium/
  • https://www.lucidchart.com/pages/er-diagrams
  • https://radacad.com/dashboard-vs-report-when-where-why-which-to-use
  • https://stoneridgesoftware.com/what-are-the-differences-between-power-bi-free-power-bi-pro-and-power-bi-premium/

Plus many more linked throughout the guide from the Microsoft docs - (If the inconsistent formatting of Microsoft Docs bothers you, please take that up with them - I would totally back you up) *This officially concludes my Microsoft rant

Note: Information on CI/CD pipeline integration with reports & workbooks as well as how to get access to the data source will be added in the future