SQL 2012 - MySemanticSearch Demo with Tag Clouds
01 November 11 12:28 AM | sqlartist | 1 comment(s)

 

Excellent demonstration of the new SQL Server 2012 Semantic Search feature available at http://mysemanticsearch.codeplex.com

Just tried it out on a large Business Intelligence related Microsoft Word collection and also the health related DMOZ collection of html files discussed in my previous posts.

I have included some screenshots below of each document collection. I have realised that the Tag Cloud may need to be a bit more configurable based on the results of any search term.

 

Business Intelligence related Word documents.

DMOZ-Health HTML documents.

Filed under: , ,
Denali CTP3 - Semantic Search 2 (Lots of documents)
19 October 11 02:18 AM | sqlartist | with no comments

Hi again, I thought I would improve on the previous post by actually putting a decent about of content into the Filetable - this time I used the opensource DMOZ Health document repository which contains 5,880 files inside 220 folders. The files are all html and are pretty small in size. The entire document collection is about 120Mb unzipped and 30Mb zipped. If any one is interested in testing this collection drop me a note and I will upload the dmoz_health repository archive to Skydrive.

This time I included the SET STATISTICS_IO ON data to show how well the engine is performing with this new feature. I will investigate more over the next few weeks and really begin to understand the impact of huge document collections stored in SQL Server 2012 and also some best practices on what, if any are the limitations of this feature.

EXECUTE xp_cmdshell 'md C:\dmoz_health', no_output
GO

SELECT * FROM sys.fulltext_semantic_languages
GO

create database dmoz_health
    on primary (name = dmoz_health_File, filename = N'C:\dmoz_health\dmoz_health_File.mdf'),
    filegroup SQLStorage
        contains filestream
            (name = dmoz_health_FS_File, filename = N'C:\dmoz_health\dmoz_health_FS')
        with filestream
            (non_transacted_access = full, directory_name = N'dmoz_health Articles')
GO

CREATE TABLE dmoz_health..Documents AS FileTable WITH (filetable_directory = N'Document Library')
GO

-- Then Load Office 2010 Filters

USE dmoz_health
GO
EXEC sp_fulltext_service 'load_os_resources',1
EXEC sp_fulltext_service 'restart_all_fdhosts'
CREATE FULLTEXT CATALOG ft AS DEFAULT
GO
CREATE UNIQUE INDEX DocumentsFt ON dmoz_health..Documents(stream_id)
GO
CREATE SEARCH PROPERTY LIST DocumentProperties;
GO
ALTER SEARCH PROPERTY LIST DocumentProperties
   ADD 'Title'
   WITH ( PROPERTY_SET_GUID = 'F29F85E0-4FF9-1068-AB91-08002B27B3D9', PROPERTY_INT_ID = 2,
      PROPERTY_DESCRIPTION = 'System.Title - Title of the item.');
ALTER SEARCH PROPERTY LIST DocumentProperties
    ADD 'Author'
   WITH ( PROPERTY_SET_GUID = 'F29F85E0-4FF9-1068-AB91-08002B27B3D9', PROPERTY_INT_ID = 4,
      PROPERTY_DESCRIPTION = 'System.Author - Author or authors of the item.');
ALTER SEARCH PROPERTY LIST DocumentProperties
    ADD 'Tags'
   WITH ( PROPERTY_SET_GUID = 'F29F85E0-4FF9-1068-AB91-08002B27B3D9', PROPERTY_INT_ID = 5,
      PROPERTY_DESCRIPTION = 'System.Keywords - Keywords (Tags) of the item.');
GO
CREATE FULLTEXT INDEX ON dmoz_health..Documents
    (file_stream TYPE COLUMN file_type LANGUAGE 1033 statistical_semantics)
    KEY INDEX DocumentsFt ON ft
    WITH SEARCH PROPERTY LIST = DocumentProperties
GO

-- Find path of Documents

USE dmoz_health
GO
SELECT FileTableRootPath() AS 'FileTable Root Path'
GO

-- Right-click the first returned row and select Copy.
/*
4.  Open a Windows Explorer window, paste the Document Library path to the address bar, and press Enter. Then double-click the Document Library folder.
5.  Open another Windows Explorer window, browse to the dmoz_health folder extracted from the dmoz_health.zip file (will upload this to Skydrive if requested).
6.  Select all files and folders in the dmoz_health folder, and drag them to the Document Library folder, while pressing Ctrl.
*/

-- \\KIWI\MSSQLSERVER\dmoz_health Articles
-- Check all files are in the table

SELECT stream_id, name, file_type,
(SELECT REPLACE(N'File:\\'+FileTableRootPath()+file_stream.GetFileNamespacePath(), N' ', N'%20')
FROM dmoz_health..Documents T2
WHERE T2.stream_id = T1.stream_id FOR XML PATH(''), TYPE) AS FilePath,
    creation_Time, last_write_time, last_access_time, is_directory, is_offline,
    is_hidden, is_readonly, is_archive, is_system, is_temporary
        FROM dmoz_health..Documents T1
        ORDER BY name ASC
GO
-- 6104 rows
/*
Table 'Documents'. Scan count 0, logical reads 36, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 2, logical reads 93, physical reads 0, read-ahead reads 0, lob logical reads 24, lob physical reads 0, lob read-ahead reads 0.
Table 'Documents'. Scan count 0, logical reads 36, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
etc...
(6104 row(s) affected)
Table 'Documents'. Scan count 1, logical reads 18699, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

*/


-- Querying table
/*
SELECT column_name FROM table_name
WHERE CONTAINS (PROPERTY (column_name,'property_name'),'')
*/

USE dmoz_health
GO
SELECT name DocumentName, file_stream.GetFileNamespacePath() Path FROM Documents
    WHERE CONTAINS(PROPERTY(file_stream, 'Title'), 'migraine OR headache')
GO
-- 55 rows

SELECT name DocumentName, file_stream.GetFileNamespacePath() Path FROM Documents
    WHERE CONTAINS(file_stream, 'NEAR(("migraine", "headache"), 5, FALSE)')
GO
-- 60 rows
/*
Table 'Documents'. Scan count 0, logical reads 28, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 2, logical reads 69, physical reads 0, read-ahead reads 0, lob logical reads 16, lob physical reads 0, lob read-ahead reads 0.
Table 'Documents'. Scan count 0, logical reads 28, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
etc...

(60 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Documents'. Scan count 1, logical reads 387, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'fulltext_index_docidmap_245575913'. Scan count 0, logical reads 132, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

*/


SELECT name, document_key, keyphrase, score
    FROM semantickeyphrasetable(Documents, *)
  INNER JOIN Documents ON stream_id = document_key
    ORDER BY name, score DESC

GO
-- 469852 rows
/*
(469852 row(s) affected)
Table 'fulltext_index_docidmap_245575913'. Scan count 3, logical reads 70, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Documents'. Scan count 3, logical reads 387, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ti_245575913'. Scan count 3, logical reads 2391, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

*/



SELECT KeyPhrase, AVG(score) AS score FROM semantickeyphrasetable(Documents, *)
    INNER JOIN Documents ON stream_id = document_key
    GROUP BY KeyPhrase ORDER BY score DESC
GO
-- 46028 rows

DECLARE @Title as NVARCHAR(1000)
DECLARE @DocID as UNIQUEIDENTIFIER

SET @Title = '996621.htm'

SELECT @DocID = stream_id FROM Documents WHERE name = @Title

SELECT name, document_key, keyphrase, score
    FROM semantickeyphrasetable(Documents, *, @DocID)
    INNER JOIN Documents ON stream_id = document_key
    ORDER BY name, score DESC
GO
-- 100 rows
/*
Table 'Documents'. Scan count 1, logical reads 246, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(100 row(s) affected)
Table 'ti_245575913'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'fulltext_index_docidmap_245575913'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Documents'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

*/


DECLARE @Title as NVARCHAR(1000)
DECLARE @DocID as UNIQUEIDENTIFIER

SET @Title = '996578.htm'

SELECT @DocID = stream_id FROM Documents WHERE name = @Title

SELECT @Title AS SourceTitle, name AS MatchedTitle, stream_id, score
    FROM semanticsimilaritytable(Documents, *, @DocID)
    INNER JOIN Documents ON stream_id = matched_document_key
    ORDER BY score DESC
GO
-- 10 rows
/*
Table 'Documents'. Scan count 1, logical reads 246, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(10 row(s) affected)
Table 'Documents'. Scan count 0, logical reads 30, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'fulltext_index_docidmap_245575913'. Scan count 0, logical reads 22, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'dsi_245575913'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

*/

USE dmoz_health
DECLARE @SourceTitle as NVARCHAR(1000)
DECLARE @MatchedTitle as NVARCHAR(1000)
DECLARE @SourceDocID as UNIQUEIDENTIFIER
DECLARE @MatchedDocID as UNIQUEIDENTIFIER

SET @SourceTitle = '996578.htm'
SET @MatchedTitle = '996603.htm'

SELECT @SourceDocID = stream_id FROM Documents WHERE name = @SourceTitle
SELECT @MatchedDocID = stream_id FROM Documents WHERE name = @MatchedTitle

SELECT @SourceTitle AS SourceTitle, @MatchedTitle AS MatchedTitle, keyphrase, score
    FROM SEMANTICSIMILARITYDETAILSTABLE(Documents, file_stream, @SourceDocID, file_stream, @MatchedDocID)
    ORDER BY score DESC
GO
-- 24 rows
/*
Table 'Documents'. Scan count 1, logical reads 246, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Documents'. Scan count 1, logical reads 246, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(24 row(s) affected)
Table 'ti_245575913'. Scan count 101, logical reads 505, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'fulltext_index_docidmap_245575913'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
*/

Filed under: , , , ,
Denali CTP3 - Semantic Search features
18 October 11 02:59 AM | sqlartist | 1 comment(s)

Long time since I posted but the last post I did mentioned the Semantic Search feature potentially coming in SQL Server. Well its finally here and I finally got to test it out on the Denali CTP3 build. It is not feature complete and only allows Key terms to be analysed - the holy grail for text indexing or search engine developers would be key terms and key phrases - that really then opens up the world of text indexing and un structured information analysis.

I followed the tutorial included in the recently released SQL Server 2012 Developer Training Kit but to save anyone the time of copy and pasting I have included the SQL code from that tutorial. I hope to test this out on some decent document collections very soon.

EXECUTE xp_cmdshell 'md C:\SQLServerArticles', no_output
GO

SELECT * FROM sys.fulltext_semantic_languages
GO

create database SQLServerArticles
    on primary (name = SQLServerArticles_File, filename = N'C:\SQLServerArticles\SQLServerArticles_File.mdf'),
    filegroup SQLStorage
        contains filestream
            (name = SQLServerArticles_FS_File, filename = N'C:\SQLServerArticles\SQLServerArticles_FS')
        with filestream
            (non_transacted_access = full, directory_name = N'SQLServer Articles')
GO

CREATE TABLE SQLServerArticles..Documents AS FileTable WITH (filetable_directory = N'Document Library')
GO

-- Then Load Office 2010 Filters

USE SQLServerArticles
GO
EXEC sp_fulltext_service 'load_os_resources',1
EXEC sp_fulltext_service 'restart_all_fdhosts'
CREATE FULLTEXT CATALOG ft AS DEFAULT
GO
CREATE UNIQUE INDEX DocumentsFt ON SQLServerArticles..Documents(stream_id)
GO
CREATE SEARCH PROPERTY LIST DocumentProperties;
GO
ALTER SEARCH PROPERTY LIST DocumentProperties
   ADD 'Title'
   WITH ( PROPERTY_SET_GUID = 'F29F85E0-4FF9-1068-AB91-08002B27B3D9', PROPERTY_INT_ID = 2,
      PROPERTY_DESCRIPTION = 'System.Title - Title of the item.');
ALTER SEARCH PROPERTY LIST DocumentProperties
    ADD 'Author'
   WITH ( PROPERTY_SET_GUID = 'F29F85E0-4FF9-1068-AB91-08002B27B3D9', PROPERTY_INT_ID = 4,
      PROPERTY_DESCRIPTION = 'System.Author - Author or authors of the item.');
ALTER SEARCH PROPERTY LIST DocumentProperties
    ADD 'Tags'
   WITH ( PROPERTY_SET_GUID = 'F29F85E0-4FF9-1068-AB91-08002B27B3D9', PROPERTY_INT_ID = 5,
      PROPERTY_DESCRIPTION = 'System.Keywords - Keywords (Tags) of the item.');
GO
CREATE FULLTEXT INDEX ON SQLServerArticles..Documents
    (file_stream TYPE COLUMN file_type LANGUAGE 1033 statistical_semantics)
    KEY INDEX DocumentsFt ON ft
    WITH SEARCH PROPERTY LIST = DocumentProperties
GO

-- Find path of Documents

USE SQLServerArticles
GO
SELECT FileTableRootPath() AS 'FileTable Root Path'
GO

-- Right-click the first returned row and select Copy.
/*
4.  Open a Windows Explorer window, paste the Document Library path to the address bar, and press Enter. Then double-click the Document Library folder.
5.  Open another Windows Explorer window, browse to the Assets folder of this lab and enter the SQLServerArticles folder.
6.  Select all files and folders in the SQLServerArticles folder, and drag them to the Document Library folder, while pressing Ctrl.
*/

-- \\KIWI\MSSQLSERVER\SQLServer Articles
-- Check all files are in the table

SELECT stream_id, name, file_type,
(SELECT REPLACE(N'File:\\'+FileTableRootPath()+file_stream.GetFileNamespacePath(), N' ', N'%20')
FROM SQLServerArticles..Documents T2
WHERE T2.stream_id = T1.stream_id FOR XML PATH(''), TYPE) AS FilePath,
    creation_Time, last_write_time, last_access_time, is_directory, is_offline,
    is_hidden, is_readonly, is_archive, is_system, is_temporary
        FROM SQLServerArticles..Documents T1
        ORDER BY name ASC
GO


-- Querying table
/*
SELECT column_name FROM table_name
WHERE CONTAINS (PROPERTY (column_name,'property_name'),'')
*/

USE SQLServerArticles
GO
SELECT name DocumentName, file_stream.GetFileNamespacePath() Path FROM Documents
    WHERE CONTAINS(PROPERTY(file_stream, 'Title'), 'data OR SQL')
GO

SELECT name DocumentName, file_stream.GetFileNamespacePath() Path FROM Documents
    WHERE CONTAINS(file_stream, 'NEAR(("data", "SQL"), 5, FALSE)')
GO

SELECT name, document_key, keyphrase, score
    FROM semantickeyphrasetable(Documents, *)
  INNER JOIN Documents ON stream_id = document_key
    ORDER BY name, score DESC

GO

SELECT KeyPhrase, AVG(score) AS score FROM semantickeyphrasetable(Documents, *)
    INNER JOIN Documents ON stream_id = document_key
    GROUP BY KeyPhrase ORDER BY score DESC
GO

DECLARE @Title as NVARCHAR(1000)
DECLARE @DocID as UNIQUEIDENTIFIER

SET @Title = 'InsideSQLAzure.docx'

SELECT @DocID = stream_id FROM Documents WHERE name = @Title

SELECT name, document_key, keyphrase, score
    FROM semantickeyphrasetable(Documents, *, @DocID)
    INNER JOIN Documents ON stream_id = document_key
    ORDER BY name, score DESC
GO

DECLARE @Title as NVARCHAR(1000)
DECLARE @DocID as UNIQUEIDENTIFIER

SET @Title = 'ConsolidationPrescriptiveGuidance.docx'

SELECT @DocID = stream_id FROM Documents WHERE name = @Title

SELECT @Title AS SourceTitle, name AS MatchedTitle, stream_id, score
    FROM semanticsimilaritytable(Documents, *, @DocID)
    INNER JOIN Documents ON stream_id = matched_document_key
    ORDER BY score DESC
GO

USE SQLServerArticles
DECLARE @SourceTitle as NVARCHAR(1000)
DECLARE @MatchedTitle as NVARCHAR(1000)
DECLARE @SourceDocID as UNIQUEIDENTIFIER
DECLARE @MatchedDocID as UNIQUEIDENTIFIER

SET @SourceTitle = 'ConsolidationPrescriptiveGuidance.docx'
SET @MatchedTitle = 'SS08R2Virtualization.docx'

SELECT @SourceDocID = stream_id FROM Documents WHERE name = @SourceTitle
SELECT @MatchedDocID = stream_id FROM Documents WHERE name = @MatchedTitle

SELECT @SourceTitle AS SourceTitle, @MatchedTitle AS MatchedTitle, keyphrase, score
    FROM SEMANTICSIMILARITYDETAILSTABLE(Documents, file_stream, @SourceDocID, file_stream, @MatchedDocID)
    ORDER BY score DESC
GO



-- Testing Semantic Search capabilities

USE AdventureWorks2008R2
GO
CREATE FULLTEXT INDEX ON Production.Document
    (Document
        TYPE COLUMN FileExtension
        LANGUAGE 1033
        statistical_semantics,

        DocumentSummary
        LANGUAGE 1033
    )
    KEY INDEX PK_Document_DocumentNode
    ON AW2008FullTextCatalog
    WITH (CHANGE_TRACKING = AUTO)
GO

ALTER FULLTEXT INDEX ON HumanResources.JobCandidate
    ALTER COLUMN Resume
    ADD statistical_semantics
GO

SELECT OBJECT_NAME(object_id) TableName, COL_NAME(object_id, column_id) IndexedColumn, *
    FROM sys.fulltext_index_columns
GO

SELECT Title, document_key, keyphrase, score
   FROM semantickeyphrasetable(Production.Document, *)
   INNER JOIN Production.Document ON DocumentNode = document_key
   ORDER BY Title, score DESC
GO

DECLARE @Title as NVARCHAR(1000)
DECLARE @DocID as HierarchyID

SET    @Title = 'Seat Assembly'
SELECT @DocID = DocumentNode FROM Production.Document WHERE Title = @Title

SELECT @DocID AS ManualID, @Title AS 'Manual', keyphrase, score
    FROM semantickeyphrasetable(Production.Document, *, @DocID)
    ORDER BY score DESC
GO

DECLARE @Title as NVARCHAR(1000)
DECLARE @DocID as HierarchyID

SET    @Title = 'Seat Assembly'
SELECT @DocID = DocumentNode FROM Production.Document WHERE Title = @Title
SELECT @Title AS 'Original Document', Title AS 'Similar Document', score
    FROM semanticsimilaritytable(Production.Document, *, @DocID)
    INNER JOIN Production.Document ON DocumentNode = matched_document_key
    ORDER BY score DESC
GO

DECLARE @SourceTitle as NVARCHAR(1000)
DECLARE @MatchedTitle as NVARCHAR(1000)
DECLARE @SourceDocID as HierarchyID
DECLARE @MatchedDocID as HierarchyID

SET @SourceTitle = 'Seat Assembly'
SET @MatchedTitle = 'Front Reflector Bracket and Reflector Assembly 3'

SELECT @SourceDocID = DocumentNode FROM Production.Document WHERE Title = @SourceTitle
SELECT @MatchedDocID = DocumentNode FROM Production.Document WHERE Title = @MatchedTitle

SELECT @SourceTitle AS 'Original Document', @MatchedTitle AS 'Related Document', keyphrase, score
    FROM semanticsimilaritydetailstable(Production.Document, Document, @SourceDocID, Document, @MatchedDocID)
    ORDER BY score DESC
GO
Filed under: , , , , , , ,
Microsoft Semantic Search
25 March 10 08:56 PM | sqlartist | 3 comment(s)

 

This is something I really get excitied about - Microsoft Semantic Search. There is an excellent PDC demo and presentation here - http://microsoftpdc.com/Sessions/SVR32.

Intially I didn't think this was SQL related but I read that it may be included in future versions of SQL Server.

For many years I have written linguistic, semantic, text extraction & clustering code in SQL Server for fun - now finally I can throw that all away and use this tool :)

It reminds me of the Microsoft Research project called Stuff I've Seen by Susan Dumais, paper here http://research.microsoft.com/en-us/um/people/sdumais/siscore-sigir2003-final.pdf

 

Filed under: ,
Business Intelligence in SharePoint Server 2010 (Outstanding Poster)
10 February 10 06:01 PM | sqlartist | 5 comment(s)

 

I have been blown away by the high quality of architecture diagrams and process posters from the SharePoint team back from the beta of 2007. Now SharePoint 2010 is coming I have just come across the work they have been doing for this release and is of very high quality. As SharePoint 2010 has a significant BI feature list I thought I would post an example here. These Visio diagrams are huge…

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Getting started with business intelligence in SharePoint Server 2010

 

http://www.microsoft.com/downloads/details.aspx?familyid=FC97D587-FFA4-4B43-B77D-958F3F8A87B9&displaylang=en 

 I wish the SQL development team would produce material like this :) 

 

I got in touch with the guy behind the Business Intelligence diagrams (Norm Warren) and he is also producing new posters highlighting common BI scenarios. His team has a site when you can find most of their excellent documentation.

 

http://blogs.technet.com/tothesharepoint/ 

 

Filed under: ,
Mapping the Brain using SQL Server 2008 Spatial Features
05 September 09 01:37 AM | sqlartist | 2 comment(s)

I have had a problem with my brain for the past few years now and about 3 years ago had a ‘Brain Pacemaker’ fitted – now without going into the gory details (check my facebook for the pictures J) this involves placing a couple of electrodes into the brain on the left and right sides. When I first had the implant I was the youngest in the world to have it but the 12th person overall.  So research was lacking on exactly the right place to put the electrodes. I was convinced for some time they were in the wrong place.

Since having the pacemaker replaced 2 weeks ago the change in the way the stimulator works is dramatic – this time they hit the sweet spot and instead of feeling like someone was sticking needles in my head in the same place at the rate of 5 a second it feels like the whole of my head is being stimulated  and this according to the neurologist is a good sign it will work well.

So my theory has been that because it is not an exact science or because people are different that placement of these electrodes needs a way better way of predicting where that sweet spot actually is.  The device is not only used for my condition but also for people suffering from Parkinson’s, Tourette’s Syndrome, back pain and even to give women orgasms at the touch of a button (the last one is not available on the NHS)

So I am expanding my knowledge of SQL Server 2008 Spatial beyond the Geography  data type – which alone has the potential to change the way we will navigate and visualize data in the upcoming years, to really understanding the  Geometry data type and develop a spatial map of the brain. Now I do not actually intend to map the whole thing but there must be data in the public domain that already holds this information in some form of importable / convertable format that could be indexed within SQL Server.

The goal then would be to map the electrode placement information gathered from all patient information and layer that over the map of the brain. Immediate placement pattern will be obvious but to take it a stage further may would look to use data mining to predict best placement.

It’s a fun little project that I aim to develop to demonstrate using spatial data in different ways, so if anyone has data of this type that’s available in the public domain give me a shout. The actual patient data will be fake I just want to demonstrate the principal of using spatial mapping with the Geometry data type

 

Filed under: , ,
MapPoint Add-in for SQL Server (Updated with Example)
31 July 09 08:14 PM | sqlartist | 4 comment(s)

 

Nice :) If you have MSDN you can download MapPoint Europe or US or get the trial from the link below - will be trying this out tonight

View and edit your SQL data on a map. It’s quick, low cost, and doesn’t require special training. To get started you need the free Add-in, MapPoint 20091, and access to data in a SQL Server 2008 database

http://www.microsoft.com/mappoint/en-us/sqladdin.aspx

 

 

 UPDATED:

 

Right first I downloaded the 1.8GB European MapPoint and then remembered all my spatial SQL 2008 databases are US only :( so another 1.7GB later I have it all installed and tried out the add-in.

I used a US Airports table in SQL Server - approx 6000 records with a GEOGRAPHY column and added a layer onto the map of the US. Here is the result.

 

 US_Airports using MapPoint addin for SQL 2008

 

Still exploring the info further as there are many features. It would have been nice for the add-in to come with an example or a walkthrough on using the SQL 2008 sample spatial database but maybe thats for next time. This took less than 5 minutes from start to finish.

Filed under: , ,
Live Meeting: Creating a Star Schema and OLAP Cubes
25 June 09 08:06 PM | sqlartist | 1 comment(s)

Hi,

I just finished my first live meeting for the UK SQL Server user group on the subject of creating a star schema and OLAP cubes. I think it went well, it was recorded so Ill let you all be the judge of that - presentation to follow.

I spoke way too fast, Tony Rogerson told me I sound "well Cockney" - but depressing as in my own head I sound like Hugh Grant. I spoke way too fast as I am inclinded to do but even though I got through most of my content I defintely forgot loads and apologies to anyone for that. I will follow it up by slowing the next one down and spending much more time on each subject.

 

 ps. I found out today my Neurologist wants to move my brain implant (not joking) to somewhere else in my body - scheduled for the 14th August

 

Filed under: , ,
Thinking outside the box - impressive
07 June 09 10:13 PM | sqlartist | 1 comment(s)

 

 I cant believe I have missed this blog for so long but I came across a site last night that is just plain exciting for people who love Transact-SQL.

"Thinking outside the box" - http://weblogs.sqlteam.com/peterl/Default.aspx has some of the best and unique examples of working with sql I have seen (Itzik quality)

I think Peter even won the recent String Grouping competition with his submission. I loved that Celko even posted comments on one of the articles dismissing the way Peter had solved the problem. Who cares :)

 Definatley one to check out.

Filed under: , ,
Avanade & Princes Trust Business Intelligence Training
20 May 09 02:51 PM | sqlartist | with no comments

During the past two weeks the company I work for was asked by the Princes Trust to provide a training course to them to explain the entire Microsoft Business Intelligence software stack. My company agreed to donate my time and a colleague’s time to put this training material together. Our remit was to provide novice to advanced training over two weeks on Microsoft SQL Server and Microsoft Office Sharepoint Server. So basically a minimum of 8 weeks worth of training material given over 2 weeks written in just over a week – no mean feat J

Because of the worthwhile causes that the Princes Trusts supports and the fact that talking about the entire Microsoft SQL Server product family is something I love doing this was a fairly exciting but somewhat overwhelming challenge. I called in some old friends back from when I was a SQL server MVP and asked Itzik Ben-Gan and Kalen Delany if I could use some of their content to assist me in my training plan – they both very kindly agreed to let me use some of their material and Itzik sent through a large number of his books to distribute and Kalen is sending through some of her SQL Server Architecture and Metadata training DVDs. The generosity of the SQL community never fails to amaze me.

So I have just finished my part of the training and am assisting my colleague with the Sharepoint and Performance Point stuff. I’m not a official Microsoft trainer but I do love seeing people excited by SQL Server and hearing people spending their own time over the weekend downloading SQL Express on their home machines and trying out some of the labs we developed for them to further understand the product.

So I would like to thank Audrius Pranckevicius, Igal Greenberg and David Haigh from Avanade for giving their time for free - obviously at the request of Avanade J and to Marina Rumyantseva and Richard Chadwick at the Princes Trust for making the whole experience very enjoyable and rewarding.

Extra special thank to Itzik Ben-Gan, Kalen Delany for their kind offer of materials and to Tony Rogerson for validating my entire two weeks’ worth of content.

 

"These views are mine alone and do not necessarily reflect the views of Avanade."

http://www.princes-trust.org.uk

http://www.avanade.com

 

Filed under: , , ,