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
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:12FD68B7-BD47-4C86-BA66-99BE1EDF9473@.microsoft.com...
> 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/de...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:
> 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/pro.../c07ppcsq.mspx
http://msdn.microsoft.com/library/de...tbpwithdts.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/is...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/pro.../anservog.mspx
> Thanks
|||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?
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:12FD68B7-BD47-4C86-BA66-99BE1EDF9473@.microsoft.com...
> 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
|||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).
> 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.
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
Showing posts with label legacy. Show all posts
Showing posts with label legacy. Show all posts
Monday, March 19, 2012
Need an expert advice
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 repor
t
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"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:12FD68B7-BD47-4C86-BA66-99BE1EDF9473@.microsoft.com...
> 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/d...>
_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/pr...s/c07ppcsq.mspx
http://msdn.microsoft.com/library/d...ntbpwithdts.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/i.../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/pr...n/anservog.mspx
> Thanks|||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?
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:12FD68B7-BD47-4C86-BA66-99BE1EDF9473@.microsoft.com...
> 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|||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).
> 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.
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
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 repor
t
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"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:12FD68B7-BD47-4C86-BA66-99BE1EDF9473@.microsoft.com...
> 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/d...>
_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/pr...s/c07ppcsq.mspx
http://msdn.microsoft.com/library/d...ntbpwithdts.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/i.../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/pr...n/anservog.mspx
> Thanks|||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?
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:12FD68B7-BD47-4C86-BA66-99BE1EDF9473@.microsoft.com...
> 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|||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).
> 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.
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
Friday, March 9, 2012
Need advice on approach
I am new to DTS/SSIS and would like some input on an approach to solve a problem.
I have a solution using SQL Server 2005 and another legacy solution running on Access. Status records are written to a table in SQL Server and as they are written, I need to write a record to a table in Access. This needs to be as real-time as possible.
I thought about writting a managed stored procedure in C# so I could also access MS Access, but someone pointed me to DTS.
Records are constantly written to the status table and need to remain in there even after they are processed.
Can you recommend an approach or an article to read?
Thanks,
Steve
This could be done with SSIS with a little work but I think if your are talking about a very small amount of data that needs to be written to access each time, then I think the SP idea is better or something in general from the SQL engine to Access rather than involving SSIS. So, you may want to repost in one of the more general SQL forums.
Now if you have SSIS already involved in the pitcure some where, then the SSIS idea might still be worth investigating. For example if the part of your story "Status records are written to a table in SQL Server " was itself being done by SSIS, then enhancing the SSIS scope could be a good approach.
good luck
Subscribe to:
Posts (Atom)