Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The Date Dimension in Analysis Services


The Date Dimension in Analysis Services

Author
Message
Rick Todd
Rick Todd
SSC Veteran
SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)

Group: General Forum Members
Points: 222 Visits: 441
I 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,
Rick


Rick Todd
VincentRainardi
VincentRainardi
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 191
Hi 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.
erin-590196
erin-590196
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 49
Hi. 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.
VincentRainardi
VincentRainardi
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 191
Hi erin, I sent part 2 on 31st December so hopefully it would be published soon.
Vincent
BudaCli
BudaCli
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1166 Visits: 598
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 Yr2QNr
20090301 2009-03-01 2009 2008 March 03 12 01 04
20090331 2009-03-31 2009 2008 March 03 12 01 04
20090401 2009-04-01 2009 2009 April 04 01 02 01

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 you don't know won't hurt you but what you know will make you plan to know better
Mike C
Mike C
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1349 Visits: 1168
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:

DateSeq CalendarDate Yr1 Yr2 Monthname1 Yr1MonthNr Yr2MonthNr Yr1QNr Yr2QNr
20090301 2009-03-01 2009 2008 March 03 12 01 04
20090331 2009-03-31 2009 2008 March 03 12 01 04
20090401 2009-04-01 2009 2009 April 04 01 02 01

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...
VincentRainardi
VincentRainardi
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 191
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
BudaCli
BudaCli
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1166 Visits: 598
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

What you don't know won't hurt you but what you know will make you plan to know better
BudaCli
BudaCli
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1166 Visits: 598
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?

What you don't know won't hurt you but what you know will make you plan to know better
VincentRainardi
VincentRainardi
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 191
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search