Forum Discussion
Data Modeling - Monthly Data from Multiple Cloud Instances
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.
Community ChampionHi 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.