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.