Solved

Time Dimension in SSAS/SSRS

Posted on 2007-03-19
7
417 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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 eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Suggested Solutions

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

751 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