Forum Discussion

rjhale's avatar
rjhale
Helper V
10 years ago

Using BI Desktop with Excel File in OneDrive for Business

We have an Excel file that has a data model derived from a Microsoft Access DB.  The Excel file and subsequent data model is updated locally and then synced to OneDrive for Business.  We then use the...
Need Help
rjhale's avatar
rjhale
Helper V
10 years ago

It seems that I can't use the file -> import feature for a file that exists in my OneDrive for Business space.  I did try using the file -> import function and pointed it at the file using the OneDrive for Business client, however; when I published this file the refresh was disbled on the data source. 

 

My original data source was a Microsoft Access table which I loaded into a PowerPivot data model.  That data model is part of the Excel file that is sitting in my OneDrive for Business space.  

pqian's avatar
pqian
Microsoft Employee
10 years ago

I think your best course of action to have a refreshable data set published from PBI desktop is the following setup:

AccessDB (local) <- Personal Gateway <- PowerBI.com
 ^
 +------- PowerBI Desktop

I haven't verified this before, maybe this will also work:

 

AccessDB (OneDrive) <- PowerBI.com
 ^
 |------- PowerBI Desktop

In other words, put the data source directly to OneDrive and connect to that. You don't really need Excel in the picture.

 

For the calculations that you already defined in your existing Excel Data model, you need to migrate them over by importing or rewriting

 

  • rjhale's avatar
    rjhale
    Helper V
    10 years ago

    The first option probably isn't the best option for us using the data gateway requires a Pro account. We're trying to avoid that for the moment because it will likely be a while before we have an outlet for purchasing accounts.

     

    I like your second suggestion, and I've actually setup a test to do just that. I'm running into a new problem though. I put the Access DB on ODB, connected to it using the Web functionality in BI Desktop, published the project to the BI service, setup the oAuth2 authentication in the service.  The data and reports come over correctly, but when I choose Refresh Now I get the following error:

     

    Can't connect to the data source.

    PersonalGateway_DataSourceKindLabel
    Data source errorMicrosoft Access: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. The 64-bit version of the Access Database Engine 2010 Access Database Engine OLEDB provider is required to connect to read this type of file. To download the client software, visit the following site: https://go.microsoft.com/fwlink/?LinkID=285987.
    Cluster URIWABI-US-NORTH-CENTRAL-redirect.analysis.windows.net
    Activity ID8921653f-0461-2c4c-8023-07eb28e5bfd9
    Request ID6c12b369-ed47-3457-7c71-9d7fe703edac
    Time2016-02-12 13:23:55Z

     

    The Access DB in question was created with a 64-Bit version of Office 2013 x64 on Windows 7 x64 and published with BI Desktop x64.  I've also tried this in the other direction and used all 32-Bit applications on the Windows 7 x64 and Windows 10 x64.  I understand that bitness matters in this case, so I've tried to be as consistent as possible when creating and publishing, but I continue to get this error.  I've seen some other posts that suggest installing the Office 2010 x64 Access components, but even that did not fix my problem.

     

    Any ideas why I continue to receive this error?