Home > SQL Analysis Services > Processing SSAS

Processing SSAS

The below are the notes I took when I read the books and Internet posts before. I just organize them into one paragraph and remind me of different methods.

There are several options for processing SSAS objects

  • BIDS
    • BIDS can track only metadata changes. E.g. it cannot detect changes made to data source
  • SSMS
    • Connect to the SSAS server, specify one or more objects and right-click to process
    • You can generate the XMLA script for processing
  • SSIS – SSIS Analysis Services Processing Task
  • Proactive Caching
    • Silence interval – Defines the minimum period to wait for a quiet time in the relational database.
      • Predefined Setting: -1 (infinite) Ignore database notifications
    • Silence override interval – Defines the threshold period after which the server will start rebuilding the cache, if no quiet period occurs, that is, if there are perpetual data changes.
      • Predefined Setting: -1 (infinite) No override period
    • Latency – Defines the lifetime of the old MOLAP cache during which it is still used to respond to queries; data retrieved by those queries is stale.
      • Predefined Setting: -1 (infinite) Never revert to Relational Online Analytical Processing (ROLAP)
    • Rebuild Interval – Schedules the new cache to be build automatically, whether there is data activity or not.
      • Predefined Setting: -1 (infinite) No periodic update
    • Bring online immediately – When set to Immediate (On), the server satisfies queries from ROLAP while the cache is being rebuilt; when set to OnCacheComplete(Off), the server will never revert to ROLAP but will be in infinite latency.
    • Enable ROLAP aggregations – Attempts to create materialized SQL views for aggregations while queries are answered in ROLAP mode.
    • Apply settings to dimensions – Applies storage mode and proactive caching settings to associated dimensions.
    • If query performance is the highest priority, you shouldn’t allow proactive caching to revert to ROLAP al tall. In the Storage Options dialog box, disable the Latency setting (the Not Enabled item in the drop-down list) and clear the Bring Online Immediately checkbox. The result of this combination will be that the server will satisfy the queries from the old MOLAP cache until the new cache is ready.
    • Notification Options – three options
      • SQL Server Notifications – it uses the SQL Server trace events, which are raised when data is changed.

        Ø  Requires SSAS to connect to the data source with administrator rights. (When setting up the data source connection, you have to specify credentials of an account that has administrator rights to the data source.)

        Ø  SQL server notification doesn’t support for even queuing. For example, if Analysis Services is restarted while the data is changed in the relational database, proactive caching will not pick up the data change events.

        Ø  SQL Server notifications always result in Full Process for partitions and Process Update for dimensions. One scenario in which this might be a problem is when only new data will be added to a partition or dimension. In this case, Process Add would be a more efficient option. (alternatively, you can consider scheduled polling notifications.)

            • Client-Initiated Notifications
              • Use an external application to notify the server when the data is changed.
                • Send a NotifyTableChange XMLA command page 384
            • Scheduled Polling Notifications
              • Most commonly used
              • Assumes that the relational table has a column that indicates an update event
                • e.g. a column called ModifiedDate column.

                  • select max(modifieddate) as ModifiedDate from SrcTable

                • the query must be singleton query (1 row, 1 column) . Each query tracks data changes from a single table. If you have a measure group that spans multiple tables, based on a named query or SQL view, you need to add as many polling queries to the grid as there are tracked tables. Often, data is only added, never changed or deleted. In this case, you can improve the proactive caching performance by incrementally updating the cache. To do so, specify an additional processing query next to the polling query.

          • Categories: SQL Analysis Services
            1. No comments yet.
            1. No trackbacks yet.

            Leave a Reply

            Fill in your details below or click an icon to log in:

            WordPress.com Logo

            You are commenting using your WordPress.com account. Log Out /  Change )

            Google photo

            You are commenting using your Google account. Log Out /  Change )

            Twitter picture

            You are commenting using your Twitter account. Log Out /  Change )

            Facebook photo

            You are commenting using your Facebook account. Log Out /  Change )

            Connecting to %s

            %d bloggers like this: