Search code examples
data-warehouseolapbusiness-intelligencedatamart

Identifying the fact table in data warehouse design

I'm trying to design my first data mart with a star schema from an Excel Sheet containing informations about a Help Desk Service calls, this sheet contains 33 fields including different informations and I can't identify the fact table because I want to do the reporting later based on different KPI's. I want to know how to identify the fact table measures easily and I have another question which is : Can a fact table contain only foreign keys of dimensions and no measures? Thanks in advance guys and sorry for my bad English.

Solution

    1. You can have more than one fact table.

    2. A fact table represents an event or process that you want to analyze.

    3. The structure of the fact tables depend on the process or event that you are trying to analyze.

    4. You need to tell us the events or processes that you want to analyze before we can help you further.

    Can a fact table contain only foreign keys of dimensions and no measures?

    Yes. This is called a factless fact table.

    Let's say you want to do a basic analysis of calls:

    Your full table might look like this

    CALL_ID
START_DATE
DURATION
AGENT_NAME
AGENT_TENURE    (how long worked for company)
CUSTOMER_NAME
CUSTOMER_TENURE (how long a customer)
PRODUCT_NAME    (the product the customer is calling about)
RESOLVED

    You would turn this into a fact table like this:

    CALL_ID
START_DATE_KEY
AGENT_KEY
CUSTOMER_KEY
PRODUCT_KEY
DURATION       (measure)
RESOLVED       (quasi-measure)

    And you would have a DATE dimension table, AGENT dimension table, CUSTOMER dimension table and PRODUCT dimension table.

    Agile Data Warehouse Design is a good book, as are the ones by Kimball.