Need Help with the Analsis Manager

[SpiroT]

Hi,

Can someone tell me why I get the following error message when I try using
the Analsis Manager to extend the 'dimensions' of an OLAP cube:

“Unable to count the members of the ‘Project Enterprise Text_#.
Unable to open the record set.
Error: the text, ntext, and image data types are invalid in the subquery or
aggregate expression."

/Spiro.

 

[Guidho]

Are you trying to add dimensions to the EPM OLAP cube?
This cannot be done using the Analysis Manager.
The OLAP cube, and it's dimensions are build programmatically, everytime
the cube is build.

To extend the cube with extra dimensions, you have following options:
- use Project or Resource outline codes for the data you want to appear
in the cube. Project and Resource outline codes are automatically added
to OLAP
- If you need other data in the cube, you need to extend the cube by
programming a breakout dll. This requires some serious programming.

Hope this helps,
Guidho

 

[SpiroT]

Hi Guidho,

But I can do it for measures! Why would dimensions be any different?

/Spiro.

 

[Guidho]

So you added measures to the OLAP cube using Analysis Manager? And where
do you get the data from?
You should also add measures by extending the cube programmatically
(programming a breakout dll)

Building an OLAP cube fron within projectserver is a 2 step process. The
cube is not populated with data from the database:

In a first step, staging tables are build. Staging tables contain
denormalized data, build from the EPM database.
In a second step, the OLAP cube is build.

You can find more information on how to extend the OLAP cube in the
Project Server SDK.

If you add measures/dimensions using Analysis Manager, you might loose
these changes, as the cube is build programmatically and on the next
run, your changes might be lost.

Hope this helps,
Guidho

 

[SpiroT]

You are partly right in your options...

The custom data was setup in MS Project Server via conventional means (Ent.
Global Template), and thus readily accessible to the Analysis Manager via the
SQL Database. Once you know how to do this, it is "cinch"! I end up with an
extended OLAP cube. Works great! Unfortunately, my procedures for extending
dimensions is buggered up somewhere and I'm trying to find someone who knows
what I'm talking about. Note, I'm not trying to extend the cube with task
level data; there you are right in offering up the options that you do.

Thanks just the same,

/Spiro.
==========

 

[Guidho]

All I was mentioning, is that your method is not a 'supported' scenario.
And that the only recommended scenario is by extending the OLAP using a
breakout dll, even though your method might work for your purpose.

I just reread your initial question... and you might try this:
Cast all text/ntext data to varchar, as text/ntext fields cannot be used
in aggregate functions, groupings, ...

So wherever you have a text/ntext field in your query, use CAST([your
ntext field] AS VARCHAR(255))

Hope this helps,
Guidho

 

[SpiroT]

thank you.

All I was mentioning, is that your method is not a 'supported' scenario.
And that the only recommended scenario is by extending the OLAP using a
breakout dll, even though your method might work for your purpose.

I just reread your initial question... and you might try this:
Cast all text/ntext data to varchar, as text/ntext fields cannot be used
in aggregate functions, groupings, ...

So wherever you have a text/ntext field in your query, use CAST([your
ntext field] AS VARCHAR(255))

Hope this helps,
Guidho

You are partly right in your options...

The custom data was setup in MS Project Server via conventional means (Ent.
Global Template), and thus readily accessible to the Analysis Manager via the
SQL Database. Once you know how to do this, it is "cinch"! I end up with an
extended OLAP cube. Works great! Unfortunately, my procedures for extending
dimensions is buggered up somewhere and I'm trying to find someone who knows
what I'm talking about. Note, I'm not trying to extend the cube with task
level data; there you are right in offering up the options that you do.

Thanks just the same,

/Spiro.
==========