Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2004
    Posts
    13

    Unanswered: Database warehousing questions

    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:

    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.

    Thanks

  2. #2
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    1. SSIS

    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.

    Hope this helps.

  3. #3
    Join Date
    Sep 2004
    Posts
    13
    Thanks, for the detail reply, i don't get ROLAP MOLAP or HOLAP architechture. can you please refer some article on these or give me some guidline to build DW step by step.

  4. #4
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    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.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ROLAP MOLAP or HOLAP

    Google google google, google is your friend.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •