Usage-based optimization in AS 2005

Usage-based optimization in AS 2005

• nixnutz

  Unlike AS2K which has the query log turned on by default, in AS2K5 you must first enable it. Sounds like that is where the errors is -- either in getting data from it (if you have already enabled it) or you haven't enabled it.

  _-_-_ Dave

   

  
  
  

• Abu Ismail

  Well, maybe users didn't actually submit any queries to the cube, which would create usage information in the OLAPQueryLog.- this was mentioned after step 5:

  >>

  Now that you have created your query log table, you can send queries to your OLAP cubes ...

  >>

  Typically, you would only run Usage-Based Optimization after you have applied a representative set of user queries (which may be generated by OLAP client tools, like Excel pivot table). The data collected in OLAPQueryLog could then be used...

  
  
  

• t4ure4n

   

  Sorry. Mayber I should describe more detail.

  After 5 steps, I have created query log table named "OLAPQueryLog" in database.

  Then, I browse cube and drag dimensions, measure to pivot table in SQL Management Studio. I also use MDX query to query cube in SQL Management Studio.  (I think the above action should be submit query to the cube.)

  But I open the query log table (OLAPQueryLog), there still is no data exists in the table.

  Actually, the data should be generated in the query log table when I use MDX query to query cube in SQL Management Studio, isn't it

   

  Deepak Puri wrote:

  Well, maybe users didn't actually submit any queries to the cube, which would create usage information in the OLAPQueryLog.- this was mentioned after step 5: >> Now that you have created your query log table, you can send queries to your OLAP cubes ... >> Typically, you would only run Usage-Based Optimization after you have applied a representative set of user queries (which may be generated by OLAP client tools, like Excel pivot table). The data collected in OLAPQueryLog could then be used...

  
  

• FireWisp

  This is an awesome feature. However, how do I get the OlapQueryLog table to a different schema On table creation, it defaults to dbo. I want to create the table on an "admin" schema, i.e. admin.OlapQueryLog.

  I tried to put in admin.OlapQueryLog in the table name property, but this just makes a table called dbo.admin.OlapQueryLog.

  Thanks for your help!

  Jason

  Houston Mortgage Broker

  
  

• Victoria Helen Tierney

   

    thank for responding...

    But there is something problem...

    I set 5 steps about "Log\QueryLog" then there is a table name 'OLAPQueryLog' in my database. (There is no data exist in table OLAPQueryLog )

    When I open Usage-based Optimization Wizard, there is a warning message: "There are no queries in the log for selected measure group."

    Is something wrong Would I append queries data into table manually

  thanks,

  
  

• RussP

  Denny Lee's blog explains how to set up the OLAPQueryLog DB:

  http://spaces.msn.com/members/denster/Blog/cns!1pvF91EMBBNxnTGlLGUgGehg!297.entry

  >>

  November 23

  Analysis Services 2005 Usage Based Optimization and OlapQueryLog

  The Usage-based Optimization feature is alive and well within SQL Server 2005 Analysis Services (AS2k5).  But by default, the QueryLog feature (which samples and records the queries sent to AS2k5 is turned off.  To turn it on, you will need to: 1) Create a SQL database (e.g. QueryLog) on the Analysis Services server (Actually, you can put it on another SQL server if you like, but the preferred option is to place it on the SQL server on the box).   2) Through SQL Management Studio, connect to your Analysis Services, right click, and click on "Properties".   3) Within the Analysis Services Properties dialog, scroll down the properties, and first look for: Log \ QueryLog \ QueryLogConnectionString   Click on the Value column and it will provide you a dialog to point the connection string to the SQL database.  Point to the SQL dB (e.g. QueryLog) that you had just created.   4) Then scroll to the property Log \ QueryLog \ CreateQueryLogTable   Flip the Value column from false to true.   5) Click on OK and then you will have created the OlapQueryLog table within the QueryLog (if that's what you called it) database.  The name of the table is actually the parameter: Log \ QueryLog \ QueryLogTableName     Now that you have created your query log table, you can send queries to your OLAP cubes and then when its time to perform usage-based optimization: 1) Connect to your Olap cube via the SQL Management Studio   2) Go down the hierarchy of Measure Group > Partition, right-click the partition in question and then you will be provided the "Usage-based Optimization Wizard".   From there, you can use that wizard to optimize your Olap cubes.  The "SQL Server Books Online" provides good reference material for the wizard and can view some of the screenshots of it right now at Mosha's blog at: hthttp://www.sqljunkies.com/WebLog/mosha/archive/2005/06/23/usage_based_optimization.aspxwww.sqljunkies.com/WebLog/mosha/archive/2005/06/23/usage_based_optimization.aspx >>

   

  
  
  
• Usage-based optimization in AS 2005