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)

trans hours:0-1 1-2 2-3 ....
type ac az ay total ac az ay tot ac az ay tot ....
Consignee:
a 2 2
b 1 1 2
c 1 1 2
d 1 1
Total 4 1 5 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

The list of dimensions sounds about right: "(I think carrier , consignee
, type , time dimensions ??)".

When using AS 2000, I've typically split Time into Date and TimeOfDay
dimensions, each with its own dimension table, and used a view over the
fact table to compute fields for just the date and time of day. In this
case, "Trans Hours" might be a time of day dimension - with AS 2005,
there may be more ways to implement it.

I an Analysis Services cube is built on the star schema above, then
Excel (2000 and up) pivot tables can connect directly to such an OLAP
source. Not all normal pivot table features may work, though -
especially with Excel 2000.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***