CsHttpPowerBiXmlaProfiler.cs

/*
DAXPROSERVICES.COM - CSHttpPowerBIXMLAProfiler example

Function authentication (SAS).
Query URI parameters: workspaceName
Requisite: Username and password to access the Power BI XMLA Endpoints (hardcoded in this example)
Returns:
ZIP File, named "Workspace Name-Dataset Name.zip"
- Datasets
- - TableExpressions
- - Expressions

*/
using System;
using System.Collections.Generic;
using System.IO;
using System.IO.Compression;
using System.Text;
using System.Threading.Tasks;

using Microsoft.AnalysisServices.Tabular;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Extensions.Http;
using Microsoft.Extensions.Logging;

namespace Custom.PowerBI
{
    public static class CSHttpPowerBIXMLAProfiler
    {
        [FunctionName("CSHttpPowerBIXMLAProfiler")]
        public static async Task<IActionResult> Run(
            [HttpTrigger(AuthorizationLevel.Function, "get", "post", Route = null)] HttpRequest req,
            ILogger log)
        {
            log.LogInformation("C# 'download_powerbi_queries' function processed a request.");

            string workspace_name = req.Query["workspace_name"];

            var responseMessage = string.Empty;
            if (string.IsNullOrEmpty(workspace_name))
            {
                responseMessage = "URI query parameter missing 'workspace_name'.";
                return new BadRequestObjectResult(responseMessage);
            }
            
            string ConnectionString = $"Provider=MSOLAP;Data Source=powerbi://api.powerbi.com/v1.0/myorg/{workspace_name};User ID=myemail@host.com;Password=***"; 

            var databases = new List<(string Name, List<(string Name, string Expression)> Expressions, List<(string TableName, string SourceType, string PartitionExpression)> TableExpressions)>();
            using (var server = new Server())
            {
                try
                {
                    server.Connect(ConnectionString);
                }
                catch (Exception e)
                {
                    log.LogDebug($"Unable to connect to Power BI with parameters provided: {e}");
                    return new BadRequestObjectResult("Unable to connect to Power BI with parameters provided");
                }

                foreach (Database database in server.Databases)
                {
                    log.LogDebug($"Processing dataset: {database.Name}");
                    var model = database.Model;

                    var expressions = new List<(string Name, string Expression)>();
                    foreach (var expression in model.Expressions)
                    {
                        expressions.Add(
                            (expression.Name, expression.Expression)
                        );
                    }

                    var tableExpressions = new List<(string TableName, string SourceType, string PartitionExpression)>();
                    foreach (Table table in model.Tables)
                    {
                        var type = table.Partitions[0].Source.GetType();
                        if (type == typeof(MPartitionSource))
                        {
                            var source = (MPartitionSource)table.Partitions[0].Source;
                            tableExpressions.Add(
                                (table.Name, "MPartitionSource", source.Expression)
                            );
                        }
                        else if (type == typeof(CalculatedPartitionSource))
                        {
                            var source = (CalculatedPartitionSource)table.Partitions[0].Source;
                            tableExpressions.Add(
                                (table.Name, "CalculatedPartitionSource", source.Expression)
                            );
                        }
                        else if (type == typeof(QueryPartitionSource))
                        {
                            var source = (QueryPartitionSource)table.Partitions[0].Source;
                            tableExpressions.Add(
                                (table.Name, "QueryPartitionSource", source.Query)
                            );
                        }
                        else
                        {
                            log.LogDebug($"Table Expression: {table.Name}: non-supported Source type '{type}'.");
                        }
                    }

                    databases.Add(
                        (
                            Name: database.Name,
                            Expressions: expressions,
                            TableExpressions: tableExpressions
                        )
                    );
                }
            }

            // ZIP Files
            try
            {
                using (var memoryStream = new MemoryStream())
                {
                    using (var archive = new ZipArchive(memoryStream, ZipArchiveMode.Create, true))
                    {
                        foreach (var database in databases)
                        {
                            foreach (var expression in database.Expressions)
                            {
                                byte[] bytes = Encoding.UTF8.GetBytes(expression.Expression);

                                var zipEntry = archive.CreateEntry($"{database.Name}/expressions/{expression.Name}.m",
                                    CompressionLevel.Fastest);
                                using (var zipStream = zipEntry.Open())
                                {
                                    zipStream.Write(bytes, 0, bytes.Length);
                                }
                            }

                            foreach (var tableExpression in database.TableExpressions)
                            {
                                byte[] bytes = Encoding.UTF8.GetBytes(tableExpression.PartitionExpression);

                                var zipEntry = archive.CreateEntry($"{database.Name}/tableExpressions/{tableExpression.SourceType}_{tableExpression.TableName}.txt",
                                    CompressionLevel.Fastest);
                                using (var zipStream = zipEntry.Open())
                                {
                                    zipStream.Write(bytes, 0, bytes.Length);
                                }
                            }
                        }
                    }

                    return new FileContentResult(memoryStream.ToArray(), "application/zip")
                    {
                        FileDownloadName = $"{workspace_name}.zip"
                    };
                }
            }
            catch (Exception e)
            {
                responseMessage = "Something went wrong while processing the Power BI data.";
                log.LogError(responseMessage, e);
                return new ObjectResult("Something went wrong while processing the Power BI data.") { StatusCode = 500 };
            }
        }
    }
}