Designing Aggregations and Hierarchies in Visual C#

Encode QR in Visual C# Designing Aggregations and Hierarchies

Your screen looks similar to this:

You have now created aggregations for your cube in several ways. Some aggregations were designed based upon the structure of the cube, using the counts of cube objects like the measure groups and the granularity attribute for the dimension. Other aggregations were built because you changed the AttributeUsage property to add an attribute to the pool of aggregations. Lastly, you created a user hierarchy for which there is a chain of attribute relationships from one attribute to the next.

The Aggregation Design Wizard selects aggregations from a pool of potential aggregations. As you learned previously in this chapter, the default pool of potential aggregations includes all the granularity attributes from each dimension. You can increase this pool by changing attribute properties and adding user hierarchies from related attributes. This design strategy is good for a new cube that you are putting into production, but it might not be optimized for your users. The best way to design optimized aggregations is to consider the actual data that users request from the cube and the frequency with which they request it after they ve had a chance to use the cube for a while. One of the most powerful features of Analysis Services is usage-based optimization. It allows you to select aggregations based on actual queries submitted to the server that is, on the usage of real-world users. The Usage-Based Optimization Wizard behaves exactly like the Aggregation Design Wizard except that it factors into the equation the actual usage patterns.

Part II:

Usage patterns come from the query log. The query log can be stored in a relational database or in a file located in a folder of your choosing. By default, the server logs 1 out of 10 queries. If you want to experiment with usage-based optimization, increase the sampling frequency temporarily to make it easier to add entries to the log. You can change the sampling frequency in Microsoft SQL Server Management Studio by changing a property of the Analysis server. In this procedure, you ll create a query log and execute queries to populate the query log with entries. Populate the query log 1. Open Microsoft SQL Server Management Studio, click Database Engine in the Server Type box, ensure the server name is correct, and then click Connect. 2. In the Object Explorer window, right-click the Databases folder, and then click New Database. 3. Type a name for the database, ASQueryLog, and then click OK. Next, you will connect to Analysis Services to start the logging process. 4. In the Object Explorer, click Connect, click Analysis Services, ensure the server name is correct, and then click Connect. 5. Right-click the Analysis Server in the Object Explorer window, and then click Properties. 6. Locate the Log \ QueryLog \ CreateQueryLogTable property, and then select True in the Value drop-down list for this property. 7. Click the button in the Value box for the Log \ QueryLog \ QueryLogConnectionString property, type localhost in the Server Name box, select Use Windows Authentication, select ASQueryLog in the Select Or Enter A Database Name drop-down list, and then click OK. 8. Type 1 in the Value box for the Log \ QueryLog \ QueryLogSampling property.

7:

Your screen looks like this:

This value will create an entry in the log for each query to an Analysis Services database. Generally, this frequency of sampling would be excessive, but for now, you need to quickly build up data for the Usage-Based Optimization Wizard. Notice that the Log \ QueryLog \ QueryLogTableName property defaults to OlapQueryLog. When you close the Properties, the table called OlapQueryLog is added to the ASQueryLog database that you created. Of course, the table name can be edited if you prefer. 9. Click OK. 10. Right-click the Databases folder for the SQL Server, click Refresh, expand the Databases folder, expand ASQueryLog, expand Tables, right-click dbo.OlapQueryLog, and then click Open Table. The table is currently empty. Now you will perform queries against the cube to log some usage. Each query that you perform will create a new record in the ASQueryLog database. Later, the Usage-Based Optimization Wizard will analyze this usage to make optimization recommendations for aggregations. 11. Keep the table open in SQL Server Management Studio, expand the Databases folder for the Analysis Server, expand the SSAS Step by Step folder, expand the Cubes folder, rightclick SSAS 7, click Process to apply the designed aggregations, click OK, and then click Close. 12. Right-click SSAS 7, and then click Browse.

Part II: