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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER