Named Set with TOPCOUNT Not Working Correctly

I need to create reports that contain our top 15 customers at any given point in time. I created the following Named Set using the TopCount function in SSAS 2005:

TOPCOUNT([Customer].[Customer Type-Name-Door Hierarchy].[Customer Name].members, 15, [Measures].[Net Ship Dollars])

When I bring this set into the client (Excel 2007 pivot table), its shows our top 15 customers for all time. When I add a date filter for just 2007, it doesn't update the list to just show the top customers for 2007. It just keeps the same "all time" group.

When I create this named set in a browser query, it works correctly. That is, it shows the top 15 customers for 2007. Is there any way I can get the Named Set created in SSAS to show the correct data based on the filtered date in the client?

Here is the browser query:

WITHSET

[Top Performers]

AS

'TOPCOUNT([Customer].[Customer Type-Name-Door Hierarchy].[Customer Name].members, 15, [Measures].[Net Ship Dollars])'

SELECT

{[Measures].[Net Ship Dollars]} ONCOLUMNS,

{[Top Performers]}ONROWS

FROM Shipments

Where ([Date].[Calendar Year].&[2007])

Thank you.

David

Are you using AS 2005 SP2? This problem has been fixed there.|||

The problem here is because the named set in the MDX Script gets resolved when it is first parsed at which time all the other dimensions will be set to their default members (which is usually the "All" member). Using the EXISTING statement in your set definition should force it to be re-evaluated with the current context each time it is used.

eg

TOPCOUNT(EXISTING [Customer].[Customer Type-Name-Door Hierarchy].[Customer Name].members, 15, [Measures].[Net Ship Dollars])

|||I am using SP2 but still experiencing the problem.|||

Darren,

I tried this but the same problem is occuring. Apparently, the default member of the time hierarchy determines what customer members to return. When I changed the default member to 2007, the named set showed the top customers for 2007. I came across another post which confirmed this as a problem. Does anybody have any workarounds? Thyank you.

David

|||

Hi David,

It seems that I did not read your full question and missed some information. So, just want to clarify. Named sets in AS 2005 are static named sets, which means that they are calculated just once in the context of the current member awailable at that moment. They are not reevaluated during execution of the select statement. Daren is right in the fact that named sets created in the cube scope or session scope (using Create statement) are evaluated when the CREATE statement is executed, therefore TopCount is calculated in the context of the default member. Sets created in the query scope using With clause are evaluated right after the WHERE clause and therefore take current member defined by the where clause in concideration (this is why your query with WITH statement works as you expect and query with named set from CREATE statement return different results). There are it's own pros on having static named sets (mostly performance), but in your case you should try to avoid using TopCount in the named set, if you want the members on other axis to affect results of the topcount.

Irina

P.S.

>>> I came across another post which confirmed this as a problem.

Could you please point me to this thread?

|||

Irina Gorbach wrote:

Named sets in AS 2005 are static named sets, which means that they are calculated just once in the context of the current member awailable at that moment. They are not reevaluated during execution of the select statement.

Of course! sorry for leading you slightly astray. The EXISTING statement will work if you have a set in a calculation

eg

CREATE MEMBER CurrentCube.Measures.CustomersInTop10Pcnt AS COUNT(TOPPERCENT(EXISTING Customer.Customer.Members, 150 Measures.Sales)

This would re-evaluate the set used in the toppercent function based on the current slicing conditions, which is similar, but different.

|||so is this the solution then?

TOPCOUNT(EXISTING [Customer].[Customer Type-Name-Door Hierarchy].[Customer Name].members, 15, [Measures].[Net Ship Dollars])

or is there another way?

Also, how do you get it to sort correctly in excel, if you pull over the named set it just shows you the top X sorted by the SET Expression, whereas if you do an MDX query it sorts it by the Numeric Expression

Named Set with TOPCOUNT Not Working Correctly

I need to create reports that contain our top 15 customers at any given point in time. I created the following Named Set using the TopCount function in SSAS 2005:

TOPCOUNT([Customer].[Customer Type-Name-Door Hierarchy].[Customer Name].members, 15, [Measures].[Net Ship Dollars])

When I bring this set into the client (Excel 2007 pivot table), its shows our top 15 customers for all time. When I add a date filter for just 2007, it doesn't update the list to just show the top customers for 2007. It just keeps the same "all time" group.

When I create this named set in a browser query, it works correctly. That is, it shows the top 15 customers for 2007. Is there any way I can get the Named Set created in SSAS to show the correct data based on the filtered date in the client?

Here is the browser query:

WITH SET

[Top Performers]

AS

'TOPCOUNT([Customer].[Customer Type-Name-Door Hierarchy].[Customer Name].members, 15, [Measures].[Net Ship Dollars])'

SELECT

{[Measures].[Net Ship Dollars]} ON COLUMNS,

{[Top Performers]}ON ROWS

FROM Shipments

Where ([Date].[Calendar Year].&[2007])

Thank you.

David

Are you using AS 2005 SP2? This problem has been fixed there.|||

The problem here is because the named set in the MDX Script gets resolved when it is first parsed at which time all the other dimensions will be set to their default members (which is usually the "All" member). Using the EXISTING statement in your set definition should force it to be re-evaluated with the current context each time it is used.

eg

TOPCOUNT(EXISTING [Customer].[Customer Type-Name-Door Hierarchy].[Customer Name].members, 15, [Measures].[Net Ship Dollars])

|||I am using SP2 but still experiencing the problem.|||

Darren,

I tried this but the same problem is occuring. Apparently, the default member of the time hierarchy determines what customer members to return. When I changed the default member to 2007, the named set showed the top customers for 2007. I came across another post which confirmed this as a problem. Does anybody have any workarounds? Thyank you.

David

|||

Hi David,

It seems that I did not read your full question and missed some information. So, just want to clarify. Named sets in AS 2005 are static named sets, which means that they are calculated just once in the context of the current member awailable at that moment. They are not reevaluated during execution of the select statement. Daren is right in the fact that named sets created in the cube scope or session scope (using Create statement) are evaluated when the CREATE statement is executed, therefore TopCount is calculated in the context of the default member. Sets created in the query scope using With clause are evaluated right after the WHERE clause and therefore take current member defined by the where clause in concideration (this is why your query with WITH statement works as you expect and query with named set from CREATE statement return different results). There are it's own pros on having static named sets (mostly performance), but in your case you should try to avoid using TopCount in the named set, if you want the members on other axis to affect results of the topcount.

Irina

P.S.

>>> I came across another post which confirmed this as a problem.

Could you please point me to this thread?

|||

Irina Gorbach wrote:

Named sets in AS 2005 are static named sets, which means that they are calculated just once in the context of the current member awailable at that moment. They are not reevaluated during execution of the select statement.

Of course! sorry for leading you slightly astray. The EXISTING statement will work if you have a set in a calculation

eg

CREATE MEMBER CurrentCube.Measures.CustomersInTop10Pcnt AS COUNT(TOPPERCENT(EXISTING Customer.Customer.Members, 150 Measures.Sales)

This would re-evaluate the set used in the toppercent function based on the current slicing conditions, which is similar, but different.

|||so is this the solution then?

TOPCOUNT(EXISTING [Customer].[Customer Type-Name-Door Hierarchy].[Customer Name].members, 15, [Measures].[Net Ship Dollars])

or is there another way?

Also, how do you get it to sort correctly in excel, if you pull over the named set it just shows you the top X sorted by the SET Expression, whereas if you do an MDX query it sorts it by the Numeric Expression
sql

Mysterious query problem?

Hey all,
strange problem here... query #1 displays 357 records correctly and all
is well. However, when placed within query #2 as a subquery, it updates
every single record in the lta table, what's going on here? any
thoughts?

1.) select *
from LTA INNER JOIN new_list
ON lta.voy = new_list.voy AND
lta.poe = new_list.poe

2.)
update lta
set lta.LL_RCVD = 'N'
where exists (select *
from LTA INNER JOIN new_list
ON lta.voy = new_list.voy AND
lta.poe = new_list.poe)Gah! What am I doing wrong? The query below is a modification of query
#2 above, yet updates 32 records (all of which are NOT located in table
new_list)??!
Please help!

update lta
set LL_RCVD = 'j'
from lta a, new_list b
where a.voy = b.voy AND
a.poe = b.poe|||On 10 Jan 2005 07:25:14 -0800, Roy wrote:

>Hey all,
>strange problem here... query #1 displays 357 records correctly and all
>is well. However, when placed within query #2 as a subquery, it updates
>every single record in the lta table, what's going on here? any
>thoughts?
>
>1.) select *
>from LTA INNER JOIN new_list
>ON lta.voy = new_list.voy AND
>lta.poe = new_list.poe
>
>2.)
>update lta
>set lta.LL_RCVD = 'N'
>where exists (select *
>from LTA INNER JOIN new_list
>ON lta.voy = new_list.voy AND
>lta.poe = new_list.poe)

Hi Roy,

The subquery on #2 doesn't reference the outer query. Hence, it returns
the same 357 rows for each row in LTA (from the outer query), so the
EXISTS predicate is always true.

You probably need

UPDATE lta
SET LL_RCVD = 'N'
WHERE EXISTS (SELECT *
FROM new_list
WHERE new_list.voy = lta.voy
AND new_list.poe = lta.poe)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||On 10 Jan 2005 07:42:14 -0800, Roy wrote:

>Gah! What am I doing wrong? The query below is a modification of query
>#2 above, yet updates 32 records (all of which are NOT located in table
>new_list)??!
>Please help!
>update lta
>set LL_RCVD = 'j'
>from lta a, new_list b
>where a.voy = b.voy AND
>a.poe = b.poe

Hi Roy,

This should affect the same rows as the query I suggested in my reply to
your other message (but this one sets LL_RVCD to 'j'; the other one sets
it to 'N').

An important difference occurs if one row in lta matches more than one row
in new_list (and, since the select returns more rows than are affected by
the update statement, this appears to be the case with your data). The
above query will repeatedly change the values for any lta row that matches
more than one new_list row (with the results being undefined, though in
you case, where the new value is a constant, the results will be as
expected); my version will simply update the rows exactly once.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||I love you forever, thanks Hugo!