Enhance the Foodmart Percent of Total Cube to Support the Percent of Total Calculation
From the
perspective of the Percent of Total cube, in order to present the percent
of total information requested by the information consumers, we will need to
create a calculated member in Analysis Services.
1.
Right-click
the new Percent of Total cube.
2.
Select Edit
... from the context menu that appears, as shown in Illustration 5.
The Cube
Editor opens.
3.
Right-click
the Calculated Members folder within Cube Editor.
4.
Select New
Calculated Member ... from the context menu that appears, as depicted in Illustration
6.
Illustration 6: Select
New Calculated Member from the Context Menu
The Calculated Member
Builder opens.
5.
Ensure that Measures
is selected in the Parent dimension selector atop the Calculated
Member Builder.
6.
Type the
following into the Member name box within the Calculated Member
Builder:
Percent Total Sales
7.
Type the
following into the Value expression box within the Calculated Member
Builder:
([Store Sales], [Store].CURRENTMEMBER) / ([Store Sales], [Store].[All Stores])
The above MDX provides
for a simple Percent Total Sales calculation. In essence, it consists
of Store Sales for the currently selected Store, divided by Store
Sales for all Stores. We will assign a "percent" format
in the following steps, mostly for purposes of viewing in the cube browser - we
will handle formatting for the report at the Reporting Services level in
a subsequent section. Keep in mind that the above is a simple approach, and
that, in a real world scenario, we would want to build in more sophisticated
logic to manage such scenarios as nulls, composition of rollups, and so forth.
The Calculated Member
Builder appears as shown in Illustration 7.
Illustration 7: The
Calculated Member Builder, with Our Input
8.
Click OK
to close the Calculated Member Builder, and to accept our input.
We see the new Calculated
Member appear in the tree pane, within the Calculated Member folder
9.
Click the Percent Total Sales calculated member in the tree, to
select it, if necessary.
10.
Click the Properties
bar at the bottom of the pane, to open the Properties pane, if
necessary. (If "Properties" appears on the bar with an
upward-pointing arrow to its right, clicking will open the pane / point the
arrow downward).
11.
Click the Advanced
tab in the Properties pane.
12.
In the
selector for the Format String property box, select Percent, as
depicted in Illustration 8.
Illustration 8: Setting
Percent Format for the Percent Total Sales Calculated Member
Let's
process the cube, to update it for the first time since its cloning.
13.
Select Tools --> Process Cube from the Cube Editor main
menu, as shown in Illustration 9.
Illustration 9: Process
the Cube ...
14.
Click Yes
when prompted to save the cube, as depicted in Illustration 10.
Illustration 10: Save
the Cube before Processing
15.
Click No
when asked if you wish to design aggregations, in the next Analysis Manager
dialog, shown in Illustration
11.
Illustration 11: Decline
Aggregation Design ...
The Process a Cube dialog
appears.
16.
Ensure that
the radio button to the left of Full Process, as depicted in Illustration
12, is selected (the default, as the cube has not been processed since its
creation).
Illustration 12: Full
Process Option Selected ...
17.
Click OK
to begin processing.
Processing begins. The Process viewer displays
various logged events, then presents a green Processing completed successfully message, as shown
in Illustration 13.
Illustration 13: Processing
Completes Successfully ...
18.
Click Close
to dismiss the viewer.
Let's do
a quick verification of the operation of the cube's newest calculated member, Percent Total Sales. We can examine the results of our
handiwork while here in the Analysis Manager, via the Cube Browser,
before revisiting it later in our report. This allows us to ensure
effectiveness before we leave the design environment.
19. From our present position within
the Cube Editor, click
the Data tab to open the Browser and see cube data.
20. Drag the Store dimension
down, and drop it atop the existing row ("Y") axis heading, as
necessary.
21. Ensure that the Measures
dimension appears on the column ("X") axis, dragging it there if
necessary.
The Data tab
matrix appears as partially depicted in Illustration 14.
Illustration 14: The
Data Tab Matrix Axes, with Our Modifications (Partial View)
22. Double-click the USA
Store Country in the row axis to expand it.
23. Expand the Store State level for Washington (WA).
24. Scroll over to the right in the Cube
Browser, until our new calculated measure, Percent Total Sales,
appears, as shown in Illustration 15.
Illustration 15: Percent
Total Sales Appears ...
We can readily see that
the simple percent of total calculation is performing as expected. We will now
move to Reporting Services, where we will create a similar calculated field,
which we will then juxtapose in a report side by side with the Percent Total
Sales calculated member in the cube, to compare the two, and discuss
considerations surrounding the two methods from the standpoint of an integrated
BI solution.
25.
Verify the math
behind the Percent Total Sales values, if desired, to gain comfort with
its correctness.
26.
Leave the Cube
Editor open in Data view, for comparison against our report
calculations later, if desired.
» 
Comment and Contribute
