Fact Tables like Partitions in 2005

[kristl]

Hi All -

In MSAS 2000 it was possible to have each partition point to a
different fact table - not to meet the need now being met by mulitple
measure groups but just for data management and some security
settings.

I am struggling with how to emulate this in the MSAS 2005. I want to
have monthly fact tables - Oct 2008, Nov 2008 and Dec 2008 all of them
exactly alike in form and how they join to the dimensions but each
with a separate partition in the same cube.

I tried different approaches...like mulitple data source views etc.
I've found one email suggesting that I do a union statement in the
original Data Source View.

Does anyone know what would be considered best practices in this case?

[fim...@exologic.com]

I'd probably just make each month a partition, and since you can
process just the partitions, you could just process the last few
months where the data is still settling (i.e., maybe facts that are 4
months would never change). If history does not change in your cube
(i.e. no restatements), then you could just have all of the whole, old
years, be other partitions (by the year) that are older and never
change, so there is no need to reproc them.
You might also have a few relational views that each have a WHERE
clause that specifies the month you care about, and justhave each
parititon point to each of the monthly views, by specifiying that in
the parition's embedded SQL (a different FROM
[vwMonthlyFactsYYYYMM]).

-exologic

[kristl]

we have a super strong user requirement to not touch a certain month's
data after a certain time period so we are keeping the table separate
so we can lock them into read only mode after financial close each
month. I really, really need one table per partition. I can tell that
different views were not the answer. I'm trying a Union in the view
after all. Anyone have any other ideas?

[Ohjoo Kwon]

How about using partition feature of SQL Server too.

Ohjoo

"kristl" <kristlsm...@gmail.com> wrote in message
news:afe33ef9-990d-4004...@m44g2000hsc.googlegroups.com...

[fim...@exologic.com]

You could just *not* process the other, older, partitions. That would
leave the facts and their aggregations intact.

[Deepak Puri]

If all the monthly tables include the columns of the fact table for the
measure group, you should be able to bind each partition to the relevant
monthly table:

http://msdn.microsoft.com/en-us/library/ms186527(SQL.90).aspx
>>
SQL Server 2005 Books Online (September 2007)

Table Binding Detail (Partition Source Dialog Box) (SSAS)

Use the Table Binding option in the Partition Source dialog box to
specify the fact table that provides the data for the partition. You can
display this pane by selecting Table Binding from the Binding type
option in the Partition Source dialog box.
..
>>

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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