4 Replies Latest reply on Dec 12, 2016 6:29 AM by Kristy McGee

    Point in Time Data Scaffolding/Shaping - Salesforce

    Kristy McGee

      Good morning,

       

      I need some help, obviously.  How would I show how the open backlog item count over time?  Sounds easy, but since the data from salesforce is dynamic - I don't get an accurate picture.

       

      We had this issue with our quality control data as well.  We wanted to see how many tickets were open at any point in time and how many were closed.  I have been watching Bethany Lyons video from conference "Structuring Data for Analysis" but I am not sure if I should create a Data Scaffolding table.  I am newer to sql and do not have the rights to create tables or stored procs there.  Bigger still, the real data issue I have is connecting to SF - not necessarily sql in this case.

       

      Any and all help would be greatly appreciated!

       

      Thank you,

      Kristy

       

      The attached screenshot was the solution to our qc data question - but I had to answer it in excel.  I would love to move this to tableau as well.  Hopefully have an applicable answer for both.

       

      Mark Jackson Joe Mako Jeffrey Shaffer Richard Leeke Alex Blakemore

      I tagged you all, because I really need to get some help with this.  I am new to this 'data scaffolding' approach and am slamming my head on the wall.

        • 1. Re: Point in Time Data Scaffolding/Shaping - Salesforce
          Norbert Maijoor

          Hi Kristy,

           

          Without seeing the data it's scary to make an attempt;) but here is my "shot". I use "Scaffolding" when i have to merge two sources based e.g. date when the individual dates are not available in both sources. By adding a third source with all individual dates of the year and assigning it has the primary data source you will create a "backbone" for the other two sources against which the individual dates will match with the "backbone". Hope it make sense and is applicable in your scenario.

          • 2. Re: Point in Time Data Scaffolding/Shaping - Salesforce
            Kristy McGee

            Good morning Norbert,

             

            That is a very valid scenario and I really understand that.  I think this is a little different.  I am essentially trying to create this datasource in tableau.  This is the sql query that produces the temp table with table variables. How would you create this to show the point in time status?

             

            USE healthstream_livesite_db

            GO

             

             

            DECLARE @bugs TABLE(

            BugID /*Defect.Defect ID*/

            , DetectionDate /*Defect.Detected on Date*/

            , Customer /*Defect.Customer*/

            , Priority /*Defect.Priority*/

            , Severity /*Defect.Severity*/

            , Summary /*Defect.Summary*/

            , IssueType /*Defect.Issue Type*/

            , ResolutionType/*Defect.Resolution Type*/

            , Status  /*Defect.Status*/

            , StatusDate

            , Subject  /*Defect.Subject*/

            , ClosingDate  /*Defect.Closing Date*/

            , ChangeDateTime

            , OldValue

            , NewValue

            )

            INSERT INTO @bugs(

            BugID, DetectionDate, Customer, Priority, Severity, Summary, IssueType, ResolutionType, Status, StatusDate, Subject, ClosingDate, ChangeDateTime, OldValue, NewValue

            )

            SELECT

            1. BUG.BG_BUG_ID /*Defect.Defect ID*/

            , BUG.BG_DETECTION_DATE /*Defect.Detected on Date*/

            , BUG.BG_USER_09 /*Defect.Customer*/

            , BUG.BG_PRIORITY /*Defect.Priority*/

            , BUG.BG_SEVERITY /*Defect.Severity*/

            , BUG.BG_SUMMARY /*Defect.Summary*/

            , BUG.BG_USER_11 /*Defect.Issue Type*/

            , BUG.BG_USER_02 /*Defect.Resolution Type*/

            , BUG.BG_STATUS /*Defect.Status*/

            , STATUS_DATE

            , BUG.BG_SUBJECT /*Defect.Subject*/

            , BUG.BG_CLOSING_DATE /*Defect.Closing Date*/

            , AU_TIME

            , AP_OLD_VALUE

            , AP_NEW_VALUE

            FROM td.BUG BUG

            LEFT OUTER JOIN

            (

            SELECT

            AUDIT_LOG.AU_ACTION /*Audit Log.Action*/

            ,AUDIT_LOG.AU_DESCRIPTION /*Audit Log.Audit Description*/

            ,AUDIT_LOG.AU_ENTITY_ID /*Audit Log.Entity ID*/

            ,AUDIT_LOG.AU_ENTITY_TYPE /*Audit Log.Entity Type*/

            ,AUDIT_LOG.AU_TIME /*Audit Log.Time*/

            ,CONVERT(DATE,AUDIT_LOG.AU_TIME) AS 'STATUS_DATE'

            , AUDIT_PROPERTIES.AP_OLD_VALUE /*Audit Properties.Old Value*/

            , AUDIT_PROPERTIES.AP_NEW_VALUE /*Audit Properties.New Value*/

            , DayRank = row_number() OVER(partition by AUDIT_LOG.AU_ENTITY_ID, CONVERT(DATE,AUDIT_LOG.AU_TIME) order by AUDIT_LOG.AU_TIME desc)

             

            FROM td.AUDIT_LOG AUDIT_LOG /*Audit Log*/

             

            INNER JOIN  td.AUDIT_PROPERTIES AUDIT_PROPERTIES /*Audit Properties*/

            ON AUDIT_LOG.AU_ACTION_ID = AUDIT_PROPERTIES.AP_ACTION_ID

            WHERE AUDIT_LOG.AU_ENTITY_TYPE = 'BUG'

            AND AUDIT_LOG.AU_ACTION = 'UPDATE'

            AND AUDIT_PROPERTIES.AP_TABLE_NAME = 'BUG'

            AND AUDIT_PROPERTIES.AP_FIELD_NAME = 'BG_STATUS'

            ) StatusChange

            ON BUG.BG_BUG_ID = StatusChange.AU_ENTITY_ID

            AND DayRank = 1

            AND (BUG.BG_CLOSING_DATE >= '9/30/2014' OR BUG.BG_CLOSING_DATE = '' OR BUG.BG_CLOSING_DATE IS NULL)

             

             

             

             

             

            DECLARE

            @StartDate date

            , @EndDate date

             

             

            --IF @StartDate = '1/1/1950'

              SET @StartDate = '9/30/2014'

             

             

            --IF @EndDate = '12/31/2999'

              SET @EndDate = CONVERT(DATE,DATEADD(DAY,-1,GETDATE()))

             

             

            DECLARE @ProcessingDate date

            SET @ProcessingDate = @StartDate

             

             

            DECLARE @DefectStatusByDate TABLE (DefectID int, StatusDate date, DefectStatus varchar(50))

            WHILE @ProcessingDate <= @EndDate

            BEGIN

             

             

            INSERT INTO @DefectStatusByDate(DefectID, StatusDate, DefectStatus)

             

              SELECT StatusDt.BugID, @ProcessingDate, NewValue

             

              FROM

              (

              SELECT BugID, MAX(StatusDate) AS 'RecordStatusDate'

              FROM @bugs

              WHERE StatusDate <= @ProcessingDate

              GROUP BY BugID

              ) StatusDt

             

             

              INNER JOIN @bugs RDS

              ON StatusDt.RecordStatusDate = RDS.StatusDate

              AND StatusDt.BugID = RDS.BugID

              SET @ProcessingDate = DATEADD(DAY,1,@ProcessingDate)

            END

             

             

            select DefectID

            , StatusDate

            , DefectStatus

            , CASE WHEN DefectStatus in ('New','Open') THEN 'Backlog'

            WHEN DefectStatus in ('Duplicate', 'Fix Proposed', 'Fix Validated - BT', 'Fix Validated - IT', 'Fix Validated - Prod', 'Fixed', 'Fixed-Task Complete') THEN 'In Progress'

            WHEN DefectStatus in ('Need Info', 'Hold') THEN 'Waiting'

            WHEN DefectStatus in ('Closed') THEN 'Closed Complete'

            ELSE 'Other'

            END AS 'StatusCategory'

            from @defectstatusbydate

            where statusdate >= '9/30/2014'

            • 3. Re: Point in Time Data Scaffolding/Shaping - Salesforce
              Alex Blakemore

              Hi Kristy,

               

              I've attached a copy of a short presentation I gave at TC2012 on visualizing queue length data in Tableau that covers 3 issues that often arise. Tableau has added new features in the last 4 years, so you have a few new alternatives beyond the ones I discussed in the presentation (mostly using custom SQL)

               

              Showing backlogs over time can be tricky because the data is not often shaped well for that purpose.

               

              The problem you may be facing is if your data has one row per defect with two columns: one for open date, and another for close date (null for still open defects). In that case, it can be tricky to calculated queue lengths (or backlogs). One solution is to use a self union as described in the presentation. I did that with custom SQL in 2012, but you can do a union directly in Tableau now making that approach even easier. That is my preferred approach.

               

              Marc Reuter also has shown a solution using data blending -- in that case, you duplicate your data source, rename [open date] in one source to [date], and [close date] to [date] in the other. Then blend both sources on [date]. SUM([Number of Records]) gives the number of defects opened on each day for the first source, and the number closed for each day for the second source. Then you can calculate the change in backlog each day. A risk with the data blending using dates  is that only dates that appear in the primary source are queried in the secondary, so it is easy to accidentally filter out records if your data does not have at least one record per day. You can avoid this issue with a scaffolding source.

               

              There may also be gaps in your dates when there are no data records which can lead to a distorted graphics (the first issue discussed in the presentation). Tableau demonstrated line step charts at TC2016 as a coming feature so that problem could be solved sometime soon in a 10.x release (That was the very first feature request I ever made to Tableau back in version 1.0, so I suppose you just have to be patient) Until step line charts (https://community.tableau.com/ideas/1067) are available, the typical solution is to pad the data by using a union with a "calendar" table that has one row for every date, and nulls for all fields.

               

              Finally, take a look at the chart in the bottom right of page 2 in the attached presentation, also shown below.

              Defects.jpg

              This is a useful view for telling where a project is in the maturity life cycle. It shows cumulative (running total) open and closed defects in a stacked area chart. The typical progression has very few defects in the initial days because the system is not yet functional enough to test heavily, then a phase where defects are created much faster than they are being closed leading to a surge in backlog as the first draft of significant functionality comes on-line. That's a frustrating phase for managers because it seems like the defect work will continue with no end in sight, but eventually the team begins to close defects quickly as the core functionality becomes solid and they then have the ability to work off other features independently. Finally, the new defect rate begins to slow, and the backlog (orange gap) begins to narrow and you can tell you are headed towards a more stable phase. It is amazing how many projects follow this same trajectory.

               

              Thanks for tagging me in such an illustrious list of Tableau users - all top zen masters save me. You made my day.

               

              Hope this reply is helpful.

              Alex

              1 of 1 people found this helpful
              • 4. Re: Point in Time Data Scaffolding/Shaping - Salesforce
                Kristy McGee

                Thank you!  I am reading your presentation now.  Very nice and succinct.  Your explanation here is just what I needed.  I will put it together today and look like a hero.  Thank you for your help.  It will help me do what several others could not.

                I tried to find a picture of Indiana Jones passing the remains of previous explorers, but I think you get my drift.