This content is part of the Essential Guide: Guide to SQL business intelligence, analytics and data visualization
Get started Bring yourself up to speed with our introductory content.

Using Power BI to enhance SQL Server self-service business intelligence

SQL Server DBAs and developers should be familiar with Power BI for Office 365. Here's an overview of what it does and how to use it.

Microsoft has extended its reach into self-service business intelligence with the release of Power BI for Office 365, a cloud-based service that allows users to create and share visually rich reports and the data queries that support them. Integrated with Microsoft Excel, Power BI makes it possible to consolidate information from a wide range of sources and combine it into content that users can search and collaborate on from a central portal in the cloud.

At the heart of Power BI lies the data that drives the content. Because some of that data may reside in SQL Server databases, users might turn to you or other SQL Server folks to help ensure the data is available. If you're one of the lucky chosen ones, you'll want to have a basic understanding of where you fit into the system. Even if you never touch the Power BI interface, you'll at least know what your co-workers are talking about when they refer to gateways and data sources and refreshing information in the cloud.

A look at Power BI

Power BI is a Web portal where users can post Excel-based reports along with the queries used to gather the report's data. Other users can access this information and use it to generate their own reports or collaborate on existing ones.

Oddly enough, the glue that binds the Power BI ecosystem together is Excel, a product completely separate from Power BI. Microsoft has made four plug-ins available to Excel that let users retrieve heterogeneous data, create reports and integrate with Power BI:

  • Power Query: Builds queries that connect to various data sources, merge data from those sources and support sophisticated analytics within Excel.
  • PowerPivot: Creates complex table-based data models that support relationships, hierarchies and custom measures.
  • Power View: Creates analytical reports that include interactive charts and graphs.
  • Power Map: Previously called GeoFlow, this presents geospatial data on 3-D maps integrated into the reports.

Once users create their reports, they can post them to the Power BI site, along with their related queries. Then users can manage those postings and view analytics about their usage.

Power BI administration

At times, users might want to use an on-premises data source to create their queries and populate their reports. That's where you come in.

Administrators managing an organization's Power BI sites use Admin Center to configure settings; manage roles; and, most importantly, establish on-premises data sources. It's possible that you would be added as an administrator to help with the data-related configurations. Assuming this occurs, when you log on to Admin Center, you'll find six tabs:

  • System Health: Views logged event data about various operations, including details about Data Management gateways.
  • Data Sources: Adds and manages on-premises data sources. This is where you enable OData feeds and set up cloud access for your worksheet data sources.
  • Gateways: Adds and manages gateways, the client agents used to connect to on-premises data sources. You must install at least one gateway before you can register a data source.
  • Role Management: Adds and manages users in the Admin group or the Data Steward group. Data stewards can use Power Query to certify that a query is authentic.
  • Settings: Configures settings, such as enabling Power BI to track a query's top users or to send a notification email if an OData feed indexing operation fails.
  • Get Started: Finds information and shortcuts for performing common administrative tasks, such as registering an on-premises data source or enabling workbook data to be refreshed.

This is a brief overview of what Admin Center allows administrators to do. If you find yourself in this role, take the time to review the tabs. They all contain settings that could apply to what you're doing. In the meantime, let's take a closer look at the two areas likely to concern you the most: gateways and data sources.

Power BI gateways and data sources

Before you can set up a data source, you must install at least one gateway on an on-premises computer. The gateway is a client agent that connects to the Data Management Gateway cloud service and facilitates access between Power BI clients and the on-premises data sources.

More on SQL Server self-service BI:

Check out our SQL Server self-service BI guide

Think you know about self-service BI? Take our quiz on it.

Read about self-service BI in the midmarket.

To set up a gateway, you must first create it in Admin Center. Then you download the installation package to the computer, install the gateway and configure it. During the installation process, the Data Management Gateway Configuration Manager tool is also installed on the computer. The tool lets you perform such tasks as registering the gateway with the Data Management Gateway service or specifying whether OData feeds can be accessed via HTTP or HTTPS. Power BI lets you install up to 200 gateways per tenant.

Once you have a gateway installed and configured, you can use Admin Center to set up on-premises data sources. Power BI supports SQL Server databases versions 2005 to 2012 and Oracle databases versions 10g to 11gR2. Power BI also lets you register a SQL Server Integration Services, or SSIS, package as a data source.

To set up a data source, you first use Admin Center to register it, then you enable an OData feed for the source, select the tables and views to include in the feed, and grant users the necessary access.

You can also enable data sources used in Excel workbooks posted to SharePoint Online. If the workbook uses data from an on-premises SQL Server or Oracle database, you can extract the connection string from the workbook, use it to register the data source in Admin Center, enable cloud access to the data source, and then grant access to your users. They will then be able to refresh the workbook data as needed.

The evolving role of SQL developers and DBAs

As someone responsible for your organization's data, you never know when and where you'll be asked to help expedite data access. If you're called upon to assist with Power BI, your primary duties will likely be confined to setting up gateways and data sources, although as you're probably well aware, anything is possible. But even if you don't touch Power BI components, knowing the service's capabilities and what users are trying to achieve can be useful in understanding what sort of data to deliver and how to deliver it. The better you can facilitate the self-service BI processes, the happier you and everyone else will be.

About the author:
Robert Sheldon is a technical consultant and the author of numerous books, articles and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation.

Next Steps

Power BI updates bring cloud reporting services down to earth

CDS for Analytics brings Power BI and data sources together

Dig Deeper on SQL Server Business Intelligence Strategies