Flowing Fount of Yak Knowledge
Posted - 06/06/2007 : 10:49:10
| Fact table? You mean in the Analysis services?
It is too generic question, without knowing your db structure, data mart design etc.
Secondly, you should post this question in the Analysis services forum on this site.
"The IMPOSSIBLE is often UNTRIED"
Posted - 06/06/2007 : 12:14:17
Originally posted by faizanayan
can any body tell me how to populate the fact table from base tables.
databse name: bookorder
customer (customerid,l_name,f_name,city, district,country)
Now populate the datawarehouse name: book_orderdw
having fact table & three dimension tables given below from the above bookorder database
dimtime(timeid,orderdate,dayofweek, month, year)
Now I have to populate the fact & dimension tables by writing sql scripts.
Now I have already populated the dimension tables by writing sql script,
But I have to populate the fact table taking into account, here I am facing problem in wriring sql script
(i) unit_price is taken from the book base table with reference to the isbn
(ii) sales_quantity is taken from the order_detail.quantity with reference to table cust_order(via orderid & orderdate)
(iii) discount_price is determined dependent on the quantity. if the quantity > 20 then discount 20 %(i.e discount_price = 0.8 * unit_price). if quantity < 10, no discount i.e normal price. if quantity between 10 and 20, discount 10%. Note that the quantity is determined based on each order of each customer, thus if the same book appears at multiple positions in an order, those positions shall be grouped together. This could happen because the pk of the order_detail table is order_id + item no, not order_id + isbn
(iv) sales_amount is sales_quantity * discount_price