03-08-2017 09:05 AM
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?
Solved! Go to Solution.
03-09-2017 11:51 AM - edited 03-09-2017 11:51 AM
@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.
03-09-2017 07:54 PM
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?
03-09-2017 08:49 PM
@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!
03-09-2017 10:59 PM
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.
03-10-2017 06:39 PM
Apologies, perhaps I used the incorrect terminology. I have the access database stored in our sharepoint site as a document.
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.
03-12-2017 07:00 PM