Re: New datawarehouse

From: Danny (istdrs_at_flash.net)
Date: 11/13/04

  • Next message: Rea Peleg: "can not open dts - password lost..." 
    Date: Sat, 13 Nov 2004 15:09:55 GMT

    Dave,

    Surprising that you didn't seem to spark a lively conversation on this
    question.

    If you really need a DW then the right answer is to take a long term
    approach and design an enterprise level data warehouse where the
    organization can go to analyze data from a single perspective that has been
    defined, researched, tested, and authorized by the business as the single
    point of truth. Sounds like a lot of work? Well perhaps what you are
    really needing is a reporting operational data store, ODS. This is a common
    starting point of a DW. There are many reasons for needing a reporting ODS.

    Adhoc queries are contending with transactional inserts/updates
    The OLTP is slowly down due to volume of data and the OLTP doesn't need the
    old data
    Queries are too slow against the OLTP normalized schema
    Queries are to hard for users to create against the complex OLTP schema.

    An assumption here is that archive means copy and remove. If you are
    intending to remove the data from the OLTP then replication is out as a
    solution.

    Based on the limited information provided and not knowing budget or skill
    levels, I would recommend:
    Buying a separate SQL server
    Defining an easier/faster schema for the data your users need (Star or just
    denormalized and use surrogate keys)
    Depending on skill level, budget, and transformation complexity use TSQL,
    DTS, or a third party ETL tool to extract, transform, load, and purge the
    incremental data
    Be careful of updates and deletes as the source system may not clearly
    indicate them in the schema
    The usual system development life cycle and operational techniques and
    should be applied.

    Good luck,
    Danny

    "Dave Mortenson" <dmortenson@ident.com> wrote in message
    news:OXrlm7ByEHA.1396@tk2msftngp13.phx.gbl...
    > What is the best way to set up a datwarehouse? Do I use replication or dts
    > packages or what? What I need to do is archive production data every two
    > weeks to this datawarehouse. Then I will need to be able to query the
    > archive data. What is the best way to do this? And how do I go about doing
    > it?
    >
    > Thanks in advance for the help.
    >
    >
    > Dave
    >

  • Next message: Rea Peleg: "can not open dts - password lost..."

    Relevant Pages

    • Re: Named Mistakes and Questionable Practices
      ... But they both involve mixing data and meta-data in a schema! ... Sounds like you are talking about a data warehouse and not OLTP. ... They are not keys by definition; a key has to be a subset ... they can actually get the physical location as a ...
      (comp.databases.theory)
    • RE: Warehouse Server Requirements
      ... Dataware house usually is organized to archive history data of OLTP Server. ... These queries access and process large portions of the ... A data warehouse offloads the historical data from the OLTP, ... which does not need additional indexes for their support. ...
      (microsoft.public.sqlserver.datawarehouse)
    • Re: PyTable?
      ... A database plan ... Normally the schema is created in Python code, ... queries, for instance. ... As to BasicProperty, its purpose is to allow for defining rich ...
      (comp.lang.python)
    • Re: Link server very slow
      ... DDL = Data Definition Language. ... It's basically the schema of the ... DML = Data Manipulation Language. ... It's the queries that you're running ...
      (microsoft.public.sqlserver.server)
    • Re: Create star schema in SQL Server 2005?
      ... if you are not confortable to transform your data from your OLTP to a DW ... and if your OLTP database has a simple schema ... then create your cubes and dimensions against these views. ... and then create the source database and the table required. ...
      (microsoft.public.sqlserver.datawarehouse)