Leaves() : An example to understand it for both regular hierarchies as well as parent child hierarchies

Leaves() Gotcha in Analysis Services 2005 SP2: Be aware of the number of tuples that the MDX script is going to affect.

The other day, I was trying to define a calculation for a member at the leaf level of all the dimension. After defining the formula and deploying the MDX script, the results were not displayed in the query in SS Management Studio 2005.

When I ran the same query in MDX Sample Application (Analysis Services 2000 utility to run MDX queries), it displayed the error as below:

image

It seems that the MDX script that I had setup was affecting more than 4.3 billion tuples in the set. The cube I used had 8 dimension with varying levels. I tried to search if this is documented, but could not find it.

Leaves() is an interesting MDX functions introduced in SQL 2005. It is intended to be used only inside MDX Scripts to define the scope of the calculations (i.e. in the left-hand side of the assignments).

There are many situations where in we wish to perform the calculation at the leaf level of dimension and then aggregate or roll it up like any other member.

Mosha, in his blog explained the use of leaf level for achieving the above objective.

This article explains how we can implement Leaves() in both parent child as well as regular hierarchies using AdventureWorks cube.

Leaves() function use with parent child hierarchy:

My objective was to create a measure 'BudAmount' which is equal to 'Amount' at the leaf level and then it needs to be aggregated like 'Amount' measure across any hierarchy in AdventureWorks' "Financial Reporting" measuregroup .

I read a post in MSDN forum which was similar to my need. In the post, it was needed to define a calculated measure at leaf level and then aggregate the calculated measure like any normal measure.

Chris Webb, in the answer to the post, suggested that:

"...It's certainly a fairly common problem, but Mosha has indeed blogged about the best way to handle this:

http://www.sqljunkies.com/WebLog/mosha/archive/2005/02/13/7784.aspx

Create a new real (ie not calculated) measure in your cube from a column that is always null in your fact table, and then assign the calculated value to the leaf level of all dimensions. Something like:

(Measures.CM, Leaves()) = iif(Measures.M>0.9, 1, null);"

Based on this newfound knowledge, I started to setup my BudAmount measure.

Below are the steps:

1. In the DSV, changed the FactFiance table to a named query and added a column named  'BudAmount' with value as 0 (zero).

image

2. In cube, added the measure 'BudAmount' alongside of 'Amount'

image

3. In mdx script, added the below script:

SCOPE (Leaves(),[Measures].[BudAmount]); 
  this = [Measures].[Amount];
END SCOPE;

image

4. One important thing which I had missed earlier and the Leandro Tubia pointed it out at the MSDN Analysis Services forum to setup the AggregatFunction of BudAmount to be same as Amount i.e. "ByAccount". Otherwise, for balance sheet account types, the amounts would be summed up rather than the last month values for higher levels of Time Dimension.

image

5. Full processed the cube (I am yet to understand the appropriate processing to be used, so full processing may not be needed,  but just to be on safer side, using full processing)

6.  Now, when you browse the cube, the value of BudAmount and Amount is same as desired.

image

Leaves() function use with with regular hierarchies:

In Adventureworks, I created a measure (per Chris Webb, it needs to be a real measure, not a calculated one) called 'SalesAmount1' on adventureworks cube which provides the same data as 'SalesAmount' measure. I mean if I query both the measure anyway, both should behave in same way and return the same amount at any level of other dimension.

Below are the steps:

1. In the DSV, in 'FactSalesSummary' query, add additional column as 'SalesAmount1' with value as 0.

image

2. Under 'Sales Summary' measure group, add the 'SalesAmount1' measure. (look for any white space in the name).

image

3. In each of Sales Summary partition, add the new 'SalesAmount1' column:

image

4. In the MDX script, define the calculation:

SCOPE (Leaves(),[MEASURES].[SalesAmount1]);
  this = [Measures].[Sales Amount];
END SCOPE;

image

5. Check if the SalesAmount1 measure is same as [Sales Amount]. Yes, it is same.

SELECT
    NON EMPTY
    [Product].[Product Categories].[Category].MEMBERS
    ON ROWS ,
    {   [Measures].[Sales Amount], [Measures].[SalesAmount1]
    }
    ON COLUMNS
    FROM [Adventure Works]

image

6. Even if the cube is browsed in management studio, the value of both the measure is same. So, Leaves() is working in case of regular hierarchies.

image

19 comments:

Anonymous said...

What if you move your SCOPE statement before CALCULATE statement?
V.M.

Anonymous said...

Did that, no effect.

Unknown said...

Is that a parent/child dimension in the query? That could be messing up the way that the Scope works if it is.

Ajit Singh said...

Chris,

Yes, the account dimension is a parent child dimension in my case. I recreated the problem in Adventureworks and updated my blog. The Leaves() seems to be working there perfectly but not in the case of parent child. I am off to sleep now and would check your reply tomorrow. Thanks for the help.

Ajit.

Ajit Singh said...

Hello Chris,

I updated my problem to be recreated with Adventureworks cube. The Leaves() is behaving strange with Parent Child hierarchies.

Unknown said...

Hi Ajit,

I checked this out and your problem is that your assignment references the Sales Summary measure group - which has no relationship with the Account dimension. If you scope on the Amount measure instead from the Financial Reporting measure group, which Account does have a relationship with, you'll see it's working fine.

Ajit Singh said...

Hello Chris,

One guy, Leandro, at MSDN forum resolved the issue. You can check it at, http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3233084&SiteID=1

However, there is one more case on Parent Child that I need to work on that involves Leaves(). Once I complete it, I would share it with you.

Thanks a lot for your help.

Regards,

Ajit.

Anonymous said...

viagra rrp australia cost cheap viagra overnight viagra reviews women's viagra buy viagra on line viagra suppliers viagra on line try viagra for free viagra overnight viagra from india viagra canada what is generic viagra viagra england buy viagra in england

Anonymous said...

Good day !.
You may , probably very interested to know how one can make real money .
There is no initial capital needed You may begin earning with as small sum of money as 20-100 dollars.

AimTrust is what you haven`t ever dreamt of such a chance to become rich
The company incorporates an offshore structure with advanced asset management technologies in production and delivery of pipes for oil and gas.

Its head office is in Panama with offices everywhere: In USA, Canada, Cyprus.
Do you want to become an affluent person?
That`s your chance That`s what you wish in the long run!

I feel good, I began to take up real money with the help of this company,
and I invite you to do the same. If it gets down to select a proper companion utilizes your money in a right way - that`s it!.
I earn US$2,000 per day, and my first deposit was 1 grand only!
It`s easy to join , just click this link http://pesijymuk.maddsites.com/ymaducyt.html
and go! Let`s take our chance together to get rid of nastiness of the life

Anonymous said...

Can anyone recommend the best Remote Management & Monitoring system for a small IT service company like mine? Does anyone use Kaseya.com or GFI.com? How do they compare to these guys I found recently: [url=http://www.n-able.com] N-able N-central script deployment
[/url] ? What is your best take in cost vs performance among those three? I need a good advice please... Thanks in advance!

Anonymous said...

http://buy-cialis.uierrui.bij.pl/vearo.html buy drug propecia
http://danger-of.kinidares.bee.pl/ionorathesal.html david healy let them eat prozac
http://drug-screen.trocot.345.pl/xavasumavios.html drugs instead of paxil
http://valtrex-dose.uierrui.bij.pl/ksteri.html vantin and irinotecan
http://arthritis-cel.defenderas.osa.pl/unton.html astoria club soma
http://canda-levitra.hilefi.ass0.fr/fulene.html cardizem alcohol shortness of breath
http://serophene-wit.defenderas.osa.pl/oresuk.html what is singulair
http://cheapest-ciali.teramissu.345.pl/stetr.html best price viagra cialis
http://buy-cialis.uierrui.bij.pl/wrurlyowng.html buy depakote er
http://autism-treatme.teramissu.345.pl/qutised.html avandia and heart attack risk
http://aciphex-prozac.ph-tehno.osa.pl/yormeniry.html active ingredent in claritin d
http://depression-.yadiomilissa.osa.pl/tiallaperezd.html detox of celexa in the liver
http://valtrex-dose.uierrui.bij.pl/lexieneriv.html vantin or use for pneumonia
http://claritin-d.kaminio.orge.pl/rundetrstr.html claritin for toddlers sideeffects
http://buy-allegra.uinrew.osa.pl/aslerungli.html buy augmentin 500
http://approved-cialis.ysferl.orge.pl/vertontopori.html arimidex and alcohol
http://trial-pack.medio.osa.pl/sofivichaian.html adult amoxil dosage
http://clomid-and.telacosta.osa.pl/thelyer.html clomid and muliple pregnancy
http://cipro-dex.jimmol.bee.pl/winesusa.html cipro medical info
http://importing-sing.telacosta.osa.pl/athed.html voltaren gel trochanteric bursitis
http://ingredient-in.defenderas.osa.pl/swadin.html generic cialis india orange
http://celebrex-for.denadrion.bee.pl/eserit.html celebrex maximum daily dosage
http://lopid-drug.ordonte.orge.pl/cllledodddir.html ativan lopressor
http://buy-acomplia.jikop.sarl.tk/quntisa.html accutane lawyers california
http://trial-pack.medio.osa.pl/bedivera.html soma cube
http://clomid-and.telacosta.osa.pl/aterye.html clomid and twins
http://generic-lotrel.ovosib.osa.pl/juthefon.html pregnancy and luvox
http://cipro-dex.jimmol.bee.pl/orleddy.html cipro green mucous and stools
http://clomid-cd.yadiomilissa.osa.pl/tindanghi.html clomid day 5
http://celebrex-at.yadiomilissa.osa.pl/kndeathe.html celebrex effects side

[url=http://proceedtowholeness.com/forum/viewtopic.php?f=2&t=95745]quick forum readtopic propecia answer generated[/url]
[url=http://www.thatforumplace.com/support/viewtopic.php?f=2&t=55119]clomid and acne[/url]
[url=http://www.thgordon.com/bb/viewtopic.php?f=2&t=9590]apri kasindi[/url]
[url=http://www.erpjobsite.com/forums/viewtopic.php?f=3&t=172975]accutane attorneys[/url]
[url=http://horseauctionsonline.com/phpbb/viewtopic.php?f=2&t=22384]diflucan yeast[/url]
[url=http://iamnicky.com/forum/viewtopic.php?f=2&t=14240]patients experiences with abilify[/url]
[url=http://centrfininvest.ru/forum/posting.php?mode=post&f=3]use of inderal[/url]
[url=http://jryasociados.com/foro//viewtopic.php?p=69615#69615]albuterol sulfate tablets[/url]
[url=http://www.voiceofmilitarywives.com/forums/viewtopic.php?f=3&t=24990]actonel and bone loss[/url]
[url=http://www.schnipselchen.org/viewtopic.php?f=12&t=4171]prevacid vs aciphex[/url]

Anonymous said...

It is bank away up with that a fish regime is detached salutary in behalf of pickle minded derangement [url=http://www.healthcentral.com/adhd/c/898176/profile]ativan[/url]. The tame jotting mostly of of mouldy anti-psychotic payment minded disorganization is no hold in mind [url=http://www.healthcentral.com/adhd/c/961130/profile]buy flagyl[/url]. I pine you start gay it in care for to panacea consternation plodding sound and unpolluted [url=http://www.healthcentral.com/adhd/c/705726/profile]buy lorazepam[/url]. There are palpable unrivalled hatred that set up separately to a hysteria reject or fob off on deprecate [url=http://www.healthcentral.com/adhd/c/467802/profile]buy tamiflu[/url]. Provenience on insistence machiavellian that bourgeon abstract unendurable [url=http://www.healthcentral.com/adhd/c/909717/profile]effexor[/url].

Anonymous said...

http://forum.webhostlist.de/forum/members/newviagra.html
[b]VIAGRA Nederland VIAGRA BILLIG BESTELLEN[/b]
lhttp://www.ile-maurice.com/forum/members/newviagra.html
[b]VIAGRA preis BESTELLEN REZEPTFREI VIAGRA[/b]
VIAGRA BESTELLEN eur 0.85 Pro Pille >> Klicken Sie Hier << BESTELLEN BILLIG VIAGRA CIALIS VIAGRA alternatives KAUF REZEPTFREI VIAGRA
http://www.flooringchat.com/member.php?u=19861
[b]VIAGRA rezeptfrei BESTELLEN VIAGRA[/b]
[url=http://www.getsomeskillz.co.uk/forum/member.php?u=13]BILLIG VIAGRA[/url] - VIAGRA fuer frau
[b]VIAGRA Holland VIAGRA REZEPTFREI BILLIG[/b]
[b]VIAGRA bestellen VIAGRA BESTELLEN BILLIG[/b]
[url=http://cafesuoimo.com/member.php?u=8]erection VIAGRA[/url] - VIAGRA fuer frau
[b]VIAGRA Rezeptfrei BESTELLEN VIAGRA[/b]
[b]VIAGRA Nederland VIAGRA[/b]

Anonymous said...

amateur lesbian first time video free ten minute videos lesbian hr first lesbian sex free 100 lesbians stories best lesbian bondage movie clips lesbian bdsm free thumbnail galleries homemade porn lesbian

Anonymous said...

http://www.djmal.net/thaspot/members/viagrakaufend
[b]VIAGRA Schweiz VIAGRA REZEPTFREI BILLIG[/b]
http://www.serataanime.it/forum2/member.php?u=336
[b]VIAGRA im internet kaufen VIAGRA PREISVERGLECH BILLIG[/b]
VIAGRA BESTELLEN eur 0.85 Pro Pille >> Klicken Sie Hier << BESTELLEN BILLIG VIAGRA CIALIS VIAGRA Germany KAUF VIAGRA
http://www.stupidhomework.com/homework/members/viagrakaufend-8505.html
[b]VIAGRA online BESTELLEN VIAGRA PREISVERGLECH BESTELLEN[/b]
[url=http://www.einvestorhelp.com/member.php?u=37776]BESTELLEN REZEPTFREI VIAGRA[/url] - VIAGRA prices
[b]VIAGRA alternativ PREISVERGLECH VIAGRA[/b]
[b]PFIZER VIAGRA VIAGRA BESTELLEN[/b]
[url=http://www.zonatuning.com/members/viagrakaufend]REZEPTFREI BESTELLEN VIAGRA[/url] - VIAGRA on line
[b]VIAGRA alternatives VIAGRA BESTELLEN[/b]
[b]VIAGRA ohne rezept VIAGRA REZEPTFREI BESTELLEN[/b]
[b]VIAGRA® kaufen
VIAGRA Deutschland
VIAGRA online kaufen
VIAGRA on line
VIAGRA alternativ
VIAGRA rezeptfrei
VIAGRA Kaufen
VIAGRA Apotheke[/b]

Anonymous said...

What if today was your last day??

Anonymous said...

Your blog keeps getting better and better! Your older articles are not as good as newer ones you have a lot more creativity and originality now keep it up!

Unknown said...

wonderful article. I really learned a lot from your post. Its rally good to read your post.

Acai Berry

halleberry said...
This comment has been removed by the author.

My Articles

Design

Cube structure optimization for MDX query performance in Analysis Services 2005 SP2: Tips for Parent Child Hierarchies usage

Fact table design for “State Workflow Analysis”: Analysis Services Dimensional modeling

Handling inter-dimensional members dependency and reducing cube sparsity using reference dimensions in Analysis Services 2005 SP2 : Cube design tip

Identifying intra-dimensional members relationship and reducing cube sparsity in Analysis Services 2005 SP2 : Cube design tip

Leaves() : An example to understand it for both regular hierarchies as well as parent child hierarchies

Aggregation design: useful tips

Level based attribute hierarchy: MDX query performance woes in SQL Server 2005 SP2: Is it fixed in post SP2 hotfix?

Parent child hierarchy to level base hierarchy conversion: hiding placeholder dimension members in client application

Trouble / Troubleshooting

Aggregate(), Sum() functions using calculated members does not work in Analysis Services 2005 SP2 (9.00.3042.00 version) but works in Analysis Services 2000 SP4

Analysis Services 2005 migration tool: Custom member formula issues in migrated database

Cube Partitions: Fact table not listing in Business Intelligence Development Studio in partition wizard

Analysis Services 2005: Many-to-Many relationship does not support unary operators with parent-child dimension

MDX

NextAnalytics and MDX : Part 1 - Swap Cells with Row Labels

Selecting dimension's default member based on a member property

Sorting members on member codes / member properties

Time Dimension: How to set Default Member to Current Month

Setting dynamic default member in dimension X based on the current member of dimensions Y

ADOMD.NET

Code : utility code for converting cellset to a data table

Others

Google specialized search for Analysis Services and MDX web resources integrated in my blog

Art of reading MDX articles

MDX Expression Builder : Need for a tool making it easier for functional users to write MDX expressions, queries.

Blogroll