Joey Blue, Author at SQL Training Online

November 15, 2012 by Joey Blue Leave a Comment

Microsoft Excel to Windows Azure SQL in 10 minutes

Video: Microsoft Excel to Windows Azure SQL in 10 minutes – SQL Training Online
In this video, I walk you through setting up a Windows Azure SQL Database and then importing some Microsoft Excel data. I show you a trick to get the SQL Server Management Studio Import/Export Wizard to work for a Windows Azure connection.

How do you import data into Windows Azure?

There is a pretty easy way to import data into Windows Azure using the SQL Server Management Studio Import/Export wizard. But, you have to use a little trick I came up with to make it work.

I found a sample project over at CodePlex by Derrick VanArnam that demonstrates how to import Microsoft Excel data into Windows Azure.

It has a sample Microsoft Excel file and a Microsoft Word document that walk you through using SQL Server Integration Services (SSIS) for importing the Excel file into Azure.

In the video, I cover the following.

• Login to Windows Azure

• Create a SQL Server

• Create a Database

• Connect with Management Studio

• Show you a trick for Import/Export Wizard

• Move Data from Excel to Windows Azure

After you create a Windows Azure database, you will have to allow your IPAddress access through the Azure firewall.

Then, you open SQL Server Management Studio and connect to your database.

Now, create a new Azure database with the following script.

CREATE DATABASE [Olympics]
GO

It’s now time for the trick I mention.

By default, you won’t see the Import/Export wizard on the menus in the Azure connection.

You have to open up another connection in your Object Explorer in Management Studio. You should make this new connection a non-Azure database.

Then, you will see the Import/Export wizard.

Next, you will need to connect to the Excel File and the Azure Database.

One last trick, is to make sure your new tables have a clustered index.

Insert the following into each of your excel create table scripts as the first column.

id int identity primary key,

Finish the wizard and watch the data go into the tables.

That’s it. That is how you import data from Microsoft Excel to Windows Azure SQL database.

*Let me know what you think by commenting or sharing on twitter, Facebook, google+, etc.

You can visit me at any of the following:

SQL Training Online: https://www.sqltrainingonline.com

Twitter: http://www.twitter.com/sql_by_joey

Google+: https://plus.google.com/#100925239624117719658/posts

LinkedIn: http://www.linkedin.com/in/joeyblue

Facebook: http://www.facebook.com/sqltrainingonline

November 12, 2012 by Joey Blue Leave a Comment

Microsoft Business Intelligence Poster

Microsoft put out a new poster to explain all of their Business Intelligence Tools and Components. In this article, I try to break down each of the sections in the poster to help make it easier to understand.

[divider_line_dashed][/divider_line_dashed]

Introduction

Anyone who has tried to understand the full set of Microsoft Business Intelligence Tools knows that there are many pieces and it can be difficult to understand how they all fit together.

You have Microsoft SQL Server as the core, SQL Server Analysis Services as the OLAP tool (SSAS), SQL Server Reporting Services as the canned Reporting Tool (SSRS), and SQL Server Integration Services as the ETL tool (SSIS).

Then you can also add in Sharepoint, Excel, PowerPivot, Power View, Excel Services, Report Designer, and Report Builder.

If you aren’t overwhelmed already, you can try to understand how all of this works in the Cloud with Apache Hadoop-based Services in Windows Azure, Microsoft Excel, HiveQL Queries, and Power View.

That is a lot to take in, so Microsoft put out a nice poster to help group these technologies together.

[features_box_blue width=”75%” + border=”2px”]

Download Microsoft’s Poster:

Microsoft Business Intelligence at a Glance Poster

This is a big poster and they suggest that you print it on 44” x 34” paper.

[/features_box_blue]

Poster Breakdown

I thought it would be good for me to go through each section and make sure I understand what is really going on.

The rest of this article simply breaks down the poster and links to the relevant content to help in understanding this poster.

So, let’s take a look at a few of the sections.

Client Tools

These are the tools that the end-user will see.

*The print is too small to read in this image, but you can follow along in the downloaded document.

Business Intelligence in Excel

Microsoft starts by mentioning the Microsoft Excel as their “Self-Service BI tool of Choice”. Which is why you see the big circle around the X.

The key components of this piece of the diagram:

SQL Server Data Tools

The SQL Server Data Tools are used to help with your Visual Studio Development and to manage the different projects.

You should focus on the following:

Reporting Tools

The Reporting tools section is based in SQL Server Reporting Services (SSRS), but also adds the Power View into the mix.

For this sections, the 3 parts to focus on are very apparent. But, Power View is the newest toy.

Data Mining Add-Ins

There are some add-ins for Excel and SQL Server for data mining predictive analytics.

Presentation Subsystem

Now we are moving our way toward the behind the scenes programs.

SharePoint Server 2013

This is where the services run for Excel Services, PerformancePoint Services, Visio Services, PowerPivot for SharePoint, and Reporting Services Power View for SharePoint.

Audit and Control Management Server

There is an audit control system or change management system to track what is going on with your office documents in Network Shares and SharePoint libraries.

Microsoft Audit Control and Management Server

Information Sources

In the context of this poster, Information Sources bring in data from multiple locations and mash it together as an Information source that the end user can consume.

Microsoft lists 2 different source types in this section.

BI Semantic Model in Analysis Services

The BI Semantic Model in Analysis Services has two different categories in it. Multi-Dimensional and Tabular.

Here are the main topics to understand from this portion of the diagram.

Native Mode Reporting Services

This is the traditional SQL Server Reporting Services (SSRS). It is the standalone version and is not the Integrated Mode that runs with SharePoint. This means that it does not include Reporting Services Power View.

Native Mode SQL Server Reporting Services

Data Sources

This is a grouping of Data Sources that can feed the Information Sources layer.

Here are a few of the highlights from this section.

Explore, Visualize, and Control

This section is a bit of marketing, but it helps to understand Microsoft’s focus in this document.

Explore – They want you to know about Excel’s PowerPivot, Power View, and xVelocity.
Visualize – They are doubling down on the new Power View, with a small focus on the flexibility with the data models.
Control – This is all about Self-Service for the End-User. But, they don’t want to scare off IT, so they talk about monitoring and protecting BI assets.

Mobility, Cloud and Scalability

The right column is all about mobility, cloud and scalability. Let me take it from the top to the bottom.

Mobile BI

This section is about showing that you can view BI on your mobile device. Even the Apple iPad.

Here are the list of items that are claimed to be available on mobile.

PowerPivot Workbooks in Office 365

This is more mobility. You can share your Excel PowerPivot workbook up to Microsoft Office 365 and then you can use your browser to interact with it.

Farm Internal and External Access to Workbooks as a Data Source

These two images talk about the SharePoint 2013 Farm and show the technical layers involved with both the Internal and External setup.

Here are a few of the topics to focus on:

Reporting Services Data Alerts

This section talks about the ability of Integrated Reporting Services (SharePoint mode) to alert you when data changes on a report. The point is so that you don’t have to look at a report until something happens and then the report is pushed (emailed) to you.

Reporting Services Data Alerts

Big Data Analytics

This section of Microsoft’s Report focuses on the Apache Hadoop-based Services in Windows Azure and how it can feed Microsoft Excel, SQL Server Database Engine, and Analysis Services.

Without being too redundant with the rest of this blog post, here are a few links from the focus on this image.

Conclusion

Microsoft’s new Business Intelligence at a Glance Poster is a good way to become familiar with all of the parts involved with Microsoft’s BI solution.

Let me know what you think by commenting or sharing on twitter, facebook, google+, etc.

SQL Training Online: https://www.sqltrainingonline.com

Twitter: http://www.twitter.com/sql_by_joey

Google+: https://plus.google.com/#100925239624117719658/posts

LinkedIn: http://www.linkedin.com/in/joeyblue

Facebook: http://www.facebook.com/sqltrainingonline

November 9, 2012 by Joey Blue Leave a Comment

How to Remove Duplicate Rows in SQL Server

Video: How to Remove Duplicate Rows in SQL Server – SQL Training Online
In this video, I show you a trick on removing duplicate rows in SQL Server 2012.

[sharebox4 sharetext=”Share This Page”] [/sharebox4]

How to Remove Duplicate Rows in SQL Server?

I first need a table that has duplicate records to remove, so here is a script to create some duplicates in an email address table.

create table email_list
(
name varchar(100)
,email varchar(300)
)
go
insert into email_list values ('Jan','[email protected]');
insert into email_list values ('Bob','[email protected]');
insert into email_list values ('Jill','[email protected]');
insert into email_list values ('Bob','[email protected]');
insert into email_list values ('Jill','[email protected]');
insert into email_list values ('Bob','[email protected]');
insert into email_list values ('Jill','[email protected]');

Now, the first method to remove the duplicates is in the following script that I adapted from Alexander’s Post on duplicates.

WITH list_rownumbers AS
(
SELECT name,email,
ROW_NUMBER() OVER (ORDER BY name,email) AS 'RowNumber'
FROM email_list
)
DELETE list_rownumbers WHERE RowNumber not in
(SELECT min(RowNumber) FROM list_rownumbers GROUP BY name,email)

If you just look at the SQL With statement (CTE), you will see that it simply numbers all of the name, email combinations using the Row_Number function. Then it uses the minimum rows number per name, email group as an exclusion to remove the duplicate rows.

The second script is and adaptation of Ritesh’s Post on duplicates.

WITH list_duplicates (name, email, duplicate_count) AS
(
SELECT name,email,
ROW_NUMBER() OVER(PARTITION BY name,email ORDER BY name,email) AS duplicate_count
FROM email_list
)
DELETE
FROM list_duplicates
WHERE duplicate_count > 1

If we focus on the CTE query, we see that is uses the Partition By statement to actually reset the row_number count on each name, email group.

It makes the delete statement a little easier to follow since all we have to do is delete where the row_number (duplicate_count) is larger than 1.

And that’s it. That is how you Delete Duplicates in SQL.

Let me know what you think by commenting or sharing on twitter, facebook, google+, etc.

SQL Training Online: https://www.sqltrainingonline.com

Twitter: http://www.twitter.com/sql_by_joey

Google+: https://plus.google.com/#100925239624117719658/posts

LinkedIn: http://www.linkedin.com/in/joeyblue

Facebook: http://www.facebook.com/sqltrainingonline

November 5, 2012 by Joey Blue Leave a Comment

How to Filter for SQL Null or Empty String

Video: How to Filter for SQL Null or Empty String – SQL Training Online
In this video, I show you how to filter a SQL table that has both Null values and an Empty string. I use SQL Server 2012 to demonstrate the technique.

[sharebox4 sharetext=”Share This Page”] [/sharebox4]

How do you filter a SQL Null or Empty String?

A null value in a database really means the lack of a value. It is a special “value” that you can’t compare to using the normal operators. You have to use a clause in SQL IS Null.

On the other hand, an empty string is an actual value that can be compared to in a database. You simply use two ticks together.

''

Let’s take a look at the vendor table to demonstrate this.

select * from vendor

In this table, I specifically put in some email addresses that are both null and empty strings.

Let’s pull out the NULL values using the IS NULL operator.

select * from vendor
where vendor_email is null

Next, I want to pull out the empty string using the tick-tick, or empty string.

select * from vendor
where vendor_email = ''

If you want to combine them to search for the SQL null or empty string together and retrieve all of the empty strings and nulls all at once, you could do something like this.

select * from vendor
where vendor_email = ''
or vendor_email is null

Finally, I want to show you a little trick that I have used on occasion to change all of the null values to empty strings and then compare the result to an empty string.

select * from vendor
where isnull(vendor_email,'') = ''

And that is it.

Here is the script to create the vendor table I was using as an example.

create table dbo.vendor
(
vendor_id int identity(1,1) primary key,
vendor_name varchar(50),
vendor_email varchar(100)
)
insert into vendor (vendor_name,vendor_email) values ('Dell','[email protected]');
insert into vendor (vendor_name,vendor_email) values ('IBM','[email protected]');
insert into vendor (vendor_name,vendor_email) values ('Microsoft','[email protected]');
insert into vendor (vendor_name,vendor_email) values ('Oracle','');
insert into vendor (vendor_name,vendor_email) values ('Intel',NULL);
insert into vendor (vendor_name,vendor_email) values ('Apple','[email protected]');

There it is. That is how you filter for SQL Null or Empty String.

Let me know what you think by commenting or sharing on twitter, facebook, google+, etc.

If you enjoy the video, please give it a like, comment, or subscribe to my channel.

You can visit me at any of the following:

SQL Training Online: https://www.sqltrainingonline.com

Twitter: http://www.twitter.com/sql_by_joey

Google+: https://plus.google.com/#100925239624117719658/posts

LinkedIn: http://www.linkedin.com/in/joeyblue

Facebook: http://www.facebook.com/sqltrainingonline

November 1, 2012 by Joey Blue Leave a Comment

How to Test SQL Server Functions

Video: How to Test SQL Server Functions – SQL Training Online
In this video, I show you a trick on how to test SQL Functions in SQL Server 2012.

[sharebox4 sharetext=”Share This Page”] [/sharebox4]

How do you test a SQL Server Function?

Testing a new function is easier to do when you don’t actually try to query a table to do it.

I will show you how to trim off the front part of an email address by first testing the functions, then putting them into the actual SQL query.

Let’s start with the following SQL query that is written against the Adventure Works database.

SELECT
s.[BusinessEntityID]
,s.[Name]
,ct.[Name] AS [ContactType]
,p.[Title]
,p.[FirstName]
,p.[MiddleName]
,p.[LastName]
,p.[Suffix]
,pp.[PhoneNumber]
,pnt.[Name] AS [PhoneNumberType]
,ea.[EmailAddress]
,p.[EmailPromotion]
FROM [Sales].[Store] s
INNER JOIN [Person].[BusinessEntityContact] bec
ON bec.[BusinessEntityID] = s.[BusinessEntityID]
INNER JOIN [Person].[ContactType] ct
ON ct.[ContactTypeID] = bec.[ContactTypeID]
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = bec.[PersonID]
LEFT OUTER JOIN [Person].[EmailAddress] ea
ON ea.[BusinessEntityID] = p.[BusinessEntityID]
LEFT OUTER JOIN [Person].[PersonPhone] pp
ON pp.[BusinessEntityID] = p.[BusinessEntityID]
LEFT OUTER JOIN [Person].[PhoneNumberType] pnt
ON pnt.[PhoneNumberTypeID] = pp.[PhoneNumberTypeID];

This query joins multiple tables and can be intimidating to the beginner. The main thing to look at is that it returns an email address.

I want to trim off the front part of the email address.

So, I will copy an email address to work on as an example.

‘[email protected]’

Now, SQL Server allows you to run a Select query without a FROM clause.

We will use that capability to test our function. Here is an example.

select '[email protected]'

Now, I want to find the ‘@’ character in the string, so I will use the CharIndex function. Now it is time to test the SQL Server Function:

select charindex('@','[email protected]')

This returns the number 9, since the ‘@’ sign is the 9^th character.

Now, we need to use the substring to pull off all of the character until the ‘@’ sign. So, let’s test the substring function:

select charindex('@','[email protected]')
,substring('[email protected]',1,9-1)

This does what we want, so now we need to test both SQL Server Functions together.

select substring('[email protected]',1,charindex('@','[email protected]')-1)

This is exactly what we want. So, we just need to put the function into the main query.

SELECT
s.[BusinessEntityID]
,s.[Name]
,ct.[Name] AS [ContactType]
,p.[Title]
,p.[FirstName]
,p.[MiddleName]
,p.[LastName]
,p.[Suffix]
,pp.[PhoneNumber]
,pnt.[Name] AS [PhoneNumberType]
,ea.[EmailAddress]
,substring(ea.[EmailAddress] ,1,charindex('@',ea.[EmailAddress] )-1)
,p.[EmailPromotion]
FROM [Sales].[Store] s
INNER JOIN [Person].[BusinessEntityContact] bec
ON bec.[BusinessEntityID] = s.[BusinessEntityID]
INNER JOIN [Person].[ContactType] ct
ON ct.[ContactTypeID] = bec.[ContactTypeID]
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = bec.[PersonID]
LEFT OUTER JOIN [Person].[EmailAddress] ea
ON ea.[BusinessEntityID] = p.[BusinessEntityID]
LEFT OUTER JOIN [Person].[PersonPhone] pp
ON pp.[BusinessEntityID] = p.[BusinessEntityID]
LEFT OUTER JOIN [Person].[PhoneNumberType] pnt
ON pnt.[PhoneNumberTypeID] = pp.[PhoneNumberTypeID];

There it is. That is how you Test SQL Server Functions.

Let me know what you think by commenting or sharing on twitter, facebook, google+, etc.

If you enjoy the video, please give it a like, comment, or subscribe to my channel.

You can visit me at any of the following:

SQL Training Online: https://www.sqltrainingonline.com

Twitter: http://www.twitter.com/sql_by_joey

Google+: https://plus.google.com/#100925239624117719658/posts

LinkedIn: http://www.linkedin.com/in/joeyblue

Facebook: http://www.facebook.com/sqltrainingonline

« Previous Page
1
…
3
4
5
6
7
…
10
Next Page »