Switching from Report Builder to Power BI

[Tobias1203]

We want to switch from the very old MS Report Builder to Power BI. The report builder has data sources stored on the report server, which can then be used for a report. This means I don't have to use my own user account. Does something like this also work with Power BI? If I create the report and then leave the company, for example, the report will lack the credentials to continue accessing the SQL server.

How can I avoid that? Or do I have to create the report in Power BI Desktop with a service user who is independent of me?
This also brings me to my second question: How does a report update once it has been published? Are the data sources with the stored user then accessed again at night?
Thank you in advance for your answers 🙂

[atuljha · Accepted solution]

The data sources concept works the same between Report Server (any version - 2005 thru 2016 and beyond) and Power BI Report Server.

As a datasource is a connectionstring to your database/dataset, as long as the saved credentials have continued access to the database, it will continue to render the report/cache the data overnight, with no front end user account needed.

The question you asked is more so extreamly important from a security  perspective, and how your backend database is secured and how your data is secured.

Are

• Tables are open to all, as long as they have access to the database - i.e. you can use SQL/Domain account to refresh/cache data at night. (usual setup, check with DBAs if this is your setup)
• Tables are restricted to domain users - you cannot really used nightly refresh/subscriptions, but depending on the report setup, you may be able to work with this.
• Is there "data level" access requirements that need to be considered.

Note: This would be an ideal setup for report with data security in mind, however this mechanism is usually used for more complex/sensitive reports. i dont think this is your configuration. but you should keep it in mind.

And you answered your question yourself 🙂

How can I avoid that? Or do I have to create the report in Power BI Desktop with a service user who is independent of me?

Regarding second question:
How does a report update once it has been published? Are the data sources with the stored user then accessed again at night?
Once data sources are sorted out, you can setup data cache refresh frequency on under ReportName > Manage > Caching page.

Default setup is "run report with most recent data" but you can change it.

Hope this helps

[Tobias1203]

Hi Chris,

thanks for your answer. So i will used saved user credentials. But how does a Report update at night? Do I need to use DirectQuery or can I import data and do stuff with it in Power BI Desktop and it updates over night? Or do I need to use a Dataflow to my SQL-Server that I store in Power BI Online?

[ChrisMuthmann]

Hi Tobias,
you should use a technical user for accessing the database from the dashboard. For nighttime automatic updates, you must use saved user credentials. So it is a good idea not to use your own account with passwords that change now and then, but to use a technical account with stable password.