none
SSAS Aggregation Design

    Question

  • Hi,

    We have a SSAS production environment where we want to re-design our aggregations because of code changes through years and upgrade from SSAS 2005 to SSAS 2008.

    Please help me with the best strategy.

    Thanks.

    Sunny

    Monday, December 27, 2010 11:45 AM

All replies

  • Hi,

    If you are not in hurry and have plethora amount of time you can go for "Usage Based Optimzation" technique.

    In this approach, system logs the data based on client queries and then aggregation could be designed based on the recorded log. This is good approach to create relevant aggregations.

    http://sqlcat.com/technicalnotes/archive/2008/11/18/reintroducing-usage-based-optimization-in-sql-server-2008-analysis-services.aspx

    Regards,
    Santosh Joshi


    Josh
    Monday, December 27, 2010 12:14 PM
  • Hi,

    I am unable to understand the use of "Usage Based Optimization". This wizard identifies queries that will be optimized but with the 'Next' click it appears similar to "Design Aggregation" wizard.

    Now it left on us to specify performance gain, aggregation storage etc. In that case how usage based optimzation compares to aggregations designed through "Design Aggregation" wizard.


    Regards,

    Sunny

    Tuesday, December 28, 2010 9:40 AM
  • Hi,

    This is how it works -

    Step 1: Enable/ Create Query log which will collect all the queries which are fired against your cube. This log will be input to your Aggregation Design.

    Step 2: Fire queries on the cube for which design is required by browsing the data of the cube or the way your client will browse it. This will populate query logs in your log table created in step 1.

    Step 3: After this, you are ready to design your aggregation. Open BIDS and the cube for editing it. Click "Aggregation" tab. Right click any measure group where you want to design the aggregation, select Usage Based Optimization as an option, a wizard will open up which will guide you for the remaining steps. The wizard uses the query log statistics which are generated in step 2 for the aggregation design.

    Now, to answer your question, if you observe the wizard you will see there is no screen for "Review Aggregation Usage", i.e., there is no need to set aggregation parameters manually; UsageBasedOptimization does it for you and it's more realistic because it's based on the query log which is generated by real time cube browsing.

    HTH.

    Regards,
    Santosh

    Tuesday, December 28, 2010 2:32 PM
  • Hi Sunny,

    I agree with Santosh. Generally, we might have some aggregations designed, but the chances are that despite our best efforts many of them will not prove to be useful. To a certain extent we might be able to pick out these aggregations by browsing through them; really, though, we need to know what queries our users are going to run before we can build aggregations to make them run faster. This is where usage-based optimisation comes in:

    http://www.packtpub.com/article/query-performance-tuning-microsoft-analysis-services-part1?utm_source=sl_expertcube_abr4_0709&utm_medium=content&utm_campaign=sanjiv

     

    Hope this helps,

    Raymond


    Raymond Li - MSFT
    Wednesday, December 29, 2010 6:27 AM
    Moderator
  • Hi,

    Thanks for your help.

    I want to re-design my existing Aggregations on Production server.

    Apart from "Usage Based Optimization" option ,I come to know it can be done through BIDS helper. I can have trace on the AS server also available.

    Please help me how I can use BIDS helper on the cube already in Production.

    Regards,

    Sunny

     

    Wednesday, December 29, 2010 1:22 PM
  • Hi,

    Following URL could be help ful for BIDS Helper usage -

    http://bidshelper.codeplex.com/wikipage?title=Aggregation%20Manager&referringTitle=Home&ProjectName=bidshelper

    Also, SSAS document located at the below URL is worth going for understanding aggregations -

    http://download.microsoft.com/download/8/5/e/85eea4fa-b3bb-4426-97d0-7f7151b2011c/SSAS2005PerfGuide.doc

    HTH,

    Regards,
    Santosh Joshi

    Wednesday, December 29, 2010 1:52 PM
  • Hi Santosh,

    The URL was really helpful in understanding aggregations. Thanks for this.

    Please help me with your inputs on how BIDS helper can be helpful in designing aggregations. On the codeplex site I can see so many sample applications and I do not think these can be installed on production server. As I have already said, I can have trace of user queries available. Can I use those trace files with BIDS helper on development environment to optimise my aggregations.

    Regards,

    Sunny

    Friday, December 31, 2010 7:12 AM
  • Hi Sunny,

    Please see below URL, where a similar kind of thread was discussed -

    http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/4ead2b25-110b-41ee-8e87-6d820318d57c

    Hope it will solve your problem.

    Regards,
    Santosh

    Friday, December 31, 2010 8:45 AM
  • Hi Santosh,

    I have installed BIDS helper through BIDS helper 2005 set up from codeplex. After installation I found two files BIDSHelper.AddIn & BIDSHelper.dll in C:\Program Files\BIDS Helper.

    Please let me know what next to do to use this tool. I am unable to see anything related to BIDS helper in SSAS projects.

    Many Thanks.

    Sunny

    Monday, January 03, 2011 1:10 PM
  • Hi Sunny,

    I am not BIDS Helper expert, but following are the two links which I'd have used for my aggregation design using helper -

    http://bidshelper.codeplex.com/wikipage?title=Aggregation%20Manager&referringTitle=Home&ProjectName=bidshelper

    http://bidshelper.codeplex.com/wikipage?title=Test%20Aggregation%20Performance

    Regards,
    Santosh

    Monday, January 03, 2011 4:34 PM
  • Hi Sunny,

    You have to install BIDS Helper on either your development machine or server whereever you can open your SSAS solution in BIDS.

    Once you installed it, open your BIDS solution and go to the cube where you want to perform aggregations.

    In the cube, go to patitions tab and you can see 3 extra add-ons installed through BIDS helper. First one is Update all estimated count, this will basically update dimension member count across all partitions. THis is not must but highly recommended before you start your basic aggregation design. Second one is Edit aggregations. THis is the one you need to use to manually force aggregations to your partitions. In edit aggregation, drill down partition name till you see your basic aggregation design name, right click on it and say edit. Now you can see all your aggregations which you have deisgned before. On the right hand side, you can also see dimensions and it's attribute. Now you can manually edit, add or remove any aggregation you want from this design. e.g. if you want to improve a distinct count measure performance, you can create your own aggregation by selecting lowest level atribute members from required dimension. Once you are done ,save the aggregation design. Third option you can see is deploy aggregation. If you have set correct server name in deployment property of your solution. CLicking on this button will deploy your recent modified aggregation design on your server and you even won't need to deploy the cube seperately for these aggregation changes. but you still have to process cube on your server to see performance improvements.

    There are also some add-ons added by BIDS helper install in Calculations tab. Here also you can deploy your calculations through BIDS Helper add-ons without deploying cube.

    I hope this helps. Also Links given by Santosh are highly descriptive. You can refere them too.

    Regards,

    Amey

    Monday, January 03, 2011 9:20 PM
  • Hi Amey, Santosh,

    Now I can see BIDS helper features. Thanks.

    I want to utilize BIDS helper to re-design the aggregations on our Production Server. It is required because of code changes over years. There is no BIDS helper installed on Production.

    Using "Usage Based Optimisation" also seems to be difficult as there are many restrictions on Production to configure it. What I can have is trace of user queries on Production available.

    Please suggest me how i can utilize trace and BIDS helper to redesign aggregations.

    Regards,

    Sunny

    Tuesday, January 04, 2011 6:56 AM
  • Hi Sunny,

    The link shared with you earlier would be of some help, below see below -

    http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/4ead2b25-110b-41ee-8e87-6d820318d57c

    Also, can you start exploring things by your own and post the issues which you are facing? You probably can close this thread now and start new threads for each query.

    Regards,
    Santosh

    Tuesday, January 04, 2011 7:25 AM
  • Hi Sunny - DO you have query log set up for analysis services on your production box? If yes, is it possible to get a current copy of the query log table to your DEV box from PROD box? Once you get that, you can set up usage based aggregation on your DEV solution and then go and deploy on your PROD machine.

    If you can't do so, at least try to get what questions your busines users are asking to your cube. ( it's the same what query log table has but now not in 0,1 format but which dimensions and facts combinations they are using most). you can figure that our by running trace against the cube.

    Once you have those combinations, use BIDS helper to manually design aggregation at those intersections. For example, if user is using month attribute against customer count then in BIDS helper, design explicit aggregation for month ( this is kind of stupid example, but i hope you get an idea. Most of times it's complex than this.) Also validate or remove duplicate aggregation using bottom buttons in aggregation desgin in BIDS helper.

    Hope this helps.

    Amey

    Tuesday, January 04, 2011 5:37 PM
  • Hi Amey,

    Thanks for this. I will post any query I have during its implementation.

    Regards,

    Sunny

    Thursday, January 06, 2011 8:05 AM