CODEWITHSUNDEEP
daxssastabular
The Lyrist
The Lyrist

Reputation: 444

SSAS: The way to hide certain fields in a table from certain users

For a Microsoft Analysis Services Tabular (1500) data cube, given a Sales table:

CREATE TABLE SalesActual (
 Id Int,
 InvoiceNumber Char(10),
 InvoiceLineNumber Char(3),
 DateKey Date,
 SalesAmount money,
 CostAmount money )

Where the GP Calculation in DAX would be

GP := SUM('SalesActual'[SalesAmount]) - SUM('SalesActual'[CostAmount])

I want to limit some users from accessing cost / GP data. Which approach would you recommend?

I can think of the following:

  1. Split all the Sales and Cost into separate rows and create a MetricType flag 'C', 'S', etc. and set Row-Level Security so that some people won't be able to see lines with costs.

  2. Separate the into two different tables and handle it through OLS.

  3. Any other recommendations?

I am leaning towards approach 1 as I have some other RLS set-up and OLS doesn't mix well with RLS, but I also want to hear from the experts what other approach could fulfill such requirements.

Thanks!

UPDATE: I ended up going with the first approach.

  1. Tabular DB is fast for this kind of split
  2. OLS = renders the field invalid; and I'd have to create and maintain two reports... which is undesirable
  3. RLS is easier to control; and I think cost / GP is the only thing I'd need to exclude for now, but it also gives me some flexibility in the filter if I need to restrict other fields; my data will grow vertically, but I can also add additional data type such as sales budget, sales forecast, expenses and other cost, etc. into the model in the future. All easily controlled by RLS

The accepted answer works and would work for many scenario. I appreciate answerer's sharing, just that it doesn't solve my particular situation.

Upvotes: 1

Views: 1023

Answers (1)

msta42a
msta42a

Reputation: 3741

You can create a role where CLS does the job. There is no gui for CLS, but we can use a script (You can script your current role from SSMS "Script Role As", to modify - but better test this on new one)

{
  "createOrReplace": {
    "object": {
      "database": "YourDatabase",
      "role": "CLS1"
    },
    "role": {
      "name": "CLS1",
      "modelPermission": "read",
      "members": [
        {
          "memberName": "YourOrganization\\userName"
        }
      ],
      "tablePermissions": [
        {
          "name": "Sales",
          "columnPermissions": [
            {
              "name": "SalesBonus",
              "metadataPermission": "none"
            },
            {
              "name": "CostAmount",
              "metadataPermission": "none"
            }

          ]
        }
      ]
    }
  }
}

The key element is TablePermissions and columnPermissions in which we define which column / columns the user cannot use).

Upvotes: 1

Related Questions