Solved

Time Dimension in SSAS/SSRS

Posted on 2007-03-19
7
403 Views
Last Modified: 2016-02-12
I have just created my first cube in SSAS and am using it in SSRS.  I have a couple of questions for the experts on the best way to approach my task.  My cube is analyzing direct marketing campaigns, so at it's basic level there are source codes, mail qty and responses.  I need to have a maximum date of the dataset in my cube so I can use it in the reports.  What is the best way to do this?  Do I include the maxdate in the time dimension, or create a new dimension just for it?
0
Comment
Question by:JLEmlet
  • 3
  • 3
7 Comments
 
LVL 42

Expert Comment

by:dqmq
Comment Utility
Unless you've been listening to Art Bell too much, there is but one time dimension.  However, as a practical matter, time and date should have separate dimensions in most cases.
0
 

Author Comment

by:JLEmlet
Comment Utility
Thanks for responding.  So my take away is that the maximum date is not a time dimension, but a static dimension that I need to add.  
0
 

Author Comment

by:JLEmlet
Comment Utility
So then my question becomes the best way to get this dimension into the cube.  I can add a named query to the DSV, but when I try to add the dimension it says that it has no hierchy.  There's no other table that it joins to so it's just a stand alone table with one row and one column that has the maximum date in it.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 42

Expert Comment

by:dqmq
Comment Utility
Well, I didn't mean to say that maximum date is not a time dimension.  What I meant (somewhat fiippantly) was that we are only concerned about one set of times in the world as we know it. So, in an abstract sense, there is only one time dimension.  However, in practice, it's usually best to support the time dimension with two dimension tables: one for time and one for date.  I don't see any reason to map maxdate to one time dimension and other dates to another time dimension.

However, there is (usually) good reason to split dates and times and map each to it's own dimension table. Do the math. Suppose you want time dimension down to the second.  That's 60X60X2400 rows to represent all the times in one day.  With a date and time combined in the same dimension table, that's up to 86,400 rows each and every day.  However, if you separate have a speparate time table, it's 86,000 rows for time plus 1 row for each day.

 
0
 

Author Comment

by:JLEmlet
Comment Utility
I understand.  In our datamodel, time is meaningless.  None of the data in the database changes at a rate that is faster than a day.  So if I have one time dimension and make sure to populate it with only dates that are in the database (obviously), then I can take the max of it to get my max date linked with the dimension that controls the max date I'm looking for, right?
0
 
LVL 42

Accepted Solution

by:
dqmq earned 500 total points
Comment Utility
Almost.  I wouldn't limit the date dimension to just the dates already used. For example, you may want to pre-populate it for a whole year at once.   Instead, add a boolean column to the date dimension to identify the max date, much like you might have a holiday flag or a weekend flag.   Of course, you would need to update the max-date flag each day.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now