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
No comments:
Post a Comment