Readme for Aggregation Manager Sample

Aggregation Manager is a sample application that lets you view, design, edit, and add aggregations based on the information collected in the query log.

For more information about performance issues related to aggregations, see the section “Writing Efficient MDX” in the SQL Server 2005 Analysis Services Performance Guide. For information about setting up the query log, see Configuring the Analysis Services Query Log.

Scenario

The Aggregation Manager sample illustrates how to take advantage of various SQL Server 2005 Analysis Services features to handle aggregation design.

The sample can connect to an instance of Analysis Services and display a set of aggregation designs. It also displays the partitions that the aggregation design is applied to.

You can use the sample to display a set of aggregations in a particular aggregation design, and to change each aggregation as you want. Aggregation designs can be applied to any partition in the measure group.

You run the sample and connect to the query log table to import statistical information about Multidimensional Expressions (MDX) queries, to display the MDX queries, and to add new aggregations based on the displayed set.

Languages

C# (using the AMO object model)

Features

The Aggregation Manager sample uses the following new features of SQL Server 2005 Analysis Services.

Prerequisites

Before you run this sample:

1. Make sure that the following software is installed on the developer's computer:
   • Visual Studio 2005 with C# compiler or .Net Framework 2.0 SDK
   • SQL Server 2005 SP2 samples are installed
2. Make sure that the following services are started and available either in the local computer or another that you can administer:
   • SQL Server Analysis Services
   • SQL Server engine
3. Make sure that you have a signature file that is named AggManager.snk. To create a signature file follow the steps at Strong Name Tool (Sn.exe)
4. Configure Analysis Services to log queries, see Configuring the Analysis Services Query Log. Set the QueryLogSampling value to 1.
5. Make sure that you have run some queries against a cube you want to design aggregations on. If you do not have a suitable cube to run queries on, you can deploy the Adventure Works DW database and use the Adventure Works cube as a test case; see Running Setup to Install AdventureWorks Sample Databases and Samples.

Building the Sample

Option 1: To build the sample by using Visual Studio

1. Locate the folder where the solution file AggManager.sln is stored.

2. Move the signature file AggManager.snk to the solution folder.

3. Open Visual Studio.

4. In the Recent Projects tab, at the bottom where it says Open, click Project.

5. Open the folder where you saved the file AggManager.sln, select the file, and then click Open.

6. Press F6 to build the project.

Option 2: To build the sample by using msbuild.exe from .Net Framework SDK

1. Locate the folder where the solution file AggManager.sln is stored.

2. Move the signature file AggManager.snk to the solution folder.

3. Open a command prompt and change directory to the location where the solution file is located. Configure the environment according to the specifications in How to: Compile at the Command Prompt.

4. Type msbuild.exe AggManager.sln and press Enter.

Running the Sample

Before you run the sample you should have some query logs recorded in an SQL table; see prerequisites 4 and 5.

Step 1: To connect to Analysis Services

1. Start the sample either by pressing F5 or CTRL+F5 in Visual Studio with AggManager.sln opened, or by typing AggManager.exe and pressing Enter in a Command Prompt window opened where the executable file is located.

2. If you are connecting to the same server where you are running the executable, and the instance you are using is the default instance then you can leave localhost as is and click the Connect button. If you are connecting to a different server or a named instance, then you must replace localhost with the correct Server Name or <Server Name>\<Instance> value and then click the Connect button.

3. Click the treeview of the server and check that you can browse the databases, cubes, and other available objects.

Step 2: To connect to the query log

1. Connect to Analysis Services if you are not yet connected, see Step 1.

2. Expand the server object by clicking the + next to the server name, then and expand the Databases folder object.

3. Expand the database object that contains the cubes where you ran the queries that are logged, then expand the Cubes folder object.

4. Expand the cube object where the queries were run and are logged, then expand the Measure Groups folder object.

5. Expand a measure group object where you want aggregations to be analyzed and designed.

6. Right-click the No Aggregation Design folder object and select Add from query log. A new window opens.

7. Read the dimmed connection string in QL Server Connect text box.

Step 3: Creating new aggregation design, deleting aggregation design, associating aggregation design with partitions

1. To start creating new aggregation designs you must be connected to the query log database, see previous step.

2. In the Add Aggregations from Query Log window, assign a name to Aggregation Design Name and Aggregation Prefix. Aggregation Design Name is the base name of these current set of aggregations designs. Aggregation Prefix is the specific name of the new aggregation design that will be created.

3. If no records are available in the datagrid, click Execute SQL.

4. Click OK to create the new aggregation design and return to the Aggregation Manager window. Clicking Cancel will cancel current action, close the window, and return you to the Aggregation Manager window.

5. The newly created aggregation design now appears as part of the expanded node.

6. To delete one aggregation design, right-click the aggregation design and select delete on the shortcut menu.

7. To associate a partition to an aggregation design, right-click the intended design node and select Change Partitions.

8. On the partitions windows, select the desired partitions to be assigned to the intended design, and then click OK.

9. Expand the node and the partitions should now appear under the design.

Step 4: Editing aggregation design, viewing aggregation definition

1. Select the aggregation design you want to edit. If the right pane says Aggregation count with no value, then the design has no aggregations designed; follow the next steps to start a new aggregation design. If there are already aggregations designed, you can skip to number 8 and continue from there editing.

2. Right-click the aggregation design and select Add from query log on the shortcut menu opened.

3. Verify the connection string and adjust it accordingly.

4. Add Name and Prefix to this design.

5. Click the Execute SQL button.

6. If the query returned only one record with zeros and commas only then copy that record to the clipboard.

7. Click the OK button; a message box with Aggregation Design '<Your Aggregation Design Name here**>' updated with** n aggregations appears.

8. Select the aggregation design you want to edit, right-click it and select Edit on the shortcut menu.

9. If an empty datatgrid appears, you will need the string stored in the clipboard from numbers 2 to 7. Click the row under the Name column and write the name for this design; click the row under Aggregation Definition and paste the row of zeros and commas from clipboard; click under Type and write Flexible. Press TAB key to move to a new row. You now have an empty design created.

10. Select a row from the datagrid. You will know what row is selected by the little icon that appears to the left of the row. If a group of rows is selected only the row with the icon will be updated.

11. On the right pane appears the collection of all dimensions defined for the current measure group as a set of Trees. Each top node represents a dimension; checking or un-checking the dimension node has no effect on the aggregation design. Make sure that you have Show Attribute Relationships selected; it is located exactly below the title bar of the window. The tree structure represents the attribute relationship starting from the key attribute as the first node level; next level of attributes are those that directly depend on the key or those that are directly related to the key; next level will contain attributes directly related to the father attribute by an attribute relationship definition and similar for the descendant levels. The string of zeros, ones and commas is a map representation of the dimensions tree structure. The comma character is the dimensions separator; zeros and ones represent each a dimension attribute. The string maps left to right the nodes top to down; each selected dimension attribute node appears in the string as a 1 and those unselected appears as a 0 in the string. The lowers levels of granularity are those closer to the top of the tree.

12. To modify a design you have to select or unselect nodes in the right pane, for a given row, and changes will be reflected in the string; working on the string will also be reflected on the tree structure.

    Note

    Shortening the length of the string will make the application crash.

13. Once you are done with your edits you can press OK to save work.

14. Your work is saved in memory but has not been applied to the server. You can distinguish which designs are pending to be applied to the servers by the suffix ** modified** appended to the measure group name.

15. To save your changes to the server right-click on the measure group and select Save to server.

Step 5: Eliminating redundant attributes from the aggregations in the aggregation design

1. Open an Edit Aggregations Aggregation Design window by right-clicking on the design node and selecting Edit.

2. To eliminate redundant attributes from and aggregation design click the Eliminate Redundancy button. This will remove all attributes that are redundant in the current design; leaving the design with fewer attributes but the significant ones.

Step 6: Eliminating duplicate aggregations

1. Open an Edit Aggregations Aggregation Design window by right-clicking on the design node and selecting Edit.

2. To eliminate duplicate designs from and aggregation design, click the Eliminate Duplicates button. This will remove all duplicated rows from the current design; leaving the design with fewer rows but the significant ones.

Removing the Sample

To remove the Aggregation Manager sample

• Delete the folder that contains the solution and all descendant folders.