SQL Server Performance

MSAS 2005 Aggregation Writeback and Performance

Discussion in 'SQL Server 2005 Analysis Services' started by Budja, Dec 6, 2007.

  1. Budja New Member

    Hello. I have a newbie question regarding MSAS 2005. We have done some aggregations on the cube and we allow writeback to a relational table. I think this is the default method of MSAS. My question is if writeback is allowed, does that blow away any aggregations that were processed the night before? Will aggregations become useless on that particular dimension/fact table intersection if the report has to look at the wrtieback table and the cube?
    We are trying to improve performance and the route we are taking is building more and more aggregations. I am thinking this become useless because of the writeback table.
    Cube Processing time is not an issue.
    Thank you for any replies.
  2. ranjitjain New Member

    Hi,
    As per my experience, Process update usually drops the aggregations which are flexible so needs to be backed up by Process index.
    In case of dimension writeback, it requires incremental processing to adhere the changes in dimension structure which should not drop any aggregation of any type which is what I have read before. Still you can test the same before deploying on production.
    Full process the cube with enabled dimension writeback and add few changes and finally run only process index, if it completes in a minute means aggregation are intact.
  3. Budja New Member

    Thank you. We just use the WriteBack table for fact data. The problem is information is constantly being entered into the WriteBack and the cube is just processed twice a day. Are you saying that aggregations on the cube will not be used if there is Writeback information with regards to that dimension?
    When is the process update and process index done? Is it done everything new data is entered into the writeback table? That doesn't seem reasonable.
    I understand what you are saying about watching the process cube time. If it takes longer, that means the aggregations are rebuilt.
    Thank you for your response.
  4. ranjitjain New Member

    Hi,
    I will rephrase above sentences again, SSAS has got many types of cube processing methods and process update, process index, process incremental, full process are few of them and each one has got its own significance
    When you process cube or dimension with process update method, then SSAS drops the aggregations related to that dimension and to re-create them, you need to manually process the cube with process index option.
    Incase of writeback, SSAS requires incremental process, in other words it will not drop any aggregations and hence can use all the available aggregations.
    Process index generally creates aggregations which are dropeed and not processed, if all is well then it takes minimal time around 1-5 minutes based on aggregations.
    Read about all of them in BOL for more.
  5. Budja New Member

    Thank you for your response. Our design of the system allows two cube processing and this is based on not having the cube offline. Cube processing takes about 5-6 mins so it isn't long but we don't want it down since we have users accessing it at any time of the day. For your comment regarding SSAS requires incremental process, do that mean the user will see the data in the writeback table and aggregations in the cube. If the writeback table keeps growing, do we need to do more cube processing? Is SSAS smart enough to combine the aggregations and the writeback data? Thank you again.
    [quote user="ranjitjain"]
    Hi,
    I will rephrase above sentences again, SSAS has got many types of cube processing methods and process update, process index, process incremental, full process are few of them and each one has got its own significance
    When you process cube or dimension with process update method, then SSAS drops the aggregations related to that dimension and to re-create them, you need to manually process the cube with process index option.
    Incase of writeback, SSAS requires incremental process, in other words it will not drop any aggregations and hence can use all the available aggregations.
    Process index generally creates aggregations which are dropeed and not processed, if all is well then it takes minimal time around 1-5 minutes based on aggregations.
    Read about all of them in BOL for more.
    [/quote]
  6. satya Moderator

    ONe thing I would like to stress is the structure for writeback tables constructed by previous versions of Analysis Services has been updated in SQL 2005 Analysis Services. Data from writeback tables constructed by previous versions of Analysis Services cannot be migrated into the new writeback table structure.
    Also SSAS uses caches. A cache stores the results of calculations that occur when you perform the writeback operation. When you roll back the transaction, SQL Server does not roll back the cache. Therefore, when you query the data, SQL Server retrieves incorrect data from the cache.
  7. Budja New Member

    Can you please explain the role of caching in the writeback? As I know it, when data is written to the Writeback table, it is immediately visible to the users. How is the data cached or rolled back? Maybe its the way our application works. We are using Clarity 6 from ClaritySystems.

Share This Page