Database Reference
In-Depth Information
{[Curr Year]} ON AXIS(2),
{[Sale]} ON AXIS(3),
{[Cash]} ON AXIS(4),
{[No Promotion]} ON AXIS(5),
{[1 to 13 Years]} ON AXIS(6),
{[Under 20,000]} ON AXIS(7),
{[Memory]} ON AXIS(8),
{[05351]} ON AXIS(9),
{[Stores].levels(0).members} ON AXIS(10)
FROM ASOsamp.Sample
Versus
WITH MEMBER [Measures].[Test1] AS
'CASE WHEN IsLevel([Time].CurrentMember, 0) THEN
CASE WHEN IsLevel([Years].CurrentMember, 0) THEN
CASE WHEN IsLevel([Transaction Type].CurrentMember, 0) THEN
CASE WHEN IsLevel([Payment Type].CurrentMember, 0) THEN
CASE WHEN IsLevel([Promotions].CurrentMember, 0) THEN
CASE WHEN IsLevel([Age].CurrentMember, 0) THEN
CASE WHEN IsLevel([Income Level].CurrentMember, 0) THEN
CASE WHEN IsLevel([Products].CurrentMember, 0) THEN
CASE WHEN IsLevel([Stores].CurrentMember, 0) THEN
CASE WHEN IsLevel([Geography].CurrentMember, 0) THEN
SUM([Geography].members, [Transactions])
ELSE MISSING END
ELSE MISSING END
ELSE MISSING END
ELSE MISSING END
ELSE MISSING END
ELSE MISSING END
ELSE MISSING END
ELSE MISSING END
ELSE MISSING END
ELSE MISSING END'
SELECT
{[Measures].[Units], [Measures].[Transactions], [Measures].[Test1]}
ON AXIS(0),
{[Time]} ON AXIS(1),
{[Curr Year]} ON AXIS(2),
{[Sale]} ON AXIS(3),
{[Cash]} ON AXIS(4),
{[No Promotion]} ON AXIS(5),
{[1 to 13 Years]} ON AXIS(6),
{[Under 20,000]} ON AXIS(7),
{[Memory]} ON AXIS(8),
{[05351]} ON AXIS(9),
{[Stores].levels(0).members} ON AXIS(10)
FROM ASOsamp.Sample
Both of these examples will yield the same results. The second example has more
lines of code, but depending on the size of the database and what you are trying to
execute, in many cases the second example will return faster. For instance, if I run the
first query against ASosamp, it returns in approximately seven seconds. The second
