Verification
Deploy the Analysis Services
Solution
Deploying
the solution will dispatch the code we have created using the Cube Wizard
and Designer interfaces. Once the XML reaches the targeted Analysis
Server (the destination server must be targeted, before beginning
deployment, within the project's Deployment Configuration Properties
page), the Analysis Services database is created and processed. Once
this has been accomplished, we can fully leverage the browser that the Cube
Designer makes available to us.
1.
Right-click
the ANSYS048 Measure Groups solution, atop the tree in Solution
Explorer.
2.
Select Deploy
Solution from the context menu that appears, as depicted in Illustration
46.
Illustration 46: Deploying
the Solution ...
Processing begins, as we
can see in the Deployment Progress pane. Once deployment has
completed successfully, we see a message to this effect in the Status
area, toward the bottom of the pane, as depicted in Illustration 47.
Illustration 47:
Indication of Successful Deployment Appears ...
We are
left with a processed Analysis Services database and cube. We are ready
for the final part of our article, where we will verify the effectiveness of our
handiwork via the Cube Browser.
Browse the Cube
We can
get to the Cube Browser, at this stage, and see our Measure Groups
at work within the cube, by taking the following steps.
3.
Click the Browser
tab within the Cube Designer, as shown in Illustration 48.
Illustration 48: Click
the Browser Tab ...
4.
Within the
tree appearing inside the Metadata pane, on the left side of the Browser
tab, expand Dim Time by clicking the "+" sign to its
immediate left.
5.
Drag Hierarchy
Dim Time.CalendarYear - CalendarQuarter - EnglishMonthName –
FullDateAlternateKey to the area marked Drop Row Fields Here within
the Pivot Table area, as depicted in Illustration 49.
Illustration 49:
Creating a PivotTable Browse ...
6.
Within the Metadata
pane, once again, expand Measures.
7.
Expand the Fact
Internet Sales folder.
8.
Drag Sales
Amount to the area marked Drop Totals or Detail Fields Here within
the Pivot Table area, as shown in Illustration 50.
Illustration 50: Adding
the First Measure, from the Fact Internet Sales Measure Group ...
9.
Expand the Fact
Sales Quota folder.
10. Drag Sales Amount Quota to
the area marked Drop Totals or Detail Fields Here within the Pivot
Table area, dropping it to the immediate right of the Sales Amount measure.
The Browser pivot
table area appears as depicted in Illustration 51.
Illustration 51: The
Browser Pivot Table with Additions ...
We might
assemble any number of browse scenarios at this point. This includes
juxtaposing various column and row combinations, as well as crossjoining on
either column or row axes, adding filters of various types, and more, within
the Pivot Table (assuming that we have enacted the appropriate Dimension
Usage settings to support the combinations we select). For the purposes of
our exercise, we will simply verify the granularity we have imposed over the Time
dimension hierarchy for each of the measures we have engaged above.
11. Within the column headed "CalendarYear,"
expand the year 2003 by clicking the "+" sign to its immediate
left.
12. Expand quarter 4 (underneath
the CalendarQuarter column heading) within the expanded year.
13. Expand the month of December (underneath
the EnglishMonthName column heading) within the expanded quarter.
The Time
dimension hierarchy expands to reveal the totals, by level of
the Time dimension, as shown in Illustration 52.
Illustration 52: The Measures
Totals Appearing at Time Dimension Hierarchy Levels
We note
that the Sales Amount Quota measure remains fixed at the annual
value, within all Time dimension levels, while the Sales Amount
measure "drills down" to the individual date level of the Time dimension
hierarchy. This circumstance allows us to verify the expected operation of the Dimension
Usage settings, which dictate that the granularity of the Sales Amount
Quota (a member of the Fact Sales Quota Measure Group) is the CalendarYear
level. Moreover, we can see that the granularity of the actual Sales Amount
(a member of the Fact Sales Quota Measure Group) is established at the
individual date level.
We can
visualize an array of reporting capabilities at this point, once we add
formatting and other nuances. For now, we will conclude our session, having
focused upon the establishment of granularity for different Measure Groups
to extend our cube to leverage multiple fact tables for desired reporting and
analysis presentations.
14. Examine further the Measure
Groups within the Cube Browser as desired, perhaps with
corresponding alterations in the Dimension Usage settings to obtain
specific effects. (Remember to re-process the cube after any changes, before
browsing).
15. Select File -> Save All from the main menu, to save our
work through this point, as depicted in Illustration 53.
Illustration 53: Saving All Work
from Our Session
16. Select File -> Exit, when ready, to leave the Business
Intelligence Development Studio.
Conclusion
In this article, we
examined Measure Groups, which debut in Analysis Services 2005.
After introducing the concept of Measure Groups, we overviewed their
creation, and discussed ways in which they can offer flexibility in cube and
solution / application design and development. In preparation for our
examination of the steps involved in extending our cubes to leverage multiple
fact tables, we prepared Analysis Services, and our environment, by
creating an Analysis Services Project to house our development steps,
and to serve as a platform for the design of a quick cube model, within which
to perform subsequent procedures in our session. We next performed the steps
that are common to the design and creation of any cube within Analysis
Services 2005, including the creation of a Data Source, containing
the information Analysis Services needs to connect to a database, and a Data
Source View containing schema information.
We next created a basic cube, referencing our Data
Source and Data Source View, which contained data from our sample
relational tables. We then focused upon the addition of examples of Measure
Groups as part of cube design, from the perspective of the Cube Wizard.
We then got some hands-on exposure to assigning, via the Dimensional Usage
tab of the Designer, granularity at measure / dimension
intersects for members of the new Measure Groups. Finally, after deploying
our Analysis Services solution, we browsed the cube,
focusing on the new Measure Groups and associated details.
»
See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.
» 