Database Reference
In-Depth Information
want to create a virtual member to calculate margin Percent and return the results for
all 12 months of the year.
WITH MEMBER [Measures].[Margin Per] AS
'([Measures].[Margin] / [Measures].[Sales]) * 100'
SELECT
{[Measures].[Sales], [Measures].[Margin],
[Measures].[Margin Per]} ON COLUMNS,
{[Year].levels(0).members} ON ROWS
FROM Sample.Basic
Axis-1 (Sales) (Margin) (Margin Per)
+------------+------------+------------+-----------
(Jan)
32538.00
18378.00
56.48
(Feb)
32069.00
17762.00
55.39
(Mar)
32213.00
17803.00
55.27
(Apr)
32917.00
18242.00
55.42
(May)
33674.00
18618.00
55.29
(Jun)
35088.00
19457.00
55.45
(Jul)
36134.00
20012.00
55.38
(Aug)
36008.00
19736.00
54.81
(Sep)
33073.00
18124.00
54.80
(Oct)
32828.00
18186.00
55.40
(Nov)
31971.00
17766.00
55.57
(Dec)
33342.00
18435.00
55.29
note that within the WIth mEmBEr specification, everything between the sin-
gle quotes is exactly what you would find in a member formula if you had created the
[margin Per] member in an ASo Essbase outline.
The WIth mEmBEr specification can be extremely useful for creating calculated
members on the fly for ad hoc or one-time use. Essbase developers also can leverage this
functionality to test the results of potential member formulas before adding them to the
outline. A formula can be developed and tested without touching the database, which
can be very helpful in decentralized environments.
6.7.3 Solve Order
Solve order is an optional property you can add to a calculated member in an mDx query.
It is also a property that can be assigned directly to a member through a member formula
in an ASo Essbase outline. Solve order allows you to control the order in which calcula-
tions are executed when they are simultaneously queried. There are times when the results
of one calculation may be dependent upon the results of another calculation. In these
cases, it is important to properly define which calculation should execute first. When
defining solve order for a calculated member, the calculations will execute in ascending
order, so the lowest number will execute first and the highest number will execute last.
In the event of a tie where the solve orders are the same, the members execute in outline
order, with the members belonging to the dimension that comes later in the outline win-
ning the tie.
Solve order should be considered for performance tuning as well. While I have been
unable to ind documentation that directly supports this (until the writing of this topic),
my experience with Essbase ASo databases has been that often I can get better query
response time on members that have complex member formulas when I increase the
