Populate the fact table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Populate the fact table

Author

Topic

faizanayan
Starting Member

4 Posts

Posted - 2007-06-06 : 10:34:44

Hi!

can any body tell me how to populate the fact table from base tables.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-06 : 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.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-06-06 : 11:28:22

Ralph will tell you! [url]http://www.amazon.co.uk/Data-Warehouse-ETL-Toolkit-Techniques/dp/0764567578/ref=pd_bbs_sr_1/203-6263032-2620703?ie=UTF8&s=books&qid=1181143631&sr=8-1[/url]

Mark

faizanayan
Starting Member

4 Posts

Posted - 2007-06-06 : 12:14:17

quote:
Originally posted by faizanayan

Hi!

can any body tell me how to populate the fact table from base tables.

databse name: bookorder

Realtions:

customer (customerid,l_name,f_name,city, district,country)
cust_order(orderid,orderdate,customerid)
order_detail(orderid,itemno,isbn,quantity)
book(isbn,title,edition_no,price)
author(authorid,name,surname)
book_author(authorid,isbn,authorseqno)

Now populate the datawarehouse name: book_orderdw
having fact table & three dimension tables given below from the above bookorder database

fact table

factsales(customerid,timeid,isbn,unit_price,discount_price,sales_quantity,sales_amount)

dimension tablesdimcustomer(customerid,l_name,f_name,city,district,country)
dimtime(timeid,orderdate,dayofweek, month, year)
dimbook(isbn,title,edition_no,price)

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

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-06-06 : 14:15:56

May use ssis package.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources