Hi Simon,
The theoretical limit for the number of members in a MOLAP dimension is
2147483647 (which I think is what you're talking about, not the maximum
number of dimensions in a cube, which is 128 - see the BOL topic 'Analysis
Services-specification'), but the practical upper limit is dictated by
memory. 4 million members in a dimension is possible, but I would advise the
following:
- Make sure you have 4Gb of memory on your server, and that you use the /3Gb
Windows boot setting and adjust the HighMemoryLimit registry setting to make
sure you can use all available memory
- Only include member properties that you need to use. Each dimension member
will use approx 125b *plus* the size of any member properties you have
defined, and on large dimensions you need to be careful not to waste memory
- Use the smallest data type possible whenever you can, eg for keys
You can see a more detailed giude to how dimension memory is used in this
post, from earlier in the year:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=e...
SsBHA.2660%40tkmsftngp05&rnum=1&prev=/groups%3Fq%3DMosha%2Bdimension%2Bsize%
2Bestimate%2Bgroup:microsoft.public.sqlserver.olap%26hl%3Den%26lr%3D%26ie%3D
UTF-8%26oe%3DUTF-8%26selm%3DeETIR%2524SsBHA.2660%2540tkmsftngp05%26rnum%3D1
Also, are you sure you're not hitting the 64000 maximum number of children
for one member? If so, then you can get around it by creating member
groups - see the topic 'Member Groups' in BOL.
Finally, the obvious question to ask before you go to all the effort of
creating such a large dimension is - do your users really need to see the
whole of the dimension? For example, if your 4 million members are all
customers, is it likely that they need to view data at the customer level in
the cube? They may be happy with data at a higher level, and then maybe
using drillthrough to see the detail data.
HTH,
Chris
--
Chris Webb
Consultant, Microsoft Services Switzerland
(to email: remove 'online.' from the address this was posted with)
Microsoft Services Switzerland and our partners can help you with your
Analysis Services project!
Disclaimer: This posting is provided 'AS IS' with no warranties, and confers
no rights.
Quote:> I am loading a cube and its shared dimensions currently. I have a file
> which has about 4 million records for a dimension it is a WBS Element
> dimensions appears Analysis Server is complaining that I have exceeded
> the maximum number of dimensions.
> How many dimensions records can I load max into a SQL Server
> dimension.
> Appears the select distinct count started and appeared it counted
> until 98,000 or something of that sort and it abends stating maximum
> number of dimensions exceeded. Any idea how to fix this I have quite a
> few such large dimensions that I need to use.
> Thanks
> Simon
by