Home Forums Database Design Design Ideas and Questions Building my first data warehouse. Advice welcomed :) RE: Building my first data warehouse. Advice welcomed :)

  • March 18, 2013 at 9:12 pm

    #1598274

    alan.hollis 1097 (1/14/2013)

    Hi all,

    I've been brought on board to help a company start to build their first business intelligence setup. I'm a developer by trade, and have good knowledge of SQL and application design using relative tables but this is my first step into designing a data warehouse.

    I have two questions initially from the customer, and if I can do a good job of providing a report setup for this we'll expand from there. The questions I'm tasked to answer are:

    How many support tickets are being opened each day?

    How many support tickets have been closed each day?

    How many does each employee have?

    How many does each group of employees have?

    I've designed my first start schema to handle the answering of these questions which is as follows.

    I would love any feedback/critique of this design.

    Thanks

    Alan

    p.s As a side note I'm planning on using SISS to perform the ELT tasks rather than programming my own software to do the job? Does this seem like a sensible opinion? For the first part of the data entry I'll be pulling information out of Dynamics CRM and Request Tracker.

    As the others have mentioned, you'll need a "Group" dimension to identify how many tickets a group has.

    As for your first two questions, you'll need to identify the status as a date. That's the only way you'll be able to firgure out those two. You might even want to create a separate fact table to store the different statuses and dates of those statuses that any give ticket goes through. Don't forget that the status of a ticket may regress.

    --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)