Solved

Time Dimension in SSAS/SSRS

Posted on 2007-03-19
7
407 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
ID: 18750822
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
ID: 18750924
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
ID: 18751849
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 42

Expert Comment

by:dqmq
ID: 18752986
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
ID: 18753383
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
ID: 18762457
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

776 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