SSAS aggregation design boosts cube query performance (reprint)

Problem

What exactly is SQL Server analysis Services (SSAS) aggregations and how exactly can I review and use them?

Solution

Aggregations in SSAS offer a wonderful opportunity to improve query performance and calculation times by "pre aggregating" Sets of data. These aggregations allow a cube query to ask for a specific value or set of values for a specific group of dimension Attri Butes (a set), and has the calculation already completed before the query was even asked. The aggregation and related query could ask for a single value or actually cover a whole set of values to be returned. Without the aggregations, the query would return results much slower and with more CPUs and memory intensity as the query m UST complete the aggregation calculations at run time, which of course takes significantly longer than if the data points is already summarized to the desired level of the query. Of course it is a trade off between creating a large number of aggregations which speed up query performance versus Increa SE processing time and memory needed to process the cube.

Est ablishing Aggregations

There is three distinct methods of defining aggregations currently available to cube designers.

The first method utilizes the Aggregation Design Wizard to create a group of aggregations by comparing the amount of space Required to store and process the aggregation vs. the time it would take to query for the same values. Siddharth Mehta completed an excellent tip on using the Aggregation Design Wizard, which is available at:http://www.mssql tips.com/sqlservertip/2439/optimize-a-sql-server-analysis-services-measure-group-partition-for-performance/. This method is a good start; However it treats all possible combinations of aggregations as equally possible, so some aggregations could get created, but Never be used.

The second method, called Usage Based optimization, uses the SSAS Query log which must be turned on and then is used as a basis to establish the aggregations. This method addresses the problem with the Aggregation Design Wizard by recording every n number of queries from SSAS. This recording are controlled by the log \ Query Log properties and are set at the server level as shown in the below illust Ration. The tip, http://www.mssqltips.com/sqlservertip/2876/ Improve-sql-server-analysis-services-performance-with-the-usage-based-optimization-wizard/, written by Daniel Calbimonte provides a superb review of this topic. We could record every query, but this method has the potential to generate a very large table with many queries (which cou LD impact performance).

So if we have these, methods of creating aggregations why do we need a third method?

The third method allows us to actually create aggregations based on the intended use of the SSAS database and at the de Sired level of detail or summation. In essence, the This method allows us to decide exactly "what aggregations need" is created at any desired attribute or Hiera Rchy level and combination of attributes. Often, I compare the Aggregation Design Wizard and the Query Log to the Database Tuning Adviser (DTA) in regular SQL Serve R. They both provide good starter advice to assist with creating high performing and responsive queries. However, you'll often need to move beyond this introductory level of detail as your Cube increases in size, use, and COM Plexity.

The first step in the process was to review your existing aggregates. We'll again use the AdventureWorks DW SSAS database to review the existing aggregations and create new aggregations from Scratch. This database was available for download in CodePlex at:http://msftdbprodsamples.codeplex.com/releases/view/553 Once you has the SQL Server and OLAP AdventureWorks databases installed, you'll start SQL Server Data Tools (SSDT); SSDT is the new name of business Intelligence development Studio in SQL Server 2012.

We can now review the data aggregations so were previously created and then move to creating some new aggregations. In order to see the existing aggregations, you need to open the Adventrureworks Cube and then click on the Aggregation tab , as seen in the next screen print.

Before we move on, let us discuss a few concepts shown in the above screen print. First, on the left side, you see all your measure groups. The above instance, the Internet Sales measure group has 1 Aggregation Design. In it simplest form an Aggregation Design was like a container or folder for the embedded aggregations. These aggregation designs can be tied to one or more partitions, but to only one measure group. For the above example, the Internet sales aggregation design are tied to the Internet sales partition and contains Indiv Idual aggregations. If no aggregations has been defined then the aggregation design says "Unassigned aggregation design" as illustrated next.

Although it is not readily apparent, the other views aggregation the views are available and each enables different options and sett Ings. The default view is the standard view and is displayed next. You'll notice the first buttons is not grayed and is available to utilize. These buttons invoke the Aggregation Design Wizard and Usage Based Optimization Wizard. The standard view was invoked by clicking on the 4th button from the left.

The advanced view was implemented by clicking on the 5th button from the left (with the Sigma symbol), and is shown below. Notice how the Aggregation Design Wizard button and Usage Based Optimization Wizard button Both Dim when you move to T He advanced view, and the New Aggregation button, to the right of the standard view is available. The standard view definitely does no provide any details above the specific aggregation details, so we must move to the A dvanced view to see these details.

On the advanced view, as shown below, we need to select the measure group name, the Aggregation Design name, the sorting w  E would like to see and finally what range of aggregations we would like to display (steps 1 to 4). As the number of aggregations increases, the range field helps to manage the list and keep your from scrolling forever. You'll also notice in the below screens print, that's we actually get to see what dimension attributes make up each Aggrega Te One aggregate is setup for the Promotion Category while another if setup for the Discount Percent.

As an example, say after analyzing our usage patterns, we decide we need to add a aggregation by Product and Fiscal year As those items would make up a common query for the Internet Sales measure group. To complete the this task, you can right mouse click the aggregation detail area and select New aggregation.

The new aggregation becomes A0 while the remaining aggregations ' move ' one column to the right (note the numbering is zero Based). Now-to-create the aggregation, we simply click on the check box next to the dimension attributes which should is included In the aggregation and as shown below. Finally to complete the process and click on the Save button.

SSAS also provides "caution warning messages" in the status row. This warning stems from the fact that within the ship Date dimension, a hierarchy relationship already exists between the Date field and the Fiscal year field, and thus creating a aggregation with both these items would be redundant. Also Notice how next attribute the number of attribute values are listed which gives you an indication of the Aggre Gation size.

We can also create a new Aggregation design by clicking to the new Aggregation Design button, step 1 in the below screen p Rint. Then we define whether we want to copy an existing aggregation design or create a completely new one. Finally, the aggregation design needs to be named. As can preview in the second screen print below, the new aggregation design is added to the Internet Sales measure GR Oup.

Conclusion

Just like with SQL Server indexes, there is several layers of design available for setting up aggregations in SSAS. You can use the Aggregation Design Wizard and Usage Based Optimization Wizard to setup aggregations in just a few steps. However, SSAS also provides a method of reviewing and even adding individual aggregation designs (aggregation groups) and Individual aggregations. In order to create new aggregations, you must switch to the advanced view within the Cube Aggregation tab. Once on the Advanced view, individual dimension attributes can is added to your own custom aggregations which is tailored To your specific SSAS cube design and usage.

Next Steps

Check out these related resources:

Optimize a SQL Server analysis Services Measure Group Partition for performance

Improve SQL Server Analysis Services performance with the Usage Based optimization Wizard

SSAS aggregations and Impact on storage-http://technet.microsoft.com/en-us/library/ms174915 (v=sql.110). aspx

Original link

SSAS aggregation design boosts cube query performance (reprint)