Sunday January 21st 2018

Categories

Insider

Archives

Example – Buliding a sample data warehouse using SQL Server

This post is about an example of populating fact tables in Funds data warehouse. All the data from dbo.tranhistory was extracted along with the foreign keys for four dimensions Managers, Offices, Funds, and Accounts.

In writing this post we followed the Funds Database example in SQL Server OLAP Developer’s Guide by William C Amo published year 2000, check with Amazon if this is available. We like this example because it elaborated the process of populating the fact table very well.

The sql statement was executed to populate fact 2 fact tables (Please see funds Star Schema picture)
as follows;
1- Investments
2- Dividends

Sql statement for populating fact table Investments
select a.manager_num, M.office_num, f.fund_cd,f.fundacctno,t.trandate,
t.amount from accounts a INNER join managers m on a.manager_num=m.manager_num
inner join fundaccounts f on a.acct_no = f.ACCT_NO
INNER JOIN TRANHISTORY T ON (F.FUND_CD = T.fUND_CD) And f.fundacctno = t.fundacctno
Where t.trantype = ‘invest’
—————————————————-
Sql statement for populating fact table Dividends
select a.manager_num, M.office_num, f.fund_cd,f.fundacctno,t.trandate,
t.amount from accounts a INNER join managers m on a.manager_num=m.manager_num
inner join fundaccounts f on a.acct_no = f.ACCT_NO
INNER JOIN TRANHISTORY T ON (F.FUND_CD = T.fUND_CD) And f.fundacctno = t.fundacctno
Where t.trantype <> ‘invest’

Creating four dimensions – Managers, Offices, Funds, and Accounts sions should be simple (Please see Funds OLTP Picture and Funds Star Schema), we will write more in our next post please feel free to add more o this topic.

Read similar topic