tag:blogger.com,1999:blog-159342632011-04-21T15:39:32.705-07:00Mark Hillhttp://www.blogger.com/profile/16695786802090505692noreply@blogger.comBlogger21100tag:blogger.com,1999:blog-15934263.post-1138996470298752892006-02-03T11:52:00.000-08:002006-02-03T11:54:30.316-08:00I have been very quiet for a good reason, I have been working on a project which involves multi terabyte data sizes based on SQL Server 2005 and MSAS. Obviously there is a lot that I cannot share in this forum however there are some learning’s which I can share which should make your deployments more scalable and trouble free.<br /><br />The first thing I am going to do however is to recommend a book, this in my opinion is THE best book for Analysis Services I have seen to date (damm I wish I had written it).<br /><br /><a href="http://www.amazon.co.uk/exec/obidos/ASIN/0976635305/qid=1138996271/sr=8-1/ref=sr_8_xs_ap_i1_xgl/202-5015600-1712620">Anyway its called Applied Analysis Services by Teo Lachev</a><br /><br />Over the next few weeks i will be blogging on some of my findings so stay tuned.<div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/15934263-113899647029875289?l=markiehillas.blogspot.com' alt='' /></div>Mark Hillhttp://www.blogger.com/profile/16695786802090505692noreply@blogger.comtag:blogger.com,1999:blog-15934263.post-1125357188083724752005-08-29T16:06:00.000-07:002005-08-29T16:15:04.960-07:00Aggregations work in a completely different way than they did in MSAS 2000. Don't expect them to just happen when you click the design wizard like they did before.<br /><br />In fact if you design a cube and then click the design wizard and set say 30% aggregations, some confusing things happen.<br /><br />1) When you process the cube it will take much longer and generate loads of temp files for holding the aggregation work.<br />2) the space needed to process the cube will grow in line with what you would expect for building aggregations.<br /><br />For instance my cube increases from needing around 750mb of space to process, to over 2GB.<br /><br />However when its all finished the cube data directory stays the same size.... NO AGGREGATIONS.<br /><br />It was Andrew Sadler from Proclarity and Stuart Ozer (MS) who helped me work out what is going on here, sorry for being so dumb :)<br /><br />in fact its blindingly obvious when you know what to look for.<br /><br />The clue is in the screen which pops up and shows you the counts for your dimensions and fact table before you enter the design wizard.<br /><br />You will notice that ONLY the key col of the hierarchy has a count and there are zeros for all the levels of the hierarchy.<br /><br />In essence it would appear that MSAS does not know how to reach the levels to count them for aggregation, so you have to tell it what to do.<br /><br />You can solve this by creating a attribute relationship between each level which has a path that leads to the key, confused ?<br /><br />Look at the screenshot below, this is the geographical dimension from the adventureworksDM, in this screenshot there is no path defined<br /><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="http://markiehill.smugmug.com/photos/33994439-M.jpg"><img style="cursor: pointer; width: 500px;" src="http://markiehill.smugmug.com/photos/33994439-M.jpg" alt="" border="0" /></a><br /><br /><br />Therefore when you click on the wizard and look at the rowcounts you get a load of 0's<br /><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="http://markiehill.smugmug.com/photos/33994461-M.jpg"><img style="cursor: pointer; width: 500px;" src="http://markiehill.smugmug.com/photos/33994461-M.jpg" alt="" border="0" /></a><br /><br /><br />Now you create the correct attribute relationships like so ...<br /><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="http://markiehill.smugmug.com/photos/33994470-M.jpg"><img style="cursor: pointer; width: 500px;" src="http://markiehill.smugmug.com/photos/33994470-M.jpg" alt="" border="0" /></a><br /><br />And check the rowcounts<br /><br />Now you create the correct attribute relationships like so ...<br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="http://markiehill.smugmug.com/photos/33994479-M.jpg"><img style="cursor: pointer; width: 500px;" src="http://markiehill.smugmug.com/photos/33994479-M.jpg" alt="" border="0" /></a><br /><br />I hope this helps somebody because i was banging my head for hours with this just never quite getting it to work.<div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/15934263-112535718808372475?l=markiehillas.blogspot.com' alt='' /></div>Mark Hillhttp://www.blogger.com/profile/16695786802090505692noreply@blogger.com