Re: Need an expert advice
- From: "JT" <someone@xxxxxxxxxxxxx>
- Date: Tue, 21 Feb 2006 16:45:17 -0500
"Chris" <Chris@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:12FD68B7-BD47-4C86-BA66-99BE1EDF9473@xxxxxxxxxxxxxxxx
Hi,
we have a legacy database on a UNIX box for our production system (OLTP).
This is the only database used for both OLTP and custom reporting. The
database is now hugh and reporting is very limited as well as slow
processing. When certain reports are run the users have latency in
inputing
data.
Even with the reporting load moved to a different server, if the OLTP server
continues to accumulate massive amounts of transactions, the duration of
simple primary key lookups will eventually not meet the user's performance
expectations. In addition to replicating data for reporting purposes, you
may want to have a daily or monthly process that archives unneeded
historical transactions from the OLTP system to an Operational Data Store
(ODS) database (not necessarily a seperate server). If needed, you can still
give the operational applications query access to this data by implementing
partitioned views.
http://www.dmreview.com/article_sub.cfm?articleId=469
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_06_17zr.asp
I am thinking of importing some of the tables nightly to SQL Server and
then creating come cubes for reports. The issues I have are:
1. All of the tables in the UNIX box doesn't have timestamp so there is no
way of improting changed data and it database doesn't support replication
so
I'll have to import the entire table(s) nightly. Would this affect my
cubes?
With no timestamp of any kind, then standard replication is not an option.
You can add a trigger to log insert / update / deletes on the OLTP system to
seperate tables (perhaps only storing IDs of rows that were modified), and
implement a DTS package that copies over the needed data based on this
information from this log.
http://www.microsoft.com/technet/prodtechnol/sql/2000/books/c07ppcsq.mspx
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_busintbpwithdts.asp
2. Would it be better to import the data to a separate server (staging)
and
then import the data from the second server to a third serer running MSAS?
See the above reccomendation for an ODS database. For performance reasons,
it would be best to have this on a dedicated server, but it could be located
on the OLTP or MSAS server.
3. One of the table is an Archive table where all settled sales are
transferred to, however, due to the nature of our business and the current
system design, some users go back and edit previous data so if i run a
report
now (for let's say sept last year) I see sales as 2.00 dollars and later
today I see sales as 2.50. There is no way of knowing which row was
changed.
That's another reason for the logging trigger mentioned above; it also
audits changes on these OLTP tables. Also, useful in the log tables would be
a datetime column to keep track of when the changes took place.
http://msdn.microsoft.com/msdnmag/issues/04/04/DataPoints/
So if I import the data to create a cube can I then snapshot that cube
since
I'll be importing the antire "Archive" table nightly. I know it sounds
screwed up but it's what I have to work with. Or how can I solve this.
The audit log tables on the OLTP system can keep a history of data
modifications to settled sales, and I assume this event would be occasional
and not too frequent. Also, the MSAS cubes can be archived (just like SQL
Server databases), things like Excel pivot tables can be archived, and
reports can be printed as archivable PDF documents.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/anservog.mspx
Thanks
.
- Prev by Date: Re: Design opinion?
- Next by Date: Re: Design opinion?
- Previous by thread: Design opinion?
- Next by thread: Re: Suggestion for Handling Very Large Tables (SQL 2000)
- Index(es):
Relevant Pages
|
