I hope you are well. I have this problem and I am a beginner in data warehousing. I hope to find a solution to it.
I have an OLTP database from the GLPI system. We are only interested in ticket management (a ticket is an issue raised by an employee in the form of a ticket). Each ticket can have several statuses: new, in progress, pending, planned, resolved, closed. In the OLTP database (data source), each time the status of a ticket is modified, the 'status' attribute is also updated, along with the modification date. Additionally, we have ticket closing and resolution dates.
Our job is to create a dashboard to track and visualize the number of tickets for each period, as well as the statuses of each ticket during each period.
The ETL runs every day. Our problem is how to capture these ticket transactions and display them. The supervisor suggested the idea of creating a fact table for each status, but we found that this is not logical.
How I can keep the history and display the statuses of each ticket by date for example the month passed how many tickets were closed?