The Reality of Real-time OLAP
Microsoft® SQL Server™ 2000 Analysis Services
Summary: Discover the functionality and implementation of real-time OLAP, a key feature of SQL Server 2000 Analysis Services. (11 printed pages)
Online analytical processing (OLAP) is typically defined as the processing and analysis of shared multidimensional data. In practice, OLAP systems analyze data drawn from large, low-transaction, high-latency relational databases, such as data warehouses. The purpose of such analysis is to aggregate and organize business information into a readily accessible, easy to use multidimensional structure. OLAP systems store some or all of this aggregated information either within tables in a relational database (also known as relational OLAP, or ROLAP, storage) or in specialized data structures in multidimensional databases (also known as multidimensional OLAP, or MOLAP, storage). OLAP queries can be answered much more quickly than similar relational queries because the aggregations and computations have already been completed and the resulting derived values are readily available from a ROLAP table or MOLAP storage.
Retrieving, analyzing, and aggregating large amounts of historical data can consume extensive time and resources. OLAP systems do not usually run against online transaction processing (OLTP) or other high-transaction, low-latency databases because the time and resources required can affect the performance of the relational database. Instead, OLAP systems typically run against data warehouses, which are updated relatively infrequently, to support the requirements of most commercial and financial analysis. Most OLAP systems rely on a "snapshot" approach, periodically retrieving and aggregating data for later presentation and analysis. Because OLAP systems typically rely on stored, derived values to answer queries, the aggregation process must also reasonably match the update latency of the underlying relational data source to avoid presenting overly "stale" data.
Products that can perform aggregations quickly enough to provide multidimensional data from low-latency data sources have challenged this traditional view of OLAP in recent years. This functionality, which is referred to as real-time OLAP, is most often used in financial or industrial scenarios where multidimensional analysis of low-latency data is crucial to the organization's business intelligence requirements. However, both the definition and expectations of real-time OLAP can vary from product to product.
This white paper provides a practical definition of real-time OLAP as implemented in Microsoft SQL Server 2000 Analysis Services. It also includes technical information and guidelines for the use of this powerful but misunderstood feature.
This paper assumes that you have a basic knowledge of relational database concepts, including dimensional design, and a working knowledge of SQL Server 2000 Analysis Services.
The definition of real-time OLAP varies from product to product. In Analysis Services, real-time OLAP represents the capability to quickly retrieve, organize, aggregate and present multidimensional data for cubes and dimensions whenever the data changes in the underlying relational data source, without requiring the cube or dimension to be explicitly processed first. Because this definition differs from that used by other OLAP products, the best way to understand this definition is to study the various objects and interactions that are involved in real-time OLAP.
To use real-time OLAP, you must first create either a real-time dimension or a real-time cube in Analysis Services. The following sections describe the requirements that must first be satisfied, depending on the object.
A real-time dimension is a shared regular ROLAP dimension that supports real-time updates. The requirements for creating a real-time dimension are similar to those for creating a shared changing dimension, in that member keys must be unique for the members of the lowest level of the dimension. In addition, aggregation usage is limited to the list of available values provided for shared changing dimensions, as described in SQL Server 2000 Books Online. Unlike other changing dimensions, though, you cannot create private real-time dimensions.
For more information about the requirements of changing dimensions, see "Changing Dimensions" in SQL Server 2000 Books Online.
A real-time cube is a cube in which one or more ROLAP partitions or dimensions support real-time updates. Multiple dimensions or partitions can support real-time updates, and a real-time cube can have a mixture of dimensions or partitions that may or may not be enabled for real-time updates. Because of the complexity involved in managing such real-time cube data, the requirements for creating a real-time cube are more stringent than for a regular cube.
Remote partitions, which are used in distributed partitioned cubes, cannot be enabled for real-time updates.
To support real-time updates, a ROLAP partition must either store no aggregations, or it must use indexed views to generate and store aggregations. Using a real-time partition with no aggregations allows you to support real-time updates without requiring structural changes to a SQL Server 2000 database, but at a cost in performance. Indexed views provide a clear performance increase for most ROLAP partitions, but have some fairly stringent requirements that must be satisfied, and require the capability to make structural changes to the SQL Server 2000 database.
To store aggregations, the Analysis server creates an indexed view for each aggregation that is stored within a partition enabled for real-time updates; a single partition can have multiple indexed views associated with it. The indexed view contains aggregated measure data grouped by the levels defined as the granularity for that aggregation.
To create an indexed view for a partition, use the Storage Design Wizard. First, ensure that the partition uses ROLAP storage and is enabled for real-time updates. Then, design one or more aggregations; each aggregation requires an indexed view, and these are automatically created by the Analysis server. For more information about creating indexed views for ROLAP partitions, see "Indexed Views for ROLAP Partitions" in SQL Server 2000 Books Online.
You can also use the Storage Design Wizard to remove an existing indexed view from a partition enabled for real-time updates. To remove existing aggregations, select the Performance gain reaches option in the Storage Design Wizard and set the value to 0%. If no aggregations are required by a partition that is enabled for real-time updates, the existing indexed view is also removed.
Besides the requirements for indexed views listed in SQL Server 2000 Books Online, Analysis Services must be able to create indexed views and traces on the SQL Server 2000 data source. This means that the appropriate security credentials must be granted to the user accounts under which the SQL Server 2000 data source and the MSSQLServerOLAPService service run.
The user account that the SQL Server 2000 data source uses must have server administrator privileges to create indexed views, because Analysis Manager and the underlying Decision Support Objects (DSO) administration object model create the aggregations for a partition used by a real-time cube. If your SQL Server 2000 data source uses integrated security, the user account under which you logged in while using Analysis Manager must have server administrator privileges. If your SQL Server 2000 data source uses SQL Server authentication, the connection string for the data source must reference a user account that has server administrator privileges.
Security note When possible, use Windows Authentication.
The user account that the MSSQLServerOLAPService service uses must have server administrator privileges in order to use the notification mechanism, a specialized SQL Server 2000 trace event used to notify Analysis Services of changes to fact and dimension table data. The MSSQLServerOLAPService service uses a listener thread to subscribe to and receive trace events for a real-time cube or real-time dimension. For more information about the notification mechanism, the listener thread, and trace events, see Real-Time Change Notification.
Because two different user accounts can be involved in the creation of a real-time cube (one for the SQL Server 2000 data source and one for the MSSQLServerOLAPService service), you may be able to create indexed views for a real-time cube but be unable to receive the notification events required to support real-time updates for that cube. Make sure that both user accounts have the necessary SQL Server 2000 privileges before creating a real-time cube.
When an object that supports real-time updates is created, Analysis Services uses the trace mechanism in SQL Server 2000, along with a proprietary trace event class, to create a notification event for database tables that are used by the object. For a real-time dimension, the notification event is created for all of the tables that are used by the dimension. For a real-time cube, however, the notification event is created only for the fact tables that are used by partitions that support real-time updates for the cube. (The indexed views used by partitions in real-time cubes are used only for retrieval and aggregation purposes.) A listener thread, running on Analysis Services, subscribes to and receives these notification events.
The notification event is raised on a per-transaction, not per-operation, basis. For example, a single transaction can contain thousands of SQL UPDATE operations to a large fact table, but only one notification event is raised when the transaction is committed. If notification events are enabled for a given table during a database transaction (for example, when a real-time dimension is created on a dimension table receiving a periodic update), the transaction may not raise a notification event. However, this is an extremely rare occurrence, and you can prevent it by enabling real-time updates during periods of low relational database activity.
The per-transaction approach for the notification mechanism is important because you can potentially improve real-time performance by batching multiple SQL INSERT, UPDATE and DELETE operations into a single transaction whenever possible. Every time a notification event is received, objects in the server cache are invalidated. The effect of this invalidation becomes important when the server cache and client cache attempt to synchronize. For more information, see Client Cache Management.
Server Cache Management
When the Analysis Services listener thread receives a notification event for a database table, the thread instructs the Analysis server to invalidate the server cache for any real-time object that depends on the database table. Invalidation requires the Analysis server to update the meta data for the real-time object the next time it receives a request from a client application for data or meta data for that object. If a request for data is received, the data for the object is loaded on demand only after the meta data for the object has been retrieved and organized.
Queries and requests
Real-time OLAP depends in part on the efficiency and speed with which requests are processed. Requests for data and meta data trigger updates on the server cache and guide the server cache as to the depth and width of data to be cached. However, queries and requests are not synonymous—a request is not necessarily a query. In Analysis Services, the term "query" refers to a Multidimensional Expressions (MDX) statement issued by a client application. Microsoft PivotTable® Service, the OLE DB provider used to access Analysis Services, breaks down an MDX statement into one or more separate requests for data or meta data. If PivotTable Service can satisfy a request through the client cache, the request is not sent to the Analysis server. If all of the requests for a query can be satisfied in the same manner, no communication with the Analysis server is required.
However, PivotTable Service can send requests for other reasons. Requests can be sent in response to drillup or drilldown requests made by a client application, or through the synchronization mechanism used by PivotTable Service. As such, communication between PivotTable Service and the Analysis server can be somewhat difficult to predict or control. For more information about how the synchronization mechanism works, see Client Cache Management.
Real-time dimensions and server caching
As described earlier, real-time dimensions are shared regular ROLAP dimensions that are enabled for real-time updates. ROLAP dimensions are handled differently from other dimensions in Analysis Services. MOLAP dimension members and member properties are fully cached when the MSSQLServerOLAPService service is started, but ROLAP dimensions are cached on demand from the relational database in a two-step process:
- Starting at the top level of the ROLAP dimension, a number of members are retrieved to fill in the structure; the number is equivalent to the large level threshold for the Analysis server.
- Dimension subtrees are constructed on demand as requests for data or meta data are received.
Processing for real-time dimensions is also different than it is for other regular ROLAP dimensions. A regular ROLAP dimension is considered to be a changing dimension by Analysis Services. Unlike other dimensions, rebuilding the structure of a changing dimension is necessary only if the following changes are made:
- You add, move, rename, or delete either the top or bottom level.
- You add, move, rename, or delete a level that contains member groups.
Otherwise, a changing dimension can be incrementally updated. The benefit of an incremental update is that it does not require dependent objects such as cubes to be reprocessed. Real-time dimensions function similarly, in that if you do not change the structure of a real-time dimension, it does not require rebuilding. However, because a real-time dimension is cached on request, an incremental update isn't necessary. The cache for the real-time dimension is invalidated and re-cached on demand as requests for data and meta data are received. If the structure of a real-time dimension is altered in the ways mentioned earlier, the real-time dimension must be rebuilt.
Real-time dimensions tend to be more performance intensive; because multiple cubes can depend on a real-time dimension, their cached results can become invalidated when the underlying dimension table changes. An update to a dimension table for a real-time dimension can have a much larger effect on the server cache than an equivalent update to a fact table for a real-time cube. As such, real-time dimensions are more "expensive" in terms of resources and performance, and should therefore be used sparingly.
Real-time cubes and server caching
To the Analysis server, real-time cubes can have one or two levels of caching. If a real-time cube does not use real-time dimensions, the Analysis server retrieves and aggregates data for the real-time cube on demand, and caches the results (both data and meta data) in the query results cache. If a real-time cube uses a real-time dimension, the necessary subtrees for the real-time dimension must first be cached to provide structural information for the real-time cube. The real-time cube then retrieves data from the relational database and aggregates it within the newly cached structure.
Real-time cubes can be somewhat less performance intensive than real-time dimensions, because dimension data does not typically require reconstruction when an update is made to a fact table. If a cube uses a real-time dimension, and the real-time dimension is invalidated, the relevant dimension subtrees cached for the cube are also invalidated and must be reloaded and reconstructed before the cube can satisfy a request for data.
PivotTable Service uses a synchronization technique involving version information to determine which objects require reloading in its client cache. Synchronization occurs either when PivotTable Service issues a request for data or meta data to the Analysis server, or when auto synchronization is triggered. Management of this synchronization process becomes important when a real-time object is invalidated in the server cache.
As discussed earlier, MDX queries are not synonymous with requests for data or meta data. On each request for data or meta data, PivotTable Service validates the version information in the client cache for the object or objects referenced in the request against the version information in the server cache. If the versions in the client cache and server cache match, no request for data is made and PivotTable Service supplies the necessary information directly from the client cache. If the versions do not match, however, the referenced objects in the client cache are invalidated.
PivotTable Service also attempts to synchronize the client cache at regular intervals, even if an MDX query has not been issued. At an interval determined by the Auto Synch Period connection string property, the background thread used by PivotTable Service validates all of the objects in the client cache against the corresponding objects in the server cache. If the version information does not match for a specific object, or if the server cache has been invalidated for a specific object, that object is invalidated in the client cache.
One difference between the server cache and the client cache is that, to PivotTable Service, only cubes are cached. If a real-time dimension is invalidated in the server cache, the dimension subtrees used by cubes that depend on that real-time dimension are invalidated in the client cache. If a real-time cube is invalidated in the server cache, only the data and meta data for the real-time cube is invalidated in the client cache.
When an object is invalidated in the client cache, PivotTable Service flushes the data for that object and attempts to reconstruct the object meta data with the server cache. Note that at this point, PivotTable Service does not reconstruct the data; it waits for a request for data before attempting to repopulate the object, and then only to satisfy the request for data.
The meta data for the dimension or cube is invalidated, even if a change is made to just a single record in the relational database. With the object invalidated, any queries executed against it require the Analysis server to re-query the relational database and rebuild the data and meta data for the object in the server cache. During that time, PivotTable Service may send additional requests for data or meta data. The way in which these additional requests are resolved depends on the object being referenced. If a request for data or meta data is issued for a real-time dimension, the request is completed as the information becomes available. If a request for data is issued for a real-time cube, the state of the information influences the success of the request. If the request is executed at the time any of the objects it referenced became invalidated, the request is re-executed. If the request was open at the time any of the objects it referenced became invalidated, it falls out of context and any attempt to reference the data managed by the out-of-context request fails.
The keys to using real-time OLAP successfully are awareness and timing. You need to be aware of all of the steps that must occur successfully for real-time OLAP and monitor their performance accordingly. If the timing is significantly unbalanced during any or all of these steps, performance can suffer. The primary areas of concern when using real-time OLAP are:
- The frequency of changes made to fact and dimension tables used by real-time cubes and real-time dimensions.
If transactions occur on fact or dimension tables too quickly, the Analysis server is locked in a struggle with the database to keep its server cache updated. If objects on the server cache are invalidated too frequently, this struggle is pushed down to PivotTable Service as it tries to synchronize the client cache with the rapidly changing, frequently invalidated server cache. This struggle, referred to as "cache thrash," is represented to the client application in the form of errors when requests for data are rendered out of context too often.
- The latency of queries made by the Analysis server when retrieving data from the relational database.
If too much time is required to retrieve and organize data from the relational database, a PivotTable Service request for data or meta data may be rendered out of context. The request is then re-executed after the Analysis server aggregates and caches the data.
- The frequency of requests for data or meta data made by PivotTable Service when synchronizing its client cache with the Analysis server.
If PivotTable Service requests are too frequent, or if the value of the Auto Synch Period connection string property is too low, PivotTable Service can waste time and resources attempting to resynchronize its client cache. Reconstructing objects in the server cache takes time, and issuing requests too often means that some of the requests are rendered out of context during the time required to reconstruct the referenced objects, possibly causing errors in client applications.
- The differences between fact table updates and dimension table updates.
Dimension table updates are more expensive, in terms of time and resources, than fact table updates. A change to a fact table does not require the dimension subtrees to be reloaded and reconstructed, but a change to a dimension table does, and this can affect many other cubes.
The following list details steps you can take to mitigate the concerns mentioned earlier and improve the performance of real-time OLAP:
- Batch transactions involving the fact and dimension tables used for real-time OLAP whenever possible.
Because the notification mechanism works on a per-transaction basis, batching operations when appropriate can minimize the number of unnecessary notification events that the Analysis server receives. For example, if your application requires that users see changes every 10 seconds for a real-time cube, but your database receives 50 updates to the underlying fact table every second, you can improve performance by batching updates into a single transaction and entering them every 5 to 10 seconds. The Analysis server then receives one or two notification events every 10 seconds, rather than 500.
- Use real-time dimensions only when absolutely necessary.
Real-time dimensions consume time and resources. You should consider whether a dimension truly requires real-time updates or if it just requires frequent incremental updates. If the latency period for dimension changes can be measured in hours or more, use a changing ROLAP dimension instead of a real-time dimension. If you can satisfy your business requirements using a real-time cube instead of a real-time dimension, choose the real-time cube.
- Use deep hierarchies and adjust your large level threshold when constructing real-time dimensions.
When a real-time dimension becomes invalidated, members are initially loaded according to the large level threshold. The more frequently your real-time dimensions must be updated, the lower this threshold setting should be, because this number of members is loaded every time a real-time dimension is invalidated. After these members are loaded, dimension subtrees are loaded on demand based on request requirements. The deeper the hierarchy, the fewer the members that must be loaded to satisfy such requests (depending on the requirements of the requests themselves). By balancing both initial and on-demand loading, you can optimize the loading of real-time dimensions based on both the usage of the dimension and the frequency of updates to the underlying dimension table.
For example, suppose you have an OrganizationMember dimension with only three levels: an All level, a Location level, and an OrganizationMember ID level (which serves as the leaf level). The Analysis server has a large level threshold of 5,000 members. If you have only three locations but have 300,000 members at the leaf level, you must always load at least 100,000 members at a time to load a single subtree for this dimension. The large level threshold prevents these members from loading initially; instead, they are loaded on demand. This strategy provides a quick initial load at the expense of long invalidation periods when the dimension table is changed. If you add a new level named PostalCode between the Location level and OrganizationMember ID level, and this new level has 1,000 members with an even distribution of leaf members, the initial load time takes slightly longer because this level can be loaded initially. Each dimension subtree now only requires 300 members to be loaded to fulfill requests for dimension data, greatly improving performance when data is loaded on demand.
- Partition real-time cubes according to real-time requirements.
Like any other cube, a real-time cube can contain multiple partitions, and you can determine which partitions are enabled for real-time updates. Design your real-time cube so that real-time updates are required only for a single partition, or so that multiple partitions that require real-time updates are as small as possible. The goal here is to reduce the amount of data that must be reloaded whenever a fact table is updated.
For example, if your real-time cube shows changes for both current and previous periods, such as a cube that monitors the current and past performance of a manufacturing line on a daily or weekly basis, partition your cube by period, and enable for real-time updates only for the partition that represents the current period.
Real-time dimensions are considered changing dimensions, so a real-time dimension cannot be used to set the data slice for a partition.
- Adjust the PivotTable Service Auto Synch Period connection string property as necessary to match the frequency and latency of real-time objects.
The default value for the Auto Synch Period connection string property is 10 seconds, but it can be set as low as 250 milliseconds. However, the following factors can have an impact on the effectiveness of the property setting:
- The frequency of database changes
- The latency of server cache updates
- The latency of client cache synchronization
Setting this property too low, in an attempt to more quickly synchronize your client cache for real-time objects, can cause PivotTable Service to inundate the server with requests for data and meta data. To your client application, the result of setting this property too low can appear as error messages, "#ERR" cell values, or an extended amount of time to resolve queries. For example, if the frequency of changes to fact and dimension tables used by real-time dimensions and cubes averages about 10 seconds, the latency to update these objects in the server cache is about 2 seconds, and it takes about 2 seconds to reconcile the client cache against the server cache, setting your Auto Synch Period property to 250 milliseconds means that PivotTable Service attempts to synchronize eight times as fast as it can resolve such synchronization, and forty times faster than the actual frequency of database changes.
Also, it can be challenging to accurately predict when PivotTable Service will attempt to synchronize its client cache with the server cache, because synchronization occurs not just at the interval specified by this property but also because of direct interaction with a client application, such as when a user drills down on a real-time dimension or sends a new MDX query.
The key to using this property successfully is observation and experimentation. As a general guideline, 10 seconds is the minimum latency period for real-time OLAP; therefore, the Auto Synch Period property should probably not be changed for most real-time OLAP applications.
Real-time OLAP is an effective way to provide access to low-latency aggregated data in Analysis Services. The performance and expectations of real-time OLAP can vary based on a number of factors, including networking, database and Analysis server usage, and the design of your cubes and dimensions. The benefits of real-time OLAP become evident, however, when these factors are accounted for and mitigated through design and performance tuning.
SQL Server Books Online contains more information about Analysis Services. For additional information, see the following resources: