Using the dataset for Excel ad-hoc queries

[-Blair-]

Under the hood Power BI datasets are effectivly the same as SSAS now so in theory we should be able to use them the same.

I have sucessfully connected to the Power BI dataset hosted in the Power BI portal from Excel and can slice and dice.

The problem I am having is that it doesn't work with Power BI viewer access, I have to grant contributor access at a minimun.

Contributor access gives modify permissions to the dataset which I don't want. It will be analysts using the Excel pivot tables so it should be read only.

Any ideas how to limit the security and still have this work?

[Anonymous · Accepted solution]

Hi @-Blair- 

Unfortunately, the system does not support users who do not have the edit access for the dataset to slice and dice . Processing the data means that you need the edit access for dataset .

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[-Blair-]

This isn't a solution. It's really just highlighting a flaw in the product.

We need a viewer role for datasets, one that can be used from Excel.

[Anonymous]

Hi @-Blair- 

Yes,viewers can only view and interact with report or report .They don’t have the edit access for dataset ,so they cannot connecting dataset from Excel . If you want to connect to the dataset from excel, you must give them build permission(edit access) for the dataset.

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[-Blair-]

Which is the problem I am facing. I don't want analysts to have modify permissions to the dataset.

I want them to slice and dice via Excel like they would with analysis services.

Is there a way to give them contributor to connect but revoke edit permissions?

[Anonymous]

Hi @-Blair- 

(1)If you grant the workspace access for user, and just want them have read-only access , you can give them Viewer role in workspace .

More info about roles in workspace can refer to the link below .

https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-roles-new-workspaces

(2)If you just grant the read-only access for the specified dataset , choose the dataset and click … =>Manage permission=>Add user ,and only select the first parameter .

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[-Blair-]

I tried giving the user viewer access and they couldn't connect to the dataset from Excel.

It only worked with contributor.

Did you test connecting from Excel? If it's working for you I might be missing something.

[-Blair-]

This isn't related to Service Now.

Just like you can connect to Azure Analysis Services from Excel to slice and dice, you can do the same with a Power BI dataset.

The difference is in AAS you can limit the user connecting to read only. How do you limit the user connecting the the Power BI dataset to read only on the dataset?

I have only been able to get it working with contributor access which gives the user edit access to the dataset.

[Anonymous]

Hi @-Blair- 

I am a bit confused about the way you connect to the dataset. Can you explain in detail how you connect to your data source in the Service ?

In addition, what function do you want to use in Service now, can you clarify it ? For example, Analyze in Excel ? Because different functions have different requirements for permissions and user restrictions .

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.