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).
2. In cube, added the measure 'BudAmount' alongside of 'Amount'
3. In mdx script, added the below script:
SCOPE (Leaves(),[Measures].[BudAmount]);
this = [Measures].[Amount];
END SCOPE;
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.
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.
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.
2. Under 'Sales Summary' measure group, add the 'SalesAmount1' measure. (look for any white space in the name).
3. In each of Sales Summary partition, add the new 'SalesAmount1' column:
4. In the MDX script, define the calculation:
SCOPE (Leaves(),[MEASURES].[SalesAmount1]);
this = [Measures].[Sales Amount];
END SCOPE;
5. Check if the SalesAmount1 measure is same as [Sales Amount]. Yes, it is same.
SELECTNON EMPTY
[Product].[Product Categories].[Category].MEMBERS
ON ROWS ,
{ [Measures].[Sales Amount], [Measures].[SalesAmount1]
}
ON COLUMNS
FROM [Adventure Works]
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.
19 comments:
What if you move your SCOPE statement before CALCULATE statement?
V.M.
Did that, no effect.
Is that a parent/child dimension in the query? That could be messing up the way that the Scope works if it is.
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.
Hello Chris,
I updated my problem to be recreated with Adventureworks cube. The Leaves() is behaving strange with Parent Child hierarchies.
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.
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.
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
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
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!
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]
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].
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]
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
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]
What if today was your last day??
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!
wonderful article. I really learned a lot from your post. Its rally good to read your post.
Acai Berry
Post a Comment