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.

Create a calculated member on one measure.

I have a Version dimension which is used to break out the Net measure by 'Actual' or 'Budget'. Net is the only measure I'm using. By not using the Version dimsion. The net is incorrectly totaled by both values. Both are not used together.

'Actual' and 'Budget' are the only two members of the Version dimension. I want to add a third member which subtracts 'Budget' Net measure from the 'Actual' Net measure and call it variance. A caculated measure isn't working for me because obviously subtracting Actual - Budget doesn't work so well.

Can anyone help me with that?

First, you need to mark your Version attribute as Not Aggregatable (set IsAggregatable property to false) - this will prevent summing up Actual and Budget.

I am not sure why is it "obviously" not working well, the following should work well for you:

CREATE Version.Variance = Version.Actual - Version.Budget;

HTH,

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

|||Where do this statement go? Sorry, I'm new to this.|||You add this statement to the MDX Script of the cube. It is under the Calculation tab in the Cube window. You may want to switch to Script view from Form view in order to copy it verbatim.|||Thanks for your prompt response. So I should add a 'New Script'?|||

I created a new script and added this to it by dragging the objects to the form and substituting in "Variance", but it didn't work:

CREATE [Version Dim V].[Version].&[Variance] = [Version Dim V].[Version].&[Actual] - [Version Dim V].[Version].&[Budget];

MdxScript(X_GL_Data) (6, 133) Parser: The syntax for ';' is incorrect.

Any ideas?

Thanks!

|||

OK I got it to the point where there are no errors, but the Variance member will still not show up in the Version dimension. WHat could be the cause?

/*

The CALCULATE command controls the aggregation of leaf cells in the cube.

If the CALCULATE command is deleted or modified, the data within the cube is affected.

You should edit this command only if you manually specify how the cube is aggregated.

*/

CALCULATE;

CREATE MEMBER CURRENTCUBE.[Version Dim V].[Version].[Variance]

AS [Version Dim V].[Version].&[Actual]-[Version Dim V].[Version].&[Budget],

FORMAT_STRING = "Currency",

NON_EMPTY_BEHAVIOR = { [Net Amount] },

VISIBLE = 1 ;

Thanks!

|||I see what it's doing. Its showing up in the Version_Heirarchy on the same level as 'All', not as a sibling of 'Budget' and 'Actual'. It's unusable there. How can I add it as a sibling?|||

First - please remove NON_EMPTY_BEHAVIOR - it is defined absolutely wrong.

Second, how exactly it doesn't show up ? If you connect with SSMS to the cube and browse the dimension - don't you see this calculated member ?

|||According to your original posting, you didn't want to have All to begin with. Therefore I advised you to set IsAggregatable=false - this way you won't have level/member All in the hierarchy.|||

At the time, 'all' was allowed. I didn't have it turned off. Everytime I try to set IsAggregatable to False for the Version_Key, Version_Description and the hierarchy, I get an error:

Error 1 Errors in the metadata manager. The 'Version Key' attribute with IsAggregatable=false must not have any relationship to 'Version Description' attribute that has AttributeHierarchyEnabled=true. 0 0

If I have AttributeHierarchyEnabled=False, I get two new errors:

Error 1 Dimension 'Version Dim V' > Attribute 'Version Key' : Attribute hierarchy must be enabled if attribute relationships are used. 0 0
Error 2 Dimension 'Version Dim V' > Attribute 'Version Key' : The key attribute requires attribute hierarchy enabled. 0 0

Version_Description is the only value I want to use. It contains 'Actual' and 'Budget', and should also include the 'Variance'

Thanks

|||

I finally got 'Variance' to show up with Actual and Budget, but when I try to apply it as a filter expression, I get the error below:

A set has been encountered that cannot contain calculated members.

The other two members of Version work fine.

Here is the setup:

Attribute:
Version Description: IsAggregatable=False, AttributeHierarchyEnabled=True

VersionKey: Key, AttributeHierarchyVisible=False, AtrributeRelationship to VersionDescription

Here is how the member is created:

CREATE MEMBER CURRENTCUBE.[Version Dim V].[Version Hierarchy].[Variance]

AS [Version Dim V].[Version].&[Actual]-[Version Dim V].[Version].&[Budget],

FORMAT_STRING = "Currency",

NON_EMPTY_BEHAVIOR = { [Net Amount] },

VISIBLE = 1 ;

The relationship between dimension columns and measure group columns is VersionKey>VersioKey with a granularity attribute of 'version key'. Using Version Description as the granularity attribute fails indicating it can not find the Version_key attribute key.

Thanks

|||

Finally, I got it by changing the hierarchy to the Attribute hierarchy for VersionDescription, not the hierarchy with Version Description as the only attribute/level in the hierarchy pane.

Thanks for your help in walking me through it.

|||

Mike,

I have a very similiar problem, but didn't fully understand your solution. Can you explain how you have your dimension structured and what properties you have set again descriptively in one post? I'm a little lost trying to follow the thread.

Thanks,
-Nimitt

Create a calculated member on one measure.

I have a Version dimension which is used to break out the Net measure by 'Actual' or 'Budget'. Net is the only measure I'm using. By not using the Version dimsion. The net is incorrectly totaled by both values. Both are not used together.

'Actual' and 'Budget' are the only two members of the Version dimension. I want to add a third member which subtracts 'Budget' Net measure from the 'Actual' Net measure and call it variance. A caculated measure isn't working for me because obviously subtracting Actual - Budget doesn't work so well.

Can anyone help me with that?

First, you need to mark your Version attribute as Not Aggregatable (set IsAggregatable property to false) - this will prevent summing up Actual and Budget.

I am not sure why is it "obviously" not working well, the following should work well for you:

CREATE Version.Variance = Version.Actual - Version.Budget;

HTH,

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

|||Where do this statement go? Sorry, I'm new to this.|||You add this statement to the MDX Script of the cube. It is under the Calculation tab in the Cube window. You may want to switch to Script view from Form view in order to copy it verbatim.|||Thanks for your prompt response. So I should add a 'New Script'?|||

I created a new script and added this to it by dragging the objects to the form and substituting in "Variance", but it didn't work:

CREATE [Version Dim V].[Version].&[Variance] = [Version Dim V].[Version].&[Actual] - [Version Dim V].[Version].&[Budget];

MdxScript(X_GL_Data) (6, 133) Parser: The syntax for ';' is incorrect.

Any ideas?

Thanks!

|||

OK I got it to the point where there are no errors, but the Variance member will still not show up in the Version dimension. WHat could be the cause?

/*

The CALCULATE command controls the aggregation of leaf cells in the cube.

If the CALCULATE command is deleted or modified, the data within the cube is affected.

You should edit this command only if you manually specify how the cube is aggregated.

*/

CALCULATE;

CREATE MEMBER CURRENTCUBE.[Version Dim V].[Version].[Variance]

AS [Version Dim V].[Version].&[Actual]-[Version Dim V].[Version].&[Budget],

FORMAT_STRING = "Currency",

NON_EMPTY_BEHAVIOR = { [Net Amount] },

VISIBLE = 1 ;

Thanks!

|||I see what it's doing. Its showing up in the Version_Heirarchy on the same level as 'All', not as a sibling of 'Budget' and 'Actual'. It's unusable there. How can I add it as a sibling?|||

First - please remove NON_EMPTY_BEHAVIOR - it is defined absolutely wrong.

Second, how exactly it doesn't show up ? If you connect with SSMS to the cube and browse the dimension - don't you see this calculated member ?

|||According to your original posting, you didn't want to have All to begin with. Therefore I advised you to set IsAggregatable=false - this way you won't have level/member All in the hierarchy.|||

At the time, 'all' was allowed. I didn't have it turned off. Everytime I try to set IsAggregatable to False for the Version_Key, Version_Description and the hierarchy, I get an error:

Error 1 Errors in the metadata manager. The 'Version Key' attribute with IsAggregatable=false must not have any relationship to 'Version Description' attribute that has AttributeHierarchyEnabled=true. 0 0

If I have AttributeHierarchyEnabled=False, I get two new errors:

Error 1 Dimension 'Version Dim V' > Attribute 'Version Key' : Attribute hierarchy must be enabled if attribute relationships are used. 0 0
Error 2 Dimension 'Version Dim V' > Attribute 'Version Key' : The key attribute requires attribute hierarchy enabled. 0 0

Version_Description is the only value I want to use. It contains 'Actual' and 'Budget', and should also include the 'Variance'

Thanks

|||

I finally got 'Variance' to show up with Actual and Budget, but when I try to apply it as a filter expression, I get the error below:

A set has been encountered that cannot contain calculated members.

The other two members of Version work fine.

Here is the setup:

Attribute:
Version Description: IsAggregatable=False, AttributeHierarchyEnabled=True

VersionKey: Key, AttributeHierarchyVisible=False, AtrributeRelationship to VersionDescription

Here is how the member is created:

CREATE MEMBER CURRENTCUBE.[Version Dim V].[Version Hierarchy].[Variance]

AS [Version Dim V].[Version].&[Actual]-[Version Dim V].[Version].&[Budget],

FORMAT_STRING = "Currency",

NON_EMPTY_BEHAVIOR = { [Net Amount] },

VISIBLE = 1 ;

The relationship between dimension columns and measure group columns is VersionKey>VersioKey with a granularity attribute of 'version key'. Using Version Description as the granularity attribute fails indicating it can not find the Version_key attribute key.

Thanks

|||

Finally, I got it by changing the hierarchy to the Attribute hierarchy for VersionDescription, not the hierarchy with Version Description as the only attribute/level in the hierarchy pane.

Thanks for your help in walking me through it.

|||

Mike,

I have a very similiar problem, but didn't fully understand your solution. Can you explain how you have your dimension structured and what properties you have set again descriptively in one post? I'm a little lost trying to follow the thread.

Thanks,
-Nimitt

counting the inserts and updates on a table in a sql server database

Hello,

Can someone point me to getting the total number of inserts and updates on a table
over a period of time?

I just want to measure the insert and update activity on the tables.

Thanks.

- VishOn a single statement you can capture the @.@.rowcount into a variable and
write it to a log table. But if I'm reading this correctly, you don't want
to do this through the existing code base. Inserts are usually easy if
there is a primary or unique key. Assuming no deletes, simply how many new
keys are there since the last count. Or if the key is incrementing by one
what's the max value - the previous max value. Updates are more vague. How
many rows were updated or how many updates occurred These activities are
usually accommodated for in the initial table design with flag and
last_mod_date columns. Without auditing written into every piece of code or
proper schema design it's an ugly intensive task to rub to data sets
together (using checksums or straight comparisons) to find differences.

Danny

"Viswanatha Thalakola" <vthalakola@.yahoo.com> wrote in message
news:d762e418.0411301845.7504b0b4@.posting.google.c om...
> Hello,
> Can someone point me to getting the total number of inserts and updates on
> a table
> over a period of time?
> I just want to measure the insert and update activity on the tables.
> Thanks.
> - Vish|||On 30 Nov 2004 18:45:55 -0800, Viswanatha Thalakola wrote:

>Hello,
>Can someone point me to getting the total number of inserts and updates on a table
>over a period of time?
>I just want to measure the insert and update activity on the tables.
>Thanks.
>- Vish

Hi Vish,

The easiest way to do this is to add some counting logic to the stored
procedures that do the inserting, updating and deleting. But if you can't
or won't change those tables (or if you allow direct data modifications,
without using stored procedures), you have two other options:

1. Set up a profiler trace. Catch the trace results in a table or in a
file, then use either SQL queries (if in a table) or text manipulation
tools (if in a file) to count the number of inserts, updates, etc. I must
add that I don't know the exact format and I'm not sure either if the
number of rows affected is included in the trace data (it it isn't, you
can't use this approach).

2. Create triggers for each table you need to monitor. Have these triggers
copy @.@.rowcount in a local variable as the first statement (that yields
the number of rows affected by the statement that fired the trigger) and
save that value to a table.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
> 1. Set up a profiler trace. Catch the trace results in a table or in a
> file, then use either SQL queries (if in a table) or text manipulation
> tools (if in a file) to count the number of inserts, updates, etc. I must
> add that I don't know the exact format and I'm not sure either if the
> number of rows affected is included in the trace data (it it isn't, you
> can't use this approach).

It isn't, but you can catch number of page writes. Still, though, not a
wholly reliable number.

Then again, I assume that the aim is not to save exact numbers, but get
some approxamite statistics, so some Profiler method is proably better
than adding triggers to the system.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Counting Rows

I want to define a measure which is count of rows in a fact table satisfying certain criteria. I just need a single value and hence this measure need not be additive from any dimension.

There are 2 possible scenarios -

(1) The criteria involves columns from the fact table itself (Example: [Fact Table].[Transaction Type] = 1 or 2 or 3)

(2) The criteria involves columns from dimension tables (Example: [Dimension Table].[Transaction Category] = 'D')

I am novice to SS BI and I have tried-out various solutions involving MDX Queries, Calculated Member, Named Set etc. but not successful. Kindly suggest me, how to achieve this.

Thanks & Regards.

Is my question so naive?|||

Maybe the best solution (for performance and smart use) should be to create an additive measure.

Try it in this way:

1) Create a Named Calculation in your fact table in your data source view

2) In the Expression write a CASE to test your condition to have 1=True and Null=False, something like this:

CASE
WHEN ([Transaction Type] = 1) THEN 1
WHEN ([Transaction Type] = 2) THEN 1
WHEN ([Transaction Type] = 3) THEN 1

WHEN ([Transaction Category] = 'D') THEN 1
ELSE NULL
END

3) In your cube create an additive measure based on your Named Calculation

Now you should use directly your measure in every MDX query.

Let me know if it work fine.

Francesco

|||Another approach is to create a row count measure and create a dimension based on Transation Type in addition to the with the dimension containing Transaction Category. You can then filter the count on Transaction Type and Transaction Category at run-time. Or, if you never wanted to see a certain type of transaction, you could put in a named query in your DSV or a query binding in your partition that filters out those rows.

Counting Rows

I want to define a measure which is count of rows in a fact table satisfying certain criteria. I just need a single value and hence this measure need not be additive from any dimension.

There are 2 possible scenarios -

(1) The criteria involves columns from the fact table itself (Example: [Fact Table].[Transaction Type] = 1 or 2 or 3)

(2) The criteria involves columns from dimension tables (Example: [Dimension Table].[Transaction Category] = 'D')

I am novice to SS BI and I have tried-out various solutions involving MDX Queries, Calculated Member, Named Set etc. but not successful. Kindly suggest me, how to achieve this.

Thanks & Regards.

Is my question so naive?|||

Maybe the best solution (for performance and smart use) should be to create an additive measure.

Try it in this way:

1) Create a Named Calculation in your fact table in your data source view

2) In the Expression write a CASE to test your condition to have 1=True and Null=False, something like this:

CASE
WHEN ([Transaction Type] = 1) THEN 1
WHEN ([Transaction Type] = 2) THEN 1
WHEN ([Transaction Type] = 3) THEN 1

WHEN ([Transaction Category] = 'D') THEN 1
ELSE NULL
END

3) In your cube create an additive measure based on your Named Calculation

Now you should use directly your measure in every MDX query.

Let me know if it work fine.

Francesco

|||Another approach is to create a row count measure and create a dimension based on Transation Type in addition to the with the dimension containing Transaction Category. You can then filter the count on Transaction Type and Transaction Category at run-time. Or, if you never wanted to see a certain type of transaction, you could put in a named query in your DSV or a query binding in your partition that filters out those rows.