Expert Cube Development with SSAS Multidimensional Models (2014)
Chapter 8. Query Performance Tuning
One of the main reasons for building Analysis Services cubes as part of a BI solution is because it should mean you get better query performance than if you were querying your relational database directly. While it's certainly the case that Analysis Services is very fast, it would be naive to think that all of our queries, however complex, will return in seconds without any tuning being necessary. This chapter will describe the steps you'll need to go through in order to ensure your cube is as responsive as possible, covering the following topics:
· Partitioning measure groups
· Building aggregations
· Tuning MDX calculations and queries
· Using caching to your advantage
· Understanding Scale-out options
Note
This chapter should be read in conjunction with the very detailed white paper, "The Analysis Services 2008 R2 Performance Guide", which can be downloaded from http://tinyurl.com/ssasR2perf.
Understanding how Analysis Services processes queries
Before we start to discuss how to improve query performance, we need to understand what happens inside Analysis Services when a query is run. The two major parts of the Analysis Services engine are:
· The Formula Engine: This part processes MDX queries, works out what data is needed to answer them, requests that data from the Storage Engine, and then performs all calculations needed for the query.
· The Storage Engine: This part handles all the reading and writing of data, for example, during cube processing and fetching all the data that the Formula Engine requests when a query is run.
When you run an MDX query, then, that query goes first to the Formula Engine, then to the Storage Engine, and then back to the Formula Engine before the results are returned back to you. There are numerous opportunities for performance tuning at all stages of this process, as we'll see.
Performance tuning methodology
When tuning performance there are certain steps you should follow to allow you to measure the effect of any changes you make to your cube, its calculations or the query you're running:
· Always test your queries in an environment that is identical to your production environment, wherever possible. Otherwise, ensure that the size of the cube and the server hardware you're running on is at least comparable, and running the same build of Analysis Services.
· Make sure that no-one else has access to the server you're running your tests on. You won't get reliable results if someone else starts running queries at the same time as you.
· Make sure that the queries you're testing with are equivalent to the ones that your users want to have tuned. As we'll see, you can use Profiler to capture the exact queries your users are running against the cube.
· Whenever you test a query, run it twice; first on a cold cache, and then on a warm cache. Make sure you keep a note of the time each query takes to run and what you changed on the cube or in the query for that run.
Clearing the cache is a very important step—queries that run for a long time on a cold cache may be instant on a warm cache. When you run a query against Analysis Services, some or all of the results of that query (and possibly other data in the cube, not required for the query) will be held in cache so that the next time a query is run that requests the same data it can be answered from cache much more quickly. To clear the cache of an Analysis Services database, you need to execute a ClearCache XMLA command.
To do this in SQL Management Studio, open up a new XMLA query window and enter the following:
<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<ClearCache>
<Object>
<DatabaseID>Adventure Works DW 2012</DatabaseID>
</Object>
</ClearCache>
</Batch>
Remember that the ID of a database may not be the same as its name—you can check this by right-clicking on a database in the SQL Management Studio Object Explorer and selecting Properties. Alternatives to this method also exist: the MDX Studio tool allows you to clear the cache with a menu option, and the Analysis Services Stored Procedure Project (http://tinyurl.com/asstoredproc) contains code that allows you to clear the Analysis Services cache and the Windows File System cache directly from MDX. Clearing the Windows File System cache is interesting because it allows you to compare the performance of the cube on a warm and cold file system cache as well as a warm and cold Analysis Services cache. When the Analysis Services cache is cold or can't be used for some reason, a warm file system cache can still have a positive impact on query performance.
After the cache has been cleared, before Analysis Services can answer a query it needs to recreate the calculated members, named sets, and other objects defined in a cube's MDX Script. If you have any reasonably complex named set expressions that need to be evaluated, you'll see some activity in Profiler relating to these sets being built and it's important to be able to distinguish between this and activity that's related to the queries you're actually running. All MDX Script related activity occurs between Execute MDX Script Begin and Execute MDX Script End events; these are fired after the Query Begin event but before the Query Cube Begin event for the query run after the cache has been cleared and there is one pair of Begin/End events for each command on the MDX Script. When looking at a Profiler trace you should either ignore everything between the first Execute MDX Script Begin event and the last Execute MDX Script End event or run a query that returns no data at all to trigger the evaluation of the MDX Script, for example:
SELECT {} ON 0
FROM [Adventure Works]
Designing for performance
Many of the recommendations for designing cubes we've given so far in this book have been given on the basis that they will improve query performance, and in fact the performance of a query is intimately linked to the design of the cube it's running against. For example, dimension design, especially optimizing attribute relationships, can have a significant effect on the performance of all queries—at least as much as any of the optimizations described in this chapter. As a result, we recommend that if you've got a poorly performing query the first thing you should do is review the design of your cube (along with the relevant chapters of this book) to see if there is anything you could do differently. There may well be some kind of trade-off needed between usability, manageability, time-to-develop, overall 'elegance' of the design and query performance, but since query performance is usually the most important consideration for your users then it will take precedence. To put it bluntly, if the queries your users want to run don't run fast, your users will not want to use the cube at all!
Performance-specific design features
Once you're sure that your cube design is as good as you can make it, it's time to look at two features of Analysis Services that are transparent to the end user, but have an important impact on performance and scalability: measure group partitioning and aggregations. Both of these features relate to the Storage Engine and allow it to answer requests for data from the Formula Engine more efficiently.
Partitions
A partition is a data structure that holds some or all of the data held in a measure group. When you create a measure group, by default that measure group contains a single partition that contains all of the data. Enterprise Edition and BI Edition of Analysis Services allow you to divide a measure group into multiple partitions; Standard Edition is limited to one partition per measure group, and the ability to partition is one of the main reasons why you would want to use Enterprise Edition or BI Edition over Standard Edition.
Why partition?
Partitioning brings two important benefits: better manageability and better performance. Partitions within the same measure group can have different storage modes and different aggregation designs, although in practice they usually don't differ in these respects; more importantly they can be processed independently, so for example when new data is loaded into a fact table, you can process only the partitions that should contain the new data. Similarly, if you need to remove old or incorrect data from your cube, you can delete or reprocess a partition without affecting the rest of the measure group. We'll explore these manageability benefits in more detail in Chapter 11, Monitoring Cube Performance and Usage.
Partitioning can also improve both processing performance and query performance significantly. Analysis Services can process multiple partitions in parallel and this can lead to much more efficient use of CPU and memory resources on your server while processing is taking place. Analysis Services can also fetch and aggregate data from multiple partitions in parallel when a query is run too, and again this can lead to more efficient use of CPU and memory and result in faster query performance. Lastly, Analysis Services will only scan the partitions that contain data necessary for a query, and since this reduces the overall amount of IO needed this it can also make queries much faster.
Building partitions
You can view, create, and delete partitions on the Partitions tab of the Cube Editor in SSDT. When you run the 'New Partition' wizard or edit the Source property of an existing partition, you'll see you have two options for controlling what data is used in the partition:
· Table Binding: This means that the partition contains all of the data in a table or view in your relational data source, or a named query defined in your DSV. You can choose the table you wish to bind to on the Specify Source Information step of the New Partition wizard, or in the Partition Source dialog if you choose 'Table Binding' from the Binding Type dropdown box.
· Query Binding: This allows you to specify a SQL SELECT statement to filter the rows you want from a table; SSDT will automatically generate part of the SELECT statement for you, and all you'll need to do is supply the WHERE clause. If you're using the New Partition wizard, this is the option that will be chosen if you check the 'Specify a query to restrict rows' checkbox on the second step of the wizard; in the Partition Source dialog you can choose this option from the Binding Type dropdown box.
It might seem like query binding is the easiest way to filter your data, and while it's the most widely-used approach it does have one serious shortcoming. Since it involves hard-coding a SQL SELECT statement into the definition of the partition, any change to your fact table such as the deletion or renaming of a column can mean the SELECT statement produces error when it is run, and this means the partition processing will fail. If you have a lot of partitions in your measure group – and it's not unusual to have over one hundred partitions on a large cube – altering the query used for each one is somewhat time-consuming. Instead, binding a partition to a view in your relational database will make this kind of maintenance much easier, although you do of course now need to generate one view for each partition.
Note
It's very important that you check the queries you're using to filter your fact table for each partition. If the same fact table row appears in more than one partition, or if fact table rows don't appear in any partition, this will result in your cube displaying incorrect measure values.
On the Processing and Storage Locations step of the wizard you have the chance to create the partition on a remote server instance, functionality that is called Remote Partitions. This is one way of scaling out Analysis Services: you can have a cube and measure group on one server but store some of the partitions for the measure group on a different server, something like a linked measure group but at a lower level. It can be useful for improving processing performance in situations when you have a very small time window available for processing but in general we recommend that you do not use remote partitions. They have an adverse effect on query performance and they make management of the cube (especially backup) very difficult.
Also, on the same step you have the chance to store the partition at a location other than the default of the Analysis Services data directory. Spreading your partitions over more than one volume may make it easier to improve the IO performance of your solution, although again it can complicate database backup and restore.
After assigning an aggregation design to the partition (we'll talk about aggregations in detail next), the last important property to set on a partition is Slice. The Slice property takes the form of an MDX member, set or tuple – MDX expressions returning members, sets or tuples are not allowed, however - and indicates what data is present in a partition. While you don't have to set it the slice property we strongly recommend that you do so for ROLAP partitions, and even for MOLAP partitions for the following reasons:
· While Analysis Services does automatically detect what data is present in a partition during processing, it doesn't always work as well as you'd expect and can result in unwanted partition scanning taking place at query time in a number of scenarios. The following blog entry on the SQLCat team site explains why in detail (not all of the future improvements mentioned in this article have actually been implemented at the time of writing): http://tinyurl.com/partitionslicing.
· It acts as a useful safety mechanism to ensure that you only load the data you're expecting into a partition. If, while processing, Analysis Services finds that data is being loaded into the partition that conflicts with what's specified in the Slice property, then processing will fail.
More details on how to set the Slice property can be found in Mosha Pasumansky's blog entry on the subject here: http://tinyurl.com/moshapartition.
Planning a partitioning strategy
We now know why we should be partitioning our measure groups and what to do to create a partition. The next question is: how should we split the data in our partitions? We need to find some kind of happy medium between the manageability and performance aspects of partitioning – we need to split our data so that we do as little processing as possible, but also so the division of data means that as few partitions are scanned as possible by our users' queries. Luckily, if we partition by our Time dimension we can usually meet both needs very well. It's usually the case that when new data arrives in a fact table it's for a single day, week, or month, and it's also the case that the most popular way of slicing a query is by a time period. Therefore, it's almost always the case that when measure groups are partitioned they are partitioned by Time. It's also worth considering, though, if it's a good idea to partition by Time and another dimension; for example, in an international company you might have a Geography dimension and a Country attribute, and users may always be slicing their queries by Country too – in which case it might make sense to partition by Country.
Measure groups that contain measures with the Distinct Count aggregation type require their own specific partitioning strategy. While you should still partition by Time, you should also partition by non-overlapping ranges of values within the column you're doing the distinct count on. A lot more detail on this is available in the following white paper: http://tinyurl.com/distinctcountoptimize.
It's worth looking at the distribution of data over partitions for dimensions we're not explicitly slicing by to see how well partition elimination will work for them. You can see the distribution of member data IDs (the internal key values that Analysis Services creates for all members on a hierarchy) for a partition by querying the Discover_Partition_Dimension_Stat DMV, for example:
SELECT *
FROM SystemRestrictSchema($system.Discover_Partition_Dimension_Stat
,DATABASE_NAME = 'Adventure Works DW 2008'
,CUBE_NAME = 'Adventure Works'
,MEASURE_GROUP_NAME = 'Internet Sales'
,PARTITION_NAME = 'Internet_Sales_2003')
The following screenshot shows what the results of this query look like:
There's also a useful Analysis Services stored procedure that shows the same data and any partition overlaps included in the Analysis Services Stored Procedure Project (a free, community-developed set of sample Analysis Services stored procedures):http://tinyurl.com/partitionhealth. This blog entry describes how you can take this data and visualize it in a Reporting Services report: http://tinyurl.com/partitionslice.
We also need to consider what size our partitions should be. In general between 5 and 60 million rows per partition, or up to around 3 GB, is a good size. If you have a measure group with a single partition of below 5 million rows then don't worry, it will perform very well, but it's not worth dividing it into smaller partitions. It's equally possible to get good performance with larger partitions. It's also best to avoid having too many partitions as well – if you have more than a thousand it may make SQL Management Studio and SSDT slow to respond, and it may be worth creating fewer, larger partitions assuming these partitions stay within the size limits for a single partition we've just given.
A good reference for performance tuning your partitioning strategy is the SQL Server 2008 R2 Analysis Services Performance Guide which is available as follows: http://tinyurl.com/ssasR2perf.
Tip
Automatically generating large numbers of Partitions
When creating a measure group for the first time, it's likely you'll already have a large amount of data and may need to create a correspondingly large number of partitions for it. Clearly the last thing you'll want to do is create tens or hundreds of partitions manually and it's worth knowing some tricks to create these partitions automatically. One method involves taking a single partition, scripting it out to XMLA, and then pasting and manipulating this in Excel, as detailed here: http://tinyurl.com/generatepartitions. The Analysis Services Stored Procedure Project also contains a set of functions for creating partitions automatically based on MDX set expressions: http://tinyurl.com/autopartition. Management of partitions once the cube has gone into production is discussed inChapter 11, Monitoring Cube Performance and Usage.
Unexpected Partition scans
Even when you have configured your partitions properly it's sometimes the case that Analysis Services will scan partitions that you don't expect it to be scanning for a particular query. If you see this happening (and we'll talk about how you can monitor partition usage later in this chapter) the first thing to determine is whether these extra scans are making a significant contribution to your query times. If they aren't, it's probably not worth worrying about; if they are, there are some things to try to attempt to stop it happening.
The extra scans could be the result of a number of factors, including:
· The way you have written MDX for queries or calculations. In most cases it will be very difficult to rewrite the MDX to stop the scans, but the following blog entry describes how it is possible in one scenario: http://tinyurl.com/moshapart.
· As mentioned in Chapter 4, Measures and Measure Groups, the LastNonEmpty measure aggregation type may result in multiple partition scans. If you can restructure your cube so you can use the LastChild aggregation type, Analysis Services will only scan the last partition containing data for the current time period.
· If you have partitioned your cube using a dimension that is being used to resolve a many-to-many relationship (that's to say, not a dimension that has a many-to-many relationship but a dimension that has a regular relationship with your main measure group and an intermediate measure group used in a many-to-many relationship), then you may find that Analysis Services scans all partitions when you run a query that uses the many-to-many dimension. See this blog post for more details:http://tinyurl.com/partitionm2m.
· In some cases, even when you've set the Slice property, Analysis Services has trouble working out which partitions should be scanned for a query. Changing the attributes mentioned in the Slice property may help, but not always. The section on 'Related Attributes' and 'Almost Related Attributes' in the following blog entry discusses this in more detail: http://tinyurl.com/mdxpartitions
· Analysis Services may also decide to retrieve more data than is needed for a query to make answering future queries more efficient. This behavior is called 'prefetching' and can be turned off by setting the following connection string properties:
Disable Prefetch Facts=True; Cache Ratio=1
More information on this can be found in the section on 'Prefetching and Request Ordering' in the white paper 'Identifying and Resolving MDX Query Bottlenecks' available at http://tinyurl.com/mdxprefetch. Note that setting these connection string properties can have other, negative effects on query performance.
Note
You can set connection string properties in SQL Management Studio when you open a new MDX Query window. Just click on the Options button on the Connect to Analysis Services dialog, then go to the Additional Connection Parameters tab. Note that in certain versions of SQL Management Studio there is a problem with this functionality, so that when you set a connection string property it will continue to be set for all connections, even though the textbox on the Additional Connection Parameters tab is blank, until SQL Management Studio is closed down or until you set the same property differently.
Aggregations
An aggregation is simply a pre-summarized data set, similar to the result of a SQL SELECT statement with a GROUP BY clause, that Analysis Services can use when answering queries. The advantage of having aggregations built in your cube is that it reduces the amount of aggregation that the Analysis Services Storage Engine has to do at query time, and building the right aggregations is one of the most important things you can do to improve query performance. Aggregation design is an ongoing process that should start once your cube and dimension designs have stabilized and which will continue throughout the lifetime of the cube as its structure and the queries you run against it change; in this section we'll talk about the steps you should go through to create an optimal aggregation design.
Creating an initial aggregation design
The first stage in creating an aggregation design should be to create a core set of aggregations that will be generally useful for most queries run against your cube. This should take place towards the end of the development cycle when you're sure that your cube and dimension designs are unlikely to change much, because any changes are likely to invalidate your aggregations and mean this step will have to be repeated. It can't be stressed enough that good dimension design is the key to getting the most out of aggregations. Removing unnecessary attributes, setting AttributeHierarchyEnabled to False where possible, building optimal attribute relationships and building user hierarchies will all make the aggregation design process faster, easier and more effective. You should also take care to update the EstimatedRows property of each measure group and partition, and the EstimatedCount of each attribute before you start, and as these values are also used by the aggregation design process. BIDS Helper adds a very useful new button to the toolbar in the Partitions tab of the Cube Editor which will update all of these count properties with one click. Remember that if you are developing against a small subset of your data you will have to enter these count values manually, so that the values are similar to what they would be if you were using a full-sized production database.
To build this an initial set of aggregations we'll be running the Aggregation Design Wizard. This can be run by clicking on the Design Aggregations button on the toolbar of the Aggregations tab of the Cube Editor. This wizard will analyze the structure of your cube and dimensions, look at various property values you've set, and try to come up with a set of aggregations that it thinks should be useful. The one key piece of information it doesn't have at this point is what queries you're running against the cube, so some of the aggregations it designs may not prove to be useful in the long run, but running the wizard is extremely useful for creating a first draft of your aggregation designs.
You can only design aggregations for one measure group at a time; if you have more than one partition in the measure group you've selected, the first step of the wizard asks you to choose which partitions you want to design aggregations for. An aggregation design can be associated with many partitions in a measure group, and a partition can be associated with just one aggregation design or none at all. We recommend that, in most cases, you have just one aggregation design for each measure group for the sake of simplicity. However, if processing time is limited and you need to reduce the overall time spent building aggregations, or if query patterns are different for different partitions within the same measure group, it may make sense to apply different aggregation designs to different partitions.
The next step of the wizard asks you to review the AggregationUsage property of all the attributes on all of the cube dimensions in your cube; this property can also be set on the Cube Structure tab of the Cube Editor.
The following screenshot shows the Review Aggregation Usage step of the Aggregation Design Wizard:
The AggregationUsage property controls how dimension attributes are treated in the aggregation design process. The property can be set to the following values:
· Full: This means that the attribute or an attribute at a lower granularity directly related to it by an attribute relationship will be included in every single aggregation the wizard builds. We recommend that you use this value sparingly, for at most one or two attributes in your cube because it can significantly reduce the number of aggregations that get built. You should set it for attributes that will almost always get used in queries. For example, if the vast majority of your queries are at the month granularity it makes sense that all of your aggregations include the Month attribute from your Time dimension.
· None: This means that the attribute will not be included in any aggregation that the wizard designs. Don't be afraid of using this value for any attributes that you don't think will be used often in your queries; it can be a useful way of ensuring that the attributes that are used often get good aggregation coverage.
Note that attributes with AttributeHierarchyEnabled set to False will have no aggregations designed for them anyway.
· Unrestricted: This means that the attribute may be included in the aggregations designed, depending on whether the algorithm used by the wizard considers it to be useful or not.
· Default: The default option applies a complex set of rules, which are:
· The granularity attribute (usually the key attribute, unless you specified otherwise in the dimension usage tab) is treated as Unrestricted.
· All attributes on dimensions involved in many-to-many relationships, unmaterialized referenced relationships, and data mining dimensions are treated as None. Aggregations may still be built at the root granularity that is the intersection of every All Members on every attribute.
· All attributes that are used as levels in natural user hierarchies are treated as Unrestricted.
· Attributes with IsAggregatable set to False are treated as Full.
· All other attributes are treated as None.
The next step in the wizard asks you to verify the number of EstimatedRows and EstimatedCount properties we've already talked about, and gives the option of setting a similar property that shows the estimated number of members from an attribute that appear in any one partition. This can be an important property to set; if you are partitioning by Month, although you may have 36 members on your Month attribute, a partition will only contain data for one of them.
On the Set Aggregation Options step you finally reach the point where some aggregations can be built. Here you can apply one last set of restrictions on the set of aggregations that will be built, choosing to either:
· Estimated Storage Reaches: This means you build aggregations to fill a given amount of disk space.
· Performance Gain Reaches: This is the most useful option. It does not mean that all queries will run n percent faster, nor does it mean that a query that hits an aggregation directly will run n percent faster. Think of it like this: if the wizard built all the aggregations it thought were useful to build (note: this is not the same thing as all of the possible aggregations that could be built on the cube) then, in general, performance would be better.
Some queries would not benefit from aggregations, some would be slightly faster, and some would be a lot faster; some aggregations would be more often used than others. So if you set this property to 100% the wizard would build all the aggregations that it could, and you'd get 100% of the performance gain possible from building aggregations. Setting this property to 30%, the default and recommended value, will build the aggregations that give you 30% of this possible performance gain – not 30% of the possible aggregations, usually a much smaller number. As you can see from the following screenshot, the graph drawn on this step plots the size of the aggregations built versus overall performance gain, and the shape of the curve shows that a few, smaller aggregations usually provide the majority of the performance gain.
· I Click Stop: This means carry on building aggregations until you click on the Stop button. Designing aggregations can take a very long time, especially on more complex cubes because there may literally be millions or billions of possible aggregations that could be built. In fact, it's not unheard of for the aggregation design wizard to run for several days before it's stopped!
· Do Not Design Aggregations: This allows you to skip designing aggregations.
The approach we suggest taking here is to first select I Click Stop and then click on the Start button. On some measure groups this will complete very quickly, with only a few small aggregations built. If that's the case click on Next; otherwise, if it's taking too long or too many aggregations are being built, click on Stop and then Reset, and then select Performance Gain Reaches and enter 30% and Start again. This should result in a reasonable selection of aggregations being built; in general around 50-100 aggregations is the maximum number you should be building for a measure group, and if 30% leaves you short of this try increasing the number by 10% until you feel comfortable with what you get.
In the final step of the wizard, enter a name for your aggregation design and save it. It's a good idea to give the aggregation design a name including the name of the measure group to make it easier to find if you ever need to script it to XMLA.
Note
It's quite common that Analysis Services cube developers stop thinking about aggregation design at this point. This is a serious mistake; just because you have run the Aggregation Design Wizard does not mean you have built all the aggregations you need, or indeed any useful ones at all! Doing Usage-Based Optimization and/or building aggregations manually is absolutely essential.
Usage-Based Optimization
We now 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 Optimization comes in; it allows us to log the requests for data that Analysis Services makes when a query is run and then feed this information into the aggregation design process.
To be able to do Usage-Based Optimization, you must first set up Analysis Services to log these requests for data. This involves specifying a connection string to a relational database in the server properties of your Analysis Services instance and allowing Analysis Services to create a log table in that database. The white paper "Configuring the Analysis Services Query Log" contains more details on how to do this (it's written for Analysis Services 2005 but is still relevant for Analysis Services 2012), and can be downloaded from http://tinyurl.com/ssasquerylog.
The query log is a misleading name, because as you'll see if you look inside it doesn't actually contain the text of MDX queries run against the cube. When a user runs an MDX query, Analysis Services decomposes it into a set of requests for data at a particular granularity and it's these requests that are logged; we'll look at how to interpret this information in the next section. A single query can result in no requests for data, or it can result in as many as hundreds or thousands of requests, especially if it returns a lot of data and a lot of MDX calculations are involved. When setting up the log you also have to specify the percentage of all data requests that Analysis Services actually logs with the QueryLogSampling property – in some cases if it logged every single request you would end up with a very large amount of data very quickly, but on the other hand, if you set this value too low you may end up not seeing certain important long-running requests. We recommend that you start by setting up this property to 100 but that you monitor the size of the log closely and reduce the value if you find that the number of requests logged is too high.
Once the log has been set up, let your users start querying the cube. Explain to them what you're doing and that some queries may not perform well at this stage. Given access to a new cube it will take them a little while to understand what data is present and what data they're interested in; if they're new to Analysis Services it's also likely they'll need some time to get used to whatever client tool they're using. Therefore, you'll need to have logging enabled for at least a month or two before you can be sure that your query log contains enough useful information. Remember that if you change the structure of the cube while you're logging, the existing contents of the log will no longer be usable.
Last of all, you'll need to run the Usage-Based Optimization Wizard to build new aggregations using this information. The Usage-Based Optimization Wizard is very similar to the Design Aggregations Wizard, with the added option to filter the information in the query log by date, user, and query frequency before it's used to build aggregations. It's a good idea to do this filtering carefully; you should probably exclude any queries you've run yourself, for example, since they're unlikely to be representative of what the users are doing, and make sure that the most important users' queries are over-represented.
Once you've done this you'll have a chance to review what data is actually going to be used before you actually build the aggregations.
On the last step of the wizard you have the choice of either creating a new aggregation design or merging the aggregations that have just been created with an existing aggregation design. We recommend the latter. What you've just done is optimize queries that ran slowly on an existing aggregation design, and if you abandon the aggregations you've already got, it's possible that queries which previously had been quick would be slow afterwards.
This exercise should be repeated at regular intervals throughout the cube's lifetime to ensure that you built any new aggregations that are necessary as the queries that your users run change. Query logging can, however, have an impact on query performance so it's not a good idea to leave logging running all the time.
Tip
Processing aggregations
When you've created or edited the aggregations on one or more partitions, you don't need to do a full process on the partitions. All you need to do is to deploy your changes and then run a ProcessIndex, which is usually fairly quick, and once you've done that queries will be able to use the new aggregations. When you run a ProcessIndex Analysis Services does not need to run any SQL queries against the relational data source if you're using MOLAP storage.
Monitoring partition and aggregation usage
Having created and configured your partitions and aggregations, you'll naturally want to be sure that when you run a query, Analysis Services is using them as you expect. You can do this very easily by running a trace with SQL Server Profiler.
To use Profiler, start it and then connect to your Analysis Services instance to create a new trace. On the Trace Properties dialog choose the Blank template and go to the Events Selection tab and check the following:
· Progress Reports\Progress Report Begin
· Progress Reports\Progress Report End
· Queries Events\Query Begin
· Queries Events\Query End
· Query Processing\Execute MDX Script Begin
· Query Processing\Execute MDX Script End
· Query Processing\Query Cube Begin
· Query Processing\Query Cube End
· Query Processing\Get Data From Aggregation
· Query Processing\Query Subcube Verbose
· Query Processing\Resource Usage
Then clear the cache and click on Run to start the trace.
Note
Analysis Services 2012 also includes the ability to view information on MDX query plans through the Profiler. The following blog post gives more details on how to do this: http://tinyurl.com/evalnodes, but the truth is that the information available here is so difficult to interpret it is almost useless for any practical purpose.
Once you've done this you can either open up your Analysis Services client tool or you can start running MDX queries in SQL Management Studio. When you do this you'll notice that Profiler starts to show information about what Analysis Services is doing internally to answer these queries.
Interpreting the results of a Profiler trace is a complex task and well outside the scope of this book, but it's very easy to pick out some useful information relating to aggregation and partition usage. Put simply:
· The Query Subcube Verbose events represent individual requests for data from the Formula Engine to the Storage Engine, which can be answered either from cache, an aggregation or base-level partition data. Each of these requests is at a single granularity, meaning that all of the data in the request comes from a single distinct combination of attributes; we refer to these granularities as 'subcubes'. The TextData column for this event shows the granularity of data that is being requested in human readable form; the Query Subcube event will display exactly the same data but in the less friendly format that the Usage-Based Optimization Query Log uses.
· Pairs of Progress Report Begin and Progress Report End events show that data is being read from disk, either from an aggregation or a partition. The TextData column gives more information, including the name of the object being read; however, if you have more than one object (for example, an aggregation) with the same name, you need to look at the contents of the ObjectPath column to see what object exactly is being queried.
· The Get Data From Aggregation event is fired when data is read from an aggregation, in addition to any Progress Report events.
· The Duration column shows how long each of these operations takes in milliseconds.
· The Resource Usage event is fired at the end of query execution and gives a summary of the number of disk read operations for the query, the number of rows scanned, and the number of rows returned by the Storage Engine.
At this point in the cube optimization process you should be seeing in Profiler that when your users run queries they hit as few partitions as possible and hit aggregations as often as possible. If you regularly see queries that scan all the partitions in your cube or which do not use any aggregations at all, you should consider going back to the beginning of the process and rethinking your partitioning strategy and rerunning the aggregation design wizards. In a production system many queries will be answered from cache and therefore be very quick, but you should always try to optimize for the worst-case scenario of a query running on a cold cache.
Building aggregations manually
However good the aggregation designs produced by the wizards are, it's very likely that at some point you'll have to design aggregations manually for particular queries. Even after running the Usage-Based Optimization Wizard you may find that it still does not build some potentially useful aggregations. The algorithm the wizards use is very complex and something of a black box, so for whatever reason (perhaps because it thinks it would be too large) it may decide not to build an aggregation that, when built manually, turns out to have a significant positive impact on the performance of a particular query.
Before we can build aggregations manually we need to work out which aggregations we need to build. To do this, we once again need to use Profiler and look at either the Query Subcube or the Query Subcube Verbose events. These events, remember, display the same thing in two different formats - requests for data made to the Analysis Services storage engine during query processing - and the contents of the Duration column in Profiler will show how long in milliseconds each of these requests took. A good rule of thumb is that any Query Subcube event that takes longer than half a second (500 ms) would benefit from having an aggregation built for it; you can expect that a Query Subcube event that requests data at the same granularity as an aggregation will execute almost instantaneously.
The following screenshot shows an example of trace on an MDX query that takes 700 ms:
The single Query Subcube Verbose event is highlighted, and we can see that the duration of this event is the same as that of the query itself so if we want to improve the performance of the query we need to build an aggregation for this particular request. Also, in the lower half of the screen we can see the contents of the TextData column displayed. This shows a list of all the dimensions and attributes from which data is being requested – the granularity of the request – and the simple rule to follow here is that whenever you see anything other than a zero by an attribute we know that the granularity of the request includes this attribute. We need to make a note of all of the attributes, which have anything other than a zero next to them and then build an aggregation using them; in this case it's just the Category attribute of the Product dimension.
Note
The SQLCAT team's article on SSAS partition slicing, available from http://tinyurl.com/partitionslicing, includes more detailed information on how to interpret the information given by the Query Subcube Verbose event.
So now we know what aggregation we need to build, we need to go ahead and build it. We have a choice of tools to do this; we can either use the functionality built into SSDT, or we can use some of the excellent functionality that BIDS Helper provides. In SSDT, to view and edit aggregations, you need to go to the Aggregations tab in the cube editor. On the Standard view you only see a list of partitions and which aggregation designs they have associated with them; if you switch to the Advanced view by pressing the appropriate button on the toolbar, you can view the aggregations in each aggregation design for each measure group. If you right-click in the area where the aggregations are displayed you can also create a new aggregation and once you've done that you can specify the granularity of the aggregation by checking and unchecking the attributes on each dimension. For our particular query we only need to check the box next to the Category attribute, as follows:
The small tick at the top of the list of dimensions in the Status row shows that this aggregation has passed the built-in validation rules that SSDT applies to make sure this is a useful aggregation. If you see an amber warning triangle here, hover over it with your mouse and in the tool tip, you'll see a list of reasons why the aggregation has failed its status check.
If we then deploy and run a ProcessIndex, we can then rerun our original query and watch it use the new aggregation, running much faster as a result:
The problem with the native SSDT aggregation design functionality is that it becomes difficult to use when you have complex aggregations to build and edit. The functionality present in BIDS Helper, while it looks less polished, is far more useable and offers many benefits over the SSDT native functionality, for example:
· The BIDS Helper Aggregation Design interface displays the aggregation granularity in the same way (that is using 1s and 0s, as seen in the following screenshot) as the Query Subcube event in Profiler does, making it easier to cross reference between the two.
· It also shows attribute relationships when it displays the attributes on each dimension when you're designing an aggregation, as seen on the right-hand side in the following screenshot. This is essential to being able to build optimal aggregations.
· It also shows whether an aggregation is rigid or flexible.
· It has functionality to remove duplicate aggregations and ones containing redundant attributes, and search for similar aggregations.
· It allows you to create new aggregations based on the information stored in the Query Log.
· It also allows you to delete unused aggregations based on information from a Profiler trace.
· Finally, it has some very comprehensive functionality to allow you to test the performance of the aggregations you build (see http://tinyurl.com/testaggs).
Unsurprisingly, if you need to do any serious work designing aggregations manually we recommend using BIDS Helper over the built-in functionality.
Common aggregation design issues
Several features of your cube design must be kept in mind when designing aggregations, because they can influence how Analysis Services storage engine queries are made and therefore which aggregations will be used. These include:
· There's no point building aggregations above the granularity you are slicing your partitions. Aggregations are built on a per-partition basis, so for example if you're partitioning by Month there's no value in building an aggregation at the Year granularity since no aggregation can contain more than one month's worth of data. It won't hurt if you do it, it just means that an aggregation at Month will be the same size as one at Year but useful to more queries. It follows from this that it might be possible to over-partition data and reduce the effectiveness of aggregations, but we have anecdotal evidence from people who have built very large cubes that this is not an issue.
· For queries involving a dimension with a many-to-many relationship to a measure group, aggregations must not be built using any attributes from the many-to-many dimension, but instead must be built at the granularity of the attributes with a regular relationship to the intermediate measure group. So for example you have a Customer dimension joining to a Bank Account Balance measure group with a many-to-many relationship via a Bank Account dimension at the Account attribute granularity, you should only build aggregations involving the Account attribute. This is because when Analysis Services runs a query using Customer against the Bank Account Balance measure group, the selection on Customer is resolved to a list of distinct Bank Accounts first. As a result, in most cases it's not worth building aggregations for queries on many-to-many dimensions since the granularity of these queries is often close to that of the original fact table.
· Queries involving measures which have semi-additive aggregation types are always resolved at the granularity attribute of the Time dimension, so you need to include that attribute in all aggregations.
· Queries involving measures with measure expressions require aggregations at the common granularity of the two measure groups involved.
· You should not build aggregations including a parent/child attribute; instead you should use the key attribute in aggregations.
· No aggregation should include an attribute which has AttributeHierarchyEnabled set to False.
· No aggregation should include an attribute that is below the granularity attribute of the dimension for the measure group.
· Any attributes which have a default member that is anything other than the All Member, or which have IsAggregatable set to False, should also be included in all aggregations.
· Aggregations and indexes are not built for partitions with fewer than 4096 rows. This threshold is set by the IndexBuildThreshold property in msmdsrv.ini; you can change it but it's not a good idea to do so.
· Aggregations should not include redundant attributes, that is to say attributes from the same 'chain' of attribute relationships. For example, if you had a chain of attribute relationships going from Month to Quarter to Year, you should not build an aggregation including Month and Quarter – it should just include Month. This will increase the chance that the aggregation can be used by more queries, as well as reducing the size of the aggregation.
MDX calculation performance
Optimizing the performance of the Storage Engine is relatively straightforward; you can diagnose performance problems easily and you only have two options – partitioning and aggregation – to solve them. Optimizing the performance of the Formula Engine is much more complicated because it requires knowledge of MDX, diagnosing performance problems is difficult because the internal workings of the Formula Engine are hard to follow, and solving the problem is reliant on knowing tips and tricks that may change from service pack to service pack.
Diagnosing Formula Engine performance problems
If you have a poorly performing query, and if you can rule out the Storage Engine as the cause of the problem, the issue is with the Formula Engine. We've already seen how we can use Profiler to check the performance of Query Subcube events, to see which partitions are being hit and to check whether aggregations are being used; if you subtract the sum of the durations of all the Query Subcube events (bear in mind that some of these events may execute in parallel though) from the duration of the query as a whole you'll get the amount of time spent in the Formula Engine.
Another hallmark of a query that spends most of its time in the Formula Engine is that it will only use one CPU, even on a multiple-CPU server. This is because the Formula Engine, unlike the Storage Engine, is single-threaded. As a result, if you watch CPU usage in Task Manager while you run a query you can get a good idea of what's happening internally; high usage of multiple CPUs indicates work is taking place in the Storage Engine, while high usage of one CPU indicates work is taking place in the Formula Engine.
Calculation performance tuning
Having worked out that the Formula Engine is the cause of a query's poor performance then the next step is, obviously, to try to tune the query. In some cases, you can achieve impressive performance gains (sometimes of several hundred percent) simply by rewriting a query and the calculations it depends on; the problem is knowing how to rewrite the MDX and working out which calculations contribute most to the overall query duration. Unfortunately, Analysis Services doesn't give you much information to use to solve this problem and there are very few tools out there which can help either, so doing this is something of a black art.
There are three main ways you can improve the performance of the Formula Engine: tune the structure of the cube it's running on, tune the algorithms you're using in your MDX, and tune the implementation of those algorithms so they use functions and expressions that Analysis Services can run efficiently. We've already talked in depth about how the overall cube structure is important for the performance of the Storage Engine and the same goes for the Formula Engine. The only thing to repeat here is the recommendation that if you can avoid doing a calculation in MDX by doing it at an earlier stage; for example, in your ETL or in your relational source, and do so without compromising functionality, you should do so. We'll now go into more detail about tuning algorithms and implementations.
Note
The SSAS-Info website maintains a comprehensive list of all of the blog posts worth reading on Analysis Services performance tuning at http://tinyurl.com/ssasinfoperf.
Tuning algorithms used in MDX
Tuning an algorithm in MDX is much the same as tuning an algorithm in any other kind of programming language—it's more a matter of understanding your problem and working out the logic that provides the most efficient solution than anything else. That said there are some general techniques that can be used often in MDX and which we will walk through here.
Using Named Sets to avoid recalculating Set Expressions
Many MDX calculations involve expensive set operations, a good example being rank calculations where the position of a tuple within an ordered set needs to be determined. The following query includes a calculated member that displays Dates on the Rows axis of a query, and on columns shows a calculated measure that returns the rank of that date within the set of all dates based on the value of the Internet Sales Amount measure:
WITH
MEMBER MEASURES.MYRANK AS
Rank
(
[Date].[Date].CurrentMember
,Order
(
[Date].[Date].[Date].MEMBERS
,[Measures].[Internet Sales Amount]
,BDESC
)
)
SELECT
MEASURES.MYRANK ON 0
,[Date].[Date].[Date].MEMBERS ON 1
FROM [Adventure Works]
This query runs very slowly, and the problem is that every time the calculation is evaluated it has to evaluate the Order function to return the set of ordered dates. In this particular situation though, you can probably see that the set returned will be the same every time the calculation is called, so it makes no sense to do the ordering more than once. Instead, we can create a named set hold the ordered set and refer to that named set from within the calculated measure:
WITH
SET ORDEREDDATES AS
Order
(
[Date].[Date].[Date].MEMBERS
,[Measures].[Internet Sales Amount]
,BDESC
)
MEMBER MEASURES.MYRANK AS
Rank
(
[Date].[Date].CurrentMember
,ORDEREDDATES
)
SELECT
MEASURES.MYRANK ON 0
,[Date].[Date].[Date].MEMBERS ON 1
FROM [Adventure Works]
This version of the query is many times faster, simply as a result of improving the algorithm used; the problem is explored in more depth in this blog entry: http://tinyurl.com/mosharank
Since normal named sets are only evaluated once they can be used to 'cache' set expressions in some circumstances; however, the fact that they are static means they can be too inflexible to be useful most of the time. Note that normal named sets defined in the MDX Script are only evaluated once, when the MDX Script executes and not in the context of any particular query, so it wouldn't be possible to change the preceding example so that the set and calculated measure were defined on the server. Even named sets defined in the WITH clause are evaluated only once, in the context of the WHERE clause, so it wouldn't be possible to crossjoin another hierarchy on columns and use this approach because for it to work, the set would have to be reordered once for each column.
The introduction of dynamic named sets in Analysis Services 2008 improved the situation a little, and other more advanced techniques can be used to work around these issues, but in general named sets are less useful than you might hope. For further reading on this subject see the following blog posts:
· http://tinyurl.com/moshadsets
· http://tinyurl.com/chrisdynamicsets
Using calculated members to cache numeric values
In the same way that you can avoid unnecessary re-evaluations of set expressions by using named sets, you can also rely on the fact that the Formula Engine can (usually) cache the result of a calculated member to avoid recalculating expressions which return numeric values. What this means in practice is that anywhere in your code you see an MDX expression that returns a numeric value repeated across multiple calculations you should consider abstracting it to its own calculated member; not only will this help performance, but it will improve the readability of your code. For example, take the following slow query which includes two calculated measures:
WITH
MEMBER [Measures].TEST1 AS
[Measures].[Internet Sales Amount]
/
Count
(
TopPercent
(
{
[Scenario].[Scenario].&[1]
,[Scenario].[Scenario].&[2]
}*
[Account].[Account].[Account].MEMBERS*
[Date].[Date].[Date].MEMBERS
,10
,[Measures].[Amount]
)
)
MEMBER [Measures].TEST2 AS
[Measures].[Internet Tax Amount]
/
Count
(
TopPercent
(
{
[Scenario].[Scenario].&[1]
,[Scenario].[Scenario].&[2]
}*
[Account].[Account].[Account].MEMBERS*
[Date].[Date].[Date].MEMBERS*
[Department].[Departments].[Department Level 02].MEMBERS
,10
,[Measures].[Amount]
)
)
SELECT
{
[Measures].TEST1
,[Measures].TEST2
} ON 0
,[Customer].[Gender].[Gender].MEMBERS ON 1
FROM [Adventure Works]
A quick glance over the code shows that a large section of it occurs twice in both calculations – everything inside the Count function. If we remove that code to its own calculated member as follows:
WITH
MEMBER [Measures].Denominator AS
Count
(
TopPercent
(
{
[Scenario].[Scenario].&[1]
,[Scenario].[Scenario].&[2]
}*
[Account].[Account].[Account].MEMBERS*
[Date].[Date].[Date].MEMBERS
,10
,[Measures].[Amount]
)
)
MEMBER [Measures].TEST1 AS
[Measures].[Internet Sales Amount] / [Measures].Denominator
MEMBER [Measures].TEST2 AS
[Measures].[Internet Tax Amount] / [Measures].Denominator
SELECT
{
[Measures].TEST1
,[Measures].TEST2
} ON 0
,[Customer].[Gender].[Gender].MEMBERS ON 1
FROM [Adventure Works]
The query runs much faster, simply because instead of evaluating the Count twice for each of the two visible calculated measures we evaluate it once, cache the result in the calculated measure Denominator and then reference this in the other calculated measures.
It's also possible to find situations where you can rewrite code to avoid evaluating a calculation that always returns the same result over different cells in the multidimensional space of the cube. This is much more difficult to do effectively though; the following blog entry describes how to do it in detail: http://tinyurl.com/fecache.
Tuning the implementation of MDX
Like just about any other software product, Analysis Services is able to do some things more efficiently than others. It's possible to write the same query or calculation using the same algorithm but using different MDX functions and see a big difference in performance; as a result we need to know which are the functions we should use and which ones we should avoid. Which ones are these though? The trouble is that this changes from version to version of Analysis Services, even from cumulative update to cumulative update, and what constitutes good practice in one scenario might not work in another. The only advice we can offer is to read as many blog posts on MDX query performance as you can, and if you identify a problem in your code, search the Web to see if someone has experienced something similar and already solved the problem.
Tip
Block Computation versus Cell-by-Cell
When the Formula Engine has to evaluate an MDX expression for a query it can do so in one of two basic ways. It can evaluate the expression for each cell returned by the query, one at a time, an evaluation mode known as 'cell-by-cell'; or it can try to analyze the calculations required for the whole query and find situations where the same calculation might be repeated for multiple cells and instead do it only once, an evaluation mode known variously as 'block computation' or 'bulk evaluation'. Block computation is only possible in some situations, depending on how the code is written, but is often many times more efficient than cell-by-cell mode. As a result, we want to write MDX code in such a way that the Formula Engine can use block computation as much as possible, and when we talk about using 'efficient' MDX functions or constructs then this is what we in fact mean. Given that different calculations in the same query, and different expressions within the same calculation, can be evaluated using block computation and cell-by-cell mode; that there's no way of knowing which mode is used when; and that in some cases Analysis Services can't use block mode anyway, it's difficult to tell whether we are writing good MDX or not. One of the few indicators we have is the Performance Monitor counter MDX\Total Cells Calculated, which basically returns the number of cells in a query that were calculated in cell-by-cell mode; if a change to your MDX increments this value by a smaller amount than before, and the query runs faster, you're doing something right.
A reasonably good guide for Analysis Services MDX best practices exists in the Books Online topic "Performance Improvements for MDX in SQL Server 2008 Analysis Services", available online here: http://tinyurl.com/mdximp, although some of the issues it mentions have already been dealt with at the time of writing in the very latest builds of Analysis Services 2012. For example, the use of named sets inside functions like Sum() or Aggregate no longer cause problems, as detailed here: http://tinyurl.com/chrissets2012. There are a few general rules that are worth highlighting though:
· Don't use the Non_Empty_Behavior calculation property in Analysis Services 2012, unless you really know how to set it and are sure that it will provide a performance benefit. It was widely misused with Analysis Services 2005 and most of the work that went into the Formula Engine for Analysis Services 2008 was to ensure that it wouldn't need to be set for most calculations. This is something that needs to be checked if you're migrating an Analysis Services 2005 cube to a more recent version.
· Never use late binding functions like LookupCube , or StrToMember, StrToSet without the Constrained flag, inside calculations since they have a serious negative impact on performance. It's almost always possible to rewrite calculations so they don't need to be used; in fact, the only valid use for StrToMember or StrToSet in production code is when using MDX parameters. The LinkMember function suffers from a similar problem but is less easy to avoid using.
· Use the NonEmpty function wherever possible; it can be much more efficient than using the Filter function or other methods. Never use NonEmptyCrossjoin either. It's deprecated, and everything you can do with it you can do more easily and reliably with NonEmpty.
· Lastly, don't assume that whatever worked best in earlier versions of Analysis Services is still best practice for Analysis Services 2012. In general you should always try to write the simplest MDX code possible initially, and then only change it when you find performance is unacceptable. Many of the tricks that existed to optimize common calculations for earlier versions now perform worse on Analysis Services 2012 than the straightforward approaches they were designed to replace.
Caching
We've already seen how Analysis Services can cache the values returned in the cells of a query, and how this can have a significant impact on the performance of a query. Both the Formula Engine and the Storage Engine can cache data, but may not be able to do so in all circumstances; similarly, although Analysis Services can share the contents of the cache between users there are several situations where it is unable to do so. Given that in most cubes there will be a lot of overlap in the data that users are querying, caching is a very important factor in the overall performance of the cube and as a result ensuring that as much caching as possible is taking place is a good idea.
Formula cache scopes
There are three different cache 'contexts' within the Formula Engine, which relate to how long data can be stored within the cache and how that data can be shared between users:
· Query Context: This means that the results of calculations can only be cached for the lifetime of a single query and so cannot be reused by subsequent queries or by other users.
· Session Context: This means the results of calculations are cached for the lifetime of a session and can be reused by subsequent queries in the same session by the same user.
· Global Context: This means the results of calculations are cached until the cache is dropped (usually when some form of processing takes place on the server) and reused by subsequent queries run by other users as well as the user who ran the original query.
Clearly the Global Context is the best from a performance point of view, followed by the Session Context and then the Query Context; Analysis Services will always try to use the Global Context wherever possible, but it is all too easy to accidentally write queries or calculations that force the use of the Session Context or the Query Context. Here's a list of the most important situations when that can happen:
· If you define any calculations (not including named sets) in the WITH clause of a query, even if you do not use them, then Analysis Services can only use the Query Context (see http://tinyurl.com/chrisfewith for more details).
· If you define session-scoped calculations but do not define calculations in the WITH clause, the Session Context must be used.
· Using a subselect in a query will force the use of the Query Context (see http://tinyurl.com/chrissubcache), although this has been addressed as of Analysis Services 2012 SP1 CU4 (see http://tinyurl.com/subselects2012).
· Use of the CREATE SUBCUBE statement will force the use of the Session Context.
· When a user connects to a cube using a role that uses cell security, then the Query Context will be used.
· When calculations are used that contain non-deterministic functions (functions which could return different results each time they are called), for example, the Now() function that returns the system date and time, Username or any Analysis Services storedprocedure, then this forces the use of the Query Context.
Other scenarios that restrict caching
Apart from the restrictions imposed by cache context, there are other scenarios where caching is either turned off or restricted.
When arbitrary-shaped sets are used in the WHERE clause of a query, no caching at all can take place in either the Storage Engine or the Formula Engine. An arbitrary-shaped set is a set of tuples that cannot be created by a cross-join, for example:
({([Customer].[Country].&[Australia], [Product].[Category].&[1]),([Customer].[Country].&[Canada], [Product].[Category].&[3])})
If your users frequently run queries that use arbitrary-shaped sets then this can represent a very serious problem, and you should consider redesigning your cube to avoid it. The following blog entries discuss this problem in more detail:
· http://tinyurl.com/tkarbset
· http://tinyurl.com/chrisarbset
Even within the Global Context, the presence of security can affect the extent to which cache can be shared between users. When dimension security is used the contents of the Formula Engine cache can only be shared between users who are members of roles, which have the same permissions. Worse, the contents of the Formula Engine cache cannot be shared between users who are members of roles which use dynamic security at all, even if those users do in fact share the same permissions.
Cache warming
Since we can expect many of our queries to run instantaneously on a warm cache, and the majority at least to run faster on a warm cache than on a cold cache, it makes sense to preload the cache with data so that when users come to run their queries they will get warm-cache performance. There are two basic ways of doing this: running CREATE CACHE statements and automatically running batches of queries.
The CREATE CACHE statement
The CREATE CACHE statement allows you to load a specified subcube of data into the Storage Engine cache. Here's an example of what it looks like:
CREATE CACHE FOR [Adventure Works] AS
({[Measures].[Internet Sales Amount]}, [Customer].[Country].[Country].MEMBERS,
[Date].[Calendar Year].[Calendar Year].MEMBERS)
More detail on this statement can be found at http://tinyurl.com/catcache.
The CREATE CACHE statements can be added to the MDX Script of the cube so they execute every time the MDX Script is executed, although if the statements take a long time to execute (as they often do), this might not be a good idea. They can also be run after processing has finished from an Integration Services package using an Execute SQL task or through ASCMD, and this is a much better option because it means you have much more control over when the statements actually execute – you wouldn't want them running every time you cleared the cache, for instance.
Running batches of queries
The main drawback of the CREATE CACHE statement is that it can only be used to populate the Storage Engine cache, and in many cases it's warming the Formula Engine cache that makes the biggest difference to query performance. The only way to do this is to find a way to automate the execution of large batches of MDX queries (potentially captured by running a Profiler trace while users go about their work) that return the results of calculations and so which will warm the Formula Engine cache. This automation can be done in a number of ways, for example, by using the ASCMD command-line utility which is part of the sample code for Analysis Services that Microsoft provides (available for download at http://tinyurl.com/ssassamples); another common option is to use an Integration Services package to run the queries, as described in the following blog entries:
· http://tinyurl.com/chriswarm
· http://tinyurl.com/allancachewarm
This approach is not without its own problems, though; it can be very difficult to make sure that the queries you're running return all the data you want to load into cache, and even when you have done that user query patterns change over time so ongoing maintenance of the set of queries is important.
Scale-up and Scale-out
Buying better or more hardware, that is to say "scaling up", should be your last resort when trying to solve query performance problems. It's expensive and you need to be completely sure that it will indeed improve matters. Adding more memory will increase the space available for caching but nothing else; adding more or faster CPUs will lead to faster queries but you might be better off investing time in building more aggregations or tuning your MDX. Scaling up as much as your hardware budget allows is a good idea but may have little impact on the performance of individual problem queries unless you badly under-specified your Analysis Services server in the first place.
If your query performance degenerates as the number of concurrent users running queries increases, consider scaling-out by implementing what's known as an OLAP farm. This architecture is widely used in large implementations and involves multiple Analysis Services instances on different servers, and using network load balancing to distribute user queries between these servers. Each of these instances needs to have the same database on it and each of these databases must contain exactly the same data in it for queries to be answered consistently. This means that as the number of concurrent users increases you can easily add new servers to handle the increased query load. It also has the added advantage of removing a single point of failure, so if one Analysis Services server fails then the others take on its load automatically.
Making sure that data is the same across all servers is a complex operation and you have a number of different options for doing this: you can either use the Analysis Services database synchronization functionality, backup and restore, copy and paste the data from one location to another using a tool like Robocopy, or use the Analysis Services shared scalable database functionality. The following white paper describes how some of these options can be used to implement a network load balanced OLAP farm for Analysis Services: http://tinyurl.com/nlbssas
Shared scalable databases have a significant advantage over synchronization and file-copying in that they don't need to involve any moving of files at all. They can be implemented using the same approach described in the white paper earlier, but instead of copying the databases between instances you process a database (attached in ReadWrite mode) on one server, detach it from there, and then attach it in ReadOnly mode to one or more user-facing servers for querying while the files themselves stay in one place. You do, however, have to ensure that your disk subsystem does not become a bottleneck as a result.
Summary
In this chapter we've learned how to optimize the performance of our cube so our users' queries execute as quickly as possible. We've looked at how Analysis Services processes queries and what the roles of the Storage Engine and the Formula Engine have in this; we've seen how building partitions and aggregations can improve the performance of the Storage Engine, and we've also seen how to write MDX to ensure that the Formula Engine works as efficiently as possible. Last of all, we've seen how important caching is to overall query performance and what we need to do to ensure that we can cache data as often as possible, and we've discussed how to scale-out Analysis Services using network load balancing to handle large numbers of concurrent users. In the next chapter, we'll take a look at how to apply security to the data in a cube.