I have 1 fact and 3 dimension say dim1,dim2,dim3 tables. Fact and dim1 table are connected via key1 and having 4 fields repeating with same values between fact and dim1 table say field1,field2,field3,field4. Shall I remove those from fact table and keep in dimension only.
Yes I think you only should keep fields in master tables, not in fact tables. In fact tables only key fields and fact fields such as sales, revenue, margin, etc.
could you kindly let me know the best way to create my data model.
If I need to keep it as a STAR Schema, I need to keep TerritoryNo,UnionNo in Fact Table or put everything in Customer table and make it as a Snow Flake. But STAR Schema is the best approach for any data set (mine with some 50,000 rows) in a Excel file. Kindly let me know.
In that case, in Table(Fact) I would remove CustName as it is in Cust Table, and in Cust Table I would remove TerritoryNo and UnionNo fields, for having a star model:
Table(Fact) : CustNo,TerritoryNo,UnionNo,Amount
Cust Table : CustNo,CustName,Addr,Pin
TerritoryTable : TerritoryNo,TerritoryName
UnionTable: UnionNo,UnionName
Anyway if you only have those fields and 50K rows you could also try an only one table model with the join of all fields.
I attach you a Qlik document about these topics...
