Copy a pbix template report to a tenant app workspace

PowerBiEmbeddedManager.cs

using Microsoft.Azure.Management.ResourceManager;
using Microsoft.IdentityModel.Clients.ActiveDirectory;
using Microsoft.PowerBI.Api.V2;
using Microsoft.PowerBI.Api.V2.Models;
using Microsoft.Rest;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Threading;
using System.Threading.Tasks;

namespace Nfield.Reporting.Core.Persistence.PowerBI
{
	/// <summary>
	/// Utility class that provides connectivity to the PowerBI service.
	/// </summary>
	public class PowerBIEmbeddedManager
	{
		// In real life these settings are injected to the constructor using a Microsoft.Extensions.Configuration.IConfiguration class.
		private static string POWER_BI_API_URL = "https://api.powerbi.com";
		private static string POWER_BI_AAD_USERNAME = "powerbidevmaster@mydomain.onmicrosoft.com";
		private static string POWER_BI_API_AAD_USER_PASSWORD = "A_COMPLEX_PASSWORD";
		private static string POWER_BI_AUTHORITY_URL = "https://login.windows.net/common/oauth2/authorize/";
		private static string POWER_BI_RESOURCE_URL = "https://analysis.windows.net/powerbi/api";
		private static string POWER_BI_AAD_APP_ID = "THE_AAD_APPLICATION_GUID";

		/// <summary>
		/// Copies a report to the same or another workspace. 
		///  It also updates the connection and the credentials that will be used to access the data.
		/// </summary>
		/// <param name="templateWorkspaceId">The workspace id where the report resides.</param>
		/// <param name="tenantWorkspaceId">The destination app workspace.</param>
		/// <param name="templateReportId">The id of the pbix report (that will be used as the template) that will be copied.</param>
		/// <param name="server">The server name for the connection update.</param>
		/// <param name="database">The database name for the connection update.</param>
		/// <param name="userName">The username for the credentials update.</param>
		/// <param name="password">The password for the credentials update.</param>
		/// <returns>The cloned report id.</returns>
		public async Task<string> CloneReportAsync(string templateWorkspaceId, string tenantWorkspaceId, string templateReportId, string server, string database, string userName, string password, string displayName, IDictionary<string, string> parameters)
		{
			using (var client = await CreatePowerBiClientAsync())
			{
				var existingWorkspaces = await client.Groups.GetGroupsAsync();

				if (!existingWorkspaces.Value.Any(x => x.Id == templateWorkspaceId) || !existingWorkspaces.Value.Any(x => x.Id == tenantWorkspaceId))
				{
					throw new Exception("CustomReportsAppWorkspaceDoesNotExist");
				}

				// The preferred way when you have one dataset for each report is to export-import the report.
				// If you have one dataset for many reports then Clone-Rebind seems more appopriate.
				// Also, when you CloneReport, you should first manually create and copy the dataset to the new workspace. 
				var reports = await client.Reports.GetReportsInGroupAsync(templateWorkspaceId);
				if ((!reports.Value.Any() || !reports.Value.Any(x => x.Id == templateReportId)))
				{
					throw new Exception("CustomReportDoesNotExist");
				}
				var storedReport = reports.Value.Single(x => x.Id == templateReportId);
				var originalReportStream = await client.Reports.ExportReportInGroupAsync(templateWorkspaceId, storedReport.Id);

				var import = await TryUploadAsync(client, tenantWorkspaceId, originalReportStream, (string.IsNullOrEmpty(displayName)) ? storedReport.Name : displayName);
				var reportDatasetId = import.Datasets.First().Id;

				try
				{
					// Gets the parameters that the dataset contains.
					var reportParameters = await client.Datasets.GetParametersInGroupAsync(tenantWorkspaceId, reportDatasetId);
					// If there are parameters, then we would update their values. 
					if (reportParameters != null && reportParameters.Value.Any())
					{
						await SetReportParameters(client, tenantWorkspaceId, reportDatasetId, reportParameters.Value, parameters);
					}

					var gatewayDatasources = await client.Datasets.GetGatewayDatasourcesInGroupAsync(tenantWorkspaceId, reportDatasetId);
					// We patch the credentials for reports using Azure Sql datasources (either DirectQuery or ImportMode).
					if (gatewayDatasources != null && gatewayDatasources.Value.Any())
					{
						// We patch the credentials when there aren't any parameters associated with the connection (like server or database).
						// If we try to patch the credentials when connection related parameters exist, we get an exception: `Operation is not supported for selector - connection details contains parameters`
						if (reportParameters != null && !reportParameters.Value.Any(x => x.Name == "ServerParameter" || x.Name == "DatabaseParameter"))
						{
							// Updates the connection details: updating the Server and the Database for the domain 
							await PostUpdateDatasourcesConnectionAsync(tenantWorkspaceId, reportDatasetId, gatewayDatasources.Value.First().ConnectionDetails, server, database);
						}

						// Must Refresh the GatewayDatasources before trying to update the credentials.
						// After the refresh the GatewayDatasources will point to the correct server and database.
						// Otherwise, they would point to the report's initial server and database.
						gatewayDatasources = await client.Datasets.GetGatewayDatasourcesInGroupAsync(tenantWorkspaceId, reportDatasetId);

						// Updates the credentials for the connection
						await PatchGatewayDatasourcesCredentialsAsync(client, gatewayDatasources.Value, tenantWorkspaceId, userName, password);

						// Refreshes the dataset
						var storedReportDataset = await client.Datasets.GetDatasetByIdInGroupAsync(tenantWorkspaceId, reportDatasetId);
						if (storedReportDataset.IsRefreshable.HasValue && storedReportDataset.IsRefreshable.Value == true)
						{
							await PostRefreshDataSetAsync(tenantWorkspaceId, storedReportDataset.Id);
						}
					}
				}
				catch (Exception exc)
				{
					// The process failed to complete. Deletes the dataset and the associated report that have been created.
					await client.Datasets.DeleteDatasetByIdInGroupAsync(tenantWorkspaceId, reportDatasetId);
					if (exc is HttpOperationException httpException)
					{
						if (httpException.Response.StatusCode == HttpStatusCode.BadRequest)
						{
							if (httpException.Response.Content.Contains("DM_GWPipeline_Gateway_DataSourceAccessError"))
							{
								throw new Exception("CustomReportsInvalidCredentials");
							}
						}
						throw httpException;
					}
					throw exc;
				}
				return import.Reports[0].Id;
			}
		}

		#region Private Methods

		#region PowerBiClient Initialization

		/// <summary>
		/// Creates a client to connect to the PowerBI infrastructure based on the Token Credentials of the PowerBI User and the related AAD application.
		/// </summary>
		/// <returns>A IPowerBIClient.</returns>
		private async Task<IPowerBIClient> CreatePowerBiClientAsync()
		{
			return new PowerBIClient(new Uri(POWER_BI_API_URL), new TokenCredentials(await GetPowerBiAccessToken(), "Bearer"));
		}

		/// <summary>
		/// Returns an AccessToken for the PowerBi service.
		/// </summary>
		/// <returns></returns>
		private async Task<string> GetPowerBiAccessToken()
		{
			var powerBiAccountCredentials = new UserCredential(POWER_BI_AAD_USERNAME, POWER_BI_API_AAD_USER_PASSWORD);
			var authenticationContext = new AuthenticationContext(POWER_BI_AUTHORITY_URL);
			var authenticationResult = await authenticationContext.AcquireTokenAsync(POWER_BI_RESOURCE_URL, POWER_BI_AAD_APP_ID, powerBiAccountCredentials);
			return authenticationResult.AccessToken;
		}

		#endregion

		#region Power BI REST API Calls

		/// <summary>
		/// Helper method to call an API endpoint.
		/// </summary>
		private async Task<HttpResponseMessage> SendAsync(string endpoint, string httpMethod, HttpContent content = null)
		{
			var request = new HttpRequestMessage(new HttpMethod(httpMethod), endpoint) { };

			var client = new HttpClient
			{
				Timeout = TimeSpan.FromMilliseconds(900000)
			};
			client.DefaultRequestHeaders.Add("Accept", "application/json; charset=utf-8");
			client.DefaultRequestHeaders.Add("Authorization", "Bearer " + (await GetPowerBiAccessToken()));

			if (content != null)
			{
				request.Content = content;
			}

			var response = await client.SendAsync(request, default(CancellationToken));
			if (!response.IsSuccessStatusCode)
			{
				var exc = new HttpOperationException()
				{
					Response = new HttpResponseMessageWrapper(response, await response.Content.ReadAsStringAsync())
				};
				throw exc;
			}
			return response;
		}

		private async Task PostRefreshDataSetAsync(string workspaceId, string dataSetId)
		{
			string postRefreshEndpoint = FormattableString.Invariant($"https://api.powerbi.com/v1.0/myorg/groups/{workspaceId}/datasets/{dataSetId}/refreshes");
			await SendAsync(postRefreshEndpoint, "POST");
		}

		/// <summary>
		/// Generic GET request for retrieving PowerBi related objects (groups, datasets, etc).
		/// Used because of TaskCancelled exceptions when using PowerBiClient (in RefreshDatasets) due to timeouts.
		/// Trying to set the CancellationToken's timeout (using CancellationTokenSource) didn't work.
		/// </summary>
		/// <typeparam name="T">The type to return.</typeparam>
		/// <param name="endpoint">The endpoint to be used for the request</param>
		/// <returns>An object of type T.</returns>
		private async Task<T> GetAsync<T>(string endpoint)
		{
			var response = await SendAsync(endpoint, "GET");
			var responseContent = await response.Content.ReadAsStringAsync();
			return SafeJsonConvert.DeserializeObject<T>(responseContent);
		}

		private async Task<Import> PostImportAsync(string workspaceId, Stream content, string datasetDisplayName)
		{
			var displayNameQueryParameter = (!string.IsNullOrEmpty(datasetDisplayName))
				? FormattableString.Invariant($"?datasetDisplayName={Uri.EscapeDataString(datasetDisplayName)}")
				: string.Empty;
			string postImportEndpoint = FormattableString.Invariant($"https://api.powerbi.com/v1.0/myorg/groups/{workspaceId}/imports{displayNameQueryParameter}");
			var response = await SendAsync(postImportEndpoint, "POST", content);
			var responseContent = await response.Content.ReadAsStringAsync();
			return SafeJsonConvert.DeserializeObject<Import>(responseContent);
		}

		#endregion

		#region Using Power BI Embedded SDK Methods
		/// <summary>
		/// Tries to import a file stream to the PowerBI service.
		/// </summary>
		/// <param name="client">The PowerBIClient</param>
		/// <param name="workspaceId">The workspace where the report will be imported</param>
		/// <param name="content">The file stream</param>
		/// <param name="datasetDisplayName">The display name for the dataset</param>
		/// <returns>An Import class with the information for the import process.<c>Null</c>, in case of a failed import process.</returns>
		private async Task<Import> TryUploadAsync(IPowerBIClient client, string workspaceId, Stream content, string datasetDisplayName)
		{
			// We use the API because at Import method the SDK fails when trying directly upload an exported readonly stream (because it now checks for length).
			// In order to avoid the stream transformation, we prefer to use the API.
			var import = await PostImportAsync(workspaceId, content, datasetDisplayName);
			// Polling the import to check when the import has succeeded.
			while (import.ImportState != "Succeeded" && import.ImportState != "Failed")
			{
				import = await client.Imports.GetImportByIdInGroupAsync(workspaceId, import.Id);
			}
			if (import.ImportState != "Succeeded")
			{
				return null;
			}
			return import;
		}

		/// <summary>
		/// Updates the credentials for the Gateway Datasource.
		/// </summary>
		private async Task PatchGatewayDatasourcesCredentialsAsync(IPowerBIClient client, IList<GatewayDatasource> gatewayDatasources, string workspaceId, string userName, string password)
		{
			foreach (var gatewayDatasource in gatewayDatasources)
			{
				try
				{
					await client.Gateways.UpdateDatasourceAsync(gatewayDatasource.GatewayId, gatewayDatasource.Id, new UpdateDatasourceRequest
					{
						CredentialDetails = new CredentialDetails
						{
							Credentials = $"{{\"credentialData\":[{{\"name\":\"username\",\"value\":\"{userName}\"}},{{\"name\":\"password\",\"value\":\"{password}\"}}]}}",
							CredentialType = "Basic",
							EncryptedConnection = "Encrypted",
							EncryptionAlgorithm = "None",
							PrivacyLevel = "Private"
						}
					});
				}
				catch (HttpOperationException exc)
				{
					if (exc.Response.StatusCode == HttpStatusCode.BadRequest)
					{
						if (exc.Response.Content.Contains("DM_GWPipeline_Gateway_DataSourceAccessError"))
						{
							throw new Exception("CustomReportsInvalidCredentials");
						}
					}
					throw exc;
				}

			}
		}

		/// <summary>
		/// Updates the connection for the Datasource.
		/// </summary>
		private async Task PostUpdateDatasourcesConnectionAsync(string groupId, string datasetKey, string initialConnectionDetails, string newServer, string newDatabase)
		{
			var connectionDetailsAsJson = JObject.Parse(initialConnectionDetails);

			var endpoint = $"https://api.powerbi.com/v1.0/myorg/groups/{groupId}/datasets/{datasetKey}/updatedatasources";
			var updateDatasourcesRequestContent = Microsoft.Rest.Serialization.SafeJsonConvert.SerializeObject(
				new UpdateDatasourcesRequest
				{
					UpdateDetails = new List<UpdateDatasourceConnectionRequest>
						{
							new UpdateDatasourceConnectionRequest
							{
								ConnectionDetails = new DatasourceConnectionDetails
								{
									Server = newServer,
									Database = newDatabase
								},
								DatasourceSelector = new Datasource
								{
									DatasourceType="Sql",
									ConnectionDetails = new DatasourceConnectionDetails
									{
										Server = connectionDetailsAsJson["server"].ToString(),
										Database =connectionDetailsAsJson["database"].ToString()
									}
								}

							}
						}
				}
			);

			var content = new StringContent(updateDatasourcesRequestContent, System.Text.Encoding.UTF8);
			content.Headers.ContentType = MediaTypeHeaderValue.Parse("application/json; charset=utf-8");

			await SendAsync(endpoint, "POST", content);
		}

		private static async Task SetReportParameters(IPowerBIClient client, string tenantWorkspaceId, string reportDatasetId, IEnumerable<DatasetParameter> reportParameters, IDictionary<string, string> parameters)
		{
			if (reportParameters != null && reportParameters.Any())
			{
				if (((parameters == null || !parameters.Any()) && reportParameters.Any(x => x.IsRequired)) ||
					((parameters != null || parameters.Any()) && reportParameters.Any(x => x.IsRequired && !parameters.Keys.Contains(x.Name))))
				{
					throw new Exception("RequiredParameterValueMissing");
				}

				if (parameters != null && parameters.Any())
				{
					var updateParameterList = new List<UpdateDatasetParameterDetails>();
					foreach (var reportParameter in reportParameters)
					{
						if (parameters.ContainsKey(reportParameter.Name))
						{
							updateParameterList.Add(new UpdateDatasetParameterDetails { Name = reportParameter.Name, NewValue = parameters[reportParameter.Name] });
						}
					}
					if (updateParameterList != null && updateParameterList.Any())
					{
						await client.Datasets.UpdateParametersInGroupAsync(tenantWorkspaceId, reportDatasetId, new UpdateDatasetParametersRequest
						{
							UpdateDetails = updateParameterList
						}
						);
					}
				}
			}
		}

		#endregion

		#endregion

	}
}