none
How to implement a new table role playing dimension(SSAS tabular) based on time dimension which is linked to date dimension RRS feed

  • Question

  • Hello,

    Please can someone help me with this!I need to implement a new calculated table (SSAS tabular) on time with attributes from both time and date.

    please see the screen shot below.(multi dim model)

    Can somebody help me with the DAX Query or tell me if there is a way to implement the new table by name available_date(role playing).

    Thanks a lot!



    Thursday, July 11, 2019 4:47 PM

Answers

  • Martin's suggestion is correct for cases with "per minute" timings (or worse: per second), then it would be better to keep time and date as separate dimensions (with separate keys on fact side).

    Time with hourly steps shouldn't explode dimension much, let's assume 20 years x 365 days x 24 hours = 175K rows (vs per minute case with 10M rows, leading to quite heavy dimension), the only potential problem is having these additional attributes, best practice would be to minimize those (by removing unnecessary attributes from dimension, including some work on data type optimizations, but normally datetime/datetime2 formats are quite lightweight anyway, so suitable for join keys).

    Simplest way is instead of two separate views on SQL side
    vw_time (time_sk, time, hour)
    vw_date (date_sk, date, month..year)
    having cross-join into single one (if you have relevant permissions in your database) something like this:
    declare @dt date=dateadd(day,-3,getdate())--last 4 days
    --24 hr time table
    ;with vw_time as(select 0i,cast('00:00'as time)tx union all
         select i+1,dateadd(hour,i+1,cast('00:00'as time))
         from vw_time where i<=22)
    --date table
    ,vw_date as(select 0i,@dt d union all select i+1,dateadd(day,i+1,@dt)
         from vw_date where d<cast(getdate()as date))
    --cross join into datetime
    select dt.d,tm.tx,cast(dt.d as datetime)+cast(tm.tx as datetime)dat
    from vw_time tm cross join vw_date dt order by 3 option(maxrecursion 24);
    or implement similar join on SSAS side (via SQL query, if you can't create views)

    Friday, July 12, 2019 11:41 AM

All replies

  • There's quite a space for some ambiguity, 
    1) on a dimension key/connection to MG
      (I suspect it's actually composite {date+time=datetime}, supported by MD but non-existent in Tabular,
      at least without transformation into another SK)
    2) result of join between time table and date table
      (it might be just cross-join where each day has all times, or each day having relevant individual times only)
    3) it looks like time-date tables (forming dimension) are linked on date_sk
    (then time table would be huge, > date table)?

    Could you clarify these (preferable with data sample)

    General idea/options would be:
    a) creating a single view on a SQL side
    b) or write custom SQL query on SSAS (for import mode)
    c) or do join in M on SSAS side - if your version of SSAS supports it
    d) DAX join (calculated table) - as you've planned.

    Not sure about Tabular "best practice" (performance/maintenance) here though, really depends on specific case.
    Friday, July 12, 2019 12:26 AM
  • I've combined separate Date and Time dimensions into a single "Date" dimension with hour / minute as lowest level of hierarchy and will tell you from experience, don't. Just don't. The reason I did is that users had this requirement to query for the last 24 hours. I should have resisted and created a third dimension to satisfy this requirement and if you've got reporting requirements tracking a Time dimension, you're also going to have low latency reporting requirements. This means frequent processing. Problem? Every hour, you're running ProcessUpdate on the Date dimension MD or ProcessData/ProcessRecalc on Tabular. Huge problems on both as users are querying and you're processing....generally every stinking thing in your model as everything is associated with the "Date" dimension. So just a piece of advice. Don't use this approach. Ever. You've been warned.

    HTH, Martin


    Martin Mason Wordpress Blog

    Friday, July 12, 2019 2:26 AM
  • Hi Jennifer,

    You could new a calculated table, then DAX for that table is like this.

    [Calculated Table]=
    ADDCOLUMNS (
        'date',
    	"time_sk",RELATED('time'[time_sk]),
    	"time",RELATED('time'[time]),
    	"hour",RELATED('time'[hour])
    	)

    Best Regards,

    Will


    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.

    Friday, July 12, 2019 6:58 AM
  • Hi will,

    Thank you for your reply.

    But I am having a doubt now to create a new calculated table after seeing the above reply from Martin.He is saying that from his experience, so I don't think it will work in my case as well.

    But as Yuri in his reply has suggested that there are some more options to work with.

    Which one do you think will be best approach please?


    Friday, July 12, 2019 8:41 AM
  • Hi Martin,

    Thank you very much for your reply!I appreciate it!

    I think the situation is same with me.Even in multi dimensional this time and date dimension has created a problem always(one of the reason for migration).And what do mean by created a third dimension?Could you tell me how to do that in tabular?

    Thanks in advance!

    Friday, July 12, 2019 8:43 AM
  • Hi will,

    Thank you for your reply.

    But I am having a doubt now to create a new calculated table after seeing the above reply from Martin.He is saying that from his experience, so I don't think it will work in my case as well.

    But as Yuri in his reply has suggested that there are some more options to work with.

    Which one do you think will be best approach please?


    This would go back to your original thread.:-)

    https://social.msdn.microsoft.com/Forums/en-US/d48bcfe4-d4f7-43b0-a0fa-0a6185d97a09/ssas-tabular-modelrole-playing-dimension-implementationwhich-is-better-optionimport-the-date?forum=sqlanalysisservices, which approach you would choose depends on your actual environment and business requirements.

    Best Regards,

    Will


    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.

    Friday, July 12, 2019 8:54 AM
  • Hi Yuri,

    Thanks for your reply.

    See the screenshot below.

    Friday, July 12, 2019 8:56 AM
  • Hi will,

    Yes!Thank you!:)

    But its still very confusing to choose one approach!I want it easier to implement on my side!If you check the above screenshots you see the resulting hierarchy coming from that date-time join!

    Want to have that way in tabular as well(based on report requirements) :)

    Thanks again in advance :)


    Friday, July 12, 2019 8:57 AM
  • Hi All,

    Answer to my own question.We are convinced that its a really bad design that we have already.So will be going to design from scratch(This time no reference to multidim)!

    But thanks a lot and  I appreciate your inputs!

    Regards

    Jenni


    Friday, July 12, 2019 10:51 AM
  • Martin's suggestion is correct for cases with "per minute" timings (or worse: per second), then it would be better to keep time and date as separate dimensions (with separate keys on fact side).

    Time with hourly steps shouldn't explode dimension much, let's assume 20 years x 365 days x 24 hours = 175K rows (vs per minute case with 10M rows, leading to quite heavy dimension), the only potential problem is having these additional attributes, best practice would be to minimize those (by removing unnecessary attributes from dimension, including some work on data type optimizations, but normally datetime/datetime2 formats are quite lightweight anyway, so suitable for join keys).

    Simplest way is instead of two separate views on SQL side
    vw_time (time_sk, time, hour)
    vw_date (date_sk, date, month..year)
    having cross-join into single one (if you have relevant permissions in your database) something like this:
    declare @dt date=dateadd(day,-3,getdate())--last 4 days
    --24 hr time table
    ;with vw_time as(select 0i,cast('00:00'as time)tx union all
         select i+1,dateadd(hour,i+1,cast('00:00'as time))
         from vw_time where i<=22)
    --date table
    ,vw_date as(select 0i,@dt d union all select i+1,dateadd(day,i+1,@dt)
         from vw_date where d<cast(getdate()as date))
    --cross join into datetime
    select dt.d,tm.tx,cast(dt.d as datetime)+cast(tm.tx as datetime)dat
    from vw_time tm cross join vw_date dt order by 3 option(maxrecursion 24);
    or implement similar join on SSAS side (via SQL query, if you can't create views)

    Friday, July 12, 2019 11:41 AM
  • Hi Yuri

    Thanks a lot your reply!

    Friday, July 12, 2019 2:14 PM