One of the changes introduced in Ranet 3.7 version was modification of expand-collapse algorithm of MDX query generation in the pivot table.
Previous versions of Ranet had one, default, mode of the algorithm. It generated a very large query, adding a new MDX function to the query with each expand and collapse operation, which, after a couple of dozens operations could lead to slower operation or hanging up of the application.
To address this issue, in version 3.7 we have introduced three modes of the expand-collapse algorithm: Default, Nested, and Excel.
DEFAULT MODE
Default mode is an updated version of the old algorithm we had in previous Ranet builds.
We have optimized it so that now it generates a smaller query. However, it is still significantly large as a new function is added each time expand or collapse operation is performed, making the query grow rather quickly.
Out of the three modes, Default provides the longest MDX query, and therefore there is a bigger chance of application performance slowdown.
However, it allows engineers to work with the initial MDX query, as opposed to the other two, which can be important for some projects.
NESTED MODE
Nested mode has an optimized expand-collapse algorithm as compared to the Default mode. With each new expand-collapse action, only one member unique name is added to the drilldown MDX function in the query. Thus, the size of the final query is much smaller than the one generated in Default mode. Nested is the most efficient out of the three modes.
Nested mode doesn’t provide access to the initial MDX query – engineers can only work with the MDX designer.
For the end user, there are no differences between Default and Nested modes as far as the view of the pivot table is concerned. Expand-collapse function works just the same as in the old Default mode, when each expand or collapse actions open hierarchy for a member in the axis only in the place where the action was performed. The difference is the size of the MDX query and subsequent application performance.
EXCEL MODE
In the Excel mode, expand-collapse algorithm works the same as in the Excel pivot table. Hierarchy members are expanded and collapsed, and then axis hierarchies are cross-joined. So, if you expand hierarchy member at one place, this member will be expanded in all others places in the axis.
As well as the Nested mode, Excel mode doesn’t allow engineers to work with the initial query.
Excel mode was developed to provide more convenient experience with Ranet pivot table for end-users used to working with MS Excel.
The modes are chosen by the engineer at design time. The solution currently doesn’t provide opportunities for end users to configure them at run time.
All in all, the above mentioned modes should be chosen in the following cases:
• If you need to work with the initial MDX query, use the Default mode.
• If there is no need to only need to work with the initial query, use the Nested mode. Out of the three modes, it generates the smallest query, and therefore is the most efficient.
• In case the end-users are used to working in MS Excel, consider choosing Excel mode. It is a bit less efficient that the Nested mode, but will be more convenient for users.