Maintaining the Query Log in C#.NET

Generate QR in C#.NET Maintaining the Query Log

Suppose that you have accumulated a significant number of entries in the log for a cube. You then change the definition of the cube, which makes the entries in the query log no longer meaningful. Leaving those entries in the log as you perform usage-based optimization could

7:

lead to invalid optimization. If you no longer need the old log data, you can execute a DELETE query to remove records from the log for that one cube. If you do want to retain the old query logs, you can extract them from the log database or you can filter for only new log entries when optimizing aggregations. On the Usage Based Optimization Wizard s criteria screen, set the Beginning Date to the date the cube structure was last modified. In this procedure, you ll delete records from the query log for the Reseller Sales partition. Delete records from the query log 1. Switch to SQL Server Management Studio, and then click New Query on the toolbar. 2. Click Database Engine in the Server Type list box, and then click Connect. 3. Click ASQueryLog in the database drop-down list that appears on the toolbar. 4. In the Query window, type the following query:

DELETE FROM OlapQueryLog WHERE MSOLAP_Database = 'SSAS Step by Step'

5. Press the Execute button on the toolbar. Deleting records from the log has no effect on any aggregations designed by the UsageBased Optimization Wizard.

To Design aggregations Do this On the Partitions tab of the Cube Designer, right-click a partition, click Design Aggregations, specify object counts, select one of the three options for limiting aggregations, and then click Start. In the Aggregation Design Wizard, change the partition counts, and/or select one of three options: Design aggregations until the storage meets a certain size, until the performance gain meets a certain percentage, or until you click Stop. In Solution Explorer, click the Show All button, right-click the cube s partition file, and then click View Code. On the Cube Structure tab of the Cube Designer, click the attribute, change the AggregationUsage property to Full, run the Aggregation Design Wizard, and add a count for the attribute. On the Dimension Structure tab of the Dimension Designer, drag attributes to the Hierarchy and Levels pane in hierarchical order.

Inspect aggregation definitions Add an attribute to the pool of potential aggregations Create a user hierarchy

Part II:

Do this On the Dimension Structure tab of the Dimension Designer, create a direct attribute relationship between each pair of attributes from level to level (but only if the relationship also exists in the Data Source View), create a user hierarchy using these attributes, run the Aggregation Design Wizard, and add a count for each attribute. In SQL Server Management Studio, create a new database, connect to Analysis Services, right-click the server, click Properties, set the CreateQueryLogTable property to True, set the QueryLogConnectionString to localhost, using the query log database name, and set the QueryLogSampling to the desired sampling rate value. After creating a query log and allowing users to browse the cube for a period of time, on the Partition tab of the Cube Designer, right-click a partition row, click Usage Based Optimization, filter the query log if necessary, specify object counts, select one of the three options for limiting aggregations, and then click Start.

In this part: 8: Using MDX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .177 9: Exploring Special Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . .217 10: Interacting with Cubes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .245

In Part II, Design Fundamentals, you learned the basics for designing dimensions and measure groups to create a useful analytical solution with Analysis Services. You also learned how to design objects to handle certain unique financial analysis requirements. Throughout the chapters of Part III, you ll work with advanced design techniques to support sophisticated analysis on the one hand and to simplify access to analysis on the other hand. Later in Part IV, Production Management, you ll shift your focus from building a solution to implementing, managing, and maintaining that solution.