Usage-Based Optimization in Analysis Services 2005 - Page 5
January 9, 2006
Process the Database Clone and Generate Statistics to be Captured by the Query Log
We need some log entries upon which to base the work of the Usage-Based Optimization Wizard in our practice example, so we will populate the table with some rows in a "quick and dirty" manner by doing quick manipulations of the data in the cube. First, we will process the new Analysis Services database copy we have created.
1. Right-click the new Analysis Services database, ANSYS043 Adventure Works DW, which we created earlier. (The database can be found within the Databases folder of the Analysis Server instance, within Object Explorer.)
2. Select Process from the context menu that appears, as shown in Illustration 20.
Click for larger image
The Process Database dialog appears for Analysis Services database ANSYS043 Adventure Works DW, as depicted in Illustration 21.
Click for larger image
3. Leaving all settings on the dialog at default, click OK to begin processing.
The Process Progress viewer appears, logging the events of database processing as they occur. Once all database objects are processed, we receive a Process Succeeded message in the Status bar at the bottom of the viewer, as shown in Illustration 22.
4. Click the Close button to dismiss the Process Progress viewer.
We are returned to the SQL Server Management Studio, where next we will perform a few actions to generate statistics in the Query Log table.
5. Expand the Analysis Services database ANSYS043 Adventure Works DW, by clicking the "+" sign to its immediate left within Object Explorer.
6. Expand the Cubes folder that appears within the ANSYS043 Adventure Works DW tree.
7. Right-click the Adventure Works cube that appears within the Cubes folder.
8. Select Browse from the context menu that appears, as depicted in Illustration 23.
The Adventure Works [Browse] tab appears within SQL Server Management Studio. Here we will do a few browses to generate sample query statistics within the Query Log.
9. Expand Measures within the Metadata pane by clicking the "+" sign to its immediate left.
10. Expand the Internet Sales measure folder that appears underneath the expanded Measures level.
The measures related to Internet Sales appear.
11. Drag measure Internet Sales Amount into the Data pane, dropping it into the area marked Drop Totals or Detail Fields Here, as shown in Illustration 24.
12. Drag measure Internet Order Quantity from the Metadata pane to the Data pane, dropping it to the right of the Internet Sales Amount.
13. Expand the Date dimension within the Metadata pane.
14. Expand the Calendar folder that appears underneath the expanded Date dimension.
15. Expand the Date.Calendar hierarchy that appears within the expanded Calendar folder.
16. Drag member Calendar Year into the Data pane, dropping it onto the area marked Drop Column Fields Here, as depicted in Illustration 25.
17. Expand the Product dimension within the Metadata pane.
18. Expand the Product Categories hierarchy that appears underneath the expanded Product dimension.
19. Drag the Category level, appearing underneath the Product Categories hierarchy, into the Data pane, dropping it onto the area marked Drop Row Fields Here, as shown in Illustration 26.
The Data pane appears, after our insertions, as partially depicted in Illustration 27.
20. Drill down on each of the Product Categories by clicking the "+" sign to its immediate left, as shown in Illustration 28.
21. Drill down on the Calendar Year CY 2003, to display the bi-annual levels H1 CY 2003 and H2 CY 2003.
22. Drill down on level H2 CY 2003 one level further, to expose the underlying quarterly levels Q3 CY 2003 and Q4 CY 2003, as depicted in Illustration 29.
23. In a manner similar to the steps above, perform several browses involving different dimensions / dimension hierarchies and measures.
24. Close the Adventure Works [Browse] tab, when finished.
Having provided for the collection of a representative set of query statistics, we can now call the Usage-Based Optimization Wizard into action and get some exposure to its operation.