InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 23:10:53 GMT20http://www.sqlservercentral.com/Forums/Topic610071-292-1.aspx[quote][b]duda (12/9/2009)[/b][hr]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)")[/quote]This caused the other hierachy to only show measured data for only the current month.Wed, 09 Dec 2009 05:44:29 GMTBudaClihttp://www.sqlservercentral.com/Forums/Topic610071-292-1.aspxThanks 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)")Wed, 09 Dec 2009 04:12:56 GMTBudaClihttp://www.sqlservercentral.com/Forums/Topic610071-292-1.aspxHi,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,VincentTue, 10 Nov 2009 05:25:52 GMTVincentRainardihttp://www.sqlservercentral.com/Forums/Topic610071-292-1.aspxI 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?Tue, 10 Nov 2009 03:04:05 GMTBudaClihttp://www.sqlservercentral.com/Forums/Topic610071-292-1.aspxHi 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,dudaTue, 27 Oct 2009 01:32:22 GMTBudaClihttp://www.sqlservercentral.com/Forums/Topic610071-292-1.aspxHi 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.VincentMon, 26 Oct 2009 12:23:01 GMTVincentRainardihttp://www.sqlservercentral.com/Forums/Topic610071-292-1.aspx[quote][b]duda (10/26/2009)[/b][hr]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:DateSeq CalendarDate Yr1 Yr2 Monthname1 Yr1MonthNr Yr2MonthNr Yr1QNr Yr2QNr20090301 2009-03-01 2009 2008 March 03 12 01 0420090331 2009-03-31 2009 2008 March 03 12 01 0420090401 2009-04-01 2009 2009 April 04 01 02 01I 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 [/quote]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...Mon, 26 Oct 2009 09:56:11 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic610071-292-1.aspxThanks 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:DateSeq CalendarDate Yr1 Yr2 Monthname1 Yr1MonthNr Yr2MonthNr Yr1QNr Yr2QNr20090301 2009-03-01 2009 2008 March 03 12 01 0420090331 2009-03-31 2009 2008 March 03 12 01 0420090401 2009-04-01 2009 2009 April 04 01 02 01I 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 Mon, 26 Oct 2009 07:47:38 GMTBudaClihttp://www.sqlservercentral.com/Forums/Topic610071-292-1.aspxHi erin, I sent part 2 on 31st December so hopefully it would be published soon.VincentFri, 06 Feb 2009 23:49:06 GMTVincentRainardihttp://www.sqlservercentral.com/Forums/Topic610071-292-1.aspxHi. This article is great. I was just wondering when part 2 might be available. I'm interested in all of the topics listed, but especially the part on "Enabling users to select a date (or month) to be used in calculated measure using 'from date' and 'to date'." Thanks. Fri, 06 Feb 2009 15:19:17 GMTerin-590196http://www.sqlservercentral.com/Forums/Topic610071-292-1.aspxHi Rick, I create the date dimension from scratch. I never modified the SSAS project files directly (except for a few rare cases), I always use BIDS* to edit/create the SSAS objects. In the current project I put all the DSV queries in an SSMS project and version control it in VSS, in the same solution folder as the SSAS project.Kind regards,Vincent*Occasionally I use AMO.Tue, 02 Dec 2008 06:53:06 GMTVincentRainardihttp://www.sqlservercentral.com/Forums/Topic610071-292-1.aspxI found this article interesting. I feel like we have our Date dimension dialed, but it's always worth reading an article from someone that knows what they're talking about. And sure enough I found some interesting ideas.Now, a question for you and for the people hopefully tracking the posts: what's your solution for reusing the code if you end up doing multiple DSV statements or Views? Do you just rebuild the Date Dimension from scratch each time? Do you copy and past the existing Date.dim file and search and replace? How do you maintain that code going forward? I ask partly because I would like to embrace the different date ranges (we have some Facts with 35 years of data, and others with only 1 or 2, for example) but don't really want to maintain 4 different date dimensions.Please post thoughts!Cheers,RickMon, 01 Dec 2008 09:15:55 GMTRick Toddhttp://www.sqlservercentral.com/Forums/Topic610071-292-1.aspxHi Mike, using named queries gives us more flexibility, i.e. we can modify the cube without modifying the database structure. Using views gives performance improvements (processing time) if there are many joins and grouping in the SQL, involving large amount of source rows (&gt;10m) and the views are indexed. For example, a complex DSV SQL for a large measure group could benefit from an indexed view. For a date dimension sourced from a single table with 40k rows I don't think there will be differences on performance between named query and view. What I meant with performance here is the cube processing time; OLAP query time is not affected either way, unless it is a ROLAP partition. Another advantage of using views is code maintainability. It is beneficial in situations where the cube has many partitions and the partition source SQL queries are complex (long). The SQL queries for these partitions are almost identical except the WHERE clause used for partitioning. Putting these queries on a view would make them easier to maintain. For example, to add a measure we would only need to modify 1 view, rather than modifying the SQL on each partition.Sat, 29 Nov 2008 14:16:07 GMTVincentRainardihttp://www.sqlservercentral.com/Forums/Topic610071-292-1.aspx[quote][b]Vincent Rainardi (11/29/2008)[/b][hr]Dear all, I'd like to thank you all for reading this article and also for your comments.Hi Mike, when different ranges are required we can create several Named Queries with different WHERE clauses and then create a date dimension for each Named Query.Hi Piers, you are right, sometimes users get confused between month and month-in-year and making month as part of the hierarchy avoids this confusion.[/quote]OK thanks. Is there an advantage (performance-wise or other) to using SSAS Named Queries over doing it in a SQL view? I was thinking of using a SQL view with some table-driven logic built in to limit the date ranges to the min and max ranges for a specific column/attribute.Sat, 29 Nov 2008 10:01:07 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic610071-292-1.aspxGood article. :)Sat, 29 Nov 2008 09:55:15 GMTAnipaulhttp://www.sqlservercentral.com/Forums/Topic610071-292-1.aspxDear all, I'd like to thank you all for reading this article and also for your comments.Hi Mike, when different ranges are required we can create several Named Queries with different WHERE clauses and then create a date dimension for each Named Query.Hi Piers, you are right, sometimes users get confused between month and month-in-year and making month as part of the hierarchy avoids this confusion.Sat, 29 Nov 2008 08:03:38 GMTVincentRainardihttp://www.sqlservercentral.com/Forums/Topic610071-292-1.aspx'Although BIDS 2008 advised to hide the attributes used in the hierarchy, in my experience some users would still prefer to see those attributes. This enables them to use (for example) the month attribute directly either as a slicer or filter, without navigating through the Year-Month-Date hierarchy.'But surely the point here is that users always get massively confused between the month attribute and the month-of-year attribute. Making month only appear as part of the year-month-date heirachy makes it's this-is-a-specific-month role much clearer, leaving the month-of-year to be used as a slicer, which is normally what people want.Sat, 29 Nov 2008 01:48:39 GMTPiers Williamshttp://www.sqlservercentral.com/Forums/Topic610071-292-1.aspxGot the book, reading it now. Great stuff!I have a question for you though - I like to have a very large date dimension because the date ranges I deal with can be very long. In fact, they can cover up to 70 years of historical dates (throw in an additional +20, 30, 40 years for future dates). Not all dates fall in this range though, some might only cover a range of -20 to +20 years from the current year. When I role play with the same date dimension (-70 to +40 years for instance), SSAS pumps way too many dates into the cube with no data in them. What's your advice for limiting the date ranges for different date values?Thanks, and again great article!Mike CFri, 28 Nov 2008 16:14:12 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic610071-292-1.aspxGood, solid article on the date dimension.I love role-playing dimensions - they can expand business users' understanding of their data to points that they had never considered.For example, one of the solutions I had implemented was a warehouse for a medical insurer. On the date dimension, I added an attribute called "Season", which would have members like "Summer", "Autumn", "Winter", and "Spring".Initially they only wanted to track the seasonality of certain treatments e.g. do people visit GP's more in winter than in summer?BUTOne day they were slicing and dicing, and suddenly they saw they could use the same date dimension to track policy movements - they could see e.g. that more clients would join in autumn (just before the winter) compared to other seasons... Suddenly they had another perspective on their data, and his new view would have an impact on their marketing campaigns...The date dimension is an absolute treasure in BI solutions, and allows analysts to bring together data, and gain real insight, into the movements of their businessFri, 28 Nov 2008 06:29:23 GMTZanoni Labuschagne-766625http://www.sqlservercentral.com/Forums/Topic610071-292-1.aspxLiked it so much I bought the book.Fri, 28 Nov 2008 04:49:23 GMTSQL-DBAhttp://www.sqlservercentral.com/Forums/Topic610071-292-1.aspxGreat article, Vincent, though I found the non-date musings WAY, WAY more interesting that the actual information on the date dimension. I'm in a project right now where we don't have FK relationships in the datamart, and this surprised me. Nice to know I'm not crazy to think it odd, though.Keep up the writing and the great tips!Thu, 27 Nov 2008 23:26:42 GMTCalvin Lawsonhttp://www.sqlservercentral.com/Forums/Topic610071-292-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/Analysis+Services/64802/">The Date Dimension in Analysis Services</A>[/B]Thu, 27 Nov 2008 22:16:57 GMTVincentRainardi