Click here to Skip to main content

Submit your article

MDX

MDX

Great Reads

Serializing CellSet Adodm MDX Query Result Set to JSON

by Fedor Dzjuba

Sometimes, we need to present MDX result on the web or a client application that has no native provider to Microsoft Analysis Service. Solution is to use Adodm.Client CellSet, serialize it into sensible JSON and spit it out to the client to render.

Analysis Service MDX Query Viewer web client

by Fedor Dzjuba

Client and a WebAPI to execute MDX query against SSAS and display a resulted cellset in a pivoted grid form with Axis neatly arranged in a hierarchical and grouped manner.

Combining MDX with T-SQL in One Result Set for SSRS (Hybrid Query)

by Ahmad A.A. Ahmad

Combining MDX with T-SQL in One Result Set for SSRS (Hybrid Query)

Introduction to Percolator Analysis Services LINQ to MDX ORM

by Suffyan Asad

Percolator Analysis Services is a LINQ to MDX ORM that enables writing MDX Queries in LINQ instead of using hard-coded strings of MDX code, resulting in cleaner, easier to read and easier to maintain code.

Latest Articles

Analysis Service MDX Query Viewer web client

by Fedor Dzjuba

Client and a WebAPI to execute MDX query against SSAS and display a resulted cellset in a pivoted grid form with Axis neatly arranged in a hierarchical and grouped manner.

Serializing CellSet Adodm MDX Query Result Set to JSON

by Fedor Dzjuba

Sometimes, we need to present MDX result on the web or a client application that has no native provider to Microsoft Analysis Service. Solution is to use Adodm.Client CellSet, serialize it into sensible JSON and spit it out to the client to render.

Wild Card in MDX Queries

by Harikrishnan-BI

This tip discusses about the MDX-Wild Card expressions (like 'Like Operator in SQL) with examples.

Introduction to Percolator Analysis Services LINQ to MDX ORM

by Suffyan Asad

Percolator Analysis Services is a LINQ to MDX ORM that enables writing MDX Queries in LINQ instead of using hard-coded strings of MDX code, resulting in cleaner, easier to read and easier to maintain code.

All Articles

MDX

Serializing CellSet Adodm MDX Query Result Set to JSON

1 Dec 2019 by Fedor Dzjuba

Sometimes, we need to present MDX result on the web or a client application that has no native provider to Microsoft Analysis Service. Solution is to use Adodm.Client CellSet, serialize it into sensible JSON and spit it out to the client to render.

multi-dimensional

Analysis Service MDX Query Viewer web client

7 Jan 2020 by Fedor Dzjuba

Client and a WebAPI to execute MDX query against SSAS and display a resulted cellset in a pivoted grid form with Axis neatly arranged in a hierarchical and grouped manner.

How to retrive data from cube using C#.net

20 Sep 2014 by kingsa

Hi,i want to retrive data from cube,in my cube have lakhs of data but i want to retrive 10 rows only , for 10 rows i have unique id , how to pass the unique ids and retrive the particular 10 persons data onlylike how sql server using in operator, same as in cube can u guide me or send snippets

How Can I Retrieve The Limited Records Using Row_Number Function In Mdx Query

24 Sep 2014 by kingsa

Hi,I written MDX query for getting row Number. I got the row_number but i want to retrieve the data between 10 to 15 Records using row_ number column. can u guide me or send snippetsWITH MEMBER [Measures].[Row number] AS iif( IsError(Axis(0).Count), null, Rank ( ...

how to retrive limited number of records from mdx query

25 Sep 2014 by kingsa

Hi all,i want retrive data from cubes using mdx query , the data need to shown in grid , but i want limited no of records , wen i do paging i need to records which i want to shown those records i need to fectch, canu guide or send snippets

SQL-Server-2008R2

how to retrive limited number of records from mdx query

25 Sep 2014 by _Asif_

Check this forum for solutionMDX Query TOP n rows[^]

SQL-Server-2008R2

Combining MDX with T-SQL in One Result Set for SSRS (Hybrid Query)

27 Sep 2014 by Ahmad A.A. Ahmad

Combining MDX with T-SQL in One Result Set for SSRS (Hybrid Query)

How to retrive data from cube using C#.net

28 Sep 2014 by Sibeesh KV

We want to see those employees who are working in Hyderabad. Remember emp and dept are joined on deptno and city column is in the dept table. Assuming that wherever the department is located the employee is working in that city.Select * from emp where deptno in (select deptno from dept where...

How Can I Retrieve The Limited Records Using Row_Number Function In Mdx Query

29 Oct 2014 by Maciej Los

MDX queries differs from SQL queries in many aspects...MSDN wrote:ImportantUnlike the WHERE clause in SQL, the WHERE clause of an MDX SELECT statement never directly filters what is returned on the Rows axis of a query. To filter what appears on the Rows or Columns axis of a query, you...

How to get text data in cell using MDX query

26 Mar 2015 by Satish Kumar Trivedi

Can we get text data in cell using MDX query? Like I mentioned required table result as below:- I need to have Columns heading's as FName, LName, Addressand Row as Employer's name. And data should be details of the employee. ...

Match OR between two MDX querry

22 Jul 2015 by Member 11603655

Hi , I have 2 MDX Querry : ==> SELECT NON EMPTY { [Measures].[Nb FULL Pal PFS] } ON COLUMNS FROM ( SELECT ( [Heure].[H_Heure].[Heure].&[23] : [Heure].[H_Heure].[Heure].&[24] ) ON COLUMNS FROM ( SELECT ( { [Temps].[Annee_Mois_Jour].[Jour].&[20150623] } ) ON COLUMNS FROM...

How to get the Max value from an intermidiate table by using DAX

14 Aug 2015 by RishenWu

Hi,I have below tables in a tabular data base, and I want to get the max favorite price range for each member.DimMember(MemberKey int,MemberName varchar(50)DimPriceRange(PriceRangeKey,PriceRangeValue varchanr(50))FactOrders(FactOrderKey int,MemberKey int,PriceRangeKey)The DAX...

SQL-Server-2012

Not able to remove duplicate values from the result set in MDX Query

11 Sep 2015 by Member 11977352

I am very new to MDX and just started learning basics. In my project we are using MDX cube on OLAP.We are facing some issue with duplicate records being fetched from the query. I tried to put distinct on rows but didn't work out.Here is the original query.with member...

Show Dimension Names In MDX Query Result

27 Oct 2015 by Sibeesh Passion

Hi,Hope you are fine. I am facing an issue in MDX Query. I have a custom MDX query, When I run the query I am getting results too. Now my requirement is to show the dimension names in the query result, So that I can get those header names in the cell set itself. Please see the below...

Show Dimension Names In MDX Query Result

29 Oct 2015 by Kuthuparakkal

Please try like this: WITH MEMBER [Measures].[Fiscal Year] AS [Dim Data Period].[Fiscal Year].CURRENTMEMBER.MEMBER_CAPTION MEMBER [Measures].[Fiscal Quarter] AS [Dim Data Period].[Fiscal Quarter].CURRENTMEMBER.MEMBER_CAPTIONSELECT NONEMPTY( ...

How to join multiple dimensions for same measures in MDX

1 Nov 2015 by Member 12105776

Hi,So, I am developing a cube for the first time and here is the situation. Cube contains multiple dimensions and 1 measure group. There are 2 same Date dimensions - one for Booking date and one for the stay date. It is connected to the fact on Book_key and Stay_key.I am writing a...

Find data type of measures in Cube using MDX queries

30 Dec 2015 by Sibeesh Passion

I am working SSAS MDX queries. Now I need to identify the data type of each columns from the MDX queries. I have already tried as below.WITH MEMBER PageSize AS 1 Member [Measures].[My Type] as TypeName([Measures].[Budget Delivered COGS])But it returns double for the currency field....

How do I select limit the result set from an MDX Query in SQL Server

19 Jul 2016 by Daniel 11317535

I'm writing an MDX query for SQL server 2008 and are trying to look at the data from a crossjoin of 2 dimensions. There is probably too much data to display the result so I just want to return the top 10 results. How can i do this?Current Queryselect {[Measures].[Measure1],...

Sorting all the columns in MDX queries

3 Feb 2016 by Sibeesh Passion

Below is my MDX query. Here I have applied sorting for one column, how can I sort all the columns, the reason behind this is, I no column should have any values like (null), null, empty, unknown etc.WITH MEMBER PageSize AS 1MEMBER [PageNumber] AS 1MEMBER [Measures].[Orderby...

Sorting all the columns in MDX queries

3 Feb 2016 by Sibeesh Passion

I have done it by myself. I have changed the ROWAXISWOF as follows.SET ROWAXISWOF AS NonEmpty ( NonEmpty ( ( [Time Periods].[Fiscal Year].[Fiscal Year].Members, [Time Periods].[Fiscal Quarter].[Fiscal Quarter].Members, [Time Periods].[Fiscal Month].[Fiscal Month].Members,...

How do i get the latest row based on the last start date

15 Feb 2016 by Member 12328896

HiCan any one plese help me in retreiving the latest Startdate record with its corresponding record using calculated member in ssas. I have four dimension out of which two are date dimension and the last is department.EmployeeId StartDate EndDate Dept101 03/02/2013 01/01/2014...

How do i get the latest row based on the last start date

15 Feb 2016 by OriginalGriff

Just use a JOIN:SELECT a.EmployeeId, a.StartDate, a.EndDate, a.Dept FROM MyTable aJOIN (SELECT EmployeeID, MAX(StartDate) AS maxSD FROM MyTable GROUP BY EmployeeId) bON a.EmployeeId = b.EmployeeId AND a.StartDate = b.maxSD

Looking for MDX query solution

12 Apr 2016 by Member 12455250

I use an MDX Query to get a list of Databases from a SQL Analysis Service session, but I don't know how to write MDX queries.How would I do something like the following :- select * from $system.DBSCHEMA_CATALOGS where CATALOG_NAME like '%_15%'Note the first line works, but when I...

How do I select limit the result set from an MDX Query in SQL Server

19 Jul 2016 by Robert Griner

Use the TopCount()[^] function. Remember, the set must already be ordered using the Order (MDX)[^] function.select { [Measures].[Measure1], [Measures].[Measure1] , [Measures].[Measure1]} on columns,topcount( crossjoin( ...

Add column in existing MDX.

22 Aug 2016 by Member 10833473

hi,I need to add a column in existing MDX and its values should be 1,1,1,1, and 2,2,2 like below. Please refer below values which are under column 'measure code'.Top line income 1Products (GL) 1 Net Interest Income (NII) 1Non Interest Revenue (NIR) ...

How to show row header name in MDX quaries

19 Sep 2016 by Maciej Los

Start here: SELECT Statement (MDX)[^]As MSDN documentation states, you have to add a member to be able to produce "row-header".For further details, please check this thread: No row headers for MDX query...[^]

Mdx query to display data in monthwise

1 Feb 2017 by mastanrao

I am using mdx query to bind charts in month wise, if I am using in the following wayselect { [Measures].[MetricRatio],[Measures].[Average]} on 0,{ Filter( LastPeriods(3, [Time].[All Time].[2017].[Quarter 1].[January].[19].parent), true ) } on 1By using lastperiods with .Parent it...

How to pass particular string name in mdx parameter

3 Jan 2018 by BinnyVishwant

i have an input parameter value "Europe - DMS Finland". but i need to input only finland to the mdx query to fetch the required data result. What I have tried: i have tried this in where condition in mdx: where strtomember((right(@ManagementGroup,7))) but still the complete value is passing...

How to pass a parameter in mdx?

9 Jan 2018 by BinnyVishwant

I have a parameter value 'SF - LYON' while trying to pass in where condition: MEMBER [Measures].[ParameterCaption] AS [Organization].[Organization].CURRENTMEMBER.MEMBER_CAPTION MEMBER [Measures].[Parametervalue] AS [Organization].[Organization].CurrentMember.UNIQUENAME MEMBER...

Issue handling dimension member that does not exist

6 Jun 2018 by RosemaryJ

Hi, I have a requirement to create a calculated member ‘Sales Invoice as % of Revenue’. I have a ‘document-types’ dimension for which the document-typeIDs 16,19 and 20 indicate a ‘Sales Invoice’. The catch is that the DimDocumentTypes may not have all of the DocTypeIDs always. So in case , for...

How can you load an existing MDX query into SSAS's MDX editor?

15 Sep 2018 by Member 12824529

I have an existing sizable MDX query and need to modify it slightly without having to rewrite the entire MDX. I opened SSAS, selected my cube, right-clicked, and clicked Browse to open the MDX query editor. There, similar to graphically building SQL in SSMS, I can graphically build a new MDX...

How do I achieve proper aggregation using following MDX?

2 Sep 2019 by cluelessentity

I have the following MDX: SCOPE ([measures].[std_mg_qst_sup_olap]); SCOPE ([d_std_dim_questionnaire].[l_std_dim_questionnaire_key].[l_std_dim_questionnaire_key].members); THIS = sum( [d_std_dim_questionnaire].[l_std_dim_questionnaire_key].currentmember,...

Introduction to Percolator Analysis Services LINQ to MDX ORM

4 Jan 2015 by Suffyan Asad

Percolator Analysis Services is a LINQ to MDX ORM that enables writing MDX Queries in LINQ instead of using hard-coded strings of MDX code, resulting in cleaner, easier to read and easier to maintain code.

Wild Card in MDX Queries

26 Feb 2016 by Harikrishnan-BI

This tip discusses about the MDX-Wild Card expressions (like 'Like Operator in SQL) with examples.

How can I pass multiple value( as a parameter) as input in MDX query

15 Sep 2015 by Member 11985582

declare @BranchList nvarchar(255), @Description varchar(100),@DateFrom varchar(50),@DateTo varchar(50),@ContractBase int,@ScenarioNo nvarchar(100),@ResponsiblePerson nvarchar(250)set @BranchList='B0000002135'set @DateFrom='01/04/2014'set @DateTo='30/04/2014'set...

How to show row header name in MDX quaries

19 Sep 2016 by Member 11789566

How to Show Row Header Name in MDX QuariesWhat I have tried:SELECT NON EMPTY { [Measures].[Revenue] } ON Columns ,[Service].[ServiceGroup].[ServiceGroup].members ON Rows FROM [CCAnalyticsCube] WHERE ( [DimDate].[Year].[All], [DimDate].[Month].[All], [DimDate].[WeekOfYear].[All] )

How to fetch data from external datacubes for a 'data reservoir' in SQL in local machine

12 Dec 2016 by Member 12898095

Hi Guys,Greetings.I need to create a 'data reservoir' (a database) in SQL in a local machine (will act like a local server and will be accessible to a few analytics-team members), where a limited amount of data will be stored. This 'data reservoir' should be connected to multiple...