Dimension or factless fact

View previous topic View next topic Go down

Dimension or factless fact

Post  robo on Sun Feb 07, 2010 6:01 am

Hi,
I am new at the business of DW archirecture.
Our goal is to design a data mart that deals with the document processsing in our organization.
We have about 5,000,000 documents. Each document has a header table (with fields like document type, status, creation date, author etc) and document body depending on the document type (each document type has its own body table). Most of the fields in header and body tables are code values but some caontain text (subject, short description etc).
There are a few measurements for each document - average processing time, number of attachments, number of employees listed in the document etc.
The granularity needed is the document level (i.e. document id).
There are several questions we need to answer using this data mart - average document processing time per author, number of documents created by each author (by status and type), number of employees listed in a specific document an more.
Some other data marts need to use a documents dimenstion - the employees data mart for example - how many documents created by the employee, how many documents refer to the employee and more.
I am not sure if documents needs to be a dimension or a fact table and if a fact table, then do I include all of the description codes (keys) from the header and bodies?
Or should I create a fact with docid and measures linking to a documents dimension (this will mean a join of two 5,000,000 tabels)?
Thanks for your help.

robo

Posts : 1
Join date : 2010-02-07

View user profile

Back to top Go down

Re: Dimension or factless fact

Post  Jeff Smith on Wed Feb 17, 2010 3:43 pm

It's probably a series of dimension tables and a fact table pulling it all together. The Document ID is probably a Degenerate Dimension. You probably need a bridge table that links the Employee to the Document, unless the Document has other information that is specific to the Employee. If that's the case, then the base Fact table needs to include the Employee and the grain, I think, would be at the Document/Employee.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum