Hello,
I've started working with Qlik Sense SaaS a few months back, mostly designing apps. We hired a company that did most of the job concerning data ETL and modeling, but I'm starting to study more about the subject. Maybe someone could clarify some doubts I have?
The first one regards whether the unit price should be stored as a dimension in the product dimension table or in the fact table. By unit price I mean the "default price", not the value the product was actually sold and is tied to a purchase ticket. For instance, I have a product ABC that was registered in our system for $100, but it was actually sold for $80 because it was on sale in a certain day. Considering we calculate the markdown of a product, shouldn't it be placed in the product dimension table so we could compare it?
Beyond that, if we changed the "default price" for that product, how could I keep a record of the different prices ever set, linked to the date it was changed? I've read some stuff about "slowing changing dimensions", would that be the best practice?
My second questioning is about the history of the stock count. Currently we "print" the counting by the end of each month, that means we don't have a daily update on the amount of a certain product (we use our ERP for that). How could I fix this? Is it the same case for the product price? Or in this case it's a fact table considering it changes on a daily basis? If it's a fact table, will it be in the same fact table as the transaction history (sales, etc.)?
If anyone could guide me through, I'd be very thankful.
Pedro.
