Saturday 29 March 2014

A Gamification view of the future

I did a Gamification course on Coursera a while back and this video was referenced during it. While not the greatest topic it is a great view of what the future may hold and really what is shown doesn't seem so far fetched. Until the last seconds perhaps.

Believe in Gamification! [A Futuristic Short Film…: http://youtu.be/ziHCvpikLh8

Microsofts in 2019

I do enjoy seeing these future vision clips. It does give inspiration and ideas for today.

Microsoft in 2019 [HQ]: http://youtu.be/RWxqSEMXWuw

Tuesday 18 March 2014

The world is one big dataset

This is a TED talk, seeing things like this really do inspire you.

Dan Berkenstock: The world is one big dataset. No…: http://youtu.be/7pVPmmwSeJQ

Sunday 9 March 2014

Search SSRS Datasets in SQL

The XML for SQL Server Reporting Services Reports are stored in the ReportServer Database of the server. Its possible to pull the XML out and then use the contents to find which reports are using which tables etc. This can be very handy if you there is a change to a schema where logic in reports may need to be updated but you dont know which reports need to be looked at.

The below will place the XML into a table so you can use it to filter what you are after.

CREATE TABLE [dbo].[ReportContents](
 [CatalogItemID] [uniqueidentifier] NOT NULL,
 [Type] [int] NOT NULL,
 [Path] [nvarchar](425) NOT NULL,
 [Name] [nvarchar](425) NOT NULL,
 [ItemContent] [xml] NULL
) ON [PRIMARY]

INSERT INTO ReportContents
 (CatalogItemID, [Type], [Path], [Name], ItemContent)


SELECT
 ItemID
 , [Type]
 , [Path]
 , [Name]
 , cast(CONVERT(VARCHAR(MAX), CONVERT (VARBINARY(MAX), [Content])) as xml) As [ItemContent]
FROM ReportServer.dbo.Catalog
WHERE [Type] <> 1     --Folder
   AND [Type] <> 3 --Image


The below queries the table you created, adjusting the value of the @SQLToCheckFor variable will adjust which text to look for within the SQL Datasets of the SSRS XML. Note this is set up for SQL2008, from memory its just a change of the http address from 2008 to 2005 etc.

--Check all the reports to find a bit of SQL

Declare @SQLToCheckFor varchar(500) = 'FACTMemberBalance'--'dPlanClassification' --Enter your bit of SQL here

select MydataSets.CatalogItemID, MydataSets.ReportName , MyDataSets.DataSetName,
MydataSets.CommandText, MydataSets.ReportPath
from
 (
  select
   CatalogItemID,
   [Name] As ReportName,
   [path] as ReportPath
   ,nref.value('@Name', 'nvarchar(255)' ) As DataSetName
   ,nref.query('.') As DataSetXML
   ,nref.value('declare namespace p1="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition";
     (./p1:Query[1]/p1:CommandText)[1]', 'varchar(max)') As CommandText
   ,nref.value('declare namespace p3="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner";
      declare namespace p1="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition";
      declare default element namespace "http://schemas.microsoft.com/AnalysisServices/QueryDefinition";
     (./p1:Query/p3:MdxQuery/QueryDefinition/QuerySpecification/From)[1]', 'varchar(max)') As Cube   
  from ReportContents
  cross apply ItemContent.nodes('declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition";
            declare namespace p1="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner";
  //DataSet') as R(nref)
--A filter can be added here to limit what reports you are looking through
 )MyDataSets
where CommandText like '%' + @SQLToCheckFor + '%'



How Target Figured Out A Teen Girl Was Pregnant Before Her Father Did

A favorite article of mine, was brought to my attention by a friend of mine. Its pretty impressive what Target was able to do but at the same time you have to wonder how far should organisations go to understand their customers.

How Target Figured Out A Teen Girl Was Pregnant Before Her Father Did

Saturday 8 March 2014

Telstra fields 40,000 government data requests

That seems pretty high to me. I understand there are times in emergencies etc but I would like to know a bit more about the rest.

Tuesday 4 March 2014

Westpac using big data to woo customers with offers made to measure

An interesting article on how Westpac is using its data to target communications to its customers.
Nothing really new but it is a bit of a milestone a lot of Business would love to get to.

The below comment caught my eye and made me wonder what their definition is of Big Data and why they would have more than one data warehouse. Maybe its just the initial extracts from the Web that is the "Big Data" component which is then transformed into structured data in a Warehouse?

"Our data sources are growing very fast and customer interactions are growing very fast," Ms Ganschow said. "We know who you are paying. We know where you are shopping and what you are buying. There is a lot of data pouring into our data warehouses."


Saturday 1 March 2014

Microsoft Power BI

Interesting stuff, but like most of these tools you really need to use them first before you can make a call on them.

http://www.techrepublic.com/article/microsoft-power-bi-brings-big-data-to-the-little-guys/

SQLCLR

I found this a useful article on SQL Server Central.
SQLCLRs are a great way of integrating SQL Server with other applications and/or to create better ways of achieving your results.
The Level 2 page is very in depth, I wish I had access to such information years ago when I first started playing with them.
My favorite SQLCLR I created was to trigger the SSRS Web Service so we can schedule and run Reports with variables set in a table. Very handy when you need hundreds/thousands of reports scheduled to run only after certain events complete etc.

http://www.sqlservercentral.com/stairway/105855/