Need an expert advice

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chris - 20 Feb 2006 19:59 GMT

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.

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?

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?

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.
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.

Thanks

Reply to this Message

JT - 21 Feb 2006 21:45 GMT

> Hi,
> we have a legacy database on a UNIX box for our production system (OLTP).
[quoted text clipped - 3 lines]
> 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_d
es_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:
[quoted text clipped - 4 lines]
> 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/sq
l_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
[quoted text clipped - 3 lines]
> 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

Reply to this Message

Jéjé - 11 Mar 2006 12:48 GMT

what is the number of rows in the OLTP database?
what is your RDBMS on this server?

servers like Oracle can create a simple flatfile which contains all the
transaction made against a table;

there is no timestamp, but can you create triggers in your database?

> Hi,
> we have a legacy database on a UNIX box for our production system (OLTP).
[quoted text clipped - 30 lines]
>
> Thanks

Reply to this Message

Peter Nolan - 13 Mar 2006 10:44 GMT

Hi ???

(for some reason the first post is not here)

> Hi,
> we have a legacy database on a UNIX box for our production system (OLTP).
[quoted text clipped - 3 lines]
> inputing
> data.

Well, situation normal.....starting point is to look at the business
from a business perspective and determine if there is value in going
the data warehouse route or if all that is warranted from a
profitability point of view is operational reporting on another
database image.

In most businesses today a data warehouse is well justified based on
profit contribution.

If you go the DW route you will need to get to incremental
extracts....if the operational system does not have the ability to
provide them (due to appaulingly limited design in this day and age)
then you must generate them. My company provides free tools to do this.

Then you are on the road to doing 'Business Intelligence' and there is
quite a learning curve. You can choose to 'go it alone' or hire in a
partner to assist. Of course, as a long term BI consultant I would
land on the side of hiring someone but many companies 'buy the books
and go it alone'... ;-)

I sincerely doubt the most profitable approach is getting one answer at
a time from a forum.

Please see my beginners page for the best books on the
subject....www.peternolan.com

Best Regards

Peter

Reply to this Message