Chris Webb's BI Blog

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

AddCalculatedMembers bug

with 8 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

8 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

  7. WITH SET [aDIPAS Design IDs] AS
    Descendants (
    { [Area Product].[Family-Matnr].[Product Family].&[DRAM], [Area Product].[Family-Matnr].[Product Family].&[MCP], [Area Product].[Family-Matnr].[Product Family].&[NAND FLASH], [Area Product].[Family-Matnr].[Product Family].&[NOR FLASH] },
    [Area Product].[Family-Matnr].[Design ID]
    )
    MEMBER [Measures].[aQtr Lock Goal Met%] AS
    CASE
    WHEN ( [Measures].[Qtr Lock Goal Qty Cumulative] ) < 1 And ( [Measures].[Area Actuals Qty Cumulative] ) < 1
    THEN NULL
    WHEN ( [Measures].[Qtr Lock Goal Qty Cumulative] ) 1
    THEN 1 /* 100% */
    ELSE Round (
    ( [Measures].[Area Actuals Qty Cumulative] ) / ( [Measures].[Qtr Lock Goal Qty Cumulative] ),
    4
    ) END,
    FORMAT_STRING = “Percent”,
    VISIBLE = 1,
    DISPLAY_FOLDER = ‘DIPAS’
    MEMBER [Measures].[aFamily] AS
    IIf ( [Measures].[Qtr Lock Goal Qty Cumulative] = 0 And [Measures].[Area Actuals Qty Cumulative] = 0, NULL, [Area Product].[Family-Matnr].CurrentMember.Parent.Parent.Parent.Properties ( “MEMBER_KEY”, typed ) ),
    VISIBLE = 1,
    DISPLAY_FOLDER = ‘DIPAS’
    MEMBER [Measures].[aKPI Bucket] AS
    IIf (
    [Area Goal Qty] = 0,
    NULL,
    IIf ( [Area Actuals Qty] = 0, 0, [Area Actuals Qty] / [Area Goal Qty] )
    ),
    FORMAT_STRING = “Percent”,
    VISIBLE = 1,
    DISPLAY_FOLDER = ‘DIPAS’
    MEMBER [Measures].[aQtr Lock Goal Met Trend%] AS
    CASE
    WHEN ( [Measures].[Qtr Lock Goal Qty Cumulative] ) < 1 And ( [Measures].[Actual Qty + Remainder Projection Qty Cumulative] ) < 1
    THEN NULL
    WHEN ( [Measures].[Qtr Lock Goal Qty Cumulative] ) 1
    THEN 1 /* 100 % */
    ELSE Round (
    ( [Measures].[Actual Qty + Remainder Projection Qty Cumulative] ) / ( [Measures].[Qtr Lock Goal Qty Cumulative] ),
    4
    ) END,
    FORMAT_STRING = “Percent”,
    VISIBLE = 1,
    DISPLAY_FOLDER = ‘DIPAS’
    MEMBER [Measures].[a% Bucket DIPAS] AS
    CASE
    WHEN IsEmpty ( [Qtr Lock Goal Met%] )
    THEN NULL
    WHEN ( [Qtr Lock Goal Met%] ) > 1.05
    THEN ‘Bucket1 (>105%)’
    WHEN ( [Qtr Lock Goal Met%] ) .95
    THEN ‘Bucket2 (95% to 105%)’
    WHEN ( [Qtr Lock Goal Met%] ) .75
    THEN ‘Bucket3 (75% to 95%)’
    WHEN ( [Qtr Lock Goal Met%] ) <= .75
    THEN 'Bucket4 ( 1.05
    THEN ‘Bucket1 (>105%)’
    WHEN ( [Qtr Lock Goal Met Trend%] ) .95
    THEN ‘Bucket2 (95% to 105%)’
    WHEN ( [Qtr Lock Goal Met Trend%] ) .75
    THEN ‘Bucket3 (75% to 95%)’
    WHEN ( [Qtr Lock Goal Met Trend%] ) <= .75
    THEN 'Bucket4 (<75%)' END,
    VISIBLE = 1,
    DISPLAY_FOLDER = 'DIPAS'
    MEMBER [Measures].[aDID Count DIPAS] AS
    CASE
    WHEN ( [Qtr Lock Goal Met%] ) = 0 OR ( [Qtr Lock Goal Met%] ) IS NULL
    THEN NULL
    ELSE 1 END,
    FORMAT_STRING = "#,##",
    VISIBLE = 1,
    DISPLAY_FOLDER = 'DIPAS'
    MEMBER [Measures].[aDID Count Trend] AS
    CASE
    WHEN ( [Qtr Lock Goal Met Trend%] ) = 0 OR ( [Qtr Lock Goal Met Trend%] ) IS NULL
    THEN NULL
    ELSE 1 END,
    FORMAT_STRING = "#,##",
    VISIBLE = 1,
    DISPLAY_FOLDER = 'DIPAS'
    SELECT NON EMPTY
    { [Measures].[aFamily], [Measures].[Qtr Lock Goal Qty], [Measures].[Qtr Lock Goal Qty Cumulative], [Measures].[Area Actuals Qty Cumulative], [Measures].[Area Actuals Qty], [Measures].[aQtr Lock Goal Met%], [Measures].[a% Bucket DIPAS], [Measures].[aDID Count DIPAS], [Measures].[Actual Qty + Remainder Projection Qty Cumulative], [Measures].[aQtr Lock Goal Met Trend%], [Measures].[a% Bucket Trend], [Measures].[aDID Count Trend] } ON COLUMNS,
    NON EMPTY
    {
    {
    { [Location].[SAP PLANT].&[SG01], [Location].[SAP PLANT].&[SG07], [Location].[SAP PLANT].&[SG10], [Location].[SAP PLANT].&[SUBCON], [Location].[SAP PLANT].&[US01] } * { [aDIPAS Design IDs] } *
    { [Area Product].[Material Group].&[AOUT], [Area Product].[Material Group].&[AFGPN] } * { [Selected Qtr Lock Work Weeks] }
    }
    } ON ROWS
    FROM [Area Projection Accuracy]
    WHERE ( [Qtr Lock Dates].[Qtr Lock].&[FY2014-Q2 QTR LOCK], [Location].[Area - Plant].[AREA LABEL].&[3]&[ASSEMBLY] ) CELL Properties Value,
    FORMATTED_VALUE,
    CELL_ORDINAL

    when i run above query in Proclarity it is running fine but when i tried to "Qtr Lock" move to background and select one quarter value and click apply button am getting "Query failed error"

    Anil

    April 29, 2014 at 12:47 pm

    • You will have to give me more details on the query that errors and the error message that you’re getting if I’m going to help you. Giving me a query that works and a generic error message isn’t enough for me to work out what’s going wrong here.

      Chris Webb

      April 29, 2014 at 1:01 pm


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 3,070 other followers

%d bloggers like this: