create a new dimension on the basis of two existing dimensions?

Question

Hi,

I'm quite new to MDX. Is it possible to create a new dimension on the basis of two existing dimensions? (Microsoft Visual Studio, SSAS)

For example if I have in the cube a dimension called Year and a dimension called Quarter, is it possible to generate a new dimension called Time that is dynamically made from the two dimensions.

Year = 2000, 2001, 2002,...

Quarter = Q1, Q2, Q3, Q4

New dimension Time= 2000Q1, 2000Q2, 2000Q3, 2000Q4, 2001Q1, 2001Q2,...

I could make a new table to the SQL database, but is it possible and how to make this join in OLAP cubes.

Best,

Aulis Paski

Answer 1

Maybe not the answer to your question but most people have a Dimension that is specific to Time.  That would contain year/semester/quarter/month/day.  (vs doing what your are attempting to do)

Sounds like you are kind of new to this as I was and currently am.  I would suggest going through the adventureworks tutorial from beginning to end as this provided me with more information than anything else.

http://msdn.microsoft.com/en-us/library/ms170208.aspx

Answer 2

I have been reading the adventureworks tutorial, but not yet found a similar situation. Maybe the example was bad, because usually the time dimension is made as you pointed.

The problem stays the same, I need to join two classifying variables when making the cube, so that the end user can choose in the browser items from the combined dimension.

Answer 3

In order for attributes to end up in the browser and be usable by the end user browsing the cube, they must be pulled over to the attribute pane (left panel).  You can access the dimension you are on in the right pane automatically but you can also right click in the right pane in pull in additional dimensions (tables) and pull their attributes to the left pane upon processing these will be in the same dimension folder when browsing.  Once again if you are dealing with the Time dimension I would suggest doing as I pointed out above or reading up more about this, it is difficult to wrap your head around it initially.

Answer 4

Hi Aulis,

You also can build a view to combine the 2 dimension attributes and then create the dimension against the view.

Here is useful link to introduce named query in data source view -

http://msdn.microsoft.com/en-us/library/ms175683.aspx

Regards,
Jerry

Answer 5

Thank you Jerry,

I made a query in the DSV layer and made a combined view of two dimensions. The new view having a duplicate key to the fact table and it's workin perfectly.

Best regards,

Aulis.

Answer 6

Hi Aulis

I am having similiar issue. I need to know how to create a combines view of two dimensions in DSV.

I am having two dimensions as Customer and Customer_flag. Both these dimensions have customer_key as primary key  and havedifferent information related to customer.

I want to see entire customer information in one Dimension / named query in DSV... how should i do it?

i tried this but i gave error. both customer and customer_flag are views in DSV and not tables.

Select c.*, cf.flag  from customer c

inner join customer_ flag cf

on c.customerkey = cf.customerkey