I have strong confusion with fact values, is it derived directly from OLTP by ETL mechanism into fact table & then we need to perform aggregate function to get summarized that by throwing queries? Or it is nothing but summarized value that we get from the dimensional table present into star schema but not directly from OLTP?
Please find attached two examples for more detail.
in stark schema1 image,https://i.sstatic.net/Ibbg7.png fact table showing two facts or measure as 'dollars_sold' & 'unit_sold' where is this coming from? & it is not summarized however with this fact table by applying aggregate function summarized date can obtain.
in stark schema2 image, fact table showing one facts or measure as total sales, but here i know that it came from customer-trans-dia also it is summarized data.
So please tel me out of this two example which one is correct?
Thanks in advance.
is it derived directly from OLTP by ETL mechanism into fact table
Yes that is the usual meaning of FACT table: a table of lowest level operational data items.
Such data may be subsequently processed into aggregations or other configurations, in data marts. But data mart users normally want the capability to drill down to the raw data if necessary. Otherwise how can they have confidence in the summed amounts?
So your first schema example shows a true fact table and your second example is more of a data mart. (Data marts are derived from fact tables but often do not expose the raw data immediately, presenting a processed version of it suited to the data mart's purpose.)
where is this coming from?
Your first example schema is obviously a toy one, intended for training purposes. In real life we would expect the fact table to have metadata columns telling us where the data came from and when it was loaded into the table, and probably much else besides.
