Named sets and Existing

I have a very large Account dimension (> 2,000,000 members). I would like to create a named set for the top most profitable accounts. I plan to use this named set in the SSRS Report Builder to compensate for its lack of aTop N feature. The problem is that the user can change the fiscal period and SSAS needs to reevaluate the set. Using Generate function won't work for performance reasons. So, I'm trying to use Existing to force the set generation. e.g.

CREATE SET CURRENTCUBE.[Top 5 Profitable Accounts]

AS

Order(TopCount(

(EXISTING [Account].[Account].[Account].Members, [Period].[Period].CurrentMember), 5, [Measures].[Profit]),

[Measures].[Profit], DESC);

However, this gives me "The Period hierarchy already appears in the Axis1 axis." error when I test the set with the following query

select non empty [Measures].[Profit] on 0,

non empty [Top 5 Profitable Accounts] on 1

from [RPM]

WHERE [Period].[Period].&[20030228]

and duplicated dimensionality error in the Report Builder.

Does anyone know how this could be implemented?

There are a couple of issues in the Named Set:

The error can be eliminated by not explicitly specifying [Period].[Period].CurrentMember with Existing.|||

Deepak,

Thank you for helping. True, the modified set doesn't error out. However, as you pointed out, it is not dynamic. It only works with the default time period which is where I started. In other words, EXISTING doesn't help here. Changing the query slicer to a different time period doesn't produce any results. It looks like we cannot change the context of standard named sets defined with CREATE SET in the cube script. This pretty much leaves me with no options to simulate TopN with the Report Builder.

MyTable, ServerTimeDimension, Null-Values

Hello experts,

I’ve got one Problem and four solutions, but none is a good one =(

My entire problem:

I’ve got a Dimension with a column Production_Date. I would like to relate it with a ServerTimeDimension. I created a ServerTimeDimension and related about the Dimension Usage it to my Dimension. In the Production_Date column are some Null values and now I’ve got some Problems.

My four solutions:

1. to alter my dimension through one query and take just this rows with correct date à not good, because I don’t want to lose some Information
2. to create a new named Calculation and alter all Null values through a very old date. Something like this:
   case when (INS_PRODUCTION_DATE is null)
   then '01.01.1969'
   else (CONVERT(VARCHAR(10), INS_PRODUCTION_DATE, 104))
   end à there are some wrong information now, that’s not so good too
3. make a time dimension one this column à not so good, because I can’t make a hierarchy right now
4. and to work with the ErrorConfiguration and KeyErrorLimit is the worst solution

Have somebody another good idea?

Best regards

Tschi2001

As a best practice, it is recommended you implement a date dimension table in your data warehouse and then add foreign key references to your fact table. The NULL date can be represented easily with this approach.

If that is not an option here, what is wrong with the UnknownMember/ErrorConfiguration option (Option 4)? This type of situation is just what that is for.

B.

|||

Hello Bryan,

You are right, that’s not a option here.

In my Opinion a correct cube have to work without use ErrorConfiguration.

And the Option UnknownMember doesn’t work with my cube or I make something wrong.

The way I try to solve this problem with UnknownMember:

- I take the Production Time Dimension (it’s my ServerTimeDimension)

- Went to Dimension Structure

- Look at the Date-Properties (it’s the Key of this Dimension)

- Went to KeyColumns

- Set NullProcessing to “ZeroOrBlank”

- Try to deploy

Came the old failure…

Made I something wrong or could it be that I misunderstand something?

Best regards

Alexander

|||

So, the first thing you need to do is configure the UnknownMember in your time dimension. To do this, set the UnknownMember property on the dimension to either Hidden of Visible. Then, open the cube and move to the Cube Structure tab and select each measure group referencing the time dimension. Set up the ErrorConfiguration as follows:

NullKeyConvertedToUnknown = IgnoreError

KeyErrorLimitAction = Stop Logging

Try that and see if that fixes the problem for you.

B.

|||

Thank you very much, it works very good !!!!

MyTable, ServerTimeDimension, Null-Values

Hello experts,

I’ve got one Problem and four solutions, but none is a good one =(

My entire problem:

I’ve got a Dimension with a column Production_Date. I would like to relate it with a ServerTimeDimension. I created a ServerTimeDimension and related about the Dimension Usage it to my Dimension. In the Production_Date column are some Null values and now I’ve got some Problems.

My four solutions:

1. to alter my dimension through one query and take just this rows with correct date à not good, because I don’t want to lose some Information
2. to create a new named Calculation and alter all Null values through a very old date. Something like this:
   case when (INS_PRODUCTION_DATE is null)
   then '01.01.1969'
   else (CONVERT(VARCHAR(10), INS_PRODUCTION_DATE, 104))
   end à there are some wrong information now, that’s not so good too
3. make a time dimension one this column à not so good, because I can’t make a hierarchy right now
4. and to work with the ErrorConfiguration and KeyErrorLimit is the worst solution

Have somebody another good idea?

Best regards

Tschi2001

As a best practice, it is recommended you implement a date dimension table in your data warehouse and then add foreign key references to your fact table. The NULL date can be represented easily with this approach.

If that is not an option here, what is wrong with the UnknownMember/ErrorConfiguration option (Option 4)? This type of situation is just what that is for.

B.

|||

Hello Bryan,

You are right, that’s not a option here.

In my Opinion a correct cube have to work without use ErrorConfiguration.

And the Option UnknownMember doesn’t work with my cube or I make something wrong.

The way I try to solve this problem with UnknownMember:

- I take the Production Time Dimension (it’s my ServerTimeDimension)

- Went to Dimension Structure

- Look at the Date-Properties (it’s the Key of this Dimension)

- Went to KeyColumns

- Set NullProcessing to “ZeroOrBlank”

- Try to deploy

Came the old failure…

Made I something wrong or could it be that I misunderstand something?

Best regards

Alexander

|||

So, the first thing you need to do is configure the UnknownMember in your time dimension. To do this, set the UnknownMember property on the dimension to either Hidden of Visible. Then, open the cube and move to the Cube Structure tab and select each measure group referencing the time dimension. Set up the ErrorConfiguration as follows:

NullKeyConvertedToUnknown = IgnoreError

KeyErrorLimitAction = Stop Logging

Try that and see if that fixes the problem for you.

B.

|||

Thank you very much, it works very good !!!!