sql server data warehouse: Design question -- sql server data warehouse

Design question

Stijn Verrept
4/24/2007 12:00:00 AM

sql server data warehouse:

I have 2 tables like this:

First table has rows indicating is someone comes in (InOut = 1 or goes
out (InOut = 0)

InOut
SNID Date InOut
3 '2007-01-01' 0
4 '2007-01-15' 0
3 '2007-03-28' 1
5 '2007-01-01' 0
4 '2007-04-15' 1
5 '2007-02-12' 1

Second table indicates a category

Category
SNID Date Cat
3 '2007-01-01' 0
4 '2007-01-15' 4
3 '2007-04-16' 2
5 '2007-01-01' 2
4 '2007-03-15' 1
5 '2007-01-18' 3

I need to have a list of each week, month, year, ... with the number of
people present and their category:

Something like:

Date Cat Total
Jan 2007 0 31
Jan 2007 4 16
Feb 2007 ...

I can do the calculation each day and save the number to the
datawarehouse. But isn't it also possible to save the two tables to
the datawarehouse and have it do the calculations on the fly?

How can I do the second option? Is it with a slowly changing dimension?

--
Thanks in advance,

Re: Design question

MC
4/27/2007 7:45:40 PM

No, I would actually use T-SQL to calculate. It shouldnt take too long..
Something like (I would replace 0 with -a in InOut column):

select tbl2.category, tbl1.date, sum(tbl1.InOut) as NumberOfPeople
from
tbl1
inner join tbl2 on tbl1.date = tbl2.date and tbl1.SNID = tbl2.SNID
group by
tbl2.category, tbl1.date

MC

PS. You can optimize by using only recent changes or something....

[quoted text, click to view]