10 Replies Latest reply on Jul 11, 2017 11:33 AM by Hari Ankem

    Date Comparison with Aggregated and Non-Agregated Fields+ Calendat

    Haroldy Tevaras

      Hi all! Appologies from the start because this is probably a winding post. :/

       

      So here is my business case. I want  a calendar to display the days a database was backedup. Ideally I would like to be able to compare more than one DB at a time, but getting it to work for one would be a huge help.  

       

      Originally I thought it would be as simple as joining a table with all the days with the table that contains the dates a db was backeup up. What happened with that is that if I filter by DB I loose the days in which a DB was not backeup up. Such a problem was documented here, but I am still trying to convince my dbas to create a cross join for me. I tried following this advice here, but I still had all the dates in which a db wasnt backed up removed, plus the fact that I didnt get all of my DBS.  I tried "Show all missing values", but alas.

       

      Then I created a new workbook and instead of joining the tables as one data source I brought in two data sources and blended. So far that has worked really well, except that I now want to create a calculated field that compares calendar dates with lastbackeup up. After doing much reading I decided to do a datediff on it and if datediff = 0 then it was backeup that day. Ultimately this is for coloring the days in the calendar that a DB was backeup up. However, I get the error cannot mix aggregate and not aggegate measures. I'm guessing that its because my calendar date when I blendit is brough into the calced field as a ATTR.  So then I created another field called day in which I wrapped the calendar date in a date function. Still get the same error. 

       

      Can you guys see if there is a flaw on my logic? Or point me somewhere that will help me? I can't attach a copy of my workbook but mocked up some data that looks like what I have. If you need anything else, please let me know.

       

      Thanks!

        • 1. Re: Date Comparison with Aggregated and Non-Agregated Fields+ Calendat
          Hari Ankem

          If you can get your data to look as below first, you can subsequently change it to a calendar view as needed.

          Capture.PNG

           

          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.

          Capture.PNG

          • 2. Re: Date Comparison with Aggregated and Non-Agregated Fields+ Calendat
            Haroldy Tevaras

            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?

            • 3. Re: Date Comparison with Aggregated and Non-Agregated Fields+ Calendat
              Hari Ankem

              You just need to make that view using the data. It's easy now.

              Capture.PNG

              • 6. Re: Date Comparison with Aggregated and Non-Agregated Fields+ Calendat
                Haroldy Tevaras

                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.

                 

                However, when I try to visualize it I get the following error in tableau. Thoughts?

                 

                UPDATE:

                 

                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.

                • 7. Re: Date Comparison with Aggregated and Non-Agregated Fields+ Calendat
                  Tom W

                  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)
                  
                  • 8. Re: Date Comparison with Aggregated and Non-Agregated Fields+ Calendat
                    Haroldy Tevaras

                    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!!

                    • 9. Re: Date Comparison with Aggregated and Non-Agregated Fields+ Calendat
                      Haroldy Tevaras

                      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 

                          UNION ALL 

                          SELECT num+1 FROM gen WHERE num+1<=@endnum 

                      SELECT num, dateadd(day,num,@lowestdate) PlaceholderDate 

                      INTO #days

                      FROM gen 

                      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,

                      a.AppCode,

                      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

                       

                      SELECT  

                      c.PlaceholderDate, 

                      c.databasename,

                      --d.database_name,

                      c.DomainName,

                      c.AppCode,  

                      CONVERT(bit,CASE WHEN d.database_name is null then 0 else 1 END) BackupSuccess 

                      FROM 

                      SELECT d.PlaceholderDate, 

                      db.databasename,

                      db.DomainName,

                      db.AppCode

                      FROM #days d 

                      CROSS APPLY @DBNameTable db 

                      ) c 

                      LEFT JOIN [PERSISTENT].[dbo_backupset] d

                      on c.databasename = d.database_name

                      and c.PlaceholderDate = convert(date,backup_finish_date)

                       

                      VIZ:

                       

                       

                      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!!

                      • 10. Re: Date Comparison with Aggregated and Non-Agregated Fields+ Calendat
                        Hari Ankem

                        Glad that you finally have a solution.