Fact table granularity

[roisolberg]

Hi All,

I am developing a model in Qlik sense and iv'e been thinking about my fact table.

Basically the fact table should be at the most atomic grain level.

In my model the fact table is my purchase orders, but the purchase orders have header and lines.

Initially i thought about treating both header and lines as a fact table and create a link table, but i am starting to think that this is not the right way to do this,  and maybe i need to just join my header and lines into a single table and use that as the fact table and create a normal star schema. 

Any thoughts about this? Any better ideas?

Many thanks,

Roi

[dplr-rn]

not entirely clear on the issue. could you share sample data.
In theory star schema is the way to go. but depending on your requirement, size of data etc. pick the best match (link table vs start schema) for performance plus flexibility

[JordyWegman]

Hi Roi,

This is really a business question. What you have to think about are four things:

1. Joining headers and lines with an outer join means that you can both have headers without lines and lines without headers
   1. Impact of this is that you can have an order without any products for instance that are ordered, also an empty order.
   2. Also you can have products that have been bought, maybe, but you don't have a header to attach it to.
2. Joining headers on the lines with a left join means that you have only headers with lines, but you can have lines with no headers.
   1. You can have products that have been bought, maybe, but you don't have a header to attach it to.
3. Joining lines on the headers with a left join means that you have only lines with headers, but you can have headers with no lines.
   1. Impact of this is that you can have an order without any products for instance that are ordered, also an empty order.
4. The last option is doing an inner join of the headers and lines. This will result only in combinations where both are present.

Jordy

Climber

[roisolberg]

Thank you @dplr-rn and @JordyWegman ,

I am still not sure on the way to go here

the first option was making the PO header as fact 

the schema looks as follow:

and if i look on the tables the data is:

In the second option i used Inner join between the PO header and line,

The schema:

the data:

Should i treat the PO lines as a fact table as well? it doesn't seem right as it is an extension of the PO header

and if i join them it looks like it is duplicating lines.

what do you think?

[marcus_sommer]

The simplest and maybe most suitable approach could be not to join/map/concatenate the header- and the line-table else just to associate them within the datamodel with the order-id. By a common header/line-datastructure the most calculations and views should work with this logic.

- Marcus