locked
Goal Seek always produces a Parser error message RRS feed

  • Question

  • I am using the SQL Server Analysis Services add-in for Microsoft Excel with the Adventure Works database (used in the Microsoft tutorial) that comes with SQL Server and which is also downloadable from Microsoft.  When I click within the tabel in the "Table Analysis Tools Sample" worksheet, the "Table Tools" ribbon opens, and I click on "Analyze" and then select the icon for "Scenario Analysis" and choose "Goal Seek."  Then, no matter how I try to do the goal seek, I always get the same error message: 

    Query (1, 46) Parser: The syntax for 'value' is incorrect.

    By the way, I have Googled this error and searched Microsoft forums and can see others have reported this problem but do not see anybody posting a solution.  For instance, see this:

    http://social.msdn.microsoft.com/Forums/en/sqldatamining/thread/1c4db606-86c8-4d84-b27e-33ccabce6da0

     

    Thank you for your help!

    Robert

     

     

    • Moved by Darren GosbellMVP Monday, October 3, 2011 3:16 AM This is a data mining question (From:SQL Server Analysis Services)
    Friday, September 30, 2011 5:45 PM

All replies

  • The data mining tools will be taking the data from Excel and firing off DMX statements. This error may be an indication of some strange value in your data. You could try running SQL profiler while you try to do the goal seek to see if you can capture the exact statement that is causing the issue. This may help suggest a fix.
    http://darren.gosbell.com - please mark correct answers
    • Proposed as answer by Bogdan Crivat Monday, October 3, 2011 6:01 AM
    Monday, October 3, 2011 3:16 AM
  • Thanks, Darren. I'll look up SQL Profiler and give it a try.

    Still, considering that I'm using the Microsoft's own AdventureWorks sample database, used by by both Microsoft tutorials as well as many 3rd party tutorials, it seems odd that there could be something so odd in the data.  (In fact, the query I'm attempting is directly out of the Data Mining with Microsoft SQL Server 2008 book published by Wiley).

    I'll let you know what I find.

    Thanks,

    Rob

    Monday, October 3, 2011 12:37 PM
  • I've tried SQL Profiler, but don't know what to make of the result.  (Keep in mind, I'm focusing on the Excel Add-in to connect to SQL Analysis Services since I don't yet understand or have any training in the DMX language and am a complete newbie even in terms of SQL.)

    In the trace, I see the following:

    1. A "DISCOVER_PROPERTIES" EventSubclass with the following:

    <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
              <PropertyName>Catalog</PropertyName>
            </RestrictionList>

    <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis">
              <Catalog>Adventure Works DW 2008</Catalog>
              <LocaleIdentifier>1033</LocaleIdentifier>
              <Content>SchemaData</Content>
              <Format>Tabular</Format>
            </PropertyList>

    2. A "DISCOVER_SCHEMA_ROWSETS" EventSubclass and a "DBSCHEMA_CATALOGSA" EventSubclass, both with the following:

    <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis">
              <Catalog>Adventure Works DW 2008</Catalog>
              <LocaleIdentifier>1033</LocaleIdentifier>
              <Content>SchemaData</Content>
              <Format>Tabular</Format>
            </PropertyList>

    3. A "DISCOVER_PROPERTIES" EventSubclass with the following:

    <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
              <PropertyName>Catalog</PropertyName>
            </RestrictionList>

    <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis">
              <Catalog>Adventure Works DW 2008</Catalog>
              <LocaleIdentifier>1033</LocaleIdentifier>
              <Content>SchemaData</Content>
              <Format>Tabular</Format>
            </PropertyList>

    4. A "DMSCHEMA_MINING_MODELS" EventSubclass with the following:

    <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis">
              <Catalog>Adventure Works DW 2008</Catalog>
              <LocaleIdentifier>1033</LocaleIdentifier>
              <Content>SchemaData</Content>
              <Format>Tabular</Format>
            </PropertyList>

    5. A "DMXQuery" EventSubclass that has the following for the "Query Begin":

    select [Purchased Bike], PredictProbability([Purchased Bike], @stateValue) FROM [Table2_503848_Purchased _595939] NATURAL PREDICTION JOIN @paramTable AS T

    <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis">
              <Catalog>Adventure Works DW 2008</Catalog>
              <LocaleIdentifier>1033</LocaleIdentifier>
              <Format>Tabular</Format>
              <Content>SchemaData</Content>
            </PropertyList>

    ... and then has the following for the "Query End":

    select [Purchased Bike], PredictProbability([Purchased Bike], @stateValue) FROM [Table2_503848_Purchased _595939] NATURAL PREDICTION JOIN @paramTable AS T

    6. Finally, this is followed by the ERROR EventClass, which has the following:

    Query (1, 46) Parser: The syntax for 'value' is incorrect.

    Query Text:
    SELECT PredictProbability([Purchased Bike], @value) FROM [Table2_503848_Purchased _595939]

    ----

    I can't make heads or tails of this, though I do find it curious that "@stateValue" in the SELECT that was submitted somehow changed to "@value" in the error message.  (I thought the whole purpose of the Excel add-in was to provide some of these Analysis Services capabilities to people who don't know the query language! :) )

     

    Do you see anything amiss?

    Any help would be greatly appreciated.

    Thanks again,

    Rob

    Monday, October 3, 2011 1:15 PM
  • Hi Rob,

    Do you have the Request Parameters column into your profiler trace in order to see the values for @stateValue and @paramTable?

    Regards
    Jerry

    Tuesday, October 4, 2011 8:54 AM
  • I'm not sure how to do this. 

    I'm trying to figure out why I'm getting this odd error message when I try to use "Goal Seek" from the Analysis Server add-in for Microsoft Excel.  I'm going in through that route because I don't know DMX and am a complete newbie in SQL.

    Per the earlier suggestion, I tried starting a trace before doing the Goal Seek by clicking on START > All Programs > Microsoft SQL Server 2008 R2 > Performance Tools > SQL Server Profiler and then, from within the SQL Server Profiler, I clicked on File > New Trace.  From here, I don't see how to specify "Request Parameters."

    When I did a Google search on "Request Parameters," I see a reference to it when a trace is initiated via syntax in a block of SQL code, but I don't have a deep enough understanding of SQL to see how to do that in conjunction with the Excel add-in that I'm trying to use.

    As a side question, I'd be interested to get opinions as to whether the Analysis Server functionality is really "ready for prime time," or if it is more of a "beta" sort of functionality.  It scares me that I can't even get very far through the tutorial before running into issues like this.  My worry is that if even the tutorial doesn't work smoothely, what are the odds I could get it to work reliably for a real project?

    Thanks, once again, to all for your help!

    Rob

    Tuesday, October 4, 2011 12:55 PM
  • Hi Rob,

    From the queries captured by profiler trace, the issue is associated with @value because the "Value" is reserved word for DMX, so the parser failed with the error. If the parameter @value was generated automatically by the add-in (from your posts, it should be) , you could open a ticket with Microsoft Customer Services for quick solution or submit a feedback on Microsoft connect website to wait for a response.

    thanks,
    Jerry

    Friday, October 7, 2011 6:41 AM
  • Thanks, Jerry!
    Friday, October 7, 2011 12:21 PM
  • Rob -

    I am having the same problem when trying to follow the tutorial for the Scenario Analysis tool. I was following your thread here, and wonder if you ever received an answer?

    Cheers,

    Floyd

    Monday, November 7, 2011 1:02 AM
  • Floyd,

    Regrettably, no.  My explorations into this tool were taking so much time that I was falling behind in my work. The last advice received was to open a ticket with Microsoft Customer Service and provide the info related earlier in this chain, and I still intend to do that when I get a chance.  This still seems like a useful tool, if I could get it to work.

    In the meantime, if you make any progress, please let me know.

    Thanks,

    Rob

    Monday, November 7, 2011 1:45 PM
  • Rob,

    Tried working with a fresh copy of the Sample Data but that didn't work either and neither does it work when you pull in data from another source.This was the same issue with the What If tool..It could well do with alterations made to the Data through running some of the other Table Analysis Tools

    What I did was run the same Goal Seek and WhatIf icons on a different system and they worked just fine..remember to save as under a different filename after running each tool considering they would alter the original sample data file with extra columns.

    See System specs on both machines

    PC doesn't work..Windows 7(64bit)-Office 2010- PowerPivot all in 32 bits (PPVT doesn't connect to database)-Visual Studio 2010

    PC that works Windows 7(64 bits)- Office 2007 32 bits-Visual Studio 2008  

    It's a great tool for those that wouldn't need to get into the nitty gritty of knowing Data Mining algorithms.

    Moreover if you installed the Data Mining client for MS Excel..you can use the Trace Option on the Data Mining menu to see all requests sent from the Table Analysis Tools to Analysis Services.

    You can also use the Browse option to explore the contents of the running models created by the Table Analysis Tools



    • Edited by ASTSQL Tuesday, November 22, 2011 3:19 PM
    Tuesday, November 22, 2011 2:30 PM
  • Are you connecting to an SSAS 2008 R2 server?

    The word "value" is a reserved keyword in MDX and as such cannot be used as a parameter name in a query on an SQL Server 2008 R2 Analysis Services server.  This change was introduced in 2008 R2 as part of the integration of the DAX query language for PowerPivot and inadvertently broke the data mining add-ins since they generate DMX statements with parameters that may match MDX reserved keywords.

    The data mining add-ins will still work against a SQL Server 2008 server, but for now may generate errors against a SQL Server 2008 R2 server.  A fix is planned to resolve this error by relaxing the keyword parameter matching restrtictions on SQL Server 2008 R2. The fix will be released in an upcoming Cumulative Update for 2008 R2 SP1.

    Monday, November 28, 2011 6:15 PM
  • and the solution? one year has passed and nothing!!
    Friday, November 23, 2012 2:50 AM
  • This was fixed in CU4 for SQL server 2008 R2 Service Pack 1 available at http://support.microsoft.com/kb/2633146

    If you are still encountering the problem on SP1 CU4 or later, please open a case with Microsoft Support to investigate further.

    Monday, November 26, 2012 4:47 PM