Hi all!
I am currently working on a project and am struggling with multiple issues. Could you help me?
Situation: we manage around 500 databases that contain data about ERP systems of clients. Each database/ERP system is related to one client of ours. The databases are spread over multiple on-premise SQL Servers.
Goal: to share dashboards and reports with these clients. Also, these dashboards and reports should be refreshed once or twice a day, in order for our clients to have access to up-to-date visualizations.
Thoughts: since it is not necessary to have real-time dashboards, I consider creating a streaming dataset as out of scope. I already installed a gateway to be able to get data from our on-premise SQL Servers.
Questions: - What is, in terms of storage and duplicating data, the difference between a dataflow and a dataset?
- Since scheduled refresh is possible in both cases: if ETL is performed before importing the data into Power BI, is there still a reason to create a dataflow? Or is importing a dataset sufficient?
- Is it secure to have all 500 client datasets/data flows in one app workspace? Or should there be 500 workspaces to maximize security?
- Which Azure services are necessary/beneficial to create this? I am considering Analysis Services, Datawarehouse, Event Hubs and Stream Analytics.
- Would it be better to merge all 500 databases into one data warehouse and then load all dataflows from this warehouse OR to keep all databases separate?
- How much of this can be automated/standardized? Because all 500 cases will go through the same process (ETL, creating dataflows, importing datasets, visualizations, sharing,...).
