A current recurring task we have is that we get nightly sales reports via FTP which are loaded in to SQLServer. This data is then manipulated through SQL views and exported into Excel where graphs, charts and tables are created within the spreadsheet and copies saved out and emailed to management to view the reports.
This is proving problematic and I'm hoping to replace the Excel part with PowerBI so that:
Essentially I want to configure each report in PowerBI and then have it run so that the report gets regenerated every morning when new data is available..
I know PowerBI has the visualisation capabilities and know that it can import from SQL, but will it fulfill the use case described above?
So, you have a couple of options here. One, you could create an Excel file and use Power Query to pull the data from SQL Server and create your reports. Then, load that Excel file in OneDrive for Business and Connect it to Power BI. Power BI Service, Get Data, OneDrive for Business and use the Connect versus Import. You can set a Daily refresh and just point your people to the OneDrive file and it will be up-to-date every day.
Alternatively, you could use Power BI Desktop, create your reports there, publish to Power BI Service and set the refresh schedule. You basically eliminate Excel and replace it with Desktop.
To my knowledge, you cannot email a copy of the report. That is kind of old school but I get that a lot of people are resistant to change. You could send an email with a link every day...
Did I answer your question? Mark my post as a solution!
@cosmos To just extend on what @Greg_Deckler suggests, I would say that PowerBI makes this process easier. Use PowerBI exclusively, connect to SQL, create reports, share.
Extending this to the Service you can create all these reports, schedule a refresh (or not if you use direct query to the SQL database) and share the dashboards. Then you don't need to email anything, and the end users can see/access the reports any time they want to.
As others noted here, they have very good suggestions. I would like to add that since your reports are imported into SQL and then manipulated in SQL Views, then all you really need is configure PowerBI to connect to the SQL View then you're set. With an assumption that FTP > SQL is automated.
PowerBI will eventually have an option in upcoming releases for email notifications that will help with notifying your users. And depending on how you design the 'report' in PowerBI, your users have the ability to export to Excel if they need to.
Thanks for all the replues, lots of really useful information there. Unfortunately, emailing copies of the report (as attachements for disconected viewing etc) is a non-negiotiable requirement. I was hoping to do that as a starter just to tick the box but then wow the recipients with the rich, interactive visualisations of the power bi reporting capabilities, but if that can't be achieved then unfortunately its a non starter
Thanks for the info. 4-6 week wait would be fine, but what functionality will that be? Will it be to export the Dashboard as an Excel file as we have some senior users who unequivocally will not accept anything other than the report attached and opening in excel (I know, I know!).
not sure about Flow - as much as I like the idea, its just another piece of the puzzle that could go wrong (plus I searched for PowerBI on there and it came back with nothing!
The business requirement is that we can't lose any existing functionality and thats how its currently delivered and the recipients actually really like that functionality. Regardless of whether it makes sense, its the way it is.