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!

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

Jobs

Rule for Partition

Rule for Partition

(OP)
Hi All,

We have 6 Mln rows in Fact table with 100+ measures. As of now we have 3 partitions with following config

Part1 : 1,834,099 with 0%Aggregation MOLAP
Part2 : 3,092,143 with 30%Aggregation MOLAP
Part3 : 1,105,211 with 30%Aggregation MOLAP

We are using SQL Analysis Service 2005 on 64Bit Server with 2CPU and 8GB RAM. We are using VMWare.

Question1: All our measures in FACT tables are related, is it good to have single FACT table or we should some how split it into multiple fact tables.

Question2: Considering our system configuration, should we create more partitions. How much should be size of a partition and how is it related to system configuration. I read on Microsoft website that a partition should not have mroe than 20 Mln rows but could not find proper RULE/FORMULE to divide partitions.

Question3: At this moment CUBE PROCESS (FULL)take 30 to 40 mins. Is it right time or can we get better performance if we increase hardware CPU/RAM. How much should be ideal CUBE PROCESS time for 6Mln rows?

Question4: I read somewhere if design is OK, we should be able to process 2-5Mln rows per minute. Is it true. With our hardware how mich time it should to take to process cube with 6LMN rows in Fact and 10 Dimensions.

Dimensions are not big, except one Dimension with 100,000+ and 2nd with 5000 memebers, others are less than 2000.


Thanks in advance.

 

RE: Rule for Partition

(OP)
Corection

Our Fact table has 200 measures not 100 and all are related.

 

RE: Rule for Partition

when you say the measures are no related, are they not related to eachother or not related to all dimensions joined to that fact?

200 Measures does seem like quite a lot especially for a single measure group.

Most people start by partitioning the data across an element of time such as Month depending on the amount of data they may go lower say to the day level or even involve another dimension in the partition Say Month  & Product Category.

How frequently is your cube loded?
What is the partioning strategy in your data mart?
What is the relationship of your measures to one another?
How man months/years of data do you have loaded?
 

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

RE: Rule for Partition

(OP)
I mentioned all MEASURES are RELATED so it may not make sense to break Fact table but still if breaking Fact Table may help than we can consider that too.

I have partition on base of date whcih is working fine. I want to optimize our environemnt and trying to figure out if we can do somthing better and hense all my questions in original email.

It will be really helpfull if you could reply to questions.

Answer to your questions:
How frequently is your cube loded?
Ans: It is loaded 4 times in 24 hours. 3 times during day and once time in late night

What is the partioning strategy in your data mart?
Ans: We donn't have any partition on Fact table in warehouse, if that was your question. Do we need to create partition on FACT table and what should be STRATEGY/RULE.

What is the relationship of your measures to one another?
It is hard to explain, it may be possible to group them up in separate FACT if need. Again question is, is it requried? I read on MS website that recommendation to KEEP related MEASURES in single FACT table. Each MEASURE explain some aspect of our lowest GRAIN in FACT and it is possible to use any set/group of MEASURES in query/report. We are using 3rd party tool called Tableau against our cube.

How man months/years of data do you have loaded?
Starting from 2003 till June 2007 we were only saving month end data but since JUly 2007 we have DAILY data also. Over all we have 400 dates in Time dimension till date.

RE: Rule for Partition

What is your cube partinioned on?  You have 3 partitions but how are the sliced?

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

RE: Rule for Partition

oops!

Also what is the Date/Time grain of your data?  both in the DW and in your cube.

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

RE: Rule for Partition

(OP)
What is your cube partinioned on?
Cube is partitioned on date, for example

Partition1: Most recent 200 dates
Partition2: Most recent 201 to 300 dates
Partition3: Older than Most recent 300 dates.

How it works: During warehouse load we update a field called [DaysDiff From IsRecent] which has DAYS difference between given date and current date for instance
For today: [DaysDiff From IsRecent] =0
For Yesterday: [DaysDiff From IsRecent] = 1
etc..

In both warehouse and cube DATE/Time grain is DATE so we DON'T have HOURLY data. LOWEST time factor is DATE and than Month and than Quarter and at last YEAR.
 

RE: Rule for Partition

So records can move from Partion 1 to partition 2 over time?

If this is the case then you should revisit the design.  There is really no point unreloadin and unloading records as they get older.  You can partition your cube by month and filter the base query used to process the partition where Date Between first and last or if you have implemented a proper date dimension you can say where date_sk between x and y.  

how long does it take you to do a Select * From Table where IsRecent = 1 ?

If it does not yet exist you may want to put an index on this column.

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

RE: Rule for Partition

(OP)
We have INDEX on field used to filter partition.

Our historic data also get UPDATED daily so we are doing "FULL PROCESS" daily.

Question1: Is there any better option other than "FULL Procss" considering the fact our historic data get UPDATED daily. According my understanding INCREAMENTAL update wouldn't work in our environment.

Question2: Is it still BAD to move records between partition if we are doing "FULL Process"

So you are suggesting it is BAD design to let records move from one Partition to other. I was not aware of this fact, thanks so much for pointing it out . Could you please give specific example to partition our Fact table in cube. I couldn't understand what do you mean by

"You can partition your cube by month and filter the base query used to process the partition where Date Between first and last or if you have implemented a proper date dimension you can say where date_sk between x and y.  "

My Time dimension has following fields

TimeKey int         IDENTITY,
ImportDate_Org      datetime    NOT NULL,
    YearNo          int         NULL,
    QtrNo           int         NULL,
    Qtr             varchar(2)  NULL,
    MonthNo         int         NULL,
    MonthName       varchar(9)  NULL,
    Weekday         varchar(10) NULL,
    MonthEnd        varchar(5)  NULL,
    QuarterEnd      bit  NULL,
    YearEnd         bit  NULL,
    [Is Recent Single Import Date]       bit NULL,
    [Is Recent Single Import Date -1]    bit NULL,
    [Is Recent + Trailing 3 Month Ends]  bit NULL,
    [Is Recent + Trailing 12 Month Ends] bit NULL,
    [Is Trailing 365]                    bit NULL,
    [DaysDiff From IsRecent]             int NULL

 

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!

Resources

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