Star schema or using reference tables?

View previous topic View next topic Go down

Star schema or using reference tables?

Post  mbolder on Fri May 20, 2011 5:35 am

Iím trying to model a data model for a request report process.

Some information about a report that can be requested: a report can have different fields (max 30 fields) and different departments (max 20).

The DWH should at least answers the following questions:
- How often is each fields requested?
- What is the top 10 requested fields?
- How many reports are requested for each department?

Whatís the best way to model this? Should I create a pure star schema, with the lowest grain? (See model below). But this can create 30x20 = 600 records for one report! Isnít this a waste of storage?



Perhaps is smarter to use a reference table to limit the number of rows in the fact table? But then, the model looks more like a relational model



So my question basically is: is it okay to create a large fact table? Does this large fact table have a better performance? (Because it saves two joins, right?)

mbolder

Posts : 2
Join date : 2011-05-20

View user profile

Back to top Go down

Re: Star schema or using reference tables?

Post  mbolder on Tue May 24, 2011 1:51 am

After some reading this weekend, I decided to use the start schema instead of a reference (bridge?) table. I think this is a more flexible and performance model.

Trying to continue my model, I encountered some different issues. Because my fact table "fReport" is a fact less fact table, I cannot easily get the number of reports per department . I don't think you have this issue with "normal fact tables". Because the total sum for orderline is always the sum in orderheader. But this is different for fact less fact. (Because I'm adding 1 to amount for each field per report per department per reportdetails)

What I could do is add an (degenerated?) ReportNumber to the fact table. With a count distinct I could easily get the number of reports per department. Is this the best way to do this?
Or would it be better to add a second (new) fact table with a different grain? (reports per department per reportdetails)

Thanks in advance for your help

mbolder

Posts : 2
Join date : 2011-05-20

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