Latest value for all members at a given point in time

We have a fact table with a current balance record. For this balance rows are added to the table only when the balance has changed. This way we can reduce the volume in the fact table by not saving redundant information for every point in time that we want to analyze.

In SQL it is a simple task to get a view of the lastest fact records for a given point in time. SELECT f1.* FROM factTable f1 WHERE f1.timestamp = (SELECT MAX(f2.timestamp) FROM factTable f2 WHERE f2.timestamp <= givenPointInTime AND f2.balanceKey = f1.balanceKey). In other words, the given point in time directly determines which rows should be fetched from our fact table, one row for each balance record (balanceKey). This row will of course include several dimension attributes, which represent the point in time when the balance changed.

Now, we would like to reproduce this behaviour in our Analysis Services 2005 cube. Using a time dimension, we would like to be able to select a given point in time and have the cube return all rows previous to that time, one row for each balanceKey. Not only the rows matching the given point in time. How can this be done? For the other dimension attributes, they should behave in a normal way, such that for the returned rows, you can slice using any combination of them.

Any help or ideas are appreciated,
Lars

Assuming that you're using AS 2005 Enterprise Edition, you could try the "Last Nonempty Child" aggregation:

http://msdn2.microsoft.com/en-us/library/ms175356.aspx

>>

SQL Server 2005 Books Online

Defining Semiadditive Behavior

Semiadditive measures, which do not uniformly aggregate across all dimensions, are very common in many business scenarios. Every cube that is based on snapshots of balances over time exhibits this problem. You can find these snapshots in applications dealing with securities, account balances, budgeting, human resources, insurance policies and claims, and many other business domains.

...

LastNonEmpty

The member value is evaluated as the value of its last child along the time dimension that contains data.

>>

|||

Thanks Deepak. We are on an Enterprise Edition (pre-SP1) but we do not get the desired behaviour using the "Last Nonempty Child" aggregation. In fact, it doesn't seem to make any difference at all from using SUM. We have created an example containing a relational database with a simple fact table and an analysis services database built upon it with a measure called "Account Balance". If we browse the cube and add the measure and the Account dimension to the results pane, then filter based on the Time dimension and select the date 2005-12-31, Account number 3 should display the balance 400. Currently it does not.

The example (SQL script + XMLA script) can be found at: http://www.intellibis.se/pub/CumulativeExample.zip

Can anyone help us?

Regards,
Lars

sql

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 Populated Quarter''s Data

Currently I have the following calculation to get the closing Inventory balance for a year

Code Snippet

([Measures].[Total Square Area], ClosingPeriod([Time].[Quarter].[Quarter]))

But for 2007 for example this is Empty as Q4 has no data.

I know there must be a simple way to return the Last NON EMPTY Quarter's balance but I am having a fog on it....

Any help or link to an example would be great!

Will

Maybe this will work:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmpty([Time].[Quarter].[Quarter],

[Measures].[Total Square Area])).Item(0))

|||

Hi Deepak,

Unfortunately that returns an error

"Token Error, Syntax is not valid"

I figured it must be close though

|||

Well, here's a sample Adventure Works query, based on the above code snippet, which returns the last populated Quarter Exchange Rate for DeutscheMarks in FY2002 - a value of .49 in FY2002Q2 (after which the Euro replaced the DM):

Code Snippet

With

Member [Measures].[QuarterExchange] as

([Measures].[Average Rate],

Tail(NonEmpty(

[Date].[Fiscal Quarter of Year].[Fiscal Quarter of Year],

[Measures].[Average Rate])).Item(0)),

FORMAT_STRING = '#.00'

select

{[Measures].[QuarterExchange]} on 0

from [Adventure Works]

where ([Date].[Fiscal].[Fiscal Year].&[2002],

[Destination Currency].[Destination Currency].&[Deutsche Mark])

-

QuarterExchange
.49

|||I can run that in 2005 against AdventureWorks, however I need this to work in SSAS 2000. It does not seem to like NONEMPTY in 2000 - is this new & was there a workaround to get the same result prior to 2005 SSAS?|||

Hi Will,

Yes, this is new in AS 2005 - in AS 2000, you could try NonEmptyCrossJoin(), like:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmptyCrossJoin([Time].[Quarter].Members,

{[Measures].[Total Square Area]}, 1)).Item(0).Item(0))

For future reference, you might want to mention explicitly that you're asking a question specific to AS 2000, given that SQL Server 2005 was launched nearly 2 years ago, and we're on the threshold of SQL Server 2008

MDX: NonEmpty, Exists and evil NonEmptyCrossJoin

|||

Hi,

Thanks - sorry about the confusion with versions...

I was using 2005 at my old place. No I am 2005 for SSIS & SSRS but they won't budge from 2000 for AS just yet so it's a real pain & I tend to forget to mention it.

Sorry, & thanks for the help

Last Populated Quarter''s Data

Currently I have the following calculation to get the closing Inventory balance for a year

Code Snippet

([Measures].[Total Square Area], ClosingPeriod([Time].[Quarter].[Quarter]))

But for 2007 for example this is Empty as Q4 has no data.

I know there must be a simple way to return the Last NON EMPTY Quarter's balance but I am having a fog on it....

Any help or link to an example would be great!

Will

Maybe this will work:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmpty([Time].[Quarter].[Quarter],

[Measures].[Total Square Area])).Item(0))

|||

Hi Deepak,

Unfortunately that returns an error

"Token Error, Syntax is not valid"

I figured it must be close though

|||

Well, here's a sample Adventure Works query, based on the above code snippet, which returns the last populated Quarter Exchange Rate for DeutscheMarks in FY2002 - a value of .49 in FY2002Q2 (after which the Euro replaced the DM):

Code Snippet

With

Member [Measures].[QuarterExchange] as

([Measures].[Average Rate],

Tail(NonEmpty(

[Date].[Fiscal Quarter of Year].[Fiscal Quarter of Year],

[Measures].[Average Rate])).Item(0)),

FORMAT_STRING = '#.00'

select

{[Measures].[QuarterExchange]} on 0

from [Adventure Works]

where ([Date].[Fiscal].[Fiscal Year].&[2002],

[Destination Currency].[Destination Currency].&[Deutsche Mark])

-

QuarterExchange
.49

|||I can run that in 2005 against AdventureWorks, however I need this to work in SSAS 2000. It does not seem to like NONEMPTY in 2000 - is this new & was there a workaround to get the same result prior to 2005 SSAS?|||

Hi Will,

Yes, this is new in AS 2005 - in AS 2000, you could try NonEmptyCrossJoin(), like:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmptyCrossJoin([Time].[Quarter].Members,

{[Measures].[Total Square Area]}, 1)).Item(0).Item(0))

For future reference, you might want to mention explicitly that you're asking a question specific to AS 2000, given that SQL Server 2005 was launched nearly 2 years ago, and we're on the threshold of SQL Server 2008

MDX: NonEmpty, Exists and evil NonEmptyCrossJoin

|||

Hi,

Thanks - sorry about the confusion with versions...

I was using 2005 at my old place. No I am 2005 for SSIS & SSRS but they won't budge from 2000 for AS just yet so it's a real pain & I tend to forget to mention it.

Sorry, & thanks for the help

Last Populated Quarter''s Data

Currently I have the following calculation to get the closing Inventory balance for a year

Code Snippet

([Measures].[Total Square Area], ClosingPeriod([Time].[Quarter].[Quarter]))

But for 2007 for example this is Empty as Q4 has no data.

I know there must be a simple way to return the Last NON EMPTY Quarter's balance but I am having a fog on it....

Any help or link to an example would be great!

Will

Maybe this will work:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmpty([Time].[Quarter].[Quarter],

[Measures].[Total Square Area])).Item(0))

|||

Hi Deepak,

Unfortunately that returns an error

"Token Error, Syntax is not valid"

I figured it must be close though

|||

Well, here's a sample Adventure Works query, based on the above code snippet, which returns the last populated Quarter Exchange Rate for DeutscheMarks in FY2002 - a value of .49 in FY2002Q2 (after which the Euro replaced the DM):

Code Snippet

With

Member [Measures].[QuarterExchange] as

([Measures].[Average Rate],

Tail(NonEmpty(

[Date].[Fiscal Quarter of Year].[Fiscal Quarter of Year],

[Measures].[Average Rate])).Item(0)),

FORMAT_STRING = '#.00'

select

{[Measures].[QuarterExchange]} on 0

from [Adventure Works]

where ([Date].[Fiscal].[Fiscal Year].&[2002],

[Destination Currency].[Destination Currency].&[Deutsche Mark])

-

QuarterExchange
.49

|||I can run that in 2005 against AdventureWorks, however I need this to work in SSAS 2000. It does not seem to like NONEMPTY in 2000 - is this new & was there a workaround to get the same result prior to 2005 SSAS?|||

Hi Will,

Yes, this is new in AS 2005 - in AS 2000, you could try NonEmptyCrossJoin(), like:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmptyCrossJoin([Time].[Quarter].Members,

{[Measures].[Total Square Area]}, 1)).Item(0).Item(0))

For future reference, you might want to mention explicitly that you're asking a question specific to AS 2000, given that SQL Server 2005 was launched nearly 2 years ago, and we're on the threshold of SQL Server 2008

MDX: NonEmpty, Exists and evil NonEmptyCrossJoin

|||

Hi,

Thanks - sorry about the confusion with versions...

I was using 2005 at my old place. No I am 2005 for SSIS & SSRS but they won't budge from 2000 for AS just yet so it's a real pain & I tend to forget to mention it.

Sorry, & thanks for the help

Last Populated Quarter''s Data

Currently I have the following calculation to get the closing Inventory balance for a year

Code Snippet

([Measures].[Total Square Area], ClosingPeriod([Time].[Quarter].[Quarter]))

But for 2007 for example this is Empty as Q4 has no data.

I know there must be a simple way to return the Last NON EMPTY Quarter's balance but I am having a fog on it....

Any help or link to an example would be great!

Will

Maybe this will work:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmpty([Time].[Quarter].[Quarter],

[Measures].[Total Square Area])).Item(0))

|||

Hi Deepak,

Unfortunately that returns an error

"Token Error, Syntax is not valid"

I figured it must be close though

|||

Well, here's a sample Adventure Works query, based on the above code snippet, which returns the last populated Quarter Exchange Rate for DeutscheMarks in FY2002 - a value of .49 in FY2002Q2 (after which the Euro replaced the DM):

Code Snippet

With

Member [Measures].[QuarterExchange] as

([Measures].[Average Rate],

Tail(NonEmpty(

[Date].[Fiscal Quarter of Year].[Fiscal Quarter of Year],

[Measures].[Average Rate])).Item(0)),

FORMAT_STRING = '#.00'

select

{[Measures].[QuarterExchange]} on 0

from [Adventure Works]

where ([Date].[Fiscal].[Fiscal Year].&[2002],

[Destination Currency].[Destination Currency].&[Deutsche Mark])

-

QuarterExchange
.49

|||I can run that in 2005 against AdventureWorks, however I need this to work in SSAS 2000. It does not seem to like NONEMPTY in 2000 - is this new & was there a workaround to get the same result prior to 2005 SSAS?|||

Hi Will,

Yes, this is new in AS 2005 - in AS 2000, you could try NonEmptyCrossJoin(), like:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmptyCrossJoin([Time].[Quarter].Members,

{[Measures].[Total Square Area]}, 1)).Item(0).Item(0))

For future reference, you might want to mention explicitly that you're asking a question specific to AS 2000, given that SQL Server 2005 was launched nearly 2 years ago, and we're on the threshold of SQL Server 2008

MDX: NonEmpty, Exists and evil NonEmptyCrossJoin

|||

Hi,

Thanks - sorry about the confusion with versions...

I was using 2005 at my old place. No I am 2005 for SSIS & SSRS but they won't budge from 2000 for AS just yet so it's a real pain & I tend to forget to mention it.

Sorry, & thanks for the help

Last Populated Quarter''s Data

Currently I have the following calculation to get the closing Inventory balance for a year

Code Snippet

([Measures].[Total Square Area], ClosingPeriod([Time].[Quarter].[Quarter]))

But for 2007 for example this is Empty as Q4 has no data.

I know there must be a simple way to return the Last NON EMPTY Quarter's balance but I am having a fog on it....

Any help or link to an example would be great!

Will

Maybe this will work:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmpty([Time].[Quarter].[Quarter],

[Measures].[Total Square Area])).Item(0))

|||

Hi Deepak,

Unfortunately that returns an error

"Token Error, Syntax is not valid"

I figured it must be close though

|||

Well, here's a sample Adventure Works query, based on the above code snippet, which returns the last populated Quarter Exchange Rate for DeutscheMarks in FY2002 - a value of .49 in FY2002Q2 (after which the Euro replaced the DM):

Code Snippet

With

Member [Measures].[QuarterExchange] as

([Measures].[Average Rate],

Tail(NonEmpty(

[Date].[Fiscal Quarter of Year].[Fiscal Quarter of Year],

[Measures].[Average Rate])).Item(0)),

FORMAT_STRING = '#.00'

select

{[Measures].[QuarterExchange]} on 0

from [Adventure Works]

where ([Date].[Fiscal].[Fiscal Year].&[2002],

[Destination Currency].[Destination Currency].&[Deutsche Mark])

-

QuarterExchange
.49

|||I can run that in 2005 against AdventureWorks, however I need this to work in SSAS 2000. It does not seem to like NONEMPTY in 2000 - is this new & was there a workaround to get the same result prior to 2005 SSAS?|||

Hi Will,

Yes, this is new in AS 2005 - in AS 2000, you could try NonEmptyCrossJoin(), like:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmptyCrossJoin([Time].[Quarter].Members,

{[Measures].[Total Square Area]}, 1)).Item(0).Item(0))

For future reference, you might want to mention explicitly that you're asking a question specific to AS 2000, given that SQL Server 2005 was launched nearly 2 years ago, and we're on the threshold of SQL Server 2008

MDX: NonEmpty, Exists and evil NonEmptyCrossJoin

|||

Hi,

Thanks - sorry about the confusion with versions...

I was using 2005 at my old place. No I am 2005 for SSIS & SSRS but they won't budge from 2000 for AS just yet so it's a real pain & I tend to forget to mention it.

Sorry, & thanks for the help

sql

Last Populated Quarter''s Data

Currently I have the following calculation to get the closing Inventory balance for a year

Code Snippet

([Measures].[Total Square Area], ClosingPeriod([Time].[Quarter].[Quarter]))

But for 2007 for example this is Empty as Q4 has no data.

I know there must be a simple way to return the Last NON EMPTY Quarter's balance but I am having a fog on it....

Any help or link to an example would be great!

Will

Maybe this will work:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmpty([Time].[Quarter].[Quarter],

[Measures].[Total Square Area])).Item(0))

|||

Hi Deepak,

Unfortunately that returns an error

"Token Error, Syntax is not valid"

I figured it must be close though

|||

Well, here's a sample Adventure Works query, based on the above code snippet, which returns the last populated Quarter Exchange Rate for DeutscheMarks in FY2002 - a value of .49 in FY2002Q2 (after which the Euro replaced the DM):

Code Snippet

With

Member [Measures].[QuarterExchange] as

([Measures].[Average Rate],

Tail(NonEmpty(

[Date].[Fiscal Quarter of Year].[Fiscal Quarter of Year],

[Measures].[Average Rate])).Item(0)),

FORMAT_STRING = '#.00'

select

{[Measures].[QuarterExchange]} on 0

from [Adventure Works]

where ([Date].[Fiscal].[Fiscal Year].&[2002],

[Destination Currency].[Destination Currency].&[Deutsche Mark])

-

QuarterExchange
.49

|||I can run that in 2005 against AdventureWorks, however I need this to work in SSAS 2000. It does not seem to like NONEMPTY in 2000 - is this new & was there a workaround to get the same result prior to 2005 SSAS?|||

Hi Will,

Yes, this is new in AS 2005 - in AS 2000, you could try NonEmptyCrossJoin(), like:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmptyCrossJoin([Time].[Quarter].Members,

{[Measures].[Total Square Area]}, 1)).Item(0).Item(0))

For future reference, you might want to mention explicitly that you're asking a question specific to AS 2000, given that SQL Server 2005 was launched nearly 2 years ago, and we're on the threshold of SQL Server 2008

MDX: NonEmpty, Exists and evil NonEmptyCrossJoin

|||

Hi,

Thanks - sorry about the confusion with versions...

I was using 2005 at my old place. No I am 2005 for SSIS & SSRS but they won't budge from 2000 for AS just yet so it's a real pain & I tend to forget to mention it.

Sorry, & thanks for the help

Last Populated Quarter''s Data

Currently I have the following calculation to get the closing Inventory balance for a year

Code Snippet

([Measures].[Total Square Area], ClosingPeriod([Time].[Quarter].[Quarter]))

But for 2007 for example this is Empty as Q4 has no data.

I know there must be a simple way to return the Last NON EMPTY Quarter's balance but I am having a fog on it....

Any help or link to an example would be great!

Will

Maybe this will work:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmpty([Time].[Quarter].[Quarter],

[Measures].[Total Square Area])).Item(0))

|||

Hi Deepak,

Unfortunately that returns an error

"Token Error, Syntax is not valid"

I figured it must be close though

|||

Well, here's a sample Adventure Works query, based on the above code snippet, which returns the last populated Quarter Exchange Rate for DeutscheMarks in FY2002 - a value of .49 in FY2002Q2 (after which the Euro replaced the DM):

Code Snippet

With

Member [Measures].[QuarterExchange] as

([Measures].[Average Rate],

Tail(NonEmpty(

[Date].[Fiscal Quarter of Year].[Fiscal Quarter of Year],

[Measures].[Average Rate])).Item(0)),

FORMAT_STRING = '#.00'

select

{[Measures].[QuarterExchange]} on 0

from [Adventure Works]

where ([Date].[Fiscal].[Fiscal Year].&[2002],

[Destination Currency].[Destination Currency].&[Deutsche Mark])

-

QuarterExchange
.49

|||I can run that in 2005 against AdventureWorks, however I need this to work in SSAS 2000. It does not seem to like NONEMPTY in 2000 - is this new & was there a workaround to get the same result prior to 2005 SSAS?|||

Hi Will,

Yes, this is new in AS 2005 - in AS 2000, you could try NonEmptyCrossJoin(), like:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmptyCrossJoin([Time].[Quarter].Members,

{[Measures].[Total Square Area]}, 1)).Item(0).Item(0))

For future reference, you might want to mention explicitly that you're asking a question specific to AS 2000, given that SQL Server 2005 was launched nearly 2 years ago, and we're on the threshold of SQL Server 2008

MDX: NonEmpty, Exists and evil NonEmptyCrossJoin

|||

Hi,

Thanks - sorry about the confusion with versions...

I was using 2005 at my old place. No I am 2005 for SSIS & SSRS but they won't budge from 2000 for AS just yet so it's a real pain & I tend to forget to mention it.

Sorry, & thanks for the help

Last child of all months

Hey everybody..
I need a little help getting the correct info out of my cube. I have
a cube that stores the balance for every day over the year.
What I need is to get the balance for every last day of every month
Ill describe my cube a bit. I have a dimention that contains the
various systems used (AH.SP,IG... ), a dimention Time (year, month,
day) and
the Measures. (balance, count)
What I need is to get the balance for jan.31, feb.29, mar 31.. and so
forth. For every system. So it would look something like
jan feb mar apr mai
AH 54 4 43 43 43
SP 43 3 45 56 56
IG 3 5 6 6 6
Thanks for any and all help
Arnar
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/OLAP-child-...pict207887.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz
.com/eform.php?p=709627http://groups-beta.google.com/group...erver.olap/msg/
50d474b9b11d21ff[vbcol=seagreen]
Newsgroups: microsoft.public.sqlserver.olap
From: Deepak Puri <deepak_p...@.progressive.com> Date: Wed, 16 Mar 2005
14:51:42 -0800
Subject: Re: multiple entries with dates
There are well-known techniques for dealing with semi-additive
"snapshot" measures, which are discussed in this MSDN paper. Please note
that Analysis Services 2005 now has built-in balances:
http://msdn.microsoft.com/libr_ary/...ry/e_n-us/dnsq.
.
Analysis Services: Semiadditive Measures and Inventory Snapshots
Amir Netz
Microsoft Corporation
Updated May 18, 2004
Applies to:
Microsoft SQL Server 2000
Microsoft SQL Server 2000 Analysis Services
Summary: Focusing on a classic inventory problem, this article describes
the implementation techniques of semiadditive measures in online
analytical processing.
.
http://www.microsoft.com/techn_et/p...alua_te/dwsqlsy
.m
spx
.
One of the greatest arguments for using an analytical server such as
Analysis Services is the ability to define complex calculations
centrally. Analysis Services has always delivered rich analytics, but
some complex concepts have been difficult to implement.
One such concept is that of a semi-additive measure. Most common
measures, such as [Sales], aggregate cleanly along all dimensions:
[Total Sales] for all time is the sales for all products, for all
customers, and for all time. A semi-additive measure, by contrast, may
be additive in some dimensions but not in others. The most common
scenario is a balance, such as the number of items in a warehouse. The
aggregate balance for yesterday plus today is not, of course, the sum of
yesterday's balance plus today's balance. Instead it's probably the
ending balance, although in some scenarios it is the beginning balance.
In Analysis Services 2000 you would have to define a complex MDX
calculation to deliver the correct measure. With Analysis Services 2005,
beginning and end balances are native aggregation types.
.[vbcol=seagreen]
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!