OLAP cube - formula length

Hi

dear all,

does anybody know the maximum length for an mdx formula for a calculated member in SSAS?

Kind regards

Frederikm

No hard limit in SSAS 2005.|||

Hi

i find this kind of hard to believe...

I have a formula with over 4000 characters... processing this cube is impossible

the formula is a formula in the form of iif(errorcheck, 0, iif(errorcheck,0... etc

if i use the basic formula without the iif's the cube processes without any problems....

Kind regards

|||There is a syntax error in your formula somewhere, and the error message should give you an idea about location of this error. The syntax error is the real cause of why your cube doesn't deploy, not the length of the formula.|||

Hi

about the max length: is this tied to any service pack version? we are using sp1
also the thing is that there is no error in the formula,
for when we use it in a normal mdx query it works without any problem...
only when in the cube things go wrong

|||The fact that it works inside MDX query means that there is no problem with the length. What is the exact error message you are getting when you are trying to deploy cube with such calculated member ?

OLAP cube - errorhandling

Dear all

i have inherited a SSAS 2k5 cube with a large number of calculated members.

I have a number of problems:

sometimes the calculated amounts are very very small like: 0.00000000000034.

This however, does not play nicely with excell... heximal display and so on. anyone know of a way to show these as being 0?

secondly

I have a number of calculated members who themselves contain calculated measures.

When one of their parents is 0, they resort into div/0 erros, etc

For my current project it is unacceptable that these errors are visible for the end user

is there any way that i can catch all the errors at once? or do in eed to include iif statements in the calculations of each calculated member?

Hi, unfortunately you need to put IIF statements around them. For example direct queries from the cube.

with

member measures.test as

0.000000003

member measures.test2 as

IIF(measures.test<0.00001,0,measures.test)

member measures.test3 as

0

member measures.test4 as

IIF(measures.test3=0,0,measures.test/measures.test3)

select {measures.test2,measures.test4} on 0 from [mycube]

or calculated members on the cube

CREATE MEMBER CURRENTCUBE.[MEASURES].[PercentUnavailable] as

IIF([Measures].[Total Count]=0,0,[Measures].[Total Unavailable])*100/[Measures].[Total Count]),

visible=1;

John

|||

Alternative which will perform better in cases where you have a large number of these is to use the format statement

member measures.test as

0.000000003,format='#.00'

OLAP calculated membres and Report Model/Builder

My question is related to Report Builder but my data model is build using OLAP database so I am posting my question in OLAP group in case this issues is related to OALP side.

I have OLAP database. There are 5 cubes and one linked cube. My one linked cube has mapped all 5 cubes (like virtual cube in AS 2000) I am using AS 2005.

In my linked cube I have calculated members. I created a Report model using my olap database in reporting services.

In report builder I am not able to see my calculated members when I select linked cube. Is there any thing I am missing?

Thanks for help - Ashok

I think this may be related to whether the calculations are associated with a measure group. The Report Model only seems to list measures that are associated with a measure group. Try going into the calculations tab for your cube clicking on the calculation properties button (looks like a little window with a yellow hand over it) as associating your calculations with a measure group. Unfortunately I think you will need to delete and re-generation your report model.

OLAP 2005 Calculated Measure using Field/Dimension

I am building an OLAP Cube off someone else's data model, so i am a bit stuck trying to decision off model decisions already made and being used.

They have a field for Satisfaction which has the value Yes, No, and Blank when no survey result was received.

I need to make a satisfaction percent based on (count of Yes) / (count of Yes + count of No). So i do not count blank when no result was received.

I need the caculated measure to work against all of the dimensions, so i do not want to build such a specific MDX code that it only works with Time, for example.

Help will be extremely Appreciated

*A

If there is a Satisfaction dimension/attribute, with the members above: Yes, No, and Blank, and a cube "count" measure like SurveyCount, then does something like this work:

([Measures].[SurveyCount], [Satisafaction].[Satisfaction].[Yes])/

(([Measures].[SurveyCount], [Satisafaction].[Satisfaction].[Yes])

+ ([Measures].[SurveyCount], [Satisafaction].[Satisfaction].[No]))

|||WORKED GREAT AND WAS REALLY EASY TO UNDERSTAND AND CHANGE FOR OTHER USES!!!!!!!sql

OLAP 2005 Calculated Measure using Field/Dimension

I am building an OLAP Cube off someone else's data model, so i am a bit stuck trying to decision off model decisions already made and being used.

They have a field for Satisfaction which has the value Yes, No, and Blank when no survey result was received.

I need to make a satisfaction percent based on (count of Yes) / (count of Yes + count of No). So i do not count blank when no result was received.

I need the caculated measure to work against all of the dimensions, so i do not want to build such a specific MDX code that it only works with Time, for example.

Help will be extremely Appreciated

*A

If there is a Satisfaction dimension/attribute, with the members above: Yes, No, and Blank, and a cube "count" measure like SurveyCount, then does something like this work:

([Measures].[SurveyCount], [Satisafaction].[Satisfaction].[Yes])/

(([Measures].[SurveyCount], [Satisafaction].[Satisfaction].[Yes])

+ ([Measures].[SurveyCount], [Satisafaction].[Satisfaction].[No]))

|||WORKED GREAT AND WAS REALLY EASY TO UNDERSTAND AND CHANGE FOR OTHER USES!!!!!!!