partitioning fact table

We have a very big fact table, almost 20million rows for each year, If I partition with each year in the warehouse how can I access all the fact partions as a single fact table when designing it in the data source view in BIDS?

Although I can partition the cube in analysis services by each year, this doesn't seem to solve the problem because the table is enormous and I need to partition it in the warehouse level. Can anyone shed some light?

I will greatly appreciate your feedbacks.

Rok

In SQL 2005 you can create partitioned tables and Indexes and the Query Engine is smart enough to direct requests to a specific partition. You can also store different partitions in different file groups if you want. You should be able to find plenty of documentation in BOL.

But you don't need to have all your data in one table for it to work with BIDS, You can pick one table to work with in the cube designer and then link other tables to specific partitions.

|||

This paper covers both relational and OLAP partitioning for Project REAL:

Project REAL: Data Lifecycle Partitioning

Overview

This paper provides a detailed discussion on how partitioning was implemented, both on the relational data warehouse and in the Analysis Services cubes. In addition to providing the general "how we did it" overview, we include specific code segments and lessons learned in the hopes that the reader will benefit from both our successes and failures. It is our hope that anyone planning or implementing a BI system based on SQL Server 2005 will find the reference implementation useful.

...