1 BIG reason to use the Sharepoint Folder connection in Power BI
What’s the main advantage to using the Sharepoint Folder connector in Power BI? BUSINESS CONTINUITY.
The ability for essential reporting to carry on in a business while key report builders are out of the business (i.e. sick leave, holiday or just plain old left the company). How does the Sharepoint Folder connection in Power BI help with that?
Let’s imagine we work at a company who is just starting out on their Power BI journey. They haven’t got a cube or SSAS or anything whizzy like that set up. Certainly not a dedicated Power BI team. Report builders create all reports using the import mode i.e. connecting PowerQuery in Power BI to Excel files, folders, PDFs and so on. The files that Power BI is connected to are all saved on employees’ OneDrive accounts or Hard Drives.
Now let’s say that some of our Analysts disappear from the Company for a while. With the current PowerQuery set up, with files being saved to OneDrives and Hard Drives, this would be a disaster for business continuity.
Why?
While imported files are saved to a OneDrive or HardDrive, only the account holder or machine owner can get at them to update them or add new files. And of course, until files are updated, or new files are added to a folder, the essential Power BI reports cannot be refreshed with new information!
How does the Sharepoint Folder connector help?
With the Sharepoint Folder connector on the other hand, imported data is stored in a shared space, a folder that anyone can access as long as they have the access rights to that Sharepoint folder. And of course, if no-one else has access to that folder, it’s not difficult for an IT team to grant access to the relevant folders. If the main employee who refreshes the Power BI file (PBIX file) is not available, it is a simple enough task for another employee to obtain the files, save them in the right folder and refresh the PBIX file.
Why not use a Shared Drive?
Some might say that a shared drive does the same thing – a repository that multiple people can access at multiple times. And to an extent, they’d be right.
Both options are also easy to connect to initially. To connect to a Sharepoint Folder, (unsurprisingly) you select the ‘Sharepoint Folder’ option, while to connect to a Shared Drive, you select the ‘Folder’ option.
Both Sharepoint and Shared Drives can have employee restrictions on folder access so the risk of an unauthorised person fiddling about with base data could also be limited.
Anyone who has access to the Sharepoint folder or the Shared Drive folder can make changes to the query settings in PowerQuery. However there are some slight challenges for each method:
For Sharepoint, each user needs to ‘sign in’ if a different user has been previously using PowerQuery in the Power BI file. This can be done through the Data Settings option on the PowerQuery ribbon. Simple enough.
For Shared Drives on the other hand, employees often have the same shared drive mapped to a different drive letter on their computers. For example, Employee Y might have the shared drive mapped to Y: while Employee Z will have the folder mapped to drive Z:. This doesn’t look like a problem, but the shared drive folder address that Power BI connects to will be different for each computer. Power BI will not be able to find the Shared Drive on Employee Z’s computer if the connection was set up using Employee Y’s Shared Drive address. However, this is not insurmountable and we can resolve the issue through the use of parameters.
However the key difference is that with a Power BI file that is connected to a Sharepoint folder, teams can set up automatic refreshing of the report on Power BI online.
While Automatic refreshing can be set up when a Power BI file is connected to Shared Drive, this requires a Gateway. The Gateway is basically a way to allow Power BI online to access the base data in a repository that’s not always online (like OneDrive, Sharepoint and Outlook) for example a shared drive or hard drive
The Gateway is installed on one single computer and that computer needs to be on and logged into whenever the report is being refreshed. That can cause problems with overnight refreshing, is less ecologically efficient etc etc. Additionally, IT teams often need to be asked for permission to download software onto employees’ computers. That means a business case, risk assessment and so on and it’s all a bit slow! Using the Sharepoint Foder Connector option is by far the better option.
Is there a downside to using the Sharepoint Folder option?
Yes – the whole PowerQuery experience in Power BI using the Sharepoint Folder is very, very slow compared to a Shared Drive, One Drive or Hard Drive. It’s slower on connection, it’s slower to perform transformation steps in PowerQuery and it’s slower on refreshing for new data if refreshing on Power BI desktop. MUCH slower.
We can get round the slow initial connection and slow transformation. We can do this by setting the original Folder connection up to a OneDrive, Shared Drive or Hard Drive folder and then swapping the connection over to a Sharepoint folder once the file architecture is complete and it’s ready to load to Power BI online.
You can’t get round the slow refresh rate if you’re refreshing manually on the desktop. However if you’ve set up automatic refreshing in Power BI online then of course it doesn’t matter how long the file refresh takes as it will be done in the background online.
What do we think then overall?
In terms of business continunity, both Shared Drives and Sharepoint would do a satisfactory job depending on the circumstances. Companies should avoid essential reports being connected to files located in OneDrive or hard drives as it’s so much more difficult to access the base file folders to refresh information, particularly on a long term basis.
Connecting to a Sharepoint folder is definitely the best option if you’re planning on setting up automated refreshing. If you’re not planning automated refreshing, then connecting to a Shared Drive would be a satisfactory option as it can be much quicker to refresh and build the initial file.
Alongside this blog, we should also mention that essential reports should only be shared from a shared Organisational App and NOT from a ‘My Workspace’ workspace. Anyone with the appropriate App Workspace access can access a PBIX file, upload changes, set automated refreshing, give file access and so on. This is not the case with a report shared through My Workspace where only the owner of the workspace can perform those changes.
