June 29, 2010 at 1:52 am
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]
June 29, 2010 at 11:20 am
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.June 29, 2010 at 1:10 pm
Yes Pablo, a scheduled job does that daily. Any more help?
June 29, 2010 at 2:34 pm
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.June 30, 2010 at 1:40 am
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.
June 30, 2010 at 7:03 am
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