Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jul 10, 2006

Introduction to the Analysis Services 2005 Query Log - Page 4

By William Pearson

Process the Database Clone and Generate Statistics to be Captured by the Query Log

We need some log entries upon which to base an examination of the Query Log, 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.  Within Object Explorer, once again, right-click the new Analysis Services database, ANSYS049 Adventure Works DW, which we created earlier. (The database can be found within the Databases folder of the Analysis Server instance.)

2.  Select Process from the context menu that appears, as shown in Illustration 20.


Illustration 20: Processing the Clone Analysis Services Database ...

The Process Database dialog appears for Analysis Services database ANSYS049 Adventure Works DW, as depicted in Illustration 21.


Illustration 21: The Process Database Dialog Appears

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.


Illustration 22: Successful Processing Completion is Indicated ...

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 ANSYS049 Adventure Works DW, as necessary, by clicking the "+" sign to its immediate left within Object Explorer.

6.  Expand the Cubes folder that appears within the ANSYS049 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.


Illustration 23: Select Browse from the Context Menu ...

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.


Illustration 24: Drag and Drop the Measure into the Data Pane ...

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.


Illustration 25: Drag and Drop Calendar Year into the Column Fields of the Data Pane ...

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.


Illustration 26: Drag and Drop Product Categories into the Row Fields of the Data Pane ...

The Data pane appears, after our insertions, as partially depicted in Illustration 27.


Illustration 27: Our Initial Browse in the Data Pane (Partial View)

20.  Drill down on each of the Product Categories by clicking the "+" sign to its immediate left, as shown in Illustration 28.


Illustration 28: Drilling Down on Product Categories ...

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.


Illustration 29: Drilling Down on Select Date Dimension Levels ...

23.  In a manner similar to the steps above, perform several browses involving different dimensions / dimension hierarchies and measures.

Having provided for the collection of a representative set of query statistics, we can now get some exposure to the contents of the Query Log.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM