A Business Intelligence project ALM's

-

Table of Contents

1   Introduction.
2   Automation process components
  2.1 The Build.
  2.2 The Deployment script.
  2.3 Automated deployment and execution.
3   Visual studio tips.
  3.1 Reducing unresolve reference errors.
  3.2 Composite Projects.
4   Resources.

1 Introduction

As I have mentioned in previous posts I have been working on a data warehouse project. One of my main roles in this project was to work on the build, deployment and development processes. Within this post, I would like to show how I, with help from my colleagues, was able to automate a build, deployment and execute the data warehouse code.

The reason for developing this automation was to try and find problems before we released to user acceptance testing (UAT). Once this automation started running it did capture errors. One error in particular, which would only appeared in release, was missing permissions. As the developers and the testing team all have different elevated rights, so that they can do their jobs, masked the problem ever existed.

2 Automation process components

A child with building blocks

2.1 The Build

To develop our data warehouse solution the project used the Microsoft development tools: Team Foundation Server (TFS) 2010 and Visual Studio (VS) 2010 / 2008. TFS 2010 was used as a source control repository and automated build platform (Team build). VS 2010 was used to develop our database code and VS 2008 - Business Intelligence Studio (BIDS) for creating the integration packages and analysis services cubes.

As we were using the above tools I was able to create, using my company's IP, a build that would drop deployable artefacts. This was then wrapped into a TFS build which would be triggered by a number of check-ins. This gave us our continuous build for the project which was helpful to find problems like: incompatible code between team members; incomplete check-ins missing files from the change set.

However, if a build was successful it didn't mean that the build was deployable. An example of this is an unresolved reference warning which shouldn’t have been a warning but an error. This happened to us, one of our builds which we tried, had several unresolved reference warnings, we tried to deploy but failed because a view referenced to a column within a table was invalid.

A parachutist

2.2 The Deployment script

We used a PowerShell script to manage the process of deploying, in the correct order, our databases, SSIS packages and analysis cubes. The script, which my colleagues had created, used a configuration file to list the artefacts for deploying with how and where. Also the script interaction used a text menu interface, which was created from the following location: http://mspowershell.blogspot.com/2009/02/cli-menu-in-powershell.html

This was helpful in reducing the work that we had to do for writing a release process. As we only had to write was: select menu option blah, select menu option blah etc. The script help reduce deployment errors from incorrect commands typed in or artefacts deployed in the wrong order.

A running nan

2.3 Automated deployment and execution

To have the automated build, deployment and execution process I reuse the build and deployment components. The build was refactored and reused within a scheduled trigger for out of office hours. The deployment script was extended to include support being called by automation tool. The automation tool was called TFSDeployer. TFSDeployer is a service which can be configured to listen for certain build events and then run a powershell script when captured. For more details about the application, please use the following link: http://tfsdeployer.codeplex.com/.

The script, which TFSDeployer would execute, when the out of office build completed, was configured to do the following steps:

  • Get the TFS build details.
  • Copy the contents of the build drop location to a deployment area.
  • Deploy the data warehouse solution using the deployment script
  • If the deployment failed then update the TFS build to failed and the status to broken
  • If the deployment was successful then execute a SQL Server job; which was configured to run the ETL.
  • If the SQL Server job reported failure then the TFS build was set as failed and the status to broken
  • If the SQL job reported success then the TFS build was set as successful and the status set for ready for testing

This then gave us the ability to test our builds and as mentioned before allowed us to find many problems before we went into UAT. This also gave us a platform to perform and automate regression testing.

3 Visual studio tips

3.1 Reducing unresolve reference warnings

Here are some tips on how we managed to reduce some of our unresolved reference warnings.

  • Converting some of derived tables to use common table expression with column definition syntax: WITH <CTEName>(Col1, Col2, Col3) AS
  • Adding a server and database variables for references which were going to go to be referred to via a link server. Within the code also wrapped the reference variables within square brackets.
  • Include objects which were created by code for example: select into tables and indexes. With indexes we also took another approach, where possible, which was to disable and enable them.

3.2 Composite Projects

During one of our early development cycles we came across a circular reference problem. This caused a problem at deployment time. I had to deploy the two databases about three times each to get a complete deployment.

The method I used to reduce a circular reference to happen was to use composite projects. The way I chose to split databases into composite projects was to put the storage objects like tables into one project. Then created another project for the code objects like views and referenced the storage project. The idea for this was based on some information I read from: http://vsdatabaseguide.codeplex.com/

4 Resources

Comments

Post a Comment

Popular posts from this blog

SSIS File handle leak in For Each Loop

-
I am currently working on a project which needs to load over a 1000 xml files. The files are stored across 10 subfolders. I am using a foreach loop with a file enumerator, which is configured at the top of the folder structure and traverses the subfolders. This loops through the files, load the data and then moves the file to another folder. The package executes fine for a few 100 files but then hangs; this happens after a different number of processed files each time the package is run. While trying to resolve the problem we ran performance counters and noticed that the number open handles increased significantly just about the time Dtexec looked like it had hanged and DTexec also then started taking a lot of the cpu processing time. Update: I put this on the Microsoft forums and got a intresting answer back See Here ; There seems to be a memory leak with foreach loop which should be fixed in sp1.
Read more

Azure SQL Server IaaS SSIS: A required privilege is not held by the client

-
Image
As mention in a previous post, we are using Azure SQL Server IaaS to help our transition to the cloud. The main reason for this is how some of our SSIS projects, which we inherited, were created. Some were using SSIS to manage the transactions, which uses MSDTC not supported in Azure. Use extra .Net libraries to read JSON and office files; external programs to do file operations like decompression. Migration timelines and priorities we didn't have time to refactor these projects. While ago we started to deploy our SSIS projects to our Azure SQL Server IaaS virtual machine, a Windows 2019 server with SQL Server 2019, to get the following error: SSIS Deployment Error 6522 "A required privilege is not held by the client". We able to resolve our problem by using the steps we found in the following blog posts: System.ComponentModel.Win32Exception: A required privilege is not held by the client while Deploying SSIS Project | Microsoft Docs . Fixing SQL 2012 SSIS Deployment Err
Read more

Challenges of move SQL Server data to Microsoft Azure SQL Server PaaS

-
This post covers one of the challenges we face when moving our infrastructure from on-prem to Microsoft Azure: Moving SQL Server data from on-premise to Microsoft SQL Server Paas. Moving our data was a challenge for the following reasons: Our company has tight controls and rules about what software/connectivity is allowed on our network The database that we need to migrate after compression was over 100GB in size. No information about how long other migration processes would take. As needed to ensure our migration was complete before the next Monday on the weekend of our migration. For most people migrating their data, the best option is to use Azure Database Migration Service , especially when transferring a database size that we were. We couldn't use the Azure Database Migration Service for the following reasons: Azure Database Migration Service requires software installed on a server within our network Connections to both databases
Read more