B

When I'm viewing a measure spliced by all the members of a particular dimension, I get an error in regards to the member name's characters.

Here's how the error appears in the different clients -

SQL BI Studio:

TITLE: Microsoft Visual Studio

A list of actions for the selected items cannot be obtained because of the following error.
XML parsing failed at line 12, column 62: Illegal xml character.
.
This error may have occurred because the definition for at least one action is not valid. Verify the definition for each action using the Actions view.

ADDITIONAL INFORMATION:

XML parsing failed at line 12, column 62: Illegal xml character.
. (Microsoft SQL Server 2005 Analysis Services)

Reporting Service's VS query builder complains:
Error:
' ', hexadecimal value 0x08, is an invalid character. Line 1, position 94333. (System.Xml)

In SQL Management Studio:
When expanding the dimension's children, a message yields "Error occured

retrieving child nodes, The server sent an unrecognizable response"

3 free bugs for any SDETs out there :-)
Are there any known work arounds? What's the best way to filter out these characters (and which characters to we need to filter?)

Probably your dimension table contains invalid XML character as part of member name or key. You can go to the attribute which causes this problem and change the bindings on the NameColumn or KeyColumn (depending which one caused the problem) for InvalidXmlCharacters from the default Preserve to Remove or Replace. This will slow down processing of the attribute a little bit, but it should solve the problem.

HTH,

Mosha (http://www.mosha.com/msolap)

AVG Measure

In AS2005 how i make a measure with tha aggregate function avg?

Thanks

Try using AverageOfChildren aggegate function and see if it works for you.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

i have tried the AverageOfChildre but i get an error because i'm using the standar edition...

I can't believe there is an easy way to get an avg measure :(

|||

One easy way I have found to get and average is to create a grain count measure and then sum up the measure you would like to average and then create a calculated member that takes the sum of the measure and then divides by the grain count.

HTH

Mark

http://mgarner.wordpress.com

|||

I create a measure SumNumber with the sum of the field, and another measure CountNumber with the count of the field. Then i create a calculated member like this:

In the expression i write: SumNumber/CountNumber and i get an error what i am doing wrong?

|||

I am new using AS2005, i don't know how to make a calculated member.. what i am doing is making a new named calculation... is the same thing?

|||

Javier,

To create a new calculated member, go to the calculations tab in your cube in BI Studio and then do one of two things, click on the "New Calculated Member" button on the tool bar. It looks like a calculator - or you can go to the menu and select "Cube", "New Calculated Member".

Once you have done that, you should put the name at the top in the box labeled "Name". (Average). Then in the "Expression" box, put the calculation. What you have looks correct, although it would probably throw an error if you divide by 0.

I would suggest getting your cube created w/o the calculated member and then pull both of your measures (count and sum) on to a pivot table and make sure they look ok and that the count won't be 0.

HTH

Mark

http://mgarner.wordpress.com

|||Thanks!!! now.. i can have a calculated member in a column of the drilldown?

AVG MDX Function

I have been trying to solve a calculated measure using both the sum/count methods and the avg function, but neither is working for the result that I want. We are trying to come up with an average course score based on an average test score. Example: user takes two tests (test1 twice for 50% and 100% - avg = 75% test2 once for 100%) and the course average would be (75+100)/2 = 87.5%. In our MDX we are getting (50+100+100)/3 = 83.33% instead.

We do have a hierarchy set based on course-->test-->test iteration where test iteration is each individual score. The overall average works the way you would typically thing with the sum/count method, but not based on how we want it to calculate AVG(AVG(test1 scores) + AVG(Test2 scorces)). I tried to use the AVG function, but this only works at the lowest level in the hierarchy and then appears to SUM up the results as you go up into the hierarchy (avg test1 75% test 2 100% course 175% -- not sure why the AVG works that way).

Can anyone tell me if this is even possible to get the average the way I am proposing or do I need to create some type of an aggregated take will calculations in the data warehouse to reference.

Thanks.

Seems like what you need to do is to define formula at the Test level to be Avg(Test.Children, Measures.CurrentMember), where at TestIteration level it will be Sum/Count formula. This will give you the results you are looking for.|||

I really appreciate your response and it is an honor to have you reply to this thread. I am sorry to sound stupid, but how do I define the calculations at the different levels and I am a bit confused with the Measures.CurrentMember. I guess I don't see how the testiteration level formula in the hierarchy gets passed to the other formula.

Thanks in advance.

|||

Here is a piece of MDX script which should do it:

CREATE AvgGrade = Measures.SumGrade / Measures.CountGrade;

(Test.Test.Test.MEMBERS, AvgGrade) = AVG(EXISTING Test.TestIteration.TestIteration.MEMBERS, AvgGrade);

|||

Thanks again for the quick response and I really appreciate you taking the time to assist me with this calculation. I just want to make sure I am doing this correctly. Here is what I put in the script editor:

CREATE AvgScore = Measures.[Test Scores] / Measures.[Completed Test Count];

([Fact Test Instance].[Hierarchy].[Course].[Test].Members, AvgScore) =

AVG(EXISTING [Fact Test Instance].[Hierarchy].[Course].[Test].[Iteration].Members, AvgScore)

We are currently using a hierarchy called [Hierarchy] (made up of Course-->Test-->Iteration) created off of a fact called [Fact Test Instance]. When I deploy this and drilldown to the test level I get a #VALUE!, so I am assuming I am doing something incorrectly. The other levels show the AvgScore result. Is there something wrong with my expression or does it have something to do with the solve order?

|||

The [Fact Test Instance].[Hierarchy].[Course].[Test] piece looks too long - there should be 3 parts - dimension, hierarchy, level and you have four parts. Is [Fact Test Instance] name of the dimension ? Then it should be

([Fact Test Instance].[Test].[Test].Members, AvgScore) = AVG(EXISTING [Fact Test Instance].[Iteration].[Iteration].Members, AvgScore);

Or another way to write it is

([Fact Test Instance].[Hierarchy].[Test].Members, AvgScore) = AVG([Fact Test Instance].[Hierarchy].CurrentMember.Children, AvgScore);

You will also need to do something similar about Course level too.

|||

Thanks again for your response and assistance. I modified the script to be the same as your second option and I am still getting the overall average at the test level when I browse the data within the BIDS browser using the AvgScore measure and the hierarchy called [Hierarchy] within the [Fact Test Instance] dimension. I have a student that has taken two different tests. Test1 taken twice - 33.33% and 100% for AVG 66.67% ; Test2 taken once - 90% for AVG 90% and the overall average would be (33.33+100+90)/3 = 74.44% and we would still like to get (66.67+90)/2 = 78.33%.

This definitely sounds like it should work, so is there something that I am missing or how can I validate that it is actually using this calculation at this specified level?

|||

From your description it sounds like you are looking above Test level, i.e. at Course level, since you want to see aggregate of two different tests. So you will also need

(AvgScore, [Fact Test Instance].[Hierarchy].Course.MEMBERS) = AVG([Fact Test Instance].[Hierarchy].Children, AvgScore);

|||

Once again I want to thank you so much for your assistance and you are absolutely right. I put in the additional script:

([Fact Test Instance].[Hierarchy].Course.MEMBERS, AvgScore) = AVG([Fact Test Instance].[Hierarchy].Children, AvgScore);

and I am now seeing the result that we are looking for. I am assuming that I simply have to repeat this up the hierarchy to keep this working this way. I really appreciate you taking the time to help me out with this issue.

AVG MDX Function

I have been trying to solve a calculated measure using both the sum/count methods and the avg function, but neither is working for the result that I want. We are trying to come up with an average course score based on an average test score. Example: user takes two tests (test1 twice for 50% and 100% - avg = 75% test2 once for 100%) and the course average would be (75+100)/2 = 87.5%. In our MDX we are getting (50+100+100)/3 = 83.33% instead.

We do have a hierarchy set based on course-->test-->test iteration where test iteration is each individual score. The overall average works the way you would typically thing with the sum/count method, but not based on how we want it to calculate AVG(AVG(test1 scores) + AVG(Test2 scorces)). I tried to use the AVG function, but this only works at the lowest level in the hierarchy and then appears to SUM up the results as you go up into the hierarchy (avg test1 75% test 2 100% course 175% -- not sure why the AVG works that way).

Can anyone tell me if this is even possible to get the average the way I am proposing or do I need to create some type of an aggregated take will calculations in the data warehouse to reference.

Thanks.

Seems like what you need to do is to define formula at the Test level to be Avg(Test.Children, Measures.CurrentMember), where at TestIteration level it will be Sum/Count formula. This will give you the results you are looking for.|||

I really appreciate your response and it is an honor to have you reply to this thread. I am sorry to sound stupid, but how do I define the calculations at the different levels and I am a bit confused with the Measures.CurrentMember. I guess I don't see how the testiteration level formula in the hierarchy gets passed to the other formula.

Thanks in advance.

|||

Here is a piece of MDX script which should do it:

CREATE AvgGrade = Measures.SumGrade / Measures.CountGrade;

(Test.Test.Test.MEMBERS, AvgGrade) = AVG(EXISTING Test.TestIteration.TestIteration.MEMBERS, AvgGrade);

|||

Thanks again for the quick response and I really appreciate you taking the time to assist me with this calculation. I just want to make sure I am doing this correctly. Here is what I put in the script editor:

CREATE AvgScore = Measures.[Test Scores] / Measures.[Completed Test Count];

([Fact Test Instance].[Hierarchy].[Course].[Test].Members, AvgScore) =

AVG(EXISTING [Fact Test Instance].[Hierarchy].[Course].[Test].[Iteration].Members, AvgScore)

We are currently using a hierarchy called [Hierarchy] (made up of Course-->Test-->Iteration) created off of a fact called [Fact Test Instance]. When I deploy this and drilldown to the test level I get a #VALUE!, so I am assuming I am doing something incorrectly. The other levels show the AvgScore result. Is there something wrong with my expression or does it have something to do with the solve order?

|||

The [Fact Test Instance].[Hierarchy].[Course].[Test] piece looks too long - there should be 3 parts - dimension, hierarchy, level and you have four parts. Is [Fact Test Instance] name of the dimension ? Then it should be

([Fact Test Instance].[Test].[Test].Members, AvgScore) = AVG(EXISTING [Fact Test Instance].[Iteration].[Iteration].Members, AvgScore);

Or another way to write it is

([Fact Test Instance].[Hierarchy].[Test].Members, AvgScore) = AVG([Fact Test Instance].[Hierarchy].CurrentMember.Children, AvgScore);

You will also need to do something similar about Course level too.

|||

Thanks again for your response and assistance. I modified the script to be the same as your second option and I am still getting the overall average at the test level when I browse the data within the BIDS browser using the AvgScore measure and the hierarchy called [Hierarchy] within the [Fact Test Instance] dimension. I have a student that has taken two different tests. Test1 taken twice - 33.33% and 100% for AVG 66.67% ; Test2 taken once - 90% for AVG 90% and the overall average would be (33.33+100+90)/3 = 74.44% and we would still like to get (66.67+90)/2 = 78.33%.

This definitely sounds like it should work, so is there something that I am missing or how can I validate that it is actually using this calculation at this specified level?

|||

From your description it sounds like you are looking above Test level, i.e. at Course level, since you want to see aggregate of two different tests. So you will also need

(AvgScore, [Fact Test Instance].[Hierarchy].Course.MEMBERS) = AVG([Fact Test Instance].[Hierarchy].Children, AvgScore);

|||

Once again I want to thank you so much for your assistance and you are absolutely right. I put in the additional script:

([Fact Test Instance].[Hierarchy].Course.MEMBERS, AvgScore) = AVG([Fact Test Instance].[Hierarchy].Children, AvgScore);

and I am now seeing the result that we are looking for. I am assuming that I simply have to repeat this up the hierarchy to keep this working this way. I really appreciate you taking the time to help me out with this issue.

averageofChildren vs avg()/count()

hi,

which is better to use: averageofchildren or avg()/count() calculated measure, if i don't want to calculate average over time.

i have read in the "Analysis Services Performance Guide" that is better to avoid using semi-additive functions to improve performance.

Special aggregate functions require unique performance-tuning techniques. They

include DistinctCount and a collection of semiadditive aggregate functions. The

semiadditive functions include: FirstChild, LastChild, FirstNonEmpty,

LastNonEmpty, ByAccount, and AverageOfChildren.

thanks

Christina

Probably the normal average (sum/count) is the better choice if you won't consider time. AverageOfChildren requires a time dimension and Enterprise Edition.|||

i have both time dimension and enterprise edition.

but my question is, i have lots of measures in which i will be using the average. i would like to know if i use averageofchildren is convenient or better use the other way, in terms of performance.

thanks

|||

Now I am not answering your question again, but.. since the average of sum/count will be a calculated member, it will take zero processing time for the calculation, but the sum and count will take processing time (both probably much faster than AverageOfChildren). However, it will take time to calculate the value when a user executes the query. AvaregeOfChildren is used for measures and therefore will take some time to process, but then zero extra time for queries (if aggregated).

How they compare (process sum+count + calculate average vs just process) I don't know, but you can probably test this on your specific system. In general using measures is better than calculated members (faster for end users, but as said, that depends on how long it takes to process the measure vs calculate the member).

Cheers

|||

Thanks HappyCow.

i think i got the point. I'll go for averageofChildren and test accordingly. if i find it slow, will try the other way.

Hope you remain always happy..but not a cow.

Cheers

AverageByChildren Function on a measure from a Fact and Dimension Table

Hi
I have a FactVendorPerformance table where in I have the CustomerID and the VendorID i.e. the Vendor who has serviced the Customer along with other details relating to the service quality of the vendor. I have also created KPIs in my cube for vendor service quality. I wanted a hierarchy - Customer-Vendor to filter the service quality KPIs since one customer could have been serviced my multiple vendors at different instances.
When I had the FactVendorPerformance table as only a FactTable, I was unable to create a Customer-Vendor hierarchy in it since it was not a dimension. So I made the FactVendorPerformance both a Dimension as well as a Fact table. However the measures have Sum as the default aggregate funtion. I changed this to AverageByChildren in the measure of FactVendorPerformance table. But this is not gettting reflected. Is there anything wrong in what I am doing? Is there any better way to implement the same?I want the Customer - Vendor hierarchy to filter the KPIs.

Thanks and Regards
Guruprasad Karnik

One customer could be serviced by multiple vendors - but, conversely, couldn't one vendor also service multiple customers? From a modelling perspective, customer and vendor would typically be separate dimensions - though they could be "stacked" for the purposes of reporting.

As far as the aggregate function, it's not clear how many fact records could exist for each combination of customer and vendor; but if you need to average a field value across all applicable fact records, you might try a calculated average wher you divide a "sum" aggregation on that value by a "count" measure for the same fact table/measure group.

|||Hi Deepak
Thanks for the reply:)! I did realise this Sum/Count workaround yesterday after going through some forums out here since AverageByChildren seems to average on only time and implemented the same. In my cube I do have Customer and Vendor as different dimensions. However the join or a relationship between the 2 exists only in the FactVendorPerformance table and all vendors need not have serviced all customers and conversly all customers need not have been serviced by all the vendors. It would be more meaningful to have a hierarchy in either ways (Customer-Vendor or Vendor-Customer) to filter the KPIs rather than having 2 different filters configured to 2 different dimensions. As you mentioned, conversely it can be a Vendor-Customer hierarchy as well. But the question is that since the relationship is existing only in the FactVendorPerformance table I had to make it a dimension table to get the hierarchy. I wanted to know as whether I am right in my approach. Is there any better way of designing or modeling the same?

Thanks and Regards
Guruprasad Karnik|||Not sure why you need to present 2 dimensions in a single hierarchy to users - what is the OLAP client tool? For example, with Reporting Services, if you wish to limit the selection list for vendors to those servicing a specific customer, you could use cascading parameters, where user selection of customer dynamically filters the selection list of vendors. In other words, can't you handle this navigation in the client, rather than in the cube?|||I am using MOSS 2007 KPI Lists to display the KPIs with the SSAS filter web part configured to the hierarchy and connected to the KPI list. If it was reporting services, then yes I could have had cascading parameters. But in MOSS 2007 I am not sure whether you can achieve this by interconnecting the filter web parts to have cascading parameters

AverageByChildren Function on a measure from a Fact and Dimension Table

Hi
I have a FactVendorPerformance table where in I have the CustomerID and the VendorID i.e. the Vendor who has serviced the Customer along with other details relating to the service quality of the vendor. I have also created KPIs in my cube for vendor service quality. I wanted a hierarchy - Customer-Vendor to filter the service quality KPIs since one customer could have been serviced my multiple vendors at different instances.
When I had the FactVendorPerformance table as only a FactTable, I was unable to create a Customer-Vendor hierarchy in it since it was not a dimension. So I made the FactVendorPerformance both a Dimension as well as a Fact table. However the measures have Sum as the default aggregate funtion. I changed this to AverageByChildren in the measure of FactVendorPerformance table. But this is not gettting reflected. Is there anything wrong in what I am doing? Is there any better way to implement the same?I want the Customer - Vendor hierarchy to filter the KPIs.

Thanks and Regards
Guruprasad Karnik

One customer could be serviced by multiple vendors - but, conversely, couldn't one vendor also service multiple customers? From a modelling perspective, customer and vendor would typically be separate dimensions - though they could be "stacked" for the purposes of reporting.

As far as the aggregate function, it's not clear how many fact records could exist for each combination of customer and vendor; but if you need to average a field value across all applicable fact records, you might try a calculated average wher you divide a "sum" aggregation on that value by a "count" measure for the same fact table/measure group.

|||Hi Deepak
Thanks for the reply:)! I did realise this Sum/Count workaround yesterday after going through some forums out here since AverageByChildren seems to average on only time and implemented the same. In my cube I do have Customer and Vendor as different dimensions. However the join or a relationship between the 2 exists only in the FactVendorPerformance table and all vendors need not have serviced all customers and conversly all customers need not have been serviced by all the vendors. It would be more meaningful to have a hierarchy in either ways (Customer-Vendor or Vendor-Customer) to filter the KPIs rather than having 2 different filters configured to 2 different dimensions. As you mentioned, conversely it can be a Vendor-Customer hierarchy as well. But the question is that since the relationship is existing only in the FactVendorPerformance table I had to make it a dimension table to get the hierarchy. I wanted to know as whether I am right in my approach. Is there any better way of designing or modeling the same?

Thanks and Regards
Guruprasad Karnik
|||Not sure why you need to present 2 dimensions in a single hierarchy to users - what is the OLAP client tool? For example, with Reporting Services, if you wish to limit the selection list for vendors to those servicing a specific customer, you could use cascading parameters, where user selection of customer dynamically filters the selection list of vendors. In other words, can't you handle this navigation in the client, rather than in the cube?|||I am using MOSS 2007 KPI Lists to display the KPIs with the SSAS filter web part configured to the hierarchy and connected to the KPI list. If it was reporting services, then yes I could have had cascading parameters. But in MOSS 2007 I am not sure whether you can achieve this by interconnecting the filter web parts to have cascading parameters

Average Over Time

Hi all,

I'm new to MDX and trying to accomplish the following.

I have a measure named [Bezetting] which comes from the following fact table:

OC_Date

OC_Category

OC_Number

I have 2 dimensions: Categories and Time.

Now I want to have a average over time measure like the one which is included in Enterprise edition (we only have the standard edition). I read you can do that with a calculated member.

I tried like this:

CALCULATE;

CREATE member CURRENTCUBE.[MEASURES].[GemBez] AS

AVG(Descendants([Time].CurrentMember,[Time].Levels.Count-[Time].CurrentMember.Level.Ordinal,LEAVES), Measures.[Bezetting])

But that give me VALUE! instead of the averages in the browser.

Can anyone help me out?

TIA!!!

Stijn Verrept.

Try This:

Code Snippet

WITH MEMBER [Measure].[MyAVG1] AS

'AVG(

YTD([Time].[CurrentMember],

[Measure].[Bezetting]

)'

OR

Code Snippet

WITH MEMBER [Measure].[MyAVG2] AS

'AVG(

YTD(),

[Measures].[Bezetting]

)'

Helped?

Regards!|||helped?|||

No PedroCGD,

I tried your solution but the results I got were no averages.

I finally solved it like this: I first created a daycount measure:

Code Snippet

Count(Descendants([Time].[Year - Quarter - Month - Date], [Time].[Year - Quarter - Month - Date].[Date]))

And then the average of bezetting got easy:

Code Snippet

IIF([Measures].[Bezetting] = 0,NULL,

[Measures].[Bezetting]/[Measures].[DayCount])

Thanks for your reply though!|||

I'm happy you get it!!

Mark your question as resolved to other people know!

Regards!!!

See you!

|||

The parameters to the descendants call do not look valid. The first call to .currentMember is missing a hierarchy reference and the second parameter to Level.Ordinal is redundant when using the leaves flag.

If you had a "Calendar" hierarchy it should look something like this:

CREATE member CURRENTCUBE.[MEASURES].[GemBez] AS

AVG(Descendants([Time].Calendar.CurrentMember,,LEAVES), Measures.[Bezetting])

|||

Dear Darren,

Thanks for the reply, tried your solution but I got the same values as Pedro's. So maybe Pedro's solution was correct after all, I'll check out some more tomorrow, 3am here, gotta go to bed, not thinking clear anymore

Edit: ok checked some more. Solution of Pedro is giving me other values back. Your solution gives me the same, I also adjusted the DayCount script like this:

Code Snippet

Count(Descendants([Time].[Year - Quarter - Month - Date],, leaves))

According to your example (I'm new at MDX) and it still works the same so I'll leave it like this, looks better! I use the daycount measure in other calculations as well.

|||

If you use the DayCount measure in other places you might get some performance benefits from the caching that SSAS does, so this probably a good solution.

Note that you may see a difference between

Measure / DayCount

and

Avg( <setOfDays>, Measure)

Because the Avg function will exclude days where there is not value for the measure, hence dividing the sum of the measure by a lower value.

Pedro's YTD() calculation should return the same value if you are selecting a year, but it should be different if you pick something like "Quarter 2" or a specific month as the YTD function will always return a set of members starting from the first member in a given year.

|||Great! Thanks for info, really usefull. Yes I was trying quarters and months as well.

Average Over Time

Hi all,

I'm new to MDX and trying to accomplish the following.

I have a measure named [Bezetting] which comes from the following fact table:

OC_Date

OC_Category

OC_Number

I have 2 dimensions: Categories and Time.

Now I want to have a average over time measure like the one which is included in Enterprise edition (we only have the standard edition). I read you can do that with a calculated member.

I tried like this:

CALCULATE;

CREATE member CURRENTCUBE.[MEASURES].[GemBez] AS

AVG(Descendants([Time].CurrentMember,[Time].Levels.Count-[Time].CurrentMember.Level.Ordinal,LEAVES), Measures.[Bezetting])

But that give me VALUE! instead of the averages in the browser.

Can anyone help me out?

TIA!!!

Stijn Verrept.

Try This:

Code Snippet

WITH MEMBER [Measure].[MyAVG1] AS

'AVG(

YTD([Time].[CurrentMember],

[Measure].[Bezetting]

)'

OR

Code Snippet

WITH MEMBER [Measure].[MyAVG2] AS

'AVG(

YTD(),

[Measures].[Bezetting]

)'

Helped?

Regards!|||helped?|||

No PedroCGD,

I tried your solution but the results I got were no averages.

I finally solved it like this: I first created a daycount measure:

Code Snippet

Count(Descendants([Time].[Year - Quarter - Month - Date], [Time].[Year - Quarter - Month - Date].[Date]))

And then the average of bezetting got easy:

Code Snippet

IIF([Measures].[Bezetting] = 0,NULL,

[Measures].[Bezetting]/[Measures].[DayCount])

Thanks for your reply though!|||

I'm happy you get it!!

Mark your question as resolved to other people know!

Regards!!!

See you!

|||

The parameters to the descendants call do not look valid. The first call to .currentMember is missing a hierarchy reference and the second parameter to Level.Ordinal is redundant when using the leaves flag.

If you had a "Calendar" hierarchy it should look something like this:

CREATE member CURRENTCUBE.[MEASURES].[GemBez] AS

AVG(Descendants([Time].Calendar.CurrentMember,,LEAVES), Measures.[Bezetting])

|||

Dear Darren,

Thanks for the reply, tried your solution but I got the same values as Pedro's. So maybe Pedro's solution was correct after all, I'll check out some more tomorrow, 3am here, gotta go to bed, not thinking clear anymore

Edit: ok checked some more. Solution of Pedro is giving me other values back. Your solution gives me the same, I also adjusted the DayCount script like this:

Code Snippet

Count(Descendants([Time].[Year - Quarter - Month - Date],, leaves))

According to your example (I'm new at MDX) and it still works the same so I'll leave it like this, looks better! I use the daycount measure in other calculations as well.

|||

If you use the DayCount measure in other places you might get some performance benefits from the caching that SSAS does, so this probably a good solution.

Note that you may see a difference between

Measure / DayCount

and

Avg( <setOfDays>, Measure)

Because the Avg function will exclude days where there is not value for the measure, hence dividing the sum of the measure by a lower value.

Pedro's YTD() calculation should return the same value if you are selecting a year, but it should be different if you pick something like "Quarter 2" or a specific month as the YTD function will always return a set of members starting from the first member in a given year.

|||Great! Thanks for info, really usefull. Yes I was trying quarters and months as well.

average of selected members?

I got a measure caled [number of persons]

Now i want another calculated meassure which shows the average number of persons within the selected time period.

My timedimension is :

[Date].[Period].[Month]

Let's say i have

jan - 1000, feb - 1200, mar - 1100, apr - 1000

So if i select "feb" and "mar" in the cube, my average should show 1150

if i select jan,feb,apr i want my average to show 1066,67

is this possible ?

Assuming that you're using AS 2005, something like:

Avg(Existing [Date].[Period].[Month].Members, [Measures].[number of persons])

|||

Ahh yes... This will work if the selection of members is specified in the WHERE statement of the MDX query. However, it will not work if a sub-select/SUBCUBE is used.

See http://www.sqljunkies.com/WebLog/reckless/archive/2006/03/08/18601.aspx for an elaboration on the issue.

Average aggregation

Hi! experts!

I just want to use the average in our measure group.

Unfortunately, there wasn't average aggreation, except averageOfChildren.

How can I implement the average aggreation?

thanks in advance.

Hi from Brazil,

"averageOfChildren" is same "Average" agregation function.

Note: It run just over SQL Server Enterprise or developer version. Not over Standard Version

Regards!

|||

AverageOfChildren will only average over the time dimension - so a typical approach to average over fact rows is:

Create a measure with "sum" aggregation on the fact table field to be averaged

Create a measure with "count" aggregation and row binding (created by default for a new measure group)

Create a calculated measure like: "average" = "sum" / "count"

Autorefresh in Microsoft Excel 2003 Cubeanalysis

Hi,

I play around with the Cubeanalysis Addin for Excel 2003. My Question: Every time, I drag a dimension, measure, etc. to an excelsheet, all data will be refreshed. This refreshment lasts often up to 1-2 minutes. I didn't need an refreshment at design-time. Is it possible to deactivate it for report design, and activate it when the report is ready for use?

Thanks
Hans

Sorry Hans. This is a wrong forum for this question.

Try posting on the Office forum.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hello Edward,

Thanks for your Information, but I didn't find any Office Forum here at MSDN Forums (?). And Cubanalysis is one of the Analysis Services 2005 Frontends, so I thought, thats the right forum.

Hans

|||

Try looking at the Office developers newsgroups like:

microsoft.public.officedev
microsoft.public.office.developer.vba

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.