Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Star schema or using reference tables?

Go down

Star schema or using reference tables? Empty Star schema or using reference tables?

Post  mbolder 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?

Star schema or using reference tables? Image111

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

Star schema or using reference tables? Image211

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

Back to top Go down

Star schema or using reference tables? Empty Re: Star schema or using reference tables?

Post  mbolder 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

Back to top Go down

Back to top

- Similar topics

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