CODEWITHSUNDEEP
jsonpowerbissaspowerbi-embeddedmultidimensional-cube
Jeffrey
Jeffrey

Reputation: 111

How to fix: "Operation returned an invalid status code 'BadRequest’" when connecting PowerBI Embedded to an SSAS Multidimensional cube

I have an (on premise) SSAS (Multidimensional) cube with a live connection to Power BI. Then it has to be shown in a portal with Power BI Embedded. I used the method: 'App owns data' and with a 'master user' account. This part works.

But when i try to add Row Level Security(RLS), it keeps giving errors. The report will be shown to customers (outside the organization). Based on their login (the authentication is held by the portal itself), they need to see their own data.

I tried to connect, using JSON script, adding username, roles, datasets and customdata. The username contains the actual active directory username which has permissions within SSAS. The customdata contains the part i want to filter. The role 'Test' is currently made for testing purposes.

The role 'Test' is setup in SSAS with read permissions and the specific 'company' dimension is setup with the following 'Allowed member set': STRTOMEMBER('[Dim Company].[BK_Company].&[{'+CUSTOMDATA()+'}]')

This is based on another topic which used this as the solution.

I have tried using USERNAME() to be the filter of RLS, but it seems I can only use actual accountnames in this field. Our current active directory doesn't hold all customer names in it.

var rls = new EffectiveIdentity(@"domain\powerbiportal", new List { report.DatasetId }, new List { "Test" }, "19164");

var tokenRequest = new GenerateTokenRequest("view", identities: new List { rls });

var tokenResponse = client.Reports.GenerateTokenInGroupAsync("[ID]", report.Id, tokenRequest).Result;

Sending JSON

{

"accessLevel": "view",

"identities": [

{

  "username": "domain\\powerbiportal",

  "roles": [

    "Test"

  ],

  "datasets": [

    "[dataset]"

  ],

  "customData": "19164"

}

]

}

The error i get is the following:

Operation returned an invalid status code 'BadRequest’

Upvotes: 1

Views: 1949

Answers (1)

Jeffrey
Jeffrey

Reputation: 111

After contacting Microsoft Support the problem was fixed.

First the solution was to uncheck the box 'enable read permissions' in the 'cell data' tab of the role. The cell was empty in my case, but for some reason it still created the problem.

Secondly the statement to filter had to be:

{STRTOMEMBER('[Dim Company].[BK_Company].&[' + CUSTOMDATA() + ']')}

instead of

STRTOMEMBER('[Dim Company].[BK_Company].&[{'+CUSTOMDATA()+'}]')

Upvotes: 1

Related Questions