SSAS Time Dimension Link

Posted on 2011-10-07
Last Modified: 2016-02-14
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.
Question by:VAMS1
    LVL 21

    Accepted Solution

    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


    where DATECOLUMN is your dattime column

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

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

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

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now