Post reply

Design ideas for historic data & trending

  • March 27, 2020 at 5:15 pm

    #3738363

    Hi everyone,

    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.

    Use Case

    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.

    Requirements

    1. Users are able to see a trend of KPIs

      1. Work Order Counts (status is not closed)
      2. Other KPIs (Overdue Inspections)
      3. Others. Mainly, depending upon status number, and the difference in different completion dates compared to today yields a different KPIs.

    2. 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.

      1. 26 Feb 2020 is the date when ETL job ran and loaded the data from source system.

    Options

    1. Implement SCD type 2 on all dimensions (work order, equipment etc.) marking a record active and other inactive.

      1. How would I enable drill down on historcal work data?
      2. 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.

    2. Just simply keep populating the dimension and subequent fact. Both grow over time and after 2 years, just archive/purge histrorical data.

    Thanks,

    Kaz

  • March 28, 2020 at 2:41 pm

    #3738628

    I'm thinking that Type 6 SCD's may be better.  With that, the use of Temporal Tables may be just what the doctor ordered here with the understanding that they won't auto-magically log the ORIGINAL_LOGIN() to track who made changes (a serious fault with Temporal Tables, IMHO).  Using Temporal Tables as a model for a homegrown solution can work out very well.

    Because of all the problems they had when MERGE first came out and some ongoing problems that they say is fixed now, I'm loath to use it.  The way they've implemented it, it doesn't seem to have any advantages over a good ol' fashioned "UPSERT" (conditional Insert/Update) other than possibly being a bit shorter to write.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply