January 21, 2015 at 8:37 am
Hi,
im a little confused to say the least.
I have a date time dimension. in it Ive got Financial Month and Financial Year.
I have set up the key to be
Financial Year
Financial Month
But when i browse the attribute you get a massive long list of repeating items. I thought Id sorted this out by setting the Key.
I also have created a Hierarchy of Financial Month to Financial Year. In it the Financial Month has the key of Financial Year
Financial Month
And this works perfectly fine. the year drills down to month.
My question is, Why doesnt the attribute work like the attribute in the hierarchy?
Does that means you should hide all the attributes that are not in the hierarchy?
Debbie
January 21, 2015 at 8:54 am
Debbie Edwards (1/21/2015)
Hi,My question is, Why doesnt the attribute work like the attribute in the hierarchy?
Does that means you should hide all the attributes that are not in the hierarchy?
Debbie
1. Just because an attribute is not part of a hierarchy it doesn't mean that it isn't subject to the same rules in terms of having to specify key column(s) so that it displays correctly. The key of the attribute will determine how the "SELECT DISTINCT " behaves when the dimension is processed. If it's occurring in a hierarchy it could also have something to do with your attribute relationships. Have a read of the following for a good primer on the subject: http://www.bidn.com/blogs/DevinKnight/ssis/1887/ssas-understanding-keycolumns-dimension-property
2. If anything it's kind of the other way around - best practice dictates that is an attribute is part of a hierarchy then it should be disabled as a selectable attribute in the dimension. In any case, you should only have attributes that users will actually need in the dimension.
Hope that helps.
January 21, 2015 at 8:59 am
great
I will have a read of that. Id love to get rid of the Financial Month outside of the hierarchy but Im wary because Im uessing a user has already added it to a report and I dont want to mess anything up for them
Im very confused because as I say, Both Months have the same Key. Financial year and Financial Month. As they are set up the same I thought they would work the same. Unless Im missing something else?
I will have a read of that documentation. thanks again
Debbie
January 21, 2015 at 9:25 am
As it's a fiscal month do you have a fiscal quarter as a level in the dimension data that is between month and year? If so, that needs to be a part of the key columns too otherwise you'll get duplication.
If a user has it in a report then talk to them - it's a fairly simple change to make it point to the hierarchy instead.
January 21, 2015 at 9:29 am
Ill have a look at that. I dont think there is a quater that could be causing the issue but Ill have a look.
I wish I could go back and hide everything right from the start. it would be alot easier.
If there isnt anything and its just date, Month and year, is their anything else I could look at that I havent thought about?
thanks again
Debbie
January 21, 2015 at 9:38 am
Without knowing your source data it's difficult to say. I'd honestly be tempted to build a new date dimension on ssas - build it up bit by bit, frequently processing and eliminating any errors as they come up, making sure your attribute relationships are correct (you should be able to have rigid relationships, this will help with performance).
When you are happy then simply swap them over and reprocess the entire cube.
Doing it in this holistic manner will ensure that you catch any defects as they come up and give you the knowledge to be able to diagnose and fix any problems in the future. Your date dimension is arguably the most important so spend some time on it.
January 21, 2015 at 9:41 am
I can see alot more work on this for the future 🙁
Thanks for your help
Debbie
January 22, 2015 at 5:04 am
Well, Ive read the documentation and I remember using that to set up my keys.
Ive tried lots more things but still Financial Month just repeats. I set it to invisible but I cant help feeling like Im avoiding the issue. Ive run the dimension health check on it but cant find any issues.
My date time dimension contains all different levels. Financial, Standard, Academic Should I have created views for each type to seperate them out?
I have spent way way too much time on this issue but its really bugging me :blink:
Debbie
January 22, 2015 at 5:26 am
Im sorry Im not quite sure what you are wanting here?
Do you mean you want to know what data items are in my date time table?
TimeID
Date
AC_YEAR
Academic_Year
AC_TERM
Trimester
Month
Week
Day_Of_Year
Day_Of_Month
Standard Year
Standard_Month_Number
Financial Year
Financial Month
Academic Month
Weekday
Working_Day
For Financial Month I have set Financial year, Financial Month as the key and this works perfectly in the ones in Attribute relationships hierarchy.
I still think I should be able to get the stand alone attributes to work
January 22, 2015 at 5:38 am
Well thats interesting. i just created a test dimension. timeID, Date Financial Month and Financial Year based on a distinct view from my table.
I set the key up to be Financial Year, Financial Month for Financial Month and tested and Im still getting all duplicates for financial Month
So that shows I think that the other data items arent causing the issue. But Ive still now idea how to solve it.
Im beginning to really not enjoy this :blush:
January 22, 2015 at 6:28 am
Debbie Edwards (1/22/2015)
Im sorry Im not quite sure what you are wanting here?Do you mean you want to know what data items are in my date time table?
TimeID
Date
AC_YEAR
Academic_Year
AC_TERM
Trimester
Month
Week
Day_Of_Year
Day_Of_Month
Standard Year
Standard_Month_Number
Financial Year
Financial Month
Academic Month
Weekday
Working_Day
For Financial Month I have set Financial year, Financial Month as the key and this works perfectly in the ones in Attribute relationships hierarchy.
I still think I should be able to get the stand alone attributes to work
In your Key Columns collection what are the exact columns and data types being used?
What happens if you perform a SELECT DISTINCT Financial_Year, Financial_Month FROM <your date table> on the source? Do you get duplicate values then?
When you process the dimension what SQL is being sent to the source?
January 22, 2015 at 6:42 am
In your Key Columns collection what are the exact columns and data types being used?
For Financial Year, Just Financial Year
For Financial Month Financial Year then Financial Month
they are both Varchar because the default value is NA
What happens if you perform a SELECT DISTINCT Financial_Year, Financial_Month FROM <your date table> on the source? Do you get duplicate values then?
No there are no duplicate values coming up when i do a select distinct
When you process the dimension what SQL is being sent to the source?
I dont understand what you mean on this one?
January 22, 2015 at 6:55 am
Ok,
I think to simplify things this is what you need to do....
1. Keep your existing Financial Month attribute as invisible - this will be the one used in the hierarchy
2. In your abstraction layer create a column for FiscalMonthNoOfYear. This should be an integer from 1-12 representing the order of the fiscal months.
3. Create a new attribute with FiscalMonthNoOfYear as the sole Key Column and with Fianancial_Month as the name column.
4. Relate this new attribute directly to your existing Financial_Month column in your attribute relationships.
5. Process the dimension fully.
You will now have an attribute with distinct financial months that you can use in reports or wherever. This isn't exactly best practice but it solves your issue.
I still really advise just keeping the Financial Month hidden but as part of a hierarchy and then if users need it then it can be called with MDX using the following pattern: [Dimension].[Hierarchy].[Attribute].Members
January 22, 2015 at 7:11 am
Great,
Ill do some testing with that, I can deninitely work on that. thankyou.
Im having trouble on report drill downs with hierachy data which is one of the reasons I wanted to get the other atributes sorted. Also, its just a matter of principle now:-)
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply