Need expert's opinion on table schema...

Hello,
I have seen different types of table schemas employed in
applications and I would like to get the opinions of the
SQL experts...
First some definitions:
LU = Lookup
IP = Index Person
IA = Index Account
Background sample info:
Fred has two accounts number 9999 and 8888
Joe has one account 7777
I can represent these accounts in the following
table examples:
(probably lots more ways to do this, but I am interested
in these two architectures, but would be willing to
entertain other schemas)
Example 1:
Person LU Account
IP Name IP IA IA Num
1 Fred 1 1 1 9999
2 Joe 1 2 2 8888
2 3 3 7777
Example 2:
Person Account
IP Name IA IP Num
1 Fred 1 1 9999
2 Joe 2 1 8888
3 2 7777
My questions to the people who live and breath SQL,
What are the pros and cons to the above table schemas?
Please be specific and if necessary write me in a
separate email : Mike@.e-liss.org
Thanks
Mike"mike" <Mike@.e-liss.org> wrote in message
news:3ff801c3b129$dd3c06e0$a601280a@.phx.gbl...
> Hello,
> I have seen different types of table schemas employed in
> applications and I would like to get the opinions of the
> SQL experts...
> First some definitions:
> LU = Lookup
> IP = Index Person
> IA = Index Account
> Background sample info:
> Fred has two accounts number 9999 and 8888
> Joe has one account 7777
>
> I can represent these accounts in the following
> table examples:
> (probably lots more ways to do this, but I am interested
> in these two architectures, but would be willing to
> entertain other schemas)
>
> Example 1:
> Person LU Account
> IP Name IP IA IA Num
> 1 Fred 1 1 1 9999
> 2 Joe 1 2 2 8888
> 2 3 3 7777
>
> Example 2:
> Person Account
> IP Name IA IP Num
> 1 Fred 1 1 9999
> 2 Joe 2 1 8888
> 3 2 7777
>
Example 1 uses a linking table, and is a more general structure than Example
2. Using a linking table it is possible to model relationships 1-1 1-many
or many-many. Using a foregn key you can only model 1-1 or 1-many. From
just that, you should prefer Example 2. One of the guiding principles of
data modeling is to use the most specific model that meets your needs.
From a performance point of view, Example will be superior as well. Notice
that you can transform Example 1 into Example 2.
CREATE VIEW v_Account
as
select Account.IA, LU.IP, Account.Num
from Account join LU
on Account.IA = LU.IA
But to add or delete anaccount will require 2 operations instead of one.
Also queries will have to perform an additional and unnecessary join.
Or, think of it this way:
LU has a 1-1 relationship with Account. Whenever you see a datamodel with a
1-1 relationship, the related should be merged. There are exceptions, but
all rules have execptions.
David|||>--Original Message--
>"mike" <Mike@.e-liss.org> wrote in message
>news:3ff801c3b129$dd3c06e0$a601280a@.phx.gbl...
>> Hello,
>> I have seen different types of table schemas employed
in
>> applications and I would like to get the opinions of
the
>> SQL experts...
>> First some definitions:
>> LU = Lookup
>> IP = Index Person
>> IA = Index Account
>> Background sample info:
>> Fred has two accounts number 9999 and 8888
>> Joe has one account 7777
>>
>> I can represent these accounts in the following
>> table examples:
>> (probably lots more ways to do this, but I am
interested
>> in these two architectures, but would be willing to
>> entertain other schemas)
>>
>> Example 1:
>> Person LU Account
>> IP Name IP IA IA Num
>> 1 Fred 1 1 1 9999
>> 2 Joe 1 2 2 8888
>> 2 3 3 7777
>>
>> Example 2:
>> Person Account
>> IP Name IA IP Num
>> 1 Fred 1 1 9999
>> 2 Joe 2 1 8888
>> 3 2 7777
>>
>Example 1 uses a linking table, and is a more general
structure than Example
>2. Using a linking table it is possible to model
relationships 1-1 1-many
>or many-many. Using a foregn key you can only model 1-1
or 1-many. From
>just that, you should prefer Example 2. One of the
guiding principles of
>data modeling is to use the most specific model that
meets your needs.
>From a performance point of view, Example will be
superior as well. Notice
>that you can transform Example 1 into Example 2.
>CREATE VIEW v_Account
>as
>select Account.IA, LU.IP, Account.Num
>from Account join LU
> on Account.IA = LU.IA
>But to add or delete anaccount will require 2 operations
instead of one.
>Also queries will have to perform an additional and
unnecessary join.
>Or, think of it this way:
>LU has a 1-1 relationship with Account. Whenever you
see a datamodel with a
>1-1 relationship, the related should be merged. There
are exceptions, but
>all rules have execptions.
>David
>
>.
>
David,
Are there any significant draw backs to not using Ex 1 ?
If not, the what is the purpose of the LU table?
So Fred and Joe can share the same account ?
ie:
IP IA
1 1
2 1
Where in example 2 this is not possible ?
Thanks
Mike|||"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:043e01c3b134$23540330$a001280a@.phx.gbl...
> >--Original Message--
> >
> >"mike" <Mike@.e-liss.org> wrote in message
> >news:3ff801c3b129$dd3c06e0$a601280a@.phx.gbl...
> >> Hello,
> >>
. . .
> David,
> Are there any significant draw backs to not using Ex 1 ?
> If not, the what is the purpose of the LU table?
No.
> So Fred and Joe can share the same account ?
> ie:
> IP IA
> 1 1
> 2 1
> Where in example 2 this is not possible ?
Exactly right. It just allows modeling different kinds of relationships.
David

Need expert help in database design..

Hello friends,I have to design a database for banking application,but it is my first db design so i need some expert advice.

Data base has some performance constraint i.e high output ,minimum query time,need to process 2 to 5 million transaction per day.

Further specification can be revealed if appropriate help is provided.

your advice will be of great help for me.

Thx

Are you requesting a 'paid' consultant to help you create a cohesive design, or, are you 'hoping' that a bunch of volunteers will take their time and help you do something that you are getting paid to do?

You know the problems that can occur when something is 'designed by committee'...

Feel free to put out YOUR design considerations and get feedback -but having a group of 'anonymous yahoos' (present company included) do your design for you is heading for disaster.

|||

Thankyou for replying to my question.

Here we are trying to design a solution for " KYC(know your customer )/AML(anti money laundering)" .These two terms stems from

various compliance in banking sector, i.e Bank secrecy ACT ,US patriotic act(if you are from America) and various other compliance related to financial industry.

We need to create a monitoring mechanism on various financial transactions(2-5 million per day,minimum requirement) either in real time or in batch mode.

In this mechanism we will monitor transactions on various predefined rules and rules using techniques of data mining.

The data related to various transaction is intended to be collected using integration services of sql server 2005.

I am looking for some volunteer,but one thing I can assure u that i will need avice only for some key areas('ll not vex u too much) i.e preventing foolish mistakes which can lead to desaster at later stage and some high performance(high data retrival speed in large database) related issues.

I am more concerned about the performance issue related to data base, you can imagin how big a bank cuctomer's database can be and data related to their every day transaction.

Plz let me know if you are intrested or can advice me some resource related to experienced data base design.

For helping hand.

Thx once again.

Gagan

|||

You are very welcome to post your questions, proposals for 'debate', and things to ponder. Some here may contribute, depending upon their time and interest.

However, I think that perhaps this isn't the best forum. I would consider the [ Transact SQL ] or [ Database Engine ] since they seem to have more 'traffic' -translating into readers and possible contributors.

There are a couple of documents referenced in this thread: http://forums.microsoft.com/MSDN/showpost.aspx?postid=1436785&siteid=1

Good Luck. (This thread is now closed.)

sql

Need Expert Help and Advice. Thank You.

Hello,

Consider I have a String:

Dim MyStringAs String ="Hello"

or an Integer:

Dim MyIntegerAs Integer = 100

or a class which its properties:

Dim MyClass As New MyCustomClassMyClass.Property1 ="Hello"MyClass.Property2 = Unit.Pixel(100)MyClass.Property3 = 100

Or even a control:

Dim MyLabelAs LabelMyLabel.Id ="MyLabel"MyLabel.CssClass ="MyLabelCssClass"

Is there a way to save a String, an Integer, a Boolean, a Class, a Control in an SQL database?

Something like:

Define something (Integer, String, Class, Control, etc)

Save in SQL 2005 Database

Later in code Retrive from database given its ID

Is this possible?

How should I do this?

What type of SQL 2005 table field should be used to store the information?

Thanks,

Miguel

Yes it is possible

You can serialize your object (some extra design work) and save as XML in XML field. The advantage is that you can search this XML on SQL server. Another solution is to save your object as binary data in image field or varbinary(max) (which is now recommended replacement for image field). You probably have to have another field in your database to identity your object.

Thanks

Need an Expert Advice

I am using VS 2003 and trying to add a web reference reportservices2005.asmx on windows 2003 server.

I am able to do the same thing in win XP machines and works perfectly fine. now facing the problem with windows 2003 server.

i need to run or render the reports(RS2005 version) from VS 2003.

Pleeeeeease help.

Not sure why you're encountering this problem on W2K3. You can create your solution on the XP machine and then just copy it over to the 2K3 box.

Hope that helps,

-Lukasz

|||

Thks Lukasz.

I found some info on that, on windows 2003 server it uses IIS6.0, iis 6.0 has it own processing mode it does not use aspnet_wp.exe process when you run or render a report.

it ggives an error saying you cannot run two versions in one single process mode.

for it we need to change the process mode to run www using iis 5.0 isolation mode. that way we can run multiple versions of .net in one process mode. thats what happens on xp based machines.

|||

Oh, that's an entirely different problem. You cannot run two version of .Net Framework in the same process space. In W2K3, you can create a new application pool for the report server service and then assign it to the report server virtual directory. You can do this in the Reporting Services configuration tool. When you use this tool, the application pool will be setup they way you need it for RS.

Let me know if that doesn't solve the problem.

-Lukasz

|||

Lukasz, i noticed a problem with that, it is not even allowing me to add a webreference from my VS 2003 project to add a reportingservice2005.asmx

this problem is only happening on windows 2003 server and it gives me the above message and logs an error under events (application log)

if i am creating or trying to add a webreference on a win xp machine, it does'nt create that problem for me.

only on w2003 server this is happening. what options do i have fro vs 2003 calling a webservice render method reportexecution2005.asmx or reportingservice2005.asmx

Thanks Lukasz.

Need an Expert Advice

I am using VS 2003 and trying to add a web reference reportservices2005.asmx on windows 2003 server.

I am able to do the same thing in win XP machines and works perfectly fine. now facing the problem with windows 2003 server.

i need to run or render the reports(RS2005 version) from VS 2003.

Pleeeeeease help.

Not sure why you're encountering this problem on W2K3. You can create your solution on the XP machine and then just copy it over to the 2K3 box.

Hope that helps,

-Lukasz

|||

Thks Lukasz.

I found some info on that, on windows 2003 server it uses IIS6.0, iis 6.0 has it own processing mode it does not use aspnet_wp.exe process when you run or render a report.

it ggives an error saying you cannot run two versions in one single process mode.

for it we need to change the process mode to run www using iis 5.0 isolation mode. that way we can run multiple versions of .net in one process mode. thats what happens on xp based machines.

|||

Oh, that's an entirely different problem. You cannot run two version of .Net Framework in the same process space. In W2K3, you can create a new application pool for the report server service and then assign it to the report server virtual directory. You can do this in the Reporting Services configuration tool. When you use this tool, the application pool will be setup they way you need it for RS.

Let me know if that doesn't solve the problem.

-Lukasz

|||

Lukasz, i noticed a problem with that, it is not even allowing me to add a webreference from my VS 2003 project to add a reportingservice2005.asmx

this problem is only happening on windows 2003 server and it gives me the above message and logs an error under events (application log)

if i am creating or trying to add a webreference on a win xp machine, it does'nt create that problem for me.

only on w2003 server this is happening. what options do i have fro vs 2003 calling a webservice render method reportexecution2005.asmx or reportingservice2005.asmx

Thanks Lukasz.

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

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