CODEWITHSUNDEEP
data-warehouse
user786423
user786423

Reputation: 125

Data Warehouse Design of Fact Tables

I'm pretty new to data warehouse design and am struggling with how to design the fact table given very similar, but somewhat different metrics. Lets say you were evaluating the below metrics, how would you break up the fact tables (in this case company is a subset of client). Would you be able to use one table for all of this or would each metric being measured warrant its own fact table or would each part of the metric being measured be its own column in one fact table?

Upvotes: 1

Views: 312

Answers (1)

Joe
Joe

Reputation: 1357

By the looks of the measure names, I think you'll be served with a single fact table with a record for each file and a link back to a date_dim

create table date_dim (
    date_sk        int,
    calendar_date  date,
    month_ordinal  int,
    month_name     nvarchar,
    Year           int,
..etc you've got your own one of these ..
)
create table fact_file_measures (
    date_sk,
    file_sk,           --ref the file_dim with additonal file info
    company_sk,        --ref the company_dim with the client details
    processed  int,    --should always be one, optional depending on how your reporting team like to work
    size_Kb    decimal -- specifiy a size measurement, ambiguity is bad
    error_count int    -- 1 if file had error, 0 if fine
    failed_count int   -- 1 or file failed, 0 if fine
)

so now you should be able to construct queries to get everything you asked for

for example, for your monthly stats:

select 
    c.company_name,
    c.client_name,
    sum(f.file_count) total_files,
    sum(f.size_Kb)    total_files_size_Kb,
    sum(f.file_count) total_files,
    sum(f.file_count) total_files
from
    fact_file_measure f
    inner join dim_company c on f.company_sk = c.company_sk
    inner join dim_date d on f.date_sk = d.date_sk
where
    d.month = 'January' and d.year = "1984"

If you needed to have the side by side 'day/month/year' stuff, you can construct year and month fact tables to do the roll ups and join back via dim_date's month/year fields. (You could include month and year fields in the daily fact table, but these values may end up being miss-used by less experienced report builders) It all goes back to what your users actually want - design your fact tables to their requirements and don't be afraid to have separate fact tables - data warehouse is not about normalization, its about presenting the data in a way that it can be used.

Good luck

Upvotes: 2

Related Questions