Introduction

The data management capabilities in Power BI for Office 365 are delivered both in the Power Query add-in for Excel, and in the Manage Data Portal in Office 365. To help data stewards and information workers get the most from their Power BI experience, we’ve updated the Manage Data Portal to improve the visibility and manageability of arguably the most important metadata in Power BI: data source metadata.

In the next paragraphs, we’ll show you how this new feature works and how you can begin leveraging it. You can also let Matt Roche, Senior Program Manager walk you through these updates.

The Importance of Data Source Metadata in Self-Service BI

Data source metadata, specifically the “Approver for access requests” property for Data Sources, is probably the most valuable metadata in the Power BI Data Catalog. Query metadata may get more visibility and attention because it is prominently displayed, but data source metadata does the heavy lifting when it’s needed the most. Data source metadata can answer the most vexing questions that users face – what is the data source, and how to get access to the data you need, when you don’t already have permissions.

Here’s the experience when a user attempts to connect to a secure data source, and that data source has not been annotated in the Data Catalog:

Data source metadata is displayed when a user attempts to connect to that source. Instead of displaying a lengthy address that may confuse non-technical information workers (servername.networkname.com;databasename or \\servername\sharedfoldername) the connection dialog in Power Query will instead display the steward-supplied business-friendly name. Even more importantly, when the data source has a request access URL specified, Power Query will also display a “Request access” link within the connection dialog itself, so that users can easily initiate the process to gain access. This property accepts either an email address or a URL, and should typically be set to one of the following values:

  • The email address of the person or team who manages access to the data
  • The URL for the documentation (on a SharePoint site or shared folder) that describes how to request access to the data
  • The URL for a self-service identity management tool (such as Forefront Identity Manager) where users can directly request access to the data.

Here’s the experience when a user attempts to connect to a secure data source, and that data source has been annotated in the Data Catalog by a data steward:

The difference between these two experiences is significant. In the first scenario, the user is blocked – unless he knows the person or team to contact to request access, he has no way to proceed, and cannot open the query that uses this data. In the second scenario, the user can clearly see what the data source is, and by clicking on the Request access link can easily follow the process established by the data source owner to get the permissions required to access the data.

Making Data Source Management More Discoverable

Given the importance of data source metadata, we’ve updated the Power BI Manage Data Portal to increase its visibility and to make it even easier for data stewards to discover data sources that lack metadata, and opportunities to make their data more usable.

In the “My Queries” page in the portal, the list of shared queries now includes a “Data Source Status” column that displays the completeness of the metadata for the data sources that are used by each query. This column can be red, yellow, or green:

  • Red: Missing – no metadata has been provided for the data sources
  • Yellow: Incomplete – some metadata has been provided, but at least one attribute is missing
  • Green: Complete – all metadata has been provided for the data sources

The “…” action fly-out for queries has also been updated to display the full list of data sources used by a query, the metadata status for each data sources, and a link to jump directly to the edit experience for a data source.

By clicking on any of the data sources, the user can view and edit the data source metadata without needing to first navigate to the data sources list. Once the data source metadata has been completed, the magic can begin. When a user previews the query, the friendly data source name will be displayed. And any time a user opens a query, the connection dialog will include a “Request access” link. When the user clicks on that link, the action specified by the data steward will take place, opening email or a web browser. This is “magical” because it represents a best-of-both-worlds experience. The user gets help exactly where he needs it, and the data source admin retains control of the data and of the process for granting access to the data.

Summary

Complete data source metadata adds value to any query that uses that data source, and can help users successfully complete the “last mile” of opening the query and connecting to the underlying data. The new capabilities in the Manage Data Portal make it easier than ever for data stewards to identify opportunities to add missing data source metadata, and to make enterprise data discoverable and understandable by business users.

Follow this links to access more resources about Power Query and Power BI: