I have a use case (below) for which I am considering multiple options. I am hoping to get some ideas, from the experts here that can help me get a better design. I am coming back to data warehousing after almost a decade, and I am more than rusty, so please excuse me if it comes across as a very trivial question.
My environment is SQL 2016. Enterprise Edition.
I am going to develop a report showing trends of plant work order history, and how they trend over the past 6 to 24 months, for example. A work order may stay open for over 6 months, and may receive some updates to its target completion date, requested finish date, status, and responsible person.
- Users are able to see a trend of KPIs
- Work Order Counts (status is not closed)
- Other KPIs (Overdue Inspections)
- Others. Mainly, depending upon status number, and the difference in different completion dates compared to today yields a different KPIs.
- Users able to drill down from work order count for a day/week/month to see what makes up the Count. For example, on 26 Feb 2020, there were 20 Open Work orders. They should be able to drill down and see what work orders (work order numbers) made up that 20, what was the status, requested finish, completion dates and who was working on it.
- 26 Feb 2020 is the date when ETL job ran and loaded the data from source system.
- Implement SCD type 2 on all dimensions (work order, equipment etc.) marking a record active and other inactive.
- How would I enable drill down on historcal work data?
- I am looking at using Merge statement, and have read some blogs where they caution about its performance impact and that it may not work very well with columnstore.
- Just simply keep populating the dimension and subequent fact. Both grow over time and after 2 years, just archive/purge histrorical data.