Database Warehousing Questions

[Adeel Ahmed]

Hi,

I am planning for database warehousing in SQL Server 2005 but confused where
to start.

Actually we have a web site in Classic ASP that is running on production
from 6 years and now we are planning to convert this site into ASP.Net C#,
Since the site is live from six years the database size is huge specially
our two tables that has the visitor information.

I have following question regarding DW:

Q1: I want to create a DW and needs to transfer historical data (that is
older than 1 month) from live database, so what is the best recommended tool
OR technique to transfer historical data from live database to DW database
in SQL Server 2005? (Replication OR SSIS Package) I just want to retain only
current month data in live database.

Q2: We have created lots of reports in classic ASP and I want to use that
reports on real time database as well as historical database, for e.g. if
the visitor select date within one month than records should pull from Live
database but if visitor select older date than records should pull from
historical DW database. And what if the user select date from current year
than records should pull from historical and live database both. So what is
the recommended way to achieve that goal? Do we manually check the date
criteria on each report and change the data source according to it?

Q3: What should be application architecture?


I will appreciate any help regarding Data warehouse.

Thanks

[Charles Wang [MSFT]]

Dear Adeel,
Welcome to Microsoft MSDN Managed Newsgroup Services.

Regarding your three questions, please refer to my answers below:
Anser to Q1: Generally for data transportation, we recommend that you use
SSIS to export data from one database to another. However for large amount
of data, if you encounter performance issue, please consider using bcp
utility to export data. Bcp utility is a lightweight and a known very
efficient way for exporting data.
You may refer to:
bcp Utility
http://msdn.microsoft.com/en-us/library/ms162802(SQL.90).aspx

Answer to Q2: For report purpose, I think that it is better to direct all
the queries to your historical DW. Even better if you separate the
historical DW to another server for performance consideration. You can
schedule an ETL job using SSIS to timely extract the current month data
from your live database and export them to the historical database.

Answer to Q3: This question is general. Basicaly I think that if the
definition of the responsibilities of the parts of your faced problems,
databases, ETL and server deployments, it may not be a difficult task for
you. I would like to provide some articles here just for your reference:
Best Practices for Data Warehousing with SQL Server 2008
http://msdn.microsoft.com/en-us/library/cc719165.aspx
Scaling Up Your Data Warehouse with SQL Server 2008
http://msdn.microsoft.com/en-us/library/cc719182.aspx
Data Warehouse Design
http://www.horsburgh.com/h_dataw.html

Also I appreciate your understanding that our MSDN Managed Newsgroup
Services is only focused on resolving break/fix issues. For advisory
questions, we may only give you some general suggestions. If you want to
get more detailed information, it is recommended that you contact Microsoft
Advisory Services. Microsoft Advisory Services provides short-term advice
and guidance for problems not covered by Problem Resolution Service as well
as requests for consultative assistance for design, development and
deployment issues. For specific information about the types of Advisory
Services available, visit the
http://support.microsoft.com/gp/advisoryservice web page.

Thank you!

Best regards,
Charles Wang
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: ***@microsoft.com.
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial

response from the community or a Microsoft Support Engineer within 2
business day is

acceptable. Please note that each follow up response may take approximately
2 business days

as the support professional working with you may need further investigation
to reach the

most efficient resolution. The offering is not appropriate for situations
that require

urgent, real-time or phone-based interactions. Issues of this nature are
best handled

working with a dedicated Microsoft Support Engineer by contacting Microsoft
Customer

Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx.
============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================