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

LAST_ALTERED Stored Procedure

Hi,
Is there anyway to find out the last altered date of a specific stored
procedure.
I tried Information_Schema.Routines but the value in that column doesn't
change after I modify the stored procedure.
When I look at BOL, it said "The last time the function was modified"
How to find out the date of all last_altered sp then'
Thanks
EdmundYou can't. SQL doesn't store this information.
Drop and recreate the procedure.
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:F9223ED0-2791-4CCC-9A6D-6A3BAC640D10@.microsoft.com...
> Hi,
> Is there anyway to find out the last altered date of a specific stored
> procedure.
> I tried Information_Schema.Routines but the value in that column doesn't
> change after I modify the stored procedure.
> When I look at BOL, it said "The last time the function was modified"
> How to find out the date of all last_altered sp then'
> Thanks
> Edmund
>|||what is the LAST_ALTERED column for under Information_Schema.Routines since
I
don't see the value of this column can be changed!!!!
Ed
"Raymond D'Anjou" wrote:

> You can't. SQL doesn't store this information.
> Drop and recreate the procedure.
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:F9223ED0-2791-4CCC-9A6D-6A3BAC640D10@.microsoft.com...
>
>|||These views are defined by ANSI SQL, to they "have to" be present in SQL Ser
ver whether the
information is actually available or not. Read the source for the view and y
ou see that it actually
displays creation date. Books Online is not correct, though, as it states it
is last time altered...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:28240995-CC07-4103-A1F7-6992939B7035@.microsoft.com...
> what is the LAST_ALTERED column for under Information_Schema.Routines sinc
e I
> don't see the value of this column can be changed!!!!
> Ed
> "Raymond D'Anjou" wrote:
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23kFf4e%23WFHA.2124@.TK2MSFTNGP14.phx.gbl...
> These views are defined by ANSI SQL, to they "have to" be present in SQL
> Server whether the information is actually available or not. Read the
> source for the view and you see that it actually displays creation date.
> Books Online is not correct, though, as it states it is last time
> altered...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
Funny.
It's as if I told my client:
I'm obligated to give you a report of your accounts receivables
The amounts are wrong, but you have your report.|||It is probably just a TBD that just fell through the cracks. I once tried to
add a trigger to the sysobjects table that would update the column with
getdate() when a record where xtype='P' is inserted or updated, but this is
not allowed.
"Raymond D'Anjou" <rdanjou@.savantsoftNOSPAM.net> wrote in message
news:uAQmiv%23WFHA.2692@.TK2MSFTNGP15.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:%23kFf4e%23WFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Funny.
> It's as if I told my client:
> I'm obligated to give you a report of your accounts receivables
> The amounts are wrong, but you have your report.
>|||LOL... I agree. It would be better to display NULL and document it properly.
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Raymond D'Anjou" <rdanjou@.savantsoftNOSPAM.net> wrote in message
news:uAQmiv%23WFHA.2692@.TK2MSFTNGP15.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%23kFf4e%23WFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Funny.
> It's as if I told my client:
> I'm obligated to give you a report of your accounts receivables
> The amounts are wrong, but you have your report.
>|||You DO know that anything, including SELECTs, on System tables are
unsupported.
That's because these tables could change in any upgrade or service pack.
"JT" <someone@.microsoft.com> wrote in message
news:%23jNUtK$WFHA.3140@.TK2MSFTNGP14.phx.gbl...
> It is probably just a TBD that just fell through the cracks. I once tried
> to
> add a trigger to the sysobjects table that would update the column with
> getdate() when a record where xtype='P' is inserted or updated, but this
> is
> not allowed.
> "Raymond D'Anjou" <rdanjou@.savantsoftNOSPAM.net> wrote in message
> news:uAQmiv%23WFHA.2692@.TK2MSFTNGP15.phx.gbl...
> in
>|||Technically, the SELECT against the system tables is supported, as long as y
ou don't derive
information from columns that aren't documented. But the intent if correct,
the structure of the
system tables will change in next version. The will be "replaced" by catalog
views, but still exist
for backwards compatibility and most code will run without changes (assuming
reserved and
non-documented columns has been used).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Raymond D'Anjou" <rdanjou@.savantsoftNOSPAM.net> wrote in message
news:uXlGE8GXFHA.3584@.TK2MSFTNGP14.phx.gbl...
> You DO know that anything, including SELECTs, on System tables are unsuppo
rted.
> That's because these tables could change in any upgrade or service pack.
> "JT" <someone@.microsoft.com> wrote in message news:%23jNUtK$WFHA.3140@.TK2M
SFTNGP14.phx.gbl...
>|||Thanks Tibor.
I've gone over to the dark side a few times and directly queried system
tables but I've never included these in production code.
Off the top of your head, do you know of any system table information that
cannot be obtained by using "Information schema views" or "System stored
procedures".
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uGWA6rHXFHA.2540@.tk2msftngp13.phx.gbl...
> Technically, the SELECT against the system tables is supported, as long as
> you don't derive information from columns that aren't documented. But the
> intent if correct, the structure of the system tables will change in next
> version. The will be "replaced" by catalog views, but still exist for
> backwards compatibility and most code will run without changes (assuming
> reserved and non-documented columns has been used).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Raymond D'Anjou" <rdanjou@.savantsoftNOSPAM.net> wrote in message
> news:uXlGE8GXFHA.3584@.TK2MSFTNGP14.phx.gbl...
>

last value of database

i want to get the last value of the database and the database look like this

table name prospec
TranID data item
1 02/02/2004 2
2 02/02/2004 2
3 02/02/2004 3
4 02/02/2004 3
i want to get the value (4) which auto generate by itself
i try "select TranID from prospec order by TranID desc limit 1"
then when i run it then this error came out "incorrect syntax new 'limit' "Originally posted by mrbear
i want to get the last value of the database and the database look like this

table name prospec
TranID data item
1 02/02/2004 2
2 02/02/2004 2
3 02/02/2004 3
4 02/02/2004 3
i want to get the value (4) which auto generate by itself
i try "select TranID from prospec order by TranID desc limit 1"
then when i run it then this error came out "incorrect syntax new 'limit' "

What type of database are you using?|||Originally posted by ika
What type of database are you using?
i'm using sqlserver databse|||Originally posted by mrbear
i want to get the last value of the database and the database look like this

table name prospec
TranID data item
1 02/02/2004 2
2 02/02/2004 2
3 02/02/2004 3
4 02/02/2004 3
i want to get the value (4) which auto generate by itself
i try "select TranID from prospec order by TranID desc limit 1"
then when i run it then this error came out "incorrect syntax new 'limit' " For MS-SQL 7.0 or later, you probably want:SELECT TOP 1 TranID
FROM prospec
ORDER BY TranID DESCYou can actually get the same result much more portably by using:SELECT Max(TranID)
FROM prospecThis should work on any database that supports SQL-89 or later.

-PatP|||Originally posted by Pat Phelan
For MS-SQL 7.0 or later, you probably want:SELECT TOP 1 TranID
FROM prospec
ORDER BY TranID DESCYou can actually get the same result much more portably by using:SELECT Max(TranID)
FROM prospecThis should work on any database that supports SQL-89 or later.

-PatP

In SQL 7 or later, you also want:

SELECT
MAX(TranID)
FROM
prospec|||thanks guys, the statment works. Thanks a million times|||Originally posted by derrickleggett
In SQL 7 or later, you also want:

SELECT
MAX(TranID)
FROM
prospec also?

isn't that what Pat said? you even quoted him!!!!!!

and what's up with the "SQL 7 or later" qualification??

are you saying select max(foo) from bar won't work in 6.5??

whoasql

Last row ?

Hello Team

How can i get a value from a column in the last row

for ex: select orderno from orders .......... but the last row ?

Thanks lot

Greetings,

SQL Server has no concept of last row, or first row or middle row for that matter.

To determine the last row you need to know by what criteria. If your Order Number column is sequential or your table contains a datetime column then you can get a close approximation by using TOP and ORDER BY.

SELECT TOP 1 OrderNo FROM Orders ORDER BY OrderDate DESC

If there is no column in the table from which you can determine the correct ordering then you're stuck.

--

Regards,
Neale NOON

|||

There is one more work around available to get the last record..

if your table has 3 columns,

declare @.col1 as varchar(100);
declare @.col2 as varchar(100);
declare @.col3 as varchar(100);

Select @.Co1l=Col1,
@.Col2=Col2,
@.Col3=Col3
From
Tablename;

Select @.Col1 as Col1, @.Col2 as Col2, @.Col3 as Col3;

So here you need not to use any order by clause. It always fet the last row.

|||If you are really just looking for the last row, reverse your sort order and use SELECT TOP 1 ... That will get you the first row in the reversed order, ie last row.

Last record ?

Hello Team

i want to get a value from a column in the last record

i wrote select ID from (select top 1 * desc from items)

any idea, Thanks lot

If you want to use top you have to use order by clause with desc to fetch the last record..

Code Snippet

Select Top 1 Id from Demo Order By Id Desc

or

Select max(id) from Demo;

Another easy way is,

Code Snippet

Declare @.Id as int;

Select @.ID = ID from Demo;

Select @.ID;

here it always return the last record

|||

Just select from the table ordering by the field that can be used to identify if this is a recent row or not.

Code Snippet

Select Top 1 ID

From Items

Order By ID Desc

Assuming ID is a column that is incremental for each new row. If you don't have an incremental ID, you can use a column that contains the date when the row has been added.

I hope this answers your question. If not, please post more information about the table.

Best regards,

Sami Samir

|||

Thank you very much

and Mr samy too

|||If you want to obtain the ID value of the last record inserted to a table, and then use that ID value in additional INSERTS/UPDATES, you may wish to explore using the system function: SCOPE_IDENTITY. It can return the value of the immediately preceding insert WITHOUT having to execute a query.sql

Last period as initial value but still have period as parameter?

Hi all,

I'm using an olap cube in a report (dash board).
The last "actual" period should be viewed when the user opens the report. I'm using the MDX tail function for that.
Once the report is opened the users want to select another period. Then I want to put period as a parameter.
But that is not possible since the parameter check box is no longer available when using a MDX filter expression.

Is there any solution to this problem?

Any help will be appreciated.

If you know MDX, then don't use the query designer and just write your own queries. You'll need to have 2 maybe 3 datasets:

Main query dataset

Last Identity Value

IDENT_CURRENT returns the last identity value generated for a specific table
in any session and any scope.
@.@.IDENTITY returns the last identity value generated for any table in the
current session, across all scopes.
I need the last identity generated for a specific table for the current
session. Looks like I can do one or the other, but not both. Or is there a
way to get it?It shouldn't be a problem. Within the session scope you just need to
preserve the IDENTITY value after the INSERT to the appropriate table.
In SQL Server 2000 SCOPE_IDENTITY is usually preferred to @.@.IDENTITY
because SCOPE_IDENTITY is unaffected by INSERTs in triggers.
Example:
INSERT INTO table1 (...)
..
SET @.t1 = SCOPE_IDENTITY()
INSERT INTO table2 (...)
..
SET @.t2 = SCOPE_IDENTITY()
David Portas
SQL Server MVP
--|||If the table has a "before insert" trigger on it scope_identity wont work.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1130235284.897816.213730@.g14g2000cwa.googlegroups.com...
> It shouldn't be a problem. Within the session scope you just need to
> preserve the IDENTITY value after the INSERT to the appropriate table.
> In SQL Server 2000 SCOPE_IDENTITY is usually preferred to @.@.IDENTITY
> because SCOPE_IDENTITY is unaffected by INSERTs in triggers.
> Example:
> INSERT INTO table1 (...)
> ...
> SET @.t1 = SCOPE_IDENTITY()
>
> INSERT INTO table2 (...)
> ...
> SET @.t2 = SCOPE_IDENTITY()
> --
> David Portas
> SQL Server MVP
> --
>|||If you mean an INSTEAD OF trigger then you are right. Use @.@.IDENTITY in
that instance.
See: http://www.aspfaq.com/show.asp?id=2174
David Portas
SQL Server MVP
--|||Unless you're also dumping data into a Audit table which has an Identity
column.
Then you're up the proverbial ;)
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1130247300.970289.319770@.g14g2000cwa.googlegroups.com...
> If you mean an INSTEAD OF trigger then you are right. Use @.@.IDENTITY in
> that instance.
> See: http://www.aspfaq.com/show.asp?id=2174
> --
> David Portas
> SQL Server MVP
> --
>|||Then you have to look up the value using the alternate key--which I prefer
because it works with both set-based and single-row inserts. It's also
necessary to issue a select to obtain the current rowversion (timestamp), so
there isn't any additional overhead involved in obtaining the IDENTITY
values (provided you're using optimistic concurrency with rowversioning).
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:435e3382$0$141$7b0f0fd3@.mistral.news.newnet.co.uk...
> Unless you're also dumping data into a Audit table which has an Identity
> column.
> Then you're up the proverbial ;)
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1130247300.970289.319770@.g14g2000cwa.googlegroups.com...
>

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 value types out of row - objectproperties

Hi,
I know that i can set "large value types out of row" table option using
"sp_tableoption" but i could not find a way to know if it is enabled or
disabled.
sp_tableoption returns nothing so i thought i could use
objectproperties but there doesn't seem to be any property name
documented for "large value types out of row" in msdn.
So, how can i check if it is enabled or not?
Thanks.What version of SQL Server? Object property in 2000 has the TableTextInRowLi
mit attribute you can
ask for.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<antimon@.gmail.com> wrote in message news:1141611920.307183.105940@.v46g2000cwv.googlegroups
.com...
> Hi,
> I know that i can set "large value types out of row" table option using
> "sp_tableoption" but i could not find a way to know if it is enabled or
> disabled.
> sp_tableoption returns nothing so i thought i could use
> objectproperties but there doesn't seem to be any property name
> documented for "large value types out of row" in msdn.
> So, how can i check if it is enabled or not?
> Thanks.
>|||It is sql server 2005. TableTextInRowLimit works fine but i want to
learn if the option that forces large value types (char(MAX),
varchar(MAX)..) to be stored in seperated pages even if it is a small
piece of data is turned on or not.|||> It is sql server 2005.
Please include version number upfront, makes the whole process quicker... :-
)
Seems like 2005 doesn't expose text in row for the new datatypes. The sys.ta
bles catalog view does,
however...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<antimon@.gmail.com> wrote in message news:1141656316.186672.224600@.j52g2000cwj.googlegroups
.com...
> It is sql server 2005. TableTextInRowLimit works fine but i want to
> learn if the option that forces large value types (char(MAX),
> varchar(MAX)..) to be stored in seperated pages even if it is a small
> piece of data is turned on or not.
>|||I thought i had it in my first post :) sorry about that.
And thanks for the solution, sys.tables works great.

large value data type (nvarchar(max))

hi,

I have inserted a new column in my db table (type: nvarchar(max))
now I can't process the cube anymore ... is there a possibility to store large text files in the cube?

thanks,

rhapsy

Hi Rhapsy

What are you trying to achieve with this data? Would it not be easier to try and have an action to get that text if it is needed?

Regards

Mike

|||hey mike,

probably it would be easier, but I'd like to get all my information from the same source ...
and I'm a little bit curious if thias is possible ...

thanks,
rhapsy