Hi Dev guys,

Have hit a snag trying to use Usage Based Optimization (64bit AS 2K5
install, olapquerylog on SQL2K 32bit).  Going through the wizard I can
select the partition(s) I'm interested in applying the aggregation design to
but on the next screen (filter screen for the queries in the query log) as
soon as I click 'Next' I get the following error.  Below the error I've
pasted the two queries that are being passed from AS2K5 to the SQL instance
that holds the log table.  The first runs without issue, it's the second one
with the 'option (Robust Plan) ' option that is causing the problem.  For
some reason it believes that the total length of the columns in the (I'm
assuming) GROUP BY is greater than 8000 bytes.  Seems like an impossibility
seeing as the two fields are both 4000 bytes each (unless 4000 + 4000 =
number > 8000!?!?!).  Removing the option (test running the querying in ol'
Query Analyzer) does return a resultset (also pasted below).

Any workarounds (other than writing up a quick winforms app to apply the UBO
aggregations myself)?

Cheers,

Steve.

===================================

This wizard will close because it encountered the following error:
(Microsoft SQL Server)

------------------------------
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.0
6&EvtSrc=Microsoft.SqlServer.Management.UI.WizardFrameworkErrorSR&EvtID=Uncaught
Exception&LinkId=20476

===================================

Warning: The query processor could not produce a query plan from the
optimizer because the total length of all the columns in the GROUP BY or
ORDER BY clause exceeds 8000 bytes.  Resubmit your query without the ROBUST
PLAN hint. (Microsoft SQL Native Client)

------------------------------
Program Location:

  at System.Data.OleDb.OleDbDataReader.ProcessResults(OleDbHResult hr)
  at System.Data.OleDb.OleDbDataReader.NextResult()
  at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior
behavior, String method)
  at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
  at System.Data.OleDb.OleDbCommand.ExecuteDbDataReader(CommandBehavior
behavior)
  at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
  at Microsoft.DataWarehouse.Design.DataSourceConnection.Fill(DataSet
dataSet, String sql, CommandBehavior commandBehavior)
  at Microsoft.DataWarehouse.Design.DataSourceConnection.Fill(DataSet
dataSet, String sql)
  at Microsoft.AnalysisServices.Wizards.ReviewQueriesPage.LoadGrid()
  at
Microsoft.AnalysisServices.Wizards.ReviewQueriesPage.OnEnterPage(EventArgs
e)
  at Microsoft.SqlServer.Management.UI.WizardPage.RaiseEnterPage()
  at Microsoft.SqlServer.Management.UI.WizardForm.NextPage(WizardPage
nextPage)
  at Microsoft.SqlServer.Management.UI.WizardForm.Next_Click(Object sender,
EventArgs e)

******************** END OF ERROR BIT *********************

******************** Start of first traced query from AS2K5 to SQL2K
(querylog) *********************

Select  Count( MSOLAP_ObjectPath ),  Count( Distinct MSOLAP_User ),
Count( Distinct Dataset ),  Avg(Duration ),  Min(StartTime ),
Max(StartTime )   From  [OlapQueryLog]  Where  ( [MSOLAP_Database] =
N'Lineitem' )    And  ( [MSOLAP_ObjectPath] =
N'GRMOLAP01.Lineitem.CUBE_Lineitem.vw_Fact_COLineitem_1' )

******************** End of first traced query from AS2K5 to SQL2K
(querylog) *********************

******************** Start of second traced query from AS2K5 to SQL2K
(querylog) *********************
*******************************************************************************************
***********************************This is the offending query
************************************
*******************************************************************************************

   SELECT [MSOLAP_ObjectPath],[Dataset],Count(MSOLAP_ObjectPath) AS
[CountOfObjectPath],Avg(Duration) AS [AverageOfDuration]
 FROM [dbo].[OlapQueryLog] AS [UBO_QueryTableAlias]
 WHERE  ( [MSOLAP_Database] = N'Lineitem' )  And  ( [MSOLAP_ObjectPath] =
N'GRMOLAP01.Lineitem.CUBE_Lineitem.vw_Fact_COLineitem_1' )  Group by
MSOLAP_ObjectPath, Dataset  Order by CountOfObjectPath Desc  Option (Robust
Plan)

******************** End of second traced query from AS2K5 to SQL2K
(querylog) *********************

********************  Start of resultset from querylog, modified the object
names to protect the innocent :P  *********************
AServer.AnASDatabase.AnASCube.AnASMeasureGroup
00000,000,0000,000000000000,00,000 171 0
AServer.AnASDatabase.AnASCube.AnASMeasureGroup
00000,000,0000,000001000000,00,000 91 1
AServer.AnASDatabase.AnASCube.AnASMeasureGroup
00000,000,0000,000001000000,01,000 73 10
AServer.AnASDatabase.AnASCube.AnASMeasureGroup
00001,000,0000,000000000000,00,000 22 17
AServer.AnASDatabase.AnASCube.AnASMeasureGroup
00001,000,0000,000001000000,00,000 20 259
AServer.AnASDatabase.AnASCube.AnASMeasureGroup
00000,001,0000,000001000000,00,000 20 27663
AServer.AnASDatabase.AnASCube.AnASMeasureGroup
00000,001,0000,000000000000,00,000 18 0
AServer.AnASDatabase.AnASCube.AnASMeasureGroup
00000,000,0000,000001010000,00,000 12 0
AServer.AnASDatabase.AnASCube.AnASMeasureGroup
00000,000,0000,000001010000,01,000 8 7281
AServer.AnASDatabase.AnASCube.AnASMeasureGroup
00000,001,0000,000001000000,01,000 4 2847
AServer.AnASDatabase.AnASCube.AnASMeasureGroup
00000,001,0000,000001010000,00,000 4 16199
AServer.AnASDatabase.AnASCube.AnASMeasureGroup
00000,001,0000,000001010001,00,000 4 16324
AServer.AnASDatabase.AnASCube.AnASMeasureGroup
00000,000,0000,000000000000,01,000 4 19
********************  End of resultset from querylog *********************