If you can get your data to look as below first, you can subsequently change it to a calendar view as needed.
I created a sheet which contains calendar data and databases as shown below. I pivot this data in Tableau and do a left-join with the data you provided.
Thank you for the quick response!
Although this would be a solution, I am trying to keep it vizualized as a calendar... Do you have any ideas on how to do that?
I am wondering if you could help me think of another way of formating the data.
The issue is that my databases are not constant--we can add and delete databases on a daily basis. The other obstacle was that my datasource is a sql query. I managed to get the data in the format you gave me with a pivot function, and by using a dynamic sql statement I can generate an accurate list of databases real time.
This issue was resolved by adding 'set nocount on;' after the as in the procedure. However, it did hit against the limit of columns tableau supports. Hence, still need a way to format the data.
It looks like your dataset looks like
Date, DBA,DBB,master,DBC, with one extra column per database. Is that true?
If so, that is nuts!
Just format it with two columns;
Date Database Backed up, Database Name.
I think where you were tripping up is what happens if you filter on a DB on a day where it wasn't backed up.
Given that you're already doing down the path of a stored procedure, I'd just create a CROSS JOIN so every database has a record for the day of the year.
Something like this;
--Declare a start date boundary as Jan 1 of the current year declare @lowestdate date = DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) --Declare an end date boundary as today declare @maxdate date = getdate() --Generate a temp table of numbers, one for each day of the year, and generate a date along with that DECLARE @startnum INT=0 DECLARE @endnum INT=datediff(day,@lowestdate,@maxdate) ; WITH gen AS ( SELECT @startnum AS num UNION ALL SELECT num+1 FROM gen WHERE num+1<=@endnum ) SELECT num, dateadd(day,num,@lowestdate) PlaceholderDate INTO #days FROM gen option (maxrecursion 10000) --Create a distinct list of databases select distinct [database_name] INTO #db from [PERSISTENT].[dbo_backupset] SELECT c.PlaceholderDate, c.database_name, CONVERT(bit,CASE WHEN d.database_name is null then 0 else 1 END) BackupSuccess FROM ( SELECT d.PlaceholderDate, db.database_name FROM #days d CROSS APPLY #db db ) c LEFT JOIN persistent.dbo_backup d on c.database_name = d.database_name and c.PlaceholderDate = convert(date,backup_finish_date)
Thank you for this!
I have been tweaking it for a while and had to do some modifications. I will update once I have the full query with the Viz ready.
Thank you again for your help!!
Final Proc and Viz:
IF OBJECT_ID('tempdb.dbo.#db', 'U') IS NOT NULL
DROP TABLE #db;
IF OBJECT_ID('tempdb.dbo.#days', 'U') IS NOT NULL
DROP TABLE #days;
--Declare a start date boundary as begining of the month one year ago
declare @lowestdate date = (dateadd(year,-1,dateadd(month,datediff(month,0,getdate()),0)))
--Declare an end date boundary as end of the month
declare @maxdate date = (DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)))
--Generate a temp table of numbers, one for each day of the year, and generate a date along with that
DECLARE @startnum INT=0
DECLARE @endnum INT=datediff(day,@lowestdate,@maxdate)
WITH gen AS (
SELECT @startnum AS num
SELECT num+1 FROM gen WHERE num+1<=@endnum
SELECT num, dateadd(day,num,@lowestdate) PlaceholderDate
option (maxrecursion 32767)
--Create a distinct list of databases
Declare @DBNameTable table (DatabaseName varchar (max), AppCode varchar (max), DomainName varchar (max))
insert into @DBNameTable
select distinct a.name,
c.Name as 'DomainName'
from [DBA].[Database] a
inner join [DBA].[Instance] b on a.InstanceID=b.InstanceID
inner join [DBA].[Domain] c on b.EnvironmentID=c.DomainID
--select * from @DBNameTable
CONVERT(bit,CASE WHEN d.database_name is null then 0 else 1 END) BackupSuccess
FROM #days d
CROSS APPLY @DBNameTable db
LEFT JOIN [PERSISTENT].[dbo_backupset] d
on c.databasename = d.database_name
and c.PlaceholderDate = convert(date,backup_finish_date)
Final Thoughts: This proc takes a while. I would do an extract. Also, for my data,@ I parametized a bunch of thing to provide greater reusability.
Thank you for helping Tom W & @HARI ANKEM!!
Glad that you finally have a solution.