Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / General / Data Warehousing / April 2005

Tip: Looking for answers? Try searching our database.

OLTP vs Reporting Database vs OLAP Database

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Robert Lie - 26 Apr 2005 04:22 GMT
Dear All,

I have a fundamental questions regarding my design of my SQL Server
Database.

Current SQL Server 2000 Design Setting (in one server)
-1 Database for OLTP
-1 Database for OLAP -> Generating Cubes

Planned SQL Server 2000 Design Setting (in two servers):
-1 Database for OLTP -> at Server A
-1 Database for Reporting Purposes  -> at Server B
-1 Database for OLAP  -> at Server B

My Question -> Is it necessary to separate reporting database and OLAP
database?
Any sugestion please.

Thanks

Robert Lie
Jéjé - 26 Apr 2005 05:06 GMT
if you want to provide reports based on a copy of your OLTP database, so you
can create a "read-only" database optimized for reporting purpose.
you can provide (near)real time reports.
this usage as an advantage: you don't impat you operationnal server by
executing complex queries. but.. garbage in garbage out...

if you want to have a common and cleansed database for both reporting and
analysis to insure that you'll allways display the same information, use the
same database for RS + OLAP. but, generally, this database in not real-time
updated.

so what is your primary usage for your reports?
- operationnal purpose
- analytical purpose

but you can also provide reports based on the 2 databases regarding the
performance of your queries.
if you have some performance problems with your OLTP database (the copy on
the server B) then the OLAP database and cubes will improove the response
time.

also, look at the number of users... 100 users on a 10seconds report vs 100
users on a 1second report...

> Dear All,
>
[quoted text clipped - 17 lines]
>
> Robert Lie
Robert Lie - 26 Apr 2005 05:29 GMT
Here my considerations
1. Reporting Database:
    - To provide operational reports with 10 minutes delay.
    - Not Disturbing OLTP Server
    - Incrase response time for users who access reports (estimation
     max 20 users in the same time)
2. OLAP Database:
    - To generate Cubes
    - To boost the Cubes generation performance since it won't            
affected by users who access reports.
    - For analytical purpose
And both of them are not real-time.

Please explain further about garbage in garbage out?
So what do you think of my considerations?

Thanks a lot

Robert Lie

> if you want to provide reports based on a copy of your OLTP database, so you
> can create a "read-only" database optimized for reporting purpose.
[quoted text clipped - 41 lines]
>>
>>Robert Lie
Jéjé - 26 Apr 2005 06:09 GMT
creating a copy of the OLTP database is good choice
you can use the log shipping function to do this (or other methods)
(how do you plan to synchronize your databases?)

generating cubes will not lock your database if you do incremental updates
on a  optimized cube.  (or just a litlle lock)
Partitions will help you for the cube process step.

But your design appear to be good.

garbage in garbage out = bad data quality in the source = bad quality in
output (non existant clientID, duplicated product name, duplicated
records...). so an ETL tool can improove the data quality during the
process. or if you want to synchronize multiple sources (like reference
tables and operationnal tables)
But these steps reduce the loading time by adding data cleansing time.

if you think next step (which is SQL 2005) you'll have a lot of new features
to help you.
for example, you will be able to load directly  a partition of your cube
while you'll load your database, so 1 read and 2 destinations
(and there is a lot of options)

> Here my considerations
> 1. Reporting Database:
[quoted text clipped - 61 lines]
>>>
>>>Robert Lie
Robert Lie - 26 Apr 2005 07:59 GMT
Actually what I want to do is not just copy the OLTP Database, but DTS
from OLTP tables to summaries form Tables to eliminate the join and
complex calculation when show up some reports.
-> What do you think???

Since my SQL Server 2000 is standard edition so I can't do a kind of
cube partition.

Thanks

> creating a copy of the OLTP database is good choice
> you can use the log shipping function to do this (or other methods)
[quoted text clipped - 84 lines]
>>>>
>>>>Robert Lie
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.