Using Google Charts API to Visualize Schema Changes

Last week I have worked on the new email report using Google Charts and liked it so much that decided to share it here with anyone who finds it useful.
I have a Schema Changes Audit table which is being maintained by the DDL Trigger. The relevant record is added to this table every time anyone changes objects on the server.
             
Here is a report that I have generated using Google Charts absolutely for free and easily.

If you are not familiar with the Google Charts, you can read my old post about it and how it works here. It is easy to use, very customizable and FREE. The above visualization is using Google Bar Chart.
To keep things short, I am using Transact SQL to build an HTML Image tag. The above email body contains this HTML:
http://chart.apis.google.com/chart?
cht=bvg&
chs=660x250&
chco=CF9036,90062D,67E13B,82088D,319CBA&
chd=t:0,1|1,3|3,2|18,5|4,8&
chds=0,19&
chxt=x&
chxs=0,ff0000,12,0,lt&
chxl=0:|Sep%202|Sep%203&
chm=N,000000,0,-1,10|N,000000,1,-1,10|N,000000,2,-1,10|N,000000,3,-1,10|N,000000,4,-1,10&
chdl=SendBarReport%20(by%20Maria)|TestAuditTable%20(by%20Diana)|TestAUditTable%20(by%20Maria)|TestAUditTable%20(by%20Roy)|V1%20(by%20Diana)
" />
In order to test the HTML chart in usual Browser, you need take the source of the IMG tag and remove the line breaks.
http://chart.apis.google.com/chart?cht=bvg&chs=660x250&chco=CF9036,90062D,67E13B,82088D,319CBA&chd=t:0,1|1,3|3,2|18,5|4,8&chds=0,19&chxt=x&chxs=0,ff0000,12,0,lt&chxl=0:|Sep%202|Sep%203&chm=N,000000,0,-1,10|N,000000,1,-1,10|N,000000,2,-1,10|N,000000,3,-1,10|N,000000,4,-1,10&chdl=SendBarReport%20(by%20Maria)|TestAuditTable%20(by%20Diana)|TestAUditTable%20(by%20Maria)|TestAUditTable%20(by%20Roy)|V1%20(by%20Diana)
------------------------------------------------------------------------
Here is a script that produces the HTML code and sends out an email. Take into consideration that, if you have high variety of objects that are changing – render this report for 1 day only. Otherwise the chart will be too big.

Create Stored Procedure that builds the HTML and sends it

In order to make sure that your Server can send emails, you will need to enable mailing functionality. Here you can find a nice article on how to configure mailing profile.

EXEC SendBarReport 'yourmailgoeshere', 1,1

CREATE PROCEDURE [dbo].[SendBarReport]
                                  @MailRecipient  varchar(256),
                                  @PrintMode  bit= 1,
                                  @MailMode bit= 1
AS
BEGIN
DECLARE @Legend varchar(max);
DECLARE @ChartSeries varchar(max)='';
DECLARE @ChartColors varchar(max)='';
DECLARE @AxisLabels varchar(max)='';
DECLARE @ChartLegend varchar(max)='';
DECLARE @DataValueMarkers varchar(max)='';
DECLARE @Min int, @Max int;
DECLARE @html varchar(max)='';
-- Prepare the data for the chart
SELECT  ChangeDate   =CAST(ChangeDate ASDate),
              Change = ObjectName +' (by '+LoginName+')',
              NumOfChanges =COUNT(1)
INTO #FinalData
FROM TestAuditTable
GROUPBYCAST(ChangeDate ASDate),ObjectName,LoginName;
-- Get distinct dats for the X axis
SELECT @AxisLabels = @AxisLabels +LEFT(DATENAME(m,ChangeDate),3)+' '+CONVERT(varchar(5),DAY(ChangeDate))+'|'
FROM #FinalData
GROUPBY ChangeDate
ORDERBY ChangeDate
-- Get MIN and MAX values to scale the chart correctly
SELECT       @Min =MIN(NumOfChanges)-1,
              @Max =MAX(NumOfChanges)+1
FROM #FinalData;
-- Prepare Chart legend, dynamically generate colors, define data value markers and chart series
-- Chart series must be in the following format |series1_val1,series1_val2,series1_val3|series2_val1,series2_val2,series2_val3| … where each part between | | belongs to separate value on X axis
SELECT  @ChartLegend = @ChartLegend + Change +'|',
           @ChartColors = @ChartColors +CONVERT(varchar(6),LEFT(newid(),6))+',',
           @DataValueMarkers = @DataValueMarkers +'N,000000,'+CAST(ROW_NUMBER()OVER (ORDERBY Change)-ASVARCHAR(10))+',-1,10|',
           @ChartSeries = @ChartSeries +(SELECTCAST(ISNULL(NumOfChanges,0)ASvarchar(50))+','
                                           FROM #FinalData f4
                                           RIGHTJOIN(SELECTDISTINCT ChangeDate,f2.Change
                                                       FROM #FinalData
                                                       OUTERAPPLY(SELECTDISTINCT Change FROM #FinalData ) f2
                                                       ) f3
                                                ON f3.ChangeDate = f4.ChangeDate and f3.Change = f4.Change
                                           WHERE f3.Change = f1.Change
                                           ORDERBY f3.ChangeDate
                                           FORXMLPATH ('')
                                   )+'|'
FROM #FinalData f1
GROUPBY Change
ORDERBY Change;
-- Remove last character “|” or “,” from dynamically created strings
set @ChartSeries =SUBSTRING(@ChartSeries,1,LEN(@ChartSeries)-2);
set @ChartSeries =REPLACE(@ChartSeries,',|','|');
set @ChartColors =SUBSTRING(@ChartColors,1,LEN(@ChartColors)-1);
set @AxisLabels =SUBSTRING(@AxisLabels,1,LEN(@AxisLabels)-1);
set @ChartLegend =SUBSTRING(@ChartLegend,1,LEN(@ChartLegend)-1);
set @DataValueMarkers =SUBSTRING(@DataValueMarkers,1,LEN(@DataValueMarkers)-1);
-- Prepare HTML
set @html =

'cht=bvg&
chs=660x250&
chdlp=t&
chco='+@ChartColors+'&
chd=t:'+@ChartSeries+'&
chds='+CAST(@Min asvarchar)+','+CAST(@Max asvarchar)+'&
chxt=x&
chxs=0,ff0000,12,0,lt&
chxl=0:|'+@AxisLabels+'&
chm='+@DataValueMarkers+'&
chdl='+@ChartLegend+'&

" />'

IF @PrintMode = 1 BEGIN
       PRINT @html
END
-- Send email using prepared HTML as an email body
IF @MailMode = 1 BEGIN
       EXEC msdb.dbo.sp_send_dbmail
       @recipients = @MailRecipient,
       @subject ='Schema Changes',
       @body = @html,
       @body_format ='HTML'
END
END
Audit table script

CREATE SEQUENCE dbo.GetNextNumber AS int START WITH 1 INCREMENT BY 1;

CREATETABLE dbo.TestAuditTable(
       ID int NOT NULL DEFAULT NEXT VALUE FOR dbo.GetNextNumber PRIMARY KEY,
       ChangeDate datetime NULL,
       ObjectName sysname,
       LoginNamesysname);
P.S.
Please, don’t say “Have you noticed that Google Charts API has been deprecated?”
They have announced the deprecation of this tool about two years ago. We can still enjoy this awesome API, Google will not make any changes to it during the next year and will announce if they intend to discontinue or make any changes to it. As soon as this sad day arrives, we will adjust ourselves to their new API https://google-developers.appspot.com/chart/interactive/docs/gallery based on the Java classes.
Yours,
Maria

Comments

Popular posts from this blog

SQL Awesomeness: Finding a QUALIFY query clause in the depths of the database ocean

The Greatest Reasons to use or not to use a Centralized Data Access Architecture

Look back and realize how far you came