Hi, i have to include a couple of questions here because of the nature of this challenge. We run sql server 2019 std. My understanding is that even 2019 std has an rls feature.
we want added security on the related "cost" portion of our fact sales record. But best practices years ago always said "keep facts with the same granularity on the same fact record".
we have a sales cube which sources excel pivots and pbi dashboards/reports. we have the same structure in our sales data warehouse (sql 2019 std) which also sources pivots and pbi dashboards/reports. and its been years but i recall studying rls as a pbi feature too. we are limited to pro licenses so i dont know if it applies. BUT as i recall rls is an easily "rolled your own" behavior using some simple dax in pbi.
so ...1) is there a column level security feature or concept anywhere in these products or as a "roll you own", 2) i think im going to get a proposal soon that we split up the cost column (to a different record) from the revenue in order to achieve rls on cost and am wondering what the community thinks about this, when/if we move to a non expiring account (service principal) in pbi, how will pbi know with which acct the user went into pbi so rls can be applied correctly?
some additional notes:
-we are looking at going to azure with ssas, ie severing it completely from the engine (sql , ssis, ssrs). but not immediately.
-one of my peers believes that if the context of a pivot is high enough in pbi or excel where 1 or move division's costs purposely not included, we should tell our users that the measure isnt always right. I disagree with this.
