Optimizing Microsoft SQL Server Analysis Services: MDX Optimization Techniques: Segregating DISTINCT COUNT
Now that we have a processed cube consisting of identical dimensions to those that exist in the Sales cube, together with the single distinct count measure, Distinct Customers, that we have created to store our distinct customer count information, we are ready to leave the new cube, and to move to the next step of integrating it into our overall solution for enhancing performance of distinct counts in our Sales data.
41. Select File —> Exit from the main menu.
The Cube Editor closes, and we are returned to Analysis Manager.
Creating a Virtual Cube to House Our Solution
We will now combine the cubes with which we have been working in this session within a virtual cube. The intent will be to create a “logical” cube that appears as a standalone physical cube, from a browsing and reporting perspective. We will thus combine functionality into a single logical cube, accruing benefits that we will discuss as we encounter them.
As many of us know already, a virtual cube allows us to broaden both the scope and capabilities of the OLAP data contained within the participating MSAS cubes, while incurring no additional physical storage space requirement. Virtual cubes can be useful in many applications, one of which is the combination of standard cubes for the purposes upon which our practice example is focused.
Let’s take the following steps to create the virtual cube to complete the assembly of our solution.
1. Right-click the Cubes folder.
2. Click New Virtual Cube on the context menu, as shown in Figure 22.
Figure 22: Select New Virtual Cube
The Virtual Cube Wizard Welcome Dialog appears as depicted in Figure 23.
Figure 23: The Virtual Cube Wizard Welcome Dialog
3. Click Next.
The Select the cubes to include in the virtual cube dialog appears.
4. Double-click the DISTINCT_CUSTOMERS cube, to select and move it from the Available cubes list to the Virtual cube includes list.
5. Double-click the Sales cube to add it to the Virtual cube includes list, as well.
The dialog appears as shown in Figure 24, after our selections.
Figure 24: Select the Cubes to Include in the Virtual Cube Dialog
6. Click Next.
The Select the Measures for the Virtual Cube dialog appears.