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

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

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

  • 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)
  • Re: Populating a list -- table structure?
    ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
    (microsoft.public.access.forms)
  • How do I do Paging through a large dataset via Stored Procedures
    ... Paging by dynamically altering the SQL Query ... Create stored procedures ... SELECT * FROM STUDENTS ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Populating a list -- table structure?
    ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... I have one report complete, ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)
  • Re: Populating a list -- learning Access
    ... It is kinda funny -- I started using databases in the early 80's with dBase and, for years, never knew that I knew SQL! ... If you are on a form or report, the most important property is the NAME, because that is how you refer to it in code. ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)