Re: Real world issue: How to "split" queries in presence of record replication and replication sensitive aggregate functions ?

Alexandr Savinov ha scritto:

Thanks. Let's define a common "protocol" to discuss and interact.

What is a replication sensitive aggregation function?

Terminology
========

Please feel free to replace the following with the *right* english
terminology.

1.
For "aggregate" function (please correct terminology if wrong) I mean
something like:

sum()
avg()
count()
var()
or any user function that turns a bunch of values into a single scalar
....
This involves a group of record and requires a GROUP BY clause.


For "row function" I mean for instance a function defined
Income - Expenditure, Age * AgeWeight, ...
this is computed on each record. These functions are uninteresting for
our purpose. Do not give us any problem.

"aggregate function" is in some sense the opposite to "row function"


2.
For "Replication sensitive" I mean:

- An aggregate function such that if some value (operand) is
replicated the result comes out wrong.

For instance:

* Replication sensitive* :
sum()
count()
avg()
Any Quantile and so on
....

* Not Replication sensitive * :
max()
min()
count distinct ()
anything not influenced by the replication of values
....


How do you define a partition (of tables and relationships)?

You have a partition when you separate the objects that are in a set
into several subsets that have no common items.


What do you mean by "record replication"?

I mean, for instance when you join 2 tables in 1-N relationship, the
values on side 1 get "replicated" on the resulting table.

Could you demonstrate how this procedure works using the following

Yes I will. But for ease of discussion and so that anyone here can
follow and make observations. I propose to take a free standard well
know database, easy to work with. I suggest to take Northwind for
ACCESS.

If you do not already have it it is a free download:

http://www.microsoft.com/downloads/details.aspx?FamilyID=C6661372-8DBE-422B-8676-C632D66C529C&displaylang=EN

Make your request referring to those tables.

If this represent a problem in any way, I will work with tables you
suggest. I will make a little access db. In such a case I need a
precise description of fields, relationships and the fields (with
possible functions) that have to be in the final report.

Number of records are irrelevant to our problems .

-P

.

Relevant Pages

  • Re: Question about replicating stored procedure execution
    ... different on the publisher and subscriber and therefore you get different ... Looking for a SQL Server replication book? ... My understanding is that, for example, if you have a stored procedure ... Is this a common practice in a replication scenario? ...
    (microsoft.public.sqlserver.replication)
  • Re: Sites & Services Question
    ... Can someone please give me a good explaination of sites & ... different techniques or methods by which sysvol and ... Transport Link (Costs & Replication Interval) and the NTDS site ... It is common for otherwise knowledgable people to have little in depth ...
    (microsoft.public.windows.server.active_directory)
  • Replication setup
    ... How many articles can take part in merge replication? ... static filters. ... Is there benefit to allocate this 25 common tables in separate publication ...
    (microsoft.public.sqlserver.replication)