Avoiding time-outs

The C++ application calls the database to look up property data. One
troublesome query is a function that returns a table, finding data which
is assembled from four or five tables through a view that has a join,
and then updating the resulting @.table from some other tables. There
are several queries inside the function, which are selected according
to which parameters are supplied (house #, street, zip, or perhaps parcel
number, or house #, street, town, city,...etc.). If a lot of parameters
are provided, and the property is not in the database, then several queries
may be attempted -- it keeps going until it runs out of queries or finds
something. Usually it takes ~1-2 sec for a hit, but maybe a minute in
some failure cases, depending on the distribution of data. (~100 mil
properties in the DB) Some queires operate on the assumption the input data
is slightly faulty, and take relatively a long time, e.g., if WHERE
ZIP=@.Zip fails, we try WHERE ZIP LIKE substring(@.Zip,1,3)+'%'. While
all this is going on the application may decide the DB is never going to
return, and time out; it also seems more likely to throw an exception the
longer it has to wait. Is there a way to cause the DB function to fail if
it takes more than a certain amount of time? I could also recast it as
a procedure, and check the time consumed after every query, and abandon
the search if a certain amount of time has elapsed.

Thanks in advance,
Jim Geissmanjim_geissman@.countrywide.com (Jim Geissman) wrote in message news:<b84bf9dc.0403031505.2838a043@.posting.google.com>...
> The C++ application calls the database to look up property data. One
> troublesome query is a function that returns a table, finding data which
> is assembled from four or five tables through a view that has a join,
> and then updating the resulting @.table from some other tables. There
> are several queries inside the function, which are selected according
> to which parameters are supplied (house #, street, zip, or perhaps parcel
> number, or house #, street, town, city,...etc.). If a lot of parameters
> are provided, and the property is not in the database, then several queries
> may be attempted -- it keeps going until it runs out of queries or finds
> something. Usually it takes ~1-2 sec for a hit, but maybe a minute in
> some failure cases, depending on the distribution of data. (~100 mil
> properties in the DB) Some queires operate on the assumption the input data
> is slightly faulty, and take relatively a long time, e.g., if WHERE
> ZIP=@.Zip fails, we try WHERE ZIP LIKE substring(@.Zip,1,3)+'%'. While
> all this is going on the application may decide the DB is never going to
> return, and time out; it also seems more likely to throw an exception the
> longer it has to wait. Is there a way to cause the DB function to fail if
> it takes more than a certain amount of time? I could also recast it as
> a procedure, and check the time consumed after every query, and abandon
> the search if a certain amount of time has elapsed.
> Thanks in advance,
> Jim Geissman

You don't give any information about your version of MSSQL, and the
client library you're using, but you may be able to set a suitable
timeout period on the client side. Alternatively, look at the "query
governor cost limit Option" in Books Online - this terminates queries
that run for more than a given number of seconds.

Simon|||jim_geissman@.countrywide.com (Jim Geissman) wrote in message news:<b84bf9dc.0403031505.2838a043@.posting.google.com>...
> The C++ application calls the database to look up property data. One
> troublesome query is a function that returns a table, finding data which
> is assembled from four or five tables through a view that has a join,
> and then updating the resulting @.table from some other tables. There
> are several queries inside the function, which are selected according
> to which parameters are supplied (house #, street, zip, or perhaps parcel
> number, or house #, street, town, city,...etc.). If a lot of parameters
> are provided, and the property is not in the database, then several queries
> may be attempted -- it keeps going until it runs out of queries or finds
> something. Usually it takes ~1-2 sec for a hit, but maybe a minute in
> some failure cases, depending on the distribution of data. (~100 mil
> properties in the DB) Some queires operate on the assumption the input data
> is slightly faulty, and take relatively a long time, e.g., if WHERE
> ZIP=@.Zip fails, we try WHERE ZIP LIKE substring(@.Zip,1,3)+'%'. While
> all this is going on the application may decide the DB is never going to
> return, and time out; it also seems more likely to throw an exception the
> longer it has to wait. Is there a way to cause the DB function to fail if
> it takes more than a certain amount of time? I could also recast it as
> a procedure, and check the time consumed after every query, and abandon
> the search if a certain amount of time has elapsed.
> Thanks in advance,
> Jim Geissman

See "remote query timeout Option" in the help text. However, relying
on this may cause inconsistent bahaviour.

This design pattern can also lead to heavy load on your database.

As a suggestion, have two separate sets of queries, one that assumes
good data (should be much quicker which you want to use most times?)
and one that may have incorrect data (will be slower, but not used
very often). In you screen have a checkbox to indicate what search
option to use. Alternatively perform better validation on the data
before submitting the form.|||That sounds interesting. I'll look into it.

> You don't give any information about your version of MSSQL, and the
> client library you're using, but you may be able to set a suitable
> timeout period on the client side. Alternatively, look at the "query
> governor cost limit Option" in Books Online - this terminates queries
> that run for more than a given number of seconds.
> Simon

Avoid to Write the Schema Name in a Function Call in SQL 2005

Avoid Writing the Schema Name in a Function Call in SQL 2005

Hi, everybody, already I try to certificate my code in Oracle PL/SQL to T-SQL to SQL Server 2005, but I have a little trouble, When I Translate the code with SQL Migration Assistant for Oracle (SSMA), this tool, put the schema name before the function name, that is a good practice, but I can avoid to do that, because, The company have a lot of calls in hard-code, and when I execute the call without the schema name, SQL2005 send me a error message, like say, “is not recognize as built-in function name”.

Somebody knows what I can do to do that?

Thanks

KC:

If the function is a scalar function you must precede the function name with the schema; it is a requirement for the way Transact SQL Works.

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.

AVG Function Overflow

Convert the column to BIGINT so you have more range: SELECT AVG(CONVERT(BIGINT, YourColumn)) FROM YourTable -- Adam MachanicSQL Server MVPhttp://www.datamanipulation.net-- <Dennis Jackson@.discussions.microsoft.com> wrote in message news:43ea50a8-ae33-4816-bb70-8935293b923c@.discussions.microsoft.com...encountered on SQL 2005 CTP Sept.When I am using the AVG function of 2 years worth of stock volume data(503 data points), I get a INT buffer overflow. However, the STDEV function continues to work, which I *think* requires you to calculate average first...Any Suggestions on how to work around this?encountered on SQL 2005 CTP Sept.

When I am using the AVG function of 2 years worth of stock volume data(503 data points), I get a INT buffer overflow.

However, the STDEV function continues to work, which I *think* requires you to calculate average first...

Any Suggestions on how to work around this?|||The reason it doesn't work is AVG is computed as SUM/COUNT and not implemented natively. Since SUM returns for any expression of integer category except bigint, it will error out depending on the values. Ideally, we should implement AVG natively to handle the domain of values. For now, workaround is to cast the expression to higher precision data type.|||I already have the variable defined as a 4byte unsigned INT.
I may try to do AVG(column/1000000).. i will see if that will work.|||I assume then that STDEV is implemented as a native function then.
that was the part that wosnt making sense.. you need average to calculate STDEV, and that worked. but the logical intermediate item it couldnt calculate.

Also another wierd thing I ran across. when I tried to create a column that was stdevP(column) when the sql management studio parses it, it keeps converting it to COUNTBig(column) weird.

Do you know if that is a change they are planning to make?

AVG function on an integer column- truncation

When I use the AVG Function on an integer column, the result is truncated

Example:

Select AVG(field1) from table1

Field1 is an int field and has 4 rows with the values 114,115,115 and 115. This will return 114.

I can get the correct result by using the following SELECT:

SELECT CAST(AVG(CAST (field1 as decimal(18,1)))+ .5 as int) from table1

Am I missing something here? Is there an simpler way to do this?

Any help will be appreciated.

Steve D.

You are correct on the problem, but I am certain you can do it a little easier. Try this example:

DECLARE @.v_test TABLE ([num] INT)

INSERT INTO @.v_test VALUES(1)

INSERT INTO @.v_test VALUES(2)

INSERT INTO @.v_test VALUES(2)

INSERT INTO @.v_test VALUES(2)

SELECT AVG(CONVERT(DECIMAL,num))

FROM @.v_Test

|||

The problem is that I need the resultant value to be an integer, not a decimal value. The query from above will return 1.75000.

I need the result back as an integer, which was why I had to cast it back to an integer after adding .5. Just looking for a simpler way to do this

|||

I'm not sure I understand. Would your answer be rounded to 2? Converting it to an INT is what it is doing for you.

You could use this to round it. If you want to recast it to INT, that is possible at this point.

SELECT ROUND(AVG(CONVERT(DECIMAL,num)),0)

|||

OR

SELECT CEILING(AVG(CONVERT(DECIMAL,num))) from yourTable

|||

More info.

I want the Average value to round to the nearest integer.

1.8 would round to 2

1.2 would round to 1

Ceiling looks like it would make 1.2 convert to 2.

Edit-

Tested this further- it looks like ROUND is what I need. It returns a decimal number (ex 1.0000), but I can convert that back to int.

AVG Function

I've imported several reports from Access that I am now trying to convert use
a shared SQL data source. Several of these report show an average value for a
datetime field in SQL, ex. a textbox with a value of =AVG(Fields.T1.Value)
Does anyone have a workaround for RS to be able to average a time value on a
report. Thank youTry this:
=DateTime.FromBinary( Avg( CDate(Fields!T1.Value).Ticks ) )
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"halej51" <halej51@.discussions.microsoft.com> wrote in message
news:0A8D8368-EEC2-46E7-A24A-EB517AA4D8C4@.microsoft.com...
> I've imported several reports from Access that I am now trying to convert
use
> a shared SQL data source. Several of these report show an average value
for a
> datetime field in SQL, ex. a textbox with a value of =AVG(Fields.T1.Value)
> Does anyone have a workaround for RS to be able to average a time value on
a
> report. Thank you|||Thank you Robert,
This command returned an error "FromBinary is not a memvber of Date"
Any help is appreciated, thank you very much.
"Robert Bruckner [MSFT]" wrote:
> Try this:
> =DateTime.FromBinary( Avg( CDate(Fields!T1.Value).Ticks ) )
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "halej51" <halej51@.discussions.microsoft.com> wrote in message
> news:0A8D8368-EEC2-46E7-A24A-EB517AA4D8C4@.microsoft.com...
> > I've imported several reports from Access that I am now trying to convert
> use
> > a shared SQL data source. Several of these report show an average value
> for a
> > datetime field in SQL, ex. a textbox with a value of =AVG(Fields.T1.Value)
> >
> > Does anyone have a workaround for RS to be able to average a time value on
> a
> > report. Thank you
>
>|||Oh, the FromBinary method is only available on .NET 2.0 (and RS 2005).
Try this for RS 2000:
=new DateTime( Avg( CDate(Fields!T1.Value).Ticks ) )
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"halej51" <halej51@.discussions.microsoft.com> wrote in message
news:7629239C-2FDD-4049-84EA-8326EF7299CA@.microsoft.com...
> Thank you Robert,
> This command returned an error "FromBinary is not a memvber of Date"
> Any help is appreciated, thank you very much.
>
>
> "Robert Bruckner [MSFT]" wrote:
> > Try this:
> > =DateTime.FromBinary( Avg( CDate(Fields!T1.Value).Ticks ) )
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> > "halej51" <halej51@.discussions.microsoft.com> wrote in message
> > news:0A8D8368-EEC2-46E7-A24A-EB517AA4D8C4@.microsoft.com...
> > > I've imported several reports from Access that I am now trying to
convert
> > use
> > > a shared SQL data source. Several of these report show an average
value
> > for a
> > > datetime field in SQL, ex. a textbox with a value of
=AVG(Fields.T1.Value)
> > >
> > > Does anyone have a workaround for RS to be able to average a time
value on
> > a
> > > report. Thank you
> >
> >
> >|||Robert,
That was a huge help! Thank you very much. I am getting a #Error in some of
the footer fields using this calculation but I think this might be a data
issue.
Thank you!
"Robert Bruckner [MSFT]" wrote:
> Oh, the FromBinary method is only available on .NET 2.0 (and RS 2005).
> Try this for RS 2000:
> =new DateTime( Avg( CDate(Fields!T1.Value).Ticks ) )
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "halej51" <halej51@.discussions.microsoft.com> wrote in message
> news:7629239C-2FDD-4049-84EA-8326EF7299CA@.microsoft.com...
> > Thank you Robert,
> >
> > This command returned an error "FromBinary is not a memvber of Date"
> >
> > Any help is appreciated, thank you very much.
> >
> >
> >
> >
> > "Robert Bruckner [MSFT]" wrote:
> >
> > > Try this:
> > > =DateTime.FromBinary( Avg( CDate(Fields!T1.Value).Ticks ) )
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > > "halej51" <halej51@.discussions.microsoft.com> wrote in message
> > > news:0A8D8368-EEC2-46E7-A24A-EB517AA4D8C4@.microsoft.com...
> > > > I've imported several reports from Access that I am now trying to
> convert
> > > use
> > > > a shared SQL data source. Several of these report show an average
> value
> > > for a
> > > > datetime field in SQL, ex. a textbox with a value of
> =AVG(Fields.T1.Value)
> > > >
> > > > Does anyone have a workaround for RS to be able to average a time
> value on
> > > a
> > > > report. Thank you
> > >
> > >
> > >
>
>|||Another similar situation which help would be appreciated for.
I have a table with 4 datetime fields, storing only the time. In the report
I need to produce an average of these 4 fields like, (T1.Value + T2.Value +
T3.Value + T4.Value)/4. This obviously throws a similar error.
Any help is appreciated.
"halej51" wrote:
> Robert,
> That was a huge help! Thank you very much. I am getting a #Error in some of
> the footer fields using this calculation but I think this might be a data
> issue.
> Thank you!
>
> "Robert Bruckner [MSFT]" wrote:
> > Oh, the FromBinary method is only available on .NET 2.0 (and RS 2005).
> > Try this for RS 2000:
> > =new DateTime( Avg( CDate(Fields!T1.Value).Ticks ) )
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "halej51" <halej51@.discussions.microsoft.com> wrote in message
> > news:7629239C-2FDD-4049-84EA-8326EF7299CA@.microsoft.com...
> > > Thank you Robert,
> > >
> > > This command returned an error "FromBinary is not a memvber of Date"
> > >
> > > Any help is appreciated, thank you very much.
> > >
> > >
> > >
> > >
> > > "Robert Bruckner [MSFT]" wrote:
> > >
> > > > Try this:
> > > > =DateTime.FromBinary( Avg( CDate(Fields!T1.Value).Ticks ) )
> > > >
> > > > --
> > > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > > >
> > > > "halej51" <halej51@.discussions.microsoft.com> wrote in message
> > > > news:0A8D8368-EEC2-46E7-A24A-EB517AA4D8C4@.microsoft.com...
> > > > > I've imported several reports from Access that I am now trying to
> > convert
> > > > use
> > > > > a shared SQL data source. Several of these report show an average
> > value
> > > > for a
> > > > > datetime field in SQL, ex. a textbox with a value of
> > =AVG(Fields.T1.Value)
> > > > >
> > > > > Does anyone have a workaround for RS to be able to average a time
> > value on
> > > > a
> > > > > report. Thank you
> > > >
> > > >
> > > >
> >
> >
> >

Avg function

The following function returns an average in minutes between to dates.
=Avg(Time.ClsReportUtils.getResponseTime(Fields!CREATED.Value,
Fields!TODO_ACTL_END_DT.Value ))/60
My problem is in figuring out how to truncate decimal places for example the
report returns 36.2795358649783 when all I want is 36.
If any one has any ideas that would be greatly appreciated.
--
kmatth007use format(Avg(Time.ClsReportUtils.getResponseTime(Fields!CREATED.Value,
> Fields!TODO_ACTL_END_DT.Value ))/60,"0")
"kmatth007" wrote:
> The following function returns an average in minutes between to dates.
> =Avg(Time.ClsReportUtils.getResponseTime(Fields!CREATED.Value,
> Fields!TODO_ACTL_END_DT.Value ))/60
> My problem is in figuring out how to truncate decimal places for example the
> report returns 36.2795358649783 when all I want is 36.
> If any one has any ideas that would be greatly appreciated.
> --
> kmatth007|||Thanks! This worked.
kmatth007
"ש×?×?×?" wrote:
> use format(Avg(Time.ClsReportUtils.getResponseTime(Fields!CREATED.Value,
> > Fields!TODO_ACTL_END_DT.Value ))/60,"0")
> "kmatth007" wrote:
> > The following function returns an average in minutes between to dates.
> >
> > =Avg(Time.ClsReportUtils.getResponseTime(Fields!CREATED.Value,
> > Fields!TODO_ACTL_END_DT.Value ))/60
> >
> > My problem is in figuring out how to truncate decimal places for example the
> > report returns 36.2795358649783 when all I want is 36.
> >
> > If any one has any ideas that would be greatly appreciated.
> >
> > --
> > kmatth007

AverageOfChildren Standard Vs Enterprise

Hello,

My question may be a simple one, is there a way to do an aggregate average function without buying enterprise edition. When I try to average a dimension instead of summing (for example a field with Percents) I get a error that the AverageOfChildren feature is not avaible in the Standard version of SQL 2005. Is this really true or did I set something up wrong.

I would have a hard time convincing my company to buy Enterprise edition because basic functionality like averaging is not available in the standard edition... I have found the features by edition page: http://msdn2.microsoft.com/en-us/library/ms143761.aspx however there is nothing on here that tells you simple dimension aggregations are unavailable in the Standard Edition.

Can some one tell me if there is a way to get around this without spending 10G on an upgraded version?

Thanks,
josh1234

It is correct that some aggregation functions are not available in the Standard Edition - specifically the semi-additive ones. For an average, however, you should be able to get around the issue quite easily. Create a measure with aggregation function "Sum" for the measure you want to average. Create another measure (for the same field in the database) with the aggregation function "Count". Now, create a calculated member in the cube, which divides the sum by the count. Remember to check for division by zero:

Example:

CREATE MEMBER CURRENTCUBE.MyAverage AS

IIF([Measures].[MyCount] = 0,

NULL,

[Measures].[MySum]/[Measures].[MyCount],

NON_EMPTY_BEHAVIOR = [Measures].[MyCount];

AverageOfChildren Aggregation function gives error.

Hi

This is a very peculiar problem, and I did not find any entry on the net regarding this.

Background: I have written .Net 2.0 programs that use AMO and create the complete gamut of SSAS 2005 objects at one go. They create Dimensions, Cubes, MeasureGroups, Measures, Partitions....etc. This is working and after processin the objects, they are browsable also.

Now the problem is: When i create a measure, I am not specifying any AggregationFunction (Not even 'None'), but the default aggregation 'SUM' is added automatically.

question 1: Why is this? How to avoid this?

Since I dont want the 'SUM' aggregation for some of the measures, I open BI studio and change one of the measure's aggregation function to 'AverageOfChildren', I get an error as soon as i do this. The measure is now underlined with the curly red (indicating an error). The error msg displayed is: Cube xyz cannot be saved because:

Cube 'xyz' > Measure Group 'abc' > Measure 'def' : Aggregate function AverageOfChildren is not allowed in Standard server edition.

question 2: What is this? Why is this? how to go about further from this?

Is this really because we use the standard server edition? (I dont know which edition we use)

Thanks and Regards

Vijay R

Yes, some functionality is only available in the Enterprise SKU. The BI Studio environment uses a property to allow you to develop targeting a particular SKU (which will change the client-side validation -- the red squigglies). This allows for a visual way to design an Analysis Services database that limits its features to the standard SKU. When the project is deployed, validation is done again by the server.

In general, semi-additive measures are only available in enterprise edition (like AverageOfChildren, LastNonEmpty, FirstNonEmpty and None).

Hope that helps.

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 problem

Hi,
I've a subtotal row in a matrix. I do an average of a column but the avg
function return me the average of all results of each rows. In fact, I want
do the average of each colums except where the results is equal to 0.
How can I do that?Aggregate functions like Sum, Count, Avg, etc. only consider values which
are not null.
Try this:
=Avg(iif(Fields!X.Value = 0, Nothing, Fields!X.Value))
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Seb" <Seb@.discussions.microsoft.com> wrote in message
news:AA16A434-5BB4-4D03-B9E8-4C4D5224BB1D@.microsoft.com...
> Hi,
> I've a subtotal row in a matrix. I do an average of a column but the avg
> function return me the average of all results of each rows. In fact, I
want
> do the average of each colums except where the results is equal to 0.
> How can I do that?

Average In MDX

Hi, Can anyone help me, I'm new in Analysis Services 2005,

how can I get the average, I did not understand the AVG function sintax

any suggestions?

Thanks.

Erika :)

Erika, have you looked at the AVG() function's description and usage examples at: http://msdn2.microsoft.com/en-us/library/ms146067.aspx?

--Artur

|||

Thank you Artur, yes I have looked these examples, I

have this code, and y run it in SQL Management Studio in the mdx query, and I get the right result but, how you

can put this code as a Calculated Member in Analysis Services 2005

WITH MEMBER AvgAge AS
'[Measures].[Age]/[Measures].[AgeCount]'
SELECT {AvgAge} ON 0,
{[Sex].[Sex].Members} ON 1
FROM PCPCube

The error says, incorrect syntax near WITH MEMBer, so what It's wrong.

Thanks

Erika :)

|||Hello Erika,

you have to put your calculation '[Measures].[Age]/[Measures].[AgeCount]' only into the expression field and the 'AvgAge' into the name field of the new Calculated Member.

Kind Regards,

Christian

average date of birth

Hi, folks.
I have a table with a column Date_of_birth.
I want to evaluate average date_of_birth by grouping on depts.
AVG function doesn't seem to work with datetime col. Plz help.Hi, folks.
I have a table with a column Date_of_birth.
I want to evaluate average date_of_birth by grouping on depts.
AVG function doesn't seem to work with datetime col. Plz help.

r u looking for average of age ??
coz avg of date of birth doesn't make any sense to me.|||yes, i want to evaluate average age on the basis of date of brith stored with in the column|||datediff ?|||AVG(datediff(yy,date_of_birth,getdate()))
this would work, i guess.
thanx|||How's about:

USE Northwind
GO

CREATE TABLE myTable99(Col1 datetime)
GO

INSERT INTO myTable99(Col1)
SELECT '10/24/1960' UNION ALL SELECT '10/24/1970' UNION ALL SELECT '10/24/1980'

SELECT CONVERT(datetime,AVG(CONVERT(float,Col1))) FROM myTable99
GO

DROP TABLE myTable99
GO|||Hi Brett, good morning.
That was what i've been lookin for.
How do we get the date back after it's converted into float.
SELECT convert(datetime,38174.0)
--------
2004-07-08 00:00:00.000
(1 row(s) affected)

Howdy!|||Here is a quick one:

************************************************** ****
SET NOCOUNT ON
/* Here is your table */
create table #working (Date_of_birth datetime)
insert into #working (Date_of_birth) VALUES ('1/1/1920')
insert into #working (Date_of_birth) VALUES ('1/6/1930')
insert into #working (Date_of_birth) VALUES ('1/17/1940')
DECLARE @.days_old int

/* Average days old */
SELECT @.days_old = AVG(datediff(dd, Date_of_birth, GETDATE()))
FROM #working

/* back it out now for average birth date, odd as that is :P */
SELECT dateadd(dd, -@.days_old, GETDATE())

drop table #working
SET NOCOUNT OFF
************************************************** ****

Hope it helps|||Aren't you concerned that cobalt sound a lot like COBOL?

Avareges for period

Hi,

I am trying to get average for sales for last 30 days.

Aggregation function for [Measures].[Avg Sales] is AverageOfChildren

Next code returns correct result

SELECT { [Measures].[Avg Sales] } ON columns

FROM [Sales]

where ([Dim Date].[Year - Day].[Day].&[2/14/2007]&[2]:[Dim Date].[Year - Day].[Day].&[3/15/2007]&[3])

But this query is not flexible. Should be selected day and returned value for 30 days back.

For example, I am trying to use next code

select

([Dim Date].[Year - Day].[Day].CurrentMember.lead(29) : [Dim Date].[Year - Day].[Day].CurrentMember

, [Measures].[Avg Sales]) // average for 30 days back

ON columns

FROM [SAles]

where ([Dim Date].[Year - Day].[Day].&[3/15/2007]&[3])

But recieved error "The Tuple function expects a tuple expression for the argument. A tuple set expression was used."

What the query should be to resolve this problem?

Thanks for help.

Try creating a query calculated measure for this,like:

With

Member [Measures].[Trailing30Sales] as

Aggregate({[Dim Date].[Year - Day].Lag(29) : [Dim Date].[Year - Day].CurrentMember},

[Measures].[Avg Sales])

select

{[Measures].[Trailing30Sales]} // average for 30 days back

ON columns

FROM [Sales]

where ([Dim Date].[Year - Day].[Day].&[3/15/2007]&[3])

|||

Thank you, very much!

It is very simple