26th December 2017We’re working hard on the next version that will include some important performance improvements such as smart cache and pre-compilation.
In the meantime, there are already a couple of “tricks” you can use to improve performance. Because they are specific to icCube, MDX+, they are not yet well known by all icCube’s users. So, we’re going to use a customer request as an excuse to present several ways for improving a classical MDX expression:
Sum( {a Set}, [some value] ).
This started with a customer telling us that some icCube requests were too slow, taking 20 seconds. This is certainly a lot of time, so we decided to dive into the query. To simplify, we are going to use parts of the original query that was using several calculated measures. It looks like this:
SET [MTD] as Mtd( ClosingPeriod([Calendar].[day]) ) SET [YTD] as Ytd( ParallelPeriod( [Calendar].[day], 1, ClosingPeriod([Calendar].[day]) ) ) member [Measures].[Sales MTD] as Sum( [MTD], [Measures].[sales] ) member [Measures].[Sales Last Year YTD] as Sum( [YTD], [Measures].[sales] )
We have a couple of measures that calculate sales for different periods, namely YTD and MTD. The hierarchy [Calendar] has been built so ClosingPeriod([Calendar].[day]) returns the last day with data. YTD and MTD will sum all days from start year and start month, respectively. For YTD we can have up to 365 days, so it’s time-consuming, especially if the query is done on several calculated measures, as was the case.
The first quick improvement is to use the CompactSet function, which transforms the input set into a smaller set. If all days of a month are in the set, they will be replaced by the month. Therefore, the size of a set decreases from hundreds of members to around 10–20 members.
This transformation is possible because the numerical expression used in the Sum function uses the sum-aggregation method. This would also be true for Min, Max, Count but not for Aggregations methods such as Distinct Count, Open Period, or End Period. It would also not be possible if the numerical expression had multiplication, as for example in a share of market.
The new MDX looks like this:
SET [MTD] as CompactSet( Mtd( ClosingPeriod([Calendar].[day]) ) ) SET [YTD] as CompactSet( Ytd( ParallelPeriod( [Calendar].[day], 1, ClosingPeriod([Calendar].[day]) ) ) ) member [Measures].[Sales PTD] as Sum( [MTD], [Measures].[sales] ) member [Measures].[Sales Last Year YTD] as Sum( [YTD], [Measures].[sales] )
This new query is already dropping the response time to 2.3 seconds—and this is a major improvement.
However, CompactSet will not work in all scenarios. So let’s try an alternate solution using our latest Eval() MDX+ function. This function is similar to the Aggregate() function but uses a subcube. The Eval() function is a bit smarter and will reuse subcubes defined with the same set.
SET [MTD] as Mtd( ClosingPeriod([Calendar].[day]) ) SET [YTD] as Ytd( ParallelPeriod( [Calendar].[day], 1, ClosingPeriod([Calendar].[day]) ) ) member [Measures].[Sales PTD] as Eval( [MTD], [Measures].[sales] ) member [Measures].[Sales Last Year YTD] as Eval( [YTD], [Measures].[sales] )
This query needs 3.2 seconds. It’s not as fast as the Compact version, but it is seven times faster than the initial version. If we combine it with CompactSet, the time drops to 1.7 seconds. This is our fastest version for now.
Finally, we are going to use Categories. Categories allow defining new members based on a set or a subcube, and is similar to the Eval function.
As [MTD] and [YTD] sets are defined at schema level, we can add them into the Advanced/Scripts panel:
CREATE STATIC SET [MTD Set] as Mtd( ClosingPeriod(([Calendar].[day]) ) CREATE STATIC SET [YTD Set] as Ytd( ParallelPeriod( [Calendar].[day], 1, ClosingPeriod([Calendar].[day]) ) ) CREATE CATEGORY MEMBER [Util].[Categories].[default].[MTD] as [MTD Set] CREATE CATEGORY MEMBER [Util].[Categories].[default].[YTD] as [YTD Set]
To use them is even simpler:
member [Measures].[Sales PTD] as ([Util].[Categories].[MTD], [Measures].[sales] ) member [Measures].[Sales Last Year YTD] as ([Util].[Categories].[YTD], [Measures].[sales] )
This is the fastest version, taking 1.6 seconds. It’s also IMHO, the cleanest and easy to reuse.
I hope these few examples will help you writing more efficient and faster MDX statements.
By David Alvarez