Verify Operation of the
Distinct Count Measure in the Browser
Let’s
take a look at our new distinct count measure in the Cube Browser,
so as to verify its operation.
1.
Click the Browser
tab within the design environment.
2.
Click the Reconnect
button in the Browser toolbar.
3.
Expand the Customer
dimension in the cube metadata tree, and then expand the Customer Geography
hierarchy, if necessary. Drag the Country level therein to the
empty data grid, dropping it in the area marked Drop Row Fields Here, as
depicted in Illustration 6.
Illustration
6: Adding the Country Level to the Browser Rows Axis ...
4.
Expand the Date
dimension in the cube metadata tree, and then expand the Calendar folder,
as necessary. Drag the Date.Calendar Year
attribute hierarchy into the area marked Drop Column Fields Here.
5.
Expand Measures,
and then the Internet Sales Measure Group. Drag the Internet Order
Quantity measure to the area of the grid marked Drop Totals or Detail
Fields Here.
6.
Again
within Measures, expand the new Product Distinct Count Measure Group,
if necessary. Drag the new Distinct Products measure to the area of the
grid marked Drop Totals or Detail Fields Here, dropping it to the right
of the first column containing the Internet Order Quantity measure.
A
partial view of our newly assembled and populated browser grid is shown in Illustration
7.
Illustration
7: The Browser with our Viewer Settings (Partial View) ...
And so
we see that our distinct count measure appears to operate as expected.
Our verification is made easier by comparing a “pure count” to our distinct
count measure, which will, of course, typically be significantly smaller than
the gross count value.
We can
leverage distinct
count measures,
and enjoy the multiple benefits we have enumerated, within our own business
environments, as easily as we have done in our practice session. Distinct
counts rank highly among a host of design improvements in Analysis
Services 2005.
Conclusion
In this article, we examined distinct
counts in Analysis Services 2005. We reviewed the general concept
of distinct counts, discussing why they are useful (and often required)
within the design of any robust analysis effort. In other articles, I have described
some of the challenges that were inherent in their use in Analysis Services
2000, typically before undertaking a practice exercise whereby we
constructed a distinct count measure to meet the business requirements
of a hypothetical client.
As a part of introducing distinct counts as they are
managed in Analysis Services 2005, we discussed various aspects of the
redesign of the capability, based upon the hierarchy and attribute structure
that debuts in Analysis Services 2005. We noted that the enhancement of
distinct counts results in much more impressive performance, and
flexibility of deployment, within our integrated business intelligence
solutions. We contrasted the new capabilities with some of the challenges inherent with using distinct
counts in Analysis Services 2000, focusing on how distinct counts
have been expanded in Analysis Services 2005 to overcome some of the shortcomings
of the previous version.
We then created a distinct count measure within the
Adventure Works sample cube to demonstrate the ease with which we can
add distinct count capabilities to the cubes in our individual business
environments. Finally, we verified the operation of the distinct count
measure using the browser within our freshly deployed cube. Throughout the
steps of our practice exercise, we highlighted other considerations that
surround the efficient use of distinct counts.
»
See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.
» 
Comment and Contribute