Showing results for 
Search instead for 
Did you mean: 
964ABC Occasional Visitor
Occasional Visitor

Hundreds of dataflows for different clients (multiple questions!)

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,...).

Helpful resources

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Top Kudoed Authors
Users Online
Currently online: 6 members 1,685 guests
Please welcome our newest community members: