Database Reference
In-Depth Information
surprised with what you find and may reconsider the dimension order for the outline.
I acknowledge that Sample.Basic is too small to notice a performance difference, but you
can build on this and use it with your larger BSo applications for potential improve-
ments in aggregation time.
WITH
MEMBER [Measures].[Market Ratio] AS
'(COUNT([Market].members) - COUNT([Market].levels(0).members)) /
(COUNT([Market].members) − 1)'
MEMBER [Measures].[Product Ratio] AS
'(COUNT(Except([Product].members, Descendants([Diet]))) −
COUNT(Except([Product].levels(0).members,
Descendants([Diet])))) /
(COUNT(Except([Product].members, Descendants([Diet]))) − 1)'
SELECT {[Year]} ON AXIS(0),
{ [Measures].[Market Ratio], [Measures].[Product Ratio] } ON AXIS(1)
FROM [Sample.Basic];
Axis-1 (Year)
+-------------------+------
(Market Ratio)
0.2083
(Product Ratio)
0.2941
A review of the Dimensions tab for Sample.Basic in EAS shows that the Product
dimension has fewer stored members compared to the market dimension and, therefore,
the designer set the Product dimension to precede the market dimension in the outline
order. Based on the calculations above, there is a good argument to have the market
dimension precede the Product dimension based on the smaller parent/child ratio. As
with most concepts pertaining to Essbase, it is hard to put an absolute rule that will always
present optimal results. nonetheless, I encourage developers to run this calculation and
consider testing alternate outline to see if BSo calculation performance can be enhanced.
6 .11 Cl o si ng
my hope in writing this chapter is that the reader comes away from it with a better under-
standing of not only how to make mDx work, but, more importantly, some creative ways
to use mDx. my experience has been that very few Essbase users use mDx for anything
other than member formulas in an ASo database. While admittedly that is a required
skill for ASo developers, I believe mDx can enhance the user experience beyond member
formulas. one of the reasons I think it is difficult to get users to invest time in learning
mDx is because in many ways it goes against the ease of use that Essbase offers. Why
should someone learn how to write complex mDx scripts when they can simply use the
office Add-In or Smartview to easily retrieve a lot of the same content without the use of
scripts? hopefully, I have done a good job of demonstrating some use cases where it would
be difficult to obtain the same results using anything other than mDx. my expectation is
that the reader of this topic is not the everyday Essbase end user and, therefore, will have
an appreciation for the nuances that mDx offers. I also believe that a thorough under-
standing of these concepts will enable any Essbase developer to write more advanced
member formulas when the requirement arises. Best of luck in your coding journeys.
