How to Create Shared Query in SQL Server
Self-service BI is only as good as the information that people can find on their own. The purpose of Power BI is to help users find the data they need, obtain access if necessary, and confirm the validity of the data. Shared queries—resources created by some users for others—can fulfill this purpose, but shared queries are managed by data stewards and administrators to ensure that data is being used correctly. Users can work with shared queries in Power Query and then monitor usage analytics in the Manage Data portal.
Creating a shared query
If your organization is using Power BI for Office 365, you can share a query from Power Query by storing the query in the cloud. When you share a query, you save not only the metadata that describes the data source but also the subsequent transformations, so you or others can reuse the steps from that query later. The data resulting from the query’s execution is not saved in the cloud as part of the shared query.
To share a query, you must first sign in to Power BI, which you do by clicking Sign In on the Power Query tab on the ribbon. Click Workbook on the Power Query ribbon to view a list of queries in the workbook, right-click the query, and then select Share. In the Share Query dialog box, shown in Figure 5.5, you can modify the name and description of the query. You should take time to provide a meaningful and keyword-rich description to help users find the query through a search. You might prefer to use a Word document to provide a longer description of the query. Rather than typing the description, you can use the Document URL field to specify its location.
FIGURE 5-5 The Share Query dialog box displaying query metadata and sharing settings.
The View In Portal link (which opens the Manage Data portal in SharePoint Online) is displayed in the Data Sources section, which also includes a list of all data sources contributing to the query results. You can review usage analytics online to determine whether the addition of a new shared query is helpful or conflicts with existing queries. This analysis may in turn help you determine how to distinguish your query in the name and description fields.
You must set the sharing settings to grant access to yourself only, to everyone, or to specific Windows login or Windows security groups. If you are the one sharing the query, you are automatically included and do not need to explicitly add your login to this section.
Another decision you make when creating a shared query is whether to upload rows of data for previewing. If the underlying data source contains sensitive data, you should not select the Upload First Few Rows For Preview check box. Even if a user does not have permissions to access the data source, the query preview results are still visible when a user browses shared queries.
If you are a member of the Data Steward group, you have the option to certify a query. The certification check box is displayed only to members of this group. Certification is an indication that a query has been formally reviewed and accepted for general use and is therefore considered as a trusted query.
Using a shared query
When working with Power Query, you can use Online Search to find shared queries, or you can click Shared on the Power Query tab on the ribbon to view a complete list of shared queries. When you point to a shared query in the list, a preview flyout screen appears. To use the query, point to the ellipsis at the bottom of the flyout screen and click Add To Worksheet. If you need to modify the query, click Edit Query on the Query tab under the Table Tools tab on the ribbon.
If you do not have permissions to use a shared query, you see a Request Access link. This link sends an email message to a designated recipient or opens a webpage where you can request access. The data steward responsible for the shared query specifies whether the access request is associated with email or a URL.
Managing query metadata
As a data steward, you can review data sources and manage metadata in the My Power BI site. To do this, click the Data link at the top of the page, and then click Data Sources in the navigation pane. Here you have access to the cloud-based metadata repository for Power BI. As data sources are added, the metadata is often missing, and you see a list of untitled data sources. Click the ellipsis to the right of a data source, click Edit, and then type a display name and a description. In addition, you have the option to provide an email address or a URL for users to contact when they request access to this data source.
Reviewing usage analytics
When your My Power BI site is open, you can use the Data link to open the Manage Data portal. Another way to access this information is to sign in to Power BI from the Power Query tab in Excel and then click Shared on the ribbon. Point to a query, click the ellipsis at the right of Edit Settings at the bottom of the preview flyout screen, and then click View Statistics. Both of these methods take you to the same location in your Power BI site, the Manage Data portal.
This portal shows the usage analytics for your queries (shown in Figure 5.6), the shared queries you created by using Power Query, and the data sources you have used.
This information can help you measure the usefulness of your queries and see whether others have come to rely on those queries. You can assess this information to determine whether it’s time to formalize a data source as part of your organization’s enterprise information architecture. You can filter the usage analytics to display results for the last day, the last 30 days, or the last year.
FIGURE 5.6 : Usage analytics in the Manage Data portal.