ODS – universal codebooks and hierarchies

Task: to show how to create universal codebooks and hierarchies for Operational Data Storage (3NF) in DW solutions

a) universal codebooks:

CREATE TABLE [dbo].[CodeBook](
 [AutoID] [int] IDENTITY(1,1) NOT NULL,
 [CodeBookTypeAutoID] [int] NULL,
 [CodeBookID] [nvarchar](50) NULL,
 [CodeBookText] [nvarchar](50) NULL,
 [CodeBookTextEnglish] [nvarchar](50) NULL,
 [SourceSystemAutoID] [int] NULL,
 [ExtractionDate] [int] NULL,
 CONSTRAINT [PK_CodeBook] PRIMARY KEY CLUSTERED 
( [AutoID] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[CodeBookType](
 [AutoID] [int] IDENTITY(1,1) NOT NULL,
 [CodeBookTypeID] [nvarchar](50) NULL,
 [CodeBookTypeText] [nvarchar](50) NULL,
 [CodeBookTypeTextEnglish] [nvarchar](50) NULL,
 [SourceSystemAutoID] [int] NULL,
 [ExtractionDate] [int] NULL,
 CONSTRAINT [PK_CodeBookType] PRIMARY KEY CLUSTERED 
( [AutoID] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[CodeBook2Contract](
 [CodeBookTypeAutoID] [int] NULL,
 [CodeBookAutoID] [int] NULL,
 [ContractAutoID] [int] NULL,
 [ValidFrom] [int] NULL,
 [ValidTo] [int] NULL,
 [SourceSystemAutoID] [int] NULL,
 [ExtractionDate] [int] NULL
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[Contract](
 [AutoID] [int] IDENTITY(1,1) NOT NULL,
 [ContractID] [nvarchar](50) NULL,
 [ValidFrom] [int] NULL,
 [ValidTo] [int] NULL,
 [SourceSystemAutoID] [int] NULL,
 [ExtractionDate] [int] NULL,
 CONSTRAINT [PK_Contract] PRIMARY KEY CLUSTERED 
( [AutoID] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


ALTER TABLE [dbo].[CodeBook2Contract] WITH NOCHECK ADD CONSTRAINT [FK_CodeBook2Contract_CodeBook] FOREIGN KEY([CodeBookAutoID])
REFERENCES [dbo].[CodeBook] ([AutoID])
GO
ALTER TABLE [dbo].[CodeBook2Contract] CHECK CONSTRAINT [FK_CodeBook2Contract_CodeBook]
GO

ALTER TABLE [dbo].[CodeBook2Contract] WITH NOCHECK ADD CONSTRAINT [FK_CodeBook2Contract_CodeBookType] FOREIGN KEY([CodeBookTypeAutoID])
REFERENCES [dbo].[CodeBookType] ([AutoID])
GO
ALTER TABLE [dbo].[CodeBook2Contract] CHECK CONSTRAINT [FK_CodeBook2Contract_CodeBookType]
GO

ALTER TABLE [dbo].[CodeBook2Contract] WITH NOCHECK ADD CONSTRAINT [FK_CodeBook2Contract_Contract] FOREIGN KEY([ContractAutoID])
REFERENCES [dbo].[Contract] ([AutoID])
GO
ALTER TABLE [dbo].[CodeBook2Contract] CHECK CONSTRAINT [FK_CodeBook2Contract_Contract]
GO


ALTER TABLE [dbo].[CodeBook] WITH NOCHECK ADD CONSTRAINT [FK_CodeBook_CodeBookType] FOREIGN KEY([CodeBookTypeAutoID])
REFERENCES [dbo].[CodeBookType] ([AutoID])
GO
ALTER TABLE [dbo].[CodeBook] CHECK CONSTRAINT [FK_CodeBook_CodeBookType]
GO


SET IDENTITY_INSERT [dbo].[CodeBookType] ON
GO
INSERT [dbo].[CodeBookType] ([AutoID], [CodeBookTypeID], [CodeBookTypeText], [CodeBookTypeTextEnglish], [SourceSystemAutoID], [ExtractionDate]) VALUES (1, N'CS', NULL, N'Contract status', NULL, NULL)
GO
SET IDENTITY_INSERT [dbo].[CodeBookType] OFF
GO


SET IDENTITY_INSERT [dbo].[CodeBook] ON
GO
INSERT [dbo].[CodeBook] ([AutoID], [CodeBookTypeAutoID], [CodeBookID], [CodeBookText], [CodeBookTextEnglish], [SourceSystemAutoID], [ExtractionDate]) VALUES (1, 1, N'CSO', NULL, N'Open contract', NULL, NULL)
GO
SET IDENTITY_INSERT [dbo].[CodeBook] OFF
GO


SET IDENTITY_INSERT [dbo].[Contract] ON
GO
INSERT [dbo].[Contract] ([AutoID], [ContractID], [ValidFrom], [ValidTo], [SourceSystemAutoID], [ExtractionDate]) VALUES (1, N'1', NULL, NULL, NULL, NULL)
GO
SET IDENTITY_INSERT [dbo].[Contract] OFF
GO


INSERT [dbo].[CodeBook2Contract] ([CodeBookTypeAutoID], [CodeBookAutoID], [ContractAutoID], [ValidFrom], [ValidTo], [SourceSystemAutoID], [ExtractionDate]) VALUES (1, 1, 1, NULL, NULL, NULL, NULL)
GO

 

b) universal hierarchies:

CREATE TABLE [dbo].[HierarchyType](
 [AutoID] [int] IDENTITY(1,1) NOT NULL,
 [HierarchyTypeID] [nvarchar](50) NULL,
 [HierarchyTypeText] [nvarchar](50) NULL,
 [HierarchyTypeTextEnglish] [nvarchar](50) NULL,
 [SourceSystemAutoID] [int] NULL,
 [ExtractionDate] [int] NULL,
 CONSTRAINT [PK_HierarchyType] PRIMARY KEY CLUSTERED 
( [AutoID] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[Hierarchy](
 [AutoID] [int] IDENTITY(1,1) NOT NULL,
 [HierarchyID] [nvarchar](50) NULL,
 [HierarchyTypeAutoID] [int] NULL,
 [HierarchyText] [nvarchar](50) NULL,
 [HierarchyTextEnglish] [nvarchar](50) NULL,
 [SourceSystemAutoID] [int] NULL,
 [ExtractionDate] [int] NULL,
 CONSTRAINT [PK_Hierarchy] PRIMARY KEY CLUSTERED 
( [AutoID] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[HierarchyLinkType](
 [AutoID] [int] IDENTITY(1,1) NOT NULL,
 [HierarchyLinkTypeID] [nvarchar](50) NULL,
 [HierarchyTypeSourceAutoID] [int] NULL,
 [HierarchyTypeDestinationAutoID] [int] NULL,
 [HierarchyLinkTypeText] [nvarchar](50) NULL,
 [HierarchyLinkTypeTextEnglish] [nvarchar](50) NULL,
 [ValidFrom] [int] NULL,
 [ValidTo] [int] NULL,
 [SourceSystemAutoID] [int] NULL,
 [ExtractionDate] [int] NULL,
 CONSTRAINT [PK_HierarchyLinkType] PRIMARY KEY CLUSTERED 
( [AutoID] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[HierarchyLink](
 [AutoID] [int] IDENTITY(1,1) NOT NULL,
 [HierarchyLinkID] [nvarchar](50) NULL,
 [HierarchyLinkTypeAutoID] [int] NULL,
 [HierarchySourceAutoID] [int] NULL,
 [HierarchyDestinationAutoID] [int] NULL,
 [ValidFrom] [int] NULL,
 [ValidTo] [int] NULL,
 [SourceSystemAutoID] [int] NULL,
 [ExtractionDate] [int] NULL,
 CONSTRAINT [PK_HierarchyLink] PRIMARY KEY CLUSTERED 
( [AutoID] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


ALTER TABLE [dbo].[Hierarchy] WITH NOCHECK ADD CONSTRAINT [FK_Hierarchy_HierarchyType] FOREIGN KEY([HierarchyTypeAutoID])
REFERENCES [dbo].[HierarchyType] ([AutoID])
GO
ALTER TABLE [dbo].[Hierarchy] CHECK CONSTRAINT [FK_Hierarchy_HierarchyType]
GO


ALTER TABLE [dbo].[HierarchyLinkType] WITH NOCHECK ADD CONSTRAINT [FK_HierarchyLinkType_HierarchyType] FOREIGN KEY([HierarchyTypeSourceAutoID])
REFERENCES [dbo].[HierarchyType] ([AutoID])
GO
ALTER TABLE [dbo].[HierarchyLinkType] CHECK CONSTRAINT [FK_HierarchyLinkType_HierarchyType]
GO


ALTER TABLE [dbo].[HierarchyLink] WITH NOCHECK ADD CONSTRAINT [FK_HierarchyLink_Hierarchy_S] FOREIGN KEY([HierarchySourceAutoID])
REFERENCES [dbo].[Hierarchy] ([AutoID])
GO
ALTER TABLE [dbo].[HierarchyLink] CHECK CONSTRAINT [FK_HierarchyLink_Hierarchy_S]
GO

ALTER TABLE [dbo].[HierarchyLink] WITH NOCHECK ADD CONSTRAINT [FK_HierarchyLink_Hierarchy_D] FOREIGN KEY([HierarchyDestinationAutoID])
REFERENCES [dbo].[Hierarchy] ([AutoID])
GO
ALTER TABLE [dbo].[HierarchyLink] CHECK CONSTRAINT [FK_HierarchyLink_Hierarchy_D]
GO

ALTER TABLE [dbo].[HierarchyLink] WITH NOCHECK ADD CONSTRAINT [FK_HierarchyLink_HierarchyLinkType] FOREIGN KEY([HierarchyLinkTypeAutoID])
REFERENCES [dbo].[HierarchyLinkType] ([AutoID])
GO
ALTER TABLE [dbo].[HierarchyLink] CHECK CONSTRAINT [FK_HierarchyLink_HierarchyLinkType]
GO


SET IDENTITY_INSERT [dbo].[HierarchyType] ON 
GO
INSERT [dbo].[HierarchyType] ([AutoID], [HierarchyTypeID], [HierarchyTypeText], [HierarchyTypeTextEnglish], [SourceSystemAutoID], [ExtractionDate]) VALUES (1, N'ORG_L1', NULL, N'Level1', NULL, NULL)
GO
INSERT [dbo].[HierarchyType] ([AutoID], [HierarchyTypeID], [HierarchyTypeText], [HierarchyTypeTextEnglish], [SourceSystemAutoID], [ExtractionDate]) VALUES (2, N'ORG_L2', NULL, N'Level2', NULL, NULL)
GO
SET IDENTITY_INSERT [dbo].[HierarchyType] OFF
GO


SET IDENTITY_INSERT [dbo].[Hierarchy] ON 
GO
INSERT [dbo].[Hierarchy] ([AutoID], [HierarchyID], [HierarchyTypeAutoID], [HierarchyText], [HierarchyTextEnglish], [SourceSystemAutoID], [ExtractionDate]) VALUES (1, N'ORG_L1_1', 1, NULL, N'ORG Level1 A', NULL, NULL)
GO
INSERT [dbo].[Hierarchy] ([AutoID], [HierarchyID], [HierarchyTypeAutoID], [HierarchyText], [HierarchyTextEnglish], [SourceSystemAutoID], [ExtractionDate]) VALUES (2, N'ORG_L2_1', 2, NULL, N'ORG Level2 A', NULL, NULL)
GO
SET IDENTITY_INSERT [dbo].[Hierarchy] OFF
GO


SET IDENTITY_INSERT [dbo].[HierarchyLinkType] ON 
GO
INSERT [dbo].[HierarchyLinkType] ([AutoID], [HierarchyLinkTypeID], [HierarchyTypeSourceAutoID], [HierarchyTypeDestinationAutoID], [HierarchyLinkTypeText], [HierarchyLinkTypeTextEnglish], [ValidFrom], [ValidTo], [SourceSystemAutoID], [ExtractionDate]) VALUES (1, N'ORG_L1_L2', 1, 2, NULL, N'ORG Level1 - Level2', NULL, NULL, NULL, NULL)
GO
SET IDENTITY_INSERT [dbo].[HierarchyLinkType] OFF
GO


SET IDENTITY_INSERT [dbo].[HierarchyLink] ON 
GO
INSERT [dbo].[HierarchyLink] ([AutoID], [HierarchyLinkID], [HierarchyLinkTypeAutoID], [HierarchySourceAutoID], [HierarchyDestinationAutoID], [ValidFrom], [ValidTo], [SourceSystemAutoID], [ExtractionDate]) VALUES (1, N'ORG_L1_1-ORG_L2_1', 1, 1, 2, NULL, NULL, NULL, NULL)
GO
SET IDENTITY_INSERT [dbo].[HierarchyLink] OFF
GO

 

Conclusion:

a) universal codebooks: one set of tables (3x codebook + 1 per entity) can contain more codebooks’ records (e.g. Contract status, Contract payment frequency, Client type etc.)

b) universal hierarchies: one set of tables (4 “common” tables) can contain more hierarchies’ records (e.g. Organisational structure, Business structure, Sales channels etc.) By using entities “HierarchyLinkType and HierarchyLink” is possible to create combinations between “source” and “destination” (bi-directional connections.)

Source code:

• ODS_UnivCodebooksAndHierarchies.zip

Additional references:

• Displaying Sorted Hierarchies (SQL Spackle) by Jeff Moden
• Hierarchical Data (SQL Server) by MSDN
• Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets by Jeff Moden
• Hierarchies on Steroids #2: A Replacement for Nested Sets Calculations by Jeff Moden

An architecture for automated team-based development and deployment of DWH/BI projects

Task: to design an architecture for automated team-based development and deployment of data warehouse and business intelligence projects by means of open source or free products

Figure 1: UML – Use Case (Enterprise Architect)

Abbreviations/Notes:

• SVN: Subversion (Apache, Open Source, Repository)
• UML: Enterprise Architect (EA, SparxSystems, Commercial Product, Script Generating)
UML alternatives:
• PowerDesigner (Sybase, Commercial Product)
• Visual Studio: Ultimate Edition (Architecture and Modeling) (Microsoft, Commercial Product)
• Office Visio (Microsoft, Commercial Product)
• wwwsqldesigner (Google, Open Source) or ArgoUML (Tigris, Open Source)
• CI: Continuous Integration (Jenkins, JAVA, Open Source)
• – – – optional

SVN+WinMerge+VS(Visual Studio: SSDT/BIDS – Free Product, Schema and Data Compare):

• server: VisualSVNServer (Standard Edition – Free Product)
• client for OS: TortoiseSVN (Open Source)
• plugin for VS: AnkhSVN (Open Source)
• WinMerge: diff tool (Open Source)
• SSDT: SQL Server Data Tools (Free Product)

SVN+VS:

Figure 2: SNV+VS (DB and BI Projects)

Jenkins:

Figure 3: Jenkins – Build Pileline (Source: Build Pipeline Plugin's Site)

Parameters:

• Environment (Choice): DEV, TEST, PROD
• INCLUDE_DB_STG (Boolean value): TRUE
• INCLUDE_DB_ODS (Boolean value): TRUE
• INCLUDE_DB_DWH (Boolean value): TRUE
• INCLUDE_DB_LOG (Boolean value): TRUE
• INCLUDE_SSIS_STG (Boolean value): TRUE
• INCLUDE_SSIS_ODS (Boolean value): TRUE
• INCLUDE_SSIS_DWH (Boolean value): TRUE
• INCLUDE_SSAS (Boolean value): TRUE
• INCLUDE_SSRS (Boolean value): TRUE

Source Code Management:

• Subversion: https://server-snv/dwh_bi/test

Build Triggers:

• Build periodically: H H(0-5) * * 1-5 (note: every working day during night hours)

Extra Plugins:

• Email-ext plugin
• Build Pipeline Plugin

Build:
1. Execute windows batch command – BUILD_DEVENV.bat

"%WORKSPACE%\CI_JENKINS\BUILD_DEVENV.bat" %Environment%

1.1 BUILD_DEVENV.bat

IF [%1]==[DEV] (
 GOTO DEV
) 
IF [%1]==[TEST] (
 GOTO TEST
)
IF [%1]==[PROD] (
 GOTO PROD
) ELSE (
 GOTO UNKNOWN
)

:DEV
 SET devenv="C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\devenv.exe"
 SET solution="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\TEST.sln"
 %devenv% %solution% /build Debug
GOTO END

:TEST
 SET devenv="C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\devenv.exe"
 SET solution="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\TEST.sln"
 %devenv% %solution% /build Release
GOTO END

:PROD
 SET devenv="C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\devenv.exe"
 SET solution="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\TEST.sln"
 %devenv% %solution% /build Release
GOTO END

:UNKNOWN
GOTO END

:END

2. Execute windows batch command – DB_RDB_BACKUP.bat

"%WORKSPACE%\CI_JENKINS\DB_RDB_BACKUP.bat" %Environment%

2.1 DB_RDB_BACKUP.bat

IF [%1]==[DEV] (
 GOTO DEV
) 
IF [%1]==[TEST] (
 GOTO TEST
)
IF [%1]==[PROD] (
 GOTO PROD
) ELSE (
 GOTO UNKNOWN
)

:DEV
 SET sqlcmd="sqlcmd.exe"
 SET backup="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\DB_RDB_BACKUP.sql"
 SET server="server\test"
 %sqlcmd% -S %server% -i %backup%
GOTO END

:TEST
GOTO END

:PROD
GOTO END

:UNKNOWN
GOTO END

:END

2.2 DB_RDB_BACKUP.sql

BACKUP DATABASE [DWH_TEST_DWH] TO  DISK = N'D:\MSSQL\MSSQL11.TEST\MSSQL11.TEST\MSSQL\Backup\DWH_TEST_DWH.bak' WITH NOFORMAT, NOINIT,  NAME = N'DWH_TEST_DWH-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

BACKUP DATABASE [DWH_TEST_LOG] TO  DISK = N'D:\MSSQL\MSSQL11.TEST\MSSQL11.TEST\MSSQL\Backup\DWH_TEST_LOG.bak' WITH NOFORMAT, NOINIT,  NAME = N'DWH_TEST_LOG-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

BACKUP DATABASE [DWH_TEST_ODS] TO  DISK = N'D:\MSSQL\MSSQL11.TEST\MSSQL11.TEST\MSSQL\Backup\DWH_TEST_ODS.bak' WITH NOFORMAT, NOINIT,  NAME = N'DWH_TEST_ODS-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

BACKUP DATABASE [DWH_TEST_STG] TO  DISK = N'D:\MSSQL\MSSQL11.TEST\MSSQL11.TEST\MSSQL\Backup\DWH_TEST_STG.bak' WITH NOFORMAT, NOINIT,  NAME = N'DWH_TEST_STG-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

2.3 DB_RDB_RESTORE.bat (optional)

IF [%1]==[DEV] (
 GOTO DEV
) 
IF [%1]==[TEST] (
 GOTO TEST
)
IF [%1]==[PROD] (
 GOTO PROD
) ELSE (
 GOTO UNKNOWN
)

:DEV
 SET sqlcmd="sqlcmd.exe"
 SET backup="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\DB_RDB_RESTORE.sql"
 SET server="server\test"
 %sqlcmd% -S %server% -i %backup%
GOTO END

:TEST
GOTO END

:PROD
GOTO END

:UNKNOWN
GOTO END

:END

2.4 DB_RDB_RESTORE.sql (optional)

USE [master]
ALTER DATABASE [DWH_TEST_DWH] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [DWH_TEST_DWH] FROM  DISK = N'D:\MSSQL\MSSQL11.TEST\MSSQL11.TEST\MSSQL\Backup\DWH_TEST_DWH.bak' WITH  FILE = 4,  MOVE N'DWH_TEST_DWH' TO N'D:\MSSQL\MSSQL11.TEST\MSSQL11.TEST\MSSQL\DATA\DWH_TEST_DWH.mdf',  MOVE N'DWH_TEST_DWH_log' TO N'D:\MSSQL\MSSQL11.TEST\MSSQL11.TEST\MSSQL\DATA\DWH_TEST_DWH.ldf',  NOUNLOAD,  REPLACE,  STATS = 5
ALTER DATABASE [DWH_TEST_DWH] SET MULTI_USER
GO

USE [master]
ALTER DATABASE [DWH_TEST_LOG] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [DWH_TEST_LOG] FROM  DISK = N'D:\MSSQL\MSSQL11.TEST\MSSQL11.TEST\MSSQL\Backup\DWH_TEST_LOG.bak' WITH  FILE = 4,  MOVE N'DWH_TEST_LOG' TO N'D:\MSSQL\MSSQL11.TEST\MSSQL11.TEST\MSSQL\DATA\DWH_TEST_LOG.mdf',  MOVE N'DWH_TEST_LOG_log' TO N'D:\MSSQL\MSSQL11.TEST\MSSQL11.TEST\MSSQL\DATA\DWH_TEST_LOG.ldf',  NOUNLOAD,  REPLACE,  STATS = 5
ALTER DATABASE [DWH_TEST_LOG] SET MULTI_USER
GO

USE [master]
ALTER DATABASE [DWH_TEST_ODS] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [DWH_TEST_ODS] FROM  DISK = N'D:\MSSQL\MSSQL11.TEST\MSSQL11.TEST\MSSQL\Backup\DWH_TEST_ODS.bak' WITH  FILE = 4,  MOVE N'DWH_TEST_ODS' TO N'D:\MSSQL\MSSQL11.TEST\MSSQL11.TEST\MSSQL\DATA\DWH_TEST_ODS.mdf',  MOVE N'DWH_TEST_ODS_log' TO N'D:\MSSQL\MSSQL11.TEST\MSSQL11.TEST\MSSQL\DATA\DWH_TEST_ODS.ldf',  NOUNLOAD,  REPLACE,  STATS = 5
ALTER DATABASE [DWH_TEST_ODS] SET MULTI_USER
GO

USE [master]
ALTER DATABASE [DWH_TEST_STG] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [DWH_TEST_STG] FROM  DISK = N'D:\MSSQL\MSSQL11.TEST\MSSQL11.TEST\MSSQL\Backup\DWH_TEST_STG.bak' WITH  FILE = 4,  MOVE N'DWH_TEST_STG' TO N'D:\MSSQL\MSSQL11.TEST\MSSQL11.TEST\MSSQL\DATA\DWH_TEST_STG.mdf',  MOVE N'DWH_TEST_STG_log' TO N'D:\MSSQL\MSSQL11.TEST\MSSQL11.TEST\MSSQL\DATA\DWH_TEST_STG.ldf',  NOUNLOAD,  REPLACE,  STATS = 5
ALTER DATABASE [DWH_TEST_STG] SET MULTI_USER
GO

3. Execute windows batch command – DB_SSIS_BACKUP.bat

"%WORKSPACE%\CI_JENKINS\DB_SSIS_BACKUP.bat" %Environment%

3.1 DB_SSIS_BACKUP.bat

IF [%1]==[DEV] (
 GOTO DEV
) 
IF [%1]==[TEST] (
 GOTO TEST
)
IF [%1]==[PROD] (
 GOTO PROD
) ELSE (
 GOTO UNKNOWN
)

:DEV
 SET sqlcmd="sqlcmd.exe"
 SET backup="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\DB_SSIS_BACKUP.sql"
 SET server="server"
 %sqlcmd% -S %server% -i %backup%
GOTO END

:TEST
GOTO END

:PROD
GOTO END

:UNKNOWN
GOTO END

:END

3.2 DB_SSIS_BACKUP.sql

BACKUP DATABASE [SSISDB] TO  DISK = N'D:\MSSQL\Backup\SSISDB.bak' WITH NOFORMAT, NOINIT,  NAME = N'SSISDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

3.3 DB_SSIS_RESTORE.bat (optional)

IF [%1]==[DEV] (
 GOTO DEV
) 
IF [%1]==[TEST] (
 GOTO TEST
)
IF [%1]==[PROD] (
 GOTO PROD
) ELSE (
 GOTO UNKNOWN
)

:DEV
 SET sqlcmd="sqlcmd.exe"
 SET backup="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\DB_SSIS_RESTORE.sql"
 SET server="server"
 %sqlcmd% -S %server% -i %backup%
GOTO END

:TEST
GOTO END

:PROD
GOTO END

:UNKNOWN
GOTO END

:END

3.4 DB_SSIS_RESTORE.sql (optional)

USE [master]
ALTER DATABASE [SSISDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [SSISDB] FROM  DISK = N'D:\MSSQL\Backup\SSISDB.bak' WITH  FILE = 3,  MOVE N'data' TO N'D:\MSSQL\Data\SSISDB.mdf',  MOVE N'log' TO N'D:\MSSQL\Data\SSISDB.ldf',  NOUNLOAD,  REPLACE,  STATS = 5
ALTER DATABASE [SSISDB] SET MULTI_USER
GO

4. Execute windows batch command – DB_SSRS_BACKUP.bat

"%WORKSPACE%\CI_JENKINS\DB_SSRS_BACKUP.bat" %Environment%

4.1 DB_SSRS_BACKUP.bat

IF [%1]==[DEV] (
 GOTO DEV
) 
IF [%1]==[TEST] (
 GOTO TEST
)
IF [%1]==[PROD] (
 GOTO PROD
) ELSE (
 GOTO UNKNOWN
)

:DEV
 SET sqlcmd="sqlcmd.exe"
 SET backup="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\DB_SSRS_BACKUP.sql"
 SET server="server\test"
 %sqlcmd% -S %server% -i %backup%
GOTO END

:TEST
GOTO END

:PROD
GOTO END

:UNKNOWN
GOTO END

:END

4.2 DB_SSRS_BACKUP.sql

BACKUP DATABASE [ReportServer$TEST] TO  DISK = N'D:\MSSQL\MSSQL11.TEST\MSSQL\Backup\ReportServer$TEST.bak' WITH NOFORMAT, NOINIT,  NAME = N'ReportServer$TEST-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

BACKUP DATABASE [ReportServer$TESTTempDB] TO  DISK = N'D:\MSSQL\MSSQL11.TEST\MSSQL\Backup\ReportServer$TESTTempDB.bak' WITH NOFORMAT, NOINIT,  NAME = N'ReportServer$TESTTempDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

4.3 DB_SSRS_RESTORE.bat (optional)

IF [%1]==[DEV] (
 GOTO DEV
) 
IF [%1]==[TEST] (
 GOTO TEST
)
IF [%1]==[PROD] (
 GOTO PROD
) ELSE (
 GOTO UNKNOWN
)

:DEV
 SET sqlcmd="sqlcmd.exe"
 SET backup="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\DB_SSRS_RESTORE.sql"
 SET server="server\test"
 %sqlcmd% -S %server% -i %backup%
GOTO END

:TEST
GOTO END

:PROD
GOTO END

:UNKNOWN
GOTO END

:END

4.4 DB_SSRS_RESTORE.sql (optional)

USE [master]
ALTER DATABASE [ReportServer$TEST] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
BACKUP LOG [ReportServer$TEST] TO  DISK = N'D:\MSSQL\MSSQL11.TEST\MSSQL\Backup\ReportServer$TEST_LogBackup_2013-05-25_11-54-42.bak' WITH NOFORMAT, NOINIT,  NAME = N'ReportServer$TEST_LogBackup_2013-05-25_11-54-42', NOSKIP, NOREWIND, NOUNLOAD,  NORECOVERY ,  STATS = 5
RESTORE DATABASE [ReportServer$TEST] FROM  DISK = N'D:\MSSQL\MSSQL11.TEST\MSSQL\Backup\ReportServer$TEST.bak' WITH  FILE = 2,  NOUNLOAD,  REPLACE,  STATS = 5
ALTER DATABASE [ReportServer$TEST] SET MULTI_USER
GO

USE [master]
ALTER DATABASE [ReportServer$TESTTempDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [ReportServer$TESTTempDB] FROM  DISK = N'D:\MSSQL\MSSQL11.TEST\MSSQL\Backup\ReportServer$TESTTempDB.bak' WITH  FILE = 2,  NOUNLOAD,  REPLACE,  STATS = 5
ALTER DATABASE [ReportServer$TESTTempDB] SET MULTI_USER
GO

4.5 SSRS_BACKUP.bat (optional)

IF [%1]==[DEV] (
 GOTO DEV
) 
IF [%1]==[TEST] (
 GOTO TEST
)
IF [%1]==[PROD] (
 GOTO PROD
) ELSE (
 GOTO UNKNOWN
)

:DEV
 SET ItemPath="/SSRS_TEST"
 SET ReportServerURL=http://server/ReportServer_TEST
 SET BackupFolder="C:\temp\CI_JENKINS\Backups\SSRS"
 SET ScriptFolder="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\SSRS_BACKUP.rss"
 RS -i %ScriptFolder% -s %ReportServerURL% -v ItemPath="%ItemPath%" -v BackupFolder="%BackupFolder%"
GOTO END

:TEST
GOTO END

:PROD
GOTO END

:UNKNOWN
GOTO END

:END

4.6 SSRS_BACKUP.rss (optional)

Public Sub Main()
 
Try
 
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
 
Dim Items as CatalogItem()
 
Dim Item as CatalogItem
 
Dim ReportName As String
 
Items = rs.ListChildren(ItemPath, false)
 
Console.Writeline()
 
Console.Writeline("Reports Back Up Started.")
 
For Each Item in Items
 
ReportName = ItemPath + "/" + Item.Name
 
Dim reportDefinition As Byte() = Nothing
 
Dim rdlReport As New System.Xml.XmlDocument
reportDefinition = rs.GetReportDefinition(ReportName)
Dim Stream As New MemoryStream(reportDefinition)
 
Dim curDate as Date = Date.Now()
Dim strDate as String = curDate.ToString("dd-MM-yyyy")
 
Dim BackupFolderNew as String = BackupFolder+"\"+strDate+"\"+ItemPath
 
If(Not System.IO.Directory.Exists(BackupFolderNew )) Then
    System.IO.Directory.CreateDirectory(BackupFolderNew)
End If
 
rdlReport.Load(Stream)
rdlReport.Save(BackupFolderNew + "\" + Item.Name +".rdl")
 
Console.Writeline("Report " + Item.Name +".rdl Backed up Successfully")
 
Next
 
Console.Writeline("Reports Back Up Completed.")
 
Console.Writeline()
 
catch e As Exception
 
Console.Writeline(e.Message)
 
End Try
 
End Sub

5. Execute windows batch command – SSAS_BACKUP.bat

"%WORKSPACE%\CI_JENKINS\SSAS_BACKUP.bat" %Environment%

5.1 SSAS_BACKUP.bat

IF [%1]==[DEV] (
 GOTO DEV
) 
IF [%1]==[TEST] (
 GOTO TEST
)
IF [%1]==[PROD] (
 GOTO PROD
) ELSE (
 GOTO UNKNOWN
)

:DEV
 SET ASCMD="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\SSAS_ASCMD.exe"
 SET XMLA="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\SSAS_BACKUP.xmla"
 SET SERVER="server\test"
 %ASCMD% -S %SERVER% -i %XMLA%
GOTO END

:TEST
GOTO END

:PROD
GOTO END

:UNKNOWN
GOTO END

:END

5.2 SSAS_BACKUP.xmla

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Object>
    <DatabaseID>SSAS</DatabaseID>
  </Object>
  <File>SSAS.abf</File>
  <AllowOverwrite>true</AllowOverwrite>
</Backup>

5.3 SSAS_RESTORE.xmla (optional)

<Restore xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <File>SSAS.abf</File>
  <DatabaseName>SSAS</DatabaseName>
  <AllowOverwrite>true</AllowOverwrite>
</Restore>

6. Execute windows batch command – INCLUDE_DB_STG.bat

"%WORKSPACE%\CI_JENKINS\INCLUDE_DB_STG.bat" %INCLUDE_DB_STG%, %Environment%

6.1 INCLUDE_DB_STG.bat

IF [%1]==[true] (
 GOTO INCLUDED
)
IF [%1]==[false] (
 GOTO END
)

:INCLUDED
 "C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\DB_STG_DEPLOY.bat" %2
GOTO END

:END

6.2 DB_STG_DEPLOY.bat

IF [%1]==[DEV] (
 GOTO DEV
) 
IF [%1]==[TEST] (
 GOTO TEST
)
IF [%1]==[PROD] (
 GOTO PROD
) ELSE (
 GOTO UNKNOWN
)

:DEV
 SET SqlPackage="C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe"
 SET publish="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\DB_STG\DB_STG_server.publish.xml"
 SET dacpac="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\DB_STG\bin\Debug\DB_STG.dacpac"
 %SqlPackage% /pr:%publish% /sf:%dacpac% /a:Publish
GOTO END

:TEST
GOTO END

:PROD
GOTO END

:UNKNOWN
GOTO END

:END

7. Execute windows batch command – INCLUDE_DB_ODS.bat

"%WORKSPACE%\CI_JENKINS\INCLUDE_DB_ODS.bat" %INCLUDE_DB_ODS%, %Environment%

7.1 INCLUDE_DB_ODS.bat

IF [%1]==[true] (
 GOTO INCLUDED
)
IF [%1]==[false] (
 GOTO END
)

:INCLUDED
 "C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\DB_ODS_DEPLOY.bat" %2
GOTO END

:END

7.2 DB_ODS_DEPLOY.bat

IF [%1]==[DEV] (
 GOTO DEV
) 
IF [%1]==[TEST] (
 GOTO TEST
)
IF [%1]==[PROD] (
 GOTO PROD
) ELSE (
 GOTO UNKNOWN
)

:DEV
 SET SqlPackage="C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe"
 SET publish="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\DB_ODS\DB_ODS_server.publish.xml"
 SET dacpac="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\DB_ODS\bin\Debug\DB_ODS.dacpac"
 %SqlPackage% /pr:%publish% /sf:%dacpac% /a:Publish
GOTO END

:TEST
GOTO END

:PROD
GOTO END

:UNKNOWN
GOTO END

:END

8. Execute windows batch command – INCLUDE_DB_DWH.bat

"%WORKSPACE%\CI_JENKINS\INCLUDE_DB_DWH.bat" %INCLUDE_DB_DWH%, %Environment%

8.1 INCLUDE_DB_DWH.bat

IF [%1]==[true] (
 GOTO INCLUDED
)
IF [%1]==[false] (
 GOTO END
)

:INCLUDED
 "C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\DB_DWH_DEPLOY.bat" %2
GOTO END

:END

8.2 DB_DWH_DEPLOY.bat

IF [%1]==[DEV] (
 GOTO DEV
) 
IF [%1]==[TEST] (
 GOTO TEST
)
IF [%1]==[PROD] (
 GOTO PROD
) ELSE (
 GOTO UNKNOWN
)

:DEV
 SET SqlPackage="C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe"
 SET publish="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\DB_DWH\DB_DWH_server.publish.xml"
 SET dacpac="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\DB_DWH\bin\Debug\DB_DWH.dacpac"
 %SqlPackage% /pr:%publish% /sf:%dacpac% /a:Publish
GOTO END

:TEST
GOTO END

:PROD
GOTO END

:UNKNOWN
GOTO END

:END

9. Execute windows batch command – INCLUDE_DB_LOG.bat

"%WORKSPACE%\CI_JENKINS\INCLUDE_DB_LOG.bat" %INCLUDE_DB_LOG%, %Environment%

9.1 INCLUDE_DB_LOG.bat

IF [%1]==[true] (
 GOTO INCLUDED
)
IF [%1]==[false] (
 GOTO END
)

:INCLUDED
 "C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\DB_LOG_DEPLOY.bat" %2
GOTO END

:END

9.2 DB_LOG_DEPLOY.bat

IF [%1]==[DEV] (
 GOTO DEV
) 
IF [%1]==[TEST] (
 GOTO TEST
)
IF [%1]==[PROD] (
 GOTO PROD
) ELSE (
 GOTO UNKNOWN
)

:DEV
 SET SqlPackage="C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe"
 SET publish="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\DB_LOG\DB_LOG_server.publish.xml"
 SET dacpac="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\DB_LOG\bin\Debug\DB_LOG.dacpac"
 %SqlPackage% /pr:%publish% /sf:%dacpac% /a:Publish
GOTO END

:TEST
GOTO END

:PROD
GOTO END

:UNKNOWN
GOTO END

:END

10 Execute windows batch command – INCLUDE_SSIS_STG.bat

"%WORKSPACE%\CI_JENKINS\INCLUDE_SSIS_STG.bat" %INCLUDE_SSIS_STG%, %Environment%

10.1 INCLUDE_SSIS_STG.bat

IF [%1]==[true] (
 GOTO INCLUDED
)
IF [%1]==[false] (
 GOTO END
)

:INCLUDED
 "C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\SSIS_STG_DEPLOY.bat" %2
GOTO END

:END

10.2 SSIS_STG_DEPLOY.bat

IF [%1]==[DEV] (
 GOTO DEV
) 
IF [%1]==[TEST] (
 GOTO TEST
)
IF [%1]==[PROD] (
 GOTO PROD
) ELSE (
 GOTO UNKNOWN
)

:DEV
 SET ISDeploymentWizard="ISDeploymentWizard.exe"
 SET ispac="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\SSIS_STG\bin\Development\SSIS_STG.ispac"
 SET server="server"
 SET SSIS="/SSISDB/TEST/SSIS_STG"
 %ISDeploymentWizard% /S /SP:%ispac% /DS:%server% /DP:%SSIS%
GOTO END

:TEST
GOTO END

:PROD
GOTO END

:UNKNOWN
GOTO END

:END

11. Execute windows batch command – INCLUDE_SSIS_ODS.bat

"%WORKSPACE%\CI_JENKINS\INCLUDE_SSIS_ODS.bat" %INCLUDE_SSIS_ODS%, %Environment%

11.1 INCLUDE_SSIS_ODS.bat

IF [%1]==[true] (
 GOTO INCLUDED
)
IF [%1]==[false] (
 GOTO END
)

:INCLUDED
 "C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\SSIS_ODS_DEPLOY.bat" %2
GOTO END

:END

11.2 SSIS_ODS_DEPLOY.bat

IF [%1]==[DEV] (
 GOTO DEV
) 
IF [%1]==[TEST] (
 GOTO TEST
)
IF [%1]==[PROD] (
 GOTO PROD
) ELSE (
 GOTO UNKNOWN
)

:DEV
 SET ISDeploymentWizard="ISDeploymentWizard.exe"
 SET ispac="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\SSIS_ODS\bin\Development\SSIS_ODS.ispac"
 SET server="server"
 SET SSIS="/SSISDB/TEST/SSIS_ODS"
 %ISDeploymentWizard% /S /SP:%ispac% /DS:%server% /DP:%SSIS%
GOTO END

:TEST
GOTO END

:PROD
GOTO END

:UNKNOWN
GOTO END

:END

12. Execute windows batch command – INCLUDE_SSIS_DWH.bat

"%WORKSPACE%\CI_JENKINS\INCLUDE_SSIS_DWH.bat" %INCLUDE_SSIS_DWH%, %Environment%

12.1 INCLUDE_SSIS_DWH.bat

IF [%1]==[true] (
 GOTO INCLUDED
)
IF [%1]==[false] (
 GOTO END
)

:INCLUDED
 "C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\SSIS_DWH_DEPLOY.bat" %2
GOTO END

:END

12.2 SSIS_DWH_DEPLOY.bat

IF [%1]==[DEV] (
 GOTO DEV
) 
IF [%1]==[TEST] (
 GOTO TEST
)
IF [%1]==[PROD] (
 GOTO PROD
) ELSE (
 GOTO UNKNOWN
)

:DEV
 SET ISDeploymentWizard="ISDeploymentWizard.exe"
 SET ispac="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\SSIS_DWH\bin\Development\SSIS_DWH.ispac"
 SET server="server"
 SET SSIS="/SSISDB/TEST/SSIS_DWH"
 %ISDeploymentWizard% /S /SP:%ispac% /DS:%server% /DP:%SSIS%
GOTO END

:TEST
GOTO END

:PROD
GOTO END

:UNKNOWN
GOTO END

:END

13. Execute windows batch command – INCLUDE_SSAS.bat

"%WORKSPACE%\CI_JENKINS\INCLUDE_SSAS.bat" %INCLUDE_SSAS%, %Environment%

13.1 INCLUDE_SSAS.bat

IF [%1]==[true] (
 GOTO INCLUDED
)
IF [%1]==[false] (
 GOTO END
)

:INCLUDED
 "C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\SSAS_DEPLOY.bat" %2
GOTO END

:END

13.2 SSAS_DEPLOY.bat

IF [%1]==[DEV] (
 GOTO DEV
) 
IF [%1]==[TEST] (
 GOTO TEST
)
IF [%1]==[PROD] (
 GOTO PROD
) ELSE (
 GOTO UNKNOWN
)

:DEV
 SET Deployment="C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Microsoft.AnalysisServices.Deployment.exe"
 SET asdatabase="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\SSAS\bin\SSAS.asdatabase"
 %Deployment% %asdatabase% /s
GOTO END

:TEST
GOTO END

:PROD
GOTO END

:UNKNOWN
GOTO END

:END

14. Execute windows batch command – INCLUDE_SSRS.bat

"%WORKSPACE%\CI_JENKINS\INCLUDE_SSRS.bat" %INCLUDE_SSRS%, %Environment%

14.1 INCLUDE_SSRS.bat

IF [%1]==[true] (
 GOTO INCLUDED
)
IF [%1]==[false] (
 GOTO END
)

:INCLUDED
 "C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\SSRS_DEPLOY.bat" %2
GOTO END

:END

14.2 SSRS_DEPLOY.bat

@echo off
::Script Variables
SET LOGFILE="C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\SSRS_TEST.log"
SET SCRIPTLOCATION=C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS



REM SET REPORTSERVER=http://server/ReportServer_TEST

IF [%1]==[DEV] (
 GOTO DEV
) 
IF [%1]==[TEST] (
 GOTO TEST
)
IF [%1]==[PROD] (
 GOTO PROD
) ELSE (
 GOTO UNKNOWN
)

:DEV
 SET REPORTSERVER=http://server/ReportServer_TEST
GOTO END

:TEST
 SET REPORTSERVER=http://server/ReportServer_TEST
GOTO END

:PROD
 SET REPORTSERVER=http://server/ReportServer_TEST
GOTO END

:UNKNOWN
 SET REPORTSERVER=http://server/ReportServer_TEST
GOTO END

:END



SET RS=rs.exe
SET TIMEOUT=60

::Clear Log file
IF EXIST %logfile% DEL %logfile%

::Write Log Header
ECHO Reporting Services Scripter Load Log >>%LOGFILE%
ECHO. >>%LOGFILE%
ECHO Starting Load at %DATE% %TIME% >>%LOGFILE%
ECHO SCRIPTLOCATION = %SCRIPTLOCATION% >>%LOGFILE%
ECHO REPORTSERVER   = %REPORTSERVER% >>%LOGFILE%
ECHO BACKUPLOCATION = %BACKUPLOCATION% >>%LOGFILE%
ECHO SCRIPTLEVEL    = SQL2008 >>%LOGFILE%
ECHO TIMEOUT        = %TIMEOUT% >>%LOGFILE%
ECHO RS             = %rs% >>%LOGFILE%
ECHO. >>%LOGFILE%

::Run Scripts

%rs% -i "%SCRIPTLOCATION%\SSRS_DEPLOY.rss" -s %REPORTSERVER% -l %TIMEOUT% -v REGIONFOLDER="" -v PARENTFOLDER="SSRS_TEST" -v RDLNAME="Report_TEST" -v DATASOURCE="" >>%LOGFILE% 2>&1
ECHO. >>%LOGFILE%

@echo off %rs% -i "%SCRIPTLOCATION%\SSRS_DEPLOY.rss" -s %REPORTSERVER% -l %TIMEOUT% -v REGIONFOLDER="SSRS_TEST" -v PARENTFOLDER="SSRS_TEST" -v RDLNAME="Report_TEST_1" -v DATASOURCE="" >>%LOGFILE% 2>&1
@echo off ECHO. >>%LOGFILE%

@echo off %rs% -i "%SCRIPTLOCATION%\SSRS_DEPLOY.rss" -s %REPORTSERVER% -l %TIMEOUT% -v REGIONFOLDER="SSRS_TEST" -v PARENTFOLDER="SSRS_TEST" -v RDLNAME="Report_TEST_2" -v DATASOURCE="" >>%LOGFILE% 2>&1
@echo off ECHO. >>%LOGFILE%

@echo off %rs% -i "%SCRIPTLOCATION%\SSRS_DEPLOY.rss" -s %REPORTSERVER% -l %TIMEOUT% -v REGIONFOLDER="SSRS_TEST" -v PARENTFOLDER="SSRS_TEST" -v RDLNAME="Report_TEST_3" -v DATASOURCE="" >>%LOGFILE% 2>&1
@echo off ECHO. >>%LOGFILE%

@echo off %rs% -i "%SCRIPTLOCATION%\SSRS_DEPLOY.rss" -s %REPORTSERVER% -l %TIMEOUT% -v REGIONFOLDER="SSRS_TEST" -v PARENTFOLDER="SSRS_TEST" -v RDLNAME="Report_TEST_4" -v DATASOURCE="" >>%LOGFILE% 2>&1
@echo off ECHO. >>%LOGFILE%

ECHO. >>%LOGFILE%
ECHO Finished Load at %DATE% %TIME% >>%LOGFILE%
ECHO. >>%LOGFILE%

14.3 SSRS_DEPLOY.rss

Public Sub Main()
	Dim pathToReport As String = "../workspace/SSRS_TEST/"

	Dim region As String = REGIONFOLDER 
	Dim folder As String = PARENTFOLDER
	Dim name As String = RDLNAME
	Dim data As String = DATASOURCE

	Dim parent As String = region + "/" + folder
	Dim location As String = pathToReport + name + ".rdl"

	Dim overwrite As Boolean = True
	Dim reportContents As Byte() = Nothing
	Dim warnings As Warning() = Nothing

	Dim fullpath As String = parent + "/" + name

	'Common CatalogItem properties
	Dim descprop As New [Property]
	descprop.Name = "Description"
	descprop.Value = ""
	Dim hiddenprop As New [Property]
	hiddenprop.Name = "Hidden"
	hiddenprop.Value = "False"

	Dim props(1) As [Property]
	props(0) = descprop
	props(1) = hiddenprop

	'console.writeline("region:   {0}", region)
	'console.writeline("folder:   {0}", folder)
	'console.writeline("name:     {0}", name)
	'console.writeline("parent=region/folder: {0}", parent)
	'console.writeline("location=name.rdl: {0}", location)
	'console.writeline("fullpath=parent/folder/name: {0}", fullpath)

	'Read RDL definition from disk
	Try
		Dim stream As FileStream = File.OpenRead(location)
		reportContents = New [Byte](stream.Length-1) {}
		stream.Read(reportContents, 0, CInt(stream.Length))
		stream.Close()

		warnings = RS.CreateReport(name, parent, overwrite, reportContents, props)

		If Not (warnings Is Nothing) Then
			Dim warning As Warning
			For Each warning In warnings
				Console.WriteLine(Warning.Message)
			Next warning
		Else
			Console.WriteLine("Report: {0} published successfully with no warnings", name)
		End If

		'Set report DataSource references
		''Dim dataSources(0) As DataSource

		''Dim dsr0 As New DataSourceReference
		''dsr0.Reference = region + "/Data Sources/MyDataSource"
		''Dim ds0 As New DataSource
		''ds0.Item = CType(dsr0, DataSourceDefinitionOrReference)
		''ds0.Name = data 
		''dataSources(0) = ds0

		''RS.SetItemDataSources(fullpath, dataSources)

		''Console.Writeline("Report DataSources set successfully")
        ''Console.WriteLine("Report: {0} published successfully", name)

	Catch e As IOException
		Console.WriteLine(e.Message)
	Catch e As SoapException
		Console.WriteLine("Error : " + e.Detail.Item("ErrorCode").InnerText + " (" + e.Detail.Item("Message").InnerText + ")")
		Console.WriteLine("Report: {0} published with error", name)
	End Try
End Sub

14.4 SSRS_TEST.log (output log)

Reporting Services Scripter Load Log 
 
Starting Load at st 22. 05. 2013 10:40:36,81 
SCRIPTLOCATION = C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS 
REPORTSERVER   = http://server/ReportServer_TEST 
BACKUPLOCATION =  
SCRIPTLEVEL    = SQL2008 
TIMEOUT        = 60 
RS             = rs.exe 
 
Report: Report_TEST published successfully with no warnings
The command completed successfully
 
off rs.exe -i "C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\SSRS_DEPLOY.rss" -s http://server/ReportServer_TEST -l 60 -v REGIONFOLDER="SSRS_TEST" -v PARENTFOLDER="SSRS_TEST" -v RDLNAME="Report_TEST_1" -v DATASOURCE="" 
off ECHO. 
off rs.exe -i "C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\SSRS_DEPLOY.rss" -s http://server/ReportServer_TEST -l 60 -v REGIONFOLDER="SSRS_TEST" -v PARENTFOLDER="SSRS_TEST" -v RDLNAME="Report_TEST_2" -v DATASOURCE="" 
off ECHO. 
off rs.exe -i "C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\SSRS_DEPLOY.rss" -s http://server/ReportServer_TEST -l 60 -v REGIONFOLDER="SSRS_TEST" -v PARENTFOLDER="SSRS_TEST" -v RDLNAME="Report_TEST_3" -v DATASOURCE="" 
off ECHO. 
off rs.exe -i "C:\Program Files (x86)\Jenkins\jobs\DWH_BI\workspace\CI_JENKINS\SSRS_DEPLOY.rss" -s http://server/ReportServer_TEST -l 60 -v REGIONFOLDER="SSRS_TEST" -v PARENTFOLDER="SSRS_TEST" -v RDLNAME="Report_TEST_4" -v DATASOURCE="" 
off ECHO. 
 
Finished Load at st 22. 05. 2013 10:40:53,28 

Recommendations:

• Offline Development: it allows developers independent development, debugging own code and database structures and consequently commit already functional code to online database project repository
• Units (granularity – smaller units are better) for Development:
• DB (table, view, stored procedure, function etc.)
• SSIS (package etc.)
• SSAS (cube, dimension etc.)
• SSRS (data sets, report etc.)
• MSBuild.exe: only database projects
• devenv.exe: database (after installing SSDT) and business intelligence projects

Source code:

• DWH_BI__CI_JENKINS.zip
  by Marian Placko

Additional references:

CI (general):
• CI Feature Matrix
  by Jeffrey Fredrick
• SQL Server 2012 Command Line Utilities
  by Muthusamy Anantha Kumar
CI (Jenkins):
• Jenkin’s Plugins
  by Jenkins Group
• Convert VS 2010 Database Project to SSDT and Automate Publishing with Jenkins – Part 3/3
  by Gary A. Stafford
• Continuous Integration with Visual Studio 2010 Database Projects, Hudson & MSBuild (Part 3 of 3)
  by Vishal Gamji
CI (RedGate):
• SQL Source Control
  by RedGate
• Deployment Manager
  by RedGate
• SQL Development Tools for Business Intelligence
  by RedGate
• Database delivery
  by RedGate
• Continuous Integration for SQL Server using Red Gate Tools, Atlassian Bamboo and GitHub
  by Ben Rees
• Continuous Integration for SQL Server Part II – Integration Testing
  by Ben Rees
• Continuous Integration for SQL Server Part III – Deployment
  by Ben Rees
CI (JetBrains):
• TeamCity
  by JetBrains
• Using SQL Test Database Unit Testing with TeamCity Continuous Integration
  by Dave Green
SVN/Versioning:
• Agent SVN – SCC Subversion Plug-in
  by Jussi Jumppanen
• SQL Server Management Studio and TortoiseSVN
  by jrummell
• Database Deployment: The Bits – Versioning
  by Phil Factor
MSBuild/devenv:
• MSBuild Extension Pack
  hosted on CodePlex
• /Build (devenv.exe)
  by MSDN
• devenv-plugin
  by zonybob
DB (general):
• Use the sqlcmd Utility
  by MSDN
• SqlPackage.exe
  by MSDN
• Backup and Restore SQL Server with the SQL Server 2012 PowerShell cmdlets
  by Allen White
• Database Deployment Articles
  by Alexander Karmanov
• Policy Based Management in SQL Server 2008
  by Keshav Singh
• TOAD FOR SQL SERVER
  by Toad World
DB (modelling):
• List of Unified Modeling Language tools
  by Wikipedia
• Data Modelling Tools
  by Dan Horne
DB (documentation):
• DB>doc for Microsoft SQL Server
  hosted on CodePlex
• Create Documentation for SQL Server Tables
  by Alexandros Pappas
SSDT/BIDS:
• BIDS Helper
  hosted on CodePlex
Debugging:
• Configure the Transact-SQL Debugger
  by TechNet
• Transact-SQL Debugger for SQL Server 2008 – Part 1
  by Bill Ramos
• Stored procedure debugging in SSDT and LocalDB
  by Jamie Thomson
Unit Testing/Testing:
• Getting Started with SQL Server Database Unit Testing in SSDT
  by Bill Gibson
• tSQLt
  by Greg M Lucas
• ssisUnit
  hosted on CodePlex
• SQL Load Generator
  hosted on CodePlex
SSIS:
• dtutil Utility
  by MSDN
• dtexec Utility (SSIS Tool)
  by MSDN
• Apex SQL SSIS Compare 2008.03.0007
  by SOFTPEDIA
SSAS:
• Analysis Services Deployment Methods
  by MSDN
• Deploy Model Solutions with the Deployment Utility
  by MSDN
SSRS:
• rs Utility (rs.exe) (SSRS)
  by MSDN
• Backup and Restore Operations for Reporting Services
  by MSDN
• Reporting Services Migration Tool
  by MS Download Center
• A Versioning system for SSRS
  by David K Bennett

DWH basics – partitioning

Task: to show how to horizontal partition a database table and how to apply different data compression to this partitions

SQL Server supports only one type of horizontal partitioning, the range partitions. It is the partitioning strategy in which data is partitioned based on the range that the value of a particular field falls in.

Figure 1: UML – Use Case

The process of horizontal partitioning of a database table and data compression consists of the following steps:
1. Create a new test database with two different filegroups (NOTE: the path C:\Temp\SQL Server\Partitioning\Primary and Secondary must already exist!)

IF EXISTS(
SELECT name
FROM sys.databases
WHERE name = N'Test_Partitioning')
DROP DATABASE Test_Partitioning
GO
CREATE DATABASE Test_Partitioning
ON PRIMARY
(NAME = 'TestPartitioning_Part2010',
FILENAME =
'C:\Temp\SQL Server\Partitioning\Primary\TestPartitioning_Part2010.mdf',
SIZE = 5, -- ISSUE: The CREATE DATABASE statement failed. The primary file
-- must be at least 5 MB to accommodate a copy of the model database.
MAXSIZE = 100,
FILEGROWTH = 1),
FILEGROUP TestPartitioning_Part2011
(NAME = 'TestPartitioning_Part2011',
FILENAME =
'C:\Temp\SQL Server\Partitioning\Secondary\TestPartitioning_Part2011.ndf',
SIZE = 2,
MAXSIZE = 100,
FILEGROWTH = 1),
FILEGROUP TestPartitioning_Part2012
(NAME = 'TestPartitioning_Part2012',
FILENAME =
'C:\Temp\SQL Server\Partitioning\Secondary\TestPartitioning_Part2012.ndf',
SIZE = 2,
MAXSIZE = 100,
FILEGROWTH = 1)
GO

2. Create the partition range function

USE Test_Partitioning
GO
CREATE PARTITION FUNCTION TestPartitioning_PartitionFunction(DATE)
AS RANGE LEFT FOR
VALUES('2010-12-31', '2012-01-01')
-- NOTE: interval: (- infinity, 2010-12-31],
--                 [2011-01-01, 2011-12-31] and
--                 [2012-01-01, infinity +)
GO

3. Create the partition scheme and attach the partition scheme to filegroups

USE Test_Partitioning
GO
CREATE PARTITION SCHEME TestPartitioning_PartitionScheme
AS PARTITION TestPartitioning_PartitionFunction
TO([PRIMARY], TestPartitioning_Part2011, TestPartitioning_Part2012)
GO

4. Create a table with the partition key and the partition scheme

USE Test_Partitioning
GO
CREATE TABLE dbo.Test_PartitionedTable
(ID INT NOT NULL,
[Date] DATE)
ON TestPartitioning_PartitionScheme([Date])
GO

5. Create the index on the partitioned table (optional and recommended)

USE Test_Partitioning
GO
CREATE UNIQUE CLUSTERED INDEX IX_TestPartition_Table
ON dbo.Test_PartitionedTable([Date])
ON TestPartitioning_PartitionScheme([Date])
GO

6. Insert data into the partitioned table

USE Test_Partitioning
GO
INSERT INTO dbo.Test_PartitionedTable(ID, [Date])
VALUES
(1, DATEADD(YEAR, -3, GETDATE())), -- inserted in the partition 2009
(2, DATEADD(YEAR, -2, GETDATE())), -- inserted in the partition 2010
(3, DATEADD(YEAR, -1, GETDATE())), -- inserted in the partition 2011
(4,                   GETDATE())   -- inserted in the partition 2012
GO

7. Test data from the dbo.Test_PartitionedTable

USE Test_Partitioning
GO
SELECT *
FROM dbo.Test_PartitionedTable
/*
SSMS RESULTS:
ID  Date
1   2009-10-19
2   2010-10-19
3   2011-10-19
4   2012-10-19
*/
GO

8. Verify rows inserted in partitions

USE Test_Partitioning
GO
SELECT *
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID) = 'Test_PartitionedTable'
ORDER BY partition_number
/*
SSMS RESULTS:
partition_id        object_id   index_id   partition_number   hobt_id             rows   filestream_filegroup_id   data_compression   data_compression_desc 
72057594039238656   245575913	1	   1	              72057594039238656   2	 0	                   0	              NONE
72057594039304192   245575913	1	   2	              72057594039304192	  1	 0	                   0	              NONE
72057594039369728   245575913	1	   3	              72057594039369728	  1	 0	                   0	              NONE
*/
GO

9. SPLIT – add the new partition P4 for the next year 2013

USE Test_Partitioning
GO
ALTER DATABASE Test_Partitioning
ADD FILEGROUP TestPartitioning_Part2013
GO
ALTER DATABASE Test_Partitioning
ADD FILE
(NAME = 'TestPartitioning_Part2013',
FILENAME =
'C:\Temp\SQL Server\Partitioning\Secondary\TestPartitioning_Part2013.ndf',
SIZE = 2,
MAXSIZE = 100,
FILEGROWTH = 1)
TO FILEGROUP TestPartitioning_Part2013
GO
ALTER PARTITION SCHEME TestPartitioning_PartitionScheme
NEXT USED TestPartitioning_Part2013
GO
ALTER PARTITION FUNCTION TestPartitioning_PartitionFunction()
SPLIT RANGE('2013-01-01')
-- NOTE: interval: (- infinity, 2010-12-31],
--                 [2011-01-01, 2011-12-31],
--                 [2012-01-01, 2012-12-31] and
--                 [2013-01-01, infinity +)
GO

10. SWITCH IN – move data from the staging table to the empty newly added partition P4

USE Test_Partitioning
GO

--INSERT INTO dbo.Test_PartitionedTable(ID, [Date])
--VALUES
--(5, DATEADD(YEAR,  1, GETDATE())), -- inserted in the partition 2013
--(6, DATEADD(YEAR,  2, GETDATE()))  -- inserted in the partition 2014
--GO

IF EXISTS(
SELECT name
FROM sys.databases
WHERE name = N'Test_Partitioning_Staging')
DROP DATABASE Test_Partitioning_Staging
GO
CREATE DATABASE Test_Partitioning_Staging
ON PRIMARY
(NAME = 'Test_Partitioning_Staging',
FILENAME =
'C:\Temp\SQL Server\Partitioning\Primary\Test_Partitioning_Staging.mdf',
SIZE = 5, -- ISSUE: The CREATE DATABASE statement failed. The primary file
-- must be at least 5 MB to accommodate a copy of the model database.
MAXSIZE = 100,
FILEGROWTH = 1)
GO


USE Test_Partitioning_Staging
GO

CREATE TABLE dbo.Test_NonPartitionedStagingTable
(ID INT NOT NULL,
[Date] DATE)
GO
CREATE UNIQUE CLUSTERED INDEX IX_Test_NonPartitionedStagingTable
ON dbo.Test_NonPartitionedStagingTable([Date])
GO
INSERT INTO dbo.Test_NonPartitionedStagingTable(ID, [Date])
VALUES
(5, DATEADD(YEAR,  1, GETDATE())), -- inserted in the partition 2013
(6, DATEADD(YEAR,  2, GETDATE()))  -- inserted in the partition 2014
/*
SSMS RESULTS:
ID  Date
5   2013-10-31
6   2014-10-31
*/
GO


USE Test_Partitioning
GO

ALTER DATABASE Test_Partitioning
MODIFY FILEGROUP TestPartitioning_Part2012 DEFAULT
GO

CREATE TABLE dbo.Test_NonPartitionedStagingTemp
(ID INT NOT NULL,
[Date] DATE)
GO
CREATE UNIQUE CLUSTERED INDEX IX_Test_NonPartitionedStagingTemp
ON dbo.Test_NonPartitionedStagingTemp([Date])
GO
ALTER TABLE dbo.Test_NonPartitionedStagingTemp
ADD CONSTRAINT PartitionFunction CHECK ([Date] > '2013-01-01'
AND [Date] IS NOT NULL)
GO
INSERT INTO dbo.Test_NonPartitionedStagingTemp(ID, [Date])
SELECT ID, [Date]
FROM Test_Partitioning_Staging.dbo.Test_NonPartitionedStagingTable
GO
SELECT * FROM dbo.Test_NonPartitionedStagingTemp
GO
/*
SSMS RESULTS:
ID  Date
5   2013-10-31
6   2014-10-31
*/

ALTER TABLE dbo.Test_NonPartitionedStagingTemp
SWITCH TO dbo.Test_PartitionedTable PARTITION 4
GO
DROP TABLE dbo.Test_NonPartitionedStagingTemp
GO

ALTER DATABASE Test_Partitioning
MODIFY FILEGROUP [PRIMARY] DEFAULT
GO

SELECT *
FROM dbo.Test_PartitionedTable
/*
SSMS RESULTS:
ID  Date
1   2009-10-19
2   2010-10-19
3   2011-10-19
4   2012-10-19
5   2013-10-19
6   2014-10-19
*/
GO
SELECT *
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID) = 'Test_PartitionedTable'
ORDER BY partition_number
/*
SSMS RESULTS:
partition_id        object_id   index_id   partition_number   hobt_id             rows   filestream_filegroup_id   data_compression   data_compression_desc 
72057594039238656   245575913	1	   1	              72057594039238656   2	 0	                   0	              NONE
72057594039304192   245575913	1	   2	              72057594039304192	  1	 0	                   0	              NONE
72057594039435264   245575913	1	   3	              72057594039435264	  1	 0	                   0                  NONE
72057594039369728   245575913	1	   4	              72057594039369728	  2	 0	                   0	              NONE
*/
GO

11. SWITCH OUT – move data from the first partition P1 to the archive table dbo.Test_NonPartitionedArchiveTable

USE Test_Partitioning
GO

CREATE TABLE dbo.Test_NonPartitionedArchiveTable
(ID INT NOT NULL,
[Date] DATE)
ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX IX_NonPartitioned_Archive
ON dbo.Test_NonPartitionedArchiveTable([Date])
GO

ALTER TABLE dbo.Test_PartitionedTable SWITCH PARTITION 1
TO dbo.Test_NonPartitionedArchiveTable
GO

SELECT *
FROM dbo.Test_NonPartitionedArchiveTable
/*
SSMS RESULTS:
ID  Date
1   2009-10-19
2   2010-10-19
*/
GO
SELECT *
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID) = 'Test_PartitionedTable'
ORDER BY partition_number
/*
SSMS RESULTS:
partition_id        object_id   index_id   partition_number   hobt_id             rows   filestream_filegroup_id   data_compression   data_compression_desc 
72057594039238656   245575913	1	   1	              72057594039238656   0	 0	                   0	              NONE
72057594039304192   245575913	1	   2	              72057594039304192	  1	 0	                   0	              NONE
72057594039435264   245575913	1	   3	              72057594039435264	  1	 0	                   0                  NONE
72057594039369728   245575913	1	   4	              72057594039369728	  2	 0	                   0	              NONE
*/
GO

12. MERGE – based on ‘2012-01-01’, i.e. P1 = PRIMARY FG (2010) + SECONDARY FG (2011)

USE Test_Partitioning
GO

ALTER PARTITION FUNCTION TestPartitioning_PartitionFunction()
MERGE RANGE('2012-01-01')
-- NOTE: interval: (- infinity, 2010-12-31]
--                 [2011-01-01, 2011-12-31],
--                 [2012-01-01, 2012-12-31] and
--                 [2013-01-01, infinity +)
GO
SELECT *
FROM dbo.Test_PartitionedTable
/*
SSMS RESULTS:
ID  Date
3   2011-10-19
4   2012-10-19
5   2013-10-19
6   2014-10-19
*/
GO
SELECT *
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID) = 'Test_PartitionedTable'
ORDER BY partition_number
/*
SSMS RESULTS:
partition_id        object_id   index_id   partition_number   hobt_id             rows   filestream_filegroup_id   data_compression   data_compression_desc 
72057594039238656   245575913	1	   1	              72057594039238656   0	 0	                   0	              NONE
72057594039304192   245575913	1	   2	              72057594039304192	  2	 0	                   0	              NONE
72057594039369728   245575913	1	   3	              72057594039369728	  2	 0	                   0	              NONE
*/
GO

INSERT INTO dbo.Test_PartitionedTable(ID, [Date])
VALUES
(1, DATEADD(YEAR, -3, GETDATE())), -- inserted in the partition 2009
(2, DATEADD(YEAR, -2, GETDATE()))  -- inserted in the partition 2010
GO
SELECT *
FROM dbo.Test_PartitionedTable
/*
SSMS RESULTS:
ID  Date
1   2009-10-19
2   2010-10-19
3   2011-10-19
4   2012-10-19
5   2013-10-19
6   2014-10-19
*/
GO
SELECT *
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID) = 'Test_PartitionedTable'
ORDER BY partition_number
/*
SSMS RESULTS:
partition_id        object_id   index_id   partition_number   hobt_id             rows   filestream_filegroup_id   data_compression   data_compression_desc 
72057594039238656   245575913	1	   1	              72057594039238656   2	 0	                   0	              NONE
72057594039304192   245575913	1	   2	              72057594039304192	  2	 0	                   0	              NONE
72057594039369728   245575913	1	   3	              72057594039369728	  2	 0	                   0	              NONE
*/
GO

13. Apply compression to your partitioned table

USE Test_Partitioning
GO
ALTER TABLE Test_PartitionedTable
REBUILD PARTITION = All
WITH 
(
DATA_COMPRESSION = PAGE ON Partitions(1), -- NOTE: (1 TO 3)
DATA_COMPRESSION = ROW  ON Partitions(2) ,
DATA_COMPRESSION = NONE ON Partitions(3)
);
GO

14. Apply compression to your partitioned index

USE Test_Partitioning
GO
ALTER INDEX IX_TestPartition_Table
ON dbo.Test_PartitionedTable
REBUILD PARTITION  = All
WITH 
(
DATA_COMPRESSION = PAGE ON Partitions(1),
DATA_COMPRESSION = ROW  ON Partitions(2),
DATA_COMPRESSION = NONE ON Partitions(3)
);
GO

15. Apply compression to your unpartitioned index

--USE Test_Partitioning
--GO
--ALTER INDEX YourUnpartitionedIndex
--ON dbo.Test_PartitionedTable
--REBUILD WITH (DATA_COMPRESSION = ROW);
--GO

16. Testing of application three types of data compression

USE Test_Partitioning
GO
SELECT *
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID) = 'Test_PartitionedTable'
ORDER BY partition_number
/*
SSMS RESULTS:
partition_id        object_id   index_id   partition_number   hobt_id             rows   filestream_filegroup_id   data_compression   data_compression_desc 
72057594039238656   245575913	1	   1	              72057594039238656   2	 0	                   2	              PAGE
72057594039304192   245575913	1	   2	              72057594039304192	  2	 0	                   1	              ROW
72057594039369728   245575913	1	   3	              72057594039369728	  2	 0	                   0	              NONE
*/
GO

Recommendations:

• filegroups can be setup as read-only. It will increase the performance
• relational and cube partitions should be based on the same column
• SQL Server 7.0 introduced partitioning through partitioned views
• partitioning: when the number of rows is higher than 1M
• roughly 25GB per partition is well manageable (100GB would be poorly manageable)
• operations: SPLIT, MERGE and SWITCH are meta data operations only and do not involve any movement of data
• usage: to migrate older data from more expensive disk to less expensive disk

Quotes from references:

• “Though it is possible to create all partitions on PRIMARY but it would be best if these different partitions are stored in a separate file groups. This gives some performance improvement even in the case of single core computers. It would be best if these file groups are on different discs on a multi core processing machine.”
• “A partitioned table may have a partitioned index. Partition aligned index views may also be created for this table.
  There may be question in your mind if it is possible to partition your table using multiple columns. The answer may be YES or NO. Why? No, because there is no such direct support for this in SQL Server. Yes, because you can still do that by using persisted computed column based on any number of columns you want.”
• “For partitioning your existing table just drop the clustered index on your table and recreate it on the required partition scheme.”
• “Though SQL Server does not directly support List Partitioning, you can create list partitions by tricking the partition function to specify the values with the LEFT clause. After that, put a CHECK constraint on the table, so that no other values are allowed to be inserted in the table specifying Partition Key column any value other than the ‘list’ of values.”
• “It must be remembered that indexes can be partitioned using a different partition key than the table. The index can also have different numbers of partitions than the table. We cannot, however, partition the clustered index differently from the table. To partition an index, ON clause is used, specifying the partition scheme along with the column when creating the index:
  If your table and index use the same Partition function then they are called Aligned. If they go further and also use the same partition scheme as well, then they are called Storage Aligned (note this in the figure below). If you use the same partition function for partitioning index as used by the table, then generally performance is improved.”

Additional references:

• Archiving SQL Server data using partitioning
  by Ben Snaidero
• Partitioning & Archiving tables in SQL Server (Part 2: Split, Merge and Switch partitions)
  by Félix Martínez Retama
• Partitioning in the AdventureWorks2008R2 Sample Database
  by MSDN
• Partitioning in SQL Server 2008
  by Muhammad Shujaat Siddiqi
• Partitioned Tables and Indexes
  by MSDN
• Partitioned Tables and Indexes in SQL Server 2005
  by MSDN
• Project REAL: Data Lifecycle Partitioning
  by Microsoft Download Center
• SQL Server Database Partitioning Myths and Truths
  by Alan Cranfield
• SQL Server Partitioning without Enterprise Edition
  by Barry King
• Switching data in and out of a SQL Server 2005 data partition
  by Greg Robidoux
• We Loaded 1TB in 30 Minutes with SSIS, and So Can You
  by MSDN

DWH basics – columnstore index for fast DW

Task: to make performance tests to show a performance impact of the columnstore index (SQL Server 2012)

Recommendations:

• to use on very large tables only (77 628 600 rows in my example; fact tables are good candidates)
• when the columnstore index is created, the table becomes read-only (despite of this fact, update/insert/delete operations are possible, e.g. by means of disabling the columnstore index)

The process of testing a performance impact of the columnstore index consists of the following steps:
1. Create the testing table

USE [AdventureWorksDW2012]
GO

CREATE TABLE [dbo].[MyFactProductInventory](
  [ProductKey] [int] NOT NULL,
  [DateKey] [int] NOT NULL,
  [MovementDate] [date] NOT NULL,
  [UnitCost] [money] NOT NULL,
  [UnitsIn] [int] NOT NULL,
  [UnitsOut] [int] NOT NULL,
  [UnitsBalance] [int] NOT NULL,
) ON [PRIMARY]
GO

2. Create the clustered regular index

CREATE CLUSTERED INDEX [IX_MyFactProductInventory_Clustered]
ON [dbo].[MyFactProductInventory]([ProductKey])
GO

3. Create the sample data table (WARNING: this query may run up to 2-10 minutes based on your systems resources)

INSERT INTO [dbo].[MyFactProductInventory]
SELECT [Fact].* FROM [dbo].[FactProductInventory] AS [Fact]
GO 100

4. Performance tests (NOTE: comparing the regular (clustered / non-clustered) index with the columnstore index)

SET STATISTICS IO ON
GO
SET STATISTICS TIME ON
GO

5. Select the table with the clustered regular index

SELECT ProductKey, SUM(UnitCost) AS 'SumUnitCost',
  AVG(UnitsBalance) AS 'AvgUnitsBalance'
FROM [dbo].[MyFactProductInventory]
GROUP BY ProductKey
ORDER BY ProductKey
GO
/* First testing results: */
-- (606 row(s) affected)
-- Table 'MyFactProductInventory'. Scan count 5, logical reads 602219,
-- physical reads 81, read-ahead reads 379178, lob logical reads 0,
-- lob physical reads 0, lob read-ahead reads 0.
-- SQL Server Execution Times: CPU time = 23665 ms, elapsed time = 18398 ms.
/* Second testing results: */
-- (606 row(s) affected)
-- Table 'MyFactProductInventory'. Scan count 5, logical reads 601939,
-- physical reads 0, read-ahead reads 602119, lob logical reads 0,
-- lob physical reads 0, lob read-ahead reads 0.
-- SQL Server Execution Times: CPU time = 20670 ms, elapsed time = 22654 ms.
/* Third testing results: */
-- (606 row(s) affected)
-- Table 'MyFactProductInventory'. Scan count 5, logical reads 602244,
-- physical reads 0, read-ahead reads 598015, lob logical reads 0,
-- lob physical reads 0, lob read-ahead reads 0.
-- SQL Server Execution Times: CPU time = 21123 ms, elapsed time = 20832 ms.

6.1. Create the non-clustered regular index

CREATE NONCLUSTERED INDEX [IX_MyFactProductInventory_Nonclustered]
ON [MyFactProductInventory](UnitCost, UnitsBalance, ProductKey)
GO

7.1. Select the table with the non-clustered regular index

SELECT ProductKey, SUM(UnitCost) AS 'SumUnitCost',
  AVG(UnitsBalance) AS 'AvgUnitsBalance'
FROM [dbo].[MyFactProductInventory]
GROUP BY ProductKey
ORDER BY ProductKey
GO
/* First testing results: */
-- (606 row(s) affected)
-- Table 'MyFactProductInventory'. Scan count 5, logical reads 290552,
-- physical reads 0, read-ahead reads 32391, lob logical reads 0,
-- lob physical reads 0, lob read-ahead reads 0.
-- SQL Server Execution Times: CPU time = 31323 ms, elapsed time = 8665 ms.
/* Second testing results: */
-- (606 row(s) affected)
-- Table 'MyFactProductInventory'. Scan count 5, logical reads 290652,
-- physical reads 0, read-ahead reads 0, lob logical reads 0,
-- lob physical reads 0, lob read-ahead reads 0.
-- SQL Server Execution Times: CPU time = 32448 ms, elapsed time = 8428 ms.
/* Third testing results: */
-- (606 row(s) affected)
-- Table 'MyFactProductInventory'. Scan count 5, logical reads 290597,
-- physical reads 0, read-ahead reads 0, lob logical reads 0,
-- lob physical reads 0, lob read-ahead reads 0.
-- SQL Server Execution Times: CPU time = 31138 ms, elapsed time = 8351 ms.
ALTER INDEX [IX_MyFactProductInventory_Nonclustered]
ON [MyFactProductInventory] DISABLE
GO

6.2. Create the columnstore index

CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_MyFactProductInventory_ColumnStore]
ON [MyFactProductInventory](UnitCost, UnitsBalance, ProductKey)
GO

7.2 Select the table with the columnstore index

SELECT ProductKey, SUM(UnitCost) AS 'SumUnitCost',
  AVG(UnitsBalance) AS 'AvgUnitsBalance'
FROM [dbo].[MyFactProductInventory]
GROUP BY ProductKey
ORDER BY ProductKey
GO
/* First testing results: */
-- (606 row(s) affected)
-- Table 'MyFactProductInventory'. Scan count 4, logical reads 12497,
-- physical reads 4, read-ahead reads 19973, lob logical reads 0,
-- lob physical reads 0, lob read-ahead reads 0.
-- SQL Server Execution Times: CPU time = 874 ms, elapsed time = 559 ms.
/* Second testing results: */
-- (606 row(s) affected)
-- Table 'MyFactProductInventory'. Scan count 4, logical reads 12320,
-- physical reads 0, read-ahead reads 4853, lob logical reads 0,
-- lob physical reads 0, lob read-ahead reads 0.
-- SQL Server Execution Times: CPU time = 1043 ms, elapsed time = 363 ms.
/* Third testing results: */
-- (606 row(s) affected)
-- Table 'MyFactProductInventory'. Scan count 4, logical reads 12370,
-- physical reads 0, read-ahead reads 9278, lob logical reads 0,
-- lob physical reads 0, lob read-ahead reads 0.
-- SQL Server Execution Times: CPU time = 874 ms, elapsed time = 435 ms.

8. Cleanup

DROP INDEX [IX_MyFactProductInventory_ColumnStore]
ON [dbo].[MyFactProductInventory]
GO
TRUNCATE TABLE [dbo].[MyFactProductInventory]
GO
DROP TABLE [dbo].[MyFactProductInventory]
GO
SET STATISTICS TIME OFF
GO
SET STATISTICS IO OFF
GO

Conclusion: the above results clearly show that the performance of the query is extremely fast after creating the columnstore index

• for logical reads: approximately 48 times faster than the clustered and 23 times faster than the non-clustered index
• for the elapsed time: approximately 33 times faster than the clustered and 16 times faster than the non-clustered index

Additional references:

• Introducing xVelocity in-memory technologies in SQL Server 2012 for 10-100X performance
  by TechNet
• SQL SERVER – ColumnStore Index – Batch Mode vs Row Mode
  by Pinal Dave
• SQL SERVER – Fundamentals of Columnstore Index
  by Pinal Dave
• SQL SERVER – How to Ignore Columnstore Index Usage in Query
  by Pinal Dave
• SQL SERVER – Updating Data in A Columnstore Index
  by Pinal Dave
• SQL Server 2012: ColumnStore Characteristics
  by Vinod Kumar