Posted by: Karlo Bartels | 2012/01/09

SQL Server 2012 Virtual Labs

Virtual Labs enable you to quickly evaluate and test Microsoft’s newest products and technologies through a series of guided, hands-on labs that you can complete in 90 minutes or less.

There is no complex setup or installation required, and you can use Virtual Labs online immediately, free.

Click here to view the SQL Server 2012 Virtual Labs.

Posted by: Karlo Bartels | 2011/01/23

VBA for Analysis Services

Execute XMLA from VBA

Below you will find a code snippet that issues an XMLA command to an Analysis Server from an Excel workbook by using VBA. This specific example processes a dimension, but can easily be modified to process other Analysis Server objects as well.

The code connects to the Analysis Server by using an ADODB Connection object and the MSOLAP provider. The XMLA command is executed within an ADODB Command.  The code snippet accepts the Analysis Server name, the database ID (not the database name), the ID of the dimension you wish to process and the process type:

Sub ProcessDimension(ServerName As String, DatabaseID As String,
                     DimensionID As String, ProcessType As String)
Dim con As New ADODB.Connection
Dim xmlaCommand As New ADODB.Command
Dim xmlaCommandTemplate As String
On Error GoTo Err_Process
 
 'Create XMLA template (or load it from disk)
  xmlaCommandTemplate = _
  "<Batch xmlns=""http://schemas.microsoft.com/analysisservices/2003/engine"">" & _
    "<Parallel>" & _
      "<Process>" & _
        "<Object>" & _
          "<DatabaseID>" & DatabaseID & "</DatabaseID>" & _
          "<DimensionID>" & DimensionID & "</DimensionID>" & _
        "</Object>" & _
        "<Type>" & ProcessType & "</Type>" & _
        "<WriteBackTableCreation>UseExisting</WriteBackTableCreation>" & _
      "</Process>" & _
    "</Parallel>" & _
  "</Batch>"
 
  Application.StatusBar = "Please wait, processing dimension '" & DimensionID & "'..."
 
  'Open a connection to the Analysis Server and execute XMLA command
  With con
    .Open "Provider=MSOLAP.3;Data Source=" & ServerName & ";Integrated Security=SSPI;"
    With xmlaCommand
      .ActiveConnection = con
      .CommandTimeout = 120 'seconds
      .CommandText = xmlaCommandTemplate
      .Execute
    End With
    .Close
  End With
Err_Process_Exit:
  Application.StatusBar = ""
  Set con = Nothing
  Set xmlaCommand = Nothing
  Exit Sub
Err_Process:
  MsgBox "An error occured while processing dimension '" & DimensionID & _         "':" & vbCrLf & vbCrLf & Err.Description, vbExclamation
  GoTo Err_Process_Exit
End Sub
 

You can start the code as follows:

Sub Go()
  ProcessDimension "<Your server name", "<Your DB ID>", "<Your dimension ID>",
  "ProcessAdd"
End Sub
 

Do not forget to set a reference to the Microsoft ActiveX Data Object 2.8 library before trying the sample code.

Download the code here

XMLA Template Library

Instead of hard-coding the XMLA template, you could also create an XMLA template library on disc that contains templates with various process options and process types. The code would have to be modified to load a template file from disc into the xmlaCommandTemplate variable. This guarantees a more generic use of the code.

To easily generate an XMLA template, connect to your Analysis Server in SQL Server Management Studio, browse to the object for which you want to generate an XMLA template, right-click it and choose Process. In the dialog that appears select Script action to file to generate the template:

Create XMLA template

Posted by: Karlo Bartels | 2010/12/14

Batch Geocode Library

Geocoding is “the process of finding associated geographic coordinates (often expressed as latitude and longitude) from other geographic data, such as street addresses, or postal codes.” Reverse geocoding is the opposite process: find the street address of geographic coordinates.

To easily facilitate batch geocoding, i.e. processing large sets of geographic data, I created a batch geocode library that uses the Bing Spatial Data Services API. This API allows processing large sets of data by creating a Geocode Dataflow job on the server that handles the geocode request and returns the geocoded data.

To use the Geocode Dataflow API you need to:

  1. Create a Geocode job and upload the data
  2. Get the status of the geocode job
  3. Download the geocode job results

Each steps requires creating HttpWebRequest/HttpWebResponse objects to upload the requests and download the results.

GeoCode Library

The library I am presenting here does all the plumbing for you and can be easily incorporated into your own projects. It exposes two main classes:

  • GeoCodeJob: creates a batch geocode job based on an input file or a GeoCodeEntity array.
  • GeoCodeResult: retrieves the status of a geocode job and downloads the job results.

Next to the aforementioned classes, the library incorporates the classes available in the Spatial Data Services schema. This allows the library to work with and expose the Spatial Data Services classes. The following picture illustrates the relationships between the classes in the schema (click to enlarge):

geocodelib-1

GeoCodeJob

This class contains an overloaded method by the name of CreateJob. The method allows you to generate a geocode job from either a data file or a GeocodeEntity array. Either method returns the geocode job ID that you need in order to check the job status and download the results. Please note that before you can call any of these methods you need the specify a Bing Maps application ID. For information about getting a Bing Maps Key, see Getting a Bing Maps Key.

The following code snippet shows how a data file is processed:

// Read file contents, build URI, create job and get job ID
using (FileStream dataStream = File.OpenRead(dataFile))
{
  Uri requestURI = BuildRequestURI(inputFormat, jobDescription);
  HttpWebRequest request = BuildWebRequestFromStream(requestURI,
                           dataStream);
  jobID = GetJobID(request);
}
 

The BuildRequestURI method builds a request URI for the Spatial Data Services REST call, while the BuildWebRequestFromStream creates an HttpWebRequest based on the supplied stream and URI.

The GetJobID method is listed below:

private string GetJobID(HttpWebRequest request)
{
  string jobID = String.Empty;
  string locationHeader = String.Empty;
  using (HttpWebResponse response =
        (HttpWebResponse)request.GetResponse())
  {
    if (response.StatusCode != HttpStatusCode.Created)
      throw new WebException("Unexpected status code: " +
                             response.StatusCode.ToString());

    // Check for presence of Location header
    locationHeader = response.GetResponseHeader("Location");
    if (String.IsNullOrEmpty(locationHeader))
      throw new WebException
            ("Missing 'Location' header in the job response.");

    // Return job ID from Location header
    if (locationHeader.IndexOf("/") > 0)
      jobID = locationHeader.Substring
             (locationHeader.LastIndexOf("/") + 1);
    else
      throw new Exception
         ("Invalid 'Location' header: ID could not be parsed.");
  }
  return jobID;
}
 

The GetJobID method returns the geocode job ID (‘5bf10c37df944083b1879fbb0556e67e’ for example) that should be used to retrieve the job status and its results. The GeoCodeResult class contains methods to do just that. 

GeoCodeResult

This class contains the GetJobStatus and GetJobResponse methods to retrieve the process status of a geocode job and download the results. To perform these tasks, you could write code like this:

// Check job status
GeoCodeResult res = new GeoCodeResult();
res.BingMapsKey = _bingMapsKey;
GeoCodeJobStatus result = res.GetJobStatus(jobID);
if (result == GeoCodeJobStatus.Completed)
{
   // Job complete, get results
   DataflowJob job = res.JobDetails;
   GeocodeFeed feed = res.GetJobResponse(job);
}
 

The JobDetails property of the GeoCodeResult class returns a serialized DataflowJob that is received every time you call the GetJobStatus method. The following snippet shows the DataflowJob section of the job response:

<DataflowJob>
  <Id>5bf10c37df944083b1879fbb0556e67e</Id>
  <Link role="self">https://spatial.virtualearth.net
                   /REST/v1/dataflows/Geocode/
                   5bf10c37df944083b1879fbb0556e67e</Link>
  <Link role="output" name="succeeded">
             https://spatial.virtualearth.net/REST/v1/dataflows/
             Geocode/5bf10c37df944083b1879fbb0556e67e/
             output/succeeded</Link>
  <Description>Xml</Description>
  <Status>Completed</Status>
  <CreatedDate>2010-05-10T13:22:35.0553408-07:00</CreatedDate>
  <CompletedDate>2010-05-10T13:23:49.1959658-07:00</CompletedDate>
  <TotalEntityCount>12</TotalEntityCount>
  <ProcessedEntityCount>12</ProcessedEntityCount>
  <FailedEntityCount>0</FailedEntityCount>
</DataflowJob>
 

As soon as the geocode job has completed, a Link node will appear in the DataflowJob that contains the download URL. The results can be downloaded by passing a DataflowJob object to the GetJobResponse method. The following code snippet shows how:

HttpWebRequest request = (HttpWebRequest)
                         WebRequest.Create(uriBuilder.Uri);
request.ContentType = "application/xml";
using (HttpWebResponse response =
      (HttpWebResponse)request.GetResponse())
{
  if (response.StatusCode == HttpStatusCode.OK)
  {
    using (StreamReader reader = new StreamReader
          (response.GetResponseStream()))
    {
      // Serialize XmlStream into a GeocodeFeed object
      XmlSerializer serializer = new XmlSerializer
                                 typeof(GeocodeFeed));
      feed = (GeocodeFeed)serializer.Deserialize(reader);
    }
  }
  else
  {
    throw new WebException("Unexpected status code: " +
                            response.StatusCode.ToString());
  }
}
 

The GetJobResponse method deserializes the XML response available at the download URL in the DataflowJob as a GeocodeFeed object. This object is the top-level object in the Spatial Data Services schema (see illustration in the diagram above).

Next, you can use the GeocodeFeed object to programmatically retrieve the Geocode job results like so:

GeocodeResponse response = feed.GeocodeEntity[0].GeocodeResponse;
MessageBox.Show("Lat/long coordinates of the first item: " +
  response.InterpolatedLocation.Latitude.ToString() + " " +
  response.InterpolatedLocation.Longitude.ToString());
 

or serialize it back to XML:

using (FileStream fs = new FileStream("geocode result.xml",
                           FileMode.OpenOrCreate))
{
  XmlSerializer serializer = new XmlSerializer
                            (typeof(GeocodeFeed));
  serializer.Serialize(fs, feed);
}
 

Wrap-up

As you can see, the Batch Geocode Library allows you to perform all tasks necessary to start batch geocoding your data without too much trouble. To get started quickly, I including a small test application:

test application

Simply download the source code here, open the Main form, view the code and enter your Bing Maps application ID in the top line:

private string _bingMapsKey = "<Your Bing Maps application ID";
 

… and press F5 to run the application.

Happy geocoding!

Posted by: Karlo Bartels | 2010/11/04

Excel Spatial Visualizer Add-In

Excel Spatial Visualizer

Inspired by the Excel Spatial Spreadsheet blog articles by Olivier Meyer, I decided to create an add-in that provides functionality to visualize spatial (geographic) data in Excel. The screenshot on the right displays the end result.

 

Spatial analysis

The aforementioned articles describe how to build an Excel add-in to perform spatial analysis in .NET. An Excel user could enter a formula like:

=PARSEANDLOCATE("One Microsoft Way, Redmond WA")

and Excel would in turn return the coordinates of that location

POINT(47.639747 -122.129731)

This allows an Excel user to easily transform addresses into coordinates which can be used as input for spatial analysis scenarios, like finding the region in which a certain product is most often sold.

Displaying spatial objects

To easily facilitate the process of displaying spatial objects in Excel, I created an Excel add-in that allows you to select a range of cells containing coordinates and show them on a map. To do this, the add-in accesses the Bing Maps SOAP Services. This is a set of programmable SOAP services that allow you to match addresses to a map (and much more). You will need a Bing Maps Application ID to access these services.

To demonstrate, let’s display Mediamarkt stores in The Netherlands on a map. First, open the Excel workbook containing the coordinates (which I geocoded by hand using the store addresses):

xl2010geoaddin-1

Select the type of map (road or aerial) and the type of marker on the Geo Visualizer ribbon and click the Launch Geo Visualizer button to display the Visualizer window.

xl2010geoaddin-2

Click the Range selection button to select the range that contains the coordinates.

xl2010geoaddin-3

Click the expand button on the range selection control to parse the coordinates and display the map with the selected coordinates. Note that you can use the slider on the right of the map to zoom in or out.

xl2010geoaddin-4

You can save the map to disc or copy it to the clipboard by right-clicking the map (or use the toolbar buttons to perform these tasks).

Routes and optional POIs

To display a route on the map, i.e. to draw a line which connects the individual coordinates, click the Draw route button on the ribbon and optionally select the route color and route width.

xl2010geoaddin-5

The Draw route option treats all coordinates in the selected range as both route markers and route segments. The screenshot below illustrates this.

xl2010geoaddin-6

In some situations (like the one shown above) this is not the desired output; simply add a third column to the coordinate range to specify how to treat the coordinate, either as a POI or as part of a route. Enter TRUE in the cell of the third column to treat that location as POI, or FALSE to treat it as part of a route. The next screenshot shows the proper output of my city tour in Paris.

xl2010geoaddin-7

Map options

The map settings dialog allows you to fine-tune the map and its POI.

xl2010geoaddin-8

Use the Offset X and Offset Y properties to correct the position of the POI markers. Due to the shape of the POI marker it sometimes looks like it is placed incorrectly. Change the value of the Offset X and Offset Y properties to correct the placement of the markers on the map in either horizontal or vertical direction.

The Map options section offers additional customization and usability options.

The code

The add-in was created by using the Excel 2010 Add-in template in Visual Studio, which I extended with my own classes and a Bing Maps Service Reference that retrieves the map images. The next image shows the solution structure in Class View.

xl2010geoaddin-9

Note that I used the VS.NET Refedit control in my add-in project which allows easy Excel range selection from a Windows Form.

The following classes are the workhorses of the application:

  • BingMaps – This class interacts with the Bing Maps SOAP Services. It creates the necessary WCF service clients, retrieves map data and adds points of interest and routes to the map image.
  • Map – This is an Excel Bing Maps wrapper class that abstracts the Bing Maps code away from the Excel add-in by providing a simple GetMap() method. Includes an Excel Range parser method that converts cells in an Excel Range object into Coordinate objects. This class also contains a local map cache.
  • Shared – Includes generic bitmap functions like downloading image data and loading byte arrays into Bitmap objects.
  • Visualizer – main Visualizer window.

The biggest challenge in programming this add-in was converting geographic coordinates (from degrees) into pixel coordinates (x,y). This is necessary to determine the proper location of the POI icons and route segments on the map Bitmap object. Refer to this Bing Maps Tile System article for an overview of the necessary formulas.

Based on that article, I created the LatLongToPixel method that accepts a coordinate, the size of the map image, the current zoom level and map center point. It returns a Point object that contains the pixel coordinate of the passed coordinate:

/// <summary>
/// Converts a Latitude/Longitude coordinate to a pixel 
    coordinate of a location based on the current map view.
/// </summary>
/// <param name="latlong">The coordinate to convert.</param>
/// <param name="view">A BestView object of the current view.</>
/// <param name="mapWidth">The width of the map image.</param>
/// <param name="mapHeight">The height of the map image.</param>
/// <returns>A Point object containing the pixel coordinate of 
    the input location.</returns>
 
private Point LatLongToPixel(Location latlong, BestView view,
                             int mapWidth, int mapHeight)
{
  // Calcuate pixel coordinates of map center point
  double sinLatitudeCenter =
         Math.Sin(view.Center.Latitude * Math.PI / 180);
  double pixelXCenter = ((view.Center.Longitude + 180) / 360)
         * 256 * Math.Pow(2, view.ZoomLevel);
  double pixelYCenter = (0.5 - Math.Log((1 + sinLatitudeCenter)
         / (1 - sinLatitudeCenter)) / (4 * Math.PI))
         * 256 * Math.Pow(2, view.ZoomLevel);

  // Calculate pixel coordinate of location
  double sinLatitude =
         Math.Sin(latlong.Latitude * Math.PI / 180);
  double pixelX = ((latlong.Longitude + 180) / 360)
         * 256 * Math.Pow(2, view.ZoomLevel);
  double pixelY = (0.5 - Math.Log((1 + sinLatitude)
         / (1 - sinLatitude)) / (4 * Math.PI))
         * 256 * Math.Pow(2, view.ZoomLevel);

  // Calculate top left corner pixel coordiates of map image
  double topLeftPixelX = pixelXCenter - (mapWidth / 2);
  double topLeftPixelY = pixelYCenter - (mapHeight / 2);

  // Calculate relative pixel cooridnates of location
  double x = pixelX - topLeftPixelX;
  double y = pixelY - topLeftPixelY;
  return new Point((int)Math.Floor(x), (int)Math.Floor(y));
}

Getting started

Start by downloading the code (see below for download link) and build the solution. The add-in is written in C# (.NET version 4.0) and is intended to be used with Excel 2010. To build the code you should use Visual Studio 2010.

To install the add-in, double-click the BartelsOnline.GeoAddIn.vsto deployment manifest file. If the installation was successful, the Geo Visualizer ribbon will appear the next time you start Excel.

Click the Settings button on the Geo Visualizer ribbon and enter your Bing Maps Application ID in the Bing Maps section. Click OK and you are ready to start visualizing!

xl2010geoaddin-10

Download

Download source code (includes sample spatial data Excel workbook)

Posted by: Karlo Bartels | 2010/07/21

Passing NULL in a Report Parameter

While creating a dashboard report in SSRS, I ran into the issue of passing a NULL value for a specific report parameter. This nullable parameter operates as follows: either filter the report dataset on the supplied values or do not filter at all. Much like the Error List window in Visual Studio, which features Errors, Warnings and/or Informational messages. Clicking a category removes the filter for that category or adds it. No selection means: “Show me all messages”.

The same goes for the report parameter I added, in this case a Boolean field which allows a filter for the underlying dataset. The report user either selects True, False or <Do not filter>, which comes down to a NULL value.

The dashboard report itself features a collection of textboxes with the names of the target reports. Their Action properties are set to Go to URL. Instead of a hard-coded URL, I used the ReportServerUrl and ReportFolder properties of the Globals object to construct the URL:

=Globals!ReportServerUrl & "?" & Globals!ReportFolder & "/MyDashboard"

This flexible approach allows you to publish the dashboard and its underlying reports to other servers without having to update the URLs in the textboxes.

If the target report contains parameters you will have to add those to the URL directly. You can use the report’s Parameters collection to pass the value of a report parameter (e.g. Period) to the underlying report as follows:

=Globals!ReportServerUrl & "?" & Globals!ReportFolder & "/MyDashboard&Period=" & Parameters!Period.Value

But what if the report parameter is NULLable? Simply leaving out the value will not do the trick since the report engine will interpret the empty parameter as an empty string. Omitting the parameter altogether is the answer. Continuing with the example above, this is what it would look like for a NULLable parameter called User:

=Globals!ReportServerUrl & "?" & Globals!ReportFolder & "/MyDashboard&Period=" & Parameters!Period.Value & IIF(IsNothing(Parameters!User.Value), "", "&User=" & Parameters!User.Value)

Posted by: Karlo Bartels | 2010/04/16

Introducing SQL Server 2008 R2

Microsoft Press just released a free e-book called Introducing Microsoft SQL Server 2008 R2. This book is “for anyone who has an interest in SQL Server 2008 R2 and wants to understand its capabilities”.

The book contains 10 chapters and 216 pages, like so:

PART I   Database Administration

CHAPTER 1   SQL Server 2008 R2 Editions and Enhancements
CHAPTER 2   Multi-Server Administration
CHAPTER 3   Data-Tier Applications
CHAPTER 4   High Availability and Virtualization Enhancements
CHAPTER 5   Consolidation and Monitoring

PART II   Business Intelligence Development

CHAPTER 6   Scalable Data Warehousing
CHAPTER 7   Master Data Services
CHAPTER 8   Complex Event Processing with StreamInsight
CHAPTER 9   Reporting Services Enhancements
CHAPTER 10   Self-Service Analysis with PowerPivot

You can download it here

Posted by: Karlo Bartels | 2009/11/03

Suspending package validation

Once in a while I deal with very large SSIS packages. Opening such packages in Visual Studio takes a considerable amount of time, because the package is immediately validated upon opening. Very annoying if all you want to do is peek at a specific setting or connection…

The bad news is that there is no built-in method in Visual Studio to suspend package validation. The good news is, however, that you can speed-up the process by working offline, which will stop SSIS from validating the metadata associated with data flow sources and destinations.

Follow these steps to quickly open large packages:

  1. Open the SSIS solution in Visual Studio, but do not open any package yet.
  2. In the SSIS toolbar, click Work Offline.
  3. Open a package.

As a result of these steps validation will be suspended until you disable the Work Offline option.

Please note that each dataflow in the package will now report an error (validation errors occurred). These errors will disappear if you disable the Work Offline option and build the package (or close and re-open it).

Posted by: Karlo Bartels | 2009/10/19

Blog list

Todd McDermid shares a couple of reasons to maintain an updated blog roll and includes this blog in his blog listing. Thanks Todd! I will update my blog roll with some of the suggestions you made. Great job.

Posted by: Karlo Bartels | 2009/07/09

SSIS file/folder variables

Retrieving file and folder names from fully qualified file paths

Sounds easy enough, doesn’t it? Well, it took me some time to simply return the file name from a file path that was handed to me by a Foreach File Loop container in a SSIS package.

I mapped a variable called ImportFile to the container’s collection value, which contains a fully qualified file name, e.g. C:\SSIS\My Data\My File.txt. How would you extract the file or folder name from this variable? Let’s see how I did it:

  • Add a Derived Column Transformation component to your data flow and add columns which will hold the values for Folder and FileName 
  • Use the following expression to extract the file name for the FileName column:

REVERSE(SUBSTRING(REVERSE(@[User::ImportFile]), 1,
FINDSTRING(REVERSE(@[User::ImportFile]), "\\", 1) - 1))

  • Use this expression to extract the folder name for the Folder column:

REVERSE(SUBSTRING(REVERSE(@[User::ImportFile])
FINDSTRING(REVERSE(@[User::ImportFile]), "\\", 1) + 1, 230))

Indeed, SSIS is missing the LastIndexOf String function, which means I had to reverse the string to be able to find the final backslash in the path. After that, it’s just a matter of returning the part before or after the backslash and reversing everything again. I hope these expressions will save you some time during SSIS package development!

Posted by: Karlo Bartels | 2009/05/20

Determine job status

Today I created a stored procedure to find out if a particular SQL Server Job was running or not. I needed some kind of polling mechanism for a web site that enables administrators to start a SQL Server job by simply clicking a button on a web page.

The web page features a AJAX Timer class, which allows you to perform asynchronous postbacks at a defined interval. The timer is enabled when the user clicks the button. The timer code executes the IsJobRunning stored procedure, which returns True if it is currently running, or False if it is not. The timer keeps executing the job at the specified interval. As soon as the job finishes, the web page is updated with the job result and the timer is halted.

So, without further ado, here is the code:

CREATE PROCEDURE [dbo].[IsJobRunning]
(
  
@JobName SYSNAME
)
AS
BEGIN
   SET NOCOUNT ON
;
  
DECLARE @Job SYSNAME;
  
DECLARE @JobIsRunning bit;
      
  
SELECT  @Job = SJ.name
  
FROM    msdb.dbo.sysjobs AS SJ INNER JOIN msdb.dbo.sysjobactivity AS SJA
  
ON      SJ.job_id = SJA.job_id
  
WHERE   SJ.name = @JobName
          
AND SJA.start_execution_date IS NOT NULL
           AND
SJA.stop_execution_date IS NULL;
  
  
IF @Job IS NULL
      
SELECT 0 AS JobIsRunning;
  
ELSE
       SELECT
1 AS JobIsRunning;
      
END

Older Posts »

Categories