EDW Design question

Hi All,
I have a design question . I got the requirement of 2 TB starting for
enterprise dataware house in my project. I want to implement next generation
warehousing technique. So want to have a near line storage layer in the
design.

If we seperate data such that current year and last year will be in
SAS(Serial Attached SCSI) drive and next past 3 years will be in Near line.
E.g. 2007 and 2008 will be in one cube which is located in SAS and 2006,2005
and 2004 will be in another cube stored in near line storage. Can we develop
such cubes seperately and how can we combine these for adhoc query option?
Another design is to have only one cube and use partitions based on years.
2008
& 2007 partition can be stored in SAS drive and remaining on some other
drive.

Please help me in this.

Thanks,
RK

the first question I have to ask is if your cibe diesign is MOLAP or ROLAP?
If MOLAP, then it doesnt really matter where your storage is, as long as the
cube is on the SAS drives. This will give your users the best performance,
and you can use partitions to seperate out the historical data.

If you are planning on a ROLAP implementation, then you can still use one
cube with partitioning, but place the historical data in different
fact/dimension tables in its own partition that resides on the RSL. that way
only historical data has the performance hit.
You could use seperate cubes, then link them up with a hyper cube. I know
its possible, but have not tried it myself.
hth,
Carl

"S V Ramakrishna" wrote:

> Hi All,
> I have a design question . I got the requirement of 2 TB starting for
> enterprise dataware house in my project. I want to implement next generation
> warehousing technique. So want to have a near line storage layer in the
> design.
>
> If we seperate data such that current year and last year will be in
> SAS(Serial Attached SCSI) drive and next past 3 years will be in Near line.
> E.g. 2007 and 2008 will be in one cube which is located in SAS and 2006,2005
> and 2004 will be in another cube stored in near line storage. Can we develop
> such cubes seperately and how can we combine these for adhoc query option?
> Another design is to have only one cube and use partitions based on years.
> 2008
> & 2007 partition can be stored in SAS drive and remaining on some other
> drive.
>
> Please help me in this.
>
> Thanks,
> RK
>
>
>
>

Thanks for the reply. Your comments are highly appreciable.
--
RK

"Carl Henthorn" wrote in message
news:61E57014-105F-41FE-9FD0-F9BF13D1877C-at-microsoft.com...
> the first question I have to ask is if your cibe diesign is MOLAP or
> ROLAP?
> If MOLAP, then it doesnt really matter where your storage is, as long as
> the
> cube is on the SAS drives. This will give your users the best performance,
> and you can use partitions to seperate out the historical data.
>
> If you are planning on a ROLAP implementation, then you can still use one
> cube with partitioning, but place the historical data in different
> fact/dimension tables in its own partition that resides on the RSL. that
> way
> only historical data has the performance hit.
> You could use seperate cubes, then link them up with a hyper cube. I know
> its possible, but have not tried it myself.
> hth,
> Carl
>
> "S V Ramakrishna" wrote:
>
>> Hi All,
>> I have a design question . I got the requirement of 2 TB starting for
>> enterprise dataware house in my project. I want to implement next
>> generation
>> warehousing technique. So want to have a near line storage layer in the
>> design.
>>
>> If we seperate data such that current year and last year will be in
>> SAS(Serial Attached SCSI) drive and next past 3 years will be in Near
>> line.
>> E.g. 2007 and 2008 will be in one cube which is located in SAS and
>> 2006,2005
>> and 2004 will be in another cube stored in near line storage. Can we
>> develop
>> such cubes seperately and how can we combine these for adhoc query
>> option?
>> Another design is to have only one cube and use partitions based on
>> years.
>> 2008
>> & 2007 partition can be stored in SAS drive and remaining on some other
>> drive.
>>
>> Please help me in this.
>>
>> Thanks,
>> RK
>>
>>
>>
>>