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:
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…
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]
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…
You\’re right, this behavior doesn\’t look like what was intended – but do you know how Office 12 and Panorama handle it, then?
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.
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.
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.
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"
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.