This document details the tasks necessary to implement Microsoft Power BI as a Cloud Services Provider (CSP) partner offering. It describes, in detail, technical requirements and best practices for designing and implementing business intelligence (BI) solutions using a combination of Microsoft Power BI, Cloud Services, hosted, and on-premises data sources.
In addition, we describe options and best practices for services you might include within a CSP partner cloud-based offering for both existing and new customers. This document provides guidance on Power BI security and identity management, as well configuration information to allow Power BI to refresh data from supported sources using the Enterprise Gateway
The target audience for this document is Cloud Service Providers (CPSs) who want to offer value-added Power BI customer solutions.
The goal of this document is to help you understand how to design and implement an architecture in which these services can securely interact with both hosted and on-premises environments. This document provides technical details required for Service Providers to add Power BI offerings to existing services.
To get the most out of this document, you should have a basic understanding of network/database connections, cloud computing and participate as a partner within the Cloud Services Provider program.
"There is a significant change in how companies are satisfying [Business Analytics] and new business-user-driven requirements. They are increasingly shifting from using the installed base, traditional, IT-centric platforms that are the enterprise standard, to more decentralized data discovery deployments that are now spreading across the enterprise. The transition is to rapidly implemented platforms that can be used by either analysts or business users, to find insights quickly, or by IT, to quickly build analytics content to meet business requirements to deliver more timely business benefits. Gartner estimates that more than half of net new purchasing is data-discovery-driven …. This shift to a decentralized model that is empowering more business users also drives the need for a governed data discovery approach."
"The intent is to expand the use of analytics, particularly insight from advanced analytics, to a broad range of consumers and nontraditional BI users — increasingly on mobile devices and deployed in the cloud."
"While most BI vendors now have a cloud strategy, many leaders of BI and analytics initiatives do not have a strategy for how to combine and integrate cloud services with their on-premises capabilities."
From Gartner's Magic Quadrant for Business Intelligence and Analytics Platforms (2/2016)
These are terms unique to cloud computing. In this document, the following definitions apply:
Cloud Computing – The National Institute for Standards and Technology defines cloud computing as a model for "enabling ubiquitous, convenient, on-demand network access to a shared pool of computing resources (e.g. networks, servers, storage, applications, and services) that can be rapidly provisioned and released with minimal management effort or service provider interaction."
Infrastructure as a Service (IaaS) – The capability to provision storage, archive, network, and server resources in a shared environment. Typically, server resources are deployed as virtual machines (VMs). The customer performs the management of the server operating systems and other resources within this environment, but the provider performs the management of the underlying hardware and hypervisor. For example, Azure Virtual Machines and Azure Virtual Networks are key components of IaaS available in Azure.
Hybrid Cloud – An infrastructure that bridges on-premises or hosted computing resources and a public cloud. A hybrid cloud environment may be used for functionality such as cloud bursting (sending a workload to the cloud when on-premises or hosted resources have been exhausted) or disaster recovery.
Platform as a Service (PaaS) – The capability for the customer to deploy application tiers, such as a database or a web service, to a container managed by the provider. The customer does not manage operating systems or infrastructure in this model but has control over their application, data, and possibly some configuration settings.
Power BI – The combined Microsoft self-service business intelligence solution. Power BI is a modern BI and analytics platform that provides a set of business analytics tools that provide powerful data insights. Tightly integrated with familiar O365 tools and technologies Power BI allows customers to view, analyze, and share Business insights across their organization - from the desktop to the cloud.
Public Cloud – Cloud resources are available for use by the general public and exist in the data centers of the cloud provider.
Private Cloud – A virtualization infrastructure provisioned for use by a single organization or department within that organization. It may exist on or off the organization's premises.
Software as a Service (SaaS) – The capability for the customer to use the provider's applications running on the provider's cloud infrastructure. The applications are broadly accessible from an Internet browser. Although the customer does not manage any of the underlying infrastructure, the customer manages content and possibly some limited configuration settings. Office 365 is an example of a SaaS offering.
The new Power BI experience, centered on PowerBI.com, is an online service that allows you can quickly create dashboards, share reports, and directly connect to (and integrate) a wide range of hosted customer data. This provides an easy, single point of access, solution for customers to gain new analytical insights from their data.
Partners using Microsoft's CSP program, can now empower their customers with turn-key solutions that provide
Managed Service Provider (MSP) partners who use remote monitoring, management, and professional services automation tools to manage customer environments can also provide Power BI Pro as an additional service offering.
This Power BI for CSPs Guidance will step through the technical details and requirements necessary for partners to deepen their engagement with customers through the creation of new data access solutions.
Before we go into more details, let's look at some terms used in Power BI:
A dataset is data uploaded into the Power BI service from an online or on-premises data source. You create a dataset when you use Get Data to connect to and upload data. Datasets appear in the left navigation pane. When you create reports and pin tiles to your dashboards, you're looking at data from your datasets.
A data source is where the data actually comes from. It can be just about anything; a database, Excel worksheet, Web service, etc. With Excel workbooks, you can create a simple worksheet with rows of data, and that is considered a data source. You can also use Power Query or Power Pivot in Excel to connect to and query data from both online and on-premises data sources, all in the same workbook. With Power BI Desktop, you use Get Data to connect to and query data from both online and on-premises data sources.
A gateway is different from a data source. The gateway is more of a proxy between on-premises and the cloud. When you configure a data source for a gateway, you are providing the connection information that the gateway will use to connect to the data source. An example of a data source would be a SQL Server deployed in your on-premises environment.
DirectQuery always gets the latest data using the credentials that were setup for the Data Source by the Gateway Admin.
This document is structured to cover various customer business data scenarios a Cloud Solution Provider program (CSP) partner may encounter and provides guidance on assisting customers to access and analyze data using Power BI. We will cover:
As a partner, the Cloud Solution Provider program (CSP) provides you with the ability to upsell Power BI to both new and existing customers. Using Microsoft Partner Center, https://partnercenter.microsoft.com you can easily sell Power BI to existing customers, connect it to their data sources and sell this solution as a single bundle thereby increasing the source of revenue from customers. Using the multi-channel capability in Partner Center, a CSP partner may also offer Power BI Pro to customers that have existing partners within the CSP program. MSP Partners may use the same steps detailed below to create born-in-the-cloud solutions that include Power BI Pro.
Using your CSP Partner credentials, login to the Microsoft Partner Center.
Figure 1. Login to the Microsoft Partner Center.
As an authorized Microsoft Partner, the Microsoft Partner Center provides you with delegated administration, allowing you to act as "Administrator on Behalf of" your customer.
For our document example, we will create an Administrator account for Fabrikam Products, Inc. that has permissions to add users / groups, and assign product licenses.
Figure 2. Create Customer and Admin Account
Figure 3. Add Customer and Admin Account
Figure 4. Create new Power BI Pro offering.
Now selecting Fabrikam Products, Inc., create an offering for a new Power BI Pro subscription that provides the appropriate number of user access licenses. For this guidance we will be using this customer.
You can also establish a CSP relationship with a customer that already has a partner within the CSP program by inviting the customer to give you "Delegated Admin Privileges." This process provides you with new delegated admin privileges but does not affect any existing relationships the customer has previously established with their Partner of Record.
You will know that the customer has accepted this invitation when they appear within your Partner Center list of customers. You can now create an offering for Power BI Pro as shown above (Figure 4).
Once your customer begins using Power BI Pro, they will then want to connect their existing data sources to PowerBI.com in order to view and analyze their data.
The first customer data source we will connect to as a Power BI customer solution is relational data stored within an Azure SQL database. Data residing within Azure, can be quickly brought into Power BI.
Figure 5. Connect Power BI to Azure SQL data.
To find an Azure SQL database we follow the steps shown in Figure 5, from PowerBI.com:
Figure 6. Connect Power BI to Azure SQL data source.
To connect to this data source, we need to provide Power BI with additional information. In figure 6 we
Figure 7. Verify Azure SQL connection
Once connectivity has been established, we can verify that our connection to the Azure SQL data source is working correctly by selecting the SQL database icon and inspecting the returned dataset, shown in Figure 7, circled on the right.
While there is significant momentum and benefit seen from moving existing data into a cloud-based Azure environment, many customers still maintain portions of their business data within hosted off-site data centers. Microsoft's CSP program provides an opportunity for partners to deepen the relationship with their customers by adding a cloud-based Power BI service, providing customers with analytical access to business data.
This document looks at configuration requirements for establishing an end-to-end customer solution that includes a combination of existing customer data sources. These same steps are also useful for hosting partners. They help integrate Power BI's Business Intelligence and Analytics visualization as part of a transition of customer services to a cloud-based solution.
Power BI today provides a wide range of data connector and gateway solutions that help customers easily establish connectivity to their existing data sources. Extensive step-by-step documentation to assist customers with installing and managing data connectivity solutions is provided on the Power BI Support pages located at https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-enterprise/
Figure 8. Connecting Power BI to existing customer data
Note: This guidance does not cover all possible step-by-step data connection details as these are extensively documented on the Power BI Support pages. We will instead focus on connectivity configurations that may initially appear less intuitive to implement.
The customer connectivity example shown below connects PowerBI.com to multiple cloud and on-premises (local and HSP hosted) data sources. To create a model of this environment for these examples, we used a database installation script to create and install sample relational database datasets for use within Power BI. Non-relational data was populated using a Comma Separated Value (CSV) file.
Figure 9: Customer Hybrid Data Configuration Details.
Looking at the various data access configurations shown in Figure 9, there are two common issues that need to be addressed to successfully connect various types of customer data to their new instance of Power BI Pro:
Detailed information on both of these connectivity technology solutions is provided below.
While much of the data shown in our customer solution may reside within Azure, it is realistic to assume that CSP partners will need to include access to portions of customer data still residing within on-premises environments. The challenge with this occurs when you need to validate each customer's credentials and show only the appropriate data for various types of authenticated users. Azure users are initially validated by Azure AD using login credentials provided from the customer tenant, but on-premises data is secured using different authentication systems. One of these systems is the domain model of AD DS. Azure provides a dynamic credential mapping service called Azure AD Connect that associates Azure AD users with their existing user credentials residing within Active Directory domains. This service is easy to install and dynamically updates to ensure that changes to user credentials are passed between both authentication systems.
Figure 10. User Authentication with Azure AD Connect
Note: Azure AD Connect only works when on-premises authentication is performed using the Domain model of Active Directory Domain Services in Windows Server. On-premise servers using stand-alone or Workgroup authentication cannot be configured to map user credentials in this way.
Detailed information for downloading and installing Azure AD Connect can be found here: https://azure.microsoft.com/en-us/documentation/articles/active-directory-whatis/
After installing Azure AD Connect on a Windows Server residing within the customer's hosted Active Directory domain (see detailed install instructions in the links provided above), CSP partners can enable authenticated Power BI access to a customer's remote data residing within a Service Provider on-premises environment.
In the Customer Hybrid Data Scenario (Figure 9 above) we also connect the customer's Power BI Pro tenant to existing Tabular Data from a customer's SQL Server Analysis Server residing within a hosted on-premises environment. This connectivity is provided by using Power BI Desktop to connect to and access the data. Using Power BI Desktop we create a Power BI report and this report is then published to Power BI Pro through the Power BI Enterprise Gateway.
Detailed Installation instructions for the Enterprise Gateway are available here: https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-enterprise/ and a software download of the Power BI Enterprise Gateway - Enterprise
is available here: https://powerbi.microsoft.com/en-us/gateway . This guidance highlights some of the important steps that will help you achieve a quick and successful installation of this Gateway.
Prior to installing the Enterprise Gateway carefully consider where you want this service will reside. To avoid latency issues, try to install your gateway on the same server as the on-premises data source you intend to connect to Power BI.
We installed the Enterprise Gateway on a separate server to demonstrate that configuration.
Figure 11. Provide Power BI credentials
Setting up the Enterprise Gateway is straight forward. Simply download the software from the link shown above and run setup on the selected machine. Once installed, you will be prompted to provide your Gateway with appropriate credentials to access Power BI on Azure (Figure 11 above). When choosing your user credentials, select a user account and password that will not experience frequent changes. Changes in either the password or user access credentials cause your Enterprise Gateway to stop functioning. To resolve the Enterprise Gateway not functioning, you must install the Enterprise Gateway again.
Note: Ensure that the user provided when setting up the Enterprise Gateway has data access credentials that permit querying of the appropriate database(s) and return data sets back to Power BI.
Figure 12. Add Remote Data Source for Power BI
Once it has been connected, the Enterprise Gateway allows you to select various remote data sources to use with Power BI. For this scenario, we are connecting the CSP partner's Power BI service to customer data residing in a SQL Server relational database that is located in an on-premises HSP environment. From within the Gateways screen of your Power BI Workspace, select ADD DATA SOURCE (shown in Figure 12 above).
Figure 13. Define Remote Data Source for Power BI
After providing the Server information, database name, and customer credentials needed to access this data, you can add this new data source as shown above (Figure 13).
Figure 14. Add Users to the Enterprise Gateway
As a CSP partner you can now provide Power BI access to the customer data residing within this on-premises environment.
In the customer solution detailed below the CSP partner is supporting multiple customers. Each of these customers is associated with their own instance of Azure Active Directory, with each customer acquiring their own space and resources from Azure. Azure AD performs user authentication, and based on the user's credentials, grants access to resources owned by that user's organization. CSP partners can support their customers by offering managed services including configuration assistance within this type of "hybrid multi-data source" environment.
Figure 15. Providing Power BI for multiple existing customers
This hybrid configuration uses the following steps to allow a CSP partner who also provides customers with on-premises data services to connect their existing customers to Power BI Pro. A CSP partner who does not own the customer's database can provide consulting services to enable Power BI access into on-premises data or hosted data.
While all of a customer's data may not be hosted by the CSP partner, it is still possible for the customer to provide specific data that can be used to populate partner created customer dashboards. This can be accomplished either statically using Power BI Desktop or by using regularly scheduled, refreshed data from the Power BI Personal Gateway. The steps for providing a static image of specific customer data are shown in Figure 16 below.
Figure 16. Define data source
In this example, the customer uses Power BI Desktop to select on-premises data that they want to provide as part of a Power BI Pro solution. In the case shown here, they have selected data available to Power BI Desktop through an ODBC connection.
Figure 17. Publish from Desktop to CSP's PowerBI.com
After creating a dashboard report, the customer selects the Power BI Desktop "Publish" option as shown above (Figure 17).
Figure 18. Data Set Persists on Power BI
This method provides Powerbi.com with a static version of customer data to include within a partner created customer dashboard. If the customer prefers to allow for periodic refreshes of this data, they can accomplish this through use of the Power BI Personal Gateway.
Within customer data environments where the Power BI Enterprise Gateway provides access to the Data Source published by Power BI Desktop, selecting the Synchronized Refresh option for the Data Set allows you to choose Power BI's DirectQuery option. Power BI's DirectQuery then refreshes the Data Set from the underlying Data Source whenever an authorized user accesses this published information.
In the initial sections of this guidance we provided step-by-step walkthroughs to assist CSP partners in connecting various customer data sources to Power BI. We will now look at ways that Power BI provides additional opportunities for partners to participate with their customers and offer solutions that generate additional revenue.
Many small to medium sized businesses hope to grow by gaining strategic insights into their specific marketplace. However, organizations of this size usually have neither the budget nor the in-house expertise to acquire the statistical data needed to accomplish this type of research and analysis. The CSP program provides a unique opportunity for partners creating value added services for customers to offer a competitive advantage by creating and marketing industry-specific dashboards and reports. The partner's customers can then use Power BI to help them make informed, strategic business decisions.
In this document we will be looking at how a CSP partner can implement customer data source solutions based on sales data contained within an example database. Implementation of a real-world solution requires the partner to have access to sales and forecast information relevant to their customer's specific business needs. This data is often available from multiple data providers on the internet, some free (open data sourced) and some fee-based (closed data source).
Here are a few examples of internet addresses for data source providers. Many more can be found through online searches:
For our example, we will have our partner access data provided by "WidgetSalesData.com" in the form of a "Comma Separated Value" (CSV) file. This is a standard delivery mechanism provided when obtaining sales and forecast data. We will explore differing uses of these industry-specific data sources that provide CSP partners with opportunities to both assist their customers and generate additional ongoing revenues.
Figure 19. External Data Feed Opportunities
As part of an ongoing service, WidgetSalesData.com provides both historical information regarding world-wide sales of Widgets as well as forecasts of future sales, all of this data was provided by country and fiscal calendar quarter. Figures 20 and 21 below show the format for a small sampling from both of these example datasets.
Figure 20. Sample set of historical data
Figure 21. Sample set of forecast data
Several slight adjustments to the formatting shown above turn this information into a dataset that is easily managed within Power BI. Using Excel, either manually or with previously created scripts, we make this data easier to visualize within Power BI by removing the second set of country headings, grouping both unit and revenue data together by quarter. We have also simplified each column heading. None of these actions are strictly required by Power BI, but this turns our dataset into a flat CSV file that is easier to work with and requires little to no joining of fields to create useful reports.
Figure 22. Sample set of merged historical data.
Figure 22 shows the same set of sample data after these modifications. Once our dataset has been converted into this flattened CSV format, when connected to Power BI it is presented in the following way.
Figure 23. Sample set with merged customer sales.
Figure 24. Partner Created Dashboard – Merged Customer Unit Sales
Figure 25. Partner Created Dashboard – Merged Customer Sales Revenue
Adding dedicated industry specific information now allows a CSP partner to create dashboard and report subscriptions that are customized to meet their customer's needs. For instance, imagine that our demo scenario customer, "Fabrikam Products, Inc.", would like to provide their CSP partner with company sales data and have it merged for them with reports of both worldwide sales of Widgets and future forecasts of worldwide Widget sales for the upcoming fiscal year. Additionally, they want this data segmented so that their North American products division only sees results for North American division customers, and their European products division sees only the information related to that division's customers. Examples of these desired reports are shown in Figures 27 and 28 below.
Using Power BI, the partner can provide a dedicated dataset for each report. All users granted access to that report are then allowed to create a personal copy of the dataset for further analysis. It is important to restrict the scope of the dataset provided for each report so that it contains only information that user group is authorized to see. To accomplish this, our Fabrikam Products administrator provides the partner with two smaller datasets, one containing sales information from their North American Division and the other containing sales from their European Division.
Figure 26. Sample set with merged EU customer sales.
The partner will then combine each of the two smaller datasets with their unrestricted subscription information concerning worldwide Widget sales and future sales forecasts.
Figure 27. Merged NA Division Unit Sales & Forecast Report.
Figure 28. Merged EU Division Unit Sales & Forecast Report.
As a Power BI Pro customer using PowerBI.com, the partner can create group-specific dashboards and reports for their customer's use by using Power BI's organizational content pack feature.
Organizational content packs allow data to be shared with specific groups within an organization. This feature uses the user login ID domain credentials to ensure that the user publishing an organizational content pack is in fact a member of that organization. One "work around" that enables a CSP partner to publish organizational content packs for their customers is to have each customer allow their provider to create and use (1) dedicated customer login ID which has been provisioned in the customer's tenant with access to PowerBI.com. For this exercise, Fabrikam Products, Inc. has allowed their CSP partner to create and use the account "email@example.com". This user becomes the partner's creator of customer organizational content packs and retains ownership of the published dashboards and reports. Content packs determine which group or groups can see the published data, whether editing of data is allowed, and when the shared dataset will be refreshed. The group designated to receive this content can be either a pre-existing set of Office 365 users, a similar group within the organization, or a newly defined group created by the author of the content pack.
Important: All members of the group will inherit the same access privileges to the information contained within the content pack.
For this exercise, we will create two new groups within Powerbi.com reflecting the North American and European divisions of our demo organization "Fabrikam Products, Inc."
Figure 29. Adding new Fabrikam Products EU Group.
In Figure 29 above, we first select the option to add a new group, name this group "Fabrikam Products EU", set permissions to "Read Only" for the group, then add login emails for users of the group. In this case, we add two generic accounts – firstname.lastname@example.org and productseu.@acmeproducts.com. While this involves adding a lot of detailed information, it only needs to be established once. Multiple users within the organization can then be granted access by the customer to use these group email accounts. Going forward, any created report can simply be updated either manually or programmatically by the provided partner account and all Fabrikam Products users granted permissions to access either of these EU group email accounts will see the updated data.
We also create another group, Fabrikam Products NA, in the same way. Having established two separate groups, we can now begin the process to create an organizational content pack for each of them. After selecting the group we want to publish to, select the "Get Data" button of that group's Content Pack Library (Figure 30).
Figure 30. Access Fabrikam Products EU content packs.
Select the "Create content pack" button to begin the process of publishing content for our new group (Figure 31).
Figure 31. Create new Fabrikam Products EU content pack.
PowerBI.com does not currently support the online merging of separate datasets, but there are multiple ways for a partner to create and publish this information within a subscription report. The method selected should take into account the skills and experience of CSP partner's personnel. (For example, a multiple table SQL join solution would not be the best choice if there are no personnel with that required SQL expertise.)
Possible implementation solutions include:
Because solution 3 is the most generalized, we will work with this solution. Figure 32 shows a sampling of our csv data file after using Microsoft Excel to merge sales and forecast data together.
Figure 32. Sampling of Merged Dataset for PowerBI.com
We are now able to use PowerBI.com to access each of our recently created customer groups and create a dedicated organizational content pack that displays all of the required information filtered by divisional sales areas.
Figure 33. Organizational Content Pack NA Sales page 1.
Figure 34. Organizational Content Pack NA Sales page 2.
Having created a dedicated report for the North American Division, we now publish this this report by creating a Power BI organizational content pack.
Figure 35. Publish NA Sales organizational content pack.
Users with permissions to view this report are added to the content pack. A similar organizational content pack is also created and published for the European Division.
Figure 36. NA Sales Manager presented only NA Division Organizational Content Packs.
When Fabrikam Products' North America personnel log into Power Bi and open the Content Pack Library, notice that they only see CSP created reports containing their North American sales data merged with North American area sales and forecasts.
Figure 37. Connecting to Dec 2015 Unit Sales and Forecast.
Figure 38. New report now available for NA Sales Manager.
Figure 39. NA Sales report with merged CSP Unit Sales Data and Forecast.
The example above walked through the steps required for a CSP to use Power BI Pro to create and market customer-focused, industry-specific dashboards and reports. This is just one of the many opportunities that Power BI provides for CSPs to further engage, assist, participate with their customers, and offer solutions that generate additional CSP revenue.
For testing, we created a comma separated data file containing transactions for the worldwide sales of "Widgets" by fictional company "Fabrikam Products Inc." The format of this file can be seen in Figure 1A below.
Figure 1A. Format of CSV sales data.
To create a sales database for testing, we created a Transact-SQL script that when executed on a database server running Microsoft SQL Server 2008 or higher, creates and populates a "star schema" database. Information contained within this database reflects the same sales transactions contained within our comma separated data file. A star schema is a database table configuration often found within Data Mart servers used to analyze sales data. The Schema for this test data is shown below.
Figure 3. Star Schema for test CSV sales database