Question about the data model

[Sapi]

Hello,

I am trying to play around with this DB found online: https://www.sqlservertutorial.net/sql-server-sample-database/

Having trouble modelling it in Qlik properly, as I am getting a circular reference which I am unable to resolve, not loosing the analytical capabilities.

The problem is with "stocks" table, as it contains both product_id and store_id. What would be the best way to resolve this?

 Here is the script:

[orders]:
LOAD order_id, 
	customer_id, 
	order_status, 
	order_date, 
	required_date, 
	shipped_date, 
// 	store_id, 
// 	staff_id,
    store_id&staff_id as OrdersToStaffsKey;
SELECT * FROM orders;

[order_items]:
LOAD order_id, 
	item_id, 
	product_id, 
	quantity, 
	list_price as order_list_price, 
	discount;
SELECT * FROM `order_items`;

[products]:
LOAD product_id, 
	product_name, 
	brand_id, 
	category_id, 
	model_year, 
	list_price as product_list_price;
SELECT * FROM products;

Left Join (products)
LOAD brand_id, 
	brand_name;
SELECT * FROM brands;

Left Join (products)
LOAD category_id, 
	category_name;
SELECT * FROM categories;

Drop Fields brand_id, category_id;

[customers]:
LOAD customer_id, 
	first_name &' '&last_name as customer_name, 
	city as customer_city, 
	state as customer_state;
SELECT * FROM customers;

[staffs]:
LOAD staff_id, 
	first_name & ' ' & last_name as staff_name, 
	active as staff_active, 
	store_id, 
	manager_id,
   store_id&staff_id as OrdersToStaffsKey
    ;
SELECT * FROM staffs;

[stores]:
LOAD store_id, 
	store_name, 
	street as store_street, 
	city as store_city, 
	state as store_state, 
	zip_code as store_zip_code;
SELECT * FROM stores;

[stocks]:
LOAD store_id, 
	product_id, 
	quantity as stocks_quantity;
SELECT `store_id`,
	`product_id`,
	quantity
FROM stocks;

[Michael_Tarallo]

Hi Sapi - I am going through unanswered threads and came across this one. I am attempting to setup MS SQL Server on my system so I can reference this example - but wanted to share a video with you to see if this helps:

https://www.youtube.com/watch?v=vPrsPXW2N2I

it explains the circular reference, and a simple way to resolve that is to rename or remove the field in the table from the data load (if not needed).

Let me know how you are doing and if we can be of further assistance.

[BrunPierre]

Hi @Sapi, your requirements should inform you on how to resolve circular references.

one way could be to create a snowflake schema by combining what happens to the fact tables (order_items, orders & stocks) by way of concatenation.

[Orders & Stock]:
//[orders]:
LOAD order_id, 
	customer_id, 
	order_status, 
	order_date, 
	required_date, 
	shipped_date, 
// 	store_id, 
// 	staff_id,
    store_id&staff_id as OrdersToStaffsKey;
SELECT * FROM orders;

Concatenate
//[order_items]:
LOAD order_id, 
	item_id, 
	product_id, 
	quantity, 
	list_price as order_list_price, 
	discount;
SELECT * FROM `order_items`;

Concatenate
//[stocks]:
LOAD store_id, 
	product_id, 
	quantity as stocks_quantity;
SELECT `store_id`,
	`product_id`,
	quantity
FROM stocks;


.....<Load remaining tables> .....