|
Home > Archive > MS SQL Data Warehousing > October 2006 > When to use a Datawarehouse for reporting?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
When to use a Datawarehouse for reporting?
|
|
| Henrik Skak Pedersen 2006-10-25, 6:00 am |
| Hello,
We are about to create a new reporting platform for our application. We
are considering different solutions and I would like to get some advice.
All the data for the application is in a Microsoft SQL Server Database and
we would like to use Reporting Services as our reporting platform.
Would you recommend me to create a datawarehouse and then create the reports
against that, or should I create the reports against the raw database?
Thanks
Henrik Skak Pedersen
| |
|
| analyze the complexity of the source system the usage of this server and the
reporting requirements.
so...
if the source system contain too many tables, creating queries against this
database will be slow and hard to create, also the indexes of the database
will not be optimize for reporting usage.
(and/or) if the source server is allready too busy, executing complex
queries will slow down all users.
(and/or) if you don't need real time reports
then a DW will be great for you
there is other parameters to consider, like synchronizing multiple sources,
doing some cleansing, applying some business rules, frequency of access,
number of end users etc...
there is other options, like using the SQL Server replication feature to
replicate the operationnal data to a reporting server, same database
structure, but on a server dedicated for reporting usage (less impact for
the end user). and the sync. can be real time or near real time.
identify correctly your needs and you'll found the solution...
"Henrik Skak Pedersen" <skak@community.nospam> wrote in message
news:uaJ9AXK3GHA.1256@TK2MSFTNGP02.phx.gbl...
> Hello,
>
> We are about to create a new reporting platform for our application. We
> are considering different solutions and I would like to get some advice.
>
> All the data for the application is in a Microsoft SQL Server Database and
> we would like to use Reporting Services as our reporting platform.
>
> Would you recommend me to create a datawarehouse and then create the
> reports against that, or should I create the reports against the raw
> database?
>
> Thanks
>
> Henrik Skak Pedersen
>
>
| |
| Charles Wang[MSFT] 2006-10-25, 6:00 am |
| Dear Henrik,
My understanding of your issue is:
You wanted to use Reporting Services to setup a reporting platform. You
need some advices if you need to create a data warehouse for it.
If I have misunderstood, please let me know.
According to my experiences, If your database is very large and coplex, I
highly recommend that you setup a data warehouse for your reporting
platform. Raw database is dynamic and requires high performance. If reports
are produced from the raw database, the performance will apparently
degrade, especially with huge history data. Ideally you can setup a data
warehouse even a simple model fantastically improving the performance.
Basically the data extraction is as following:
Raw Database -> Data Warehouse -> Report Platform
Firstly, you need to design a high level data model which is used to hold
aggregated data. If your database is large but not very large, in a simple
way, you can create an application or just use database jobs to
periodically extract, compute and aggregate data from raw data source, then
fill the result into your data warehouse. If your database is huge and
complex, I recommend that you use Analysis Service (OLAP) to build complex
report.
Also, I would like to provide some references on designing a data warehouse
for you:
Chapter 17 - Data Warehouse Design Considerations
http://www.microsoft.com/technet/pr...part5/c1761.msp
x?mfr=true
Defining Data Warehouse Structures
http://download.microsoft.com/downl...e7-b468-2ecfc4b
1c0df/ Defining_Data_Wareho
use_Structures.ppt
If you have any other questions or concerns, please feel free to let me
know. It's my pleasure to be of assistance.
Sincerely,
Charles Wang
Microsoft Online Community Support
====================
====================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
====================
====================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
====================
====================
==============
|
|
|
|
|