October 26, 2009 at 9:56 am
duda (10/26/2009)
Thanks and that’s a good article but I have a question though.I have 2 dimensions stored in the same SQL table (data mart).
Data for my date dimension is stored for 2 cycles. Both cycles have a 12 month period. The first cycle is from January to December and the other cycle is from April to March.
One row is used to store both cycles in the table.
See below for my dimensionTimeTable:
DateSeqCalendarDateYr1Yr2Monthname1Yr1MonthNrYr2MonthNrYr1QNrYr2QNr
200903012009-03-0120092008March03120104
200903312009-03-3120092008March03120104
200904012009-04-0120092009April04010201
I have one time dimensions in the cube with two hierarchies. They are both represented in two hierarchies with each hierarchy representing time cycles mentioned above.
My problem is that cycle one works fine while measuring anything but cycle2 only measures the current month and does not show any values for previous months.
Your help regarding above will be appreciated.
My problem is that
What are you trying to do? Calendar Year Dimension and Fiscal Year Dimension? Those are some big leaps in there (2009-03-01 to 2009-03-31, ...) Usually with a date dimension you want to include all days in your possible range of dates. I guess I'm having a hard time deciphering what you're doing here...
October 26, 2009 at 12:23 pm
Hi Duda, if cycle1 attributes work but but cycle2 attributes don't, please check the attribute relationships of between cycle2 attributes in the cycle2 hierarchy, especially the relationship from Year2MonthNumber to the key attribute. Do you make DateSeq column as the key attribute, or is it CalendarDate column?
Hi Mike, I think the 3 dimension rows that Duda quoted are not sequential rows. He only illustrated the sample values of the date dimension table. Yes I agree it looks like April to March is his financial calendar.
Vincent
October 27, 2009 at 1:32 am
Hi Mike,
Vincent is correct, the mentioned data is not sequential but sample data. As I said cycle1 is from the first of January to the 31st of December and cycle2 is from the 01st of April to the 31st of March.
Hope above makes sense.
Vincent I am double checking relaionships and yes the DateSeq column is the key attribute.
Ta,
duda
November 10, 2009 at 3:04 am
I cannot seem to find what the issue is with the relationships nor the properties of the non-working dimension. I have decided to re-create the time dimension. Instead of creating two hierarchies within one dimension I created two dimensions, one with a cycle from Jan to Dec and another one from April to March.
This worked on a small solution created but when implementing on the main project the cube processing fails with the following error.
MDXScript(Cube Name) (,14) The dimension ‘[LAST MONTH WITH DATA]’ was not found in the cube when the string, [LAST MONTH WITH DATA], was parsed.
MDXScript(Cube Name), (735, 25) The hierarchy ‘[YearQuarterMonthDay]’ was not found in the cube when the string, [Date].[yearQuarterMonthDay].[Day and Month and Year], was parsed.
I know that [LAST MONTH WITH DATA] is a calculated measure. Is there anything that I am missing here?
November 10, 2009 at 5:25 am
Hi,
the 2 error messages indicate that the calculated measure(s) are incorrectly written.
MDXScript(Cube Name) (,14) The dimension ‘[LAST MONTH WITH DATA]’ was not found in the cube when the string, [LAST MONTH WITH DATA], was parsed.
As Chris Webb showed as an example when writting about the single quote, the "dimension was not found" message usually occurs when we put something (not a proper MDX expression) in the calculated measure, something like "with member measures.X as [here we go]" then SSAS would response with "The dimension [here we go] was not found in the cube when the string [here we go] was parsed". Alternatively it could also means what it says, i.e. that dimension is not in the cube (incorrect name perhaps?)
MDXScript(Cube Name), (735, 25) The hierarchy ‘[YearQuarterMonthDay]’ was not found in the cube when the string, [Date].[yearQuarterMonthDay].[Day and Month and Year], was parsed.
Check Date dimension, see if you have an attribute (or a hierarchy) called [YearQuarterMonthDay].
HTH,
Vincent
December 9, 2009 at 4:12 am
Thanks guys, even though it took time for me to update you with this regard.
The main issue as to why I raised this is that I have 1 time dimensions with two hierachies. The first hierachy starts from the first of January and ends on 31 December whereas the other starts from 01 April and it ends on the 31st of March.
The issue here was that the other time dimension hierachy of the two was always showing measured data for only the current month however the other time dimension was always showed the current month.
I couldn't find where the problem was but I ended up re-creating the time dimension(New_Time_dimension).
There were some named_calculations that used members from deleted time dimension(old_time_dimension).
When creating the new time dimension I never used all members from the deleted time dimension and this caused the error posted above.
Instead I found out that the main cause of my problem is the below"
ALTER CUBE CURRENTCUBE
Update DImension [Date].[YearQuarterMonthDay],
Default_Member = StrToMember("Tail(Filter([Date].[YearQuarterMonthDay].[Month Name And Year].members,
Not IsEmpty([Date].[YearQuarterMonthDay].CurrentMember)), 1).Item(0)")
December 9, 2009 at 5:44 am
duda (12/9/2009)
ALTER CUBE CURRENTCUBE
Update DImension [Date].[YearQuarterMonthDay],
Default_Member = StrToMember("Tail(Filter([Date].[YearQuarterMonthDay].[Month Name And Year].members,
Not IsEmpty([Date].[YearQuarterMonthDay].CurrentMember)), 1).Item(0)")
This caused the other hierachy to only show measured data for only the current month.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply