Create new dimension member based on other members

I have a dimension named category which contains the following members:

O, A, B, C, Cd

Now I want to create a new dimension category (not necessarily in the same hierarchy named LowCaring and which should contain both O and A. How do I do this?

I already tried creating a calculated member with parent hierarchy the category but it doens't do what I need it to do.

Thanks in advance for any help or pointers;

Stijn Verrept.

dear rept,

Try to create a view in your SQL to get only the O and A, and add this view into your datasourceview of Analysis Services.

When create the cube add this object as dimension!

Helped?

regards!!

|||

Hi Pedro, thanks for your reply.

When I would do it in SQL then I need to calculate all the measures connected to that dimension as well?

I have for example a fact table occupation with

OC_Date

OC_Category

OC_Number

The OC_Date is connected to a time dimension and the OC_Category to the category. That table is already filled with facts so SSAS should be able to sum the OC_Number for O and A automatically.

Is that possible?

|||

Yes, It works...

the view you create works in Analysis Services as as other object.. you can define it as a dimension a works as the others.

In your case as you described it works! Try it!

Helped?

Regards!!

|||

Hi Pedro,

I'm trying as you suggested:

I created a new query which lists only the O and A, I added this one to the DSV. Then I created a new dimension which also deploys fine.

The trouble starts when I add this dimension to the cube, then I get this error:

Processing Measure Group 'Occupation' failed.

Start time: 21/07/2007 12:18:29; End time: 21/07/2007 12:18:30; Duration: 0:00:01
Processing Partition 'Occupation' failed. 1 rows have been read.
Start time: 21/07/2007 12:18:30; End time: 21/07/2007 12:18:30; Duration: 0:00:00
SQL queries 1
SELECT [dbo_Occupation].[OC_Number] AS [dbo_OccupationOC_Number0_0],[dbo_Occupation].[OC_HOID] AS [dbo_OccupationOC_HOID0_1],[dbo_Occupation].[OC_CAID] AS [dbo_OccupationOC_CAID0_2],[dbo_Occupation].[OC_Date] AS [dbo_OccupationOC_Date0_3]
FROM [dbo].[Occupation] AS [dbo_Occupation]
Error Messages 2
Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_Occupation, Column: OC_CAID, Value: 3. Errors in the OLAP storage engine: The attribute key was converted to an unknown member because the attribute key was not found. Attribute Categorie of Dimension: Laagzorgbehoevenden from Database: WZM, Cube: Dossier DW, Measure Group: Occupation, Partition: Occupation, Record: 3.

Occupation is the fact table, Laagzorgbehoevenden is the new dimension with only O and A.

What did I do wrong?

|||

Occupation is your dimension or Fact?!

Regards

|||Occupation is my fact. Category is the dimension table with (O, A, B, C, CD, ...) and Laagzorgbehoevenden is the dimension table with only O and A, based on the query I make like you said.|||

And what is the factTable?

Link the Laagzorgbehoevenden to your factTable with uniqueID in datasourceview... did it?

Regards

|||

The fact table is occupation. When I link the laagzorgbehoevenden to occupation in DSV and try to deploy I get:

Warning 2 Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_Occupation, Column: OC_CAID, Value: 3. 0 0

Which is normal because the fact table (occupation) contains values that aren't in laagzorgbehoevenden, because laagzorgbehoevenden only contains O and A. Categories contains them all.

I think this is the wrong way to go. Isn't there another easier way of getting 2 dimension members and creating a third that contains them both and sums the occupation values of those both?

|||

There are several ways to do this. Following PedroCGD's advice, create another column in the view you are using to build the Category dimension. You can do this by adding a derived column to the UDM or by modifying the underlying table. The definition for the new column would look something like this:

Code Snippet

CASE category

WHEN 'O' THEN 'LowCaring'

WHEN 'A' THEN 'LowCaring'

ELSE 'Other'

END AS CategoryType

Add the CategoryType as a new attribute hierarchy in the Category dimension. The two attribute hierarchies will act independently.

I hope I've interpreted the question correctly.

|||

Dear Friend,

Martin Mason
was more rapid than me! :-)

I hope I get it!!

If not post here, I will try to help until you get it!

Regards!!

|||

Thanks Martin,

This works great! A followup question, is it somehow possible to hide the Other value for the users (in the browser for example)?

Create dimension without member

Hi
Analysis manager wont let me create dimension unless fact table filed has value init. Other words, how do I create MT dimension(dimension without member)
thanksI'm confused. Is this really a dimension? Or is this a dimension that doesn't exist now, but will in the future? (i.e. you're rolling out a new type of product and you've created new product dimension, but the fact table has no corresponding records). Can you plug an 'unknown' attribute into your fact and dimension?

Let me know if I'm way off.|||Fact table has field called Due date which contains null values. When I create Due_date dimension, it won’t let me create, gives a message unable to count the members. I have to create dimensions out of all the fields in the fact table and send the cub to this person. Later this person will load the cub into his server and change data source and run the cub.
But my question is , If in his source table due_Date has null values(no data) , when this person runs the cube , it will give the error right?

Right now his source table due_date doen’t have values, but later he will had value into it.

He wants me to create each field in the fact table one dimension, doesn’t matter the fact table filed contains data or not? Can I do that?|||Is this 2000 or 2005? I just tried this in 2000 and it worked. No errors.|||It's 2000, you mean , it let you create a dimension with out a member.

Create Customer Ranking in Cube

Hey guys,

is there a way i can create a Rank Member or measure in my cube based on sales for that customer, no matter what time heirarchy we are looking at.

I have reporting requirements where they want to see all customers, ordered by Sales Amount. It could be at a year level, month level, or even a week/day level..

Im new to SSAS and MDX, i have looked at a few examples posted here, but cannot work out where i put the script in my cube. Is it a measure or do i do it as a calculated column in the DSV.

Any help is much appreciated.

Thanks

Scotty

Here's an Adventure Works ranking query, which uses a query-scoped calculated measure (assuming that the Customer hierarchy is on the rows of the query):

>>

with member [Measures].[CustSalesRank] as

Rank([Customer].[Customer Geography].CurrentMember,

Order(Extract(Axis(1),[Customer].[Customer Geography]),

[Measures].[Internet Sales Amount], BDESC)),

NON_EMPTY_BEHAVIOR = [Measures].[Internet Sales Amount]

select [Date].[Fiscal Year].Members *

{[Measures].[Internet Sales Amount], [Measures].[CustSalesRank]} on 0,

NON EMPTY [Customer].[Customer Geography].[Country].Members on 1

from [Adventure Works]

--

All Periods All Periods FY 2002 FY 2002 FY 2003 FY 2003 FY 2004 FY 2004 FY 2005 FY 2005
Internet Sales Amount CustSalesRank Internet Sales Amount CustSalesRank Internet Sales Amount CustSalesRank Internet Sales Amount CustSalesRank Internet Sales Amount CustSalesRank
Australia $9,061,000.58 2 $2,568,701.39 1 $2,099,585.43 1 $4,383,479.54 2 $9,234.23 3
Canada $1,977,844.86 6 $573,100.97 3 $305,010.69 6 $1,088,879.50 6 $10,853.70 2
France $2,644,017.71 5 $414,245.32 6 $633,399.70 4 $1,592,880.75 5 $3,491.95 6
Germany $2,894,312.34 4 $513,353.17 5 $593,247.24 5 $1,784,107.09 4 $3,604.83 5
United Kingdom $3,391,712.21 3 $550,507.33 4 $696,594.97 3 $2,140,388.50 3 $4,221.41 4
United States $9,389,789.51 1 $2,452,176.07 2 $1,434,296.26 2 $5,483,882.67 1 $19,434.51 1

>>

|||Please remove this line, as it is wrong:

NON_EMPTY_BEHAVIOR = [Measures].[Internet Sales Amount]

Obviously, even if [Internet Sales Amount] is NULL, the rank will never be NULL - it is always an integer value.

|||

Ok guys, thanks for the replys..

I added the following into a calculated measure that i called CustSalesRank .. (taken from the 1st line of code) is that Correct?

When i added this to the expression box in the calculated member, i get a red undeline on the "with" statement.. What am i doing wrong?

CREATE MEMBER CURRENTCUBE.[MEASURES].[CustSalesRank]

AS with member [measures].[CustSalesRank] as

Rank([Customer].[Customer Group Name].CurrentMember,

Order(Extract(Axis(1),[Customer].[Customer Group Name]),

[Measures].[Sales Amount], BDESC)),

select [Date].[Fiscal Hierarchy].Members *

{[Measures].[Sales Amount], [Measures].[CustSalesRank]} on 0,

NON EMPTY [Customer].[Customer Group Name].Members on 1

from [Sales By Market],

VISIBLE = 1 ;

--

|||

the error i get when i try to deploy is:

Error 2 MdxScript(Sales By Market) (17, 5) Parser: The syntax for 'with' is incorrect. 0 0

|||

Hi Mosha,

You're right in the strict sense - the reason I added NON_EMPTY_BEHAVIOR was to remove rows with no sales for this specific query (ie. a query ranking only among customers with sales data), as in this example:

>>

with member [Measures].[CustSalesRank] as

Rank([Customer].[Customer Geography].CurrentMember,

Order(Extract(Axis(1),[Customer].[Customer Geography]),

[Measures].[Internet Sales Amount], BDESC))

select

{[Measures].[Internet Sales Amount], [Measures].[CustSalesRank]} on 0,

NON EMPTY DrillDownLevel([Customer].[Customer Geography].[Country].&[Canada]) on 1

from [Adventure Works]

-

Internet Sales Amount CustSalesRank
Canada $1,977,844.86 1
Alberta $22,467.80 3
British Columbia $1,955,340.10 2
Brunswick (null) 5
Manitoba (null) 6
Ontario $36.96 4
Quebec (null) 7

versus:

with member [Measures].[CustSalesRank] as

Rank([Customer].[Customer Geography].CurrentMember,

Order(Extract(Axis(1),[Customer].[Customer Geography]),

[Measures].[Internet Sales Amount], BDESC)),

NON_EMPTY_BEHAVIOR = [Measures].[Internet Sales Amount]

select

{[Measures].[Internet Sales Amount], [Measures].[CustSalesRank]} on 0,

NON EMPTY DrillDownLevel([Customer].[Customer Geography].[Country].&[Canada]) on 1

from [Adventure Works]

-

Internet Sales Amount CustSalesRank
Canada $1,977,844.86 1
Alberta $22,467.80 3
British Columbia $1,955,340.10 2
Ontario $36.96 4

>>

|||

> You're right in the strict sense - the reason I added NON_EMPTY_BEHAVIOR was to remove rows with no sales for this specific query (ie. a query ranking only among customers with sales data), as in this example:

Deepak - this is very very dangerous path. NON_EMPTY_BEHAVIOR is not a semantic feature, it is a performance hint. It doesn't cause rows to be removed by NON EMPTY. They might get removed sometimes when NEB is defined incorrectly, like in the example above, but it is purely a hint, and in other situations they won't get removed. So you will get inconsistant and even wrong results.

To properly remove rows with no sales, there must be specific IIF for that, i.e.

with member [Measures].[CustSalesRank] as

IIF( IsEmpty([Measures].[Internet Sales Amount]), NULL, Rank([Customer].[Customer Geography].CurrentMember,

Order(Extract(Axis(1),[Customer].[Customer Geography]),

[Measures].[Internet Sales Amount], BDESC))),

NON_EMPTY_BEHAVIOR = [Measures].[Internet Sales Amount]

Now NON_EMPTY_BEHAVIOR is correctly defined. Another idea is to optimize performance here would be to remove Order from inside Rank, and use version of Rank with 3 parameters.

|||

Mosha,

Thanks for your input.. Could you please kindly answer my question for me.. I just need to know where i put this code, i put it in a calculated measure and it didnt work. I removed all things from the select statement and down, but im not sure if it is working correctly..

Also, with all this code talk, im not 100% sure what i should be putting in now...

Thanks

Scotty

|||

For Adventure Works, you can put something like that inside MDX Script:

CREATE [CustSalesRank] = Rank([Customer].[Customer Geography].CurrentMember,[Customer].[Customer Geography].CurrentMember.Level.Members,[Measures].[Internet Sales Amount]);

|||

Thanks Mosha.. this worked great..

If i may ask for one more piece of advise, when i do this,, all the customers that have 0 sales are still shown with the lowest rank number.. in my case 81 . what i can i add to expression that will supress customers with 0 sales..?

Thanks

scotty

|||

Thanks for clarifying that, Mosha - I had (mistakenly) assumed that, in AS 2005, NON_EMPTY_BEHAVIOR would cause those rows to be removed.

On the use of Rank() with 3 parameters in lieu of Order() - has the semantics of Rank() changed in AS 2005, because BOL still says the following:

http://msdn2.microsoft.com/en-us/library/ms144726.aspx

>>

SQL Server 2005 Books Online

Rank (MDX)

Updated: 17 July 2006

...

The Rank function does not order the set.

>>

|||

The comment that Rank function doesn not order the set is correct, although I can see how it can be misleading. It probably refers to the fact that internal implementation of Rank doesn't need to order the set in order to find the rank. Please see detailed discussion about Rank and algorithms behind it in this blog:

http://www.sqljunkies.com/WebLog/mosha/archive/2006/03/14/mdx_ranking.aspx

|||

Thanks - since the same comment also appeared in AS 2000 BOL, it could be confusing. But the MDX Solutions chapter, referenced in your blog entry, draws this distinction:

"..The semantics for this function have changed between Analysis Services 2000 and 2005. When the expression is provided, it is used to determine if ties exist and what the right rank should be. In Analysis Services 2000, the expression was used when the tuple was found to search neighbors in the set and determine fair ranking numbers.."