I have a table that has all my Materials / Products information in it such Material number and name which are perfect for lookup tables. However it also contains some info which i will use to make some calculations such as goods received lead time and shipping lead times so how would you model this? would you place it on the top of model as a 1 to many lookup or place it on the bottom as fact table???
or do i split it in the query editor into relevant sections? else i would duplicate some fields which isnt nice optimizing.
ideally you would denormalize the data from the lookup table into the fact table, like goods received time. Then you will have a true Dimension table. I know people get worried about the amount of columns and rows in a fact table, but the more important thing to be concerned with is the cardinality of those columns ( # of unique values) And I’m guessing there are not a lot of unique values.
There’s one unique column the material master number that’s the key. There are a number of columns in there that I have no intention of doing any kind of calculations with but it does contain some data I’d like to place in cards such as max stock level set for that product in SAP and I’d like to leave that in the lookup side is that acceptable as I read a lot about this kinda thing. Second to that if like to create a second copy of that table remove all the card info and keep the calculation stuff and place as a fact table at the bottom and link it to the original of itself via the MM number… Please I don’t know why but it just doesn’t seem right to me the same table as a fact and look up that are linked what do u think?
I see no problem with that since it’s not a calculation. It may be a number, but it’s still a dimension field.
Without seeing an example, it could possibly work. But, not sure if that new fact table would relate to other fact table? If so, then definitely dont do it. relating fact tables can produce some interesting results. Results that at first glace would look correct but in reality not so much.
Possible to load a small sample data set to show what you are thinking here?
