Live Connection Vs. Direct Query for Datasets

[Pikachu-Power]

Hi all,

this article discribes a new way to connect with datasets:

Using DirectQuery for datasets and Azure Analysis Services (preview) - Power BI | Microsoft Docs

I have endusers they want to add an excel file with comments to the approved dataset. I was asking myself if this is a good way?   

I have following misgivings:

The enduser should need a gateway for the excel file in Power BI Service to distribute the report

The enduser could load new tables, do new calculations and may spread wrong results 

In my eyes the enduser is not technically savvy to do this work. Maybe someone has experience with this method. Which method (live connenction or direkt query) would be the better way?

Many thanks.

PS: Maybe the Data Modeler will have no control in future which way the enduser go? Than the question would be done 🙂

[Daryl-Lynch-Bzy]

Hi @Pikachu-Power , one of the advantages of Power BI is the ability to combine "Certified" datasets with other Data Sources.  The scenario of connecting to a Power BI Dataset and Excel file hosted on SharePoint Online or One Drive (note these are the same thing) is better than them connecting to Excel hosted in Network Drive.  A Data Gateway will be required to access a Network Drive.  You do not want to give the user access to Gateway to refresh Excel files.

"enduser is not technically savvy to do this work" - I agree, so it is unlikely that they will try.  Instead, they will ask for Excel or CSV extracts and store loads of data in endless Excel Workbooks.  You will have no visibility these files, and these potentially incorrect results will spread.  

So the Power BI Composite models are better.  You will have visibility of these.  End Users sharing will be limited and controlable.

My suggestion is to train People.  Power BI will add more business value if it moves outside of centralised IT control to competent Citizen Developers.

@Applicable88 

[Pikachu-Power]

OneDrive/excel file included in the approved dataset from the data modeler and the enduser just care for excel file right? And OneDrive should be shared with everyone who get the report at the end?

[Applicable88]

Yes, exactly. You give enduser just access to the excel file, which is to "share" in OneDrive terms. 

But the whole Dataset you don't have to give access. 

[Pikachu-Power]

What if the enduser uses direct query and combines the dataset with excel file which is in own onedrive? 

I just want to avoid that enduser makes some mistakes in excel file which leads to crashes over night. Normally the dataset is used for a different report.

[Applicable88]

@Pikachu-Power own OneDrive or shared with others is no problem. To my knowledge false data in the excel file won't crash whole report, just the visuals where for example the data from the excel are conflicting. You can tell the enduser whenever he makes changes to first make a backup copy and put it in another folder. if everything is okay it can be deleted. if not just overwrite the changed excel with the original one. 

Or you can set up the excel more thoroughly, that you cannot make mistakes so easily. Like restricting what others can do with the excel. Only input of data columns, but not allowed to change anything else. 

[Pikachu-Power]

Hello and thanks for the ideas!

Is it possible to create for an existing workspace a microsoft teams channel and upload the excel file there? I think in teams channel under files both sides should have access to the file and it should be possible to load that in power bi. 

I just saw the other way: when creating a teams channel a workspace is created. but i am looking for the other direction. 

[Applicable88]

@Pikachu-Power if the file is shared in OneDrive, I guess its possible. 

[Applicable88]

@Pikachu-Power I think if the rules to everyone are clear it could be okay. Gateway is imho very troublesome. And you need to be online to make that connection as well. 

But if every enduser is on the same Microsoft OneDrive environment, why not share that as a web address, instead of sole excel file? If all users are in same company OneDrive than just share a folder and copy the OneDrive of that excel file as source for your PowerBi report. 

Hope that helps.

Best.