Solved

Partition the fact tables based on 2 columns

Posted on 2011-09-14
4
527 Views
Last Modified: 2016-02-15
I have a fact table with many fields among which two important columns are Article Id and NewsDate. Article Id's are not in a specific order. They are random. So for partitioning can I use both Article Id and News Date for filtering.

select ArticleID,NewsDate from vwFactTableArticle
where ArticleID>5100000 and NewsDate>'2011-01-01'
0
Comment
Question by:himabindu_nvn
  • 3
4 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36538927
himabindu_nvn,

I believe the answer for SQL 2005 and 2008 is you cannot. You must partition on one column.

Even if you could, just as a clarification, why would you want to? If the ArticleID is random within a given NewsDate range, are you sure you want to create a PARTITION by ArticleID | NewsDate combination? That would result in for each NewsDate range, a partition for each ArticleID range within it. Is that what you want? i.e., sub-partitioning?

I would think you would want to partition by NewsDate and using date range filter to the correct partition to find data which includes random ArticleIDs.

Anyway, it cannot be done, or at least I have not found any documentation stating it has been added to newer versions of SQL, including Denali. Therefore, I will have to stick with you cannot until someone educates me otherwise. Sorry.

Kevin
0
 

Author Comment

by:himabindu_nvn
ID: 36538941
Thanks for the response. I have already partitioned another fact table based on NewsDate. Can I use NewsDate again to partition another fact table?
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36539020
Now, what you could do is create a composite key that represents your date + article id, just ensure to put it together in a fashion that scales with your data. For example if your ArticleID is BIGINT, it can have 19 total digits; therefor you could in theory do something like this in a computed column:

DECLARE @ArticleID BIGINT, @NewsDate DATETIME;
SET @NewsDate = '2011-01-01'
SET @ArticleID = 5100000 

SELECT CONVERT(NUMERIC(38,0), 
               CONVERT(BIGINT, 
                       @NewsDate
               ) * POWER(10.0,19) + @ArticleID
       ) AS Option1
     , CONVERT(NUMERIC(38,0), 
	           CONVERT(BIGINT, 
			           CONVERT(CHAR(8), 
                               @NewsDate, 
                               112
                       )
               ) * POWER(10.0,19) + @ArticleID
       ) AS Option2
;

Open in new window


Option1 ==> 405420000000000005100000
Option2 ==> 201101010000000000005100000

Whichever you choose, you can use the resulting value as your partitioning column.
You will have to remember to build your searches based on this longer column, though, so that is the trade off.
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 36539041
You are applying partition scheme to a particular table, so I would not see why not. But maybe I am just not experienced enough with SSAS and partitioning.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now