I have an alert table which contain alerts received by iot devices every day. A Device can be related to one or many PAPE. I have an association table between devices and PAPE (the association table is historized with startDate and endDate) I have 40000 devices and 80000 association on the association table. Device and PAPE table are related also related to geography table. The device table have a status column (running, stopped...) which is not historized (every time the device change the status the row is updated) The alert table also have a status column (opened, closed,..) and is not a slowly changing dimension type 2.
I want to calculate kpi like:
- The number of oppened alert by device / PAPE / geography at the day level
- The number of running device by PAPE / geography at the day level
If I stock these kpi on an aggregated table at the PAPE level I will have 80000 rows every day.
What is the best BI model to analyse that ?
