Power BI Service Concept

[IcingOnTheCake]

Dear Community,

I have a question concerning the concept you follow in your Power BI environment.

Currently we have the following requirement:

We have standardized data in sql server. This data derives from several different sources.
Sales, Inventory, Marketing, GL facts etc... and the corresponding dimensional data.
Now we would like to provide a STANDARDIZED, PROTECTED datamodel for the users, which then can be extended/advanced by the users.
The problem we are facing now:

- if we provide dataflows, the users dont have a real model. No relations, calculated measures etc 

- if we provide datasets, the users are prevented to develop anything by themselves. No new columns. No ability to incude additional data etc
- if they (copy) a provided dataset, so that they are able to enhance it, we duplicate our data several times. Have to refresh it and if something changes in the original dataset, we would have to change it in each copy.

Maybe we miss something in the idea of Power BI. But I'm sure we are not the first company, that has this problem.

So whats your concept? 
Thank you

[v-shex-msft]

Hi @IcingOnTheCake,

I think you can try to build a template report with multiple data source connections.

It should do the process to get data from the specific data source with the connection string you provide, then you can do shaping and transform received data to the specific data structure to generate the data model tables and execute correspond calculate formulas and visuals. 

For example:

The template has two parameters, one is the data source type, the other is the connection string. You need to add if statements to check the 'data source type' parameter and use corresponding data connectors with 'connection string' and extract specific fields from endpoint data tables and convert them with static table structure which matches with current report design.

Change the Source of Power BI Datasets Dynamically Using Power Query Parameters 

Deep Dive into Query Parameters and Power BI Templates 

Notice: you need to limit the end-user data source types because these connections required you to manually defined them in the query steps.

Regards,

Xiaoxin Sheng

[collinq · Accepted solution]

Hi @IcingOnTheCake ,

If you provide the dataset and give the users Build Permission (and you aren't using RLS) then they will be able to make their own reports.  As for their ability to add new datasources, you are correct that they can't do that yet.  However, according to the Power BI Roadmap the ability for users to use composite dataset is coming soon (like next month soon).  https://docs.microsoft.com/en-us/power-platform-release-plan/2020wave2/power-bi/direct-query-over-po...

I would appreciate Kudos if my response was helpful. I would also appreciate it if you would Mark this As a Solution if it solved the problem. Thanks!

[IcingOnTheCake]

Thank you so much @collinq Very interesting concerning the composite dataset.
When you write: "..then they will be able to make their own reports" what exactly do you mean?
I know that you are able to use an existing Dataset and build a report. But then you are very limited with functions, right? 
You can't add measures or columns for example. Am I right or do I miss anything? Is it somehow possible to gain the users more functions with this "build permission"?

Thank you very much!

[collinq]

Hey @IcingOnTheCake ,

It is my understanding that they can with build.  But, I could be wrong about that and might be mixing up that they have to have the composite ability (coming soon). I just tested it and I could add things - but, i am not sure how "pure" my test was because even though I had somebody else share with Build permissions to me I am a full Power BI admin and am not sure if that is overriding the test.  If you test this, please let us know as like I say, my test may not have been very effective.

I would appreciate Kudos if my response was helpful. I would also appreciate it if you would Mark this As a Solution if it solved the problem. Thanks!

[IcingOnTheCake]

Hi @collinq,
thanks for your answer. And sorry for the late reply. I am not at the office every day.
My test results are, that you are able to create a report and add measures. But thats all.
You cannot add coloums and you have no possibility to have  a look at the datatables. 
The menue options are missing as soon as you are connected to an existing dataset with build permission.

Do I understand correct, that this will be possible with composite datasets soon? 
In the link you've provided, I cannot read details about this. Sounds interesting, but I dont know whether you will be able to add colums or have a look at the raw data for example. For sure, and thats great!, the users will be able to add other datasources.
Thank you