Regarding dimension renaming in AS2005 while migrtion

Hi,

My datamart has used dots in the dimension name. i.e. I have following dimensions

Users.Sales1 and Users.Sales2.

Currently my application has 2 cubes: Sales1 and Sales2.

Cube Sales1 is assocated with
"Parent Child" dimension Users.Sales1
which is based on view vw_Users_Sales1

Cube Sales2 is assocated with
"Parent Child" dimension Users.Sales2
which is based on view vw_Users_Sales2

Now when I am trying migration wizard, I am getting following dimensions

Users.Sales1 and Users1.Sales2.

Is there some way I can retain the existing naming convension?

And If not them what is the best convension or what will

will be the best possible to handle this scenario.

There are other dimensions as well which are getting renamed like this.

So I will like to know how others are handling this.

Thanks In Advance,

Rahul

The dimension model was changed in 2005. In 2000, you could have dimensions based off different tables that shared a common dimension name. The intention behind this was to support multiple hierarchies for the same dimension, but each one was still implemented as a seperate dimension, they just shared a common root name (Users, in your case).

2005 can now support multiple hierachies in the same dimension. However, they need to come from the same table or view. Since, in your case, they are comming from two different views, the wizard is creating two seperate dimensions. And you since you can't have two dimensions with the same name, the wizard is renaming one of them.

|||John is right, you might find that you are able to create a view or a named query as a quick way to join these tables together.

Referencing 2 dimensions

Hi all,

I hope someone can see a way around this.

I have a cube which is working well an need to add a new dimension. The new dimension does not have a reference to the fact table but refers to 2 current dimensions.

Is there a way of adding in the new dimension referencing the current dimensions, or will I need to go to the database and add a new foreign key to the fact table?

Thanks in advance,

David

Hi,

You can create a snowflake schema:

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

http://sqljunkies.com/WebLog/sqlbi/archive/2005/10/07/17040.aspx

Regards|||

Thanks Lucas.

I knew I wanted a many-to-many relationship, but didn't think of adding an extra Measure to do it.

Regards,

David.

Reference dimensions are creating too many joins

I have orders fact; customer, location and age dimensions. I am using age and location as reference dimensions through customer dimension. If I don't use reference dimension cube processing SQL would be like following

select * from factOrders

If I use reference dimensions SQL would be like following

select * from factOrders, dimCustomer as customer1, dimCustomer as customer2

where factOrders.customerid = customer1.customerid and

factorders.customerid = customer2.customerid

If I have 6 reference dimensions through customer there will be 6 joins. Is there any way I can eliminate these joins as I need only one join to get all the information?

I can create a view between factOrders and dimCustomer and use the reference dimensions as regular dimensions which will avoid all the joins but I feel I am not using the intutive features.

Processing time is significantly higher if I use reference dimensions compared to using as a view.

We had the same problem. We resolved it as below.

Add the customerid as another attribute in the age and location dimensions.

( you may the FK relation netween customer and location/age dimension tables.).

Now instead of using the referrence dimension relationship for age/location, use regular dimension and customerid as granular attribute to connect to fact table. This improved the performance a lot.

Hope this may solve your problem.

Regards,

Butchi Satuluri

|||Thank you Butchi!

Reference dimensions are creating too many joins

I have orders fact; customer, location and age dimensions. I am using age and location as reference dimensions through customer dimension. If I don't use reference dimension cube processing SQL would be like following

select * from factOrders

If I use reference dimensions SQL would be like following

select * from factOrders, dimCustomer as customer1, dimCustomer as customer2

where factOrders.customerid = customer1.customerid and

factorders.customerid = customer2.customerid

If I have 6 reference dimensions through customer there will be 6 joins. Is there any way I can eliminate these joins as I need only one join to get all the information?

I can create a view between factOrders and dimCustomer and use the reference dimensions as regular dimensions which will avoid all the joins but I feel I am not using the intutive features.

Processing time is significantly higher if I use reference dimensions compared to using as a view.

We had the same problem. We resolved it as below.

Add the customerid as another attribute in the age and location dimensions.

( you may the FK relation netween customer and location/age dimension tables.).

Now instead of using the referrence dimension relationship for age/location, use regular dimension and customerid as granular attribute to connect to fact table. This improved the performance a lot.

Hope this may solve your problem.

Regards,

Butchi Satuluri

|||Thank you Butchi!

Reefernce dimensions

We have designed a schema which has some snowflakes which we implement as Reference dimensions in SSAS. This design seems to be the most logical given that we have a dimension table called Portfolio, which has a number of related dimensions such as Client, Employee and Product. Instead of direcrly joining all these tables to the Positions fact table, we set them up as Reference dimensions via Portfolio.

I have read in a number of places (Lachev, Mundy) that there may be some issues with this approach, particularly on performance. Are there any metrics on what sort of performance degradation there may be? Does it depend on the number of Reference dimensions? Is it generally better if possible to always use the Standard dimensions by denormalising to a star schema - that is making Client, Employee and Product join directly to the fact table? Are there any other advantages or disadvantages, such as in the ease of data maintenance?

I would be grateful for any feedback on this.

I don't know what the performance implications of Reference dimensions are, other than that you should select the "Materialize" option on the Usage dialog, to improve query peformance - could you mention the respective page numbers in the 2 books, since many in this forum may have access to them?

But, regardless of whether you use Reference dimensions, or roll Client, Employee and Product into the Portfolio dimension, there should be no need to denormalize the snowflake to a star. For example, the Product dimension in Adventure Works has 3 tables, but there are other examples of Reference dimensions as well.