none
SSAS : Tabular Model Relationships with Date Dimension RRS feed

  • Question

  • Hi Everyone,

    Can you please clarify on the below confusion in the tabular models.

    Confusion is : What is the best way to create the relationships between fact table and Dim_Date table.

    I have Date Dimension with columns as DateID (Int Data type ), Date (DateTime data type where time part is always 00:00:00:000). And fact table contains the Date column as BusinessDate (Date data type).

    so can we create the relation on Date data type or can I create an Integer column in the fact table and create the relationships? is there any issues if I create on the date columns because Businessdate column in the fact is not having any time part but Date table has the time part in it. Can I assume Tabular model will take care of this automatically as the time part is zero all the time?

    DateID column in the date table has the values as 20200525

    Date column in the date table has the values as '2020-05-25 00:00:00:000')

    Fact table Businessdate column values like '2020-05-25'

    Thanks in advance.



    • Edited by Krish KKO Monday, May 25, 2020 10:59 AM
    Monday, May 25, 2020 10:58 AM

All replies

  • Hi ,

    When the column is YYYYMMDD, I don't think Tabular model will take care of this automatically. 

    But we could convert YYYYMMDD to datetime type, create a new column using 

    column = DATE(LEFT(TABLE[COLUMN],4),MID(TABLE[COLUMN],5,2),RIGHT(TABLE[COLUMN],2))
    

     and this should work for this situation.

    Regards,

    Lukas


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, May 26, 2020 2:46 AM
  • You can convert the BusinessDate filed as DateKey (YYYYMMDD) and then you can compare or compute as your need.

    SELECT CONCAT(YEAR(BusinessDate),REPLICATE('0',2-LEN(Month(BusinessDate)),Month(BusinessDate),REPLICATE('0',2-LEN(Day(BusinessDate)),Day(BusinessDate) FROM yourtable

    Thanks

    Atul

    Tuesday, May 26, 2020 3:51 AM
  • Tabular will match your dates from fact to your corresponding datetimes from Dim_Date. And, for Tabular, you should prefer date data types over integer DateIDs when working with calendar data.

    Still, if you don't need the time part in the Dim_Date's Date column, why don't you just change it to the Date datatype.


    Expect me to help you solve your problems, not to solve your problems for you.

    Tuesday, May 26, 2020 9:02 AM