Data Mining: What is Data Mining?

Overview

Generally, data mining (sometimes called data or knowledge discovery) is the process of analyzing data from different perspectives and summarizing it into useful information - information that can be used to increase revenue, cuts costs, or both. Data mining software is one of a number of analytical tools for analyzing data. It allows users to analyze data from many different dimensions or angles, categorize it, and summarize the relationships identified. Technically, data mining is the process of finding correlations or patterns among dozens of fields in large relational databases.

Continuous Innovation

Although data mining is a relatively new term, the technology is not. Companies have used powerful computers to sift through volumes of supermarket scanner data and analyze market research reports for years. However, continuous innovations in computer processing power, disk storage, and statistical software are dramatically increasing the accuracy of analysis while driving down the cost.

Example

For example, one Midwest grocery chain used the data mining capacity of Oracle software to analyze local buying patterns. They discovered that when men bought diapers on Thursdays and Saturdays, they also tended to buy beer. Further analysis showed that these shoppers typically did their weekly grocery shopping on Saturdays. On Thursdays, however, they only bought a few items. The retailer concluded that they purchased the beer to have it available for the upcoming weekend. The grocery chain could use this newly discovered information in various ways to increase revenue. For example, they could move the beer display closer to the diaper display. And, they could make sure beer and diapers were sold at full price on Thursdays.

Import data from Excel to SQLServer

Many times we come across a situation where we have readily available excel sheets having enormous data that needs to be imported in database system for enhanced querying capabilities or as a backend datasource for some sort of software development.

We could achieve this goal either by copy-pasting the excel rows directly to the destined database table in SQL Server Management Studio or by querying directly on the excel sheet from SQL Server Management Studio itself!

The former way (copy-pasting) looks pretty simple but consider a situation where you are having 10K records in a single excel sheet and wanted to fetch some selective/filtered records only! At that time, the earlier way is much efficient and ideal.

I've created this sample excel file to import/retrieve the data in SQL server, and is also the file I will be using in this article. Here is the screenshot for the same.

Excel sheet - Cars

OPENROWSET() to query excel files

Here, I've used the OPENROWSET() function to query excel files. This is a T-SQL function that can be used to access any OLE DB data source. All you need is the right OLE DB driver.

The following are the queries to retrieve data from Excel files.

--Excel 2007-2010
SELECT * --INTO #Cars
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;HDR=YES;Database=C:\cars.xlsx','SELECT * FROM [cars$]');

--Excel 97-2003

SELECT * --INTO #Cars

FROM OPENROWSET('Microsoft.ACE.OLEDB.4.0',

'Excel 8.0;HDR=YES;Database=C:\cars.xls','SELECT * FROM [cars$]');

SQL Server blocked access to STATEMENT OpenRowset OpenDatasource of component Ad Hoc Distributed Queries

Error :

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

Solution :

Solution for this common error in Sql Server is to enable the OpenRowset in Sql Server. We can do it by enabling theShowAdvanced Options and Ad hoc Distributed Queries. this error also appears when you execute some Adhoc queries in Sql Server Query analyzer. If you enable this for the first time, The further statements using this option will go fine without flaw.

To enable the ShowAdvanced Options, Please follow/use the below TSQL statements

SP_CONFIGURE 'SHOW ADVANCED OPTIONS', 1 
GO 
RECONFIGURE 
GO

After executing the above staement if you try to execute or use OpenRowSet or any TSQL commands that requires the Ad Hoc Queries option to be enabled, you would get the below error

Msg 15281, Level 16, State 1, Line 1 SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

We can get Rid of the above error using the below TSQL commands

SP_CONFIGURE 'Ad Hoc Distributed Queries', 1 
GO 
RECONFIGURE 
GO 

Once the above TSQL statements are getting executed, you would be able to use the OpenRowSet Statement successfully. To Enable these two options you must have the sysadmin role. If you do not have this role, please contact your database administrator for doing this operation