LastPeriods Function

Does the LastPeriods function have to be used with the Aggregate function when creating a KPI expression?

Aggregate({[Incident].[Incident].&[1]} *

{LastPeriods(4, [Date].[Year - Quarter].[Quarter].&[2006-01-01T00:00:00])},

[Measures].[TOTAL])

If so, is it better to just do:

Aggregate({[Incident].[Incident].&[1]} *

{[Date].[Year - Quarter].[Quarter].&[2005-01-01T00:00:00] : [Date].[Year - Quarter].[Quarter].&[2006-01-01T00:00:00]},

[Measures].[TOTAL])

Whether Aggregate() should be used depends on the definition of the KPI - what are the business rules for it?

Also, {[Date].[Year - Quarter].[Quarter].&[2005-01-01T00:00:00] : [Date].[Year - Quarter].[Quarter].&[2006-01-01T00:00:00]} is probably equivalent to LastPeriods(5, [Date].[Year - Quarter].[Quarter].&[2006-01-01T00:00:00]) - so should 4 or 5 quarters be included?

LastNonEmpty and AverageOfChildren

Hello,

we are using SQL 2005 with SP1 and we have quite small cube (<1GB).

We have a couple of measures with aggregate function AverageOfChildren or LastNonEmpty.

End users have very poor performances while browsing cube using these measures.

When we replace agg. function with SUM, for example, performances become normal.

I saw simillar discusion on OLAP forum, but I did not understand what is the best recommendation for this situation?

Is this known issue, and how it can be avoided?

Best regards

Borko

Borko,

What's your aggregation strategy for this measure group? Queries to semi-additive measures are resolved by initially going down to granularity for the measure group's time dimension and subsequently computing the aggregate. Because of this, it's important to insure that you have an aggregation that includes the granularity attribute. If not already there, you can generally force the attribute to be included by setting the AggregationUsage property to "Full" in the cube editor. However, since this setting applies across all measure groups, you may want to set it, design aggregations for this measure group, and then rest the value.

I'm not positive that this will solve your issue, but this is where I would start.

-rob

LastNonEmpty + Time PrevMember don't aggregate correctly

I'm using the Account Intelligence and when I have a balance account modified with a mdx script in cube calculation using the Time prevmember it don't aggregates correctly. I think that it's a solver order problem.

I have a account dimension like that:

Balance (balance account)

Asset (balance account)

Computers (balance account)

Result (flow account)

Computers Investments (flow account)

In the cube calculation I have these formula:

Computers = (Computers, Time.Currentmember.Prevmember) + Computer Investments;

When I query these data I receive these:

2007 Jan Fev Mar

Balance 0 100 50 0

Asset 0 100 50 0

Computers 150 100 150 150

Result 150 100 50 0

Computers Investments 150 100 50 0

It's not aggregatin the (Computers, Time.Currentmember.Prevmember) in account hierarchy, any Idea why is that?

I'm using SQL Server 2005 Enterprise SP1.

I was using the follow Time Hierarchy:

Year

Last Aggregate function

Hello, I need to use last() aggregate function in MS SQL Server 2005 but it is not built in.
How to replace this functionality?
Thanks.Hi

You can get this using row_number with an OVER clause in 2005. Check it out in BoL and see how you get on. HINT - you will need to order descending.

HTH|||Thanks for the quick response. I however am not too sure how to do it.

Let's say I have a query like this:

select
last(firstname),
lastname

from
users

group by
lastname

but the last() function does not work, so how to do it using your method?

I am quite new to the SQL so sorry for asking obvious things maybe :-)|||Ok, I think the answer is:

with A as (
select
row_number()
over (partition by lastname order by firstname desc) as 'row'
firstname
lastname
from
users
)
select
firstname
lastname
from
A
where
row = 1|||Now I am not sure that returns what you want. Does is return the same as this?
SELECT lastname
, MAX(firstname) AS firstname
FROM dbo.A
GROUP BY lastname

Last & First SQL aggregate functions

I'm trying to migrate an app. from Access to SQL Server, and find that Transact-SQL does not support LAST/FIRST functions. Is there any alternative to these?

Below is the Access SQL statement:

SELECT Last(tblZoneNameString.Val) AS strZoneName, tblZoneNameString.TagIndex
FROM tblZoneNameString
GROUP BY tblZoneNameString.TagIndex
HAVING (((tblZoneNameString.TagIndex)>0));

Use MIN and MAX

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

In SQL Server 2005, you can do something like this: select tblZoneNameString.Val as strZoneName, tblZoneNameString.TagIndex from ( select tblZoneNameString.Val as strZoneName, tblZoneNameString.TagIndex, rank() over (partition by tblZoneNameString.TagIndex order by ?) as rk from tblZoneNameString ) as T where rk = 1 Where I've written ? you will need to put whatever column or columns answer the question "last in order of what?". Perhaps this is something like someDateTime DESC. Steve Kass Drew University JimNolandCBI@.discussions.microsoft.com wrote:
> I'm trying to migrate an app. from Access to SQL Server, and find that
> Transact-SQL does not support LAST/FIRST functions. Is there any
> alternative to these?
>
> Below is the Access SQL statement:
>
> SELECT Last(tblZoneNameString.Val) AS strZoneName,
> tblZoneNameString.TagIndex
> FROM tblZoneNameString
> GROUP BY tblZoneNameString.TagIndex
> HAVING (((tblZoneNameString.TagIndex)>0));
>
>