{"id":85,"date":"2018-06-27T21:09:56","date_gmt":"2018-06-27T21:09:56","guid":{"rendered":"http:\/\/jackofalltradesmasterofsome.com\/blog\/?p=85"},"modified":"2020-04-15T14:18:03","modified_gmt":"2020-04-15T14:18:03","slug":"real-estate-data-warehouse-accelerating-the-staging-process","status":"publish","type":"post","link":"http:\/\/jackofalltradesmasterofsome.com\/blog\/2018\/06\/27\/real-estate-data-warehouse-accelerating-the-staging-process\/","title":{"rendered":"Accelerating the Staging Process for your Data Warehouse"},"content":{"rendered":"

Real Estate Data Warehouse \u2013 Accelerating the Staging Process<\/strong><\/p>\n

The script below can be used to build a staging environment for any sort of industry and not just real estate related databases. The specifics of a RE Data warehouse will be covered in future blog post. It will allow you to\u00a0Accelerating the Staging Process for your Data Warehouse<\/p>\n

When starting the process to capture data analytics, whether you planning to eventually build a data warehouse or eventually feed a big data Hadoop cluster, it helps to stage your data away from your source systems. This provides many benefits; the primary being having a copy of data to work with and process that is no longer in the transactional operational system. Having large processes or queries running against your transactional operation system provides unnecessary risk, can introduce bottlenecks or slowdowns and even open security holes that may not be needed. When you have a staging environment, all you need is one service level account managed by IT security that has read access to the source systems. From there, building a scheduled refresh process to load this data as a blanket truncate and reload can be set up easily. Many tools for ETL can be used and robust auditing and scheduling should be set up but getting off the ground quickly to start prototyping and profiling your data will allow you to get moving a lot sooner and providing value to the business.<\/p>\n

For this reason, I wrote the SQL script below a while back to help me on new projects. Running this script against a linked server connection or a replicated database will quickly allow you to build a staging database with procedures to load all the data as truncate and reloads. This can then be wrapped in a master SQL procedure and scheduled, giving you a full Staging ETL process with out needing ETL tools. Remember, this is just an accelerator and will require some tweaking and optimization to get to a final state, but this should get you off the ground with your basic SQL based source systems.<\/p>\n

\/***********************<\/p>\n

Start of Script<\/p>\n

************************\/<\/p>\n

\/***********************<\/p>\n

Configuration<\/p>\n

************************\/<\/p>\n

DECLARE @sqlCommand varchar(1000)<\/p>\n

DECLARE @DatabaseNameStaging varchar(75)<\/p>\n

DECLARE @DatabaseNameSource varchar(75)<\/p>\n

SET @DatabaseNameStaging = ‘Staging’<\/p>\n

SET @DatabaseNameSource = ‘SourceDB’<\/p>\n

— Add all tables to ignore to this list<\/p>\n

DROP TABLE #TablestoIgnore<\/p>\n

CREATE TABLE #TablestoIgnore<\/p>\n

(<\/p>\n

TableName varchar(255)<\/p>\n

)<\/p>\n

INSERT INTO #TablestoIgnore<\/p>\n

Select ”<\/p>\n

–UNION<\/p>\n

–Select ”<\/p>\n

— Table to Store List of all Table is Source Database<\/p>\n

DROP TABLE #TableList<\/p>\n

CREATE TABLE #TableList<\/p>\n

(<\/p>\n

TableName varchar(255)<\/p>\n

)<\/p>\n

\/***********************<\/p>\n

Create Staging Database<\/p>\n

************************\/<\/p>\n

SET @sqlCommand = ‘IF NOT EXISTS(SELECT * FROM sys.databases WHERE NAME = ”’+@DatabaseNameStaging+”’)<\/p>\n

BEGIN<\/p>\n

CREATE DATABASE ‘+@DatabaseNameStaging+’<\/p>\n

— Set Logging to Simple<\/p>\n

USE master ;<\/p>\n

ALTER DATABASE ‘+@DatabaseNameStaging+’ SET RECOVERY SIMPLE<\/p>\n

END’<\/p>\n

EXEC (@sqlCommand)<\/p>\n

\/***********************<\/p>\n

Get List of All Tables<\/p>\n

************************\/<\/p>\n

SET @sqlCommand = ‘INSERT INTO #TableList SELECT DISTINCT T.name AS Table_Name<\/p>\n

FROM\u00a0\u00a0 ‘+@DatabaseNameSource+’.sys.objects AS T<\/p>\n

WHERE\u00a0 T.type_desc = ”USER_TABLE”<\/p>\n

AND T.name NOT IN (SELECT TableName FROM #TablestoIgnore)<\/p>\n

ORDER By 1′<\/p>\n

EXEC (@sqlCommand)<\/p>\n

–Create Drop and Create Statements<\/p>\n

SELECT ‘IF OBJECT_ID(”’ + @DatabaseNameStaging + ‘.dbo.’+ TableName +\u00a0 ”’, ”U”) IS NOT NULL DROP TABLE ‘ + @DatabaseNameStaging + ‘.dbo.’+ TableName\u00a0 + ‘;’ AS DropStatement,<\/p>\n

‘SELECT Top 1 * INTO ‘ + @DatabaseNameStaging + ‘.dbo.’+ TableName\u00a0 + ‘ From ‘ + @DatabaseNameSource + ‘.dbo.’+ TableName\u00a0 AS CreateStatement<\/p>\n

INTO #DatabaseStatements<\/p>\n

FROM #TableList<\/p>\n

–Create Drop and Create Statements<\/p>\n

— Run Drop Commands<\/p>\n

DECLARE @MyCursor CURSOR;<\/p>\n

DECLARE @MyField varchar(500);<\/p>\n

BEGIN<\/p>\n

SET @MyCursor = CURSOR FOR<\/p>\n

SELECT DropStatement FROM #DatabaseStatements<\/p>\n

OPEN @MyCursor<\/p>\n

FETCH NEXT FROM @MyCursor<\/p>\n

INTO @MyField<\/p>\n

WHILE @@FETCH_STATUS = 0<\/p>\n

BEGIN<\/p>\n

EXEC (@MyField)<\/p>\n

FETCH NEXT FROM @MyCursor<\/p>\n

INTO @MyField<\/p>\n

END;<\/p>\n

CLOSE @MyCursor ;<\/p>\n

DEALLOCATE @MyCursor;<\/p>\n

END;<\/p>\n

— Run Create Commands<\/p>\n

BEGIN<\/p>\n

SET @MyCursor = CURSOR FOR<\/p>\n

SELECT CreateStatement FROM #DatabaseStatements<\/p>\n

OPEN @MyCursor<\/p>\n

FETCH NEXT FROM @MyCursor<\/p>\n

INTO @MyField<\/p>\n

WHILE @@FETCH_STATUS = 0<\/p>\n

BEGIN<\/p>\n

EXEC (@MyField)<\/p>\n

FETCH NEXT FROM @MyCursor<\/p>\n

INTO @MyField<\/p>\n

END;<\/p>\n

CLOSE @MyCursor ;<\/p>\n

DEALLOCATE @MyCursor;<\/p>\n

END;<\/p>\n

\/***********************<\/p>\n

Create All Stored Procedures<\/p>\n

to Load Staging<\/p>\n

*** THIS SECTION MUST BE RUN AGAINST STAGING ENVIRONMENT ***<\/p>\n

*** This step may result in Error for Identity Tables. Those ETL’s will need to be created<\/p>\n

************************\/<\/p>\n

USE Staging<\/p>\n

— Run Create Commands<\/p>\n

BEGIN<\/p>\n

SET @MyCursor = CURSOR FOR<\/p>\n

SELECT TableName<\/p>\n

FROM #TableList<\/p>\n

OPEN @MyCursor<\/p>\n

FETCH NEXT FROM @MyCursor<\/p>\n

INTO @MyField<\/p>\n

WHILE @@FETCH_STATUS = 0<\/p>\n

BEGIN<\/p>\n

EXEC ( ‘IF OBJECT_ID(”spLoad’+@MyField+”’, ”P”) IS NOT NULL DROP PROC spLoad’+@MyField+”)<\/p>\n

EXEC ( ‘TRUNCATE TABLE ‘+@DatabaseNameStaging+’.dbo.’+@MyField+’<\/p>\n

CREATE PROCEDURE dbo.spLoad’+@MyField+’<\/p>\n

AS<\/p>\n

BEGIN<\/p>\n

SET NOCOUNT ON;<\/p>\n

— Insert statements for procedure here<\/p>\n

INSERT INTO ‘+@DatabaseNameStaging+’.dbo.’+@MyField+’<\/p>\n

SELECT * FROM ‘+@DatabaseNameSource+’.dbo.’+@MyField+’<\/p>\n

END’)<\/p>\n

EXEC ( ‘CREATE PROCEDURE dbo.spLoad’+@MyField+’<\/p>\n

AS<\/p>\n

BEGIN<\/p>\n

SET NOCOUNT ON;<\/p>\n

— Insert statements for procedure here<\/p>\n

INSERT INTO ‘+@DatabaseNameStaging+’.dbo.’+@MyField+’<\/p>\n

SELECT * FROM ‘+@DatabaseNameSource+’.dbo.’+@MyField+’<\/p>\n

END’)<\/p>\n

FETCH NEXT FROM @MyCursor<\/p>\n

INTO @MyField<\/p>\n

END;<\/p>\n

CLOSE @MyCursor ;<\/p>\n

DEALLOCATE @MyCursor;<\/p>\n

END;<\/p>\n

DROP TABLE #DatabaseStatements<\/p>\n

\/***********************<\/p>\n

End of Script<\/p>\n

************************\/<\/p>\n

\u00a0<\/p>\n\n


\n
\n<\/p>\n\n\n

Be sure to check out my full online class on the topic. A hands on walk through of a Modern Data Architecture using Microsoft Azure. For beginners and experienced business intelligence experts alike, learn the basic of navigating the Azure Portal to building an end to end solution of a modern data warehouse using popular technologies such as SQL Database, Data Lake, Data Factory, Data Bricks, Azure Synapse Data Warehouse and Power BI. Link to the class can be found here<\/a> or directly here<\/a>. <\/p>\n\n\n

<\/p>\n\n\n

Part 1 – Navigating the Azure Portal<\/a><\/p>\n\n\n\n

Part 2 – Resource Groups and Subscriptions<\/a><\/p>\n\n\n\n

Part 3 – Creating Data Lake Storage<\/a><\/p>\n\n\n\n

Part 4 – Setting up an Azure SQL Server<\/a><\/p>\n\n\n\n

Part 5 – Loading Data Lake with Azure Data Factory<\/a><\/p>\n\n\n\n

Part 6 – Configuring and Setting up Data Bricks<\/a><\/p>\n\n\n\n

Part 7 – Staging data into Data Lake<\/a><\/p>\n\n\n\n

Part 8 = Provisioning a Synapse SQL Data Warehouse<\/a><\/p>\n\n\n\n

Part 9 – Loading Data into Azure Data Synapse Data Warehouse<\/a><\/p>\n\n\n


\n
\n<\/p>","protected":false},"excerpt":{"rendered":"

Real Estate Data Warehouse \u2013 Accelerating the Staging Process The script below can be used to build a staging environment for any sort of industry and not just real estate related databases. The specifics of a RE Data warehouse will be covered in future blog post. It will allow you to\u00a0Accelerating the Staging Process for […]<\/p>\n","protected":false},"author":1,"featured_media":87,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,5,11,3],"tags":[16,15,12,13,14],"class_list":["post-85","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-analytics","category-consulting","category-data-warehouse","category-real-estate","tag-accelerate","tag-big-data","tag-data-warehouse","tag-etl","tag-staging"],"_links":{"self":[{"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/posts\/85","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/comments?post=85"}],"version-history":[{"count":8,"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/posts\/85\/revisions"}],"predecessor-version":[{"id":778,"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/posts\/85\/revisions\/778"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/media\/87"}],"wp:attachment":[{"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/media?parent=85"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/categories?post=85"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/tags?post=85"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}