Reporting Database - Data Integrity Question

[David F-H]

I am setting up some reports that run against the ProjectServer_Reporting
database that is continually being updated. The report(s) also take input
from a summary table (CustomTable) that has been populated by a script that
uses the data from this database. The script that populates CustomTable
takes about three minutes which is too long to incorporate in the report
itself, so I want to replicate the database every half-hour on another server
and run the script to populate CustomTable based on the replicated database.
The report(s) will then run against the replicated database and CustomTable.
In this way, I will maintain integrity between the database and CustomTable.
My problem is that someone may run a report after the database has been
replicated, but before CustomTable has been re-populated which may lead to
incorrect values in the reports (as CustomTable won’t be in synch with the
replicated database).

It would be nice if it was possible to set up the replication and population
of CustomTable in one transactional step, such that it can all be committed
after the script to populate CustomTable has run, but I don’t know if this is
possible ? Or is there another way to solve this ? Note that it is not
practical to take the replicated database offline or put it in single user
mode as will be in continual use by users running reports.

Regards,
David

 

[Rod Gill]

My first thought was an extra field in the CustomTable that has "ReBuilding"
or "Last Built HH:NN" in it. Add the contents of this field to the report
header and at least you can check in print preview that the data is good to
go?

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com

I am setting up some reports that run against the ProjectServer_Reporting
database that is continually being updated. The report(s) also take input
from a summary table (CustomTable) that has been populated by a script
that
uses the data from this database. The script that populates CustomTable
takes about three minutes which is too long to incorporate in the report
itself, so I want to replicate the database every half-hour on another
server
and run the script to populate CustomTable based on the replicated
database.
The report(s) will then run against the replicated database and
CustomTable.
In this way, I will maintain integrity between the database and
CustomTable.
My problem is that someone may run a report after the database has been
replicated, but before CustomTable has been re-populated which may lead to
incorrect values in the reports (as CustomTable won’t be in synch with the
replicated database).

It would be nice if it was possible to set up the replication and
population
of CustomTable in one transactional step, such that it can all be
committed
after the script to populate CustomTable has run, but I don’t know if this
is
possible ? Or is there another way to solve this ? Note that it is not
practical to take the replicated database offline or put it in single user
mode as will be in continual use by users running reports.

Regards,
David

__________ Information from ESET Smart Security, version of virus
signature database 4549 (20091027) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4549 (20091027) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[David F-H]

Thanks Rod,
We have had some similar thoughts, but we're not sure that this will ensure
integrity. What happens if the report starts processing just before the
database project changes are replicated - it will find that it is not
"rebuild"ing so will start the report sql, but the replication may start &
finish before the main report query is executed, which in turn finishes
before the CustomTable is built ?

 

[Rod Gill]

The only way is to put a stop using this db sign up before re-building -
long enough for the last report cycle to finish. In fact if any process is
running a report, it can test the status field for "Last Built HH:NN" then
increment a NumberOfProcessesRunningReport counter.

After setting the "Building status, wait until the count resets to zero (or
5 minutes whichever is first) then re-build, reset counter to zero and reset
status to "Last Built HH:NN"

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com

Thanks Rod,
We have had some similar thoughts, but we're not sure that this will
ensure
integrity. What happens if the report starts processing just before the
database project changes are replicated - it will find that it is not
"rebuild"ing so will start the report sql, but the replication may start &
finish before the main report query is executed, which in turn finishes
before the CustomTable is built ?



__________ Information from ESET Smart Security, version of virus
signature database 4550 (20091028) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4550 (20091028) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[David F-H]

OK thanks Rod, I'll look into that - it seems promising. Just have to sus
out how to show a busy message in the report and to loop / wait (using
cursors ?) until the counter goes to zero.

One other thought I had - could the use of triggers help? Ie. If I had a
trigger on the Task table that updated the CustomTable when tasks for a
project are updated / added ?
- Do all tasks for a project get updated by one statement (so that all
tasks will be in the "inserted" table accessible from the trigger and
contained in one transactional unit) ?
- Can triggers be added to the Reporting database, or are they lost under
some conditions if manually added (eg. Reporting database is refreshed, etc) ?

Regards,
David