Dinesh Asanka

Dinesh Asanka
Final package after inclusion of Conditional Split.

Using the SSIS Script Component as a Data Source

June 25, 2020 by


SSIS Script component is one data transformation tasks in SQL Server Integration Services (SSIS). SSIS is an integration tool in the Microsoft BI family to extract data from heterogeneous data sources and transform it to your need. Apart from the standard data sources such as databases, text files, excel files, and web services, there can be instances where you need to retrieve non-traditional data sources. For example, let us say you want to extract the details of text files such as file sizes, created date, etc. In these types of scenarios, traditional data sources cannot be used.

Read more »
Experiement configuration is Azure ML Studio.

Introduction to Azure Machine Learning using Azure ML Studio

June 18, 2020 by


Let us see how Azure ML studio can be used to create machine learning models and how to consume them in this series. As we discussed during the data mining series, we identified the challenges in the predictions in data. In the Azure Machine learning platform, machine learning workflows can be defined in easy scale models in the cloud environment. Today will be looking at how datasets can be uploaded.

Read more »
SQL Service Broker objects in the SQL Server Management Studio after creating them.

Using the SQL Server Service Broker for Asynchronous Processing

June 10, 2020 by


In the real-world implementation of SQL Server of an enterprise system, it always needs to implement data in multiple databases. In most of the cases, a single transaction will span across multiple databases. If there are multiple databases, there will be performance issues and implementation issues during the cross-database transactions. However, with the use of SQL Service Broker that was introduced in SQL Server 2005, you can implement asynchronous transactions between databases.

Read more »

Text Mining in SQL Server

May 18, 2020 by

In this article, we will be discussing how Text Mining can be done in SQL Server. For text mining in SQL Server, we will be using Integration Services (SSIS) and SQL Server Analysis Services (SSAS). This is the last article of the Data Mining series during which we discussed Naïve Bayes, Decision Trees, Time Series, Association Rules, Clustering, Linear Regression, Neural Network, Sequence Clustering. Additionally, we discussed the way to measure the accuracy of the data mining models. In the last article, we discussed how models can be extracted from the Data query.

Read more »
Final SSIS package with Data Mining Query for Data Mining Query in SSIS.

Data Mining Query in SSIS

May 12, 2020 by

In this article, we will be discussing how SQL Server Integration Services (SSIS) can be used to predict data mining models built from SSAS. In this article, we will be looking at the Data Mining Query in SSIS. During the data mining article series, we have discussed all the Data mining techniques that are available in SQL Server. The discussed techniques were Naïve Bayes, Decision Trees, Time Series, Association Rules, Clustering, Linear Regression, Neural Network, Sequence Clustering. Further, we discussed how the accuracy of the data mining models can be verified.

Read more »
Connect to remote MySQL server without using SSL options in connection string

Measuring the Accuracy in Data Mining in SQL Server

April 29, 2020 by

In this article, we will be discussing measuring Accuracy in Data Mining in SQL Server. We have discussed all the Data mining techniques that are available in SQL Server in a series of articles. The discussed techniques were Naïve Bayes, Decision Trees, Time Series, Association Rules, Clustering, Linear Regression, Neural Network, Sequence Clustering. Data mining is a predicting technique using the existing pattern. It is obvious that we won’t be able to predict 100% accurately. However, since we are using data mining outcomes for better business decisions, the result should have better accuracy. If the accuracy is very low, we tend not to use those data mining models. Therefore, it is essential to find out how accurate your data mining models are.

Read more »
Dependancy Network tab in the Mining view in the Association Rule

Association Rule Mining in SQL Server

January 28, 2020 by

Association Rule Mining in SQL Server is the next article in our data mining article series in which we have discussed Naïve Bayes, Decision Trees, and Time Series until now. Association Rule Mining, also known as Market Basket Analysis, mainly because Association Mining is used to find out the items which are bought together by the customers during their shopping.

Read more »
Data types for selected attributes in Time Series Model

Microsoft Time Series in SQL Server

December 12, 2019 by

The next topic in our Data Mining series is the popular algorithm, Time Series. Since business users want to forecast values for areas like production, sales, profit, etc., with a time parameter, Time Series has become an important data mining tool. It essentially allows analyzing the past behavior of a variable over time in order to predict its future behavior.

Read more »
Transaction log usage after TRUNCATE TABLE statment is executed.

Truncate Table Operations in SQL Server

September 25, 2019 by

Truncating a table is removing all the records in an entire table or a table partition. TRUNCATE table is functionally similar to DELETE table with no WHERE clause. However, TRUNCATE table is much faster than DELETE with respect to the time and the resource consumptions which we will look at in this article. TRUNCATE statement removes the data by de-allocating the data pages in the table data. This means that TRUNCATE is similar to drop and re-create the table. Also, it records only the page de-allocations in the transaction log, not the row-wise as in DELETE statement.

Read more »
Dependency network of the Decision tree model.

Microsoft Decision Trees in SQL Server

September 12, 2019 by

Decision trees, one of the very popular data mining algorithm which is the next topic in our Data Mining series. In the previous article Introduction to SQL Server Data Mining, we discussed what data mining is and how to set up the data mining environment in SQL Server. Then in the next article, Microsoft Naïve Bayes algorithm was discussed. In this Article, Microsoft Decision Trees are discussed with examples. The Microsoft Decision Trees algorithm is a classification and regression algorithm that works well for predictive modeling. The algorithm supports the prediction of both discrete and continuous attributes.

Read more »
Selecting the mining struture from the available list

Introduction to SQL Server Data Mining

July 23, 2019 by

Prediction, is it a new thing for you? You won’t believe you are predicting from the bed to the office and to back to the bed. Just imagine, you have a meeting at 9 AM at the office. If you are using public transport, you need to predict at what time you have to leave so that you can reach the office for the meeting on time. Time may vary by considering the time and the day of the week, and the traffic condition etc. Before you leave your home, you might predict whether it will rain today and you might want to take an umbrella or necessary clothes with you. If you are using your vehicle then the prediction time would be different. If so, you don’t need to worry about the rain but you need to consider the fuel level you need to have to reach to the office. By looking at this simple example, you will understand how critical it is to predict and you understand that all these predictions are done with your experience but not by any scientific method.

Read more »

SQL Server auditing with Server and Database audit specifications

November 20, 2017 by

Auditing is a key feature in any application or any system as it provides end users with better analysis for administrators. Apart from analysis, auditing can be used as a troubleshooting mechanism too. Apart from organizational reasons, there are compliance reasons for enabling auditing depending on the domain of operation.

Auditing is mainly about answering four questions, i.e. who, when, what and where. However, depending on the situation, it might be decided what questions of the mentioned four should be answered.

Read more »