Our one CSV file contains about 200 fields. (5 Primary keys)
Now I am trying to design the cube layout. First I put all the Key Figures in the Fact Table. Here are my questions:
I designed the first Dimension called Orders. I put all these 5 primary keys into this dimension. Is this correct? I felt the rest of fields are all related to these primary keys. How can I split these 200 fields into multi-dimensions? I am really confused about how data get loaded to cube. Does each dimension have one to one relation with Fact table? Someone sugguested me to split these five primary keys into three dimensions which I dont really understand.
Because Order Company is not unique anymore by itself. Is this okay? Same to Order Number, line Number, Order Type and Order Suffix. In this case, what should I do? Maybe I misunderstood the concept. If I put those in one Order Dimension, it gets very large now.
For example, here are some of the fields from source file:
Original Order Document Company
Original Order Number
Original Order Type
Original Line Number
Related Order Key Company
Related Order Number
Related Order Type
Related Line Number
Promised Delivery Date
Original Promised Date
It seems like I have to put those into one dimension. I did read "Multi-Dimensional Modeling with BI", but still get confused when I am doing it. Can anyone explain me more?