Dataset Access from PowerBI Desktop

[Luis92]

Hi there,

We have a problem with a direct query Dataset but we are unsure if there is a solution for this. 

We have a Gateway connection (pointed to our PostgreSQL database) to PowerBI, I have created a direct query dataset so other users with Contributor access to the PowerBI instance can manipulate the data and create reports on it.

However, when the contributors download the dataset and try to manipulate the data (transform data) they are asked to add a local model which subsequentely adds another dataset with a reference to the first dataset when published.

Alternatevily, we can transform the data without adding a local model, but in this case it seems that the contributors need direct access to the database, which is not something we want.

Is there a way for us to have a direct query dataset and allow contributors to manipulate the dataset and create reports without having to create a second dataset and without having to provide direct access to the database? 

Any help with this would be much appreciated.

[Luis92]

Hi @Bipin-Lala thank you very much for your reply.

This post was from a little ago and eventually our requirements drove us in exactly the direction you mentioned so that's spot on.

Thank you again, will accept your solution.

Regards

[Bipin-Lala · Accepted solution]

Hi @Luis92,

In Power BI, when you connect to a published dataset, the default behavior is a Live Connection. As you've discovered, a Live Connection is "Read-Only" for the model schema - you can't add tables or transform data.

To transform data, you have to switch to a Composite Model, which creates a new "chained" dataset or second dataset as you call it.

Solution:

If you want to give them the ability to "transform" without touching the database, Dataflows are your best friend.

1. Instead of a DirectQuery dataset, build a Dataflow that connects to your PostgreSQL DB via the Gateway.

2. Your contributors connect to this Dataflow in Import Mode.

3. They can transform the data to their heart's content in Power BI Desktop. They never need the database password because the Gateway handles the "handshake," and they only publish one primary dataset.

Dataflows are ideal in your scenario. Let me know how it goes!