Using C# dynamic Expando Objects to query Microsoft Analysis Services using Standard (non OLAP) Blazor Components

Occasionally when working with data in Microsoft Analysis Services, I am reminded of the old adage: “if the only tool you have is a hammer, everything starts to look like a nail”. Many third party components depend on strongly typed objects and the pervasivness of these components together with the power and speed of Analysis Services is a powerful combination.  However sometimes it feels like too much effort to get the data in the right format.  This post outlines one approach for making it simpler to get data out of Analysis Services into C# dynamic objects compatible with mainstream Blazor components.

Any developer used to developing data driven applications against a relational database will be used to using an Object Relational Mapping Layer (O/RM) such as Entity Framework Core.  Entity Framework Core allows .Net developers to work with a database using .Net objects and minimises the amount of data access code that needs to be written, and data can be accessed, joined and loaded into .Net Objects using the powerful Language Integrated Query (Linq).

Currently there is no Object Mapping Layer for Analysis Services OLAP or Tabular and this can make it difficult to render data in anything other than a Pivot Table/Chart component or BI Tool such as Power BI, Tableau etc.  After a bit of online research to try to find the best approach, I turned up a thought provoking article by Chris Webb that talked about an approach for using Linq to create MDX Sets and then using these sets to query the cube or tabular model. However, what I am trying to achieve is simply to get some data into the application that can be consumed wither ny my own components, or third party components, rather than the more ambitious task of writing Linq code to construct MDX

Many third party components will quite happily render C# dynamic objects such as Expando, and I thought that this would the best way to go as trying to think of a class structure with all the possible combinations of levels that are possible in a multi-dimensional database made my head hurt, and in the end it would really amount to building a mulit-dimensional version of an O/RM by degrees.  

After some experimentation, I decided that if the MDX were written in a standard format where the column names are renamed (in the MDX)  to whatever I want to call them in my application, then that would be half the battle.  There are many tools for building MDX, but I used MDX Studio by sqlbi.  A sample statement in the format I used is included below.

    				
    					WITH 
      MEMBER Measures.ProductCategoryId AS 
        Product.Category.CurrentMember.Properties("Key") 
      MEMBER Measures.ProductCategoryName AS 
        Product.Category.CurrentMember.Member_Caption 
      MEMBER Measures.SalesChannelId AS 
        [Sales Channel].CurrentMember.Properties("Key") 
      MEMBER Measures.SalesChannelName AS 
        [Sales Channel].CurrentMember.Member_Caption 
      MEMBER Measures.SalesAmount AS 
        [Measures].[Sales Amount] 
    SELECT 
      {
        Measures.ProductCategoryName
       ,Measures.SalesChannelName
       ,Measures.ProductCategoryId
       ,Measures.SalesChannelId
       ,Measures.SalesAmount
      } ON 0
     ,
      [Product].[Category].Children * [Sales Channel].Children ON 1
    FROM [Adventure Works];
    				
    			

    I’m pretty sure that any MDX statement could be rewritten to confirm to this structure, however I have not proved that this is the case.  This statement renders the following output, with the columns now having the sort of name that you would expect to use in C#.

    ProductCategoryNameSalesChannelNameProductCategoryIdSalesChannelIdSalesAmount
    AccessoriesInternetAccessoriesInternet4Internet$700,759.96
    AccessoriesResellerAccessoriesReseller4Reseller$571,297.93
    BikesInternetBikesInternet1Internet$28,318,144.65
    BikesResellerBikesReseller1Reseller$66,302,381.56
    ClothingInternetClothingInternet3Internet$339,772.61
    ClothingResellerClothingReseller3Reseller$1,777,840.84
    ComponentsInternetComponentsInternet2Internet
    ComponentsResellerComponentsReseller2Reseller$11,799,076.66

    The logic to transform the data into an List of Expando objects is implemented in the two private methods of my data access layer (see below).  The public interface just contains parameterized methods to fetch the data (which is the way I usually write this sort of thing anyway). 

    For this post I have not worried about building the cube or any of the security considerations involved in connecting to a cube using the datapump.  I may address these considerations in a future post.  For now I have used the publically available cube on the Syncfusion website.  However, as I have also used their components in the demo, I hope that they will not mind.

    				
    					using System.Dynamic;
    using Microsoft.AnalysisServices.AdomdClient;
    using Tuple = Microsoft.AnalysisServices.AdomdClient.Tuple;
    
    namespace DataAccessLayer.Data;
    public interface IDataAccessLayer
    {
        public List<ExpandoObject> GetProductCategorySales(int? fiscalYear);
        public List<ExpandoObject> GetFiscalYears();
    }
    public class DataAccessLayer : IDataAccessLayer
    {
        private static void AddProperty(ExpandoObject expando, string propertyName, object propertyValue)
        {
            // ExpandoObject supports IDictionary
            var expandoDict = expando as IDictionary<string, object>;
            if (expandoDict.ContainsKey(propertyName))
                expandoDict[propertyName] = propertyValue;
            else
                expandoDict.Add(propertyName, propertyValue);
        }
        private List<ExpandoObject> GetExpandoList(string mdx)
        {
            List<ExpandoObject> resultList = new();
    
            System.Text.StringBuilder result = new System.Text.StringBuilder();
    
            try
            {
                using (AdomdConnection connection = new AdomdConnection())
                {
                    connection.ConnectionString = "Data Source=https://bi.syncfusion.com/olap/msmdpump.dll;Catalog=Adventure Works DW 2008 SE;";
                    connection.Open();
                    using ( AdomdCommand command = new AdomdCommand(mdx, connection))
                    {
                        CellSet cs = command.ExecuteCellSet();
    
                        TupleCollection tuplesOnRows = cs.Axes[1].Set.Tuples;
                        TupleCollection tuplesOnColumns = cs.Axes[0].Set.Tuples;
                        for (int row = 0; row < tuplesOnRows.Count; row++)
                        {
    
                            dynamic d = new ExpandoObject();
                            for (int col = 0; col < tuplesOnColumns.Count; col++)
                            {
                                AddProperty(d, tuplesOnColumns[col].Members[0].Caption.ToString(), cs.Cells[col, row].Value);
                            }
    
                            resultList.Add(d);
                        }
                        connection.Close();
                    }
                }
                return resultList;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
                return new List<ExpandoObject>();
            }
        }
        public List<ExpandoObject> GetProductCategorySales(int? fiscalYear)
        {
            var mdx = @"
                WITH 
                    MEMBER Measures.ProductCategoryId AS 
                        Product.Category.CurrentMember.Properties(""Key"") 
                    MEMBER Measures.ProductCategoryName AS
                        Product.Category.CurrentMember.Member_Caption
                    MEMBER Measures.SalesChannelId AS 
                        [Sales Channel].CurrentMember.Properties(""Key"") 
                    MEMBER Measures.SalesChannelName AS
                        [Sales Channel].CurrentMember.Member_Caption
                    MEMBER Measures.SalesAmount AS
                        [Measures].[Sales Amount]
                    SELECT
                    {
                    Measures.ProductCategoryName
                    ,Measures.SalesChannelName
                    ,Measures.ProductCategoryId
                    ,Measures.SalesChannelId
                    ,Measures.SalesAmount
                    }
                    ON 0
                    ,
                    [Product].[Category].CHILDREN * [Sales Channel].CHILDREN ON 1
                FROM[Adventure Works] 
            ";
    
            if (fiscalYear is not null) 
                mdx += $"WHERE  [Date].[Fiscal Year].&[{fiscalYear.ToString()}] ";
    
            return GetExpandoList(mdx);
    
        }
        public List<ExpandoObject> GetFiscalYears()
        {
            var mdx = @"
                WITH 
                    MEMBER Measures.CalendarYearId AS 
                        [Date].[Fiscal Year].CurrentMember.Properties(""Key"") 
                    MEMBER Measures.CalendarYearName AS
                        [Date].[Fiscal Year].CurrentMember.Member_Caption
                SELECT
                    {
                      Measures.CalendarYearId
                     ,Measures.CalendarYearName
                    }
                    ON 0
                    ,
                    [Date].[Fiscal Year].CHILDREN ON 1
                FROM[Adventure Works];
            ";
    
            return GetExpandoList(mdx);
        }
    
    
    
    
    }
    				
    			

    Here is the code to render the data in a standard table (i.e. non-OLAP). I chose the Syncfusion DataGrid component for this post as I used their analysis services database as the multi-dimensional data source. 

    I have also included a dropdown to allow the user to change the Year. The data for this dropdown is also loaded from Analysis Services.  The OnChange event for the Dropdown submits a new parameterized MDX query to Analysis Services which refreshes the data.

    				
    					@page "/datagrid-features"
    @using System.Dynamic
    @using Syncfusion.Blazor.Grids
    @using Syncfusion.Blazor.DropDowns
    @using DataAccessLayer.Data
    @using Localization.Data
    @using System.Diagnostics
    @inject IDataAccessLayer dal
    <div class="col-lg-12 control-section">
        <div class="content-wrapper">
            <div class="row">
                <SfDropDownList TItem="ExpandoObject" TValue="int?" PopupHeight="230px"  Placeholder=@AppResources.PlaceholderSelectYear DataSource="@FiscalYears" @bind-Value="@FiscalYearId">
                    <DropDownListEvents TItem="ExpandoObject" TValue="int?" ValueChange="OnChange"/>
                    <DropDownListFieldSettings Text="CalendarYearName" Value="CalendarYearId"/>
                </SfDropDownList>
             </div>  
        </div>
        <div class="col-lg-12 control-section">
            <div class="content-wrapper">
                <SfGrid DataSource="@ProductCategorySales" AllowPaging="true" AllowSorting="true" Toolbar="@(new List<string>() { "Search" })">
                    <GridEditSettings AllowAdding="true" AllowEditing="true" AllowDeleting="true" Mode="EditMode.Normal"></GridEditSettings>
                    <GridColumns>
                        <GridColumn Field="ProductCategoryName" HeaderText=@AppResources.DimensionLevelProductCategory Width="120" ></GridColumn>
                        <GridColumn Field="SalesChannelName" HeaderText=@AppResources.DimensionLevelSalesChannel Width="120" ></GridColumn>
                        <GridColumn Field="SalesAmount" HeaderText=@AppResources.MeasureSalesAmount Format="C2" Width="120" TextAlign="TextAlign.Right"></GridColumn>
                    </GridColumns>
                </SfGrid>
             </div>  
        </div>
    </div>
    
    <style>
        .row {
            width: 250px;
            padding: 14px;
        }
        .control-wrapper {
            width: 250px;
            margin: 0 auto;
            padding-top: 70px;
        }
        .property-section .property-value {
            padding-left: 10px;
        }
        .property-section .property-label {
            padding: 5px 0px 5px;
            width: 40%
        }
        #property {
            width: 100%;
        }
    </style>
    
    @code{
        public List<ExpandoObject> ProductCategorySales { get; set; } = new List<ExpandoObject>();
        public List<ExpandoObject> FiscalYears { get; set; } = new List<ExpandoObject>();
        public int? FiscalYearId = null;
        protected override void OnInitialized()
        {
            ProductCategorySales = dal.GetProductCategorySales(FiscalYearId);
            FiscalYears = dal.GetFiscalYears();
    
        }
        public void OnChange(Syncfusion.Blazor.DropDowns.ChangeEventArgs<int?, ExpandoObject> args)
        {
            this.FiscalYearId = args.Value;
            ProductCategorySales = dal.GetProductCategorySales(FiscalYearId);
        }
    }
    				
    			

    Finally I thought it would be useful to discuss how to manipulate data stored in an Expando object using Linq, so I wrote another page that presents a bar chart of the data aggregated to Product Category Level. 

    This Linq query is included below. It is a little more complicated than I would like, because the Expando object must be queried as a dictionary, but is still quite managable.

    				
    					        ProductCategorySales = ProductCategoryChannelSales
                .Select(g => new
                {
                    ProductCategoryName = g.Where(x => x.Key == "ProductCategoryName").Select(x => x.Value).Single(),
                    SalesAmount = g.Where(x => x.Key == "SalesAmount").Select(x => x.Value).Single()
                })
                .GroupBy(x => new { ProductCategoryName = x.ProductCategoryName })
                .Select(g => new {ProductCategoryName = g.Key.ProductCategoryName, SalesAmount = g.Sum(x=> (double) (x.SalesAmount ?? 0.0))}).ToList<dynamic>();
    				
    			

    The data from the original query are aggregated to Product Category level and then reported in the following (non-OLAP) Syncfusion Bar Chart.

    				
    					@page "/charts-features"
    @using Syncfusion.Blazor.Charts
    @using System.Dynamic
    @using DataAccessLayer.Data
    @using System.Diagnostics
    @using Localization.Data
    @inject IDataAccessLayer dal
    <h2>Charts</h2>
    <br/>
    <div id = "ControlRegion">
     <SfChart Theme="Theme.Material" Title=@AppResources.ChartProductCategorySales >
            <ChartPrimaryXAxis ValueType="Syncfusion.Blazor.Charts.ValueType.Category"></ChartPrimaryXAxis>            
            <ChartPrimaryYAxis LabelFormat="{value}K"></ChartPrimaryYAxis>
            <ChartSeriesCollection>
                <ChartSeries DataSource="@ProductCategorySales" XName="ProductCategoryName" YName="SalesAmount" Name=@AppResources.MeasureSalesAmount Type="ChartSeriesType.Bar" />
            </ChartSeriesCollection>
            <ChartTooltipSettings Enable="true"></ChartTooltipSettings>
            <ChartZoomSettings EnableSelectionZooming="true"></ChartZoomSettings>
        </SfChart>
    </div>
    <br/>
    @code{
        public List<dynamic> ProductCategorySales { get; set; } = new List<dynamic>();
        public List<ExpandoObject> FiscalYears { get; set; } = new List<ExpandoObject>();
        IEnumerable<ExpandoObject>? ProductCategoryChannelSales;
        public int? FiscalYearId = null;
        protected override void OnInitialized()
        {
            ProductCategoryChannelSales = dal.GetProductCategorySales(FiscalYearId);
    
    
            ProductCategorySales = ProductCategoryChannelSales
                .Select(g => new
                {
                    ProductCategoryName = g.Where(x => x.Key == "ProductCategoryName").Select(x => x.Value).Single(),
                    SalesAmount = g.Where(x => x.Key == "SalesAmount").Select(x => x.Value).Single()
                })
                .GroupBy(x => new { ProductCategoryName = x.ProductCategoryName })
                .Select(g => new {ProductCategoryName = g.Key.ProductCategoryName, SalesAmount = g.Sum(x=> (double) (x.SalesAmount ?? 0.0))}).ToList<dynamic>();
    
            
    
        }
        public void OnChange(Syncfusion.Blazor.DropDowns.ChangeEventArgs<int?, ExpandoObject> args)
        {
            this.FiscalYearId = args.Value;
            ProductCategoryChannelSales = dal.GetProductCategorySales(FiscalYearId);
        }
    }
    
    				
    			

    I hope that you find this post useful.  I’d love to hear your comments.  The code is available to download here.

    Comments

    0 Comments

    Posts

    Analysis Services

    Using C# dynamic Expando Objects to query Microsoft Analysis Services using Standard (non OLAP) Blazor Components

    Currently there is no Object Mapping Layer for Analysis Services OLAP or Tabular and this can make it difficult to render data in anything other than a Pivot Table or BI Tool such as Power BI, Tableau etc. This post discusses a simple method for getting Analysis Data into C# dynamic objects and using standard Blazor components to render in the UI.

    Do You Want To Boost Your Business?

    drop us a line and keep in touch