Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

AddCalculatedMembers bug

with 6 comments

I learn a lot from reading posts and answering questions on newsgroups and online forums, and here’s a good example:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=248868&SiteID=1
 
Basically there’s a bug in the AddCalculatedMembers function which means that it brings back calculated members which it shouldn’t actually bring back. Since AddCalculatedMembers isn’t the most widely used function you’d think it wouldn’t have much impact, but one area that it might cause a problem is when client tools issue MDX queries to get metadata for display purposes - a fairly widely used technique. So, for example, in AdventureWorks if you added the following calculated member definition to the cube’s MDX Script and then deployed:
 
CREATE MEMBER CURRENTCUBE.[Product].[Product Categories].[Category].&[4].[BugTest]
AS 999, VISIBLE = 1 ;
 
You’d expect the calculated member to appear only underneath the Category ‘Accessories’ when you browsed the Product Categories hierarchy in the treeview in BIDS. However, what actually happens is that the calculated member appears underneath every category in the hierarchy and not just the one you wanted, which is pretty irritating, and this doesn’t just happen in BIDS – it happens in SQLMS, the Microsoft Excel Addin, and even Proclarity (reportedly), although it doesn’t happen in Office 12 beta or Panorama.
 
Running a Profiler trace while using SQLMS reveals what the problem is. When expanding ‘Bikes’ in the treeview it shows the following MDX being run:
 

SELECT

SUBSET( ADDCALCULATEDMEMBERS( [Product].[Product Categories].[Category].&[1].CHILDREN ), 0, 501 )

DIMENSION PROPERTIES MEMBER_NAME, MEMBER_TYPE ON 0,

{} ON 1 FROM [Adventure Works]

 
If you actually run this query in SQLMS you’ll see that AddCalculatedMembers is indeed returning the [BugTest] calculated member incorrectly. Hopefully this is on the list to be fixed in SP1…

Written by Chris Webb

February 15, 2006 at 10:47 am

Posted in MDX

6 Responses

Subscribe to comments with RSS.

  1. Might have something to do with the switch to attribute-based hierarchies? One work-around that I found is to filter for Parent:
     

    With member [Product].[Product Categories].[Category].&[4].[BugTest]
    AS 999, VISIBLE = 1
    SELECT
    SUBSET( Filter(ADDCALCULATEDMEMBERS(
    [Product].[Product Categories].[Category].&[1].CHILDREN),
    [Product].[Product Categories].Parent is
    [Product].[Product Categories].[Category].&[1]),
    0, 501 )
    DIMENSION PROPERTIES MEMBER_NAME, MEMBER_TYPE ON 0,
    {} ON 1 FROM [Adventure Works]

    Deepak

    February 15, 2006 at 7:59 pm

  2. I guess it probably does have something to do with attribute hierarchies, but I doubt it\’s intended functionality if that\’s what you\’re suggesting(?). Filtering for parent is a bit of a pain to do and doesn\’t solve the problem of all those broken client tools… 

    Chris

    February 16, 2006 at 11:25 am

  3. You\’re right, this behavior doesn\’t look like what was intended – but do you know how Office 12 and Panorama handle it, then?

    Deepak

    February 16, 2006 at 4:24 pm

  4. I would guess they go through the metadata objects (can\’t remember what they\’re called exactly at the moment) rather than issue MDX queries. When I worked with them, admittedly years ago, they were very slow which was why people started to use MDX instead.

    Chris

    February 16, 2006 at 5:14 pm

  5. Thanks for pointing this out Chris… you\’re right, our basic metadata tree is affected by this bug. We do queries very similar to yours as the user expands the tree for performance reasons, and so we show the same member as a child of all 4 categories. At least one customer\’s reported it to us already… I\’ll try to find out if there\’s a known workaround.

    waded

    March 1, 2006 at 12:32 am

  6. It turns out we already had an engineer look at this.. he tells me this issue appears to be fixed by the SQL 2005 SP1 beta.

    waded

    March 1, 2006 at 1:03 am


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

Follow

Get every new post delivered to your Inbox.

Join 2,868 other followers

%d bloggers like this: