How to create Merge Replication with latest update always from subscribers

Hi ,

I am trying to create Replication Topology (Merge Replication) like below.

Subscriber1 --> Publisher <-- Subscriber2.

I have created both subscribers with Subscription Type as Server with Priority as 75. I am updating the Column A of Row_10 in Subscriber1 on time say 11 am. After i am running the Starting synchronizing agent from Subscriber1. The value propagated to Publisher now publisher contains the latest value in Column A. Uptonow the Subscriber2 is not synchronized with Publisher.

Now in Subscriber2 also Column A of Row_10 is updated say at 11.10 am. Actually now Publisher contains the value from Subscriber1 for that Column and in Subscriber2 we have the same column updated.

Now i am running the Synchronization in Subscriber2, i am getting the result which is not expected. Here Publisher's value is propagated to Subscriber2. But as per real scenario Subscriber2 has the Latest value which is updated on 11.10 AM.

I don't know what am i missing here. Actually merge replication should see the time stamp and it has to decide winner. But here it always considers publisher as a winner and puts the data to Subscriber.

Can anyone help ?

Thanks in advance

Merge replication tracks changes via a rowguid, not a timestamp. it has no idea what time a change was made. If you want to decide who wins in a conflict scenario based on a column value in your table, then you should create a custom conflict resolver for your article. see sp_addmergearticle in books online for more information.|||Greg,

Thanks for the reply. After going through MDSN I found one solution. We can use Microsoft Provided Custom Resolver for this. DateTime Later Wins resolver. I think using this my issue will be resolved. But i didn't try this resolver yet.

Thanks,

Thams

How to create Merge Replication with latest update always from subscribers

Hi ,

I am trying to create Replication Topology (Merge Replication) like below.

Subscriber1 --> Publisher <-- Subscriber2.

I have created both subscribers with Subscription Type as Server with Priority as 75. I am updating the Column A of Row_10 in Subscriber1 on time say 11 am. After i am running the Starting synchronizing agent from Subscriber1. The value propagated to Publisher now publisher contains the latest value in Column A. Uptonow the Subscriber2 is not synchronized with Publisher.

Now in Subscriber2 also Column A of Row_10 is updated say at 11.10 am. Actually now Publisher contains the value from Subscriber1 for that Column and in Subscriber2 we have the same column updated.

Now i am running the Synchronization in Subscriber2, i am getting the result which is not expected. Here Publisher's value is propagated to Subscriber2. But as per real scenario Subscriber2 has the Latest value which is updated on 11.10 AM.

I don't know what am i missing here. Actually merge replication should see the time stamp and it has to decide winner. But here it always considers publisher as a winner and puts the data to Subscriber.

Can anyone help ?

Thanks in advance

Merge replication tracks changes via a rowguid, not a timestamp. it has no idea what time a change was made. If you want to decide who wins in a conflict scenario based on a column value in your table, then you should create a custom conflict resolver for your article. see sp_addmergearticle in books online for more information.|||Greg,

Thanks for the reply. After going through MDSN I found one solution. We can use Microsoft Provided Custom Resolver for this. DateTime Later Wins resolver. I think using this my issue will be resolved. But i didn't try this resolver yet.

Thanks,

Thams

How to create a MAX Named Set for a date dimension?

I have an OLAP cube and I need to create a Named Set to return a latest date from a date dimension. For example I have the following dimension:

[Account Period].[Account Period].[Prescription Date].[Prescription Date].12-July-2006

Based on this dimension structure, how to create a named set only to return the latest date in any grouping of the other dimensions. Or to create a named set is not the correct approach to this?

You can simply use the Tail function unless your date dimension contains dates into the future for forecasting or other measures:

Tail([Account Period].[Account Period].[Prescription Date].Members,1)

If you have dates into the future you can use a measure group to filter the dates so that only dates with data for that measure group are considered:

Tail(Exists([Account Period].[Account Period].[Prescription Date].Members,,"Sales"),1)

HTH,

Steve

|||

Thank you for your reply. I guess that it is best to put down what I really want to achieve here and for the expert to give me the best advice. I am designing a report for a pharmist on a OLAP cube. the OLAP cube consists of the following dimensions:

Measure: Count of the logical primary key (treatment count)

Dimension: Patient_ID.Patient_ID.Patient_ID

Dimension: Account_Period.Prescription_Date.Prescription_Date

Dimension: Drug.Drug_Name.Drug_Name

Dimension: Hospital.Hospital.Hospital

Dimension: Drug.Drug_Ingriedient..Drug_Ingriedient

I need an MDX query for the report so that for a selected Drug_Name (first report single parameter) and the selected Drug_Ingriedient (second report multiple parameters) the "latest" Prescription_Date of the selected Drug_Name, the "earliest" Prescription_Date of the selected Drug_Name, the duration of the selected Drug_Name given to this Patient_ID, the Drug_Ingridient at the "latest" Prescription_Date (Drug_Ingridient is one to one relationship to the Drug_Name, different Drug_Ingridient can occour on the same Drug_Name but not on the same Prescription_Date), the hospital where the Drug_Name prescripted to the Patient_ID at the "latest" Prescription_Date (Patient can be prescripted the selected Drug_Name from different hospital over the period of several years) and the total count of the Drug_Name treatment.

The dataset should consists of eight columns, Patient_ID, Latest_Date, Earliest_Date, Duration, Drug_Name, Drug_Ingridient, Treatment_Count, Hospital.

Thanks.

|||

This will be tough to do via MDX on this discussion board. There are several questions that I need to ask.

1.) What is the formula for "Duration"?

2.) Are the "Earliest" and "Latest" dates for all prescriptions given to the patient?

My email is stevepon@.microsoft.com if you want to send me information off-line. A copy of your AS project files would be a big help.

Steve