<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss Content Posted by Vincent Rainardi
»
The Date Dimension in Analysis Services
22 posts, Page 1 of 3
1
2
3
»
»»
The Date Dimension in Analysis Services
Rate Topic
Display Mode
Topic Options
Author
Message
VincentRainardi
VincentRainardi
Posted Thursday, November 27, 2008 10:16 PM
SSC Journeyman
Group: General Forum Members
Last Login: Monday, January 14, 2013 4:24 AM
Points: 81,
Visits: 188
Comments posted to this topic are about the item
The Date Dimension in Analysis Services
Post #610071
Calvin Lawson
Calvin Lawson
Posted Thursday, November 27, 2008 11:26 PM
Mr or Mrs. 500
Group: General Forum Members
Last Login: Tuesday, June 21, 2011 10:03 AM
Points: 577,
Visits: 102
Great 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!
Signature is NULL
Post #610100
SQL-DBA
SQL-DBA
Posted Friday, November 28, 2008 4:49 AM
Valued Member
Group: General Forum Members
Last Login: Today @ 10:38 AM
Points: 73,
Visits: 271
Liked it so much I bought the book.
Post #610257
Zanoni Labuschagne-766625
Zanoni Labuschagne-766625
Posted Friday, November 28, 2008 6:29 AM
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, January 13, 2010 1:25 AM
Points: 5,
Visits: 23
Good, 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?
BUT
One 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 business
_____________________________________________________________________
Select 'Remember, we were all newbies once'
From Truth
Order by Experience
_____________________________________________________________________
Post #610313
Mike C
Mike C
Posted Friday, November 28, 2008 4:14 PM
Ten Centuries
Group: General Forum Members
Last Login: Wednesday, March 27, 2013 3:22 PM
Points: 1,276,
Visits: 1,112
Got 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 C
Post #610595
Piers Williams
Piers Williams
Posted Saturday, November 29, 2008 1:48 AM
Forum Newbie
Group: General Forum Members
Last Login: Monday, May 07, 2012 11:07 PM
Points: 6,
Visits: 54
'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.
Post #610661
VincentRainardi
VincentRainardi
Posted Saturday, November 29, 2008 8:03 AM
SSC Journeyman
Group: General Forum Members
Last Login: Monday, January 14, 2013 4:24 AM
Points: 81,
Visits: 188
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.
Post #610700
Anipaul
Anipaul
Posted Saturday, November 29, 2008 9:55 AM
SSCarpal Tunnel
Group: General Forum Members
Last Login: 2 days ago @ 2:50 AM
Points: 4,785,
Visits: 1,334
Good article. :)
Post #610733
Mike C
Mike C
Posted Saturday, November 29, 2008 10:01 AM
Ten Centuries
Group: General Forum Members
Last Login: Wednesday, March 27, 2013 3:22 PM
Points: 1,276,
Visits: 1,112
Vincent Rainardi (11/29/2008)
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.
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.
Post #610734
VincentRainardi
VincentRainardi
Posted Saturday, November 29, 2008 2:16 PM
SSC Journeyman
Group: General Forum Members
Last Login: Monday, January 14, 2013 4:24 AM
Points: 81,
Visits: 188
Hi 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 (>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.
Post #610774
« Prev Topic
|
Next Topic »
22 posts, Page 1 of 3
1
2
3
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
