1

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.stack.imgur.com/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.

Omkar C.
  • 755
  • 8
  • 21
rushikesh ayati
  • 17
  • 1
  • 6
  • Hi there, sorry to say, but it's hard to understand. Could you please expand and perhaps list more clearly your questions? – Luc Oct 27 '19 at 22:54
  • Thank you for responding, in above question basically i wanted to know whether the summarized data is itself called as fact value or measure like total sale, total qty sold, or it is the data on which we perform aggregation to get aggregated data? – rushikesh ayati Nov 08 '19 at 15:02
  • Great, I believe the from @APC is spot on. Please mark it as an accepted answer if, as you commented, it is a good response for you. Thanks. – Luc Nov 09 '19 at 11:28

1 Answers1

1

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.

APC
  • 144,005
  • 19
  • 170
  • 281
  • Hello Sir @APC, thank you very much for responding me, I got my answer. I am a beginner into SQL, so while studying always came across these kind of questions also i wanted to know how ETL projects works in real time.So if possible can you give me at least 2 hr of your valuable time in a week, anytime will be fine for me whenever you are free in a week days or weekends. This will really helps me to resolve my quires & doubts. – rushikesh ayati Nov 08 '19 at 14:43
  • I give my time free here but I charge for consultancy - it is my job after all. I'm afraid I'm not looking to take on any Padawans, and anyway I don't think you can afford my hourly rate :) – APC Nov 11 '19 at 15:04