WITH clause in MDX
In MDX queries, the WITH clause has an important role to play. WITH clauses are used to define some members which will be used in the SELECT clause for analysis purposes. The WITH clause contains either declarations of multiple sets and multiple members. Also, it is used to perform certain calculations like calculating profits, calculating prev year’s information, etc.
Below are some examples of the MDX queries using the WITH clause,
1. MDX Query to calculate the Total Sales for each year:
SELECT NON EMPTY {[Measures].[Sales], [Measures].[Quantity], [Measures].[Total Sales]} ON COLUMNS,
NON EMPTY [Time].[Years].members ON ROWS
FROM [SteelWheelsSales]
In the above example, the WITH clause was used to calculate the total sales value by multiplying the measures “Sales” and “Quantity”. The WITH clause creates a member [Measures].[Total Sales] (it can be used to create a new measure or a new dimension member to calculate based on the existing measures). After the WITH Clause, the member “Measures.Total Sales” is used as a measure in the SELECT clause and is displayed in the column axis whereas the dimensional data is displayed in the row axis.
Result:
2. MDX Query to set a format string in a WITH Clause:
format_string=’$#,##0.00′
SELECT {[Measures].[Sales], [Measures].[Quantity], [Measures].[Total Sales]} ON COLUMNS,
[Time].[Years].members ON ROWS
FROM [SteelWheelsSales]
In the same query, if we add a format_string keyword and specify the format in which you want to display the values in that particular member, then we would get the desired result in the output. Alternatively, you can use the name(for example, here its currency).
Result:
3. MDX query to generate result using SET in WITH CLAUSE:
SET [Not 2005] as ‘Filter({[Time].[Years].Members}, ([Time].[Years].CurrentMember.Name <> “2005”))’
SELECT {[Measures].[Sales], [Measures].[Quantity], [Measures].[Total Sales]} ON COLUMNS,
[Not 2005] ON ROWS
FROM [SteelWheelsSales]
Now lets suppose you want to show all the data except for 2015. You can use the SET Function which will filter the data (as shown in the above example) and will show you the data for all except 2005.
Result:
4. MDX Query to compare data for current year and prior year:
member [Measures].[Last Period] as ‘([Time].[Years].CurrentMember.PrevMember, [Measures].[Sales])’
SELECT {[Measures].[Sales],[Measures].[Last Period]} ON ROWS,
[Time].[Years].Members ON COLUMNS
FROM [SteelWheelsSales]
In the above expression, the member [Measures].[Last Period] is used to generate Sales information of the previous year. The CurrentMember.PrevMember function is used to generate the Previous Year information(you can do it similarly for any time member) based upon the Measure that you have specified. This is also done using WITH Clause.
Result:
Conclusion:
The WITH Clause in MDX is very similar to the WITH Clause we use in SQL SERVER/Postgres. It helps us in understand the functionality better and is also used for faster processing. The WITH CACHE clause is used for optimization which helps in faster query execution.
Best Open Source Business Intelligence Software Helical Insight is Here
A Business Intelligence Framework
Thanks,
Nitish Kumar Mishra
MDX MDX Queries OLAP Analysis pentaho