Re: UBO - Wizard fails because of Group or Order By clause(s)

Tech-Archive recommends: Fix windows errors by optimizing your registry

Well, did the unthinkable and changed the datatype from nvarchar to varchar
on the OlapQueryLog table and low and behold, the wizard now works again
(well, probably more accurately the SQL being passed is now
validated/accepted by SQL server).

I'm still going with 'this is a bug'.

Cheers,

Steve.


"stevefromoz" <steven_h_allen@xxxxxxxxxxxxxxxxxxxxx> wrote in message
news:OJ$4slY6GHA.3760@xxxxxxxxxxxxxxxxxxxxxxx
On thinking about this and looking at the table structure again, am now
thinking this could be a bug. The field types for the MSOlap_ObjectPath
and DataSet fields are both nvarchar, which probably means that they're
double-byte in size, so both of them being a size of 4000 may really mean
that their both 8000 bytes, hence the total would be 16k bytes, well above
the 8000 byte SQL limit.

Your thoughts??

Steve.


"stevefromoz" <steven_h_allen@xxxxxxxxxxxxxxxxxxxxx> wrote in message
news:uQmlnoX6GHA.3620@xxxxxxxxxxxxxxxxxxxxxxx
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.06&EvtSrc=Microsoft.SqlServer.Management.UI.WizardFrameworkErrorSR&EvtID=UncaughtException&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
*********************






.

Relevant Pages

  • UBO - Wizard fails because of Group or Order By clause(s)
    ... Have hit a snag trying to use Usage Based Optimization (64bit AS 2K5 ... Query Analyzer) does return a resultset. ... This wizard will close because it encountered the following error: ... (Microsoft SQL Server) ...
    (microsoft.public.sqlserver.datawarehouse)
  • Re: Adding Tables To Forms
    ... The only way you can select multiple tables in the Form ... Wizard is if a relationship between them is defined in the Relationships ... SQL and include a JOIN keyword) is instead a simple table. ... this query produces, redesign your form (using the Form Wizard if you must! ...
    (microsoft.public.access.forms)
  • Re: union query
    ... There isn't a good wizard for creating a union query. ... two queries seperate and then copy and paste the SQL into a new query. ...
    (microsoft.public.access.queries)
  • Re: Editing query error message
    ... > query using the query wizard. ... > be edited by the Query Wizard". ... I have rebuilt the actual ... think it means the SQL is too complex for the wizard to deconstruct. ...
    (microsoft.public.excel.crashesgpfs)
  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)