OneDrive – My Gal Again
Perhaps you think I am coming late to the OneDrive party. Actually I came early, and have finally returned. Years ago I “dated” Microsoft Mesh (a part of Windows Live) and felt jilted when her guardian (Microsoft) ended our relationship by taking her off the market. I tried others (Dropbox, Zoho, Google…) but no spark, no zeal, and my Customers typically blocked them at the firewall. Well, time heals all wounds, hardware/software never stand still, and customers evolve. These things fell into place: SkyDrive becomes OneDrive, Corporate America adopts Office365 (which includes 1TB OneDrive for Business), Solid State Drives become affordable, and the OneDrive Sync feature matures. SHAZAM! The old girlfriend is back, cuter than ever and I’m desperately seeking a long term relationship. I’m giddy for her all over again.
Seriously, OneDrive Sync could not have come at a better time for me. I spend most of my day pulling data, mashing data, posting data, repeat. It’s more glamourous than it sounds because I use smoking hot tools like PowerPivot & Power Query, but the drudgery of managing the files does get me down. This is further complicated by the fact the pulling/posting often involves other PCs with special applications and security. Then there is my “workhorse” laptop that does most of the ETL & reporting magic (via large memory & Solid State Drive). I needed help keeping my source files, ETL files, and my PowerPivot files in sync across all these devices. OneDrive Sync lets me do just that.
Now the nitty-gritty. Like all my other girlfriends, OneDrive & Sync are not perfect. Beyond the cute smile, fancy clothes, and sweet perfume there are a few less attractive features to this beauty. Frankly if I were not able to deal with them, this relationship would have never made it past the first date.
Challenge #1) You can only sync data that is stored in your Windows User directory (i.e. C:\Users\JohnDoe\OneDrive for Business). So in my case, ETL processes that feed PowerPivot are hard coded to a specific path such as C:\Data – this is a huge problem. I can’t feasibly change all my code, and even if I could the paths would not be consistent as I use different User IDs on different devices.
Solution #1) Move the data to the sync folder, then create a symbolic link to that location. For me it was as simple as dropping to a Command Prompt and running the old DOS Command:
mklink /d c:\Data “C:\Users\JohnDoe\OneDrive for Business\Data”
Note the quotes around the destination path are required since the path has spaces.
Now when my ETL & PowerPivot processes look for data at C:\data they get redirected to the OneDrive synced folder. No changes required for any of my applications/scripts/data models/etc. I do suggest you review your device’s backup strategy to make sure it properly handles this setup – but that’s another topic.
Challenge #2) Excel is too dang smart. When you have Excel save a workbook to the OneDrive synced folder it knows there is a SharePoint server in the mix (OneDrive) and Excel will not only write to the local drive, but it will also write to SharePoint. WHAT??? To make matters worse, you can’t turn this Excel “feature” off. WHAT WHAT??? Yes, I was a bit surprised by this too. You hardly notice it if the file is small and your path to the SharePoint server is fast – but I’m talking about OneDrive (SharePoint in the cloud) and large PowerPivot files… painful slow… makes my Solid State Drive seem slower than a 1980’s floppy drive…
Solution #2) Use the Symbolic Link you created for Challenge #1. You see if Excel saves the workbook to the path C:\Users\OneDrive for Business\Data\ then Excel sees SharePoint and insists on saving to it – BUT – if Excel saves the workbook to the path C:\Data\ it is not smart enough to realize SharePoint is in the mix and it will behave normally. Gotta love it when 1 fix works for 2 problems.
I’ll also mention here that UNC paths fool Excel also. So if for some reason you choose not to use the symbolic path shown above you can specify the path to save your workbooks as \\<device name>\Users\JohnDoe\OneDrive for Business\Data and Excel will not notice SharePoint.
So here I am in a newly re-committed relationship with the ole gal (OneDrive for Business with Sync). At this point my gal is syncing over 1,000 files that amount to over 40GB of data. Of that about 1 to 10GB is updated daily and my gal ain’t complaining. Nothing like a love story with a happy ending.
A few other notes/references:
“OneDrive” is the consumer version. “OneDrive for Business” is the commercial version available in Office 365. All references to OneDrive in my comments here refer to “OneDrive for Business”. I have only casually used the consumer OneDrive and probably won’t until Microsoft integrates the two with a single client.
OneDrive is surely due to mature in the coming months. The challenges/solutions discussed here may be moot very soon. Stay tuned.
The setup/configuration/usage of OneDrive for Business is easy and well documented in many places such as here… here… and here… No need to repeat in this blog.
I have not tested, but I suspect the solutions described here will disable any multi-user edit functions in Excel.
Though I often kid about my “girlfriends”, I should clarify that I’m happily married and otherwise fully occupied working out the bugs in that relationship.
brentpearce 