Extracting Metadata from PowerBI datasets to Snowflake Table Using Azure Function

In this blog, we will learn how to Extract metadata from PowerBI and store the metadata in the form of a JSON file in the azure storage account and view the JSON file as a table in snowflake using Staging.

• Connect to PowerBI.
• Extracting data from PowerBI and storing it as JSON files in a local folder.
• Obtaining credentials from azure for uploading JSON files from a local folder to blob.
• Uploading the local file to the Azure blob storage account.
• Reading blob file using Staging in snowflake.

The Code that has been developed is from visual studio code using C#. which includes an interactive debugger, so the user can step through the source code, inspect variables, view call stacks, and execute commands in the console.

Install the packages with the same version which is given below

• Microsoft.Rest.ClientRuntime(version 2.3.24).
• RestSharp(version 106.13.0).
• Microsoft.Exchange.WebServices(version 2.2.0).

Step 1: Connect to PowerBI.

• First, we need to have the following credentials PowerBI client id, PowerBI client secret, PowerBI Tenant id, and Oauth_url.
• Then, we need to create a string with a directory local folder, and a RestClient request for logging in.

The below code shows how to create a login to RestClient,

Code 1:

string basefolderforoutput = @"**************************”;
Console.WriteLine("Extracting PowerBI Refresh details.....");
string powerbi_client_id = "************************************";
string powerbi_client_secret = "**********************************";
string powerbi_tenatnt_id = "**********************************";
string ad_login = "***************************************";
string oauth_url = "*******************************************";
string adlogin_formatted = string.Format(ad_login, powerbi_tenatnt_id);
var client = new RestClient(adlogin_formatted);
client.Timeout = -1;

• Create a RestRequest and add the following header and parameters to make a request.

The below code shows how to form a RestRequest,

Code 2:

var request = new RestRequest(Method.POST);
request.AddHeader("Content-Type", "application/x-www-form-urlencoded");
request.AddParameter("client_id", powerbi_client_id);
request.AddParameter("client_secret", powerbi_client_secret);
request.AddParameter("grant_type", "client_credentials");
request.AddParameter("resource", oauth_url);
IRestResponse response = client.Execute(request)  Console.WriteLine(response.Content);

• The below code will acquire the access token from the request made in the previous code and list all the workspace to which the user has access.

Code 3:

string oAuth_token = String.Format("Bearer {0}", oauth_api_json.access_token);
List<ds_Root> lst_ds = new List<ds_Root>();
// Console.WriteLine(oAuth_token);
//List all workspaces user have access to
var rs_powerbi_client = new RestClient("https://api.powerbi.com/v1.0/myorg/groups");
rs_powerbi_client.Timeout = -1;
var rs_powerbi_request = new RestRequest(Method.GET);
rs_powerbi_request.AddHeader("Authorization", oAuth_token);
IRestResponse powerbi_workspace_response = rs_powerbi_client.Execute(rs_powerbi_request);
log.loginformation(powerbi_workspace_response.Content);
powerbi_workspace_response_Root myDeserializedClass = JsonConvert.DeserializeObject<powerbi_workspace_response_Root>(powerbi_workspace_response.Content);

List<powerbi_workspace_response_Value> lst_ws_inscope = myDeserializedClass.value;
List<refresh_details> response_output = new List<refresh_details>();

Step 2: Extract data from PowerBI and store it as a JSON file on a local premises.

• The following code is to iterate each workspace that the user has access to with workspace id.

Code 4:

foreach (var item in lst_ws_inscope)
{
//Iterate each workspace for all dataset
var ds_client = new RestClient(String.Format("https://api.powerbi.com/v1.0/myorg/groups/{0}/datasets", item.id));
client.Timeout = -1;
var ds_request = new RestRequest(Method.GET);
ds_request.AddHeader("Authorization", oAuth_token);
IRestResponse ds_response = ds_client.Execute(ds_request);
Console.WriteLine(ds_response.Content);
ds_Root ds_myDeserializedClass = JsonConvert.DeserializeObject<ds_Root>(ds_response.Content);
lst_ds.Add(ds_myDeserializedClass);
List<ds_Value> lst_ds_inscope = ds_myDeserializedClass.value;
}

• The below code will iterate through all the datasets in the workspace.

Code 5

foreach (var ds_item in lst_ds_inscope)
{
if (ds_item.isRefreshable)
{
//Iterate each dataset to get refresh history
var ds_refresh_client = new RestClient(string.Format("https://api.powerbi.com/v1.0/myorg/groups/{0}/datasets/{1}/refreshes?$top=100", item.id, ds_item.id));                        ds_refresh_client.Timeout = -1;
var ds_refresh_request = new RestRequest(Method.GET);
ds_refresh_request.AddHeader("Authorization", oAuth_token);
ds_refresh_request.AddHeader("Content-Type", "application/json");
IRestResponse ds_refresh_response = ds_refresh_client.Execute(ds_refresh_request);
Console.WriteLine(ds_refresh_response.Content);
ds_dataset_refresh_root ds_refresh_myDeserializedClass = JsonConvert.DeserializeObject<ds_dataset_refresh_root>(ds_refresh_response.Content);
refresh_details rs = new refresh_details();
rs.datasetID = ds_item.id;
rs.datasetName = ds_item.name;
rs.workspaceId = item.id;
rs.wworkspacename = item.name;
rs.refresh_data = ds_refresh_myDeserializedClass;
response_output.Add(rs);
}
}

• Declare a string with the directory path in the local on-premises and store the metadata in form of JSON in the local on-premises.

The below code is to store the collected data from PowerBI and store it in the on-premises directory which is provided in the form of JSON.

Code 6:

// Write Response to file
string JSONresult = JsonConvert.SerializeObject(response_output);
string path = basefolderforoutput + "queryresult.json";
using (var tw = new StreamWriter(path, false))
{
tw.WriteLine(JSONresult.ToString());
tw.Close();
}
//write workspacemeta
JSONresult = JsonConvert.SerializeObject(myDeserializedClass);
path = basefolderforoutput + "workspacemeta.json";
using (var tw = new StreamWriter(path, false))
{
tw.WriteLine(JSONresult.ToString());
tw.Close();
}
//write datasetset details
//write workspacemeta
string DS_JSONresult = JsonConvert.SerializeObject(lst_ds);
using (var tw = new StreamWriter(DS_path, false))
{
tw.WriteLine(DS_JSONresult.ToString());
tw.Close();
}

Step 3: Obtaining credentials from azure for uploading the on-premises file to blob.

To upload the file to the blob we need the Client secret key, tenant/Directory id, and application id. For that, we need to create an application on Azure.

• Go to Home in Search type App registration Create. Create an application of Web app/API type on the Azure portal.

Figure 1: Creating App registration in Azure

• Get Keys for Authentication

Figure 2: Above image shows the credentials for connecting to the storage account

• Now, go to your storage account that you want to use and assign your application Storage Blob Data Owner/Contributor Role.

Figure 3: Above image shows assigning a role to the storage account for the app registration.

Figure 4: above image shows the role assigned to the app registration.

Step 4: Upload the local on-premises file to the Azure blob storage account.

• Go to the code and declare a string as follows and give the appropriate Credentials, container name, and blob name where the JSON file needs to be dropped.

See the below code for reference,

Code 7:

public static void BlobUpload(string srcPath)
{
AzureOperations.applicationId = "**************************";
AzureOperations.clientSecret = "******************************";
AzureOperations.tenantId = "**********************************";
string[] filePaths = Directory.GetFiles(srcPath);
foreach (string filePath in filePaths)
{
UploadFile(filePath);
}
}
public static void UploadFile(string srcPath)
{
AzureOperationHelper azureOperationHelper = new AzureOperationHelper();
// your Storage Account Name
azureOperationHelper.storageAccountName = "samplestorageaccount";
azureOperationHelper.storageEndPoint = "core.windows.net";
// File path to upload
azureOperationHelper.srcPath = srcPath;
// Your Container Name
azureOperationHelper.containerName = "sample-container";
// Destination Path you can set it file name or if you want to put it in folders do it like below
azureOperationHelper.blobName = string.Format("PowerBI/" + Path.GetFileName(srcPath));
AzureOperations.UploadFile(azureOperationHelper);
}

• With the help of the above code, it will create a connection to the blob directory in the azure and upload the file in it.

Code 8:

public static void UploadFile(AzureOperationHelper azureOperationHelper)
{
try
{
CloudBlobContainer blobContainer = CreateCloudBlobContainer(tenantId, applicationId,clientSecret,azureOperationHelper.storageAccountName,azureOperationHelper.containerName,azureOperationHelper.storageEndPoint);
blobContainer.CreateIfNotExistsAsync();
CloudBlockBlob blob = blobContainer.GetBlockBlobReference(azureOperationHelper.blobName);
blob.UploadFromFileAsync(azureOperationHelper.srcPath).Wait();
Console.WriteLine("File Has been Uploaded");
}
catch (Exception e)
{
Console.WriteLine("Error: " + e.Message);
string error = e.Message.ToString();
}
}

• If you want to create a new container in the Azure use the following code below.

Code 9:

private static CloudBlobContainer CreateCloudBlobContainer(string tenantId, string applicationId, string clientSecret, string storageAccountName,
string containerName, string storageEndPoint)
{
string accessToken = GetUserOAuthToken(tenantId, applicationId, clientSecret);
TokenCredential tokenCredential = new TokenCredential(accessToken);
StorageCredentials storageCredentials = new StorageCredentials(tokenCredential);
CloudStorageAccount cloudStorageAccount = new CloudStorageAccount(storageCredentials, storageAccountName, storageEndPoint, useHttps: true);
CloudBlobClient blobClient = cloudStorageAccount.CreateCloudBlobClient();
CloudBlobContainer blobContainer = blobClient.GetContainerReference(containerName);
return blobContainer;
}

• Get the Oauth Token for creating the container for the above mentioned code.

The below mention code is for getting the Oauth token for creating the container.

Code 10:

static string GetUserOAuthToken(string tenantId, string applicationId, string clientSecret)
{
const string ResourceId = "https://storage.azure.com/";
const string AuthInstance = "https://login.microsoftonline.com/{0}/";
string authority = string.Format(CultureInfo.InvariantCulture, AuthInstance, tenantId);
AuthenticationContext authContext = new AuthenticationContext(authority);
var clientCred = new Microsoft.IdentityModel.Clients.ActiveDirectory.ClientCredential(applicationId, clientSecret);
Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationResult result = authContext.AcquireTokenAsync(
ResourceId,
clientCred
).Result;
return result.AccessToken;
}

Step 5: Reading blob file using Staging in snowflake

• Go to the snowflake worksheet and create a stage for Azure, so that the data in the JSON file can be viewed in the table.

Refer the below SQL query to stage in snowflake,

Query:

create file format myjsonformat type = json;
 create or replace stage query_result_test url =’Blob url in here’ 
 credentials=(azure_sas_token='**********************************’)
 file_format = myjsonformat;

Conclusion:

Finally, we can view the refresh detail data which is extracted from PowerBI and stored in the Azure blob.

About us:

Bi3 has been recognized for being one of the fastest-growing companies in Australia. Our team has delivered substantial and complex projects for some of the largest organizations around the globe and we’re quickly building a brand that is well known for superior delivery.

Website: https://bi3technologies.com/

Follow us on,
LinkedIn : https://www.linkedin.com/company/bi3technologies
Instagram : https://www.instagram.com/bi3technologies/
Twitter : https://twitter.com/Bi3Technologies