Optimizing Microsoft SQL Server Analysis Services: MDX Optimization Techniques: Segregating DISTINCT COUNT
There are more actions we can take within our current scenario, where we created the virtual cube, containing the DISTINCT_CUSTOMERS cube, which isolates the Distinct Count, to further increase performance. In our next article, we will examine a further step to leverage the solution we explored in this article to provide a higher degree of performance enhancement within the context of using distinct counts.
In this article, we extended our introduction to DISTINCT COUNT, and examined one approach to the optimization of its use within our applications. We focused upon the optimization of DISTINCT COUNT through the isolation of the distinct count attributes into a separate cube, and showed how this represents one of the more efficient approaches to optimizing the related functionality.
As in the other articles of our series, we set the stage by providing a hypothetical business requirement. We then examined a way to meet the requirement with an MDX query that contained DISTINCTCOUNT() in a calculated member, and that used a single cube as a data source. We noted query performance, and set about to improve it via the creation of a separate DISTINCT_CUSTOMERS cube, which we designed to house the distinct count attributes of our solution.
We then “married” the DISTINCT_CUSTOMERS cube to the initial cube data source through the creation of a virtual cube. Finally, we targeted the virtual cube with the query we had set out to improve, as a means of determining that performance could be enhanced through the forehanded use of an isolated distinct count cube in scenarios with similar business requirements.