1

I have a fact table called FactAccount which has the following columns:

  1. AccountId (identity)
  2. AccountMonthId - FK referring to DimMonth
  3. AccountYear

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 AccountYear.

The 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 DimMonth.AccountMonthId?

  • 2
    From what you are saying it sounds like your queries are pulling "All rows in February" regardless of year? Most queries I've seen are going to need both Month and Year. – Kenneth Fisher Feb 8 '16 at 17:39
  • 2
    On @KennethFisher's note, does DimMonth contain one row per month (total of 12 rows) or one per (Year, Month)? – Daniel Hutmacher Feb 8 '16 at 17:50
  • @DanielHutmacher - Yes, DimMonth has one row for each month and year combination. so for 2014, it has 12 rows, for 2015, it has 12 and so on. – Vikas Singh Feb 8 '16 at 18:36
  • @KennethFisher - I'll restrict based on DimMonth. Like Select * from FactAccount A inner join DimMonth m on a.AccountMonthId = m.MonthId where M.Month = 2 and m.Year = 2016 – Vikas Singh Feb 8 '16 at 18:38
  • 1
    Sounds to me like the table needs to be redesigned. I don't understand the need to partition by entire years instead of by months, or what value using a foreign key adds instead of just putting all of the date information into this table in the first place. I don't have a problem with joins to a dimension table to add other information, but if you're querying this table based on date range, you should be able to filter against JUST THIS TABLE rather than relying on join conditions to filter. – Aaron Bertrand Feb 8 '16 at 19:03
2

Short answer: No, you cannot partition by a column in another table, because that would cause problems whenever you change something in the "other" table - rows would magically have to move between partitions.

Summarizing some of the views in the comments from Aaron, Kenneth and me on your question:

  • You could change AccountMonthId from a sequential number to a natural key, in this case an integer column from which you can compute the year and month. For example, the value 201503 could correspond to march 2015. This type of key is very common for date dimensions, because date dimensions don't change, so you can hard-code their keys.

  • With a natural key for AccountMonthId you could partition on this column, RANGE RIGHT FOR VALUES (201400, 201500, 201600) or RANGE LEFT FOR VALUES (201499, 201599, 201699).

  • Whether you have dates or just month values in your table, you could use a date column to store dates/months in your fact tables and in the month dimension. This would not only save storage space (three bytes, as opposed to four with int), it would also allow for your queries to use date functions among other gains. The same logic still applies with regards to partitioning.

  • You can still achieve what's known as "partition elimination" (querying a single partition) in your current setup, as long as you include the partition column in your query. With your current setup, that could look something like Select * from FactAccount A inner join DimMonth m on a.AccountMonthId = m.MonthId where M.Month = 2 and m.Year = 2016 and a.AccountYearId = 2016

  • You could partition on months instead of years. It'll take up the same disk space, although there are perhaps more partitions to manage, but this seems like a minor cost.

  • Adding an extra column for the year and partitioning on that column seems a bit lazy, ignorant, or at least strange of your DBA.

  • We have hardly touched on the subject of indexing - indexes, be they aligned or non-aligned, are often much more important than partitioning as performance goes.

  • 1
    I also don't quite understand the resistance to use an actual date there, e.g. 20150301 stored using a date data type instead of an integer. It actually takes less bytes (3), and adds all kinds of functionality (such as automatic constraint violation prevention and datepart magic that can eliminate a lot of dimension joins). Data warehousers have tried to convince me why such and such expert said that dimension keys always have to be integers but aside from name dropping their arguments aren't very convincing. – Aaron Bertrand Feb 8 '16 at 19:44
  • Agreed. Adding this to the answer. – Daniel Hutmacher Feb 8 '16 at 19:47
  • @AaronBertrand: +100 for why not an actual date & name dropping :-) I'm working on DWHs and never got that "store a date in an INT like 20150301*, invalid dates should be handled by the ETL process. – dnoeth Feb 8 '16 at 19:58
  • 1
    In all fairness, there are applications out there that use "special dates" (yuck!) for opening balances or year-end stuff. Those "dates" can look like 20151299, 20160000, etc, and for multiple reasons, they can't be mixed with regular dates. Breaks my heart every time, but at some point, I've just started accepting it as the norm. Slightly off-topic, though, because OP doesn't seem to have this problem. – Daniel Hutmacher Feb 8 '16 at 20:03
  • 1
    @Vikas That can still be made into a date. – Aaron Bertrand Feb 8 '16 at 20:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.