Forum Discussion
Data Modeling - Monthly Data from Multiple Cloud Instances
Hello! I am diving into the world of PowerBI and looking for some advice from you expert users.
My company offers a SaaS product to other companies via segmented, private clouds. For example, we have 10 different private cloud deployments in Azure for 10 different customers. We are looking to monitor monthly usage across all of these deployments. We get monthly CSV data reports from each cloud deployment. These reports generally follow the same format, but some columns may be added over time as we add more statistics to our monitoring framework.
We are looking to use PowerBI to generate insights on both an aggregate level (e.g. what are ALL our customers using across every instance month over month?) and on an individual customer level (e.g. what is this singular customer using each month?).
So, my question is - how do I best formulate a data model in PowerBI? Given the following:
- Monthly reports (12 reports per year)
- Each Cloud instance sends a report separately (10 instances)
My idea was the following:
- Automatically combine all months together into one data table per instance
- This would create 10 different tables containing all available monthly data for the instance
- Create a separate table that maps customers to instances
- This would allow me to filter visualizations on a single customer
- I do not know, however, how to do an aggregate view. Do I need to combine all 10 tables into a single, giant table in order to do aggregate analysis?
As always, your thoughts and opinions are greatly appreciated. Hopefully this made sense - please let me know if you have any questions. Thank you very much in advanced!
Hi Anonymous ,
I see no reason why to create one table per instance. Append them all together with one column for the customerID. Then add a date and customer table.As a rule of thumb for Power BI you can assume that if tables have the same column names, they should be merged into one table.
3 Replies
- ImkeF
Community Champion
Hi Anonymous ,
I see no reason why to create one table per instance. Append them all together with one column for the customerID. Then add a date and customer table.As a rule of thumb for Power BI you can assume that if tables have the same column names, they should be merged into one table.
Hi ImkeF
Thank you very much for your response! I understand, it will be best to have one table for all instances for all months. That means the theoretical processing steps are as follows:
- Data is collected monthly for each cloud instance individually
- Upload all CSV files to the same location and map that to PowerBI
- Add a date column to each table for a record of the month
- Merge all tables together into a single, large table
- Manually create a separate table for customer mapping
Does this sound appropriate to you?
A couple follow-up questions:
- Are there any concerns over scalability? For example, we are hoping to get to 150+ customer instances. When this happens, will PowerBI be able to handle that volume of data in a single table?
- What would happen if we were to add new columns for new metrics? For example, if we start collecting a new metric in November 2022, a new column would be added to the CSVs. For the previous months, I would assume this new data column would just be blank. Would this cause any issues?
Again, many thanks for your help - I appreciate your expertise as I get started.
- ImkeF
Hi Anonymous
looking good to me.
1) No worries about scalability of single table vs. separate tables. Just the opposite: Due to PBIs compression mechanism, having it in one table will use less space.
2) You must handle these eventualities in your measures: Filter out blank rows where you want to ignore rows without entries in certain columns.