LastPeriods() Function producing incorrect results

I am using the LastPeriods Function to SUM the Last 12 periods at a month level:

MEMBER [Measures].[Last 12 Months] As
'SUM(LastPeriods(12, [Time].[Calendar Year].[Year].[2006].[Q4-06].&[December]), [Measures].[Total TEUS]) '

This should equal out to be [Time].[Calendar Year].[Year].[2006] but for some reason, the totals are different.

I thought it might be an issue with solve order so I took all other calculations out and it is still incorrect.
I am not sure what to look for now in fixing this problem. Is my syntax structure wrong here? Hopefully it is something small I am missing here.

Regards,

-Troy

What is the aggregation function for [Measures].[Total TEUS] - is it a "sum" measure?|||

Deepak,

Actually, this issue was a user error on my part. Turns out I was comparing it with a different Time Dimension.

The numbers are actually coming out correctly. Sorry for that. The problem I am seeing now is when using this member and a SET for the TopCount() function.

For Example:

WITH

MEMBER [Measures].[LastPeriod] AS
'SUM(LastPeriods(12, [Time].[Calendar Year].[Year].&[2006].&[Q4-06].&[December]), [Measures].[Total TEUS]) '

SET [TopShipline] AS
'NONEMPTY({TOPCOUNT([Ship Line].[Shiplines].[SHIPLINE].Members, 5, [Measures].CurrentMember)})'

SELECT {[Measures].[LastPeriod]} ON COLUMNS,
[TopShipline] ON ROWS
From [Account Level Aggregates]
WHERE ([Domestic Companies].[Domestic Company].[Company Name].[Some Company])

The results are as follows:

LastPeriod
ShiplineA 772.0
ShiplineB 7,738.5
ShiplineC 3,976.7
ShiplineD 2,158.1
ShiplineE 4,707.9

ShiplineA should not be in the results here. If I change to TopCount count to 6. The result is correct, minus ShiplineA.

LastPeriod
ShiplineA 772.0
ShiplineB 7,738.5
ShiplineC 3,976.7
ShiplineD 2,158.1
ShiplineE 4,707.9
ShiplineF 1,660.0

TopCount() for other dimensions that use this time dimension have similar results. This is only when using LastPeriods().

If I use say:

SET [TopPorts] AS
'NONEMPTY({TOPCOUNT([US Port].[US Port Name].Children, 5, [Measures].CurrentMember)})'

LastPeriod
PortA 2,690.7
PortB 5,036.4
PortC 6,124.4
PortD 5,542.2
PortE 513.7

In this case, PortE does not belong and the sort order of my measure is not as it should be. Again, changing the count to 6 from five brings the correct fifth Port, PortF

|||

Let me add one last piece to this and this is this apears to only be an issue when my [Domestic Company] dimension is used in the where clause:

WHERE ([Domestic Companies].[Domestic Company].[Company Name].[Some Company])

If I use a different dimension in the WHERE clause, the results are correct.

This is a somewhat large, flat dimension with about 600,000 members.

|||

Keeping in mind that the named set will be computed only in the context of the "where" slicer axis, which doesn't include a measure, you should explicitly specify [Total TEUS], like:

SET [TopShipline] AS
'NONEMPTY(TOPCOUNT([Ship Line].[Shiplines].[SHIPLINE].Members, 5, [Measures].[Total TEUS]))'

|||

Thanks for reply. I have resolved this. What I needed to do was use the Measure for my TopCount() function that I created as my calculated member.

MEMBER [Measures].[Last 12 Months] AS

'..........'

So, instead of:

SET [TopShipline] AS

'NONEMPTY( {TOPCOUNT( [Ship Line].[Shiplines].Children, 5, [Measures].[Total TEUS])})'

I needed to use:

SET [TopShipline] AS

'NONEMPTY( {TOPCOUNT( [Ship Line].[Shiplines].Children, 5, [Measures].[Last 12 Months])})'

Last available Value from Last n Years(OLAP, MDX)

Hi,

We have a requirement in which data for a member if not avbl. need to be replaced by the latest data avbl. in the last 'n' previous members and we need to control the number of previous members('n') that can be traversed.
ie..

if we are querying data for 1999 and we give traversal parameter of 3 yrs, in case of non avbl. data for 1999, the next avbl data between 1999-1997 should be placed there. In case of traversal year being 4 then the search range changes to 1999-1996.

Year--- DataSet1 --- DataSet2
1996--- 1 ------ 1
1997--- N/a ------ 2
1998--- N/a ------ N/a
1999--- N/a ------ N/a

Result Set --- YEAR --- (Traverse 3years) ---(Traverse 4years)

Data Set 1 --- 1999 --- N/a --- ------ 1
Data Set 2 --- 1999 --- 2 --- ------ 2

We used the fallowing query...but it is throwing the recursion error.

With Member [Measures].[Last Updated]AS 'tail(
Filter ( LastPeriods(3,[Time].Currentmember),
Not isEmpty([Measures].[Observation Value])
),1).item(0)'

Member [Measures].[Last Updated1]AS'
iif (NOt isEmpty([Measures].[Observation Value]),[Measures].[Observation Value],([Measures].[Last Updated]))'

Select
Crossjoin ({[Series].[All Series].[Current account].[BM.GSR.FCTY.CD],[Series].[All Series].[Travel & tourism].[ST.INT.XPND.MP.ZS]},{[Measures].[Observation Value],[Measures].[Last Updated1]}) on columns,
({[Time].[All Time].[1996],[Time].[All Time].[1997],[Time].[All Time].[1998],[Time].[All Time].[1999]}) on rows
from Test1
where([Country].[All Country].[IND])

Any help is highly appreciated.

Thanks and regards

RajaWhat is N/A? Null?

Also, you're trying to mix and match rows...do you want the MAX?|||Thaks for responding..

1. N/A is Not Available. It means for these combination of dimensions there is no data.

2. I am writing a query like, for each member what is the value available in cube and what value it has been replaced with (in case of n/a cases).

Originally posted by Brett Kaiser
What is N/A? Null?

Also, you're trying to mix and match rows...do you want the MAX?

Large table search performance

I have a large table with 300,000 rows and it will grow in the future
The table contains member profile details and it has many columns (about 70)
The stored procedure that do the search use many columns to search the results
That why the ‘Where’ is very long and the all operation take to many time, some time I get Time out exception form the SQL server.
I’ll be happy to get some ideas how to do this search.
I think maybe to divide the table to many tables by State.

Something interesting is that the operation takes long time for the first search and then the second and the third are much faster, and after a few min that I’m not do anything again the first time is very slow

70 columns in a table is probably too many. It is hard to say. But there are few options to consider:

1. You can partition the table vertically. For example, split the table vertically into two - one with the most frequently accessed columns and another with the infrequent ones. This will make each table smaller in terms of row size and queries will be faster than accessing a single wide table. This of course complicates the data modifications since you have to issue two DMLs as opposed to one but it depends on your application and nature of the table

2. You can use partitioning (horizontal). But it depends on the choice of your partitioning key whether it is part of every search criteria or at least one of the primary search conditions. You get some manageability benefit also.

Anyway, the approach depends on your needs. You can find whitepapers in MSDN that discuss partitioning for example. Please take a look at that also. You can leave the table as is also and try to optimize the queries by say adding more indexes or creating additional statistics or restricting the search columns. Each has it's pros and cons. For example, adding more indexes will slow down data modification operations.

The reason why the queries are faster after the first time is due to plan caching. Looks like you are generating lot of ad-hoc queries due to variable number of search conditions and this is expected. Of course, the plan might get removed from cache later due to memory pressure or lack of reusability. It is hard to tell without knowing the type of queries and the nature of the workload.

|||

What type of partition will give me the best performance vertical or horizontal?

In horizontal partition what is the best way to do it:

How mane tables?

Witch column on each table?

The query that do all the problems is the search query and it use 14 columns in the ‘WHERE’ statement, maybe it best to divide the table into 2 table that one will hold the 14 columns for the search query and the second table will hold the rest of the columns is this will give best performance?

Large table search performance

I have a large table with 300,000 rows and it will grow in the future
The table contains member profile details and it has many columns (about 70)
The stored procedure that do the search use many columns to search the results
That why the ‘Where’ is very long and the all operation take to many time, some time I get Time out exception form the SQL server.
I’ll be happy to get some ideas how to do this search.
I think maybe to divide the table to many tables by State.

Something interesting is that the operation takes long time for the first search and then the second and the third are much faster, and after a few min that I’m not do anything again the first time is very slow

70 columns in a table is probably too many. It is hard to say. But there are few options to consider:

1. You can partition the table vertically. For example, split the table vertically into two - one with the most frequently accessed columns and another with the infrequent ones. This will make each table smaller in terms of row size and queries will be faster than accessing a single wide table. This of course complicates the data modifications since you have to issue two DMLs as opposed to one but it depends on your application and nature of the table

2. You can use partitioning (horizontal). But it depends on the choice of your partitioning key whether it is part of every search criteria or at least one of the primary search conditions. You get some manageability benefit also.

Anyway, the approach depends on your needs. You can find whitepapers in MSDN that discuss partitioning for example. Please take a look at that also. You can leave the table as is also and try to optimize the queries by say adding more indexes or creating additional statistics or restricting the search columns. Each has it's pros and cons. For example, adding more indexes will slow down data modification operations.

The reason why the queries are faster after the first time is due to plan caching. Looks like you are generating lot of ad-hoc queries due to variable number of search conditions and this is expected. Of course, the plan might get removed from cache later due to memory pressure or lack of reusability. It is hard to tell without knowing the type of queries and the nature of the workload.

|||

What type of partition will give me the best performance vertical or horizontal?

In horizontal partition what is the best way to do it:

How mane tables?

Witch column on each table?

The query that do all the problems is the search query and it use 14 columns in the ‘WHERE’ statement, maybe it best to divide the table into 2 table that one will hold the 14 columns for the search query and the second table will hold the rest of the columns is this will give best performance?