Hi There,
I got a generic DW question.
Let me preface this
I need to design a report that displays data for 3 branches of same
companies, Branch A ,Branch B and Branch C. The problem is that each Branch
has their own distinct server and database. The schema is identical between
them, just
different data in each.

The report is identical for each Branch, the only different is that the
report needs to pull data from all the three Databases A,B,C to display the
report data for that Company as an aggregated view and Also data for each
branch individually.

How can I design the report or setup the data source.

1) One possible option I guess would be to create a Linked Server and
Distributed view that combines data from identical tables in 3 databases, and
use that view as the datasource in the report.

2) Other option is moving data to the centralized server and report from
that,
which would cause data redundancy both at local branch and at centralized
server and huge vloume of data movement and keeping track of data.

Just looking for what others have done in similar circumstances so I don't
spend multiple days architecting the wrong approach.

Thanks!
S

Centralize data is the better solution.
maybe there is some data to copy, but any query is local, so the response
time is good.
also, you can retrieve data for your reports only instead-of detailed
information.

"ERS Developer" <ERSDeveloper (AT) discussions (DOT) microsoft.com> wrote

Hi S,
the generic question is
'I have distributed data in operational systems, how do I perform
consolidated reporting?'

and the generic answer is:

'bring the data together and integrate it via ODS/DW style processing
system depending on what your needs are. Then, if the data needs to be
re-distributed from the centralised data store back out to various
areas then do that.'

You will almost certainly find that even though the systems are the
same the data in it will be different and will not 'automatically' line
up unless someone forced the user community to use some set of data
standards which almost never happens.

However, with todays line speeds and tools like RS presenting data in
web browsers the need for distributring that data back out has
dramatically reduced. I rarely re-distribute the data back out to so
called 'data marts'...I have been an advocate of 'if the data is
valuable and important it is worth centralising and managing it
properly' for over 20 years now... ;-).....I have seen many people talk
about 'fetch the data from the operational system when it is needed'
come and go.....many are still coming along and selling that as 'the
way to do reporting and analysis'......the folks who go that way
generally regret it very much and end up migrating to an ODS/DW style
solution sooner or later....especially as volumes increase...

Best Regards

Peter Nolan
www.peternolan.com

Hi Peter,

Thanks for the clarification always there has been a debate whether or not
to move the data around.Looks like unless we do that no way we can get
efficient reports Let me try the DW.

/
S

"Peter Nolan" wrote:

I think the answer depends on how much data, the query performance
requirement, and the frequency of the report. If there is a relatively large
amount of data that would need to be moved, the report is only run once per
month, and the user doesn't really care if the report takes 20 minutes vs. 2
minutes to run, then just setup a partitioned view.
If there are multiple reports that could make use of the localized data,
then the case for building a localized copy of the data would be somewhat
stronger.

"ERS Developer" <ERSDeveloper (AT) discussions (DOT) microsoft.com> wrote