I'm rather new at DW design and am working on a DW to model some IT infrastructure.
The major problem/question at this point is how to model drive information.
We will be collecting aggregate data on files and folders, and separate data on physical drives. Drive info will include total and free space at a minimum and will be updated several times a week.
One of the business questions that will need to be answered is how drive usage is trending over time. The drive information will also be used in a hierarchy leading down to the file/folder level as well.
The options that I can see now are:
DRIVEas a Dimension
- Simplifies hierarchy design
- Will this cause issues with reporting? It seems counter-intuitive to me to report time-bound data on a dimension only
- It also seems problematic to have a dimension that you KNOW will change every time you refresh your data
DRIVEas a Fact Table
- Simplifies reporting
- Complicates hierarchy(?) - I'll be using
Driveto map data back to a specific server or computer as well. Is it OK to use a fact table as an intermediate level in a hierarchy? I don't think it is.
DRIVEas both a Fact and Dimension
- Fact will contain just the key, date, and facts on space
- Dimension will include other non-additive data like what computer it's on, etc.
- Seems to resolve both issues, but is this an anti-pattern?