Easiest way to programmatically generate MDX rowcount query?

Question

Right now I'm dealing with a program that can generate and return SQL or MDX queries (depending on the source database of the queries). I'm working on adding a feature that counts all the rows returned by a given query.

Now, I have some small background with SQL, so I was able to parse table names and generate a rowcount. However, MDX is a completely new beast for me.

In SQL, I'm creating:

SELECT 
   COUNT(SUM)
AS ROWS
FROM
(
  COUNT(*) AS COUNT FROM TABLE1
  UNION ALL
  COUNT(*) AS COUNT FROM TABLE2
  UNION ALL
  COUNT(*) AS COUNT FROM TABLE3
  ETC...
)

Now, what I'm wondering is, how would I do something similar with MDX? I've done some reading on MDX, and from what I gathered the basic notation is

[Dimension].[Hierarchy].[Level]

Now with SQL, I parsed the table names out of a larger generated query and simply inserted them into a new programmatically generated query. What would I have to grab from a larger MDX query to generate my own rowcounting query and sending it off to run? A simpler example of the MDX I'm dealing with would be:

 WITH
     MEMBER [BUSINESS1].[XQE_RS_CM1] AS '([BUSINESS1].[COMPANY_H].[all])', SOLVE_ORDER = 8
     MEMBER [BUSINESS2].[XQE_RS_CM0] AS '([BUSINESS2].[all])', SOLVE_ORDER = 4
SELECT 
     NON EMPTY {[BUSINESS2].[ALL_TIME_H].[CALENDAR_YEAR_L].MEMBERS AS [XQE_SA1] , HEAD({[BUSINESS2].[XQE_RS_CM0]}, COUNT(HEAD([XQE_SA1]), INCLUDEEMPTY))} DIMENSION PROPERTIES PARENT_LEVEL,  PARENT_UNIQUE_NAME ON AXIS(0), 
     NON EMPTY {[BUSINESS1].[COMPANY_H].[COMPANY_CD__L].MEMBERS AS [XQE_SA0] , HEAD({[BUSINESS1].[XQE_RS_CM1]}, COUNT(HEAD([XQE_SA0]), INCLUDEEMPTY))} DIMENSION PROPERTIES PARENT_LEVEL,  PARENT_UNIQUE_NAME ON AXIS(1), 
     NON EMPTY {[Measures].[Measures].[BUSINESS3]} DIMENSION PROPERTIES PARENT_LEVEL,  PARENT_UNIQUE_NAME ON AXIS(2)
FROM 
    [SOURCE]  CELL PROPERTIES CELL_ORDINAL,  FORMAT_STRING,  VALUE

Any insight would be awesome, thanks.

Answer 1

At first glance your script looks reasonable then after unravelling it becomes a bit(!) more complex.

The main difference between this and other scripts is its use of axis(2). In a sub-select extra dimensions are often used but this is a little odd as most clients can't handle 3 dimensional cellsets - so I'm intrigued by what is consuming this info?

Also the member [BUSINESS1].[XQE_RS_CM1] is a single member as is [BUSINESS2].[XQE_RS_CM0] so what is the point of the sections HEAD... ?

WITH
     MEMBER [BUSINESS1].[XQE_RS_CM1] AS 
         ([BUSINESS1].[COMPANY_H].[all]), SOLVE_ORDER = 8
     MEMBER [BUSINESS2].[XQE_RS_CM0] AS 
         ([BUSINESS2].[all]), SOLVE_ORDER = 4
SELECT 
     NON EMPTY 
         {[BUSINESS2].[ALL_TIME_H].[CALENDAR_YEAR_L].MEMBERS AS [XQE_SA1] 
         ,HEAD(
            {[BUSINESS2].[XQE_RS_CM0]}, 
            COUNT(
              HEAD([XQE_SA1])
             ,INCLUDEEMPTY
            )
          )}
     ON AXIS(0), 
     NON EMPTY 
         {[BUSINESS1].[COMPANY_H].[COMPANY_CD__L].MEMBERS AS [XQE_SA0] 
         ,HEAD(
            {[BUSINESS1].[XQE_RS_CM1]}, 
            COUNT(
              HEAD([XQE_SA0])
             ,INCLUDEEMPTY
            )
          )}
     ON AXIS(1), 
     NON EMPTY 
        {
          [Measures].[Measures].[BUSINESS3]
        } 
     ON AXIS(2)
FROM 
    [SOURCE]

Does the following return the same data as the original script?

SELECT 
     NON EMPTY 
         {
          [BUSINESS2].[ALL_TIME_H].[CALENDAR_YEAR_L].MEMBERS 
         ,[BUSINESS2].[all]
         }
     ON 0, 
     NON EMPTY 
         {
          [BUSINESS1].[COMPANY_H].[COMPANY_CD__L].MEMBERS
         ,[BUSINESS1].[COMPANY_H].[all]
         }
     ON 1
FROM  [SOURCE]
WHERE [Measures].[Measures].[BUSINESS3];

All you need to calculate then is the count of members returned in the following set on the rows:

{
  [BUSINESS1].[COMPANY_H].[COMPANY_CD__L].MEMBERS
 ,[BUSINESS1].[COMPANY_H].[all]
}