Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Advice needed - enterprise datasets

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

Need Help

3 Replies

  • collinq's avatar
    collinq
    Super User
    6 years ago

    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!