AnalytiX - How to easily execute a drill operation on an OLAP cube with data pre-filtering
Recently I have come accross the following issue: I had to solve a drill operation in the easiest possible way in the case of a mobile application that uses WebService and MDX calls to communicate with an MSSQL Analysis Service operating an OLAP cube.
In itself, the drill function can be solved with relatively simple commands during the MDX call with the help of the DESCENDANTS method call. The only issue in this case is that the call returns all the members (data) found on the given hierarchy-level along the chosen dimension. What caused most of the deliberation was how to use the DESCENDANTS method in a way that it returns the values in summary at the given hierarchy level for a defined set of data. The easiest possible solution for this issue was CREATE SUBCUBE.
With the help of CREATE SUBCUBE call it is possible to make the cube in use react to all operations as if it only contained the set of values provided in the CREATE SUBCUBE base and no more. Following this the DESCENDANTS method call now returns with the desired result. This solution, however, has two drawbacks. One of them is, that after the CREATE SUBCUBE call all operations have to occur in the same session. Moreover, inside a session the cube always behaves as if it only contained the filtered dataset, until a DROP SUBCUBE call is not executed. Moreover, because of certain qualities of the MDX language the three instructions can not be executed in one single command one after the other, but have to be run as three separate commands. Therefore, the final solution for the issue is as follows:
1. command inside the session:
-- Data pre-filtering
CREATE SUBCUBE [Cube_Name] AS
(
SELECT …
)
2. command inside the session:
SELECT
{
…
}
FROM [Cube_Name]
…
3. command inside the session:
DROP SUBCUBE [Cube_Name]
Written by RP
