Hi,
I hope you are all well.
I checked your requirement and putting down my implementation plan.
For User role settings – It is a 2-step process. After extracting, modelling and report creation, you will create role in PBI desktop. Apply that role to report and check data. If everything fine, publish that report to PBI service. Go to your data set -> security and provide email id of person or group against role. That person or group will see only those data filtered by the role.
Refresh hourly – if you want to refresh complete model/data, you will need a gateway for on-premises data sources. There are on premise data gateway is available to fulfill this need.
You can use on-premises data gateway or on-premises data gateway (personal mode) based on the type of data source and usage. The first one supports cloud services, muti-user access, direct query to SQL, Oracle, Teradata and Live connection to Analysis service.
Is it best to deploy on the same server as the database?
- Yes. The data refresh time will be less as there is no need to connect another server.
Is it easy to change the database?
- No. Your report and dashboard visuals are completely tied with dataset (in import mode), the dataset holds connection properties like- server name and database name. If you change db name, the credential on that dataset will fail and the scheduled refresh won’t work.
Let me know if you have further on my email id.