Dimensional Modeling Question (FACT vs DIM Data) 1

[CanadianTechie]

As a relative newcomer to the paradigm of "Dimensional Modeling", I am wresting with some fundamental design decisions in my evolving Data Warehouse/Data Mart environment. Case in Point, consider Order Line Detail information -- in keeping with the "paradigm" and trying to restrict my fact table to only contain numeric (measures), my (simplified) ORDERLINE_FACT table is:

ORDERLINE_FACT
OrderNumber
LineNumber
QtyOrdered
QtyPlanned
QtyShipped
QtyOpen

HOWEVER, I have a number of characteristics about this Order Line entity that I wish to capture, and right now they are depicted in a separate table as follows:

ORDERLINE_DIM
OrderNumber
LineNumber
RequestedDate
LastShipDate
ItemCategory

NOW, as a "seasoned" Database professional, and understanding the type of reporting that these structures will be put through "down the line", I would much rather see these "characteristics" included in the FACT table mentioned above....from a performance point of view, we limit the joins and I have no problem increasing the record size to accomodate the additional elements -- but from a KIMBALL-Dimensional Modeling point of view, am I breaking all the rules???

BTW, I *have* ordered Kimball's 2nd edition Dimensional Modeling "bible" but have yet to receive it -- it will likely set me straight, but until then, feedback is welcome!

Thanks in advance,

CanadianTechie

 

[kishleo]

well, no need to fallow kimball or any one. the Warehousing principles depends on the particular data warehouse, just you have to take the kimball outline in building the Data warehouse. the data warehouse now i am creating is a dimension less, yes there is no dimensions at all in my data warehouse, only the fact tables, for the quick and faster access, i dont need to join any tables here.... ô¿ô Kishore

 

[Prasad678]

CanadianTechie,
I don't know whether the table structure u have put above is for an example or the real one that u r facing.

If thats real, u don't need a seperate dimension table as ur fact table is associated only with one dimension. If u have some 5 or 6 dimensions getting involved its adviced to go for a dimension model like a star schema.

Prasad
RPrasad1@Chn.Cognizant.com

 

[SteveCSC]

If you have all your dimensional information on the fact table, you must then consider how you handle slowly changing dimensions (see Kimball if you don't know this concept). You will have to scan thru all your history on the fact table to do any updates of dimensional information that is either incorrect and must be fixed, or has changed and you want to also change history.

 

[Sriramps]

Hi,

It is not really necessary to create a separate fact table. Some of these can be accommodated in the fact table as 'DEGENERATE DIMENSION'. (They will appear as columns in the fact table without a corresponding Dimensional Key; REfer to Kimball for more on that)So you are not really violating any prinicples - Kimball or otherwise. Of the attributes you have listed, the following can be degenrate dimensions

OrderNumber
LineNumber
For The rest
RequestedDate
LastShipDate
ItemCategory
my suggestion is For the date fields, use the Time Dimension (Two FK references)
For Category - Create a dimension called category and use its dimensional key; You will never know - you may have to slice by category at a later stage; Alternatively, category can be part of the rollup of Item; This will help in aggregation

Good luck

 

[CanadianTechie]

Thanks to everyone for their input to my "newbie" question. I actually received my Kimball reference within days of that post, and after going through the first few chapters, things are much clearer now. Doubly clear is that there is no "single" way to "correctly" model your data warehouse -- it really is highly dependent on the needs and expectations of your user communitity, both from a data granulatity level and content/organization.


Sriramps -- thanks for your insight sir -- I actually know what you're talking about now {Grin!}

Regards,

Canadian Techie



Pedro Cardoso
A.K.A CanadianTechie