Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a software product that creates a new database for each of my accounts. I have measures in each database called "Water Flows" that contains a value for each day going back years. Id like the SUM of all measures somehow / somwhere. Can someone point me in the right direction?
It's all about the data model. Kind of depends on your raw data starting point. I can make some guesses in having made some databases involving water utilities.
You'll want to append the differing files into 1 table with at least a Customer ID and Water flow value.
In my experience the water flow value needed to subtract the prior value in order to calculate the amount - which means before you append to a master table you want to first add a Customer Index value to order those rows....so you'll probably have a temp table to stage this, add the index, then append to the master table. If there is a measurement date field however then this step isn't needed. As long as you can find the preceeding row/record in order to calculate.
Whether this is a one time analysis or if you are setting up to refresh for incrementally new info will probably have an impact on how to approach it as well.....depending on whether the new information is all of the past too or just new rows....