• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 462
  • Last Modified:

SSAS Time Dimension Link

I am in the process of developing a cube and am having trouble adding time intelligence to it.

I have created a Time table on SQL Server database and a time dimension based on that table.

The problem is creating a relationship between the time table and another table based on a DATETIME field.

I have a DATETIME field in the oringal data that has values like "2000-01-01 22:15:00Z", but the newly created time table has a PK_Date field with values like "2000-01-01 00:00:00Z".  What I am trying to do is create a named calculation that would convert the DATETIME field to one that would allow me create a relationship between the two tables (i.e.  convert the 22:15:00Z to 00:00:00Z).

If there is a better way to solve this problem, I am open to suggestions.
0
VAMS1
Asked:
VAMS1
  • 2
1 Solution
 
Jason Yousef, MSSr. BI DeveloperCommented:
Hi,
Do you want to do a relationship between dates or dates with time also?
I mean link the Time table with your other table using date and time or just date and ignoring the time portion?

Anyway for the conversion, there are many ways...I personally would use

select DATEADD(DAY, DATEDIFF(DAY, 0, DATECOLUMN  ), 0)

where DATECOLUMN is your dattime column
0
 
VAMS1Author Commented:
I guess a better question would be to ask what is the best way to add time intelligence to the cube considering I have a DATETIME field in one original table in the format "2001-01-01 22:15:00Z"
0
 
Jason Yousef, MSSr. BI DeveloperCommented:
If you'll do a time table with every second or minute you'll end up with a huge table, nobody does that...
You can add your calculated members or after the relationship you show your datetime column and narrow your search by time.

You can let SSAS create your time dim or use such method to create it with extended members
http://beyondrelational.com/blogs/divya/archive/2011/03/22/creating-time-dimension-with-datetime-calculated-columns.aspx
0

Featured Post

Technology Partners: 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!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now