Guide lines for creating a Cube structure

Use a Real actual data not a sample data in development time, so if there any error that can be happened due to the quality of data like (logical keys, and computed columns that may generate null values,..) will be caught very quickly in the design time.

1. Be sure that all foreign keys has values on primary tables, in case of different sources, create name Query and add any missing data from the parent as unknown values using union, or make an outer join and use the isnull() functions to replace the null values with unknown values or default missing values
2. Use Regular relation (First Option) between the Dimension and the Fact, if the relation in the database is 1 to many (Dimension is one and fact is many)
3. Create different Hierarchically in the Dimension, so you can browse the dimension with different break down.
4. Do not change the source data structure, try to make all change in the Analysis server.
5. Use Fact relation (Second Option) if the Fact and Dimension are the same table
6. User Many-to many (Third Option)
   
   
   
   relation if you have a Fact want to make a relation with Dimension via another Fact that represented on a Measure group
   
   
   
   
   
   
   
   
7. Use Reference relation
   
   
   
   (Last tricky Option), if you want to link a Fact with Dimensions, that has indirect relation with the Fact dimension, this scenario will happen if you have multiple measure groups but if you have only one Dimension group, you can create a hierarchy in the dimension (in the development time, please untick materialized, with big data)
   
8. Always make the Time dimension in the Server not in source database.
9. Avoid big dimensions, do not try to construct dimension based on transaction tables, and if you did, do it in the group and summary level, not in the transaction level.