×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Implementing SQL Server 2005 Partitioned Fact Table?

Implementing SQL Server 2005 Partitioned Fact Table?

Implementing SQL Server 2005 Partitioned Fact Table?

(OP)
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?

RE: Implementing SQL Server 2005 Partitioned Fact Table?

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

RE: Implementing SQL Server 2005 Partitioned Fact Table?

(OP)
I was hoping you'd say that. Thanks!  bigsmile

RE: Implementing SQL Server 2005 Partitioned Fact Table?

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

RE: Implementing SQL Server 2005 Partitioned Fact Table?

(OP)
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? smile

RE: Implementing SQL Server 2005 Partitioned Fact Table?

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.

RE: Implementing SQL Server 2005 Partitioned Fact Table?

(OP)
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!

RE: Implementing SQL Server 2005 Partitioned Fact Table?

Quote (drajones):

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close