Solved

Time Dimension in SSAS/SSRS

Posted on 2007-03-19
7
415 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

735 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