Redshift Query performance tuning

Redshift Query performance tuning tips

Clustered vs Serverless Redshift:

• In a provisioned cluster, the resources allocated are predefined which can sometimes become bottleneck when running heavy workloads. However, Serverless Redshift can scale up dynamically according to the workload which can increase efficiency and saves money

 Order of Joins in SQL Query:

• Always try to start joining tables with less data first and then move to the tables with alot of data

CTE vs Temp table:

• Both CTE and temp table are good but try to use CTE's when you are dealing with less data. Use temp tables in your query when you are dealing with a lot of data.

Incremental Load of Redshift data into Dynamodb table using AWS Glue

In today's post we will load incremental data from Redshift into Amazon Dynamodb using AWS Glue.

To load data incrementally into Dynamodb, we need a date/id column. Dynamodb supports limited query features. Therefore, we will create a Global Secondary Index on a date column (create_datetime) as a sort key and a static temporary column (gsi_sk) as partition key with value of "1". our table name for this demo is "test_inc_tble"

Power BI - Input box to filter the visuals

Scenario: In this scenario, we will add an Input box in the power bi dashboard that will filter the visuals.

AWS Redshift data sharing using Lake formation

Scenario: In this scenario, we will be configuring data sharing between two Redshift databases in different accounts via central access management using Lake formation.

Informatica EDC profiling 10.4.1 Error - Could not create id creator for schema dbo and table due to missing row count result summary

Scenario: We want to integrate Azure SQL with Informatica EDC 10.4.1 and run profiling. 

Error: Integration and connection test was successful from the Informatica Catalog Admin. but, when we run the resource, it fails.

Steps to Resolve:

Tableau - Create Box plot visual to display the CAGR

Today, we will learn on how to create and compare historical vs future CAGR using a box plot chart

Export Athena View as CSV to AWS S3 using Glue

Today we will learn on how to export Athena view to AWS S3 using Glue

Move file from one S3 folder to another using AWS Glue

Today we will learn on how to move file from one S3 location to another using AWS Glue 

Create a parameter to filter the visual in Tableau Dashboard

Today we will learn on how to Create a parameter to filter the visual in Tableau

                                                     

AWS Glue - Unpivot columns into rows using python shell job

Today we will learn on how to unpivot columns into rows using AWS Glue python shell job

Create multiple dropdown list filters in a tableau dashboard to filter multiple sheets

Today we will learn on how to create multiple filters (as parameters) in a tableau dashboard to filter multiple sheets

Import data to Azure Cosmos DB using Azure DocumentDB Data Migration Tool

Today we will learn how to import data from our SQL Server database to Cosmos DB on Azure.

We will use "AdventureWorks2016" sample database for this article. Here we are now going to migrate our SQL Database from MS SQL Server database to Document DB on Azure using Azure DocumentDB Data Migration Tool.

Tableau - Change bar chart colours

Today we will learn on how to change the color of each bar in the bar chart visual in Tableau

Tableau visual error - field does not exist in the table

Error: The field <fieldname> does not exist in the table

Reason: Once you refresh the tableau data source, there is a possibility (in rare chances) that you might see error in some of your visuals. There can be two reasons behind this error:

• The field name gets renamed in the underlying data source
• The field name gets deleted

Resolution:

• Refresh the data source 
• If the field is renamed, then click the arrow on the right of the field that is giving error

How to resolve Configuration Package template Decimal Error in Business Central

Problem Statement: Customer is getting Decimal error while importing data through the Configuration package template.

 

Resolution: The error comes when someone has changed something in the package / template but forgot to change the same in the file. The best option is to make a new Configuration Package Card to avoid any confusion.

Steps:

• Open configuration package
• Go to Navigation Path >  SEARCH ENGINE > write "CONFIG" in the search 
• Click on configuration package

Get Started with Azure Cosmos db - SQL API

Azure Cosmos DB

Introduction

An Azure Cosmos DB is a document-oriented and a non-relational database. It does not model data in tabular format instead the data resides in a document form which is a JSON format. So unlike the SQL server, the data in cosmos DB does not reside in the table-column structure.

It extends Azure DocumentDB which was released in 2014. it is schema-less and generally classified as a NoSQL database. Azure Cosmos DB is Microsoft’s proprietary globally-distributed, multi-model database service "for managing data at the planet-scale" launched in May 2017. It uses query language similar to SQL and supports ACID property of transactions.

Create a view in AWS Athena by merging two SQL queries using Union All

Today we will lean on how to create a view in AWS Athena by merging multiple queries result in one query

To merge multiple queries in Athena, we will be creating a view in Athena by using:

• With common table expression (CTE) to hold the result of each query in a separate temp table
• Union ALL to merge multiple queries results
• To use Union keyword, both queries should return exact same columns

Setup Billing Alerts for AWS Services using CloudWatch

Today we will learn on how to setup billing alerts for AWS Services

AWS Cloud Watch service is used to create billing alarms for all the AWS services. Using Cloud watch, we can monitor the service usage charge per month (e.g.: EC2 estimated charges) and send alert notifications to the specified persons.