Getting a ROLAP cube to refresh when a dimension changes

---

Have 1 ROLAP cube in an AS DB with other MOLAP cubes. The ROLAP cube is derived from a SQL database view and uses dimensions that are also used by the MOLAP cubes. The aggregation storage of the source view is defined as ROLAP. Updates to the Fact table cause the ROLAP cube to refresh while updates to the dimensions do not cause a refresh of the ROLAP cube.

When for the ROLAP cube ,set storage mode to rolap
and in proactive caching, change settings to Real-time ROLAP
click on "Options" and make sure Enable proactive caching is marked
if now you change a measure in your fact table you will see the change right away
without processing, but the problem is we can not have the same thing working for dimensions
in the same caching setting in Storage Options enabling the check box "Apply settings to dimensions"
doesn't really solve the problem, when you check off this setting
any changes to the dimensions will not update the cube until the dimensions are processed.

What can be done to enable an update of a dimension used by the ROLAP cube to cause the ROLAP cube to be refreshed without reprocessing the MOLAP cubes also using the dimension?

Thanks

If the same dimensions are used in the other MOLAP cubes, you should keep storage mode of your dimensions as MOLAP.

If you wanted to go with storage mode as ROLAP for all of your dimensions, you should create new dimensions that are going to be used only in this ROLAP cube.

You should also take a look whether sizes of your data will allow you to keep the same level of performance- ROLAP will perform slower in general when compared to MOLAP mode.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights

|||

There should be no need to reprocess the ROLAP cube when you just process the dimension. As long as you are not deleting records from your dimension table, you should just be doing a processAdd or processUpdate on your dimension. This will leave all your cubes in a queryable state and the proactive caching feature should keep your ROLAP partitions "fresh" as new facts arrive.

If you have been doing a processFull on your dimension, it will have been clearing out all the cubes that use the dimension in question as a processFull rebuilds the dimension structure from scratch which requires that dependant cubes have their storage structures rebuilt also.

Getting a list of user access to which databases - help

Hi ,
i know there's a view "sxyslogin" in Master database that is able to show a
list of user with the default database that they have access to
however , i like to get a list of users with all the databases that they are
able to access, how can i do that with the rights that they have in these
databases as well ?
for example userA has access to DB1 , DB4 , DB5 , i need to show that userA
has the access to these users
appreciate any advise
tks & rdgs
Hi,
Execute the system procedure sp_helplogin to get all the users with
associated access to databases.
For displaying object level previlages for the user , execute sp_helprotect.
See the reference of both procedures in books online.
Thanks
Hari
SQL Server MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:455416BC-1ED3-4F69-B9DB-E17A078A7C79@.microsoft.com...
> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show
a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they
are
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that
userA
> has the access to these users
> appreciate any advise
> tks & rdgs
|||You can check the stored procedure sp_helplogins
best Regards,
Chandra
http://chanduas.blogspot.com/
"maxzsim" wrote:

> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they are
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that userA
> has the access to these users
> appreciate any advise
> tks & rdgs
|||Here you go this query will match up the sysdatabases which is the list of
databases you need with the sysusers information which will give you a
results set of databases and the user for that database. Then if you want to
go a little further and match that sid with sysxlogins if you need some
information from there. The in clause makes it where you dont have to see
all the user information for sql internal usage.
Hope this helps.
Select a.name,
b.[name],
b.[UID],
b.[SID],
b.[ISSQLROLE],
CASE WHEN b.[ISSQLUSER] = 1
THEN 1
ELSE 0
END AS issqluser
from [master].[dbo].[sysdatabases] a ,
[master].[dbo].[sysusers] b
WHERE b.[name] NOT IN (
'public',
'db_owner',
'db_accessadmin',
'db_securityadmin',
'db_ddladmin',
'db_backupoperator',
'db_datareader',
'db_datawriter',
'db_denydatareader',
'db_denydatawriter',
'dbo',
'guest',
'INFORMATION_SCHEMA',
'system_function_schema'
)
"maxzsim" wrote:

> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they are
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that userA
> has the access to these users
> appreciate any advise
> tks & rdgs

Getting a list of user access to which databases - help

Hi ,
i know there's a view "sxyslogin" in Master database that is able to show a
list of user with the default database that they have access to
however , i like to get a list of users with all the databases that they are
able to access, how can i do that with the rights that they have in these
databases as well ?
for example userA has access to DB1 , DB4 , DB5 , i need to show that userA
has the access to these users
appreciate any advise
tks & rdgsHi,
Execute the system procedure sp_helplogin to get all the users with
associated access to databases.
For displaying object level previlages for the user , execute sp_helprotect.
See the reference of both procedures in books online.
Thanks
Hari
SQL Server MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:455416BC-1ED3-4F69-B9DB-E17A078A7C79@.microsoft.com...
> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show
a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they
are
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that
userA
> has the access to these users
> appreciate any advise
> tks & rdgs|||You can check the stored procedure sp_helplogins
--
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"maxzsim" wrote:
> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they are
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that userA
> has the access to these users
> appreciate any advise
> tks & rdgs|||Here you go this query will match up the sysdatabases which is the list of
databases you need with the sysusers information which will give you a
results set of databases and the user for that database. Then if you want to
go a little further and match that sid with sysxlogins if you need some
information from there. The in clause makes it where you dont have to see
all the user information for sql internal usage.
Hope this helps.
Select a.name,
b.[name],
b.[UID],
b.[SID],
b.[ISSQLROLE],
CASE WHEN b.[ISSQLUSER] = 1
THEN 1
ELSE 0
END AS issqluser
from [master].[dbo].[sysdatabases] a ,
[master].[dbo].[sysusers] b
WHERE b.[name] NOT IN (
'public',
'db_owner',
'db_accessadmin',
'db_securityadmin',
'db_ddladmin',
'db_backupoperator',
'db_datareader',
'db_datawriter',
'db_denydatareader',
'db_denydatawriter',
'dbo',
'guest',
'INFORMATION_SCHEMA',
'system_function_schema'
)
"maxzsim" wrote:
> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they are
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that userA
> has the access to these users
> appreciate any advise
> tks & rdgs

Getting a list of user access to which databases - help

Hi ,
i know there's a view "sxyslogin" in Master database that is able to show a
list of user with the default database that they have access to
however , i like to get a list of users with all the databases that they are
able to access, how can i do that with the rights that they have in these
databases as well ?
for example userA has access to DB1 , DB4 , DB5 , i need to show that userA
has the access to these users
appreciate any advise
tks & rdgsHi,
Execute the system procedure sp_helplogin to get all the users with
associated access to databases.
For displaying object level previlages for the user , execute sp_helprotect.
See the reference of both procedures in books online.
Thanks
Hari
SQL Server MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:455416BC-1ED3-4F69-B9DB-E17A078A7C79@.microsoft.com...
> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show
a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they
are
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that
userA
> has the access to these users
> appreciate any advise
> tks & rdgs|||You can check the stored procedure sp_helplogins
--
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"maxzsim" wrote:

> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show
a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they a
re
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that user
A
> has the access to these users
> appreciate any advise
> tks & rdgs|||Here you go this query will match up the sysdatabases which is the list of
databases you need with the sysusers information which will give you a
results set of databases and the user for that database. Then if you want t
o
go a little further and match that sid with sysxlogins if you need some
information from there. The in clause makes it where you dont have to see
all the user information for sql internal usage.
Hope this helps.
Select a.name,
b.[name],
b.[UID],
b.[SID],
b.[ISSQLROLE],
CASE WHEN b.[ISSQLUSER] = 1
THEN 1
ELSE 0
END AS issqluser
from [master].[dbo].[sysdatabases] a ,
[master].[dbo].[sysusers] b
WHERE b.[name] NOT IN (
'public',
'db_owner',
'db_accessadmin',
'db_securityadmin',
'db_ddladmin',
'db_backupoperator',
'db_datareader',
'db_datawriter',
'db_denydatareader',
'db_denydatawriter',
'dbo',
'guest',
'INFORMATION_SCHEMA',
'system_function_schema'
)
"maxzsim" wrote:

> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show
a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they a
re
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that user
A
> has the access to these users
> appreciate any advise
> tks & rdgs

GetDate() in User Defined Functions, Parameters in View

1) Am I right in believing I can't access the GetDate() function from within
a table valued UDF ... ? That seems to be what the syntax checker is
telling me. Any suggested workarounds ?
2) I tried create a view which used a table valued UDF, with " getdate() "
as a parameter, in the view's from clause and the system didn't like that
either ... Any suggestions ? Are there ways of parameterizing a view ?
3) I need the view construct because I need to reference the returned
dataset from Analysis Services as a dimension. I prefer the UDF construct
(as opposed to selecting off a table with a where clause) because I don't
have to build a process to add new date records to the hypothetical table.
Performance isn't really an issue, because the only place the view is invoke
d
is in processing a cube in Analysis Services.
4) Some SQL
...
CREATE FUNCTION dbo.tfun_Date (
@.EndDate smalldatetime) --added when getdate didn't work
RETURNS @.DateTable table (
DateValue smalldatetime )
BEGIN
Declare @.DateIdx smalldatetime
Declare @.StartDate smalldatetime
Set @.StartDate = dbo.sfun_getdateparmref('DateDim1Start')
Set @.DateIdx = @.StartDate
--Set @.EndDate = getdate()
While @.DateIdx <= @.EndDate
Begin
Insert @.DateTable (DateValue) values (@.DateIdx)
Set @.DateIdx = DateAdd(dd,1, @.DateIdx)
End
Return
END
--
CREATE VIEW dbo.v_date
AS
SELECT top 10000 DateValue
, DateYYYY = Datepart(yyyy, DateValue)
, DateYYAbbrev = RIGHT(CONVERT(Char(4), Datepart(yy, DateValue)), 2)
, DateQtr = CASE Datepart(mm, DateValue)
WHEN 1 THEN 'Q1' WHEN 2 THEN 'Q1' WHEN 3 THEN 'Q1'
WHEN 4 THEN 'Q2' WHEN 5 THEN 'Q2' WHEN 6 THEN 'Q2' WHEN 7 THEN 'Q3' WHEN 8
THEN
'Q3' WHEN 9 THEN 'Q3' WHEN 10 THEN 'Q4' WHEN 11 THEN
'Q4' WHEN 12 THEN 'Q4' ELSE 'Er' END
, DateMM = Datepart(mm, DateValue)
, DateMMAbbrev = CASE Datepart(mm, DateValue)
WHEN 1 THEN 'Jan' WHEN 2 THEN 'Feb' WHEN 3 THEN 'Mar'
WHEN 4 THEN 'Apr' WHEN 5 THEN 'May' WHEN 6 THEN 'Jun' WHEN 7 THEN 'Jul' WHEN
8 THEN 'Aug' WHEN 9 THEN 'Sep' WHEN 10 THEN 'Oct'
WHEN 11 THEN 'Nov' WHEN 12 THEN 'Dec' ELSE 'Err' END
, DateDD = Datepart(dd,
DateValue), DowNbr = Datepart(dw, DateValue)
, DowAbbr = CASE Datepart(dw, DateValue)
WHEN 1 THEN 'Mon' WHEN 2 THEN 'Tue' WHEN 3 THEN 'Wed'
WHEN 4 THEN 'Thu' WHEN 5 THEN 'Fri' WHEN 6 THEN 'Sat' WHEN 7 THEN 'Sun' ELSE
'Err' END
, DayOfYear = DateDiff(d, CONVERT(smalldatetime, CONVERT(char(4),
Datepart(yyyy, DateValue)) + '01' + '01', 112), DateValue) + 1
, WeekOfYear = 1 + (DateDiff(d, CONVERT(smalldatetime, CONVERT(char(4),
Datepart(yyyy, DateValue)) + '01' + '01', 112), DateValue) + 7 - Datepart(dw
,
DateValue)) / 7
, WeekOfYearMondayDate = dateadd(dd, 1 - Datepart(dw, DateValue), DateValue)
, Workday = Case
When Datepart(dw, DateValue) > 5 then 'Weekend / Holiday'
When PublicHolidayFlag = 'H' then 'Weekend / Holiday'
Else 'Workday' End
, HolidayName
, PublicHolidayFlag
--THIS IS THE BOGUS LINE
FROM tfun_date( getdate() ) D
--ENDS HERE
Left outer Join ZR_PublicHols on D.DateValue = ZR_PublicHols.HolidayDate
order by DateValueYou could pass in to the UDF a new parameter which when called, you send it
the GETDATE/CURRENTTIMESTAMP function.
Then just use that new parameter as your getdate()
hth
Eric
MarcusW wrote:
> 1) Am I right in believing I can't access the GetDate() function from
> within a table valued UDF ... ? That seems to be what the syntax
> checker is telling me. Any suggested workarounds ?
> 2) I tried create a view which used a table valued UDF, with "
> getdate() " as a parameter, in the view's from clause and the system
> didn't like that either ... Any suggestions ? Are there ways of
> parameterizing a view ?
> 3) I need the view construct because I need to reference the returned
> dataset from Analysis Services as a dimension. I prefer the UDF
> construct (as opposed to selecting off a table with a where clause)
> because I don't have to build a process to add new date records to
> the hypothetical table. Performance isn't really an issue, because
> the only place the view is invoked is in processing a cube in
> Analysis Services.
> 4) Some SQL
> ...
> CREATE FUNCTION dbo.tfun_Date (
> @.EndDate smalldatetime) --added when getdate didn't work
> RETURNS @.DateTable table (
> DateValue smalldatetime )
> BEGIN
> Declare @.DateIdx smalldatetime
> Declare @.StartDate smalldatetime
> Set @.StartDate = dbo.sfun_getdateparmref('DateDim1Start')
> Set @.DateIdx = @.StartDate
> --Set @.EndDate = getdate()
> While @.DateIdx <= @.EndDate
> Begin
> Insert @.DateTable (DateValue) values (@.DateIdx)
> Set @.DateIdx = DateAdd(dd,1, @.DateIdx)
> End
> Return
> END
> --
> CREATE VIEW dbo.v_date
> AS
> SELECT top 10000 DateValue
> , DateYYYY = Datepart(yyyy, DateValue)
> , DateYYAbbrev = RIGHT(CONVERT(Char(4), Datepart(yy, DateValue)), 2)
> , DateQtr = CASE Datepart(mm, DateValue)
> WHEN 1 THEN 'Q1' WHEN 2 THEN 'Q1' WHEN 3 THEN
> 'Q1' WHEN 4 THEN 'Q2' WHEN 5 THEN 'Q2' WHEN 6 THEN 'Q2' WHEN 7 THEN
> 'Q3' WHEN 8 THEN
> 'Q3' WHEN 9 THEN 'Q3' WHEN 10 THEN 'Q4' WHEN
> 11 THEN 'Q4' WHEN 12 THEN 'Q4' ELSE 'Er' END
> , DateMM = Datepart(mm, DateValue)
> , DateMMAbbrev = CASE Datepart(mm, DateValue)
> WHEN 1 THEN 'Jan' WHEN 2 THEN 'Feb' WHEN 3 THEN
> 'Mar' WHEN 4 THEN 'Apr' WHEN 5 THEN 'May' WHEN 6 THEN 'Jun' WHEN 7
> THEN 'Jul' WHEN 8 THEN 'Aug' WHEN 9 THEN 'Sep'
> WHEN 10 THEN 'Oct'
> WHEN 11 THEN 'Nov' WHEN 12 THEN 'Dec' ELSE 'Err' END
> , DateDD = Datepart(dd,
> DateValue), DowNbr = Datepart(dw, DateValue)
> , DowAbbr = CASE Datepart(dw, DateValue)
> WHEN 1 THEN 'Mon' WHEN 2 THEN 'Tue' WHEN 3 THEN
> 'Wed' WHEN 4 THEN 'Thu' WHEN 5 THEN 'Fri' WHEN 6 THEN 'Sat' WHEN 7
> THEN 'Sun' ELSE 'Err' END
> , DayOfYear = DateDiff(d, CONVERT(smalldatetime, CONVERT(char(4),
> Datepart(yyyy, DateValue)) + '01' + '01', 112), DateValue) + 1
> , WeekOfYear = 1 + (DateDiff(d, CONVERT(smalldatetime,
> CONVERT(char(4), Datepart(yyyy, DateValue)) + '01' + '01', 112),
> DateValue) + 7 - Datepart(dw, DateValue)) / 7
> , WeekOfYearMondayDate = dateadd(dd, 1 - Datepart(dw, DateValue),
> DateValue) , Workday = Case
> When Datepart(dw, DateValue) > 5 then 'Weekend / Holiday'
> When PublicHolidayFlag = 'H' then 'Weekend / Holiday'
> Else 'Workday' End
> , HolidayName
> , PublicHolidayFlag
> --THIS IS THE BOGUS LINE
> FROM tfun_date( getdate() ) D
> --ENDS HERE
> Left outer Join ZR_PublicHols on D.DateValue =
> ZR_PublicHols.HolidayDate order by DateValue|||Marcus

> 1) Am I right in believing I can't access the GetDate() function from
within
> a table valued UDF ... ? That seems to be what the syntax checker is
> telling me. Any suggested workarounds ?
Correct. However, you can create a view that has the GetDate() in it and
then call that view from your UDF. This workaround may or may not continue
to work in future versions.

> 2) I tried create a view which used a table valued UDF, with " getdate() "
> as a parameter, in the view's from clause and the system didn't like that
> either ... Any suggestions ? Are there ways of parameterizing a view ?
An in-line table-valued UDF is a parameterized view. I think the problem
is still the GetDate() which cannot be a parameter to a UDF either. (Why
not? Because. Technically it is the issue of whether a function always
returns the same value or not.)
Russell Fields

getdate() causes locks in views

Hello,

I have a problem with a view. The view reads as:

create view v_lds
as
select * from lds where dsdate > getdate() - 14

The idea being that the view only shows data which has modified within
the last 2 weeks. It works fine when viewing in through any tool.

However, when I have one application read from the view and a
different application write to the lds table directly, a dead lock
occurs.

If I modify the view to read:

create view v_lds
as
select * from lds where dsdate > '2003-08-15'

The problem disappears.

Any help would be much appreciated.

Thanks,

Allan Martin[posted and mailed, please reply in news]

Allan Martin (allanmartin@.ntlworld.com) writes:
> I have a problem with a view. The view reads as:
> create view v_lds
> as
> select * from lds where dsdate > getdate() - 14
> The idea being that the view only shows data which has modified within
> the last 2 weeks. It works fine when viewing in through any tool.
> However, when I have one application read from the view and a
> different application write to the lds table directly, a dead lock
> occurs.
> If I modify the view to read:
> create view v_lds
> as
> select * from lds where dsdate > '2003-08-15'
> The problem disappears.

Not much to work from, I'm afraid.

What is likely to make a difference is that when you say

select * from lds where dsdate > '2003-08-15'

The optimizer can determine from the statistics how many rows in
lds it will hit, and therefore decide whether to use a non-clustered
index on that column or not. (You don't give any details on indexing,
but from the behaviour I guess that there such an index.)

When you instead put in an expression "getdate() - 14", SQL Server
does no longer have a value for it when building the query plan,
so it prefers to scan the table from left to right instead. This is
because, if many rows passes the where clause it would be slower
to use the index.

You should probably look into your index strategy. Many the index
on lds.dsdate should be clustered. But for a quick fix, you might
save the situation with an index hint to force use of the index
on lds.dsdate:

SELECT * FROM lds WITH (INDEX = lds_dsdate_ix) WHERE dsdate ...

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Yo can also try creating a row in a table with single column indexed
with getdate()-14 and convert the query as a join

Srinivas

allanmartin@.ntlworld.com (Allan Martin) wrote in message news:<a6d765d6.0309230557.130e1f18@.posting.google.com>...
> Hello,
> I have a problem with a view. The view reads as:
> create view v_lds
> as
> select * from lds where dsdate > getdate() - 14
> The idea being that the view only shows data which has modified within
> the last 2 weeks. It works fine when viewing in through any tool.
> However, when I have one application read from the view and a
> different application write to the lds table directly, a dead lock
> occurs.
> If I modify the view to read:
> create view v_lds
> as
> select * from lds where dsdate > '2003-08-15'
> The problem disappears.
> Any help would be much appreciated.
> Thanks,
> Allan Martin

Get user syntax

I am building a view for a report and one of the requirements is to grab the
user who is running the report. What would this syntax look like? I thought
it was getuser(), but no luck there.
Thanks,
Ryan
Ryan,
Depending on whether you want a User or a Login, you can try one of the
following: CURRENT_USER, SYSTEM_USER, USER_NAME, SUSER_SNAME. Check them
out in the Books Online, or just run them and choose what you prefer to use.
RLF
"Ryan Mcbee" <RyanMcbee@.discussions.microsoft.com> wrote in message
news:266EBEEE-3FB5-448D-A58B-0EF6D8F006F1@.microsoft.com...
>I am building a view for a report and one of the requirements is to grab
>the
> user who is running the report. What would this syntax look like? I
> thought
> it was getuser(), but no luck there.
> Thanks,
> Ryan

Get the refrences Typ

Hallo, i create a view that show me the all relations (sysreferences),
but where can i find the Typ of the relation (if is a cascading deletes and
the other thinks)These relationships are treated and catalog-ed as constraints. You can use
the property name arguments CnstIsUpdateCascade and CnstIsDeleteCascade of
the meta-data function OBJECTPROPERTY to verify the referential associated
with the constraints. Check out the topic OBJECTPROPERTY in SQL Server Books
Online for more details.
Anith

Get The Last Record by Grouping

I have a view listing tickets and reports for those tickets. I want to query LAST REPORT's OPERATOR

SELECT OPERATOR AS EXPR2, NUMBERPRGN, IS_BITIS AS EXPR1
FROM SCADMIN.V_ESKALASYON_2
WHERE (NUMBERPRGN = 'IM1289657')
ORDER BY NUMBERPRGN, IS_BITIS DESC

That query brings the resultset

IM1289657

OGUZY

04.12.2006 14:01:09

IM1289657

MUJDEO

01.12.2006 10:42:30

IM1289657

MUJDEO

28.11.2006 10:58:22

IM1289657

ILKERD

20.11.2006 14:36:12

IM1289657

ILKERD

13.11.2006 16:02:27

IM1289657

ILKERD

07.11.2006 14:02:21

IM1289657

ILKERD

31.10.2006 15:47:56

IM1289657

SINANK

19.10.2006 13:00:00

IM1289657

OGUZY

18.10.2006 17:25:56

Can you help to recover the query sentence above to return only the red marked record (LAST REPORT info written)

Thanks :)

SELECT TOP (1)...|||

Hoops, I have forgotten to say that I use that sentence to query from Oracle (SSIS). And there are lots of ticket numbers. I want to query only the red bold ones from Oracle (the criteria of red bold records is that they are last report for the ticket)

Could you help me?

IM1124672

ARIFOZ

16.11.2006 13:16

IM1124672

ARIFOZ

16.11.2006 13:16

IM1124672

ARIFOZ

26.10.2006 10:11

IM1124672

ARIFOZ

28.09.2006 11:30

IM1124672

ARIFOZ

08.09.2006 13:33

IM1124672

ARIFOZ

17.08.2006 14:18

IM1124672

ARIFOZ

02.08.2006 11:34

IM1124672

ARIFOZ

18.07.2006 08:20

IM1124672

ARIFOZ

04.07.2006 10:02

IM1124672

ARIFOZ

26.06.2006 11:25

IM1241042

ILKERD

28.11.2006 11:17

IM1241042

AYHANK

24.11.2006 10:00

IM1241042

ILKERD

20.11.2006 11:53

IM1241042

AYHANK

17.11.2006 12:10

IM1241042

ILKERD

13.11.2006 15:52

IM1241042

ILKERD

07.11.2006 13:42

IM1241042

ILKERD

30.10.2006 15:23

IM1241042

ILKERD

12.10.2006 11:19

IM1241042

ILKERD

05.10.2006 11:25

IM1241042

ILKERD

28.09.2006 14:47

IM1241042

ILKERD

22.09.2006 15:22

IM1241042

ILKERD

12.09.2006 14:30

IM1241042

ILKERD

07.09.2006 16:28

IM1241042

AYHANK

06.09.2006 12:10

IM1241042

AYHANK

04.09.2006 11:25

IM1251338

ILKERD

28.11.2006 11:22

IM1251338

ILKERD

20.11.2006 12:15

IM1251338

MUJDEO

15.11.2006 12:36

IM1251338

MUJDEO

09.11.2006 15:34

IM1251338

ILKERD

07.11.2006 13:44

IM1251338

ILKERD

30.10.2006 15:28

IM1251338

ILKERD

12.10.2006 11:23

IM1251338

ILKERD

05.10.2006 11:40

IM1251338

ILKERD

28.09.2006 14:57

IM1251338

ILKERD

22.09.2006 15:48

IM1251338

ALPS

15.09.2006 19:20

IM1251338

ALPS

15.09.2006 17:50

IM1251338

ILHANA

14.09.2006 16:24

IM1251338

HAKANM

14.09.2006 13:23

IM1253690

ILKERD

28.11.2006 11:26

IM1253690

ILKERD

13.11.2006 15:54

IM1253690

ILKERD

07.11.2006 13:45

IM1253690

ILKERD

30.10.2006 15:30

IM1253690

ILKERD

12.10.2006 11:46

IM1253690

ILKERD

05.10.2006 13:38

IM1253690

FERHATY

25.09.2006 15:30

IM1253690

ERCAND

23.09.2006 12:00

IM1253690

FERHATY

18.09.2006 15:00

IM1267973

ILKERD

28.11.2006 11:27

IM1267973

ILKERD

20.11.2006 14:11

IM1267973

ILKERD

13.11.2006 15:57

IM1267973

ILKERD

07.11.2006 13:48

IM1267973

ILKERD

30.10.2006 15:34

IM1267973

ILKERD

12.10.2006 12:26

IM1267973

ILKERD

05.10.2006 14:34

|||

I don't know Oracle very well... I hope that this works...

SELECT NUMBERPRGN, OPERATOR, IS_BITIS
FROM SCADMIN.V_ESKALASYON_2 AS Main INNER JOIN
(SELECT NUMBERPRGN, MAX(IS_BITIS) AS Date
FROM SCADMIN.V_ESKALASYON_2
GROUP BY NUMBERPRGN) AS Sub ON Main.NUMBERPRGN = Sub.NUMBERPRGN AND Main.IS_BITIS = Sub.Date
ORDER BY NUMBERPRGN

|||Thanks Lepaca, SQL is SQL (wherever queried). You know PL/SQL also, I think ;-)

Get return value of stored procedure in Query Analyzer

Hi. We've got a stored procedure on SQL Server 2000 with a Return statement,
and sending back a number. Is there a way to view the Return value when
executing this procedure in Query Analyzer? Right now, it's just displaying
how many rows were affected. Thanks.What about a PRINT statement prior to your Return statement?
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"dw" <cougarmana_NOSPAM_@.uncw.edu> wrote in message
news:u3NDF0dSGHA.792@.TK2MSFTNGP10.phx.gbl...
> Hi. We've got a stored procedure on SQL Server 2000 with a Return
statement,
> and sending back a number. Is there a way to view the Return value when
> executing this procedure in Query Analyzer? Right now, it's just
displaying
> how many rows were affected. Thanks.
>|||example
create proc prTestReturnValue
as
select getdate()
return 5
GO
declare @.i int
exec @.i =prTestReturnValue
select @.i
http://sqlservercode.blogspot.com/|||dw,
declare @.rv int
exec @.rv = dbo.p1 ...
select @.rv
go
See "execute" command/statement in BOL.
AMB
"dw" wrote:

> Hi. We've got a stored procedure on SQL Server 2000 with a Return statemen
t,
> and sending back a number. Is there a way to view the Return value when
> executing this procedure in Query Analyzer? Right now, it's just displayin
g
> how many rows were affected. Thanks.
>
>|||Thank you all for the answers. That's what I needed and it worked
beautifully :)
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:AF2BF839-61D5-48FE-9845-11532AFB82CF@.microsoft.com...
> dw,
> declare @.rv int
> exec @.rv = dbo.p1 ...
> select @.rv
> go
> See "execute" command/statement in BOL.
>
> AMB
> "dw" wrote:
>

GET MAX Date of previous year

Dear Friends,

I need to create a new column in a VIEW with a reference date to call from SQL Analysis Services 2005.

FactTable ID Name DateID Date 1 blabla… 8225 16-03-2005 2 blabla… 12-08-2006 … 9999 … … TIME Dimension DateID Day Year MonthKey Month QuarterKey Quarter 8224 15-03-2005 0:00 2005 20053 March 20051 Q1 8225 16-03-2005 0:00 2005 20053 March 20051 Q1 8226 17-03-2005 0:00 2005 20053 March 20051 Q1 8227 18-03-2005 0:00 2005 20053 March 20051 Q1 .. … … … … … … 9321 16-03-2008 0:00 2008 20083 March 20081 Q1 9322 17-03-2008 0:00 2008 20083 March 20081 Q1 9323 18-03-2008 0:00 2008 20083 March 20081 Q1 9324 19-03-2008 0:00 2008 20083 March 20081 Q1 9325 20-03-2008 0:00 2008 20083 March 20081 Q1 9326 21-03-2008 0:00 2008 20083 March 20081 Q1 9327 22-03-2008 0:00 2008 20083 March 20081 Q1 9328 23-03-2008 0:00 2008 20083 March 20081 Q1 9329 24-03-2008 0:00 2008 20083 March 20081 Q1 9330 25-03-2008 0:00 2008 20083 March 20081 Q1 9331 26-03-2008 0:00 2008 20083 March 20081 Q1 9332 27-03-2008 0:00 2008 20083 March 20081 Q1 9333 28-03-2008 0:00 2008 20083 March 20081 Q1 Result ID Name DateID Date RefDate 1 blabla… 8225 16-03-2005 25-12-2005 2 blabla… 12-08-2006 29-12-2006 … 9999 … … …

The refDate is the last VALID date from previous year for each date in each row of FactTable. My TimeTable only has valid dates (does not has holidays, saturday, sunday and forcedHolidays). so I need a query to get for each date in each row of FactTable the last date for previous year. Probably using the parameter "year" of the date in each row in facttable (using Datepart)

Someone help me?

Help me please!|||

Pedro,

To clarify: RefDate is the highest date from the Time Dimension table for a given year (dateid should be ignored)?

Also, you say "the last date for previous year" but your sample result shows the last date of the same year. Should DateID=8225 have 25-12-2004?

|||

Dalej you are write!!

I made a mistake, is

CORRECT Result ID Name DateID Date RefDate 1 blabla… 8225 16-03-2005 25-12-2004 2 blabla… 12-08-2006 29-12-2005 … 9999 … … …

The RefDate is the highest date from Time Table for a given year (for each date in each row)

Could help me?!

THANKS

|||

Code Snippet

select ft.*, td.RefDate

from FactTabl ft

innerjoin

(

select [Year],max([Day])as RefDate

from [Time Dimension]

groupby [Year]

)as td

on(ft.datepart(yy, Date)-1)= td.[Year]

|||

Dear alej,

I customize your statment to my database and there is an error...

Code Snippet

select ft.*, td.RefDate

from FactCashFlows ft

innerjoin

(

select [Ano],max([Dia])as RefDate

from [DimTime]

groupby [Ano]

)as td

on(ft.datepart(yy, T_Dia)-1)= td.[Ano]

The error is:

Code Snippet

Msg 4121, Level 16, State 1, Line 1

Cannot find either column "ft" or the user-defined function or aggregate "ft.datepart", or the name is ambiguous.

When I was trying to customize your code I saw that I dont have the DATE in my FactTable, only the integer foreign key for time dimension. So, I created the FactCashFlow as a view to do a inner join to time table to get the date value.

If I can do it only in one view would be perfect!!

Could help me?
Thanks!!!

|||

OK!

I changed and this statment finally works:

Code Snippet

select ft.*, td.RefDate

from FactCashFlows ft

innerjoin

(

select [Ano],max([Dia])as RefDate

from [DimTime]

groupby [Ano]

)as td

on(datepart(yy, ft.T_Dia)-1)= td.[Ano]

But I have 2 views, and would be better using only one...

I need to change the the first select to get the date value from time dimension...

|||

In order to have only one view to call from SSAS, I need the query something like this:

Code Snippet

select ft.*, td.RefDate, MyTable.Dia

from(SELECT Dia FROM dbo.Time INNERJOIN dbo.CashFlows ON CF_RKData_ID=time.ID) MyTable INNERJOIN

CashFlows ft

innerjoin

(

select [Ano],max([Dia])as RefDate

from [DimTime]

groupby [Ano]

)as td

on(datepart(yy, ft.T_Dia)-1)= td.[Ano]

Code Snippet

Msg 102, Level 15, State 1, Line 11

Incorrect syntax near 'Ano'.

And this view will be the FactCashFlows in SSAS!

But this is wrong, could someone help me?

Thanks!

|||

OK I Found the solution...

Code Snippet

select ft.*, td.RefDate

from(dbo.time INNERJOIN CashFlows ft ON CF_RKData_ID=time.ID)

innerjoin

(

select [Ano],max([Dia])as RefDate

from [DimTime]

groupby [Ano]

)as td

on(datepart(yy, dbo.time.Dia)-1)= td.[Ano]

THANKS ALL!!!