While browsing the cube, I'm getting a "#Value" error, which I believe is due to the non existence of certain attribute members.
For example, working on Adventure Works database. Assuming there is no Product Category = 5 in the SSAS database but we want to add this category into the calculation for future purposes, so the calculation would be as follows
(Measures.[Internet Sales Amount], [Product].[Category].&[5])
This calculation gives me a "#Value" error while browsing, to handle this kind of situation, I added an exception handling, check whether the member exist, if not return 0.
IIF(
ISEMPTY(EXISTS([Product].[Category].[Category].MEMBERS, [Product].[Category].&[5]).ITEM(0)),
0,
Measures.[Internet Sales Amount], [Product].[Category].&[5]
)
This seems to work fine in the SQL Managemen Studio, but when added to the Cube in the Calculations and processes, still I get the same "#Value" error.
Is the apporach to handle this is right? Is there a different way to handle this?
-
Vivek
Doing the whole "IIF( ISEMPTY( EXISTS(...)))" is going to wreck your performance. I'm surprised that it appears to work in SSMS as the EXISTS function will attempt to resolve both the sets in order to evaluate them and should throw an exception trying to resolve [Product[.[Category].&[5] and IsEmpty is not checking if the set is empty, but rather it will be doing a value comparison against the default measure which could lead to misleading results.
There are better ways to code a check like this, but they are still not a good idea. If you know you are going to have a Category 5, add that row to the dimension now and re-process the dimension, even though you don't yet have any facts for it. This way this script will be able to resolve the member reference and everything will work.
Adding references in the script to non-existant members and trying to trap errors is generally a bad thing.
NOTE: in SSMS you can hover over or double click on the #Value to see the text of the error message.
|||Thanks for the reply.
I guess you are right, I further did a search on this and go the same inputs.
Books Online suggested to use "IS" instead of "ISEMPTY()" to check for emptiness of dimension/attribute members. Even this works in SSMS (MDX query) and when ported to BIDS and browse fails.
You did say there are ways to handle this kind of exception, can you share them? I know it is performance intensive, but I have to catch such kind of exception.
-
Vivek
|||Just to be entirely clear - you ONLY ever need this sort of check if you are taking user input for something like a parameter in SSRS or some other reporting engine. You should NEVER use the following type of code in your MDX Script.
The pattern in something like an SSRS report would look like the following, passing in the unique name as a string and using the StrToMember() function and testing with the IS operator.
eg.
IIF( StrToMember( @.parameter ) IS NULL, ...
I know I am repeating myself, but I need to make sure this is clear - if you know that you are going to need a product category 5, then create it in the dimension member now. Never put references in your MDX script to members that do not yet exist and then try to wrap these references with code to handle the errors.
If you have a calc that works in SSMS, but not in BIDS, it will be because of the way the browser control in BIDS is structuring the query. Start up a profiler session and capture the queries that BIDS generates and test them in SSMS. There will probably be multiple commands fired off as BIDS quite often uses things like session scoped sets in it's queries.
|||Darren,
Thanks. Surely will not use this in BIDS, as it clearly doesn't work.
--
Vivek
