Have a Very Spatial Holiday

When I was a kid, we would cut Christmas trees and snowflakes out of construction paper and tape them to the windows at school.  Of course, today we must use databases and code!

imageimage

I’ve been doing some map reporting for a client lately with Reporting Services and SQL Server Spatial queries.  In the Holiday season, just for fun we were volleying holiday themed queries between friends in the SQL Server community.  Arnie Rowland replied with this query that draws a Christmas tree.  To view it, paste this query into the SSMS query window and run it against any edition of SQL Server 2008 or newer.  Note that the ornaments on the tree are placed at random.

SELECT geometry::Parse(‘POLYGON((
4 0, 0 0, 3 2, 1 2, 3 4, 1 4, 3 6, 2 6,
4 8, 6 6, 5 6, 7 4, 5 4, 7 2, 5 2, 8 0, 4 0
))’)
UNION ALL
SELECT geometry::Parse(‘POLYGON((
2.5 0, 3 -1, 5 -1, 5.5 0, 2.5 0
))’)
UNION ALL
SELECT geometry::Parse(‘POLYGON((
4 7.5, 3.5 7.25, 3.6 7.9, 3.1 8.2, 3.8 8.2,
4 8.9, 4.2 8.2, 4.9 8.2, 4.4 7.9, 4.5 7.25, 4 7.5
))’)
UNION ALL
SELECT geometry::Point(
RAND(CHECKSUM(NEWID())) * 5 +1.5,
RAND(CHECKSUM(NEWID())) * 6, 0
).STBuffer(0.3)
FROM master.dbo.spt_values
WHERE type = ‘P’ AND number BETWEEN 1 AND 20;

Not to be outdone, Itzik Ben-Gan replied with this query which uses fractal math to generate a Koch Snowflake:

SET NOCOUNT ON;
USE tempdb;

— Definition of GetEndPoint table function
IF OBJECT_ID(‘dbo.GetEndPoint’) IS NOT NULL
  DROP FUNCTION dbo.GetEndPoint;
GO

CREATE FUNCTION dbo.GetEndPoint
(
  @x     AS FLOAT,
  @y     AS FLOAT,
  @dist  AS FLOAT,
  @angle AS FLOAT
) RETURNS TABLE
AS
RETURN
  SELECT @x + @dist * COS(PI()*@angle/180) AS x, @y + @dist * SIN(PI()*@angle/180) AS y;
GO

— Definition of KochSnowflakeColored table function
IF OBJECT_ID(‘dbo.KochSnowflakeColored’) IS NOT NULL
  DROP FUNCTION dbo.KochSnowflakeColored;
GO

CREATE FUNCTION dbo.KochSnowflakeColored
(
  @iterations AS INT
) RETURNS TABLE
AS
RETURN
  WITH Koch(iteration, length, angle, x0, y0, x1, y1) AS
  (

  — anchor members

  — side b 
  SELECT
    1 AS iteration, 100E0 as length, 60E0 AS angle,
    0E0 AS x0, 0E0 AS y0, x as x1, y as y1
  FROM dbo.GetEndPoint(0E0, 0E0, 100E0, 60E0) AS P

  UNION ALL

  — side a
  SELECT
    1 AS iteration, 100E0 as length, 300E0 AS angle,
    x AS x0, y AS y0, 100E0 AS x1, 0E0 AS y1
  FROM dbo.GetEndPoint(0E0, 0E0, 100E0, 60E0) AS P

  UNION ALL

  — side c
  SELECT
    1 AS iteration, 100E0 as length, 180E0 AS angle,
    100E0 AS x0, 0E0 AS y0, 0E0 AS x1, 0E0 AS y1

  UNION ALL

  — recursive members

  — segment 1
  SELECT
    P.iteration + 1 AS iteration, A1.length, A1.angle,
    P.x0, P.y0, A2.x AS x1, A2.y AS y1
  FROM Koch AS P
    CROSS APPLY (SELECT P.length / 3 AS length, P.angle AS angle) AS A1
    CROSS APPLY dbo.GetEndPoint(P.x0, P.y0, A1.length, A1.angle) AS A2
  WHERE P.iteration < @iterations

  UNION ALL

  — segment 2
  SELECT
    P.iteration + 1 AS iteration, A1.length, A1.angle,
    A2.x AS x0, A2.y AS y0, A3.x AS x1, A3.y AS y1
  FROM Koch AS P
    CROSS APPLY (SELECT P.length / 3 AS length,
                  P.angle – CAST(P.angle AS INT) + CAST(P.angle + 60E0 AS INT) % 360 AS angle) AS A1
    CROSS APPLY dbo.GetEndPoint(P.x0, P.y0, A1.length, P.angle) AS A2
    CROSS APPLY dbo.GetEndPoint(A2.x, A2.y, A1.length, A1.angle) AS A3
  WHERE P.iteration < @iterations

  UNION ALL

  — segment 3
  SELECT
    P.iteration + 1 AS iteration, A1.length, A1.angle,
    A3.x AS x0, A3.y AS y0, A2.x AS x1, A2.y AS y1
  FROM Koch AS P
    CROSS APPLY (SELECT P.length / 3 AS length,
                  P.angle – CAST(P.angle AS INT) + CAST(P.angle + 300E0 AS INT) % 360 AS angle) AS A1
    CROSS APPLY dbo.GetEndPoint(P.x0, P.y0, A1.length * 2, P.angle) AS A2
    CROSS APPLY dbo.GetEndPoint(A2.x, A2.y, A1.length, A1.angle + 180E0) AS A3
  WHERE P.iteration < @iterations

  UNION ALL

  — segment 4
  SELECT
    P.iteration + 1 AS iteration, A1.length, A1.angle,
    A2.x AS x0, A2.y AS y0, P.x1, P.y1
  FROM Koch AS P
    CROSS APPLY (SELECT P.length / 3 AS length, P.angle AS angle) AS A1
    CROSS APPLY dbo.GetEndPoint(P.x0, P.y0, A1.length * 2, A1.angle) AS A2
  WHERE P.iteration < @iterations
  )
  SELECT
    GEOMETRY::STLineFromText(‘LINESTRING(‘
      + STR(x0, 12, 9) + ‘ ‘
      + STR(y0, 12, 9) + ‘,’
      + STR(x1, 12, 9) + ‘ ‘
      + STR(y1, 12, 9) + ‘ ‘
      + ‘)’, 0) AS line
  FROM Koch
  WHERE iteration = @iterations;
GO

— Merry Christmas
SELECT * FROM dbo.KochSnowflakeColored(6);

My point in making this post was to say “Merry Christmas” and “Happy Holidays” using the tools that we all know and love.  As a side note, there are two articles on SQL Server Magazine that describe the techniques used to create the snowflake:

http://sqlmag.com/t-sql/ctes-multiple-recursive-members
http://sqlmag.com/t-sql/ctes-multiple-recursive-members-part-2

Digg This

Reporting Services & MDX Queries – Video Tutorials

Working with multiple clients lately, mentoring report development on SSAS (mostly Tabular and some multidimensional), this prompted me to create these tutorials to provide some training and follow-on support.

Reporting Services can be a great tool for designing reports using data in an Analysis Services database with MDX queries.  MDX works with either a multidimensional model (containing cubes & dimensions) or a Tabular model using the xVelocity in-memory aggregation engine.  Basic MDX queries can be fairly easy but graduating beyond simple design can be like opening Pandora’s Box unless you really know what you’re doing.

I’ve recorded a set of video tutorials that begin with basic MDX created using the graphical MDX Query Designer and then move to hand-written MDX queries and more advanced features.  Each tutorial is about 24 minutes in length.  The focus of these presentations is to show you how to use MDX in Reporting Services and doesn’t cover MDX language essentials.  If you need help getting started with the MDX query language, I recommend that you view Chris Webb’s MDX training on Project Botticelli.

These tutorials include:

1.Using the MDX Query Builder to generate queries & parameters

  • Examining the MDX query
  • Understanding parameter mapping

2.Hand writing MDX Queries

  • Adding parameters
  • Creating parameter list queries
  • Manual parameter mapping

3.Enhancing a Report

  • Creating cascading parameters
  • Updating a hand-written MDX query

These examples use the SSAS multidimensional sample database.  You can download the AdventureWorks Multidimensional Models SQL Server 2012 databases from the CodePlex site here.  Follow the directions to install/restore the relational data mart, the SSAS multidimensional project and then process the database from SSDT.  In full screen view, use the HD option.

Tutorial 1 – Using the MDX Query Builder to generate queries & parameters

Tutorial 2 – Hand writing MDX Queries

Tutorial 3 – Enhancing a Report

Digg This