Implementing SQL Server 2005 Partitioned Fact Table? 1

[alexjones]

I have a check (the kind you use to pay your bills) fact table with three date-role dimensions - issue date, expiration date and final disposition (stopped, cleared, etc.) date. These will be implemented in the usual way - each date represented by the appropriate key value from its date dimension.

This will be a huge table and I would like to take advantage of SQL Server 2005 partitioned tables to manage it. I would like to partition the table on expiration date (which is determined at the time a check is issued).

Would it be reasonable for me to include the actual expiration date as a degenerate dimension, strictly for partitioning purposes?

 

[johnherman]

Yes. The design must statisfy technical requirements as well as user requirements. Yes.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[alexjones]

I was hoping you'd say that. Thanks!

 

[Hans63]

I do not grasp why you would include the actual expiration date as a degenerate dimension. Why not as a real dimension.

 

[alexjones]

I have included an expiration date key to a full expiration date dimension in the fact table for analysis and reporting purposes. The actual expiration date is used strictly during the load process to determine in which partition of the physical table the check record belongs.

Does this help?

 

[Hans63]

What is the reason to use a partition field that you don't use? I am not familiar with SQL server, but with Oracle. You will get the best benefit if you partition along a column you use very often.

Of course you can use it, and the RDBMS will be better able to process your queries in parallel. Any query that does not use the partitioning column will access all partitions.
So I would look for another key for partitioning.

 

[alexjones]

You are absolutely right. Using the actual date instead of the key to that date in the date dimension is a nervous newbie thing - the SQL Server partition functions and schemas would be easier for a human being to interpret with dates specified instead of surrogate keys. I had not fully considered the effect of my partitioning choice on accessing the data once the table was loaded. Will work with my DBA's on this.

Thanks for your perspective! Have a star!

 

[MDXer]

the SQL Server partition functions and schemas would be easier for a human being to interpret with dates specified instead of surrogate keys.

Not if the individual understands the partioning scheme. In SQL Server the partition is transparent to the user. If you run any analysis on your partioned data you get Partion 1 Rows: X Storage Use: Y. Since SQL Server stores your datetime value as a float it is still being stored at the base level as a number and not date.

I use surrogate keys all the time but sometimes using a smart key makes more sense. And in the case of Dates I use smary keys. Many people use smart keys for dates in a number of different formats forexample YYYYMMDD 20050101 for Jan 1 2005. Personally I use the part of the float that represents the Day which is the value to the left of the decimal or FLOOR(CAST(date as float)) I use the floor to make sure that records posted afternoon still get keyed to the proper dates.

So if a developer knows both your partitioning scheme and you date dimension then it is pretty easy for them to figure out.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck