Create a daily, weekly, monthly and quarterly average for all measures

hi,

What's the best way to create an average for all time grains across all measures?

At the moment, I've got a calculated measure for each measure, which itself uses two calculated measures:

Code Snippet

DayCount =
count(descendants([DATE TIME].[Calendar], ,LEAVES))

MonthCount =
count(descendants([DATE TIME].[Calendar], [DATE TIME].[Calendar].[Month]))

...

Code Snippet

SumSales =
sum([DATE TIME].[Calendar], [Sales])

SumUnits =
sum([DATE TIME].[Calendar], [Sales Units])

...

Code Snippet

AvgDailySales =
iif([Measures].[DayCount] = 0, null, [Measures].[SumSalesUnits] / [Measures].[DayCount])

AvgMonthlySales =
iif([Measures].[DayCount] = 0, null, [Measures].[SumSalesUnits] / [Measures].[MonthCount])

...

Thanks in advance,

If you search this forum on "AVERAGE" and "TIME" you'll find a bunch of solutions addressing this issue.

B.

|||

Hi mmmman,

For the days counting and using it for avarages caculations you should create additional measure group that use the time dimension table as fact table with a measure that counts days.

If you play futher, for examle to base your calculations on count of business days, set in fact table 0 for weekend days and 1 for work days then made a sum measure on it.

The proposed approach has following advatages: is multiselect aware, hasn't performance drawbacks.

More about day counting you cat take from Mosha's blog

http://www.sqljunkies.com/WebLog/mosha/archive/2007/05/27/counting_days_mdx.aspx

where another approaches are also described.

Create a daily, weekly, monthly and quarterly average for all measures

hi,

What's the best way to create an average for all time grains across all measures?

At the moment, I've got a calculated measure for each measure, which itself uses two calculated measures:

Code Snippet

DayCount =
count(descendants([DATE TIME].[Calendar], ,LEAVES))

MonthCount =
count(descendants([DATE TIME].[Calendar], [DATE TIME].[Calendar].[Month]))

...

Code Snippet

SumSales =
sum([DATE TIME].[Calendar], [Sales])

SumUnits =
sum([DATE TIME].[Calendar], [Sales Units])

...

Code Snippet

AvgDailySales =
iif([Measures].[DayCount] = 0, null, [Measures].[SumSalesUnits] / [Measures].[DayCount])

AvgMonthlySales =
iif([Measures].[DayCount] = 0, null, [Measures].[SumSalesUnits] / [Measures].[MonthCount])

...

Thanks in advance,

If you search this forum on "AVERAGE" and "TIME" you'll find a bunch of solutions addressing this issue.

B.

|||

Hi mmmman,

For the days counting and using it for avarages caculations you should create additional measure group that use the time dimension table as fact table with a measure that counts days.

If you play futher, for examle to base your calculations on count of business days, set in fact table 0 for weekend days and 1 for work days then made a sum measure on it.

The proposed approach has following advatages: is multiselect aware, hasn't performance drawbacks.

More about day counting you cat take from Mosha's blog

http://www.sqljunkies.com/WebLog/mosha/archive/2007/05/27/counting_days_mdx.aspx

where another approaches are also described.

CPU usage by DB

I have a SQL server cluster that hosts about 40 db. The CPU utilization went
from an average of 6% to 30%. I did not add any more DBs during this
timeframe. I can see from the running processes that it is sqlservr process
using it.
How can I determine which DBs are using the most CPU?
thankstime to dust of SQLProfiler and start investigating.
"jason" <jason@.discussions.microsoft.com> wrote in message
news:6A92892A-7952-4A9A-9BA1-F89167C26BBC@.microsoft.com...
>I have a SQL server cluster that hosts about 40 db. The CPU utilization
>went
> from an average of 6% to 30%. I did not add any more DBs during this
> timeframe. I can see from the running processes that it is sqlservr
> process
> using it.
> How can I determine which DBs are using the most CPU?
> thanks|||Not in SQL 2000. No idea what new toys are available for 2005.
Try a PSSDiag to see what all is happening:
http://support.microsoft.com/kb/830232/en-us
--
Kevin Hill
3NF Consulting
www.3nf-inc.com
http://kevin3nf.blogspot.com
"jason" <jason@.discussions.microsoft.com> wrote in message
news:6A92892A-7952-4A9A-9BA1-F89167C26BBC@.microsoft.com...
>I have a SQL server cluster that hosts about 40 db. The CPU utilization
>went
> from an average of 6% to 30%. I did not add any more DBs during this
> timeframe. I can see from the running processes that it is sqlservr
> process
> using it.
> How can I determine which DBs are using the most CPU?
> thanks|||Hi Jason,
My understanding of your issue is that:
Your SQL Server cluster hosts about 40 databases. You found that the CPU
utilization went from an average 6% to 30%. You wanted to see which DBs are
busily using CPU.
If I have misunderstood, please let me know.
You may use SQL Profiler and PSSDiag to monitor your SQL Server
performance. However there is a direct way on this issue:
use master
Go
select t2.name as dbname, t1.* from sysprocesses t1 join sysdatabases t2 on
t1.dbid=t2.dbid order by t1.dbid
Run the SQL statement and pay attention on the columns: lastwaittype, cpu,
physical_io, memusage, status, and cmd.
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Charles thank you for the assistance.
i ran the query. can you tell me more about what these indicators mean.
e.g. i sorted by cpu. does a large number there definitely indicate its
consuming a lot of cpu?
when i read about it in books online, it says that is the cumulitive CPU
time. to me that means it could have been running for days. so if i look at
the other columns, it show this process logged in oct 10th, over 2 weeks ago.
so is this large cpu value adding up all the time this process has used on
the cpu for the last 2 weeks?
or is there somewhere in books online that i can learn more about what i am
looking at?
"Charles Wang[MSFT]" wrote:
> Hi Jason,
> My understanding of your issue is that:
> Your SQL Server cluster hosts about 40 databases. You found that the CPU
> utilization went from an average 6% to 30%. You wanted to see which DBs are
> busily using CPU.
> If I have misunderstood, please let me know.
> You may use SQL Profiler and PSSDiag to monitor your SQL Server
> performance. However there is a direct way on this issue:
> use master
> Go
> select t2.name as dbname, t1.* from sysprocesses t1 join sysdatabases t2 on
> t1.dbid=t2.dbid order by t1.dbid
> Run the SQL statement and pay attention on the columns: lastwaittype, cpu,
> physical_io, memusage, status, and cmd.
> If you have any other questions or concerns, please feel free to let me
> know. It is my pleasure to be of assistance.
> Charles Wang
> Microsoft Online Community Support
> ======================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================>|||Hi Jason,
I am sorry for not saying clearly in my first reply. Yes, it is cumulitive
time, but it is helpful.
First, you can see the status and cmd columns to check the current
executing command and the current status of the process that the database
used. Second, you can know which database is the busiest from the
cumulitive time since it has been started; Also, you can use subtraction to
compute the CPU time cost, physical IO read and write times by running the
query twice during a time frame, then you will know which database is the
busiest during the time frame.
Hope this helpful. For the columns meaning, you can refer to sysprocesses
in SQL Server Books Online.
If you have any other questions or cnocerns, please feel free to let me
know. It is my pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi Jason,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Have a great day!
Charles Wang
Microsoft Online Community Support

CPU usage by DB

I have a SQL server cluster that hosts about 40 db. The CPU utilization wen
t
from an average of 6% to 30%. I did not add any more DBs during this
timeframe. I can see from the running processes that it is sqlservr process
using it.
How can I determine which DBs are using the most CPU?
thankstime to dust of SQLProfiler and start investigating.
"jason" <jason@.discussions.microsoft.com> wrote in message
news:6A92892A-7952-4A9A-9BA1-F89167C26BBC@.microsoft.com...
>I have a SQL server cluster that hosts about 40 db. The CPU utilization
>went
> from an average of 6% to 30%. I did not add any more DBs during this
> timeframe. I can see from the running processes that it is sqlservr
> process
> using it.
> How can I determine which DBs are using the most CPU?
> thanks|||Not in SQL 2000. No idea what new toys are available for 2005.
Try a PSSDiag to see what all is happening:
http://support.microsoft.com/kb/830232/en-us
Kevin Hill
3NF Consulting
www.3nf-inc.com
http://kevin3nf.blogspot.com
"jason" <jason@.discussions.microsoft.com> wrote in message
news:6A92892A-7952-4A9A-9BA1-F89167C26BBC@.microsoft.com...
>I have a SQL server cluster that hosts about 40 db. The CPU utilization
>went
> from an average of 6% to 30%. I did not add any more DBs during this
> timeframe. I can see from the running processes that it is sqlservr
> process
> using it.
> How can I determine which DBs are using the most CPU?
> thanks|||Hi Jason,
My understanding of your issue is that:
Your SQL Server cluster hosts about 40 databases. You found that the CPU
utilization went from an average 6% to 30%. You wanted to see which DBs are
busily using CPU.
If I have misunderstood, please let me know.
You may use SQL Profiler and PSSDiag to monitor your SQL Server
performance. However there is a direct way on this issue:
use master
Go
select t2.name as dbname, t1.* from sysprocesses t1 join sysdatabases t2 on
t1.dbid=t2.dbid order by t1.dbid
Run the SQL statement and pay attention on the columns: lastwaittype, cpu,
physical_io, memusage, status, and cmd.
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Charles thank you for the assistance.
i ran the query. can you tell me more about what these indicators mean.
e.g. i sorted by cpu. does a large number there definitely indicate its
consuming a lot of cpu?
when i read about it in books online, it says that is the cumulitive CPU
time. to me that means it could have been running for days. so if i look a
t
the other columns, it show this process logged in oct 10th, over 2 weeks ago
.
so is this large cpu value adding up all the time this process has used on
the cpu for the last 2 weeks?
or is there somewhere in books online that i can learn more about what i am
looking at?
"Charles Wang[MSFT]" wrote:

> Hi Jason,
> My understanding of your issue is that:
> Your SQL Server cluster hosts about 40 databases. You found that the CPU
> utilization went from an average 6% to 30%. You wanted to see which DBs ar
e
> busily using CPU.
> If I have misunderstood, please let me know.
> You may use SQL Profiler and PSSDiag to monitor your SQL Server
> performance. However there is a direct way on this issue:
> use master
> Go
> select t2.name as dbname, t1.* from sysprocesses t1 join sysdatabases t2 o
n
> t1.dbid=t2.dbid order by t1.dbid
> Run the SQL statement and pay attention on the columns: lastwaittype, cpu,
> physical_io, memusage, status, and cmd.
> If you have any other questions or concerns, please feel free to let me
> know. It is my pleasure to be of assistance.
> Charles Wang
> Microsoft Online Community Support
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> ========================================
==============
>|||Hi Jason,
I am sorry for not saying clearly in my first reply. Yes, it is cumulitive
time, but it is helpful.
First, you can see the status and cmd columns to check the current
executing command and the current status of the process that the database
used. Second, you can know which database is the busiest from the
cumulitive time since it has been started; Also, you can use subtraction to
compute the CPU time cost, physical IO read and write times by running the
query twice during a time frame, then you will know which database is the
busiest during the time frame.
Hope this helpful. For the columns meaning, you can refer to sysprocesses
in SQL Server Books Online.
If you have any other questions or cnocerns, please feel free to let me
know. It is my pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Hi Jason,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Have a great day!
Charles Wang
Microsoft Online Community Support

counting number of specific rows in SQL

hi,

I would like to create a user defined SQL function which returns the number of rows which meets certain condition, and the average value of one of the culomns. I cannot find a code example for it. Please help.

Thanks,

Dror.

I don't think a SQL Server function is the best option for you. If you are returning this to SSRS, you'll probably want to leverage a stored procedure. Still, here is a generic representation of what you're asking for:

create function dbo.MyFunction( @.MyParam varchar(10) )

returns @.ret table (MyCount int, MyAvg float)

begin

insert into @.ret (MyCount, MyAvg)

select count(*), avg(MyField) from MyTable where MyOtherField=@.MyParam

return

end

This function returns a table. To retrieve data, you will call it as follows:

select *

from dbo.MyFunction( @.MyParam)

Again, I don't think this is the best approach to this problem. There are more easily read and consumed approaches to this problem.

Thanks,
Bryan

|||I am aware of the fact I can perform the same in VB - even eassier, but there is a reason for using function on the server. Supposed I want to set the number of rows to a variable, how do I do it ?|||

A VB function is not a good choice either. Are you using this data in SSRS?

Thanks,
Bryan

|||

Hi Bryan,

To tell the truth - I do not even know what SSRS is...I am new to this field, still learning. I tried to minimize my application for security (I'm affraid of paople will restore my code and see my logic creating the code), so some of the important stuff will perform as stored procedures on the SQL (which I'm also learning now...) Now I managed to calculate the average, and count the rows. But my function needs to return the result in a variable: the first variable - numbers of rows, the second - the average of a column, and the third - I need to find the fifth biggest value in a specific column. The prolem is assigning the results into variables.

Thanks again for your help,

Dror.

|||

Try something like this with appropriate substitutions. Please note, the data types I'm using for the parameters does not imply that these would be the approapriate data types for your application.

Code Snippet

create proc MyProc @.MyParam varchar(10), @.MyCount int OUTPUT, @.MyAvg float OUTPUT

as

select @.MyCount = count(*), @.MyAvg = avg(MyField)

from MyTable

where MyOtherField = @.MyParam

return 0

end

You can look up more info on using output parameters by reading the Books Online entry for the CREATE PROCEDURE statement.

Thanks,
Bryan Smith

|||thank you very much for your great help Bryan.

Counting Average Value

i want to count average on one of the fields in my table
i am doing a right click on it and expression and am typing the following
but getting 0 as an answer
=(( Fields!valueactiveaccounts.Value+ Fields!valueskipaccounts.Value)/( Fields!numactiveaccounts.Value+ Fields!numskipaccounts.Value))
is there any other way to get average value?Raj wrote:
> i want to count average on one of the fields in my table
> i am doing a right click on it and expression and am typing the
> following
> but getting 0 as an answer
> =(( Fields!valueactiveaccounts.Value+
> Fields!valueskipaccounts.Value)/( Fields!numactiveaccounts.Value+
> Fields!numskipaccounts.Value))
> is there any other way to get average value?
Try the inbuilt avg aggregate
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_10pz.asp
roland