Aveek Das

Aveek Das
Latest posts by Aveek Das (see all)
Create table using Design Pane

Working with Database Projects

January 22, 2021 by

In this article, I am going to talk about developing and deploying a database project, also known as a data-tier application using Visual Studio. In my previous article Getting started with Data-Tier Applications using Visual Studio, I have provided an overview of the data tier applications and how can we create one using Visual Studio. This article is a follow-up to the previous article. I’d advise you to have a look at it before proceeding forward with this as this is a continuation of the previous. For the article, I would be using Visual Studio 2019, however, you are free to use any other versions of Visual Studio.

Read more »
Selecting Target Platform

Getting started with Data-Tier applications in Visual Studio

January 15, 2021 by

In this article, I am going to talk about creating a data-tier application using Visual Studio. In my previous article An introduction to Data-Tier applications in SQL Server, I have explained in detail what a data-tier application is all about. I have explained what the different types of data-tier applications are available and how can we create such applications from existing SQL Server databases. In this article, the primary focus would be to create data-tier applications from scratch using Visual Studio. For this article, I am going to use Visual Studio 2019, however, the technique will remain similar for other editions of SQL Server as well.

Read more »
AWS IAM Service in AWS Management Console

An overview of AWS IAM

January 13, 2021 by

In this article, I am going to introduce the concept of AWS IAM, also known as Identity and Access Management in AWS. In any cloud service, controlling who has access to the services and how each of the services accesses the other services is an important task. If we do not control the access or restrict then there might be cases of a security breach within the services and we might not be able to track those as well. So as a best practice to restrict or control access within the AWS, there is a special service called IAM that can be used to manage and control almost everything in AWS. It is the permission control system that controls access to the various AWS resources and services.

Read more »
Creating job step for OLAP Cube

Advanced Usages of SQL Server Agent

January 8, 2021 by

In this article, I am going to introduce some advanced usages of the SQL Server Agent service in Microsoft SQL Server. In my previous article, Introduction to SQL Server Agent, I have discussed in detail how to use the service and the various components related to the service. To recap briefly, the SQL Server Agent is a job scheduler service within SQL Server and allows us to schedule T-SQL scripts, SSIS jobs, automate database backups and other tasks etc. In the last article, I have shown how to schedule a simple T-SQL script using the SQL Server Agent. This article will focus more on advanced concepts like scheduling a package in SSIS and processing an OLAP cube.

Read more »
Creating the Azure Function App

Logging messages from Azure Functions to Azure SQL Database

December 25, 2020 by

In this article, I am going to explain how to create a serverless application using Azure Functions and use Azure SQL Database to log messages generated by the function. In this world of cloud-based applications, it is very important that you are aware of how to create and design serverless applications. An important aspect while designing any application is to generate log messages at every key step or operation that is being performed. This helps us to understand the workflow whenever there are some issues and need debugging at some later point in time.

Read more »
Starting the SQL Server Agent Service

Introduction to the SQL Server Agent

December 15, 2020 by

In this article, I am going to explain in detail about the SQL Server Agent service. This is a windows service that enables database developers and database administrators to schedule jobs on the SQL Server machine. The jobs can be simple T-SQL scripts, stored procedures, SSIS packages or SSAS databases. This service is available on all the editions of SQL Server except the Express edition.

Read more »
Capturing updates and Operation - change tracking in sql server

Understanding Change Tracking in SQL Server using Triggers

December 9, 2020 by

In this article, I am going to explain what change tracking is in SQL Server and why do we need it. I will also illustrate the same using some practical examples using triggers in SQL Server. Change tracking as the name suggests, is a mechanism that helps us to identify the changes in the database as the application grows. In other words, it enables us to have a history of the changes that have been made to one or more tables in the database. The changes can be considered as either INSERTs, UPDATEs, or DELETEs.

Read more »
Documenting SSIS Packages using Sequence Diagrams

Documenting SSIS Packages using Sequence Diagrams

November 25, 2020 by

In this article, I am going to explain in detail how to document SSIS packages using Sequence Diagrams and the importance of these diagrams in the field of software engineering, no matter which programming language are you using. In my previous article, I have talked about the various UML Diagrams that are being used to document various software engineering processes. Also, I have talked about modular ETL architecture and how to create such a modular package in SSIS. Sequence diagrams are also a part of the broader UML Diagrams which define the interaction between the various components in the system in a chronological manner.

Read more »
Executing the master package - ETL in SSIS

Implementing a Modular ETL in SSIS

November 24, 2020 by

In this article, I am going to demonstrate about implementing the Modular ETL in SSIS practically. In my previous article on Designing a Modular ETL Architecture, I have explained in theory what a modular ETL solution is and how to design one. We have also understood the concepts behind a modular ETL solution and the benefits of it in the world of data warehousing. We have also related the concept of microservices architecture in software development to that of the modular ETL solution.

Read more »

Designing a Modular ETL Architecture

November 17, 2020 by

In this article, I am going to demonstrate in detail the Modular ETL Architecture. ETL is a vast concept which explains the methodology of moving data across various sources to destinations while performing some sort of transformations within it. This is an advanced article that considers that the user has a substantial amount of understanding about how ETL is implemented using different tools like SSIS and the underlying working principle along with how to deploy multiple packages using SSIS. It is extremely important to implement a well-designed ETL architecture for your organization’s workload, otherwise, it might lead to performance degradations along with other challenges. To keep things simple, I will just explain the Modular ETL Architecture in this article which will be followed by a detailed hands-on tutorial in the next article – “Implementing Modular Architecture in ETL using SSIS”.

Read more »
Executing the SSIS Package

An introduction to SSIS Data Lineage concepts

September 3, 2020 by

In this article, I am going to discuss SSIS data lineage concepts, which are often used while designing ETL workloads on a data warehouse. Although this article is focused on implementing data lineage using SSIS, it does not only confine to SSIS but to any ETL tools in the market using which data is moved from one source to a destination. In my previous article, Understanding Data Lineage in ETL, I have already discussed the generic importance of data lineage concepts for any ETL tool. I would definitely suggest you have a look at it if you want to understand in general how data lineage helps to track the source of a single record in the warehouse.

Read more »
Transformation flow diagram

Understanding Data Lineage in ETL

September 3, 2020 by

In this article, I am going to explain what Data Lineage in ETL is and how to implement the same. In this modern world, where companies are dealing with a humongous amount of data every day, there also lies a challenge to efficiently manage and monitor this data. There are systems that generate data every second and are being processed to a final reporting or monitoring tool for analysis. In order to process this data, we use a variety of ETL tools, which in turn makes the data transformation possible in a managed way.

Read more »
Adding optional arguments for demonstration - databases in python

Exploring databases in Python using Pandas

August 24, 2020 by

In this article, I am going to cover in detail about working with databases in Python using Pandas and SQLAlchemy. This is a part of the series “Learn Pandas in Python” where I talk about the various techniques to work with the Pandas module in Python.

Read more »
Selecting data from table

Introduction to SQLAlchemy in Pandas Dataframe

August 20, 2020 by

In this article, I am going to demonstrate how to connect to databases using a pandas dataframe object. Pandas in Python uses a module known as SQLAlchemy to connect to various databases and perform database operations. In the previous article in this series “Learn Pandas in Python”, I have explained how to get up and running with the dataframe object in pandas. Using the dataframe object, you can easily start working with your structured datasets in a similar way that of relational tables. I would suggest you have a look at that article in case you are new to pandas and want to learn more about the dataframe object.

Read more »
Virtual Environment created

Working with Pandas Dataframes in Python

August 19, 2020 by

In this article, I am going to explain in detail the Pandas Dataframe objects in python. In the previous article in this series Learn Pandas in Python, I have explained what pandas are and how can we install the same in our development machines. I have also explained the use of pandas along with other important libraries for the purpose of analyzing data with more ease. Pandas provides a dataframe object which makes it relatively easier to consider working with the data as it provides a tabular interface for the data in it. People who are already familiar in working with relational databases, they can really find similarities between a table in the database and the dataframe object in pandas.

Read more »
Deploying the serverless application

Deploy serverless applications using the AWS SAM CLI

August 18, 2020 by

In this article, we are going to learn to deploy serverless applications to the AWS Cloud using the AWS SAM CLI. This article is a part of the three-article series “Develop and Deploy Serverless Applications with AWS SAM CLI”. If you have some idea about how to develop and test your serverless applications locally using the AWS SAM CLI, then you might proceed with this article. However, if you want to learn more about developing and running your code locally, I would strongly recommend reading the previous articles of this series, Getting started with the AWS SAM CLI and Set up a local serverless environment using the AWS SAM CLI, which explains in detail the various configurations required to start and run the serverless functions on your local.

Read more »
Opening serverless app in VS Code

Set up a local serverless environment using the AWS SAM CLI

August 18, 2020 by

In this article, we are going to work on setting up your local development environment for creating serverless applications using the AWS SAM CLI. This article is a part of the three-article series “Develop and Deploy Serverless Applications with AWS SAM CLI”. If you already know about the working principle of the AWS SAM CLI, you may proceed with this; otherwise, I would highly recommend reading my previous article on the series, Getting started with the AWS SAM CLI, where I talk about the introduction to the AWS Serverless Application Model and its workflow.

Read more »
AWS SAM Workflow

Getting started with the AWS SAM CLI

August 17, 2020 by

In this article, we will learn the concept of the AWS SAM CLI. This is a part of the three-article series “Develop and Deploy Serverless Applications with AWS SAM CLI”. SAM, abbreviated for Serverless Application Model is a framework provided by Amazon Web Services, which can be leveraged to build applications on the local machine and deploy those to the AWS Lambdas directly.

Read more »
Notebook

Getting started with Jupyter Notebooks

August 14, 2020 by

In this article, I am going to explain what Jupyter Notebooks are and how to install the same on your machine. Further, I will demonstrate how to use these notebooks using Visual Studio Code and perform data analysis and other development activities. It is an open-source platform using which you can create and share documents that contain live code, equations, and visualizations along with the formatted text. Most importantly, these notebooks can be run on the web browser by just starting a server and using it. This open-source project is maintained by the team at Project Jupyter.

Read more »
Installing Pandas in Python

Getting started with Pandas in Python

August 5, 2020 by

In this article, I am going to explain how to use Pandas in Python. Pandas is one of the most popular modules in python that can be used for data manipulation and analysis using python. Basically, it provides an easy interface to interact with flowing data and apply transformations to them on the go. This module is covered under the BSD license and can be used for free. You can download this module by visiting the website or by installing it through the python package manager.

Read more »
AWS S3 Home Screen

Getting started with Amazon S3 and Python

July 31, 2020 by

In this article, I am going to explain what Amazon S3 is and how to connect to it using python. This article will be focused on beginners who are trying to get their hands on in python and working around the AWS ecosystem. AWS, as you might know, is one of the largest cloud providers along with Microsoft Azure and Google Cloud Platform. There are a lot of services offered by Amazon including AWS S3. Amazon S3, also abbreviated as Amazon Simple Storage Service is a storage service offered by the cloud provider that enables the users to store any kind of files in this service. It is designed to make web-scale computing easier for developers.

Read more »
Power BI Report

Working with Power BI Data Models in Visual Studio Code

July 30, 2020 by

In this article, I am going to introduce the Tabular Object Model (TOM) in the Power BI Data Model and provide an understanding of how this model can be accessed outside of the Power BI environment. For more info about the Tabular Object Model in the Power BI Data Model, please read this article. In this tutorial, we are going to use the Visual Studio Code to simply write a dotnet console application and try to access the Tabular Object Model from the Power BI file. With the help of this knowledge, programmers and BI developers can not only view the underlying model in the Power BI Data Model but also can enhance the data model programmatically by writing a few lines of code. It can also be further improved by automating the creation of the Power BI models with the help of the Tabular Object Model library in dotnet.

Read more »
Binary Search Tree Example

Diving deep with complex Data Structures

July 29, 2020 by

In my previous article, Understanding common Data Structures, I have mentioned the most commonly used data structures in software programming. In this article, let us get into some more details about the other data structures that are a bit complex than the ones already discussed but also used quite often while designing software applications. Here, we will look into the following data structures.

Read more »

Understanding the SQL MERGE statement

July 27, 2020 by

In this article, I am going to give a detailed explanation of how to use the SQL MERGE statement in SQL Server. The MERGE statement in SQL is a very popular clause that can handle inserts, updates, and deletes all in a single transaction without having to write separate logic for each of these. You can specify conditions on which you expect the MERGE statement to insert, update, or delete, etc.

Read more »

Understanding common Data Structures

July 15, 2020 by

In this article, I am going to walk you through the concepts of the common Data Structures that every student, colleague working with computers should be aware of. Data Structure forms an integral part of any system or database design. It is a very interesting and intuitive concept that you can apply anywhere. Through this article, I aim to introduce the beginners to the concepts of Data Structures and brush up the same for colleagues who have already been associated with the industry for years. This will also help you understand some database concepts more easily once you have a grasp over these concepts.

Read more »