I am looking for someone who can give some advice on creating
partitions/cubes.

Here is my situation:
I have a fact table with 80 million rows (results for kids in a state). I
create a giant cube with all the results and it takes too long to process.
(I have the need to process this data a couple times a day)

I am researching creating several cubes (450, one per district)
VS
creating several partitions (450, one per district) for the same cube.

It is my understanding that I can process partitions separate from one
another which would allow me to do more of a selective update on the cube.

Anyone out there have any advice or resources they could point me to which
would assist me in making this decision?

tx,
tim

I recommand parititions!

the work required to "merge" these cubes into 1 virtual cube and create
calculated measures which aggregate the 450 measures of the 450 cubes is
big.

also, with cubes you have 450 cubes to manage. 1 change = 450 changes !!!

also, with a virtual cube in top of your cubes, you can have a problem if 1
or more cubes is not processed.
For example, if you change 1 dimension (or you do a full process of 1
dimension), then you have to reprocess all cubes before the virtual cube can
be used. With the partition option you must reprocess all partitions, but
after the creation of the first partition, the cube is accessible to the
end user. (Well only 1 district accessible at this time, but its better then
nothing)

in another way, if you have no plan or no measure across these 450 districts
("all districts" not available and no district comparison needed) then you
can have 450 cubes whitout any virtual cube and each user must select the
cube to use.


"Tim Chapla" <tchapla (AT) nospam (DOT) turnleaf.com> wrote

Absolutely agree. Use partitioning.

There is a discussion of partitioning in the SQL Server 2000 Resource Kit
book, and here:
http://www.microsoft.com/resources/d...rt6/c2661.mspx
(in the section titled Partition Strategy)

And here: (AS Performance Guide)
http://www.microsoft.com/technet/pro.../ansvcspg.mspx
(all over the place)

And here: (AS Operations Guide)
http://www.microsoft.com/technet/pro.../anservog.mspx
in the section titled: Performance Configuration Issues (among other
sections)
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


"Jéjé" <willgart_A_ (AT) hotmail_A_ (DOT) com> wrote

Thank you very much for the info.

My final step is to implement a changing dimension (not sure if I should go
shared or private) on the cube to allow me to process specific partitions
based on changes to the dimension.

I have read that member keys must be unique. It will have a Parent(Class)
and a child(Student) level. Does this mean I cannot have Students in more
than one class?

Can anyone provide me with any links providing more detailed descriptions
and implementations of changing dimensions? I am having some difficulty
understanding how changing dimensions work and require processing(the cube
and dimension).

thanks again,
tim

"Dave Wickert [MSFT]" <dwickert (AT) online (DOT) microsoft.com> wrote

Exensive discussion in the AS Performance Guide
http://www.microsoft.com/technet/pro.../ansvcspg.mspx
And some minor ones in the AS Operations Guide
http://www.microsoft.com/technet/pro.../anservog.mspx
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


"Tim Chapla" <tchapla (AT) nospam (DOT) turnleaf.com> wrote

in a changing dimension the lowest level must have a unique key.
(the student key in your case)

And yes, the student is in only 1 class in a changing dimension.

If you want to have multiple classes by student you have to create a new
student (1 student by class) but not so good because you'll count twice the
same student. or you have to create 2 separate dimensions 1 for classes 1
for students



"Tim Chapla" <tchapla (AT) nospam (DOT) turnleaf.com> wrote

Yes partitioning could solve the issue, what i feel is creating region
wise OLAP databases which should cover all the districts involved so
that atleast 4 OLAP databases can handle all the districts associated
and then partitioning in each of the olap databases .20 million rows in
each of the OLAP databases is not an issue for processing and on top of
it monthly or yearly partitioning in each of the cubes in the OLAP
databases and having sliding window to hold the data for the max 2-3
years for analysis in each of the OLAP database would solve the
performance of querying also.

Regards,
Prasanna

That totally kills me. I have to be able to report on this data in real
time, AND I must be able to put a student in multiple classes. I have over
64,000 students in the system, therefore I cannot create a 'Student'
Dimension because it breaks the 64,000 child limit. It is really beginning
to look like I may need to create District specific Cubes. I will keep
prototyping and let you all know what I do. Any other ideas out there?

Thanks for all the input

"Jéjé" <willgart_A_ (AT) hotmail_A_ (DOT) com> wrote

Awesome articles. Packed to the gills with information. Thank you.

"Dave Wickert [MSFT]" <dwickert (AT) online (DOT) microsoft.com> wrote

the student dimension can have 2 or more levels.
For exemple, group the student by the first letter of the name, this split
your students dimension.
Or you can split by district if the student is not in 2 district at the same
time.

Realtime data for a student system... I hope your users will not ask you for
distinct count calculations!!!!
I'm not sure if olap cubes are rights for you, because I presume that your
users will create student level queries and not or less statistical
analysis. (but I don't know your requirements)

District specific cubes will kill your AS server. specially with real time
updates.
if you are in prototype mode, look at 2005 which provide more features for
your needs (student level detail, better real time support...)

"Tim Chapla" <tchapla (AT) nospam (DOT) turnleaf.com> wrote