Set up the Microsoft Learn Organizational Reporting service Power BI template for SQL DB storage

  • Article

We've created a Power BI template for the Microsoft Learn Organizational Reporting service data, based off the standard SQL Server and DB configuration from Azure Data Share (i.e. 1:1 mapping between the dataset and the DB). It's a starting point to visualize and report on the organizational reporting datasets, including high-level views as well as user-level training progress of individuals in your organizations, but can also be extended to build out further reporting and join to other data.

This article will walk you through the one-time setup of the Power BI template to make it a live reporting solution. It will only work for those who are already using the Microsoft Learn Organizational Reporting service and have their data mapped to a SQL DB for storage.

Note: If you aren't already using the Microsoft Learn Organizational Reporting service, you should review the feature overview and the setup articles first before proceeding further with this article. If you're already using the Learn Organizational Reporting service, but are using a different target storage location (ex: blob, Data Lake), this template won't work for you. You can choose to build your own report for those locations using your reporting tool of choice, or you can remap the datasets to a SQL DB in order to use this service.

Example Power BI dashboard showing high-level Learn completion data generated from Organizational Reporting.

Example Power BI dashboard showing user learning path completion data generated from Organizational Reporting.

Example Power BI dashboard showing user Learn module completion data generated from Organizational Reporting.

Requirements

  • You must have already setup the Learn Organizational Reporting service and configured the Azure Data Share to map to a SQL Server & DB.
  • Your SQL DB table names must follow the standard DB table names from the Azure Data Share and not have tenant IDs appended to the end. If you do have tenant IDs appended to the end of your tables, or you have renamed the tables - you can either modify your configuration to match the template - or edit the queries in Power BI to match your configuration. Instructions listed below in the 'Troubleshooting common issues' section.
  • You have downloaded the user list from your Microsoft Entra admin center. If you have not done this, you will see blanks where the user's names should be.
  • You must have Power BI Desktop installed to create the initial report and do subsequent edits. Users do not need Power BI Desktop to view the report once it is published.

Template setup instructions

First, you'll have to download the template and input your SQL Server and DB as a connection:

  1. Download the template from the mslearn-organizational-template GitHub repo. Note: You don't need a GitHub account to download the template.
  2. Open the file.
  3. Input the SQL Server and DB.
  4. Select Load.

Next, you'll want to upload the Microsoft Entra user data from the CSV you exported earlier in order to see user's names and personal information with the data.

  1. Select Get Data.
  2. Select Text/CSV.
  3. Select File.
  4. Select Open.
  5. Select Load.
  6. From the Report Screen find the table UserAADInfo in the right column.
  7. Right-click UserAADInfo.
  8. Select Edit Query.
  9. In the list of tables make sure UserADDInfo is highlighted.
  10. In the top menu on the right-side, select Append Queries.
  11. Ensure Two tables is selected.
  12. In the drop-down of Table to append, select the one you created through the import in previous steps (most start with exportUser).
  13. Select OK.

It should now populate the users' names into the reports.

There are many other ways to make this report more efficient, such as having a regular sync of your Microsoft Entra user data into a table that is then dynamically pulled into this report. This template is intended to be a starting point. There are also a handful of report sharing options.

Troubleshooting common issues when using the template

If you created custom table names and they don't match the table names in the Power BI template or your table names have tenant IDs appended to the end of them (and therefore, do not match the table names in the Power BI template):

You can either rename your table names to match the template - or edit the queries in Power BI using the advanced editor to match your configuration by replacing the table names with the one in your setup.

If you're considering to rename your SQL DB tables, think carefully before you do! If existing queries, views, user-defined functions, stored procedures, or programs refer to that table, the name modification will make these objects invalid. You may have to update your Azure Data Share mapping to point to the newly renamed tables.

More Power BI resources

Next steps

Review the resources below to help you on your development journey: