Affordable and user-friendly database solution

[redhughes]

Hi,

First of all we're a small business and without a proper IT department or IT background, We use PowerBI for reporting and have a few data sources - Podio (via Web data feed), Excel spreadsheet saved in OneDrive (not much data - 40 lines per week), Access database saved in OneDrive (more data - 1000 lines per week). Web data feed is great as we don't really have to run reports or update a spreadsheet. The other are not ideal for a number of reasons but mainly because I'm the only one that can really update the data (via personal gateway).

What we are looking for is a database solution that is affordable, easy to use (set up and update) and allowing multiple user access. I guess we need to find a cloud-based database, is that correct? Anything that the community could recommend based on similar experience/circumstances or just pure IT knowledge?

It would be great if anyone on my team could jsut copy and paste that week's data, or upload a csv file somewhere, which would then automatically refresh our PowerBI reports (or via a scheduled, daily refresh). I just want to avoid having to manually refresh PowerBI datasets.

Alternatively, maybe there's a different way of setting up our Access database? Maybe moving it to Sharepoint, and then somehow changing the Gateway settings so that anyone can update the database and then refresh the reports?

Thanks in advance!!

[parthurs]

@redhughes not sure if my first question came through.  I had to register to respond on this forum and I can't see my question.  Anyhow, would you mind sharing with me how you connect to Podio with Power BI?  Thank you in advance for your help!

[redhughes]

Hi @parthurs,

First of all you need GlobiFlow, then in GF you create a data feed as one of the app's flows (I usually set them up as XML) and select the fields you need. Then you copy the url (read and be aware of the warning underneath). Then in Power BI you choose 'Web' as the data source. Power BI will then connect to it, and you're good to go.

[v-jiascu-msft]

Hi @redhughes,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

[redhughes]

Apologies, haven't had much time for work over the last week.

I think we should be looking into cloud database solutions - they just seem much more appropriate and beneficial. Any user-friendly and affordable software that you could recommend?

Thanks a lot for your help.

[v-jiascu-msft]

Hi @redhughes,

That's a very complicated question. That depends on many factors. For example, 

1. If you happen to know a software well, that would be great. Such as the MySQL on Azure. It's easy to use and the price is reasonable too in my opinion.

2. Any chance the data will grow big. We have to consider the scalability of the database.

I would suggest you try the Azure platform. There are many databases. You can start with a free account. 

Best Regards,
Dale

[v-jiascu-msft]

Hi @redhughes,

Power BI datasets can be updated automatically by scheduled refresh. So a database isn't a must. Firstly, you need to keep the data sources update. Then Power BI will sync the updates according to the settings of the scheduled refresh.

What's your main concern?

Best Regards,
Dale

[redhughes]

Hi @v-jiascu-msft,

I understand a database isn't a must, but with large amounts of data (in my mind 1k lines per week with quite a few columns is large as it seriously slows Excel down) it's easier to manage.

My main concern at the moment is the fact that only I can refresh the PowerBI dataset with the Access database we're using. Like I said, I'm using a personal gateway which means it's specific to my machine, and the machine has to be on to run the refresh. Can this set up be improved so that my colleagues can update the database and then refresh the dataset themselves (or let the autorefresh do it at a specific time of day)? Is it a matter of changing the gateway settings or using a different gateway?

Thanks for your help,

Jan

[v-jiascu-msft]

Hi Jan,

1. The Gateway (personal) can be used by one user at the same time. That means it can be used by many users but only one account can be logged in at a time. If we switch accounts time to time, that would be a disaster. 

2. The scheduled refresh is still available for the Gateway (personal). So if you set up the scheduled refresh in the Service side, your colleagues only need to update the data sources such as the database, Excel files. 

3. The most important part is all the members of an App workspace can refresh the dataset by clicking "Refresh Now". So you set up the Gateway and your colleagues just use it. Please refer to the snapshot below.

Reference: https://docs.microsoft.com/en-us/power-bi/service-gateway-personal-mode#limitations-and-consideratio...

Best Regards,
Dale

[redhughes]

@v-jiascu-msftthanks for the reply. does it still mean that I (my laptop) has to be online when the other user is refreshing the data?

[v-jiascu-msft]

Hi @redhughes,

Yes, you are right. The Gateway is a bridge. You need to keep it open.

Best Regards,
Dale