Enterprise Project Code values as an OLAP Dimension

[Trevis]

Is there any way of mapping an output value from a Enterprise Project Code
formula to an OLAP cube dimension. I know that OLAP Cube Dimensions are
normally only available for Enterprise Codes with look-up tables. However, I
need to use formula created data in an Analysis View - Does someone know a
way around this or do I have to an SSRS solution?

 

[Jonathan Sofer]

I am assuming you are using PS 2003 because calculated fields are now
available in the cubes in PS 2007. Other than that or using SSRS I do know.

Jonathan

 

[Paul Conroy]

Both 2003 and 2007 offer the ability to create calculated measures in Data
Analysis views. As Jonathan says in 2007 you can apply MDX expressions
directly into the cube build from PWA cube build settings.

 

[Trevis]

Thanks for the response using MDX. Unfortunately the output of formula in the
Enterprise Project Code is a text string which we need to use as a pivot
table field. So using an MDX calculated measure will not provide the
solution, unless you have any other ideas?

 

[Trevis]

Thanks for the response using MDX. Unfortunately the output of formula in the
Enterprise Project Code is a text string, which we need to use as a pivot
table field. So using an MDX calculated measure will not provide the
solution, unless you have any other ideas?

 

[Gary L. Chefetz [MVP]]

Trevis:

Assuming that you are using the formula to perform string manipulation to
set a value for a code from a known set of possible values, then what might
work for you is to run a macro to perform the calculation and set an
enterprise custom field value with the result where the enterprise custom
field is attached to a value list of all the know values. Does this make any
sense? Hope so.

--

Gary L. Chefetz, MVP
MSProjectExperts
For Project Server Consulting: http://www.msprojectexperts.com
For Project Server FAQS: http://www.projectserverexperts.com

 

[Trevis]

Gary thanks for your help - yes it makes sense.
Let me expalain - we have adopted a strict naming convention for the project
file and I have developed code to extrapolate five Enterprise Codes from the
Project Name. We need these five codes populated for report filtering /
grouping purposes and future reporting developments. Your idea would be ok
for four of the variables as these are more or less fixed, and can be held
and maintained in a lookup table, however, one is for a SAP ID and is
infinitely variable, consequently this would mean maintaining the lookup
table, which is in some ways defeating the object of the exercise. So maybe I
will have to think again about this automated method of E Code population

 

[Gary L. Chefetz [MVP]]

Trevis:

There is no way on earth that you would ever want the SAP code to become a
dimension. This is a unique ID and not at all a candidate for dimensioning
data. This is a fact table candidate. By the way, you could also use a
server-side event to handle this rather than a client-side macro.

--

Gary L. Chefetz, MVP
MSProjectExperts
For Project Server Consulting: http://www.msprojectexperts.com
For Project Server FAQS: http://www.projectserverexperts.com

Gary thanks for your help - yes it makes sense.
Let me expalain - we have adopted a strict naming convention for the
project
file and I have developed code to extrapolate five Enterprise Codes from
the
Project Name. We need these five codes populated for report filtering /
grouping purposes and future reporting developments. Your idea would be ok
for four of the variables as these are more or less fixed, and can be held
and maintained in a lookup table, however, one is for a SAP ID and is
infinitely variable, consequently this would mean maintaining the lookup
table, which is in some ways defeating the object of the exercise. So
maybe I
will have to think again about this automated method of E Code population