none
SSAS Tabular Model-Role Playing dimension Implementation-which is better option?Import the Date table for every role and have active relationship or Import once and have Inactive Relationships RRS feed

  • Question

  • Hi,

    As already been mentioned in the title,can someone help me with my question please!

    I have been designing a tabular cube.There is a date dimension that is role playing.I was told there are options to have relationship between this date dim and fact.

    1)Import a date dim for every role and rename them accordingly and have active relationship.

    2)Use same date dim for every role but keep other relationship inactive with only one being active.

    Is this right?If so,which is better?what are difference between two approaches?

    Thanks a lot


    Wednesday, July 10, 2019 4:41 PM

Answers

  • Do you know what there's third option too: Creating calculated table based on imported one?
    BTW: inactive relationships are a bit harder to keep a track on (via DAX formulas/userelationship) in case if any changes on data model side are implemented.
    Another issue to consider - if there's a difference in performance (resolution on a fly via DAX or direct connection).
    • Marked as answer by Jennifer Zen Thursday, July 11, 2019 9:39 AM
    Wednesday, July 10, 2019 6:33 PM
  • Hi,

    Thank you for the detailed reply.

    I totally agree with you.Its far better to have it built in tabular from scratch, as it's total waste of time trying to replicate the multidimensional model.

    If only employee thoughts are valued. They have their own reasonings whatsoever(project deadline/complicated model/ reports  issues)etc .So its only one cube that's left in multidimension.So they want it migrated. All other ones has  been built from scratch in tabular.

    Thanks again!




    Hi Jennifer,

    For the question of original post, you may take a look at this blog, which could completely answer your doubts.

    Role-playing Dimension in SSAS Tabular Models

    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.

    • Marked as answer by Jennifer Zen Thursday, July 11, 2019 9:39 AM
    Thursday, July 11, 2019 2:50 AM

All replies

  • Do you know what there's third option too: Creating calculated table based on imported one?
    BTW: inactive relationships are a bit harder to keep a track on (via DAX formulas/userelationship) in case if any changes on data model side are implemented.
    Another issue to consider - if there's a difference in performance (resolution on a fly via DAX or direct connection).
    • Marked as answer by Jennifer Zen Thursday, July 11, 2019 9:39 AM
    Wednesday, July 10, 2019 6:33 PM
  • Hi,

    Thanks for the answer.

    I thought the much easier third option that you mentioned( import only once but can be referenced multiple times)is only available after 2016.If not,have to write DAX. We are on sqlserver 2012 and VS2015 at the moment so didn't gave a thought.

    Actually we are developing tabular cube from multidimensional one that we already have on the instance.Its kind of a migration project.so whole project seems bit of complicated since  the relationships works differently in both models.🙂

    There are errors everywhere(ambiguous etc)

    So any suggestions how I can approach the problem with this role playing dim?

    Thanks a lot in advance.







    Wednesday, July 10, 2019 8:56 PM
  • Just a small note: it seems a little bit pointless exercise to migrate from old server to old version.
    of course you might have very specific exotic benefit from it (company policy, licensing {SA}, etc.)
    but generally it's considered as "a lot of activity but not much productivity" type of scenario
    (P.S. SQL Server 2012 {SP3} is approaching EOL in just 3 years {2022}, then another migration spending resources/efforts? {again: with different functionality})

    ..and yes: MD and Tabular conceptually are quite different solutions with different functionality/specifics designed for a different purposes/approaches (each having it's own advantages/disadvantages over the other) , therefore obviously transferring solution from one system to another is not expected to be painless and straight-forward (one-to-one migrations are very rare and work only for very simple scenarios), sacrifices normally are made about giving up some functionality and performance, whole model review/redesign normally is made to leverage benefits of recipient system.
    Although to be honest Tab is starting catching up recently with incremental processing {not really much use though as it works differently}, aggregations {as separate tables}, calculation groups {vs scope}, M2M {sort of}, translations/perspectives, etc., let's hope they bring other goodies from MD too (Assemblies, Keys vs Names vs Value, proper incremental processing, file locations for scalability, TOM fixes, internal data model aware query optimizer, custom actions, etc.).
    Wednesday, July 10, 2019 9:29 PM
  • Hi,

    Thank you for the detailed reply.

    I totally agree with you.Its far better to have it built in tabular from scratch, as it's total waste of time trying to replicate the multidimensional model.

    If only employee thoughts are valued. They have their own reasonings whatsoever(project deadline/complicated model/ reports  issues)etc .So its only one cube that's left in multidimension.So they want it migrated. All other ones has  been built from scratch in tabular.

    Thanks again!




    Thursday, July 11, 2019 12:39 AM
  • Hi,

    Thank you for the detailed reply.

    I totally agree with you.Its far better to have it built in tabular from scratch, as it's total waste of time trying to replicate the multidimensional model.

    If only employee thoughts are valued. They have their own reasonings whatsoever(project deadline/complicated model/ reports  issues)etc .So its only one cube that's left in multidimension.So they want it migrated. All other ones has  been built from scratch in tabular.

    Thanks again!




    Hi Jennifer,

    For the question of original post, you may take a look at this blog, which could completely answer your doubts.

    Role-playing Dimension in SSAS Tabular Models

    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.

    • Marked as answer by Jennifer Zen Thursday, July 11, 2019 9:39 AM
    Thursday, July 11, 2019 2:50 AM
  • Hi Will,

    Thanks for the reply. That's what I was looking for but was curious when the other reply from Yuri mentioned there are other ways as well like implementing calculated tables on the original dim table.Looks like quite useful feature that has been added!I wanted to know what will be the performance and which stands as a best approach!

    But as of now i think I can manage with multiple import as mentioned in the article you linked, but definitely want to try calculated tables to really see and understand the difference it has on performance etc.

    Thanks to you both for replies!I appreciate it!

    Thursday, July 11, 2019 9:39 AM