[AccessD] Stored Procedure not producing results

Jim Lawrence accessd at shaw.ca
Wed Nov 22 16:20:19 CST 2006


This sounds like an excellent idea... I would be delighted to see a system
designed using XML for export, with a SOAP wrapper using SMTP to
message/queue the data into an OLAP DB. I have heard it described, seem it
work but have not been able to get it successfully operating on my
servers....
  
Jim  

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Shamil
Salakhetdinov
Sent: Wednesday, November 22, 2006 10:31 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Stored Procedure not producing results

<<<
This caused some fancy trigger-footwork (inserts, 
updates, etc.) across db and server boundaries.
>>>
So, you do use triggers?

<<<
To further complicate things, there was a firewall separating 3 of the
databases from the other 5.
>>>
OK.
How about, e.g., using Extended Stored Procedures called from triggers,
which write inserted/updated/deleted rows in text(bcp-like) or XML formats,
which are then queued by service MS Windows application  to Mail Server,
which then sends them to OLAP server(s), where they are processed?

--
Shamil
 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of artful at rogers.com
Sent: Wednesday, November 22, 2006 8:37 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Stored Procedure not producing results

In my recent project involving PITA, there were 8 live databases, each
growing by an estimated 1 TB per year. To further complicate things, there
was a firewall separating 3 of the databases from the other 5. There was not
even a remote possibility that the OLAP db could reside on the same server
as the OLTP part.

This in turn posed problems, since RI doesn't work across db boundaries (in
MS-SQL). We got around this by duplicating some lookup tables -- not that
I'm suggesting that this is the best solution, but merely the one that was
selected. This caused some fancy trigger-footwork (inserts, updates, etc.)
across db and server boundaries.

Arthur

----- Original Message ----
From: Shamil Salakhetdinov <shamil at users.mns.ru>
To: Access Developers discussion and problem solving
<accessd at databaseadvisors.com>
Sent: Wednesday, November 22, 2006 12:27:34 PM
Subject: Re: [AccessD] Stored Procedure not producing results

<<<
And I am "extremely" flattered that you noticed.
>>>
OK :)

<<<
and also trigger complexities (fire a trigger on instance X and have it do
something to instance Y which resides on another server in another city). It
can be done, but it is non-trivial.
>>>
Well, OLAP PITA database keeping data for only the current working day, week
or month can be always installed on the same server where its "parent" OLTP
database is installed.
Then replication can be used to replicate OLAP PITA database wherever it's
needed to be replicated => to the main OLAP database(s).

Will that work OK?


--
Shamil
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of artful at rogers.com
Sent: Wednesday, November 22, 2006 6:19 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Stored Procedure not producing results

I agree with you on the usefulness of triggers, but there are problems
(surmountable). Consider a database (or collection of databases) whose size
grows by a TB per year. This is going to be a huge problem, literally.
Chances are that you won't be able to support the OLTP and OLAP instances on
a single server, unless you have a SAN and very fancy hardware. This is
going to cause RI problems (on MS-SQL at least; Oracle has table-spaces and
can easily work around this issue), and also trigger complexities (fire a
trigger on instance X and have it do something to instance Y which resides
on another server in another city). It can be done, but it is non-trivial.

And I am "extremely" flattered that you noticed. 

----- Original Message ----
From: Shamil Salakhetdinov <shamil at users.mns.ru>
To: Access Developers discussion and problem solving
<accessd at databaseadvisors.com>
Sent: Wednesday, November 22, 2006 9:49:33 AM
Subject: Re: [AccessD] Stored Procedure not producing results

<<<
As anyone who has followed my threads must
know by now, I tend toward extremism.
>>>
That's so true Arthur! :) (Just kidding of course)

<<<
That said, I suggest that you design PITA in,
just in case it later becomes needed.
>>>
Arthur, these days I'm "biased" toward simple designs and MS SQL triggers
and a parallel OLAP database could be such relatively simple extendable
(plugged/unplugged anytime) solution. I mean I'd just use such triggers to
"replicate" deleted/inserted/updated rows into parallel OLAP database with
tables' structure as you describe i.e. with BeginData and EndDate added.
(And I'd do that only for the tables' customers wanted to have your PITA
enabled with.)
Then all PITA enabled forms/reports/SPs/... will use this parallel OLAP
database together with actual OLTP one if needed...

Do I miss some more effective solutions of this practical task? As you wrote
harddisk memory is cheap these days - I agree with that of course - that's
also a point, which makes rather simple solution with "parallel versioned
data OLAP database" inexpensive...

<<<
"We need to be able to roll back the database to any given PIT without
actually having to do a restore. We just want to specify a date (Jan 1 2004)
and without changing a single line of code, run the reports.
>>> 
You mean "virtual rollback" of course? IOW a "point in time aware"
view/SP/UDF?
 
--
Shamil
 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of artful at rogers.com
Sent: Wednesday, November 22, 2006 5:03 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Stored Procedure not producing results

I have no way to verify whether you mused on this subject or not, but since
you asked, I will provide a thumbnail sketch:

0. You could do it with triggers, but that would not be my first choice.
1. every table will contain two columns called BeginDate and EndDate.
2. any inserted row will have a NOT NULL value in BeginDate, not necessarily
equivalent to today (otherwise you couldn't insert a row that takes effect
on January 1, 2007).
3. any row  without an EndDate is assumed to be effective now.
4. any update to an existing row copies its data to a new row, fills in the
EndDate column on the existing row with GetDate(), and supplies GetDate() as
the BeginDate on the new row.
5. Any "deleted" row is not actually deleted. Rather, it is preserved, but
its EndDate column is updpated to GetDate().

That's a quick thumbnail sketch of PITA. I may have left out a quirk or two
in the sketch, but that's the basic idea. Nothing is destroyed. Everything
is kept, and its time-span is demarcated by its BeginDate and EndDate
columns. 

Taking a practical example, I just switched doctors yesterday. This involves
several operations.

1. The "family physician" has changed, but do not destroy the previous data.
Therefore insert a new row whose BeginDate is yesterday, and update the
previous row, changing its EndDate to yesterday.
2. Agree that all previous medical data concerning me shall be forwarded to
the new physician. (Called Consent, in the medical lingo.)
3. Step two can be further refined, as in "release data about conditions x,
y and z, but not t, u and v" to my new physician.

At the end of the day, for various important reasons, not the least of which
is litigation, we must be able to determine the state of your particular
record(s) in the database as of January 1, 2004. Who was your physician?
What rights did she have to your prior data? What tests were performed upon
you, and on what dates, and with what results and with what follow-ups?

I chose this example a) because I have been there and written that, and b)
because it illustrates the problem vividly. We could easily substitute
lawyers for the physicians, or dentists, or any professional, including
ourselves, software developers. Consider a project as a metaphorical person
in the above. In 2001, the practitioner list comprised Fuller, Colby and
Salakhetdinov. In 2002, it comprised Hindman, Carbonell, Colby and Lawrence.
In the medical schema, one could easily have several practitioners, one for
heart, one for ENT (ear, nose, throat), one for gynecology, etc. And the
same holds true for a software client -- she employs an accounting expert, a
CRM expert, an Excel macro wizard, an Access expert, and so on.

That was a bit of a sidetrack, but remains on point, I think. The point
being, "We need to be able to roll back the database to any given PIT
without actually having to do a restore. We just want to specify a date (Jan
1 2004) and without changing a single line of code, run the reports.

That is the point of PITA.

It must be designed in from the outset. Not every app needs PITA, but it
becomes very difficult to add it after the fact. As anyone who has followed
my threads must know by now, I tend toward extremism. That said, I suggest
that you design PITA in, just in case it later becomes needed. In that case,
you look like a genius. And if the requirement never emerges, you wasted
some very cheap disk space. 

Arthur

<<< tail skipped>>>

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list