Security and scd-2 in tabular over time

  • I have a scd-2 on our Employee table, tracking changes to the manager reporting chain, pulled into the tabular model as the Employee dimension. I have a fact table – call it Hiring. I have an organizational hierarchy set up in the tabular model based upon the Employee management chain. I need to implement security in SSAS by login username(), accounting for the scd-2 changes.

    For example: A person logs into the tabular model, and the requirement is that he can see all of the data in the Hiring table for himself and the people reporting to him. I can get security working, using the path function and the management hierarchy, so that he can see all of the data for the people currently reporting to him. But I’m stumped as to how to make security work through the hierarchy or path functions in different time periods other than the current. The manager is supposed to also be able to see data in the Hiring fact for the time period where people that previously reported to him, but now report to someone else, but the manager is not allowed to see any data for time periods where a person does not report to him.

    Jim worked for Mark from 1/2015 to 6/2015. Jim then transferred to work for Rachael from 6/2015 to 8/2015. Mark is allowed to see Hiring data for Jim from 1/2015 to 6/2015, but not for the current date. Rachael is allowed to see Hiring data for Jim from 6/2015 to the current date, but not for prior to 6/2015.

    Mark wants a report of all Hiring data for his employees for 1/2015 to the present, so he should see Mark’s data for the first part of the year, but not the latter.

    Rachael’s report for the year should show data about Mark for only the latter part of the year, and not for the earlier part.

    Does anyone have any suggestions?

    Thank you.

Viewing 0 posts

You must be logged in to reply to this topic. Login to reply