Data Warehousing and Business Intelligence

7 October 2010

Parser: The query contains … parameter, which is not declared

Filed under: Analysis Services — Vincent Rainardi @ 3:09 pm
Tags:

This error happens when a 2008 cube is deployed and processed on 2005 SSAS server.

The deploy itself is OK. The error happens when the cube (not the database, not the dims) when we do “process structure”. Process structure is part of “process full”, so the error also happens at the end of “process full”.

The calculation tab of BIDS 2008 has Display Folder and Associated Measure Group text boxes. These two are new in 2008 as 2005 doesn’t have them. However, I guess it’s for backward compatibility reason, the Calculation Properties dialog box 2008 BIDS still has Display Folder and Associated Measure Group and Description columns. So, if you open a 2005 cube project in 2008 BIDS, then fill in the Display Folder or Associated Measure Group, you make your cube incompatible with 2005.

When that cube is deployed and processed to a 2008 server, no problem. When that cube is then deployed to a 2005, no problem occurs. But when it is processed, this error message appears: “Parser: The query contains the … parameter, which is not declared.”

Hence it is important to ensure that your production, test, UAT, staging, development servers have the same version of SSAS. Having some of your servers in 2005 and some in 2008 is only looking for trouble in my opinion.

“But we can set it to be compatible with 2005”, some people say.  True, we do have “Deployment Server Version” in BIDS’ Configuration Properties dialog box:

And we can set it to 9.0 (2005) instead of 10.0 (2008), then deploy the cube to that 2005 production server and process it there. Very true, you can deploy it to 2005 server that way, but my question to you is: do you want to risk it? In Dev you use 2008 server and it works fine. In UAT/test you use 2008 server and it tested OK. But the Production you uses 2005 server and you don’t know whether it will work 100% OK or not. To me it sounds like a recipe for a trouble.

Yet some companies do that: dev in 2008, prod in 2005. Or worse: Prod in 2008, DR in 2005. I’d advise to upgrade all the AS servers in the same fortnight period. It’s 2010 now, 2 years after 2008 AS was released, and many companies have done it (upgrading 2005 AS to 2008). Any upgrade issues would have been logged on MS support and MSDN forums. And those upgrade issues would have been resolved by now. I myself did it (upgrade to 2008) during the first half of last year and it went smoothly.

You’ve got to test it of course. But you’ve got to test it quickly, i.e. upgrade Dev, deploy prod cubes there and test using your client tool (Excel, Strategy Companion, Tableau, ProClarity). Then upgrade the production servers.

If you use ProClarity which is not the latest version (6.3 SP3 = 6.32.2222), be careful. 2008 AS is not compatible with your ProClarity (say you use 6.2 or 6.3 RTM = 6.3.209). The issue is with the total. From memory I think ProClarity 6.2 uses “sum” MDX keyword instead of “aggregate”, which causes the total to be incorrect. Reading the KB article on SP2 and SP3 would give you the details of the issues fixed in each SP. Some of these issues are related to upgrading AS from 2005 to 2008.

As always I welcome questions and discussion on vrainardi@gmail.com

Hope this helps, Vincent Rainardi, 7/10/10.

5 Comments »

  1. If you don’t mind digging in the XMLA definition of the cube, there is another solution. I’ve found that this error typically occurs when there are custom calculations and KPIs defined in the cube that have an associated measure group defined.

    The solution is to script the cube as Alter, and find those calculations and KPIs. For each, there you will find a statement or tag “Associated Measure Group”. Delete these, and you’re good to go.

    Comment by Eric Page — 10 August 2011 @ 6:25 pm | Reply

  2. Hi,
    I created a sample SSAS Project from AdventureworksDW2008R2 datbase
    and created single dimension from DimGeography table.Create relationships and hierachies as Country>>State>>city>>PostalCode

    When I deploy this project it gives error that City=’Augsburg’
    is duplicate.but we can see that ‘Augsburg’ is in different states.

    SELECT *
    FROM DimGeography WHERE City=’Augsburg’

    Please help me as I am stuck in my first project

    Comment by Parvez Khan — 11 December 2012 @ 7:36 am | Reply

    • Hi Parvez,
      SSAS requires the KeyColumn to be unique. To make that, go to the properties of City attribute in DimGeography and find the KeyColumn property. Click the ellipsis and add State (so the KeyColumn is City+State). This “Composite Key” makes it unique.
      Note that you need to redefine the Name property (to City) as it will be wiped out.

      I think you need to make your learning systematic. I suggest you buy Sivakumar’s SSAS 2012 book and go through it systematically so you get a structured learning in SSAS. Sivakumar* described the solution for the above problem in detail on page 151.
      * coauthor with Ronald, Denny, John, Robert
      Of course you can also buy other SSAS book but make sure it’s 2012, not 2008 book because unlike 2008, 2012 is both tabular and multidimensional.
      I wish you all the best in your SSAS journey.

      Comment by Vincent Rainardi — 12 December 2012 @ 8:08 am | Reply

  3. Hi, Vincent Rainardi,
    I set the KeyColumn Property and it works.
    Thanks a lot for your precious suggestions……..!
    Currently I have a book Wrox SQL server Analysis services 2008 with mdx. I am doing study by this book. I will buy your suggested book also.

    I expect kind Guidence from you in future also.

    Comment by Parvez Khan — 13 December 2012 @ 6:57 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: