Solved

Posted on 2011-10-07
441 Views
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
Question by:VAMS1

LVL 21

Accepted Solution

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

Author Comment

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

LVL 21

Expert Comment

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 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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
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
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.