Hello,
I am creating a model that mainly manage a huge production process, based on 50 indicators. Each indicator has 6 parameters and these parameters are calculated diferently for each indicator. Based on these 6 parameters it is possible to generate a status for each indicator, such as: on time, risk of delay, late etc.
The data model used to calculate each of this parameters contemplates 23 different tables, and some of them has about 5 million records.
Each indicator belongs to an area and there are formulas that, based on each indicator’ status, set a status to its area. For example, if the status of 20% or more indicators from an area are late, the status of this specific area is also late. In the same logic, there are calculated status for each business, based on its areas status.
The last level of analysis shows each business related to an period of time and its status, which results 22 different status, and the results of these last two sections (status per area and business) are taking too much time to be calculated. I assume that the main reason is that we are calculating everything inside the objects and, considering that, formulas depend on each other, the higher levels analysis need a lot of calculation to generate a status.
The first solution is to calculate each indicator's parameters in script and then generate one table with all the results and use it to calculate the views that depend on this results. This way, there is no necessity of relating all the data every time there is a need to calculate something, and it also avoids big formulas into objects. In the other hand, this might take a lot of time.
Does someone has a better solution?
Thank you in advance!
