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.

Refresh cube after data changed?

After I created a cube in BI Studio, I added more data to the fact table, modified dimension table, and refreshed DSV. I got Refresh Data Source View message ‘No changes have been found’, but I didn’t see the data that I just added in from cube browser.

(Database connecting and database structure didn’t change)

My questions:

1. Is refresh DSV only for database structure change?
2. Which step I was missing for refreshing cube data?

Thanks in advance.

Yes, refreshing is to pull database structure changes into the DSV. To get the new data, "Process" the cube.

You'll find that MSAS provides a rich set of capabilities and abilities to update the data. For now, always do a "Full Process", until you get more comfortable with the technology. Depending on the size of your cube, plan on spending a little time learning about incremental processing and partitions. For anything other than small cubes, expect to define / manage partitions are part of the definitions. The impact on performance is dramatic.

|||Thank you very much Dave.

It is very helpful information.

Refresh cube after data changed?

After I created a cube in BI Studio, I added more data to

the fact table, modified dimension table, and refreshed DSV. I got Refresh Data

Source View message ‘No changes have been found’, but I didn’t see the data that

I just added in from cube browser.

(Database connecting and database structure didn’t change)

My questions:

1. Is refresh

   DSV only for database structure change?

2. Which

   step I was missing for refreshing cube data?

Thanks in advance.

Yes, refreshing is to pull database structure changes into the DSV. To get the new data, "Process" the cube.

You'll find that MSAS provides a rich set of capabilities and abilities to update the data. For now, always do a "Full Process", until you get more comfortable with the technology. Depending on the size of your cube, plan on spending a little time learning about incremental processing and partitions. For anything other than small cubes, expect to define / manage partitions are part of the definitions. The impact on performance is dramatic.

|||Thank you very much Dave.

It is very helpful information.

Refresh cube after data changed?

After I created a cube in BI Studio, I added more data to the fact table, modified dimension table, and refreshed DSV. I got Refresh Data Source View message ‘No changes have been found’, but I didn’t see the data that I just added in from cube browser.

(Database connecting and database structure didn’t change)

My questions:

1. Is refresh DSV only for database structure change?
2. Which step I was missing for refreshing cube data?

Thanks in advance.

Yes, refreshing is to pull database structure changes into the DSV. To get the new data, "Process" the cube.

You'll find that MSAS provides a rich set of capabilities and abilities to update the data. For now, always do a "Full Process", until you get more comfortable with the technology. Depending on the size of your cube, plan on spending a little time learning about incremental processing and partitions. For anything other than small cubes, expect to define / manage partitions are part of the definitions. The impact on performance is dramatic.

|||Thank you very much Dave.

It is very helpful information.

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.

referenced relationship error

I try to add dimension with referenced relationship. I defined everything like explaination in Books online, but when I try to save cube, I always get message:

"Cube 'Cube' cannot be saved because of the following errors:

Errors in the metadata manager. The 'Key' intermediate granularity attribute of the'IntDim'

measures group dimension does not have an attribute hierarchy enabled."

I enabled this attribute hierarchy for Key... but....

Anybody who can help?

When I tried these basic steps using the Adventure Works sample it worked for me. If you remove the dimension relationship and try to re-create it after saving the dimension with the key enabled, does the problem still occur? If so, could you send me the project files in a Zip?|||

How my problems started...

First of all, when I migrate OLAP DB, in one cube I had relation (referenced) by two fields in tables. I don't know is it supported in AS 2005. I think no.

This is my main question.

After that I try to do several different thinks, explaned in Books on-line (attributes, new named calculation...), but without good results... Unfortunatly, I spend two days to solve this problem, end on the end I changed this relationship between tables (replaced referenced with regular relationship) on the AS 2000 and made migrated again.

At the moment I have not project because I made new migration, but I will try to re-create all story and send to you..

I send to me proper your e-mail adress

|||

Here's my email. Just replace the "-" with an "@.".

Matt.Carroll-Microsoft.com

|||

Matt Carroll wrote:

Here's my email. Just replace the "-" with an "@.".

Matt.Carroll-Microsoft.com

Or you can send it directly to me. "Sasha.Juric" with same domain name as Matt's.

|||When I try to do it from the beginning, everything is ok. But when I migrate old Datebase in AS, I have problem. when i have time I re-craete it. But stay the question, if I use two fields in referenced relationship, does AS 2005 support it

referenced relationship error

I try to add dimension with referenced relationship. I defined everything like explaination in Books online, but when I try to save cube, I always get message:

"Cube 'Cube' cannot be saved because of the following errors:

Errors in the metadata manager. The 'Key' intermediate granularity attribute of the'IntDim'

measures group dimension does not have an attribute hierarchy enabled."

I enabled this attribute hierarchy for Key... but....

Anybody who can help?

When I tried these basic steps using the Adventure Works sample it worked for me. If you remove the dimension relationship and try to re-create it after saving the dimension with the key enabled, does the problem still occur? If so, could you send me the project files in a Zip?|||

How my problems started...

First of all, when I migrate OLAP DB, in one cube I had relation (referenced) by two fields in tables. I don't know is it supported in AS 2005. I think no.

This is my main question.

After that I try to do several different thinks, explaned in Books on-line (attributes, new named calculation...), but without good results... Unfortunatly, I spend two days to solve this problem, end on the end I changed this relationship between tables (replaced referenced with regular relationship) on the AS 2000 and made migrated again.

At the moment I have not project because I made new migration, but I will try to re-create all story and send to you..

I send to me proper your e-mail adress

|||

Here's my email. Just replace the "-" with an "@.".

Matt.Carroll-Microsoft.com

|||

Matt Carroll wrote:

Here's my email. Just replace the "-" with an "@.".

Matt.Carroll-Microsoft.com

Or you can send it directly to me. "Sasha.Juric" with same domain name as Matt's.

|||When I try to do it from the beginning, everything is ok. But when I migrate old Datebase in AS, I have problem. when i have time I re-craete it. But stay the question, if I use two fields in referenced relationship, does AS 2005 support it

reference member in user hierarchy

hi,

I have a user hierarchy in my Date Dimension such as [Calendar]->[Years]->[Quarters]

And I can access the member by directly call the memeber name, for example:

select [Date].[Calendar].[Years].[2006].[Q2] on columns, ...................................

The results will be correct. However, if I use reference to call the member, for example:

select [Date].[Calendar].[Years].[2006].&[2] on columns, ...................................

There will be nothing in my results.

Would you please tell me what shall I do to enable the reference call to my hierarchy member? Thanks.

With "select [Date].[Calendar].[Years].[2006].&[2] on columns, you are referencing the keys for the member.

"[Date].[Calendar].[Years].&[2006].&[2]" might help.

Another method is [Date].[Calendar].[Years].Lastchild.Lastchild. With this method you will always find the last quarter of the last year in your time dimension.

Regards

Thomas Ivarsson

|||

Hi, Thomas

Thanks a lot for your answer. I have also tried "[Date].[Calendar].[Years].&[2006].&[2]" but no use. I still get nothing from that.

I was forced to use the format: "[Date].[Calendar].[Years].&[2006].&[2]" because this is a fixed format in a third-part software. I guess maybe there is sth wrong about the settings of my user hierarchy?

|||

It can be a problem with key for the Quarter member. Are you using integers like 1,2,3,4 for Quarters? In that case you can make a combination by year and quarter for the quarter key. Check also your attribute relation between quarter and year in the dimension editor.

Regards

Thomas

|||

Hi, Thomas,

Thanks and would you please give me a little bit more details?

Do you mean that I should use 1,2,3,4 as the quarter key for Quarters?

And do you mean that I should build the attribute relation between quarter and year? I have tried both with/without attribute relation but it did not work. Thx.

|||

No. You should use a composite key with both the year and the quarter. A quarter key like 1,2,3,4 is not unique. If you klick on the key-column, for the level, in the properties(dimension editor) you can add year to quarter(in the key) and create a collection. This is in the dataitem collection editor.

Regards

Thomas Ivarsson

|||

hi, Thomas,

Thanks and I have tried to add a composite key (year+quarter) according to your steps.

But during processing, there is an error message: The key columns of the Quarter measure group attribute do not match in either number or data types to the key columns of the source attribute.

Would you please tell me how to deal with this problem? Thanks a lot.

|||

Hello. Make a new column, for either year or quarter, and use the TSQL convert och cast-function to change the data type to something common.

You can also simply use the datepart function for year and quarter and return two integer fields.

Regards

Thomas Ivarsson

|||

hi, Thomas

I have checked that in the AS2005, my Year and Quarter are in the format: Integar.

And in the data source view, I see that the source columns of the key column are both: System.Int32

However, the error still exists, do you have any idea about this? Thanks.

|||

BTW, do I also need to change the Name Column (or the value column) to the integer column I defined in the source?

|||

hi, Thomas,

I have finally get the result I want ([Date].[Calendar].&[2005].&[2] is available).

And the step follows: 1. Remove any attribute relationship in dimension date. 2. Build a new integer column (1,2,3,4) as the source of the key column for Quarter. 3. Specify the key column of Quarter to that new integer column.

I do not know if this will cause any other problems. Anyway, thanks a lot for your help.

|||

I realized this now. So you have a join on one of the levels in your time hierarchy above the leaf level? In the dimension usage tab for the cube you must use the same combination as in your composite key. You do not have to change the name column.

Check the Adventure Works Demo project that is part of the installation. Have a look at the date.dim there(and the attribute month_name(properties)) and check the relation between the date dim and sales targets measure group in the Adventure Works cube.

Regards

Thomas Ivarsson

|||

Nice that it works. Without attribute relations you will not get any aggregations on the time dimension. Another problem is that your calculations can be wrong. What you needed to do was to add 2006(Year) to the quarters(1,2,3,4) so that their collection will point to year and quarter, at the same time. Actually you combine each year with each of their quarter in this way. Quarter(1,2,3,4) will not point to a specific year if you do not do this(ie add the year key to each quarter key).

Regards

Thomas Ivarsson

|||

Hi, Thomas

Thanks a lot for your response! I have several questions about your answer.

First, what do you mean by have a join on one of the levles above the leaf levels? And how can I see that?

Second, I have seen the AdventureWorks cube and see the attribute month_name has a combination key column of year + month. But in the dim usage table, I do not know how to modify my own project. I can see a RED line under my date dimension and I think that indicates error. However, if I add an attribute relationship between Quarter and Year, the dim usage table will automaticlly add this relationship to itself. (In other words, I can not find the place to add sth about the combination key in the dim usage table)

|||

Hello again Jeremy. If you scroll out to the right, in the dimension usage tab of the cube editor(In the Adventure Works demo cube), you will find a box where the dimension date intersect with the measure group Sales Targets. All the other relations between date and the measure groups in this cube are at the lowest(leaf level) or the box empty(no relation). Click the box(date dim and Sales Targets) There you can see the granularity attribute for the relation, thats the level that is used in the join between the measure group and the dimension.

Check here that you join on the correct column to avoid the error message you talked about before: "The key columns of the Quarter measure group attribute do not match in either number or data types to the key columns of the source attribute." You will only have to this if you have changed the key column for the Quarter member in your time dimension.

Regards

Thomas Ivarsson

reference member in user hierarchy

hi,

I have a user hierarchy in my Date Dimension such as [Calendar]->[Years]->[Quarters]

And I can access the member by directly call the memeber name, for example:

select [Date].[Calendar].[Years].[2006].[Q2] on columns, ...................................

The results will be correct. However, if I use reference to call the member, for example:

select [Date].[Calendar].[Years].[2006].&[2] on columns, ...................................

There will be nothing in my results.

Would you please tell me what shall I do to enable the reference call to my hierarchy member? Thanks.

With "select [Date].[Calendar].[Years].[2006].&[2] on columns, you are referencing the keys for the member.

"[Date].[Calendar].[Years].&[2006].&[2]" might help.

Another method is [Date].[Calendar].[Years].Lastchild.Lastchild. With this method you will always find the last quarter of the last year in your time dimension.

Regards

Thomas Ivarsson

|||

Hi, Thomas

Thanks a lot for your answer. I have also tried "[Date].[Calendar].[Years].&[2006].&[2]" but no use. I still get nothing from that.

I was forced to use the format: "[Date].[Calendar].[Years].&[2006].&[2]" because this is a fixed format in a third-part software. I guess maybe there is sth wrong about the settings of my user hierarchy?

|||

It can be a problem with key for the Quarter member. Are you using integers like 1,2,3,4 for Quarters? In that case you can make a combination by year and quarter for the quarter key. Check also your attribute relation between quarter and year in the dimension editor.

Regards

Thomas

|||

Hi, Thomas,

Thanks and would you please give me a little bit more details?

Do you mean that I should use 1,2,3,4 as the quarter key for Quarters?

And do you mean that I should build the attribute relation between quarter and year? I have tried both with/without attribute relation but it did not work. Thx.

|||

No. You should use a composite key with both the year and the quarter. A quarter key like 1,2,3,4 is not unique. If you klick on the key-column, for the level, in the properties(dimension editor) you can add year to quarter(in the key) and create a collection. This is in the dataitem collection editor.

Regards

Thomas Ivarsson

|||

hi, Thomas,

Thanks and I have tried to add a composite key (year+quarter) according to your steps.

But during processing, there is an error message: The key columns of the Quarter measure group attribute do not match in either number or data types to the key columns of the source attribute.

Would you please tell me how to deal with this problem? Thanks a lot.

|||

Hello. Make a new column, for either year or quarter, and use the TSQL convert och cast-function to change the data type to something common.

You can also simply use the datepart function for year and quarter and return two integer fields.

Regards

Thomas Ivarsson

|||

hi, Thomas

I have checked that in the AS2005, my Year and Quarter are in the format: Integar.

And in the data source view, I see that the source columns of the key column are both: System.Int32

However, the error still exists, do you have any idea about this? Thanks.

|||

BTW, do I also need to change the Name Column (or the value column) to the integer column I defined in the source?

|||

hi, Thomas,

I have finally get the result I want ([Date].[Calendar].&[2005].&[2] is available).

And the step follows: 1. Remove any attribute relationship in dimension date. 2. Build a new integer column (1,2,3,4) as the source of the key column for Quarter. 3. Specify the key column of Quarter to that new integer column.

I do not know if this will cause any other problems. Anyway, thanks a lot for your help.

|||

I realized this now. So you have a join on one of the levels in your time hierarchy above the leaf level? In the dimension usage tab for the cube you must use the same combination as in your composite key. You do not have to change the name column.

Check the Adventure Works Demo project that is part of the installation. Have a look at the date.dim there(and the attribute month_name(properties)) and check the relation between the date dim and sales targets measure group in the Adventure Works cube.

Regards

Thomas Ivarsson

|||

Nice that it works. Without attribute relations you will not get any aggregations on the time dimension. Another problem is that your calculations can be wrong. What you needed to do was to add 2006(Year) to the quarters(1,2,3,4) so that their collection will point to year and quarter, at the same time. Actually you combine each year with each of their quarter in this way. Quarter(1,2,3,4) will not point to a specific year if you do not do this(ie add the year key to each quarter key).

Regards

Thomas Ivarsson

|||

Hi, Thomas

Thanks a lot for your response! I have several questions about your answer.

First, what do you mean by have a join on one of the levles above the leaf levels? And how can I see that?

Second, I have seen the AdventureWorks cube and see the attribute month_name has a combination key column of year + month. But in the dim usage table, I do not know how to modify my own project. I can see a RED line under my date dimension and I think that indicates error. However, if I add an attribute relationship between Quarter and Year, the dim usage table will automaticlly add this relationship to itself. (In other words, I can not find the place to add sth about the combination key in the dim usage table)

|||

Hello again Jeremy. If you scroll out to the right, in the dimension usage tab of the cube editor(In the Adventure Works demo cube), you will find a box where the dimension date intersect with the measure group Sales Targets. All the other relations between date and the measure groups in this cube are at the lowest(leaf level) or the box empty(no relation). Click the box(date dim and Sales Targets) There you can see the granularity attribute for the relation, thats the level that is used in the join between the measure group and the dimension.

Check here that you join on the correct column to avoid the error message you talked about before: "The key columns of the Quarter measure group attribute do not match in either number or data types to the key columns of the source attribute." You will only have to this if you have changed the key column for the Quarter member in your time dimension.

Regards

Thomas Ivarsson

reference member in user hierarchy

hi,

I have a user hierarchy in my Date Dimension such as [Calendar]->[Years]->[Quarters]

And I can access the member by directly call the memeber name, for example:

select [Date].[Calendar].[Years].[2006].[Q2] on columns, ...................................

The results will be correct. However, if I use reference to call the member, for example:

select [Date].[Calendar].[Years].[2006].&[2] on columns, ...................................

There will be nothing in my results.

Would you please tell me what shall I do to enable the reference call to my hierarchy member? Thanks.

With "select [Date].[Calendar].[Years].[2006].&[2] on columns, you are referencing the keys for the member.

"[Date].[Calendar].[Years].&[2006].&[2]" might help.

Another method is [Date].[Calendar].[Years].Lastchild.Lastchild. With this method you will always find the last quarter of the last year in your time dimension.

Regards

Thomas Ivarsson

|||

Hi, Thomas

Thanks a lot for your answer. I have also tried "[Date].[Calendar].[Years].&[2006].&[2]" but no use. I still get nothing from that.

I was forced to use the format: "[Date].[Calendar].[Years].&[2006].&[2]" because this is a fixed format in a third-part software. I guess maybe there is sth wrong about the settings of my user hierarchy?

|||

It can be a problem with key for the Quarter member. Are you using integers like 1,2,3,4 for Quarters? In that case you can make a combination by year and quarter for the quarter key. Check also your attribute relation between quarter and year in the dimension editor.

Regards

Thomas

|||

Hi, Thomas,

Thanks and would you please give me a little bit more details?

Do you mean that I should use 1,2,3,4 as the quarter key for Quarters?

And do you mean that I should build the attribute relation between quarter and year? I have tried both with/without attribute relation but it did not work. Thx.

|||

No. You should use a composite key with both the year and the quarter. A quarter key like 1,2,3,4 is not unique. If you klick on the key-column, for the level, in the properties(dimension editor) you can add year to quarter(in the key) and create a collection. This is in the dataitem collection editor.

Regards

Thomas Ivarsson

|||

hi, Thomas,

Thanks and I have tried to add a composite key (year+quarter) according to your steps.

But during processing, there is an error message: The key columns of the Quarter measure group attribute do not match in either number or data types to the key columns of the source attribute.

Would you please tell me how to deal with this problem? Thanks a lot.

|||

Hello. Make a new column, for either year or quarter, and use the TSQL convert och cast-function to change the data type to something common.

You can also simply use the datepart function for year and quarter and return two integer fields.

Regards

Thomas Ivarsson

|||

hi, Thomas

I have checked that in the AS2005, my Year and Quarter are in the format: Integar.

And in the data source view, I see that the source columns of the key column are both: System.Int32

However, the error still exists, do you have any idea about this? Thanks.

|||

BTW, do I also need to change the Name Column (or the value column) to the integer column I defined in the source?

|||

hi, Thomas,

I have finally get the result I want ([Date].[Calendar].&[2005].&[2] is available).

And the step follows: 1. Remove any attribute relationship in dimension date. 2. Build a new integer column (1,2,3,4) as the source of the key column for Quarter. 3. Specify the key column of Quarter to that new integer column.

I do not know if this will cause any other problems. Anyway, thanks a lot for your help.

|||

I realized this now. So you have a join on one of the levels in your time hierarchy above the leaf level? In the dimension usage tab for the cube you must use the same combination as in your composite key. You do not have to change the name column.

Check the Adventure Works Demo project that is part of the installation. Have a look at the date.dim there(and the attribute month_name(properties)) and check the relation between the date dim and sales targets measure group in the Adventure Works cube.

Regards

Thomas Ivarsson

|||

Nice that it works. Without attribute relations you will not get any aggregations on the time dimension. Another problem is that your calculations can be wrong. What you needed to do was to add 2006(Year) to the quarters(1,2,3,4) so that their collection will point to year and quarter, at the same time. Actually you combine each year with each of their quarter in this way. Quarter(1,2,3,4) will not point to a specific year if you do not do this(ie add the year key to each quarter key).

Regards

Thomas Ivarsson

|||

Hi, Thomas

Thanks a lot for your response! I have several questions about your answer.

First, what do you mean by have a join on one of the levles above the leaf levels? And how can I see that?

Second, I have seen the AdventureWorks cube and see the attribute month_name has a combination key column of year + month. But in the dim usage table, I do not know how to modify my own project. I can see a RED line under my date dimension and I think that indicates error. However, if I add an attribute relationship between Quarter and Year, the dim usage table will automaticlly add this relationship to itself. (In other words, I can not find the place to add sth about the combination key in the dim usage table)

|||

Hello again Jeremy. If you scroll out to the right, in the dimension usage tab of the cube editor(In the Adventure Works demo cube), you will find a box where the dimension date intersect with the measure group Sales Targets. All the other relations between date and the measure groups in this cube are at the lowest(leaf level) or the box empty(no relation). Click the box(date dim and Sales Targets) There you can see the granularity attribute for the relation, thats the level that is used in the join between the measure group and the dimension.

Check here that you join on the correct column to avoid the error message you talked about before: "The key columns of the Quarter measure group attribute do not match in either number or data types to the key columns of the source attribute." You will only have to this if you have changed the key column for the Quarter member in your time dimension.

Regards

Thomas Ivarsson

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!

Reference Dimension causing long processing times

I have a reference dimension that is linked to a regular dimension that is linked to the fact table. For some reason, when I process the database with some small sample data, it take about 5 minutes to process, where it should take 10 seconds. I am using a reference dimension because it is a child-parent hierarchy, and I don't think I can merge it into the regular dimension. My work-around is to just include the link to this reference dimension in the fact table, so I can create the child-parent dimension as regular. Now is processes in 10 seconds. It doesn't seem ideal since I have what seems to me an unessary dimension now.

If I turn "materialize" off, it processes in 10 seconds, but I don't know the implications of this.

Does anyone have any idea of what is going on or have any advice?

Thanks,

Ernie

Are you using a referenced dimension here because the parent column is in another table from the child column? If so, you could create a view on top of them that encapsulates the relationship. What are the other issues you see regarding not being able to merge these columns into a regular dimension?|||

Thanks for the reply Scott.

The child and parent are in the same view. I've created another dimiension because the child attribute has to be a key. The child attribute, or key of this dimension, is not what joins directly to the fact table. There is an intermediate "field" that has to create this link, which is a foriegn key in the regular dimension, which has a key to link to the fact table. Hmmm, did I say that right? I may not be explaining things well, as I'm a bit new at this. Anyway, this is why I "think" I can't merge them.

Maybe this is a valid example:

Fact table has column SubAccount

SubAccount table has Account column (multiple sub-accounts per account)

Account table has AccountParent column which created the child parent relationship.

In this example, I would create a regular SubAccount dimension and a reference dimension called Account. My "workaround" is to put both the Account and SubAccount columns in the Fact table (or view).

Ernie

|||

It sounds like you should merge the sub account and account into a single dimension (make it a parent-child). You'll use a snowflake schema which will describe the join between the account and sub account tables as a source for the dimension, yet only have a single dimension.

Without doing this, if you tried going down the road of having two account dimensions (subaccount and account), then you'd end up with cases where you would get confusing results (like subaccounts that are not really related to an account showing up together, etc.)

Hope that helps

|||

Scott (or anyone else):

I am running into a problem that seems to be related to the one described by Ernie above. Here is the situation:

1) I have a referenced dimension (dimA) that joins to the fact table (factB) through an intermediate dimension table (dimC).

2) The referenced dimension (dimA) has a parent-child heirarchy. The primary key of this table (keySelf) and the parent key (keyParent) are in the same table (dimA) i.e. the parent-child hierarchy is stored in the same table and not in a separate table.

3) I am able to deploy the cube and it gives correct results.

4) I am currently in the design stage so I am dealing with fairly small data sets. Therefore, I donot know if it is performing poorly during deployment.

5) My problem occurs when I try to design aggregations. I get the error at the end of the wizard when I am deploying the aggregations. The error is on the Measure group that is based on factB. The error states:

"Errors in the high-level relational engine. The binding for the 'standardId' column is not a ColumnBinding type."

Note that 'standardId' is the primary key (keySelf) in the dimA table. i.e. it is the key that maps factB to dimA (through dimC).

6) The error goes away if I uncheck the "Materialize" option in the Define Relationship window for the relationship between factB and dimA.

7) I have other referenced dimensions in the cube that relate to factB through dimC that do not have a parent-child hierarchy and they do not give this problem.

So it seems that if a referenced dimension has a parent-child hierarchy and is materialized then the aggregations run into problem. I realize that I can eliminate the issue by making dimA a regular dimension of factB but I am curious to understand why this is happening.

Is this expected behaviour or am I doing something wrong?

Thanks beforhand.

Regards,

Amardeep

|||

Amardeep, this sounds like it could be a bug that you're hitting. I'll forward this on to our test team to verify.

One thing you could do to work around the issue that you're seeing though (since not materializing the dimension will be slower at query time) would be to not use a referenced dimension and instead use a regular dimension. To do this you'll need to modify your data source view (DSV) to include additional relationships in order to allow the join to work properly.

Hope this helps.

Reduce Records Shown From Dimension

Hi There,

I'm relatively new to AS2005, so you'll have to excuse me if there is a simple solution that has been overlooked.

Here is the situation - We have a fact table, linked to several dimensions - one of them being a generic date dimension used by a number of fact tables. The Date Dimension has all dates ranging from 1901 to 2100, but the fact table I am querying just has records from 2005 to present. Once the cube is processed and I open the cube in Excel (for example) it lists every date from the Date Dimension, is there any way to limit which dates are processed into the cube to those that just appear in the fact table (in essence - what would be an Inner Join in TSQL).

Hope this makes sense,

Regards,

Tobias

Are you using the dimension on rows or columns? Or as a filter?

If it is rows or columns you change the setting to not show empty values.

If it is a filter, you could create a set that only contains only those dates that has rows. Regardless, you could probably remove a lot of dates from the time dimension that is not used by any fact table and/or create a view that adds dates as time goes by.|||

Instead of telling the cube (via the data source view) to read directly from the time dimension table, we tend to use db views that restrict the members to only those members that appear in the fact table. You can change the dsv to read from the view and you will then only get dates that appear in the fact table.

However, with the time dimension, you want to make sure you fill in all dates of a year -- if you don't you can't really do year-over-year comparisons reliably. So, perhaps you can have the view return all dates from your time dimension table that start AFTER the first date in your fact table. You can use similar logic to restrict the dates on the high end as well.

|||

So does this mean, for each fact table - you have a different view set up?

We have about 15-20 cubes that we want to set up which all use the time dimension, with varying volumes of historical data from each of the source systems. Is it best practise to then limit the data returned by the dimension for each group of cubes?

I was hoping that there would be a technique/feature that prevented 'redundant' (i.e. rows that do not have a related fact record) dimension members from appearing in the cube, that way we could begin to build the suite of cubes in a smaller number of projects and reduce administrative overheads (by having several Time Dim views for different date ranges for example)

Regards,

Tobias

|||

Well, our one view looks at all of our various fact tables to determine which entries are needed.

The problem you have is that Excel will list out all dimension members, so I'm not sure what the best solution is for you. But I don't think there is magic property that you can tell Excel to list only dimension members that are only for a particular cube.

|||Is it Excel 2007 you use? Because then you can create a named set that only contains dates that are non empty, has rows in the fact table connected to it. I′m not sure if Excel 2003 can use sets.|||We're using Excel 2003. shame there isn't a feature that does that.|||

Even in Excel 2003 pivot table, you have the option to hide the members that do not have data.

As you are using time dimension in different measure groups, the best practice would be to create hierarchy in the date dimension like Year ==> Month ==> Date levels that would make the navigation easier for the users. There are lot of articles on ways to handle it. Pl refer to BOL and newsgroups.