I have a fact table called
FactAccount which has the following columns:
AccountMonthId- FK referring to
The DBA has done the partitioning on the
AccountYear (2014, 2015, 2016...) column. But this table will be queried on the
AccountMonthId column (with a join to
DimMonth) where users will select the year and month from the
DimMonth dimension table.
So in this case, I don't think the partitioning will make any difference as the
WHERE clause doesn't filter by
DimMonth dimension contains all combinations of year and month, i.e. 12 dimension members for each year and the
AccountMonthId column is an identity column, not a natural key.
Is there a way to partition a table on a column in a table referenced by a foreign key? Or, to be very specific in my case, is it possible to partition the
FactAccount table on