Advice needed - enterprise datasets

[Anonymous]

Hi all,

I am after some advice / best practice done in your respective organizations.

I work for a mining company with various systems of records (historian with process data from Honeywell, ERP solution for procurement and maintenance from Oracle, etc). We have a large number of employees who all wish to interrogate these systems, extract the data relevant to them, build a report and in some instances publish it. It is fair to say that my organization is very conservative / backward on the IT side of things and has not implemented some of the best practices I've heard of.

Knowing our users won't learn SQL and we would be worried about them interrogating the db directly with a poorly written statement, what is your suggestion to make data available to them? Could the following work:

• have a SQL expert write a number of reports extracting all likely information from the db with date parameters to only pull incremental data (daily refresh from a base of 5 year history data loaded) - we already have a good idea of what data would be needed to meet 90% of the needs
• certify these reports and make them available to everybody to connect to?
• all users then can select the dataset relevant to them to create a new report from and upon publishing in a new workspace, have the admin change the parameters in the gateway to only pass the relevant data

For those of you who have done this, how do you get around the fact that you can't create columns or add an additional data source when connecting to an existing dataset?

Thanks in advance for any help / guidance you can provide.

Thanks,

OF

[collinq]

Hi @Anonymous ,

The question about "where to build" is ongoing and is debated constantly.  To me, it goes to the ability of the organization and the ongoing sustainability of the organization to maintain that data.

I prefer the Power BI way (having been a SQL Admin in the past).  I do exactly what you indicate.  I create the data model in Power BI as this gives me the most freedom and other folks (not just SQL experts and people with SQL permissions) can use it as per my security settings.

For the large dataset, we can then use incremental refresh in Power BI.

This allows me to have the security, data model, refreshes and ongoing maintenance in one place.

But, as I stated, some folks will have a different opinion.  We have just found that if yo uwant to make a change, you have to get onto the SQL folks' radar and often have to make a sacrifice to the IT guru to get a change made.

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!

[Anonymous]

Thanks @collinq 

One question: how do you handle someone needing to combine two data sources in a single pbi file, both of which living in two different datasets? THis is the biggest roadblock we have at the moment as well as wanting to add calculated columns and not being able to.

[v-yiruan-msft]

Hi @Anonymous ,

There is no direct or elegant method to combine different pbix files. You can refer to the method in the following documentations: make one of them as the main pbix file, and copy the data source and visualizations information from the other pbix files.

Consolidate queries from multiple Power BI pbix files into one file

Copy Measures Between 2 Power BI Files

Copy & paste visuals between Power BI Desktop files

Best Regards

Rena