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)?