This is the fourth in a series of posts on some SQL Server Analysis Services (SSAS) errors for which there is apparently little, if any, documentation anywhere on the planet. This particular post covers material for which the documentation is generally better than that discussed in previous posts; it is provided anyways though in the hope that it will help other developers track down errors a little faster. For an introduction to the compendium, see http://multidimensionalmayhem.wordpress.com/2011/09/21/an-informal-compendium-of-ssas-errors-part-1-introduction-and-data-mining-errors/. I had originally planned to post this way back on Saturday, Oct. 15 and apologize for the delay. I’m shooting for next Saturday, Nov. 19 for the final installment, An Informal Compendium of SSAS Errors, Part 6: Common MDX Syntax Errors.
This post contains information in a table format that may not render clearly if viewed at SQL Server Central because of the wider column style used at that site. If you have problems viewing the table, please check out the original post at http://multidimensionalmayhem.wordpress.com.
| Error | Cause/Solution |
|---|---|
| 1. #IND and #ERROR with Certain Statistical Functions – For example, you use functions like StDev or VarP in an MDX statement and receive #IND and/or #ERROR messages in your results. | #IND means that the result of the function is indeterminate. In many cases, this occurred in my projects when I made the error of supplying these functions with a single value rather than a set. Try applying a set, such as .Members. Just one individual value might not work, since the function may not have another value to make a valid comparison to. I have seen such errors in connection with the Stdev (a.k.a. Stddev), StdevP (StddevP), Var (Variance), and VarP (VarianceP) functions. I have yet to see them occur with Covariance, CovarianceN and Correlation, but be on the lookout. It is possible that they may also occur with other statistical functions which I haven’t used much to date, like LinRegIntercept, LinRegPoint, LinRegR2, LinRegSlope, LinRegVariance. |
| 2. Incorrect calculations for averages – You perform calculations of averages in measures but the results are sometimes incorrect. | I ran into at least two distinct causes for this subtle problem. On one occasion, I was receiving incorrect results when calculating a daily average over a range of dates, but a couple of the dates were missing due to data entry errors. On that occasion and others, I ran into a related problem due to the way SSAS handles null processing. Set each dimension column’s NullProcessing property to Preserve if you don’t want to count missing data in calculations like averages. For example, if you want an average over the course of 30 days but there are 15 dates missing in the middle of the time period, setting this property to Preserve will prevent SSAS from applying the missing ones to the divisor. Otherwise, you may get lower averages than expected. Here’s another useful tip: the Preserve setting is hard to find under the list that pops up when you click NullProcessing. At least under SQL Server 2008 and 2008 R2 versions of BIDS, the list is only four items long and includes other settings like Error, Unknown Member, ZeroOrBlank and Automatic. If you scroll up, however, there’s a fifth item on the tiny list, Preserve, which seems invisible at first. This seemingly minor user interface issue had me tearing my hair out one night. |
| 3. No Logon Servers in MDX Studio – You cannot sign in to a server when working with MDX Studio because “There are currently no logon servers available to service the logon request.” | There may be esoteric permissions reasons for this error message which are beyond my ken, since the same message appears frequently in other Windows programs for completely unrelated reasons. Yet I did find a quick workaround when I encountered this in MDX Studio, by signing in as (local) or localhost (I forget which) with no user name. |
| 4. Two SSAS databases won’t sync – I forgot the exact text of the error message, but you’ll know it when you see it. | After hours of trying to find out why two of my SSAS databases wouldn’t synch, I discovered that the servers involved in a synchronization operaetion have to be running the same version. I was running 2008 on one server and 2008 R2 on the other. This requirement is not documented anywhere in SQL Server’s Help files, nor is there an explanation of the error message I received. |
| 5. A Dimension Can’t Materialize – There are many reasons why a measure group can take forever to process, but this performance problem can occur with a ridiculously simple and small measure group. | It would have taken me a lifetime to figure this one out, had I not remembered that I had recently added a reference dimension. Unclicking Materialize solved the issue immediately. |
| 6. The 1899 Default Date Problem – Null dates are changed to a default of 12/30/1899 00:00:00 in certain dimension columns. | Change the NullProcessing property (which is found under the KeyColumns property) for that dimension column. It has been awhile since I encountered this problem, but used to run into it routinely so I had to consult Darren Gosbell’s post to jog my memory about the solution. Setting it to ZeroOrBlank or Default may cause the problem. All of the dimension columns I’m using for my current projects are set to Automatic or Preserve and I no longer have this issue with any of them. |
