How to generate mdx query using C#?

Question

I am new to mdx query and I am very curious about mdx query generation using C# so I searched for any demo or open source then I found Ranet.olap (https://ranetuilibraryolap.codeplex.com/) which is providing what I need. After taking the dlls I tried to incorporate them in my code. I am pasting my full console code which should generate mdx query but it's not doing so, am I doing something wrong?

using System;
using System.Collections.Generic;
using Microsoft.AnalysisServices.AdomdClient;
using Ranet.Olap.Core.Managers;
using Ranet.Olap.Core.Metadata;
using Ranet.Olap.Core.Types;

namespace MDX
{
    class Program
    {
        static void Main(string[] args)
        {
            startWork();
        }

        public static void startWork()
        {
            string connString = "Provider=MSOLAP.3; Data Source=localhost;Initial Catalog=AdventureWorkDW2008R2;Integrated Security=SSPI;";
            CubeDef cubes;
            AdomdConnection conn = new AdomdConnection(connString);
            conn.Open();
            cubes = conn.Cubes.Find("AdventureWorkCube");

            Ranet.Olap.Core.Managers.MdxQueryBuilder mdx = new Ranet.Olap.Core.Managers.MdxQueryBuilder();
            mdx.Cube = cubes.Caption;
            List<Ranet.Olap.Core.Wrappers.AreaItemWrapper> listColumn = new List<Ranet.Olap.Core.Wrappers.AreaItemWrapper>();
            List<Ranet.Olap.Core.Wrappers.AreaItemWrapper> listRow = new List<Ranet.Olap.Core.Wrappers.AreaItemWrapper>();
            List<Ranet.Olap.Core.Wrappers.AreaItemWrapper> listData = new List<Ranet.Olap.Core.Wrappers.AreaItemWrapper>();

            //Column area
            Dimension dmColumn = cubes.Dimensions.Find("Dim Product");
            Microsoft.AnalysisServices.AdomdClient.Hierarchy hColumn = dmColumn.Hierarchies["English Product Name"];
            //hierarchy properties
            List<PropertyInfo> lPropInfo = new List<PropertyInfo>();
            foreach (var prop in hColumn.Properties)
            {
                PropertyInfo p = new PropertyInfo();
                p.Name = prop.Name;
                p.Value = prop.Value;
                lPropInfo.Add(p);
            }
            Ranet.Olap.Core.Wrappers.AreaItemWrapper areaIColumn = new Ranet.Olap.Core.Wrappers.AreaItemWrapper();
            areaIColumn.AreaItemType = AreaItemWrapperType.Hierarchy_AreaItemWrapper;
            areaIColumn.Caption = hColumn.Caption;
            areaIColumn.CustomProperties = lPropInfo;
            listColumn.Add(areaIColumn);

            //Rows Area
            Dimension dmRow = cubes.Dimensions.Find("Due Date");
            Microsoft.AnalysisServices.AdomdClient.Hierarchy hRow = dmRow.Hierarchies["English Month Name"];
            List<PropertyInfo> lRowPropInfo = new List<PropertyInfo>();
            foreach (var prop in hRow.Properties)
            {
                PropertyInfo p = new PropertyInfo(prop.Name,prop.Value);
                lRowPropInfo.Add(p);
            }
            Ranet.Olap.Core.Wrappers.AreaItemWrapper areaIRow = new Ranet.Olap.Core.Wrappers.AreaItemWrapper();
            areaIRow.AreaItemType = AreaItemWrapperType.Hierarchy_AreaItemWrapper;
            areaIRow.Caption = hRow.Caption;
            areaIRow.CustomProperties = lRowPropInfo;
            listRow.Add(areaIRow);


            //Measure Area or Data Area
            Measure ms = cubes.Measures.Find("Order Quantity");
            Ranet.Olap.Core.Wrappers.AreaItemWrapper areaIData = new Ranet.Olap.Core.Wrappers.AreaItemWrapper();
            areaIData.AreaItemType = AreaItemWrapperType.Measure_AreaItemWrapper;
            areaIData.Caption = ms.Caption;

            List<PropertyInfo> lmpropInfo = new List<PropertyInfo>();

            foreach (var prop in ms.Properties)
            {
                PropertyInfo p = new PropertyInfo(prop.Name, prop.Value);
                lmpropInfo.Add(p);
            }
            areaIData.CustomProperties = lmpropInfo;
            listData.Add(areaIData);

            mdx.AreaWrappersColumns = listColumn;
            mdx.AreaWrappersRows = listRow;
            mdx.AreaWrappersData = listData;
            string mdxQuery = mdx.GenerateMdxQuery();
            conn.Close();
        }
    }
}

Answer 1

A simple example of the generation mdx query (only Ranet OLAP 3.7 version):

using System.Collections.Generic;
using Ranet.Olap.Core.Data;
using Ranet.Olap.Core.Managers;
using Ranet.Olap.Core.Types;
using Ranet.Olap.Core.Wrappers;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            startWork();
        }

        public static void startWork()
        {

            var mdx = new QueryBuilderParameters
            {
                CubeName = "[Adventure Works]",
                SubCube = "",
                MdxDesignerSetting = new MDXDesignerSettingWrapper(),
                CalculatedMembers = new List<CalcMemberInfo>(),
                CalculatedNamedSets = new List<CalculatedNamedSetInfo>(),
                AreaWrappersFilter = new List<AreaItemWrapper>(),
                AreaWrappersColumns = new List<AreaItemWrapper>(),
                AreaWrappersRows = new List<AreaItemWrapper>(),
                AreaWrappersData = new List<AreaItemWrapper>()
            };

            //define parameters
            mdx.MdxDesignerSetting.HideEmptyColumns = false;
            mdx.MdxDesignerSetting.HideEmptyRows = false;
            mdx.MdxDesignerSetting.UseVisualTotals = false;
            mdx.MdxDesignerSetting.SubsetCount = 0;

            var itemCol1 = new Hierarchy_AreaItemWrapper
            {
                AreaItemType = AreaItemWrapperType.Hierarchy_AreaItemWrapper,
                UniqueName = "[Customer].[Customer Geography]"
            };
            mdx.AreaWrappersColumns.Add(itemCol1);

            var itemRow1 = new Hierarchy_AreaItemWrapper
            {
                AreaItemType = AreaItemWrapperType.Hierarchy_AreaItemWrapper,
                UniqueName = "[Date].[Calendar]"
            };
            mdx.AreaWrappersRows.Add(itemRow1);

            var itemData1 = new Measure_AreaItemWrapper();
            itemData1.AreaItemType = AreaItemWrapperType.Measure_AreaItemWrapper;
            itemData1.UniqueName = "[Measures].[Internet Order Count]";
            mdx.AreaWrappersData.Add(itemData1);

            string query = MdxQueryBuilder.Default.BuildQuery(mdx, null);

        }
    }
}

MDX Query result:

SELECT 
HIERARCHIZE(HIERARCHIZE([Customer].[Customer Geography].Levels(0).Members)) DIMENSION PROPERTIES PARENT_UNIQUE_NAME, HIERARCHY_UNIQUE_NAME, CUSTOM_ROLLUP, UNARY_OPERATOR, KEY0 ON 0, 
HIERARCHIZE(HIERARCHIZE([Date].[Calendar].Levels(0).Members)) DIMENSION PROPERTIES PARENT_UNIQUE_NAME, HIERARCHY_UNIQUE_NAME, CUSTOM_ROLLUP, UNARY_OPERATOR, KEY0 ON 1 
FROM 
[Adventure Works] 
WHERE ([Measures].[Internet Order Count]) 
CELL PROPERTIES BACK_COLOR, CELL_ORDINAL, FORE_COLOR, FONT_NAME, FONT_SIZE, FONT_FLAGS, FORMAT_STRING, VALUE, FORMATTED_VALUE, UPDATEABLE, ACTION_TYPE

Answer 2

Still in process of revising code for this engine, though some suggestions for you:

• It looks like you just grab cube metadata (dims, measures etc.) and pass it to generator. This does not sound like a way to generate MDX. MDX statement should look like

  select 
  { 
    // measures, calculated members
  } on 0,
  {
    // dimension data - sets
  } on 1 // probably more axis
  from **Cube**
  

All other parameters are optional