Sunday, October 4, 2020

Azure Synapse Analytics - Part I

 This video speaks about the latest in data warehousing and big data analytics: Azure Synapse Analytics. This is the first part of it and it explains;

- The way we have been creating on-premise data solutions
- The way we have been creating data solutions in Azure before Azure Synapse Analytics
- How we can create data solutions in Azure using Azure Synapse Analytics
- Quick demo on creating the workspace, SQL Pool, Spark cluster and accessing and processing data



Friday, June 5, 2020

Power BI Desktop Model – Connect from SSMS and Export more than 30,000 rows

This video shows the way of connecting to Power BI Desktop Model from SQL Server Management Studio and using it for various operations. Additionally this shows how to get data saved into csv files using SSMS when number of records exceeds 30,000.


Azure Databricks – Enabling Databricks CLI for Azure Portal and Windows

This video shows the way of installing and configuring Azure Databricks CLI for Azure Portal (Cloud Shell) and Windows.


Azure Data Factory - Accessing a Databricks Notebook with Input and Output Parameters

This video shows the way of accessing Azure Databricks Notebooks through Azure Data Factory. Additionally, it explains how to pass values to the Notebook as parameters and how to get the returned value from Notebook to Data Factory Pipeline.



Monday, May 11, 2020

Coronavirus (Covid-19) Dashboard solution with Power BI

If you are interested in #covid-19 statistics, check this #dashboard out. This has been developed using #powerbi, #azuresqldatabase and #azuredatafactory.

http://coronavirusexplorer.com/



Sunday, April 19, 2020

Azure Databricks - Accessing Data Lake using Azure Active Directory Credentials

This video discusses the way of accessing Azure Data Lake Gen 2 through Azure Databricks, using Azure Active Directory Credentials. Following are discussed;
  • Ways of Accessing ADLS Gen 2
  • Enabling Azure Data Lake Credential Passthrough
  • Demo: Accessing ADLS Gen 2 in Azure Databricks
    • Creating an Azure Databricks workspace
    • Configuring Standard Cluster for AAD Credentials
    • Configuring High Concurrency Cluster for AAD Credentials

Azure Databricks - Accessing Data Lake - Using a Service Principal

This video discusses the ways of accessing Azure Data Lake Gen 2 in Azure Databricks, specifically using a Service Principal. Following are discussed in detail;
  • Ways of accessing Azure Data Lake Gen 2
  • Creating a Service Principal for accessing Azure Data Lake
  • Access using Role-based access control
  • Access using ACL-based access control

Wednesday, April 8, 2020

Sending Emails from Azure SQL Database - YouTube Video

This video focuses on sending emails for operations related to Azure SQL databases. Following are discussed and demonstrated;

  • Possibilities of tracking changes in Azure SQL Database for sending emails
  • Setting up an Azure SendGrid account
  • Setting up an Azure Logi Apps for sending emails

Data Science with SQL Server Machine Learning Services - YouTube Video

This video discusses performing data science experiments in SQL Server using Python. Setting up the environment, training, storing models and consuming trained models stored in the database are discussed with a demonstration.


Automating Resume and Pause of Azure Analysis Services - YouTube Video

This video discusses how to automate starting and stopping Azure Analysis Services using Azure Automation Account. Following are demonstrated;
  • Create Azure Automation Account
  • Import libraries required
  • Create a PowerShell Runbook
  • Schedule the Runbook

Saturday, March 21, 2020

Power BI App for seeing Coronavirus - COVID-19 updates in Sri Lanka

Here is an Azure site built for showing updates related to Coronavirus - COVID-19, specifically for Sri Lanka.

This has been designed using Azure Data Factory, Azure SQL Database, Power BI and Azure App Services.

 Coronavirus COVID-19 - Sri Lanka - Update

Tuesday, April 2, 2019

Azure Databricks - What, Why & How - YouTube Video

Here is the first video on Azure Databricks. This talks about;

  • Why we need to Azure Databricks for processing data
  • What is Azure Databricks
  • Demo: How to provision an Azure Databricks workspace, a cluster, a database and a table.



Monday, February 18, 2019

Creating Paginated Reports with Power BI

Here is a video on Power BI Paginated reports. This videos discusses/demonstrates;

  • What is a paginated report?
  • Power BI support on paginated reports
  • Power BI configuration for paginated reports
  • Creating a paginated report using Report Builder
  • Publishing a paginated report sourced to local database, to Power BI portal.
Here is the video:

Tuesday, July 3, 2018

Azure Data Lake Analytics - Accessing External Data Sources with USQL

This video discusses the way of accessing external data sources such as Azure SQL Database with USQL as external data sets. It explains why we need it, what objects should be created for accessing external sources and how external data can be accessed in USQL codes.

Watch and see!


Sunday, June 24, 2018

Azure Data Factory V2 - Lookup and ForEach Activities

Here is my third video on Azure Data Factory. This video discusses and demonstrates;

  • Why we need iteration in ETLs
  • Lookup Activity
  • ForEach Activity
  • Demo: Iterating over a data collection using ForEach Activity, with data loaded from Lookup Activity

Thursday, February 1, 2018

Azure Data Factory V2 - Creating a Pipeline using Author-Tab

Here is another video on Azure Data Factory V2. This video explains how to create a Pipeline manually, using Author-Tab. This video discusses and demonstrates;
  • How to create Linked Services manually
  • How to create Datasets manually
  • How to create a Pipeline and add Activities
  • And how to schedule the Pipeline






Thursday, January 25, 2018

Azure Data Factory V2 - Copying On-Premise SQL Server data to Azure Data Lake Store

Azure Data Factory has been enhanced significantly with V2 and its support on Cloud-ETL-and-ELT is excellent now. First version of it was not much user-friendly but now it is not that difficult to implement ETL solutions with V2.

This video discusses;
  • Why we need Azure Data Factory
  • What is Azure Data Factory
  • How it works and how it should be used
  • Integration Runtime - installing for connecting with sources
  • Copying On-Premise SQL Server Data to Azure Data Lake using Data Factory

Here is the video;


Here are some sample screens;





Monday, January 15, 2018

Introduction to Azure Data Lake Analytics - and basics of U-SQL

I made another video on Azure Data Lake, specifically on Azure Data Lake Analytics. This is a 40-minutes video and it discusses following items along with demonstrations;

  • What is Azure Data Lake Analytics
  • Data Lake Architecture and how it works
  • Comparison between Azure Data Lake Analytics, HDInsight and Hadoop for processing Big Data.
  • What is U-SQL and basics of it.
  • Demo on How to create an Azure Data Lake Analytics account
  • Demo on How to execute a simple U-SQL using Azure Portal
  • Demo on How to extract multiple files, transform using C# methods and referenced assembly, making multiple results with bit complex transformations using Visual Studio.
Here is the video.



Thursday, January 4, 2018

Introduction to Azure Data Lake Store - Does it replace the Data Warehouse?

I have made a video on Azure Data Lake and Azure Data Lake Store and published in YouTube. This video explains What is Azure Data Lake, Its Characteristics, Components of it, How it works, Usage, Comparison between Data Lake Store and Blob Storage, Modern Data Warehousing and How can we use Azure Data Lake with modern data warehousing.



Have a look on it and let me know you thoughts or questions you have.

Saturday, October 21, 2017

Microsoft Access Database Engine 2016 Redistributable Setup - You cannot install the 32-bit version, You cannot install the 64-bit version

In order to access Excel workbook using Integration Services, one component we need to install is Microsoft Access Database Engine 2016 Redistributable. If 32-bit version of Office is installed, then 32-bit version of Microsoft Access Database Engine 2016 Redistributable should be installed. If 64-bit version of Office is installed, then 64-bit version of it has to be installed.

If it is not installed, this is what we see when try to access an Excel workbook

The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. If the 32-bit drivers is no.....


Generally, once it is installed, we can connect to Excel and work with it without any issue. However, I started experiencing below error when I try to install 32-bit version of Microsoft Access Database Engine 2016 Redistributable.

You cannot install the 32-bit version of Microsoft Access Database Engine 2016 because you currently have 64-bit Office products installed. If you want to.....


I was confused with this message because I have installed 32-bit version of Office. However, just for checking, I tried to install 64-bit version of Microsoft Access Database Engine 2016 Redistributable and this is what I got when I tried........

You cannot install the 64-bit version of Microsoft Access Database Engine 2016 because you currently have 32-bit Office products installed. If you want to....

Not sure what is the reason but it stopped me continuing, leaving me only option as uninstalling Office and installing again. Since it is not the best way, just thought to try out older version of Microsoft Access Database Engine Redistributable, hence downloaded 32-bit version of Microsoft Access Database Engine 2010 Redistributable from https://www.microsoft.com/en-us/download/details.aspx?id=13255. Good news is, I was able to install it and the best news is, SSIS could connect to Excel without any issue. This may not be the best solution for above error but for an urgent situation, you can try this and see.

Friday, October 20, 2017

Azure Marketplace - SQL Server 2016 Virtual Machine - Data Tools cannot be installed

As you know, easiest way of getting a SQL Server set up in Azure is, purchasing a Virtual Machine in the Marketplace. There are multiple VMs for SQL Server based on Standard, Enterprise and Developer editions, you can pick the one as per your requirements.

Remember, it does not come with all tools. It has Management Studio installed but it does not have Data Tools installed, hence it needs to be installed.

I had configured a SQL Server 2016 SP1 VM and have been using for sometime. There was no requirement for Data Tools but I experienced an issue when I try to install Data Tools using standard Setup.exe.



I noticed that Setup fails when it tries to download required components from Microsoft site.



......Error 0x80072f08: Failed to send request to URL: https://go.microsoft.com/fwlink/?LinkId=832089&clcid=0x409...............

I did not immediately identify the issue but with my fellow MVPs' help, I realized that it can happen with a block related to security.

If you face the same, make sure that no firewall settings block required site and Administrator (or whoever run) has no restrictions. You can make sure that Administrator has no restrictions by checking Server Manager - Local Server - IE enhanced security configuration.


Wednesday, October 18, 2017

tempdb becomes full when updating Clustered Columnstore index

I had a table with 3.5 billion records and wanted to update one column in all records. This table was not partitioned though the design says that it should be partitioned. With spec of 2 processors (12 cores) and 64 GB RAM, it could never complete the update as it makes the tempdb full, almost 800GB, consuming all space in the drive.

I am still not sure about the exact reason for this but listing out possible reasons that could help you too.
  1. Table is not partitioned. I should have applied partitioning before loading billions of records.
  2. Clustered columnstore index needs to be rebuilt. May it takes long time because the index is fragmented (had no way of checking as everything was urgent).
  3. May be, it is not something to be surprised, update means, delete and insert, that makes records adding delta and then move to rowgroups, hence takes time and needs extra support from tempdb.
  4. Not enough memory for completing the UPDATE process, hence use tempdb.
However, I manage to execute the UPDATE as a batch process. Although it took a significant time, it did not use tempdb (it might have slightly used but did not notice).

Here is the code I finally used;

WHILE EXISTS (SELECT * FROM dbo.Table01 WHERE Column1 IS NULL)
BEGIN

 UPDATE TOP (1000000) dbo.Table01
  SET Column1 = RIGHT(Column2, 4)
 WHERE Column1 IS NULL
END

Monday, October 16, 2017

SQL Server Always Encrypted - Serving applications using Azure Key Vault and Certificate Store

Here is my second video related to Always Encrypted. This video discusses the way of configuring CMK for serving multiple applications hosted in multiple places, using Local Key Store - Certificate Store and Centralized Key Store - Azure Key Vault.


Here are the codes related to the video. I create a table in my Azure SQL Database, this is the code for it;

CREATE TABLE dbo.Message
(
 MessageId int identity(1,1) primary key
 , MessageCode char(5) COLLATE Latin1_General_BIN2 not null 
 , Message varchar(4000)  COLLATE Latin1_General_BIN2 not null
);
GO

INSERT INTO dbo.Message (MessageCode, Message)
 VALUES ('AA56B', 'This is a test message');
GO

CREATE OR ALTER PROCEDURE dbo.AddMessage @MessageCode char(5)
  , @Message varchar(4000)
AS
BEGIN

 INSERT INTO dbo.Message
  (MessageCode, Message)
 VALUES
  (@MessageCode, @Message);
END
GO


CREATE OR ALTER PROCEDURE dbo.GetMessage @MessageCode char(5)
   , @Message varchar(4000) OUTPUT
AS
BEGIN

 SELECT @Message = Message 
 FROM dbo.Message
 WHERE @MessageCode = MessageCode;
END
GO

And then, I have a .Net Application that accesses Azure Key Vault for taking the CMK and inserting and updating records. Here is the code of it. 

private static ClientCredential _clientCredential;

static void InitializeAzureKeyVaultProvider()
{
    _clientCredential = new ClientCredential("9e67ee1f-50ef-49d1-8ee0-0c48eaf4457b", "HjRqkx7BKLP7Lu+UYgTa5D/zCKAdxx3YITQ6fRrsQTQ=");

    SqlColumnEncryptionAzureKeyVaultProvider azureKeyVaultProvider =
        new SqlColumnEncryptionAzureKeyVaultProvider(GetToken);

    Dictionary providers =
        new Dictionary();

    providers.Add(SqlColumnEncryptionAzureKeyVaultProvider.ProviderName, azureKeyVaultProvider);
    SqlConnection.RegisterColumnEncryptionKeyStoreProviders(providers);
}

public async static Task GetToken(string authority, string resource, string scope)
{
    var authContext = new AuthenticationContext(authority);
    AuthenticationResult result = await authContext.AcquireTokenAsync(resource, _clientCredential);

    if (result == null)
        throw new InvalidOperationException("Failed to obtain the access token");
    return result.AccessToken;
}

public Form1()
{
    InitializeComponent();
    InitializeAzureKeyVaultProvider();

}

private void buttonSubmit_Click(object sender, EventArgs e)
{
    SqlConnection connection = new SqlConnection("Password=Pa$$w0rd;Persist Security Info=True;User ID=Dinesh;Initial Catalog=Marketing;Data Source=dinesqlserver.database.windows.net;Column Encryption Setting = Enabled");
    SqlCommand command = new SqlCommand("AddMessage", connection);
    command.CommandType = CommandType.StoredProcedure;

    SqlParameter parameterMessageCode = new SqlParameter("MessageCode", SqlDbType.Char, 5);
    parameterMessageCode.Value = textBoxMessageCode.Text;

    SqlParameter parameterMessage = new SqlParameter("Message", SqlDbType.VarChar, 4000);
    parameterMessage.Value = textBoxMessage.Text;

    command.Parameters.Add(parameterMessageCode);
    command.Parameters.Add(parameterMessage);

    connection.Open();
    command.ExecuteScalar();
    connection.Close();

}

private void buttonGet_Click(object sender, EventArgs e)
{
    SqlConnection connection = new SqlConnection("Password=Pa$$w0rd;Persist Security Info=True;User ID=Dinesh;Initial Catalog=Marketing;Data Source=dinesqlserver.database.windows.net; Column Encryption Setting = Enabled");
    SqlCommand command = new SqlCommand("GetMessage", connection);
    command.CommandType = CommandType.StoredProcedure;

    SqlParameter parameterMessageCode = new SqlParameter("MessageCode", SqlDbType.Char, 5);
    parameterMessageCode.Value = textBoxMessageCode.Text;

    SqlParameter parameterMessage = new SqlParameter("Message", SqlDbType.VarChar, 4000);
    parameterMessage.Direction = ParameterDirection.Output;

    command.Parameters.Add(parameterMessageCode);
    command.Parameters.Add(parameterMessage);

    connection.Open();
    command.ExecuteScalar();
    connection.Close();

    MessageBox.Show(parameterMessage.Value.ToString());
}

You can find more information and standard codes related to Azure Key Vault usage with Always Encrypted at: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-always-encrypted-azure-key-vault


Thursday, October 12, 2017

Introduction to Always Encrypted

Here is my second video that speaks about Always Encrypted feature. This video shows How Always Encrypted works, how it should be configured and how we can access and update date using a .Net application.


Monday, October 2, 2017

Introduction to Azure HDInsight

I have written few posts on HDInsight and thought to make series of Video on it as well. Here is the first one, with this video, I discuss, what is Hadoop, What is HDInsight, Configuration of HDInsight, Important Settings related to HDInsight and how to access the cluster using Azure Portal and PuTTY.

Watch and see!