Hello Everyone,

I am working with a MS Project Server 2003 database.

There is a standard table in the databse that includes a field
ProjectEnterpriseText1 that we have populated with the name of the project
sponsor.

I am trying to add a new Dimension called Sponsor so that my users will be
able to use it to sort project data. The table is visable in the Cube Editor.
And, I can see the ProjectEnterpriseText1 field. However when I select it, an
Analysis Manager window pops up with the following messages:

Unable to count the members of the Project Enterprise Text1 level.
Unable to open the record set.
Error: The text, ntext, and image data types are invalid in this subquery
or aggregate expression.

I would presume that this means I can't create a dimension based on a text
field. Is this correct?

Can anyone suggest another way to do this?

Bob Segrest, PMP
BSegE LLC
(540) 937-5875
http://www.BSegE.com

you need to give some sample data from this text field; what exactly are you
trying to do?

You could create a view, where I think that you can easily cast the first
1000 characters from a text field.. but that would be pretty inefficient.

but what you're really looking for is some way to INDEX (_NOT_ sql index--
grab each distinct word (delimited by space) and allow people to search for
strings within this text)

it is doable.

look for a function (UDF) called Split-- this'll let you to build a
FREE-TEXT type index where you can warehouse these text fields in order to
make it searchable using olap

then you could make a report that would say 'what records have this value in
thier text fields'?

you are most likely going to have a VLDM, but if you manage it correctly; it
is totally doable.

I woudl start (once you understand how many distinct keywords you have) I
would start by developing a heirarchization strategy in order to keep these
in 64k buckets.

in other words; you start with a level for the first 2 letters; and then the
next 4 letters; and then the whole word.

depending on the consisitency of you keywords, you can tweak the levelling
to make sure you stay within 64k buckets.

A
Adam
Aaron
Alphabet
B
Bob
Billy
Bible
C
Cat
Cradle

etc... (this is just a lil example-- yours will most likely be a lot more
complicated)




"Bob Segrest" <Bob.Segrest (AT) BSegE (DOT) com> wrote