cube and DTS

[claws2rip]

I want to create a package in DTS (on SQL 2000) to update a cube on a daily basis and then schedule it. I know how to schedule the package but what i'm not sure how to do is tell the package to perform an incremental update based on the current date. So that each day i would not have to change the date.

logically, i want the filter to say this:

LastModifiedDate=date

 

[ozi2]

Hi,
I'm not sure I understood, but u can
create a new DTS, drag a cube from the tool box, and then
select the cube u want and select incremental update option.
You don't need to change the date yourself.



Hopeit helped,
Offir

 

[claws2rip]

Offir,

u're right, when i schedule the package to run i can choose how often and when. but when i select incremental update i have to put in a filter.

i have a field in my table called "lastmodifieddate"

so if today is Oct. 17th i would put as the filter
lastmodified date=10/17/02 and when the package is ran at midnight it will update the cube with today's records

tomorrow oct. 18th i want the filter to say:
lastmodifieddate=10/18/02
and so on for each day.

instead of going and changing the date in the filter everyday, i want it done automatically or to a statment that will select the current date.

i hope that clears things up..

Sam

 

[PTFlea]

Change the SQL script in your package to read:
lastmodifieddate = getdate()

Getdate() is a T-SQL function that returns the current date/time (equivalent to VB Now()). To filter purely for date you need to convert the value to remove the time portion. Use convert(varchar(11), getdate(), 106). This returns the date in dd mon yyyy format.

Hope this helps.

 

[livi2621]

try creating a view in SQL Query Analyzer... here is an example:

CREATE VIEW dbo.vw_OLAP_dates_lastmonth
AS
SELECT
DISTINCT CS.actual_surgery_date as or_date
FROM
vw_LastDate as MaxDate, Orstat.dbo.orcase as CS
WHERE
CS.actual_surgery_date >= '10/1/1995' AND
CS.actual_surgery_date < CONVERT(datetime, STR(DATEPART(yyyy, DATEADD(day, 1,MaxDate.LastDate)), 4)
+ '-' + STR(DATEPART(mm, DATEADD(day, 1,MaxDate.LastDate)), 2)
+ '-' + '1', 102)


Then create the DTS package, Execute SQL task...

SELECT *, CONVERT(int, (((12 + datepart(month, or_date)
- CASE WHEN DatePart(day, or_date)
< 1 THEN 10 + 1 ELSE 10 END) % 12 + 1) / 3.0) + 0.75)
AS fiscal_quarter, (datepart(month, or_date)
+ CASE WHEN DatePart(month, or_date)
> 9 THEN - 9 ELSE 3 END) AS fiscal_month
FROM
vw_OLAP_dates_lastmonth


you can run any stored procedures in this package after updating... process all cubes, and you should be all set.. This particular job is one I run every month, to update the cubes... Hope you can make sense of this.

 

[claws2rip]

Thank you for all your help
the cube now updates on adaily basis

Sam