Define Pre-Calculated AVG measures?

It is possible in SQL Server Analysis to define Pre Calculated AVG measure?

Because If you are defining AVG measure for Calculated member then your report performance will down, so I need Pre Calculated AVG measure.

let me know anybody know any solution?

Dinesh Patel

You may want to read this:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=548837&SiteID=1

Also, I think you can get SSAS 2005 to cache results when using MDX scripts to define calculations.

The following example for the use of CACHE function is listed on MSDN:

Scope (Customers.Geography.USA);
Sales = 1.1 * (Sales, Time.Year.PrevMember);
Cache (Sales, Time.Year);
End Scope;

|||Even though the CACHE statement is available and can be used, it is not supported. As such, there is not guarantee that it will work as expected.

Default Time Member Issue

We have a DW/BI solution that use the now() function to determine the default week to set the time dimension to. This has been in place for over a year and has worked fine. For some reason it is not working due to a wacky time stamp being returned.

Example code ....

-- The First Calculated member is the value of NOW()
WITH MEMBER [Measures].[Full Date] as 'now()'
-- The Second Calculated Member is the Day part of the first calculated member.
MEMBER [Measures].[What Day] as 'DAY([Full Date])'
-- The Third Calculated Member is the Month part of the first calculated member.
MEMBER [Measures].[What Month] as 'MONTH([Full Date])'
-- The Fourth Calculated Member is the Year part of the first calculated member.
Member [Measures].[What Year] as 'YEAR([Full Date])'
SELECT

{[Full Date],[What Day],[What Month],[What Year]} ON COLUMNS

FROM [Cube]

Returned Datetime.....

Full Date What Day What Month What Year

10/15/4376 6:17:06 PM 138 207 46161

1. I verified the server has the correct system date and time.

2. I can run this same code against another analysis server and the correct time is returned.

Any ideas?

If I run this MDX in the sample app with a solve_order=1 it works fine. When setting the default member of a dimension there is no place to indicate a solve_order....we have restarted SQL Server/Olap services and rebooted the server but still not working.

Any Ideas?

|||

Maybe there is any other calculation interfering with your measures.

For example Time Inteligence with YTD or some Utility dimension you have with changed default members ...

To be sure you should do your default member without measures - only use StrToMember and the Now Function...

At the end of the cubes mdx script - for example

Code Snippet

alter cube currentcube update dimension [Time].[Year], default_member=StrToMember("[Time].[Year].&[" + cStr(Year(Now())) + "]");

Hannes

|||I don't think so. In the example I posted, the MDX is using the now() function. When I run this now() function on one server the correct time is returned on the other server I am getting an incorrect time. Thanks for your help.

Default Time Member Issue

We have a DW/BI solution that use the now() function to determine the default week to set the time dimension to. This has been in place for over a year and has worked fine. For some reason it is not working due to a wacky time stamp being returned.

Example code ....

-- The First Calculated member is the value of NOW()
WITH MEMBER [Measures].[Full Date] as 'now()'
-- The Second Calculated Member is the Day part of the first calculated member.
MEMBER [Measures].[What Day] as 'DAY([Full Date])'
-- The Third Calculated Member is the Month part of the first calculated member.
MEMBER [Measures].[What Month] as 'MONTH([Full Date])'
-- The Fourth Calculated Member is the Year part of the first calculated member.
Member [Measures].[What Year] as 'YEAR([Full Date])'
SELECT

{[Full Date],[What Day],[What Month],[What Year]} ON COLUMNS

FROM [Cube]

Returned Datetime.....

Full Date What Day What Month What Year

10/15/4376 6:17:06 PM 138 207 46161

1. I verified the server has the correct system date and time.

2. I can run this same code against another analysis server and the correct time is returned.

Any ideas?

If I run this MDX in the sample app with a solve_order=1 it works fine. When setting the default member of a dimension there is no place to indicate a solve_order....we have restarted SQL Server/Olap services and rebooted the server but still not working.

Any Ideas?

|||

Maybe there is any other calculation interfering with your measures.

For example Time Inteligence with YTD or some Utility dimension you have with changed default members ...

To be sure you should do your default member without measures - only use StrToMember and the Now Function...

At the end of the cubes mdx script - for example

Code Snippet

alter cube currentcube update dimension [Time].[Year], default_member=StrToMember("[Time].[Year].&[" + cStr(Year(Now())) + "]");

Hannes

|||I don't think so. In the example I posted, the MDX is using the now() function. When I run this now() function on one server the correct time is returned on the other server I am getting an incorrect time. Thanks for your help.