topic Questions About Data Model (Regarding Unit Price and Stock History) in Connectivity & Data Prep

Questions About Data Model (Regarding Unit Price and Stock History)

pedrohenriqueperna — Tue, 25 Apr 2023 10:13:05 GMT

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.

Re: Questions About Data Model (Regarding Unit Price and Stock History)

marcus_sommer — Wed, 26 Apr 2023 06:37:16 GMT

I would suggest to keep the mentioned parts in separate ETL chains and applications - of course with some overlapping. This means mainly:

a sales-analysis - which also includes the default unit price as measure-field within the facts and an associated stock table on a daily level (with just a few dimensions)
a price-analysis - with all price-changes over the time + all further price-relevant information like discounts for amounts/channels/customer, taxes, cost-parts and so on
stock-analysis - without any prints (unless the opening stock) else loading all movements of the stock (purchase, sales, cancellation, defects, ...)

Each data-model has enough complexity to use a specialized application-chain for it - and only for a few certain views it's sensible to share some extracted information between them.

Re: Questions About Data Model (Regarding Unit Price and Stock History)

pedrohenriqueperna — Tue, 02 May 2023 13:28:02 GMT

Hi, Marcus!!

Sorry for the delay.

Indeed it makes sense to keep them separated. I will sure follow that. Thank you for your suggestion!