Conversion SQL datatypes to Snowflake datatypes

Introduction

I'm currently involved in a migration project from Azure SQL Database to Snowflake and I would like to share something that I've learned regarding the conversion of datatypes from Azure SQL Database. For this blogpost I used the Migration guide. So you'll see most of the same conversions in this blogpost, but with some small changes. For instance, in the migration guide "Datetime" is the Snowflake alternative for the SQL Database "Datetime" and that is not true. It is Timestamp_NTZ. 

Yet another thing I've added is that, although Snowflake doesn't use the SQL Server datatype, it recognizes the SQL Datatype and turns it into a Snowflake datatype. For instance, Snowflake understands "Datetime" and it will automatically convert into Timestamp_NTZ datatype. So, I've added the supported column : "Is the SQL Server datatype supported by Snowflake or not?" For instance, "Bit" is not recognized by Snowflake. I don't know why, but most of the SQL Server datatypes are recognized and converted into Snowflake datatypes.

This is also applicable for SQL Server and Azure Synapse

Azure SQL Database and Snowflake Datatypes

Here is the list of the Azure SQL Database datatypes that are supported or not and the Snowflake equivalent.

SQL SERVER	Snowflake		Comments
​	Supported	Preferred​	​
BIGINT ​	Y	NUMBER ​	Precision and scale not to be specified when using Numeric.​
BINARY​	Y	BINARY​	Snowflake: maximum length is 8 MB.​
BIT ​	N	BOOLEAN ​	Recommended: Use NUMBER if migrating value-to-value to capture the actual BIT value. Use Boolean in Snowflake if the desired outcome is to restrict to Ternary Logic (three valued): TRUE,  FALSE  or NULL (UNKNOWN).​
CHAR​	Y	VARCHAR(1)​	Any set of strings that is shorter than the maximum length is not space-padded at the end.​
DATE ​	Y	DATE​	Default in SQL Server is YYYY-MM-DD.​
DATETIME​	Y	TIMESTAMP_NTZ ​	SQL Server datetime is not ANSI or ISO 8501 compliant. Storage size is 8 bytes. Accuracy is rounded to increments of .000  .003 or .007.​
DATETIME2 ​	N	TIMESTAMP_NTZ ​	Snowflake: TIMESTAMP with no time zone time zone is not stored. DATETIME2 has a default precision of up to 7 digits Snowflake has TIMESTAMP_NTZ with the precision of 9 digits.​
DATETIMEOFFSET​	N	TIMESTAMP_LTZ​	SMALLDATETIME is not ANSI or ISO 8601 compliant. It has a fixed 4 bytes storage space. TIMESTAMP_LTZ Up to 34 7 in precision scale.​
DECIMAL ​	Y	NUMBER​	Default precision and scale are (38,0).​
FLOAT​	Y	FLOAT​	Snowflake uses double-precision (64-bit) IEEE 754 floating point numbers.​
IMAGE​	N	N/A​	Use NVARCHAR,  VARCHAR, or VARBINARY instead.​
INT ​	Y	NUMBER​	Precision and scale not to be specified when using Numeric.​
MONEY ​	N	NUMBER​	Money has a range of 19 digits with a scale of 4 digits, so NUMBER(19,4) can be used.​
NCHAR​	Y	VARCHAR(1)​	CHAR is used on fixed-length-string data​
NTEXT​	N	VARCHAR​	This data type will be discontinued on SQL Server. Use NVARCHAR,  VARCHAR, or VARBINARY instead.​
NUMERIC ​	Y	NUMBER​	Default precision and scale are (38,0).​
NVARCHAR​	Y	VARCHAR​	NVARCHAR’s string length can range from 1–4000.​
REAL ​	Y	FLOAT​	The ISO synonym for REAL is FLOAT(24).​
SMALLDATETIME ​	N	TIMESTAMP_NTZ ​	SMALLDATETIME is not ANSI or ISO 8601 compliant. It has a fixed 4 bytes storage space.​
SMALLINT​	Y	NUMBER​	Default precision and scale are (38,0).​
SMALLMONEY ​	N	NUMBER​	NUMBER with precision of 10 digits, with a scale of 4, so NUMBER(10,4) can be used.​
TEXT​	Y	VARCHAR​	This data type will be discontinued on SQL Server. Use NVARCHAR,  VARCHAR, or VARBINARY instead.​
TIME​	Y	TIME ​	SQL Server has a precision of 7 nanoseconds. Snowflake has precision of 9 nanoseconds​
TIMESTAMP​​	Y	TIMESTAMP_NTZ​	Use DATETIME2 or CURRENT_TIMESTAMP function.​
TINYINT​	Y	NUMBER​	Default precision and scale are (38,0).​
UNIQUEIDENTIFIER​	N	STRING​	Not Supported.​
VARBINARY​	Y	BINARY​	Snowflake: maximum length is 8 MB.​
VARCHAR​	Y	VARCHAR​	Any set of strings that are shorter than the maximum length is not space-padded at the end.

Final thoughts

This blogpost is a list of Azure SQL Database to Snowflake datatypes conversions 

Hennie de Nooijer

DevOps series : Composite projects in Visual Studio SSDT

Introduction

In my current project, I'm working in a rather complicated environment with people working in teams in databases and these are also shared (used) among the teams. All kind of projects are deployed in a common database (in reality there a couple of more, but for the sake of simplicity for this blogpost, I'll assume there is one database. The main story is that objects (View, Stored procedures) of databases are shared between teams). This introduces an extra complication when using Visual Studio in combination with SSDT, because normally a project is a database (and a database is a project).

One of the challenges I see here is that if code is not present in a Visual Studio project it will try to delete the code in a database. Yet another challenge I'm investigating is version management through the different environments and what happens when a dacpac is pushed to a new step in the Software Development environment (DTAP).

Composite projects

For reasons described in the introduction, I was looking into composite projects. Bill Gibson mentioned something about working with different projects in a database (7 years ago! Time flies) in a blogpost. In my opinion, the following situations are possible in a database project (not SSDT project;-)) :

In this example there are 4 working projects (not Visual studio) on different systems and these are deployed into the same database. Working Project 1 uses code from Working Project 2 and Working Project 3. Working project 3 uses code from Working Project 2. Working project 4 uses code from Working project 2. Now, in this imaginary situation, I'm focusing on Working Project 1. As said before Working Project 1 uses code from Working Project 2 and 3. So the scope of Working project 1 is 2 and 3 (and off course itself). Working Project 4 is out of sight and not used by Working Project 1.

All the projects in a Visual Studio solution

There are a couple of options possible when designing your Visual Studio projects. First, You can include all of the code of projects in one Visual Studio solution. In my current situation I'm talking about 20 to 30 projects in the database and I don't need them (all of the time). The problem is that when one of the projects has some changes in the code, I've to update my local development database every time (even for projects that I'm not using for my current development). The advantage is that when I execute a Schema Compare between the database and the project it would show all of the changes easily.

Using DacPacs for decoupling

Yet another option is using dacpac's for decoupling the projects. So, not every change is reflected in your Visual Studio project. You set a database reference with the option "Same Database" and include the DacPac in your project and you're done. But, what about projects you don't even reference in your project? In the example is that Working Project 4.

In reality, It would look something like the situation below. We have THE project (eg. working Project 1), where we working on, we have the real referenced database project which code we are using in the THE project and we have projects that we are not using in the development of a database project at all.

To see what actually happens, I've created a mock up with two demo databases of Microsoft AdventureworksLT2017 and WideWorldImporters. I imported both databases in a Visual studio project, deployed them into one database and investigated what would happen if I execute a Schema Compare. If I don't add Database References to the projects, the Visual Studio WideWorldImporters wants to delete the code of AdventureWorksLT2017 and when I execute a Schema Compare in the AdventureWorksLT2017 it wants to delete the WideWorldImporters code in the database.

The next thing I tried was adding a Database reference, option Same Database and use the DacPac of the other project, I executed the Schema Compare again (don't forget to check "Include Composite Projects") and check the results again. The result was that the errors were gone. Even when the code is not used in a project, you can reference a dacpac for a "no error" comparison between a composite project and the database.

I can understand "following" the updates of the DacPacs from the referenced projects, but updating a local development database for every "not referenced" (other) projects can be time consuming. So, what will happen when you deploy your project to the Central Integration database and other steps in the DevOps pipeline.

Composite projects in DTAP

How will this work in the real world, where projects have different releases in different environments? Perhaps they need to execute a roll back of a release in an environment or are working in a different way? What if a project is provided with an old version of a dacpac and the responsible person of the project updated one of environments in the OTAP-line with a newer version and your project is planning an update to that environment?

I experimented with my AdventureWorksLT2017 and WideWorldImporters projects in one database and deployed these to a new database (as in a scenario like a new environment in the OTAP) and I added a new function in the WideWorldImporters project and deployed that to the new database. The result was that the AdventureWorksLT2017 project wants to delete the newly created function (because it is not in the dacpac of WideWorldImporters). So, I need to update the WideWorldImporters DacPac in the AdventureWorksLT2017 project.

Final Thoughts

This blogpost is an imagination of some experiments I ran with multiple database projects in one database. At the moment, I've not found a satisfying solution for my problem. The option "Same database" in the Database reference seems handy in a small environment but in a multi team project environment, it introduces all kind of version issues and a high probability of errors and mistakes. It requires a certain skill set. Deployment should be done with high caution.

Hennie

DevOps series : tSQLt, Visual Studio and Azure DevOps

Introduction

Currently using and investigating tSQLt in a data warehouse project in a complicated environment with multiple developers, datamodellers and testers. I decided to investigate on how using Visual Studio together with the tSQLt framework and using that in Azure DevOps. This blogpost is just one step in the process of researching tSQLt  together with Visual Studio and Azure DevOps. I'm not stating that this is the final solution on how to DevOpsing with tSQLt. Finetuning alert ahead ;-) I'll blog about that in the future. I am using AdventureWorksLT2017 as an example project for this experiment.

I'm not covering all of the details of tSQLt, Visual Studio and Azure DevOps, but I will show how to set up a project with AdventureWorksLT2017, tSQLt framework and together with some tests in Visual Studio end Azure DevOPs

Inspiration for this blog is from Nikolai Thomassen. I found more information from blogposts like these from Sandra Walters and Medium.com. Many thanks to these contributors.

The local environment

I've a VM with a development environment together with Visual Studio 2017 professional, SSDT installed, SSMS with Git support, connected with Azure DevOps. On this machine, I've created a set of Visual Studio projects and databases. I've depicted that in the following diagram.

So there are a couple of parts in this diagram :

• The visual studio project (green boxes).
• Database references (green lines).
• Deployment to the different database projects (blue lines and blue cylinders).

The Visual Studio project

I've created one solution with three projects:

• AdventureWorksLT2017. This is the development project where all of the database code is stored. It contains the table definitions, views, stored procedures and all other database objects. 
• AdventureWorksLT2017.framework.tsqlt. This is the project where the framework is installed. one of the reasons doing so, is that you can update the framework regardless of the tests and the database project. 
• AdventureWorksLT2017.tests. This is the project where all the test definitions aka the unittesting procedures are stored.

Now, this setup will make sure that the different parts of the project are deployed in their targeted environment. One of the issues I had, when I started is that the tSQLt was installed at my development database and with this setup. The development code (AdventureWorksLT2017) is now more separated from the tSQLt code and test procedures (AdventureWorksLT2017_CI).

Importing the TSQLt in your project can be a bit tricky but I assume you can do it!

Database references

Now, in order to make this work it is necessary to set up the projects with a so called composite project. Composite projects are projects that are part of a database. Normally a Database Reference (as it says) is a reference to a whole database : a project is a database. This can be very unhandy.

For composite projects it is necessary to set up a Database Reference with Database Location set up : "Same Database".

Deployment to databases

I've created some publish files at my projects for deploying the projects to the databases. I've created them for my local deployment but these can also be used in the Test release or other release environment.

The testproject

Now, the testproject is not much different than that of Nicolai. I've created a test procedure that will test whether a value of color is inserted in the column Color with the stored procedure InsertProduct.

CREATE PROCEDURE [tsqlttests].[test_insert_Product_check_if_color_is_inserted ]
AS 
BEGIN

--ASSEMBLE
DECLARE @Name   NVARCHAR = 'Car';
DECLARE @ProductNumber NVARCHAR = '12345';
DECLARE @StandardCost MONEY = 1 ;
DECLARE @ListPrice      MONEY = 2;
DECLARE @SellStartDate DATETIME = '2019-07-31';
DECLARE @Color   NVARCHAR (15)  = 'Yellow';
--ACT

EXEC SalesLT.InsertProduct 
  @Name    = @Name
    ,@ProductNumber  = @ProductNumber 
    ,@Color    = @Color
    ,@StandardCost  = @StandardCost
    ,@ListPrice   = @ListPrice
    ,@SellStartDate     = @SellStartDate

--ASSERT
DECLARE @Actual NVARCHAR = (SELECT TOP 1 Color FROM SalesLT.Product)


EXEC tSQLt.AssertEqualsString @Color, @Actual, 'Color name was not saved when @color was given'

END;

This stored procedure is shown below :

CREATE PROCEDURE SalesLT.InsertProduct
(
  @Name     [dbo].[Name]   
    ,@ProductNumber          NVARCHAR (25)    
    ,@Color                  NVARCHAR (15)
    ,@StandardCost           MONEY  
    ,@ListPrice              MONEY
    ,@Size                   NVARCHAR (5)
    ,@Weight                 DECIMAL (8, 2)
    ,@ProductCategoryID      INT
    ,@ProductModelID         INT  
    ,@SellStartDate          DATETIME 
    ,@SellEndDate            DATETIME  
    ,@DiscontinuedDate       DATETIME   
    ,@ThumbNailPhoto         VARBINARY (MAX)
    ,@ThumbnailPhotoFileName NVARCHAR (50) 
)
AS 
BEGIN
SET NOCOUNT ON;


SET NOCOUNT OFF;
END

The procedure will not insert a value and therefore the test will go wrong.

The DevOps Environment

The next step is setting up the Azure DevOps environment. I assume you have some basic knowledge of Azure DevOps. I'm not showing all of the basics here. In the following diagram, I'm showing a basic Build, Test and Release process that we are going to follow.

We have a local environment and we have a DevOps environment. The local environment is already described in the previous section. So what will happen when we are done, is that the code is committed to the Git database, where the build process is started to make sure that code is correct. Next step in the process is that the code is deployed to a CI environment. This will be executed with the dacpac from the test project. When the tests are done, it will deploy to TST environment for user testing.

The build process

The build process is executed in the Build part of Azure DevOps. Now, I'm not sure where Microsoft is heading with this, but it seems that YAML code writing will be the preferred way, in contrast with a half a year ago, when click and go was the way doing it. I've created the following YAML script:

pool:
  name: Hosted Windows 2019 with VS2019
  demands: msbuild

variables:
  BuildConfiguration: 'Debug'

steps:
- task: MSBuild@1
  inputs:
    solution: '**/*.sln'
    msbuildArguments: '/property:DSP="Microsoft.Data.Tools.Schema.Sql.SqlAzureV12DatabaseSchemaProvider"'

- task: CopyFiles@2
  displayName: 'Copy Files from Build to Artifact folder'
  inputs:
    SourceFolder: ''
    Contents: '**\bin\$(BuildConfiguration)\**.dacpac'
    flattenFolders: true 
    TargetFolder: '$(Build.ArtifactStagingDirectory)'

- task: PublishBuildArtifacts@1
  displayName: 'Publish Artifact: AdventureWorksLT2017'
  inputs:
    ArtifactName: 'AdventureWorksLT2017'

Notice the msbuild argument : /property:DSP="Microsoft.Data.Tools.Schema.Sql.SqlAzureV12DatabaseSchemaProvider". This is necessary to build the code for Azure databases. Locally I'm using SQL Server 2016  and I want to keep it that way, but when I deploy the code to Azure I must have to be Azure compatible. More information at medium.com.

The build process will deliver three (or four) dacpacs. These will be used for the release process.

The release process

I've setup the release process in two steps. One release and test on the CI database and one on the TST database. These database have different goals. The CI database uses the test dacpac and the TST database uses the AdventureWorksLT2017 dacpac. This makes sense because you don't want to deploy the test procedures to the TST database. So, the process is depicted below.

First step is to get the artefacts from the Artefacts folder and this is passed to the CI and TST release process.

The steps in the CI release process are presented below :

Most of the steps are quite straightforward. I borrowed the Powershell script from Nicolai and it worked like a charm.

$connectionString = "Server=tcp:devhennie.database.windows.net,1433;Initial Catalog=AdventureWorksLT2017_CI;Persist Security Info=False;User ID=xxxxxx;Password=xxxxxxxx;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"

$sqlCommand = 'BEGIN TRY EXEC tSQLt.RunAll END TRY BEGIN CATCH END CATCH; EXEC tSQLt.XmlResultFormatter'

$connection = new-object system.data.SqlClient.SQLConnection($connectionString)
$command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
$connection.Open()

$adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
$dataset = New-Object System.Data.DataSet
$adapter.Fill($dataSet) | Out-Null

$connection.Close()
$dataSet.Tables[0].Rows[0].ItemArray[0] | Out-File "$(System.DefaultWorkingDirectory)/_AdventureWorksLT2017/AdventureWorksLT2017/testresults.xml"

I've executed the following script, in order to retrieve the output.

BEGIN TRY EXEC tSQLt.RunAll END TRY BEGIN CATCH END CATCH; EXEC tSQLt.XmlResultFormatter

This is the output. This is readable by the test process in Azure DevOps.

<testsuites>
  <testsuite id="1" name="tsqlttests" tests="1" errors="1" failures="0" timestamp="2019-08-07T06:55:29" time="0.077" hostname="devhennie" package="tSQLt">
    <properties />
    <testcase classname="tsqlttests" name="test_insert_Product_check_if_color_is_inserted " time="0.077">
      <error message="Procedure or function 'InsertProduct' expects parameter '@Size', which was not supplied.[16,4]{SalesLT.InsertProduct,0}" type="SQL Error" />
    </testcase>
    <system-out />
    <system-err />
  </testsuite>
</testsuites>

When the testing is done, an overview is created in the Test tab in Azure DevOps. This is a nice integration with tSQLt and Azure DevOps.

Final thoughts

It was quite some work to figure it out but it was fruitful in the end. I had some great help with this blog from Nikolai Thomassen. He described some tweaks that I didn't know. I learned some interesting stuff again!

Hennie

Certifications and courses

Introduction

I have used some of my "in between job" time to study and learn more about the Microsoft stack and I participated in a course "Fact oriented Modelling" aka FOM aka FCO-IM and formerly known as NIAM. The certifications for Microsoft technology are more technical oriented and the other is more based on data- or information modelling about facts.

Microsoft certifications

Now, in the last month, I've studied for the following certifications:

• MCSA SQL Server 2016 Business intelligence development.
• MCSA SQL Server 2016 Database development.
• MCSA BI reporting.
• MCSE Data management and Analytics.
• Microsoft Certified - Azure fundamentals.

I've already earned the MCSA SQL server 2012/2014 a couple of years ago. The badges of the achieved certifications are shown in the picture, below. 

The MCSA certifications are based on on - premise knowledge but also Azure knowledge is required. The Azure fundamentals certification is based on exam AZ-900 and is an exam of basic knowledge of Azure.

Fact oriented modelling

Fact Oriented Modelling (FOM) is another area of interest. FOM is based on NIAM. FOM is focussed on conceptual modelling, logical modelling and about algoritmic way of deriving an ER model based.

FOM is not a very broadly used modelling methodology, but has some important aspects I have not seen before:

• It is based on the communication about things in the world around us and not based on the objects in it self, in contrast with what traditional modelling techniques prescribes like Codd and Chen. If nobody talks (verbal communication, reports, Excel, etc) about it, it is not important for modelling in data structures. That makes sense.
• It has a strong PDCA cyclus in conceptual modelling, logical modelling and physical modelling. From what we learn about communication, sentences are created (verbalization), a logical model and a physical model is designed. From the data that is present in the tables, we can recreate the sentences and show that to the users in order to verify that the model is properly created. That is PDCA in the data modelling! This is much more intuitive than normal ER modelling with the Normal Forms.

These are just some aspects of FOM I've learned. I'll blog about this topic in the future to show more aspects of FOM. 

Hennie

DevOps : Deploying a Tabular cube with Powershell

Introduction

One step in a SQL data warehouse DevOps project is to deploy a SSAS tabular project on an instance. In this blogpost I'm going to show you a script that I'm using for deploying SSAS tabular cubes. As inspiration for creating the deployment script, I used information from a blogger Harbinger Singh. I had to make some changes to the script to make it work in my situation.

Steps

In the script, I've created a couple of blocks of code :

1. An array of cubes, I want to deploy to the server. This will help me control which cubes to deploy. Another option is to loop over the content of a folder and deploy the cubes.
2. Create a loop and loop through the array.
3. Check if the cube is present and print a warning if it can't find the cube.
4. Adjust the .asdatabase file database connectionstrings. I've multiple connections to databases and they must be changed.
5. Adjust the .deploymenttargets file database connectionstring.
6. Generate a .configsettings file. This file is not generated with the build of a SSAS tabular model.
7. Adjust .configsettings file database connectionstrings with the desired connectionstrings.
8. Not every cube uses a connectionstring to two databases. There is check whether there is a DWH_Control connectionstring in the .configsettings file. 
9. Adjust .deploymentoptions file database connectionstrings.
10. Create the xmla script with AnalysisServices.Deployment wizard.
11. The last step is to deploy the xmla script to the server with Invoke-ASCmd.

The code

The complete script is written below.

#---------------------------------------------------------------------
# AllCubes.SSASTAB.Dev.Script
#
#---------------------------------------------------------------------
# General variables
$path           = "C:\<directory>"
$SSASServer     = "localhost"
$DwDBnameDM     = "DWH_Datamart"
$DwDBnameCTRL   = "DWH_Control"
$DwServerName   = "localhost"

# Structure bimname, CubeDB, modelname
$CubeArray = @(
             ("<filename1>" , "<cubeDB1>"           , "<modelname1>"),
             ("<filename2>" , "<cubeDB2>"           , "<modelname2>")
)

cls
Write-Host "------------------------------------"
foreach ($element in $CubeArray) {

    $bim            = $element[0]
    $CubeDB         = $element[1]
    $CubeModelName  = $element[2]

    $AsDBpath             = "$path\$bim.asdatabase"
    $DepTargetpath        = "$path\$bim.deploymenttargets"
    $ConfigPath           = "$path\$bim.configsettings"
    $DeployOption         = "$path\$bim.deploymentoptions"
    $SsasDBconnection     = "DataSource=$SsasServer;Timeout=0"
    $DwDbDMConnString     = "Provider=SQLNCLI11.1;Data Source=$DwServerName;Integrated Security=SSPI;Initial Catalog=$DwDBnameDM"
    $DwDbCTRLConnString   = "Provider=SQLNCLI11.1;Data Source=$DwServerName;Integrated Security=SSPI;Initial Catalog=$DwDBnameCTRL"
    $IsDMConnStrPresent   = [bool]0
    $IsCTRLConnStrPresent = [bool]0

    if (!(Test-Path $AsDBpath))  {
      Write-Warning "$AsDBpath absent from location"
      Write-Host "------------------------------------"
      continue
    }

    #Adjust .asdatabase file database connectionstring
    $json = (Get-Content $AsDBpath -raw) | ConvertFrom-Json
    $json.model.dataSources | % {if($_.name -eq 'DWH_DataMart'){$_.connectionString=$DwDbDMConnString ; $IsDMConnStrPresent=[bool]1 }}
    $json.model.dataSources | % {if($_.name -eq 'DWH_Control'){$_.connectionString=$DwDbCTRLConnString ; $IsCTRLConnStrPresent=[bool]1 }}
    $json | ConvertTo-Json  -Depth 10 | set-content $AsDBpath

    #Adjust .deploymenttargets file database connectionstring
    $xml  = [xml](Get-Content $DepTargetpath)
    $xml.Data.Course.Subject
    $node = $xml.DeploymentTarget
    $node.Database = $CubeDB
    $node = $xml.DeploymentTarget
    $node.Server = $SsasServer
    $node = $xml.DeploymentTarget
    $node.ConnectionString = $SsasDBconnection
    $xml.Save($DepTargetpath)

    # generate .configsettings as this file is not generated with the build. 
    if (($IsDMConnStrPresent) -and ($IsCTRLConnStrPresent))  {
        '<ConfigurationSettings xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400" xmlns:ddl500="http://schemas.microsoft.com/analysisservices/2013/engine/500" xmlns:ddl500_500="http://schemas.microsoft.com/analysisservices/2013/engine/500/500" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0">
            <Database>
            <DataSources>
              <DataSource>
                <ID>DWH_DataMart</ID>
                <ConnectionString>Provider=SQLNCLI11.1;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=DWH_Datamart</ConnectionString>
                <ManagedProvider>
                </ManagedProvider>
                <ImpersonationInfo>
                  <ImpersonationMode xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">ImpersonateServiceAccount</ImpersonationMode>
                  <Account xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
                  </Account>
                  <Password xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
                  </Password>
                  <ImpersonationInfoSecurity xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">Unchanged</ImpersonationInfoSecurity>
                </ImpersonationInfo>
              </DataSource>
              <DataSource>
                <ID>DWH_Control</ID>
                <ConnectionString>Provider=SQLNCLI11.1;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=DWH_Control</ConnectionString>
                <ManagedProvider>
                </ManagedProvider>
                <ImpersonationInfo>
                  <ImpersonationMode xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">ImpersonateServiceAccount</ImpersonationMode>
                  <Account xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
                  </Account>
                  <Password xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
                  </Password>
                  <ImpersonationInfoSecurity xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">Unchanged</ImpersonationInfoSecurity>
                </ImpersonationInfo>
              </DataSource>
            </DataSources>
          </Database>
        </ConfigurationSettings>' | Out-File -FilePath $path\$bim.configsettings
    }
    else {
        '<ConfigurationSettings xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400" xmlns:ddl500="http://schemas.microsoft.com/analysisservices/2013/engine/500" xmlns:ddl500_500="http://schemas.microsoft.com/analysisservices/2013/engine/500/500" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0">
          <Database>
            <DataSources>
              <DataSource>
                <ID>DWH_DataMart</ID>
                <ConnectionString>Provider=SQLNCLI11.1;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=DWH_Datamart</ConnectionString>
                <ManagedProvider>
                </ManagedProvider>
                <ImpersonationInfo>
                  <ImpersonationMode xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">ImpersonateServiceAccount</ImpersonationMode>
                  <Account xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
                  </Account>
                  <Password xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
                  </Password>
                  <ImpersonationInfoSecurity xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">Unchanged</ImpersonationInfoSecurity>
                </ImpersonationInfo>
              </DataSource>
            </DataSources>
          </Database>
        </ConfigurationSettings>' | Out-File -FilePath $path\$bim.configsettings
    }

    #Adjust .configsettings file database connectionstring
    $xml = [xml](Get-Content $ConfigPath)
    $xml.Data.Course.Subject
    $nodeDM = $xml.ConfigurationSettings.Database.DataSources.DataSource | ? { $_.ID -eq $DwDBnameDM }
    $nodeDM.ConnectionString = $DwDbDMConnString
    $nodeCTRL = $xml.ConfigurationSettings.Database.DataSources.DataSource | ? { $_.ID -eq $DwDBnameCTRL }

    # In case here is not a DWH_Control Connectionstring in the .configsettings file
    if (![string]::IsNullOrEmpty($nodeCTRL))
    {
        $nodeCTRL.ConnectionString = $DwDbCTRLConnString
        $xml.Save($ConfigPath)
    }

    #Adjust .deploymentoptions file database connectionstring
    $xml = [xml](Get-Content $DeployOption)
    $xml.Data.Course.Subject
    $node = $xml.DeploymentOptions
    $node.ProcessingOption = "DoNotProcess"
    $xml.Save($DeployOption)

    # Create the xmla script with AnalysisServices.Deployment wizard
    Write-Host "Deploying Cube : $CubeDB"
    $path = $path
    cd $path 
    $exe = "C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Microsoft.AnalysisServices.Deployment.exe"
    $param1 = $bim + ".asdatabase"
    $param2 = "/s:" + $bim + ".txt"
    $param3 = "/o:" + $bim + ".xmla"
    $param4 = "/d"
    &($exe)($param1)($param2)($param3)($param4)

    Write-Host "Importing SQL modules..."
    # import modules 
    if ((Get-Module -ListAvailable | where-object {($_.Name -eq 'SqlServer') -and ($_.Version.Major -gt 20) } |Measure).Count -eq 1){
        # implementation of new sql modules migated into new location
        Import-Module SqlServer -DisableNameChecking
    }
    else{
        # fallback for SQLPS 
        Import-Module SQLPS -DisableNameChecking -Verbose
    }

    Write-Host "Invoking deployment script... This may take several minutes."
    Invoke-ASCmd -Server:$SsasServer -InputFile $path\$bim.xmla | Out-File $path\$bim.xml
    Write-Host "Please check $path\$bim.xml as this is output of this deployment"
    Write-Host "Done."
    Write-Host "------------------------------------"
}

Final thoughts

Although it is quite a script, it is fairly easy to setup and deploy a cube with a Powershell script. In order to use it in Azure DevOps you have to replace some of the variables with the Azure DevOps variables to make it work as you desire.

Hennie

Azure : Setting up a SQL DW (MPP)

Introduction 

In this blogpost I'll describe a walkthrough of setting up of a SQL DW in Azure. This is the MPP solution of Microsoft in the cloud. As you will see in this blogpost setting up a SQL DW is very easy in Azure.

Settung up the SQL DW

1. Sign up for the SQL DW in Azure.

2. Click New, choose database and then SQL Data warehouse create.

3. The next step is configuring the database and the Server.

4. And don't forget to select Select when you configure the Server. When all is setup correctly press Create. It will take some time to create the SQL Data warehouse. Wait for the notification of successful deployment. 

5. Now next step is adding your client pc you're working on to the Firewall settings

Connecting to the SQL DW from my local VM

6. Next step is spinning up a local VM and try to connect to the SQLDW instance

7. And this succeeds..

Conclusion

This is a blogpost about how to setup a SQLDW in the Azure. Setting up a basic SQL DW is very simple. Working with the client tools with Azure is also easy to achieve.

Hennie 

Setting up Visual Studio Team Services

Introduction

When a team of developers grows larger and larger it is a best practice to introduce Version Control in the project. Version control has some advantages :

• A controlled way of working together on the same code.
• Storing versions in order to understand the differences and to understand what happened during development of the code.
• To see how much work a subproject/sprint was during evaluation.
• As a backup for the code (in a former blogpost I described a way to automatically backup the project)
• Creating multiple branches in the code in order to distinguish subprojects, Development and production code (eg. for hotfixes).

In this blogpost the following steps are described:

• Step 1 : Create a sample project in Visual Studio
• Step 2 : Create a Team Project
• Step 3 : Connect to the Team project environment in Visual Studio.
• Step 4:  Setup a workspace on your local system.
• Step 5:  Add the project to Source control / Team project
• Step 6:  Do some changes in the local VS project.
• Step 7:  Check the online Team foundation Server environment.

Step 1: Create a sample project

First, lets make a solution with some examples in a Visual Studio project. The solution I've created for this blogpost includes a SQLProject, a SSISProject and a Tabular Project.

Step 2: Create a Team Project

The next step is to go to the VisualStudio.com and to create a new team project with New team project. 

Give the Team project a proper project name, give the project a Description, choose the type of version control and choose the type of project type with Work Item process. Choose Create.

Now, the project is created in Team Projects and an overview is given about the project.

Step 3:  Connect to the Team Project environment

The next step is to find the Team Explorer in Visual Studio, and there are several options to choose from. In this case, select the Connect link of the Visual Studio Team Services.

The window "Connect to Team Foundation Server" appears and now the url is needed of name of the Visual Studio Team services account. Select the option "Servers".

Press the Servers button and Add a new server. Login in with your account and press sign in

The following window appears

Connecting with the Visual Studio Team Services and select the Team projects.

Step 4: Setup a workspace

In Visual Studio it is needed that there is a local workspace to work with. This is a local copy of the code of the central repository. Click on Configure Workspace.

Next step is to Map & Get the local workspace with the central repository

When the Map&Get is successfully a message is presented : "The workspace was mapped successfully"

Step 5: Add the project to Source control / Team Projects

Now the workspace and the central repository is connected and now it 's time to add the local solution to the Visual Studio Team Services by clicking on Add Solution to Source Control.

Now, it is important to set the location in Visual Studio Team Services. Press OK when  this is done

Now the projects are marked with a + sign indicating that the files are ready to check in Visual Studio Team Services.

Right click on the solution and click on Check in to upload the code the repository.

Add some comment, check if everything is ok and press Check in

A confirmation window appears and click on Yes.

A confirmation is shown to the usr that the code is successfully checked in

Step 6: Do some changes in the local VS project

Now, let's see what happens when something changes in the code. In the initial situation the code is locked and check in.

For instance let's add a SSIS package to the SSIS project.

Two packages were added to the local Workspace. You can see this below Package1 and Package2.

Because the SSIS Packages are added to the project(file), the project (file) is also marked as changed and therefore this should be uploaded to the repository too.

Select the Project and click on the Source Control option.

An overview is shown of the new and changed packages. Click on Check in.

Step 7: The check the online Visual Studio Team Services environment

The last step is checking the online Visual Studio Team Services environment where the code is now stored.

Conclusion

In this blogpost I've described the setup of Visual Studio and Visual Studio Team Services working together such that version control of your BI project is enhanced.