ETL

  • Hi all,

    I am putting up a data warehouse, so I have a relational DB with different entities, but am adopting some method of which am not very sure about.

    It is a software reporting database, to update the tables, what I do is drop the data contents of these tables (using the coloured method below) and re-load with new information, but am not sure if the data consistency is maintained in the process.

    What I actually do is:-

    1.Remove the constraint

    2.Drop the table content

    3.Load with new information

    4.Reactivate constraint

    Please, bare in mind I am also using sql server CASCADE functionality where required.

    So, my question is, in a situation like mine where the whole information needs to be updated each time a new report is ran, could this be a possible solution? If there’s one, what is the standard procedural process?

    Or what is the usual procedure in data warehouse context?

    Please advice. Many thanks in advance.

    Begin

    Begin Try

    ALTER TABLE [FootprintMapARP] NOCHECK CONSTRAINT ALL--FK_FootprintMapARP_Product

    ALTER TABLE [FootprintsARP] NOCHECK CONSTRAINT All--FK_FootprintMapARP_Product

    ALTER TABLE [SoftwareInstall] NOCHECK CONSTRAINT All

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Product]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

    Delete from Product

    ----------------------------------------------------------------------------------

    -----------Select statements or other forms of procedures come’s here------------

    ALTER TABLE [FootprintMapARP] CHECK CONSTRAINT ALL--FK_FootprintMapARP_Product

    ALTER TABLE [FootprintsARP] CHECK CONSTRAINT ALL

    ALTER TABLE [SoftwareInstall] CHECK CONSTRAINT ALL[/b]

  • Shall we understand Data Warehouse core tables are being truncated and fully reloaded in a daily basis?

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • Yes Pablo, a scheduled job does that daily. Any more help?

  • greg eze (6/29/2010)


    Yes Pablo, a scheduled job does that daily. Any more help?

    Yes.

    1 - Do not use delete, use truncate - much faster.

    2 - Drop constraints / Truncate / Load / Enable constraints strategy makes sense.

    3 - If you trust source data (you should) FKs can be added with nocheck option

    4 - Depending on your local circumstances you may want to drop indexes at the beginning of the process and build after data gets loaded.

    Having said that, my question on previous post has a reason. Environment was described as being a Data Warehouse and reloading all base tables in a daily basis is not what you do in a Data Warehouse environment but in a reporting resource. I was just trying to confirm the scenario 😉

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • Many thanks Pablo,

    Actually, I wanted to make sure I wasn’t doing anything out of normal (standards) and your response confirmed that. Yes! You’re right, it isn’t really a data warehouse, but a reporting database.

    Thanks for your time.

  • Glad to help.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply