Time Dimension in SSAS/SSRS

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?
JLEmletAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dqmqCommented:
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
JLEmletAuthor Commented:
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
JLEmletAuthor Commented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

dqmqCommented:
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
JLEmletAuthor Commented:
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
dqmqCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.