Identifying the fact table in data warehouse design

Question

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.

Answer 1

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.

Answer 2

In general, the way I've done it (and there are a number of ways to do anything) is that the categorical data is referenced with a FKey in the fact table, but anything you want to perform aggregations on (typically as data types $/integers/doubles etc) can be in the fact table as well. So for example, a fact table might contain a hierarchy of types, such as product_category >> product_name, and it usually contains a time and/or location field as well; all of which would be referenced by a FKEY to a lookup table. The measure columns are usually integer based or money data, and are used in aggregate functions grouped by the other fields like this:

    select sum(measureOne) as sum, product_category from facttable
where timeCol between X and Y group by product_category...etc

At one time a few years ago, I did have a fact table that had no measure column... because the only measure I had was based on count, which I would do dynamically by grouping different dimensions in the fact table.