Reply
Highlighted
Frequent Visitor
Posts: 4
Registered: ‎03-08-2017
Accepted Solution

Best Way to Design PowerBI Reports with Access Database stored in Sharepoint Online

Hello community,

 

Our small company primarily uses an access database to manage our business, to which I have hosted in our sharepoint online site.

 

I'm looking to use PowerBI Desktop to design a multitude of reports, and then upload them to the PowerBI Service for our internal users to use. I need to it be refreshed on a schedule.

 

Does anyone have any advice as to how I should set this up?

 

Thanks!


Accepted Solutions
Super Contributor
Posts: 2,091
Registered: ‎06-25-2015

Re: Best Way to Design PowerBI Reports with Access Database stored in Sharepoint Online

@swan1099This quick tutorial might help

Near SE WI? Join our PUG MSBIWI

View solution in original post


All Replies
Super Contributor
Posts: 2,091
Registered: ‎06-25-2015

Re: Best Way to Design PowerBI Reports with Access Database stored in Sharepoint Online

[ Edited ]

@swan1099 You'll need to install the on-premises data gateway. I would create all your reports in Power BI Desktop first, this will provide you flexibility in so many ways. Create and use a Group Workspace to publish the reports to, this will allow multiple users to be able to manage the reports instead of just one person.

When you deploy the Desktop reports, a dataset will be created for each set of reports you publish. The dataset can be scheduled to refresh up to 8x per day, this is set up and done in the Service.

There was a new way to easily embed into SharePoint Online, but it does require certain version etc., but it's really straightforward. Otherwise you would need to do some custom DEV to embed.

Near SE WI? Join our PUG MSBIWI
Frequent Visitor
Posts: 4
Registered: ‎03-08-2017

Re: Best Way to Design PowerBI Reports with Access Database stored in Sharepoint Online

Hello @Eno1978,

 

Thank you for your comments, I do appreciate the advice. I've downloaded & installed the gateway, and for the life of me, cannot get the gateway to add our sharepoint site as a data source. 

 

Any thoughts on what may be holding me up? It looks like I'm being hung up in the username/password area, but I've tried my o365 credentials as well as my ActiveD credentials...it won't accept either?

 

Thanks!Capture.PNG

Super Contributor
Posts: 2,091
Registered: ‎06-25-2015

Re: Best Way to Design PowerBI Reports with Access Database stored in Sharepoint Online

@swan1099This quick tutorial might help

Near SE WI? Join our PUG MSBIWI
Frequent Visitor
Posts: 4
Registered: ‎03-08-2017

Re: Best Way to Design PowerBI Reports with Access Database stored in Sharepoint Online

@Eno1978 This appears to be exactly what I was looking for -- I can do some trial and error I suppose, but will this method of connecting to the data not require a data gateway? That would be ideal!

Moderator
Posts: 1,923
Registered: ‎03-10-2016

Re: Best Way to Design PowerBI Reports with Access Database stored in Sharepoint Online

Hi @swan1099,

How do you host Access database in SharePoint Online? According to my knowledge, all of Access tables become SharePoint lists, and records become list items.

This way, you can connect to SharePoint Online list from Power BI Desktop, create reports there and publish them to Power BI Service, it doesn't require gateway when you refresh the dataset in Power BI Service.

Thanks,
Lydia Zhang

Frequent Visitor
Posts: 4
Registered: ‎03-08-2017

Re: Best Way to Design PowerBI Reports with Access Database stored in Sharepoint Online

Apologies, perhaps I used the incorrect terminology. I have the access database stored in our sharepoint site as a document.

 

@v-yuezhe-msft

 

I've followed @Eno1978's advice, and it worked as far as connecting to the sharepoint data and the ability to refresh without the data gateway.

 

I've scheduled a refresh, but now I'm recieiving this error upon scheduled and forced refreshes:

Microsoft 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 may be required to read this type of file. To download the client software, visit the following site: https://go.microsoft.com/fwlink/?LinkID=285987.

 

I can't seem to understand though why I am getting this, as I have 32 bit office, Powerbi Desktop, & Microsoft Access Database Engine 2010 32 bit installed.

 

Any thoughts?

Moderator
Posts: 1,923
Registered: ‎03-10-2016

Re: Best Way to Design PowerBI Reports with Access Database stored in Sharepoint Online

Hi @swan1099,

Power BI Service can only leverage 64 bit drivers, you should have no issues refreshing 64 bit Access database in Power BI.  There is an similar thread for your reference.

Thanks,
Lydia Zhang