Recency, time since last sale

Hi,
I'm trying to implement a recency measure for my individual customers,
telling me how many days have gone by since the last purchase of a customer.
I would like it to go something like this:
with member time.week.last_purchase_date as
'Tail(Filter([Time].[Week].[Date].members, Not
IsEmpty(([Measures].[Quantity],[Customers].[Geography].CurrentMember)))).Item(0).Item(0).uniquename '
member measures.days_since_last_purchase as
'Datediff(time.week.lastpurchasedate : [Time].[Week].Currentmember)'
Of course there is no Datediff in MDX, but how can I construct the syntax to
meet my requirements?
Many thanks!
have you tried using vba functions? DateDiff() and CDate() ?
"Jacob_I" wrote:

> Hi,
> I'm trying to implement a recency measure for my individual customers,
> telling me how many days have gone by since the last purchase of a customer.
> I would like it to go something like this:
> with member time.week.last_purchase_date as
> 'Tail(Filter([Time].[Week].[Date].members, Not
> IsEmpty(([Measures].[Quantity],[Customers].[Geography].CurrentMember)))).Item(0).Item(0).uniquename '
> member measures.days_since_last_purchase as
> 'Datediff(time.week.lastpurchasedate : [Time].[Week].Currentmember)'
> Of course there is no Datediff in MDX, but how can I construct the syntax to
> meet my requirements?
> Many thanks!
|||Hi Mike
Actually, I didn't think about that at all. I'll try that and post my
findings.
"mike" wrote:
[vbcol=seagreen]
> have you tried using vba functions? DateDiff() and CDate() ?
> "Jacob_I" wrote:
|||As far as the MDX goes, you could substract index positions of two dates.
It would mean that you find index position of last purchase date in the date
dimension and then find index position of 'today' or some other date...
MC
"Jacob_I" <JacobI@.discussions.microsoft.com> wrote in message
news:5C0C8187-C289-4E36-92BE-11F711D5B6E1@.microsoft.com...[vbcol=seagreen]
> Hi Mike
> Actually, I didn't think about that at all. I'll try that and post my
> findings.
> "mike" wrote:
|||Thanks a lot Mike & MC. I've gotten the datediff stuff working beautifully
thansk to you valuable advice. But I also need it to be time-sensitive, so
that when I use the calc member:
'Tail(Filter([Time].[Week].[Date].members, Not
IsEmpty(([Measures].[Quantity],[Customers].[Geography].CurrentMember)))).Item(0).Item(0).uniquename '
...the [Time].[Week].[Date].members is replaced by a set of members that
looks something like ([time].[week].(All):[Time].[week].Currentmember) ie. it
returns all members from beginning of time until time.currentmember.
Can this be done? Maybe it's woth a new thread?
Thanks in advance!
Regards, Jacob
|||In article <E88FB2ED-46C5-4DDC-B4A6-C1222C611986@.microsoft.com>,
JacobI@.discussions.microsoft.com says...
> ..the [Time].[Week].[Date].members is replaced by a set of members that
> looks something like ([time].[week].(All):[Time].[week].Currentmember) ie. it
> returns all members from beginning of time until time.currentmember.
>
You could use something like
[time].[week].members.item(0):[Time].[week].Currentmember
Or, to be completely idependant of which level you are on:
[time].CurrentMember.Level.Members.item(0):[Time].[week].Currentmember
HTH
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell
|||hi Jacob,
I am also facing a similar problem with the datediff. Could you share on how
you overcame the prob on datediff.
Thanks in advance
Prady
"Jacob_I" wrote:

> Thanks a lot Mike & MC. I've gotten the datediff stuff working beautifully
> thansk to you valuable advice. But I also need it to be time-sensitive, so
> that when I use the calc member:
> 'Tail(Filter([Time].[Week].[Date].members, Not
> IsEmpty(([Measures].[Quantity],[Customers].[Geography].CurrentMember)))).Item(0).Item(0).uniquename '
> ..the [Time].[Week].[Date].members is replaced by a set of members that
> looks something like ([time].[week].(All):[Time].[week].Currentmember) ie. it
> returns all members from beginning of time until time.currentmember.
> Can this be done? Maybe it's woth a new thread?
> Thanks in advance!
> Regards, Jacob
>
>
|||Hi Prady,
Although I don't have the code right here, I can illustrate the layout of my
final query:
// This part gives me the date of last puchase and case it to DATE type
CDate(Tail(Filter({<Configure a date member range>}, Not
IsEmpty(([Measures].[Quantity],[Customers].[Geography].CurrentMember)))).Item(0).Item(0).uniquename) -
// Minus the currentdate
Now()
(end)
The date member range can be anything from a date range in a named set to
something that changes with the time.currentmember, eg.
{time.currentmember.lag(182):time.currentmembe r} which if a date-level will
give you a range of half a year. As this query if quite heavy for at large
customer dimension it's rather sensible to have a small date member range.
The minus part can also be Cdate(time.currentmember.name) which (depending
on your data level member formatting) also will give you a DATE type result
to minus with the first part.
HTH
Jacob_I

Recency, time since last sale

Hi,
I'm trying to implement a recency measure for my individual customers,
telling me how many days have gone by since the last purchase of a customer.
I would like it to go something like this:
with member time.week.last_purchase_date as
'Tail(Filter([Time].[Week].[Date].members, Not
IsEmpty(([Measures].[Quantity],[Customers].[Geography].Curre
ntMember)))).Item(0).Item(0).uniquename '
member measures.days_since_last_purchase as
'Datediff(time.week.lastpurchasedate : [Time].[Week].Currentmember)'
Of course there is no Datediff in MDX, but how can I construct the syntax to
meet my requirements?
Many thanks!have you tried using vba functions? DateDiff() and CDate() '
"Jacob_I" wrote:

> Hi,
> I'm trying to implement a recency measure for my individual customers,
> telling me how many days have gone by since the last purchase of a custome
r.
> I would like it to go something like this:
> with member time.week.last_purchase_date as
> 'Tail(Filter([Time].[Week].[Date].members, Not
> IsEmpty(([Measures].[Quantity],[Customers].[Geography].Cur
rentMember)))).Item(0).Item(0).uniquename '
> member measures.days_since_last_purchase as
> 'Datediff(time.week.lastpurchasedate : [Time].[Week].Currentmember
)'
> Of course there is no Datediff in MDX, but how can I construct the syntax
to
> meet my requirements?
> Many thanks!|||Hi Mike
Actually, I didn't think about that at all. I'll try that and post my
findings.
"mike" wrote:
[vbcol=seagreen]
> have you tried using vba functions? DateDiff() and CDate() '
> "Jacob_I" wrote:
>|||As far as the MDX goes, you could substract index positions of two dates.
It would mean that you find index position of last purchase date in the date
dimension and then find index position of 'today' or some other date...
MC
"Jacob_I" <JacobI@.discussions.microsoft.com> wrote in message
news:5C0C8187-C289-4E36-92BE-11F711D5B6E1@.microsoft.com...[vbcol=seagreen]
> Hi Mike
> Actually, I didn't think about that at all. I'll try that and post my
> findings.
> "mike" wrote:
>|||Thanks a lot Mike & MC. I've gotten the datediff stuff working beautifully
thansk to you valuable advice. But I also need it to be time-sensitive, so
that when I use the calc member:
'Tail(Filter([Time].[Week].[Date].members, Not
IsEmpty(([Measures].[Quantity],[Customers].[Geography].Curre
ntMember)))).Item(0).Item(0).uniquename '
..the [Time].[Week].[Date].members is replaced by a set of memb
ers that
looks something like ([time].[week].(All):[Time].[week].Curr
entmember) ie. it
returns all members from beginning of time until time.currentmember.
Can this be done? Maybe it's woth a new thread?
Thanks in advance!
Regards, Jacob|||In article <E88FB2ED-46C5-4DDC-B4A6-C1222C611986@.microsoft.com>,
JacobI@.discussions.microsoft.com says...
> ..the [Time].[Week].[Date].members is replaced by a set of mem
bers that
> looks something like ([time].[week].(All):[Time].[week].Cu
rrentmember) ie. it
> returns all members from beginning of time until time.currentmember.
>
You could use something like
[time].[week].members.item(0):[Time].[week].Currentmember
Or, to be completely idependant of which level you are on:
[time].CurrentMember.Level.Members.item(0):[Time].[week].Current
member
HTH
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell|||hi Jacob,
I am also facing a similar problem with the datediff. Could you share on how
you overcame the prob on datediff.
Thanks in advance
Prady
"Jacob_I" wrote:

> Thanks a lot Mike & MC. I've gotten the datediff stuff working beautifully
> thansk to you valuable advice. But I also need it to be time-sensitive, so
> that when I use the calc member:
> 'Tail(Filter([Time].[Week].[Date].members, Not
> IsEmpty(([Measures].[Quantity],[Customers].[Geography].Cur
rentMember)))).Item(0).Item(0).uniquename '
> ..the [Time].[Week].[Date].members is replaced by a set of mem
bers that
> looks something like ([time].[week].(All):[Time].[week].Cu
rrentmember) ie. it
> returns all members from beginning of time until time.currentmember.
> Can this be done? Maybe it's woth a new thread?
> Thanks in advance!
> Regards, Jacob
>
>|||Hi Prady,
Although I don't have the code right here, I can illustrate the layout of my
final query:
// This part gives me the date of last puchase and case it to DATE type
CDate(Tail(Filter({<Configure a date member range>}, Not
IsEmpty(([Measures].[Quantity],[Customers].[Geography].Curre
ntMember)))).Item(0).Item(0).uniquename) -
// Minus the currentdate
Now()
(end)
The date member range can be anything from a date range in a named set to
something that changes with the time.currentmember, eg.
{time.currentmember.lag(182):time.currentmember} which if a date-level
will
give you a range of half a year. As this query if quite heavy for at large
customer dimension it's rather sensible to have a small date member range.
The minus part can also be Cdate(time.currentmember.name) which (depending
on your data level member formatting) also will give you a DATE type result
to minus with the first part.
HTH
Jacob_I

receive top 20

HI

I am trying to set up a stored procedure to retrieve to 20 messages from a queue into a table to implement a batched process. I have the following code in a stored procedure.

WAITFOR (
RECEIVE top (20) -- get batched so that we can process same listid once
message_type_name,
message_body, -- the message contents
conversation_handle -- the identifier of the dialog this message was received on
FROM dbo.target
into @.PayloadData
), TIMEOUT 3000 -- if the queue is empty for three second, give UPDATE and go away

However, the stored procedure is only retrieving 1 message at a time from the queue. Did I miss some other setting

thanks

P

RECEIVE can only return messages on one conversation group. Normally each conversation is its own conversation group. If you sent only one message on each conversation, RECEIVE cannot get more that one message at a time, even if there are more messages in the queue.|||

Hi

so, in your blog on T-SQL RECEIVE. Fast. : Set based Processing.

How are you able to receive the message in bulk? Is it because of the way you send the message in LoadQueueReceivePerfBlog?

P|||Yes. This is also the reason why I recommend reusing dialogs in my other entry at http://blogs.msdn.com/remusrusanu/archive/2007/04/24/reusing-conversations.aspx|||

one follow up,

when I send the message using the same conversation handle, the receive top (20) statement waits until the previous batch is committed before it will start retrieving the next 20. I guess this is because of the its now part of the same conversation group and service broker need to guarantee process order?

This is what my proc looks like

BEGIN TRANSACTION

WAITFOR (

RECEIVE top (20) -- get batched so that we can process same listid once

message_type_name,

message_body, -- the message contents

conversation_handle -- the identifier of the dialog this message was received on

FROM dbo.target

into @.PayloadData

), TIMEOUT 3000 -- if the queue is empty for three second, give UPDATE and go away

-- do some processing of the records in @.PayloadData

COMMIT TRANSACTION

if "-- do some processing of the records in @.PayloadData" is taking a long time, its going to block the messages in the queue.

If I remove the begin and commit transaction block, it only wait for the 3 seconds I specified.

Question: is the transaction block necessary in the activated procedure.

thanks

Paul

recbase.cpp:1378 & somerge.c:1616 Errors

Hi,
I am trying to implement a DTS to copy data from one server to another
(both servers are identical), Windows 2000 server and have installed
SQL Server 2000 sp3a.
The DTS in question has the two server connections defined as
"Microsoft OLE DB Provider for SQL Server" and there are several
"Transform Data Tasks" that copy data from tables on one server to
tables on the other. Unfortunately I have to run these steps often as
I am in the process of developing/debugging and I am experiencing one
of the following errors, always when the DTS arrives at the task of
copying the same large table:
Location somerge.c:1616
Expression: mrP->mrStatus!=MERGERUN::NONE
SPID: 78
Process ID: 724
Location recbase.cpp:1378
Expression: m_offBeginVar < m_sizeRec
SPID: 64
Process ID: 840
These errors do not always occur but either one or the other pops up
on a regular basis.
I have run DBCC checkDB and DBCC checktable on the affected Databases
and tables several times and have never found any errors.
Has anyone got any idea of what is causing this? Any suggestions of
what I can do to solve this?
Any help is much appreciated.
Regards
Rekkie
Dear ,
While executing this particular process both servers "sa" passwords should be same,Other wise it never work.
Regards
Faheem Latif
Netsol Pvt Ltd

recbase.cpp:1378 & somerge.c:1616 Errors

Hi,
I am trying to implement a DTS to copy data from one server to another
(both servers are identical), Windows 2000 server and have installed
SQL Server 2000 sp3a.
The DTS in question has the two server connections defined as
"Microsoft OLE DB Provider for SQL Server" and there are several
"Transform Data Tasks" that copy data from tables on one server to
tables on the other. Unfortunately I have to run these steps often as
I am in the process of developing/debugging and I am experiencing one
of the following errors, always when the DTS arrives at the task of
copying the same large table:
Location somerge.c:1616
Expression: mrP->mrStatus!=MERGERUN::NONE
SPID: 78
Process ID: 724
Location recbase.cpp:1378
Expression: m_offBeginVar < m_sizeRec
SPID: 64
Process ID: 840
These errors do not always occur but either one or the other pops up
on a regular basis.
I have run DBCC checkDB and DBCC checktable on the affected Databases
and tables several times and have never found any errors.
Has anyone got any idea of what is causing this' Any suggestions of
what I can do to solve this?
Any help is much appreciated.
Regards
RekkieDear ,
While executing this particular process both servers "sa" passwords should b
e same,Other wise it never work.
Regards
Faheem Latif
Netsol Pvt Ltd|||Both passwords are the same. I don't see however what has
to do with the problem as each OLEDB server connection is
defined separately with the user to use for that
connection.

>--Original Message--
>Dear ,
>While executing this particular process both servers "sa"
passwords should be same,Other wise it never work.
>Regards
>Faheem Latif
>Netsol Pvt Ltd
>.
>

real time reporting + OLTP dbs

How does one implement real time reporting off of data from the OLTP
systems. Right now we run nightly extracts and load into OLAP DBs to
minimise locking,etc while the extracts are being run. However our customers
now want to see real time data as soon as possible and we cannot give them
access to the live OLTP systems. Are there any ways to implement this
efficiently ?Have you tried replication? It should give you more frequent updates.
You might want to consider using read uncommitted query because replication
also needs to make changes to your OLAP database to keep data up to date.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:upTfU1d0EHA.3584@.TK2MSFTNGP11.phx.gbl...
> How does one implement real time reporting off of data from the OLTP
> systems. Right now we run nightly extracts and load into OLAP DBs to
> minimise locking,etc while the extracts are being run. However our
> customers
> now want to see real time data as soon as possible and we cannot give them
> access to the live OLTP systems. Are there any ways to implement this
> efficiently ?
>|||we thought about replication but cant really justify replicating each of our
OLTP dbs to serve the reporting needs besides the administration needed to
support it ,etc..
"wei xiao [MSFT]" <weix@.online.microsoft.com> wrote in message
news:Oc0vFSf0EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Have you tried replication? It should give you more frequent updates.
> You might want to consider using read uncommitted query because
replication
> also needs to make changes to your OLAP database to keep data up to date.
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://weblogs.asp.net/weix
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:upTfU1d0EHA.3584@.TK2MSFTNGP11.phx.gbl...
> > How does one implement real time reporting off of data from the OLTP
> > systems. Right now we run nightly extracts and load into OLAP DBs to
> > minimise locking,etc while the extracts are being run. However our
> > customers
> > now want to see real time data as soon as possible and we cannot give
them
> > access to the live OLTP systems. Are there any ways to implement this
> > efficiently ?
> >
> >
>

real time reporting + OLTP dbs

How does one implement real time reporting off of data from the OLTP
systems. Right now we run nightly extracts and load into OLAP DBs to
minimise locking,etc while the extracts are being run. However our customers
now want to see real time data as soon as possible and we cannot give them
access to the live OLTP systems. Are there any ways to implement this
efficiently ?
Have you tried replication? It should give you more frequent updates.
You might want to consider using read uncommitted query because replication
also needs to make changes to your OLAP database to keep data up to date.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:upTfU1d0EHA.3584@.TK2MSFTNGP11.phx.gbl...
> How does one implement real time reporting off of data from the OLTP
> systems. Right now we run nightly extracts and load into OLAP DBs to
> minimise locking,etc while the extracts are being run. However our
> customers
> now want to see real time data as soon as possible and we cannot give them
> access to the live OLTP systems. Are there any ways to implement this
> efficiently ?
>
|||we thought about replication but cant really justify replicating each of our
OLTP dbs to serve the reporting needs besides the administration needed to
support it ,etc..
"wei xiao [MSFT]" <weix@.online.microsoft.com> wrote in message
news:Oc0vFSf0EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Have you tried replication? It should give you more frequent updates.
> You might want to consider using read uncommitted query because
replication
> also needs to make changes to your OLAP database to keep data up to date.
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://weblogs.asp.net/weix
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:upTfU1d0EHA.3584@.TK2MSFTNGP11.phx.gbl...
them
>

real time reporting + OLTP dbs

How does one implement real time reporting off of data from the OLTP
systems. Right now we run nightly extracts and load into OLAP DBs to
minimise locking,etc while the extracts are being run. However our customers
now want to see real time data as soon as possible and we cannot give them
access to the live OLTP systems. Are there any ways to implement this
efficiently ?Have you tried replication? It should give you more frequent updates.
You might want to consider using read uncommitted query because replication
also needs to make changes to your OLAP database to keep data up to date.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:upTfU1d0EHA.3584@.TK2MSFTNGP11.phx.gbl...
> How does one implement real time reporting off of data from the OLTP
> systems. Right now we run nightly extracts and load into OLAP DBs to
> minimise locking,etc while the extracts are being run. However our
> customers
> now want to see real time data as soon as possible and we cannot give them
> access to the live OLTP systems. Are there any ways to implement this
> efficiently ?
>|||we thought about replication but cant really justify replicating each of our
OLTP dbs to serve the reporting needs besides the administration needed to
support it ,etc..
"wei xiao [MSFT]" <weix@.online.microsoft.com> wrote in message
news:Oc0vFSf0EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Have you tried replication? It should give you more frequent updates.
> You might want to consider using read uncommitted query because
replication
> also needs to make changes to your OLAP database to keep data up to date.
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://weblogs.asp.net/weix
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:upTfU1d0EHA.3584@.TK2MSFTNGP11.phx.gbl...
them[vbcol=seagreen]
>