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:
Q1: I want to create a DW and needs to transfer historical data(that is older than 1 month) from current database, so what is the best recommended tool OR technique to transfer historical data from current database to DW database in SQL Server 2005? (Replication OR SSIS Package)
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 historical 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? 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.
2. I don't understand why you would ever do that. It's a lot of trouble, especially since the DW will presumably have a different schema to the operational system. Change your reports to point at the DW - that's what it's for.
3. Basics: integration tool, staging database, metadata repository, DW, presentation tier. Optionally a ROLAP, MOLAP or HOLAP architecture. A key decision is the presentation/analytical tool. Do your business users just want static reports or do they need analytic tools such as PerformancePoint, MicroStrategy, etc?
I would strongly advise you to get some help. DW projects have an alarmingly high failure rate and mistakes can be extremely expensive. Unless you are sure the solution is trivial you should hire some expert advice and assistance. Don't attempt to do it just from a book or, worse, learn by trial and error.
Take a more top-down approach and forget the technical details for the moment. Firstly, do you have a business owner and a clear understanding of the business objectives and potential return on investment? BI projects live or die by how well they deliver for the business. Executive-level sponsorship is often needed to make things happen. Don't assume that if you build something then users will flock to your door and the work will pay for itself.
Once you know the goals, you should have some idea of scope, functionality and what kind of budget you can afford. You can then decide whether you have the expertise in-house or need to outsource or contract for the work. You can't trust any "step by step" cook-book approach to make up for lack of expertise. Building a BI solution (it's not just a Data Warehouse) isn't like baking a cake to a recipe. It is more like designing and building a bridge or a road, ie it requires a range of skills, experience and an understanding of how to solve the problems rather than just follow a list of instructions.
Hope this helps. I'm not trying to avoid answering directly but your line of questioning seems a bit odd and unfocussed if this is actually a real project you are planning to implement.