Author: MichaelAdrianJohnson

I’m speaking: PASS Business Analytics Marathon

MichaelAdrianJohnsonLeave a comment

PASS_17_BAMarathon_Banner_440x220.jpg

I will be presenting a webinar on 7 June for the PASS Business Analytics marathon sponsored by Quest.

During my talk, I will introduce attendees to streaming data and how it different from how we as data professionals have traditionally dealt with data. We will then look at how Azure Stream Analytics can be used to provide rich real-time reporting.

We will conclude the talk with a practical demonstration of creating a streaming analytics Job in Azure and use PowerBI to visualize the results.

Presentation code:
You can download the code I used to create the events here

Importing data from web pages using Power BI

MichaelAdrianJohnsonLeave a comment

Sometimes the data we need for our data models can only found on web pages. The challenge with web pages is that they are often poorly structured and contain a mix of content types such as tables, lists and images, this makes importing data challenging so is often omitted in most ETL tools. Fortunately importing data from websites is made pretty easy in Power BI. In this post, we will import a list of countries along with its populationRead More »

Importing data into Power BI desktop using R

MichaelAdrianJohnsonLeave a comment

Power Bi is a great tool for data discovery with new functionality being made available all the time. However, this functionality is still limited when compared to a mature platform like R and you may come across scenarios where R is a better choice. It is for just such cases that Power BI now supports R script as a data source. Three scenarios come to mind where this might want to do this:

Read More »

SatRday Cape Town – I’m speaking

MichaelAdrianJohnsonLeave a comment

satrdaylogo-squareSQL Saturdays are popular events with many of these events taking place every weekend all over the world. Well, the R community is getting in on the action and I am excited to have been selected to speak at only the second event taking place in Cape Town on the 18th February 2017.

I will be talking about how Power BI  can be extended using R scripts and visualisations. There is still of space available with some amazing speakers so make sure to register.

Update: The event is now fully booked. See you all there.

CREATE OR ALTER now in SQL 2016 SP1

MichaelAdrianJohnsonLeave a comment

The other day I wrote about the DROP IF EXISTS statement. I concluded that post by saying that I wished that there was a CREATE OR ALTER statement available and it turns out that there is. As of SQL 2016  SP1 the  CREATE OR ALTER statement is available for a limited number of SQL objects, namely objects that do not have a state.

  • Views
  • Stored Procedures
  • Functions
  • Triggers

An example of using the CREATE OR ALTER statement without the need to check for its existence first.


CREATE OR ALTER PROCEDURE [DBO].[TestProcedure]
AS
BEGIN
SELECT 1 [TestValue]
END
GO

EXEC [DBO].[TestProcedure];
GO

CREATE OR ALTER PROCEDURE [DBO].[TestProcedure]
AS
BEGIN
SELECT 2 [TestValue]
END
GO

EXEC [DBO].[TestProcedure];

It is important to mention that security of these objects is preserved so this is not the same as dropping and recreating an object.

Like the DROP IF EXISTS statement, this will make creating deployable code much easier as we no longer have to perform the existence checks that we had to in the past.

 

Connecting to SQL Server using R

MichaelAdrianJohnsonLeave a comment

When analyzing data in R it is often best to source data directly, and because so much data exists in rational databases such as SQL Server it is important that we are able to connect to this data without first exporting it to a common format like CSV only to import that data into R. RODBC is a package that allows access to databases using an ODBC interface. To use RODBC you can connect using an ODBC connection as documented here or you can connect using a connection string as seen in the code snippet below.


#install.packages("RODBC")

library(RODBC)

dbConn <-  odbcDriverConnect('driver={SQL Server};server=localhost;database=MyDatabase;trusted_connection=true')

tableList <- sqlQuery(dbConn, 'select * from INFORMATION_SCHEMA.TABLES')

close(dbConn)

In this example, I have used a trusted connection but you would be able to provide a UserName and password (for more on connection strings make sure to check out connectionstrings.com).

DROP IF EXISTS in SQL2016

MichaelAdrianJohnsonLeave a comment

Before SQL Server 2016 if you wanted to drop an object such as a table you would first have to check if that object existed, then if it did exist delete it. The code could look something like this.


IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES T WHERE TABLE_NAME = 'MyTable' AND TABLE_SCHEMA = 'DBO')

DROP TABLE [dbo].[MyTable];

With the enhancement, the code above could be reduced to.


DROP TABLE IF EXISTS [dbo].[MyTable];

This code is both simpler to read and implement which is never a bad thing. Tables are not the only things that can be dropped in this way and other objects such as indexes, schema and even databases can also be dropped in this way.

In addition to dropping the objects,  you can also use the DROP IF EXIST in the ALTER TABLE statement to drop columns or constraints.


DROP TABLE IF EXISTS [dbo].[MyTable];

CREATE TABLE [dbo].[MyTable]

(

[ID] INT,

[SomeColumn] INT,

CONSTRAINT PK_MyTable_ID PRIMARY KEY CLUSTERED ([ID])

);

ALTER TABLE [dbo].[MyTable] DROP COLUMN IF EXISTS [SomeColumn];

ALTER TABLE [dbo].[MyTable] DROP CONSTRAINT IF EXISTS [PK_MyTable_ID];

This is a great addition to T-SQL however, I feel a CREATE OR ALTER IF EXISTS would be more useful although understandably harder to implement and is something we will hopefully see in the near future.