Loading Images Into SQL Server

A requirement arose recently to import a folder full of JPEG images into a SQL Server table. I ideally wanted to achieve this using pure T-SQL code; that is without using CLR code, external stored procedures or references to OLE objects.

The first challenge was to find a way to obtain a list of all the files in a particular operating system folder. To do this I decided to use xp_cmdshell to execute the DIR *.jpg command. The results returned from the EXEC statement can then be stored in a database table using the INSERT … EXEC statement. The required code is:

INSERT INTO ImportedImages(FileName)
EXEC xp_cmdshell 'dir c:\work\images\*.jpg /B'

DELETE FROM ImportedImages
WHERE FileName IS NULL

The DELETE is used to necessary to clear out a NULL file name returned by DIR.

The next challenge is, given a file name, to import the contents of the file into a column in a record in the database. The OPENROWSET function has a BULK option that can do this and the required command is:

SELECT bulkcolumn
FROM OPENROWSET(BULK 'C:\work\Images\1001.jpg', SINGLE_BLOB) AS I

This can be combined with an update to directly update the image column in the database as follows:

UPDATE ImportedImages

SET ImageData = (SELECT bulkcolumn
FROM OPENROWSET(BULK 'C:\work\Images\1001.jpg', SINGLE_BLOB) AS I)
WHERE FileName = 'C:\work\Images\1001.jpg'

The rest of the code just requires a cursor to loop through each file name and load the images one by one using dynamic SQL.

The full code including creating the database table is:

-- Create the table to hold the images

CREATE TABLE ImportedImages(
      FileName sysname NULL, ImageData varbinary(max) NULL)
GO

-- Get images names from folder
INSERT INTO ImportedImages(FileName)
EXEC xp_cmdshell 'dir c:\work\images\*.jpg /B'

DELETE FROM ImportedImages

WHERE FileName IS NULL
GO

-- Import all impages from folder

DECLARE C CURSOR FOR
      (SELECT FileName FROM ImportedImages WHERE ImageData IS NULL)
DECLARE @FileName sysname
DECLARE @Path sysname
DECLARE @SQL varchar(max)

-- Loop through each file loading the images one by one

OPEN C
FETCH NEXT FROM C INTO @FileName
WHILE (@@FETCH_STATUS <> -1)
BEGIN
  SET @Path = 'C:\work\Images\' + @FileName
  SET @SQL =
  'UPDATE ImportedImages
      SET ImageData = (SELECT bulkcolumn FROM OPENROWSET(
      BULK ''' + @Path + ''', SINGLE_BLOB) AS IMAGE)
      WHERE FileName = ''' + @FileName + ''''
  EXEC (@sql)
  FETCH NEXT FROM C INTO @FileName
END
CLOSE C
DEALLOCATE C

Mapping Spatial Data in SQL Server

Spatial data types were first introduced in SQL Server 2008 and allow points, lines and shapes to be stored inside a column inside a database table. There are two spatial data types:
·         Geometry: Stores spatial data on a flat surface
·         Geography: Stores spatial data on a sphere, or to be more exact the earth

Data can be inserted into a geography column in a table using references to spatial data objects such as POINT, LINESTRING, POLYGON etc. Some example inserts into a table that has a Location column of type geography are:

INSERT INTO Site (ID, Location)
VALUES (1, 'POINT(51.508 -0.128)')--London

INSERT INTO Site (ID, Location)
VALUES (2, 'POINT(48.857 2.352)') --Paris

INSERT INTO Site (ID, Location)
VALUES (2, 'LINESTRING(1 1,2 3,4 8, -6 3)')

The spatial data types also have built in methods allowing spatial computations to be carried out as part of an SQL query. There are many such methods including: STDistance(), STIntersection(), STUnion() and STDifference(). The first of these STDistance() calculates the distance between two points or shapes, in metres when using the default coordinate system, as shown in the example below:

DECLARE @p1 geography

SELECT @p1 = Location FROM Site WHERE ID = 1
SELECT Location.STDistance(@p1) FROM Site WHERE ID = 2

The answer is just over 402793 metres which when divided by 1609.344 gives 250 miles which is correct given that the first point is in London and the second one is in Paris.

So spatial shapes can be stored in a database and calculations can be carried out on them in SQL queries. But how do we visualise spatial data? Well one way is to the use the mapping facility in SQL Server Reporting Services (SSRS).

Conveniently the AdventureWorks sample database has a geography column called SpatialLocation in the Address table. So to demonstrate the combined capabilities of SQL spatial data types and maps; I have devised an example report in SSRS that allows a user to select an address in New York together with a distance in miles and then display on a map all the addresses that are within the distance.

I first wrote a query to return all the addresses in New York. This query is used to populate the dropdown list for the user to select a starting address:
SELECT A.[AddressID], A.[AddressLine1] + ' '
       + ISNULL(A.[AddressLine2], '')
       + ' ' + A.[City]
       + ' ' + SP.StateProvinceCode
       + A.[PostalCode] AS [Address]
FROM [AdventureWorks].[Person].[Address] AS A
INNER JOIN [AdventureWorks].[Person].[StateProvince] AS SP
ON A.StateProvinceID = SP.StateProvinceID
WHERE SP.CountryRegionCode = 'US'
AND SP.StateProvinceCode = 'NY'
ORDER BY A.[PostalCode]

I then wrote a query to return all the addresses within @Distance of the selected property. Note the used of the STDistance() spatial method to calculate the distance between the addresses. This query will be used to plot the addresses on the map:

DECLARE @Distance int = 100 -- Parameter
DECLARE @NYAddress int = 770 – Parameter
SELECT A.[AddressID], A.[AddressLine1] + ' '
       + A.[City]
       + ' ' + SP.StateProvinceCode +
       A.[PostalCode] AS [Address],
       CONVERT(int, A.[SpatialLocation].STDistance(
              A2.[SpatialLocation])/1609.344)
       AS [Distance], A.[SpatialLocation]
FROM [AdventureWorks].[Person].[Address] AS A
INNER JOIN [AdventureWorks].[Person].[StateProvince] AS SP
ON A.StateProvinceID = SP.StateProvinceID
INNER JOIN [AdventureWorks].[Person].[Address] AS A2
ON CONVERT(int, A.[SpatialLocation].STDistance(A2.[SpatialLocation])/1609.344)
       < @Distance
WHERE A2.AddressID = @NYAddress
ORDER BY [Distance]

Next step was to create a new blank SSRS report in SQL Server Data Tools and add:

·         A SQL Server data source connecting to AdventureWorks
·         Two data sets one for each of the SQL queries shown above
·         Two parameters, one for each of the parameters used in the second query
·         I dragged a Table item from the toolbox onto the report surface and configured it to display the Address and Distance columns for each for the selected addresses
·         I then dragged a Map item from the toolbox onto the report surface and configured it to plot the position of each of the selected addresses on a Bing map. To do this I went through wizard screens as shown below:

 

On this screen I selected “Layer Type” as Point and I checked the “Add a Bing Maps layer” option. I ignored the message about the @Display variable because I have defined it as a parameter and so I knew it would be OK.

 

For the rest of the Wizard screens I just selected the default options.

I then opened the Point Properties dialog for the Point Layer on the map and changed the fill colour to Red to make it easier to see the points on the map.

The result is below with 8 matching addresses shown for the 50 mile radius. Change the New York Address and the distance and click View Report and the map is rescaled and redrawn as required.

This is a simple example, but it shows how easy it is to create a spatially aware application using SQL Server. The database engine stores the locations and calculates the distances, while SSRS renders them nicely as a familiar and easy to use map.

 

This article is based on SQL Server 2012.

PowerPivot: Excel PivotTables on Steroids?

Pivot tables in Excel are an excellent tool and are used by many to aggregate and summarise data for reporting purposes. For example: A pivot table could be used to display product sales by month vs. region, department vs. year or as shown below: product vs. discount type:

If the original data is available in an Excel worksheet then the “Insert > Pivot Table” menu inserts a pivot table into the worksheet and presents a field list that can be used to drag and drop the required fields onto the table. This is all very straightforward and very powerful.

Earlier versions of Excel were limited to 65535 rows a worksheet. Later versions increased the limit to 1048576 rows. However the processing involved in combining data from worksheets and pivoting is computationally expensive and pivoting large volumes of data quickly becomes too slow. For data sets in excess of a million of rows it is simply not possible.

Excel pivot tables can also connect directly to external data sources such as SQL Server databases and cubes in SQL Server Analysis Server. Using an external data source in this way, and cubes in particular, is the usual way to pivot large volumes of data. Coverage of Analysis Server and the creation of cubes are beyond the scope of the blog, but they are a kind of large scale pivot table based on raw data in a database.

The snag is that although Excel pivot tables can be setup and used by a power user, Analysis Server cubes require a developer. Until that is, PowerPivot came on the scene with the release of SQL Server 2008 R2 and SQL Server 2012. PowerPivot allows a power user to combine data from Excel and external data sources such as databases and create large scale pivot tables without a developer. PowerPivot is essentially a cut down version of Analysis Server that is built into Excel. There is also a SharePoint version as well.

To trial the capabilities of PowerPivot I selected a customer database containing a table with 3.6 million rows of product sales and discount information. The database did not contain user friendly names for either the products or the discounts and so this information was added as Excel worksheets. I then selected the “Create Linked Table” on the “PowerPivot” menu as shown below to link my product and discount worksheets to PowerPivot (as shown below):

The next step was to select the “PowerPivot Window” menu in Excel followed by the “From Database” menu to create a sales data set base on a SQL query as shown below (as an alternative whole database tables can be loaded, avoiding the need to write SQL): 

Next the “Diagram View” was used to join the SQL database to the two linked worksheets by dragging and dropping the keys:

At this point there is data from 3.6 million rows linked to two worksheets all in PowerPivot. New calculated columns can be added just by entering a normal Excel formula, as shown below for the new column CalculatedColumn1.

In the “Calculation Area” (the lower half of the screen below the data) new aggregate calculations such as “=Min([Total Price])”, can be added, again by just using standard Excel formulas. However PowerPivot automatically calculates all the obvious ones and so there is no need create simple examples like this.

Once the data is in place, click the “PivotTable” menu to create pivot tables and charts from the data in the normal Excel way, but this time using millions of rows of data.

I opted to select “POS Name” and “Discount Name” as slicers to allow the user to easily select the required data with the mouse. The pivot table shows discount and total price for the selected products and discounts. The pivot chart shows the breakdown of the discounts for the selected products and discounts. Selecting any combination of products and discounts in the slicers on the left recalculates and redisplays the table and chart in a fraction of section, even with 3.6 million rows, and I could go way higher than this.

Essentially this example shows how easy it is to create large scale pivot tables in Excel without having to manually define and create cubes using Analysis Server.

This article is based on SQL Server 2012 PowerPivot for Excel in Excel 2010.

What underpins the Microsoft Application Platform?

The Microsoft Application Platform (MAP) is a technology stack used for the development and deployment of high end enterprise level business applications. It provides for both on-premise, cloud and hybrid based deployments.

MAP is based on the following layers:

·         Windows Server provides for the Infrastructure Layer and includes support for the core functionality needed by any application such as security, virtualisation and networking. Windows Azure provides the same for cloud based deployments

·         SQL Server provides the Database Layer and includes support for transactional database programming and data warehousing. SQL Azure provides for cloud deployments

·         The Application Services Layer provides a fully functional middle tier and includes facilities such as message handing, work flow, state management and caching. This layer is covered by Microsoft .NET, Windows Server AppFabric and Microsoft BizTalk Server. For the cloud it is covered by Windows Azure Platform AppFabric

·         The top layer is the Application Layer which encompasses a number of line-of-business applications from the Microsoft Dynamics suite and in particular Microsoft Dynamics CRM, Microsoft SharePoint and Microsoft Exchange. For the cloud there is Office 365 for Exchange and SharePoint and Microsoft Dynamics CRM Online

 

In addition to the application layers there are supporting tools including Visual Studio for developers and Microsoft System Centre for system administrators to help monitor and manage the infrastructure, particularly relevant net with on-premise and hybrid deployments.

Note that SQL Server is used to provide the data layer for all of the key components of MAP including BizTalk, SharePoint, CRM and any custom components.

MAP is not just designed to support deployment of fixed packaged applications. One of its unique properties is that it provides the core functionality for custom applications without requiring development from scratch. For example:

·         SharePoint can be customised by:

o   Developing Web Parts that add new functionality to the web pages

o   Workflows can be developed to manage the handing of documents and other content

o   Event Receivers can add special processing when data changes

o   Developing new page layouts that control how pages are edited in the content management system

·         Microsoft CRM also has a programming interface that supports the development of custom:

o   Data entities

o   Screens

o   Reports

·         BizTalk and .NET are development environments and so directly support customisation

·         Exchange also supports customisation through its Web Service and Message Filter facilities and the ability to change the appearance of Outlook Web Access

An example of how an enterprise system in the electricity industry has been developed using the Microsoft Application Platform is covered below. This is of course based on a real example:

·         An Electronic Data Interchange (EDI) file of type D0300 arrives at an electricity supplier. The file is of type D0300 which is the format for “Disputed or Missing Readings on Change of Supplier”

·         BizTalk server:

o   Picks up the incoming messages and converts the EDI format into XML format

o   Executes a BizTalk Orchestration workflow that takes the XML message and carries out validations on the readings and meters it contains and updates state of the reading in the SQL Server database

o   Issues outgoing EDI messages to other market participants on the state of the disputed reads

o   Makes web service calls to the line-of-business CRM system to update the state of the customer account associated with the disputed read

·         Call Centre staff login to a SharePoint Portal and access the Disputed Reads page

o   This page contains a custom .NET Web part that accesses the disputed read tables in SQL Server via an Web Service calls to an Orchestration in BizTalk and allows the operator to raise new disputed reads and manage existing ones 

The above demonstrates the power of MAP. Windows provides the infrastructure. SQL Server provides the data platform. SharePoint provides the portal with authentication and security to manage permissions and BizTalk provides the message handling and workflow capabilities.

A full- blown enterprise level system with the minimal custom code.

Reporting by Exception with Data Alerts

Business Reports are a fact of life. They provide essential information on how an organisation is running and so on a regular basis reports are produced and studied.

SQL Server Reporting Services (SSRS) allows reports to be run on demand; a user selects a report, enters the parameters and the report is executed and displayed straight away. It also allows scheduled reports; a report and parameters are selected and a schedule of when it should be executed, say weekly, is determined and the report is executed and usually emailed to the user.

On demand and scheduled reporting facilities are all fine but the end result is a report that user has to read which can be time consuming particularly if there are a lot of reports. In many cases a considerable amount of time would be saved if the user only had to look at a report if some exceptional condition occurs, such as items reaching low stock levels, or the occurrence of loss making orders, or a change in market direction, etc. This is reporting by exception.

SQL Server 2012 Reporting Services introduces an reporting by exception facility called Data Alerts which allows conditions to be associated with the data stream that feeds the report. If the conditions are true it runs and delivers the report to the user. If the conditions are false it does nothing. So the user only gets to see the report if some interesting happens. Note that: The Data Alert conditions are independent of the report itself; so for example the a report could be set to run if stock levels drop below 10, but the report shows all products regardless of stock level.

A few points about Data Alerts:

1.       They are only part of SQL Server 2012 and later

2.       They only operate when SQL Server Reporting Services is in SharePoint Integrated mode

3.       The report has to be configured with a Data Source that uses Stored Credentials

Consider a simple example report that shows product stock levels. This has been deployed into a Reports library on a SharePoint 2010 site that is running SSRS 2012 integrated mode.

The report just displays stock levels and takes a single parameter the LocationID. The report below shows the stock for LocationID 3.

Now imagine that the user only wants to see this report if the stock is below 10 for any of the products.

Parameters have to be defined and the report has to be executed before a Data Alert can be created using the Action menu for the port. This is a requirement because alerts are dependent on the parameters.

A Silverlight control allows the conditions to be defined and a schedule for how often to check:

Once an alert has been created the Data Alerts menu for the report in SharePoint can be used to Edit, Delete and Run Data Alerts:

Whether the alert executes it checks the data against the conditions and if the is result is true it runs the report and emails it to the recipient. If the result is false the report is not executed, as in the example above. You can add multiple alerts for the same report and even the same combination of parameters. It’s as simple as that.

Master Data Services

Recently a client asked me the following question: What is Master Data Services, What is it for and what has it to do with SQL Server? I answered as succinctly as I could. It then occurred to me that the answer would make a good blog posting, as others may well be asking themselves the same question.
Master Data Services was first released with SQL Server 2008 R2. It is also part of SQL Server 2012, which adds some additional capabilities.

Master Data Services (MDS) is managed central data repository. It defines data schemas, applies business rules to validate the data and provides interfaces for external systems to use the data. Important to note is that it is not just a data dictionary it also stores the actual data.

Data can be versioned so that applications using the data can reference a particular version and are not forced to continually update to the latest version when the schema of the data changes.

Consider a simple example to put all this in to perspective: A centralised calendar to tracks the days when core business processes jobs run. MDS would contain:

·         Entity and attribute definitions to define the schema for the calendar.

·         Central business rules defining the validations to apply to the data. In this simple example there will be one validation rule that prevents calendar entries prior to 01/01/2012

·         The data for the calendar

·         The methods by which external applications can access the shared calendar data. In this case this will be a SQL Server database view, although other methods such as Web Services are allowed

To get started with MDS the first step is to create a database and the Master Data Manager web site for MDS. This is done using the Master Data Services Configuration Manager which comes as part of SQL Server. The resulting Master Data Manager web site is shown below:

The System Administration menu in the Master Data Manager web site is used to define:

·         A new Model called Calendar. This will hold the entity definitions and data

·         The JobType entity. This is a very simple entity that holds the Names of the types of jobs that are allowed: StockCheck, ProcessDirectDebits and IssueStatements

·         The Calendar Entity. Thus will consist primarily of a Name, Date and JobType

·         A business rule that prevents calendar entries before 01/01/2012

The various screens are easy to use. The one for defining the validation rule is:

The Explorer menu in the Master Data Manager web site is used to define the actual data. It is relatively straightforward to use, just pick an entity and add the data and apply the validation rules. The example below shows how one of the entries, the 30/04/1999 one, has failed the validation rule:

The last step is to use the Integration Management menu in the Master Data Manager to create the SQL Server views that will expose the Calendar for use by the external applications. In this case I created a view called CalendarView. The following SQL query uses the view to display the Calendar data in MDS:

SELECT *  FROM [MDS].[mdm].[CalendarView] ORDER BY Code

As well as the actual data in the entity, the view includes lots of other information, such as version number, entry date time, author,  and so on. Note that not all of the fields are shown in the above screen for space reasons.    

Admittedly lot of details have been left out of this simple example including: security, web service interfaces, APIs, interfaces to Excel, data imports, versioning and so on. However this information is readily available in the SQL Server Documentation and the MDS system as a whole is easy to use and manage.

So if MDS provides a service that you need then it should be relatively easy to get started.