MDX query builder implementation

Question

I'm using Microsoft.AnalysisServices.AdomdClient to query a cube. I have a Multidimensional Expressions (MDX) query that I pass to the AdomdCommand object to query my cube. I would like to have a MDX builder class that I could use to pass a list of Measures, Dimensions, Levels, etc and generate a string of the corresponding MDX query.

Does this already exist?

Answer 1

To generate MDX query on a template can be used Ranet Olap. Below is a simple example code. It shows typical use cases, including filter settings and using multiple measures.

using System.Collections.Generic;
using System.Windows.Forms;
using Ranet.Olap.Core.Common;
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;

            //columns
            var itemCol1 = new Hierarchy_AreaItemWrapper
            {
                AreaItemType = AreaItemWrapperType.Hierarchy_AreaItemWrapper,
                UniqueName = "[Date].[Calendar]"
            };
            mdx.AreaWrappersColumns.Add(itemCol1);

            //rows
            var itemRow1 = new Hierarchy_AreaItemWrapper
            {
                AreaItemType = AreaItemWrapperType.Hierarchy_AreaItemWrapper,
                UniqueName = "[Customer].[Customer Geography]"
            };
            mdx.AreaWrappersRows.Add(itemRow1);

            //data
            var itemData1 = new Measure_AreaItemWrapper
            {
                AreaItemType = AreaItemWrapperType.Measure_AreaItemWrapper,
                UniqueName = "[Measures].[Internet Order Count]"
            };
            mdx.AreaWrappersData.Add(itemData1);
            var itemData2 = new Measure_AreaItemWrapper
            {
                AreaItemType = AreaItemWrapperType.Measure_AreaItemWrapper,
                UniqueName = "[Measures].[Internet Sales Amount]"
            };
            mdx.AreaWrappersData.Add(itemData2);
            //if Measures in Data area > 1 - add special element VALUES in COLUMNS or ROWS areas
            var itemCol2 = new Values_AreaItemWrapper
            {
                AreaItemType = AreaItemWrapperType.NamedSet_AreaItemWrapper,
            };
            mdx.AreaWrappersColumns.Add(itemCol2);

            //filter
            var itemFilter1 = new Level_AreaItemWrapper
            {
                AreaItemType = AreaItemWrapperType.Level_AreaItemWrapper,
                UniqueName = "[Date].[Calendar Year].[Calendar Year]",
                HierarchyUniqueName = "[Date].[Calendar Year]",
                CompositeFilter = new Composite_FilterWrapper()
            };
            itemFilter1.CompositeFilter.IsUsed = true;
            itemFilter1.CompositeFilter.MembersFilter.IsUsed = true;


            // two options to choose
            // option 1 - simply SET
            //itemFilter1.CompositeFilter.MembersFilter.FilterSet = "{[Date].[Calendar Year].&[2007], [Date].[Calendar Year].&[2008]}";
            #region
            // option 2 - collection Members

            var mcf1 = new MemberChoiceSettings
            {
                SelectState = SelectStates.Selected_Self,
                Info =
                    new MemberData
                    {
                        UniqueName = "[Date].[Calendar Year].&[2007]",
                        HierarchyUniqueName = "[Date].[Calendar Year]",
                        Caption = "2007"
                    }
            };
            itemFilter1.CompositeFilter.MembersFilter.SelectedInfo.Add(mcf1);
            var mcf2 = new MemberChoiceSettings
            {
                SelectState = SelectStates.Selected_Self,
                Info =
                    new MemberData
                    {
                        UniqueName = "[Date].[Calendar Year].&[2008]",
                        HierarchyUniqueName = "[Date].[Calendar Year]",
                        Caption = "2008"
                    }
            };
            itemFilter1.CompositeFilter.MembersFilter.SelectedInfo.Add(mcf2);
            // build SET on collection
            itemFilter1.CompositeFilter.MembersFilter.BuildFilterSet();

            #endregion
            mdx.AreaWrappersFilter.Add(itemFilter1);

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

        }
    }
}

As a result will be generated such a MDX query

SELECT 
HIERARCHIZE(CrossJoin(HIERARCHIZE([Date].[Calendar].Levels(0).Members), {[Measures].[Internet Order Count], [Measures].[Internet Sales Amount]})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME, HIERARCHY_UNIQUE_NAME, CUSTOM_ROLLUP, UNARY_OPERATOR, KEY0 ON 0, 
HIERARCHIZE(HIERARCHIZE([Customer].[Customer Geography].Levels(0).Members)) DIMENSION PROPERTIES PARENT_UNIQUE_NAME, HIERARCHY_UNIQUE_NAME, CUSTOM_ROLLUP, UNARY_OPERATOR, KEY0 ON 1 
FROM 
(SELECT ({{[Date].[Calendar Year].&[2007], [Date].[Calendar Year].&[2008]}}) on COLUMNS FROM [Adventure Works]) 
CELL PROPERTIES BACK_COLOR, CELL_ORDINAL, FORE_COLOR, FONT_NAME, FONT_SIZE, FONT_FLAGS, FORMAT_STRING, VALUE, FORMATTED_VALUE, UPDATEABLE, ACTION_TYPE

Answer 2

That's a good start for: http://www.c-sharpcorner.com/UploadFile/akrishnaraj/MdxQueryBuilder12052005041819AM/MdxQueryBuilder.aspx

If open source code is an alternative for you, then this library contains a MDX generator included: http://code.google.com/p/ranet-uilibrary-olap/