Hello,

I have to design a OLAP solution for the business analysts. The data in the
transaction table is someting like below

table transactions
( transaction_dt , consignee , carrier)

transaction_dt consignee carrier type
xx/xx/xxxx 01:01 a z ac
xx/xx/xxxx 02:01 b y az
xx/xx/xxxx 01:01 c x ay
xx/xx/xxxx 02:11 d w ac
xx/xx/xxxx 01:01 a w ac
xx/xx/xxxx 03:00 b x ay
xx/xx/xxxx 01:01 c y ac


I have to design a aggregates repot dynamic one, where the user can choose
the hours as the header and consignee/carrier as deatail , like below
( the time interval is 1 and consignee is chosen for detail below; the users
can choose their own time interval and consignee/carrier in detail)

transaction hours : 0-1 1-2 2-3
.........119-120
type ac az ay total ac az ay total ac az ay
total ac az ay tot
Consignee:
a 2 2
b
1 1 2
c 1 1 2
d 1 1
Total 1 1 2 3 3 1
1 2



( this data format mostly looks like a pivot table)
can some body tell me how to go about doing this report , as there is a huge
number of rows in the 'transactions' table

do I have to do a star schema and pump the data to it ?
IF I have to buid a star schema , what dimensions should I consider ...
( I think carrier , consignee , type , time dimensions ?? what fields
needs to be considered in time dimension and what fields needs to be there in
fact table ? )

If I have to display this report in an excel sheet and email it to the
analysts whenever they want , what is the best tool to connect to the star
schema in sqlserver and throw it to excel.

What would be the easiest and best solution...Any books, links or
suggestions will be greatly appreciated .

Thanks