130 ) KP & RACKSPACE QSNS

 

Client : KP.org

Scheduled by : Peoplefy.com

Analysis : 

Qsn : He suddenly asked me to design a sample  data model 

Ans : i am not prepared thoroughly 

Qsn : How do you speed up sql query using perf tuning

Ans : ???

Qsn : prepare DM fact tables for inventory and shipment 

Ans : i have designed but not correct he said 

Where i failed : 

I dint analyse the whole o2c cycle to design it 

I shd have thought about if order delivered 

FACT table shd show how much was available , how much delivered , how much updated

Also i dint include sales order table. 

Answer below 

Inventory Fact Table Design

Rackspace ( 11jun26) 

1.  HOW WILL YOU CREATE SURROGATE KEYS IN DIMENSIONS AND 

2.  IF IT is a sequential number and if there needs to be a reload happening for customer dimension then how will you reload the same number to customers

3. if you have to apply for a 3nf  in dimensional model then how will you apply it 

---

4. SQL QUERY : 

• If a customer buys products from my company 

• If his address is changing from time to time 

• Query to track his order id and along with address shipped that order 

 

SELECT

    o.OrderID,
    o.CustomerID,
    o.OrderDate,
    o.DeliveryAddress AS OrderDeliveryAddress,
    c.CustomerName
FROM 
    [order] o
JOIN 
    customer c ON o.CustomerID = c.CustomerID;

Sample Output

OrderID	CustomerID	OrderDate	OrderDeliveryAddress	CustomerName
101	1	2026-06-01	123 Main St, New York, NY	Alice Smith
102	1	2026-06-18	999 New Pine Rd, Boston, MA	Alice Smith
103	2	2026-06-20	456 Oak Rd, Austin, TX	Bob Jones