How measure attributes values are populated in fact table?

Question

I am new to Data modelling and trying to understand the practical scenario of using star schema model for the business intelligence

below is the example image of star schema model- for a sales table. i understood dimension table and how it is referred in fact table with foreign keys. I have below 2 question

1. i am trying to understand how value for attributes- usually called measure attributes, in this case 'dollars_sold' and 'unit_sold' are getting defined/populated in fact table?

2. i believe BI team use this fact table to query for various analytics cases? Please correct me if i am wrong here?

i searched most places, but no where it clearly defined how exactly this measure values are getting retrieved in fact table . is there any query can give with an example

Answer 1

To take a very basic example, let's assume you have 2 tables coming from your source system: CUSTOMER and TRANSACTIONS, and you've loaded the data from CUSTOMER into your CUSTOMER_DIM dimension table:

CUSTOMER_SK	CUSTOMER_BK	LAST_NAME	FIRST_NAME
1	abcd123	smith	john

You then have a transaction record in your source TRANSACTIONS table that you want to load into your sales fact table. The transaction record looks like this:

CUSTOMER_ID	TRANSACTION_DATE	UNITS_SOLD	SOLD_AMOUNT
abcd1234	2022-08-22	1500	156789.87

To load your fact table your would run a query like this:

INSERT INTO SALES_FACT(CUSTOMER_SK, DATE_SK, UNITS_SOLD, SOLD_AMOUNT)
SELECT
NVL(CUST.CUSTOMER_SK,0) -- defaults to zero if no matching CUSTOMER_DIM record
,SRC.TRANSACTION_DATE, SRC.UNITS_SOLD, SRC.SOLD_AMOUNT
FROM SOURCE_TRANSACTIONS SRC
LEFT OUTER JOIN CUSTOMER_DIM CUST ON
    SRC.CUSTOMER_ID = CUST.CUSTOMER_BK

Answer 2

Firstly a Primary key (PK) stores unique values for a column in a relation database.Foreign key(FK) links data in one column with data in the other column. Fact tables contain the actual metrics or values of the attribute. Dimension tables hold the context for the attributes in Fact table.

There are essential two types of columns in Fact tables:

• Those that have a Foreign Key (FK) attached to it such that they can be joined with a dimension table such that context of its value can be retrieved. In this case the columns time_key, item_key, branch_key, location_key all have FK in Fact table and each of them have PK in time, item, branch and location tables.
• And measures columns that contain the data, in this case columns dollars_sold and units_sold

As an example if you wanted to find out the no. of units sold based on the brand or type of item, you cannot retrieve it from the sales table alone, you would have to first get the context by joining the sales table with the items table on the item_key and then retrieve the data.

select item_key, units_sold, brand from item
    inner join sales on item.item_key = sales.item_key
group by item_key;