31:[{"id":"28d2fa4f89a8034e61a384d439eb55aa28ef11a68c9d9aec027a607a6c2cb6ac8134931a0cc53b69331af41d2bd7278bd9bb71dd7551d4084e8f252ec7972977","href":"https://static.observableusercontent.com/files/28d2fa4f89a8034e61a384d439eb55aa28ef11a68c9d9aec027a607a6c2cb6ac8134931a0cc53b69331af41d2bd7278bd9bb71dd7551d4084e8f252ec7972977","name":"tableau-source@1","type":"application/octet-stream","lastModified":1600329677819.128,"size":163192},{"id":"dd3f4844a3b882d392cb5d31bc623068acd7fa8aaa0682ffa9f966a575d68f06eec0217b85c65ffeed66f06a7b497fdbee9f809f4380a4c186a4e15395fa2a64","href":"https://static.observableusercontent.com/files/dd3f4844a3b882d392cb5d31bc623068acd7fa8aaa0682ffa9f966a575d68f06eec0217b85c65ffeed66f06a7b497fdbee9f809f4380a4c186a4e15395fa2a64","name":"nj-positivity-dashboard.png","type":"image/png","lastModified":1600048796079.709,"size":400652}] 2f:{"title":"Reverse-Engineering a Tableau Dashboard Feed","cells":[{"id":0,"value":"md`# Reverse-Engineering a Tableau Dashboard Feed`","pinned":false,"hidden":false,"mode":"ojs"},{"id":146,"value":"md`What we're trying to convert - the [by-day confirmed positive cases in New Jersey](https://dashboards.doh.nj.gov/views/DailyConfirmedCaseSummary7_22_2020/PercentPositivity?:isGuestRedirectFromVizportal=y&:embed=y).`","pinned":false,"hidden":false,"mode":"ojs"},{"id":148,"value":"FileAttachment(\"nj-positivity-dashboard.png\").image()","pinned":false,"hidden":false,"mode":"ojs"},{"id":25,"value":"md`This is a download of the raw data Tableau sends to that dashboard. I grabbed it from the console in Firefox, but there are ways to get this without doing that. 😏`","pinned":false,"hidden":false,"mode":"ojs"},{"id":5,"value":"raw = FileAttachment(\"tableau-source@1\").text()","pinned":true,"hidden":false,"mode":"ojs"},{"id":28,"value":"md`Now we need to turn that mush into something actionable. We're looking for the \\`secondaryInfo\\` key.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":16,"value":"secondaryInfo = raw\n .split(/\\d{2,10};(?={.+})/) // parse out non-JSON pieces\n .filter(s => s.length) // get rid of empty strings\n .map(t => JSON.parse(t)) // JSON.parse\n .find(d => d.secondaryInfo).secondaryInfo // where the good stuff is","pinned":true,"hidden":false,"mode":"ojs"},{"id":33,"value":"md`We need to go even _deeper_. The \\`presModelMap\\` is where the details about the data live.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":36,"value":"root = secondaryInfo.presModelMap","pinned":true,"hidden":false,"mode":"ojs"},{"id":59,"value":"md`Now that we have the root object which contains the data itself (\\`root.dataDictionary\\`) and the tables/sheets it corresponds to (\\`root.vizdata\\`) we can start working toward splicing them back together.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":62,"value":"md`### \\`root.dataDictionary\\``","pinned":false,"hidden":false,"mode":"ojs"},{"id":40,"value":"dataColumns = root.dataDictionary.presModelHolder\n .genDataDictionaryPresModel.dataSegments[0].dataColumns","pinned":true,"hidden":false,"mode":"ojs"},{"id":44,"value":"md`Now we're talking - here are the data types available in this file.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":50,"value":"Inputs.table(\n dataColumns.map((d, idx) => ({\n index: idx,\n 'data type': d.dataType,\n '# of values': d.dataValues.length\n }))\n)","pinned":false,"hidden":false,"mode":"ojs"},{"id":69,"value":"md`Let's turn this into something more usable so we can use it to do lookups later.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":72,"value":"dataLookup = new Map(dataColumns.map(d => [d.dataType, d.dataValues]))","pinned":true,"hidden":false,"mode":"ojs"},{"id":66,"value":"md`### \\`root.vizData\\``","pinned":false,"hidden":false,"mode":"ojs"},{"id":54,"value":"presModelMap = root.vizData.presModelHolder.genPresModelMapPresModel\n .presModelMap","pinned":true,"hidden":false,"mode":"ojs"},{"id":76,"value":"md`This mapping tells us what \"sheets\" exist in this dashboard and give us some direction for determining which values we want from above.\n\nNow we can explore these options to find what we're after.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":77,"value":"viewof sheet = Inputs.select(Object.keys(presModelMap), {\n label: \"Worksheets\",\n description: \"Choose a sheet to work on\"\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":88,"value":"md`Deep within each sheet's object there are column \"indices,\" which communicate which values in the \\`dataColumns\\` above correspond with each sheet.\n\nThis Tableau source has two!`","pinned":false,"hidden":false,"mode":"ojs"},{"id":91,"value":"columns1 = presModelMap[sheet].presModelHolder.genVizDataPresModel\n .paneColumnsData.paneColumnsList[0].vizPaneColumns","pinned":true,"hidden":false,"mode":"ojs"},{"id":141,"value":"columns2 = presModelMap[sheet].presModelHolder.genVizDataPresModel\n .paneColumnsData.paneColumnsList[1].vizPaneColumns","pinned":true,"hidden":false,"mode":"ojs"},{"id":94,"value":"md`In particular we're interested in the \\`valueIndices\\` and \\`aliasIndices\\` fields in that object, which refer to which indexes match to a value in one of the \\`dataColumns\\`.\n\nUnfortunately, this is where this process goes from science to art - through trial and error/knowing what values you're after, we have to sort out which indices match to the \\`dataColumns\\` column that contains our values.\n\nThere's a lot going on in the graphic we're trying to peel this out of, so let's go after the \"easy\" part first - the bars.\n\nThe bar chart values are the only integers on the chart. This is where you have to look for values you recognize. Something looks promising about \\`columns1[1].aliasIndices\\`.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":236,"value":"columns1[1].aliasIndices.length","pinned":true,"hidden":false,"mode":"ojs"},{"id":190,"value":"uniqueValues = new Set(columns1[1].aliasIndices)","pinned":true,"hidden":false,"mode":"ojs"},{"id":196,"value":"md`Note that the size of the Set is ${uniqueValues.size}, but the number of indices is ${columns1[1].aliasIndices.length}. One trick with Tableau is it will _reuse_ values that appear multiple times by repeating an index. This cannot be the indices for the date because those shouldn't repeat. Let's use them to do a lookup against the integer values.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":130,"value":"positives = {\n const indices = columns1[1].aliasIndices;\n const values = dataLookup.get('integer');\n\n return indices.map(idx => values[idx]);\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":248,"value":"md`There they are! Huh - seems like they're maybe in reverse-chronological order? (You can validate these by hovering on the chart itself!) Let's bring our attention to \\`columns1[2].valueIndices\\`.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":215,"value":"uniqueDates = new Set(columns1[2].valueIndices)","pinned":true,"hidden":false,"mode":"ojs"},{"id":217,"value":"md`That looks promising. These are probably the indices for the dates. I do a little bit of cleanup here to remove the trailing \\`00:00:00\\` but otherwise its a straightforward lookup.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":115,"value":"dates = {\n const indices = columns1[2].valueIndices;\n const values = dataLookup.get('datetime');\n\n return indices.map(idx => values[idx].replace(' 00:00:00', ''));\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":133,"value":"data = d3.zip(dates, positives).map(([date, positive]) => ({ date, positive }))","pinned":true,"hidden":false,"mode":"ojs"},{"id":265,"value":"md`We got it! This was a pretty painless match.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":137,"value":"Inputs.table(data)","pinned":true,"hidden":false,"mode":"ojs"},{"id":155,"value":"md`Now the tough part - the lines on the chart.\n\nThere's clearly not enough information in the first set of columns (\\`columns1\\`) to cover this, so odds are its all in \\`columns2\\`.\n\nWe also need an extra piece here - even though there are four lines on the chart, the data for each line is all in the same data type - \\`real\\`. So we need to find the indices for the line grouping too so we know which dates and values belong to which line.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":224,"value":"uniqueLineLabels = new Set(columns2[2].aliasIndices)","pinned":true,"hidden":false,"mode":"ojs"},{"id":222,"value":"md`This was pretty easy - \\`columns2[2].aliasIndices\\` only has ${uniqueLineLabels.size} unique values, so they're likely the line categories. This is also the first time we've used the \\`cstring\\` data type.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":173,"value":"lineCategories = {\n const indices = columns2[2].aliasIndices;\n const values = dataLookup.get('cstring');\n\n return indices.map(idx => values[idx]);\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":271,"value":"md`Whoa! There are ${\n lineCategories.length\n} values in \\`lineLabels\\`. But that makes sense! Remember how we had ${\n columns1[1].aliasIndices.length\n} bars? And what happens to be ${tex`${columns1[1].aliasIndices.length} * ${uniqueLineLabels.size}`}? ${columns1[1]\n .aliasIndices.length * uniqueLineLabels.size}.\n\nNow that we know the magic number, the length of \\`columns2[3].valueIndices\\` (${\n columns2[3].valueIndices.length\n}) should set off some alarms.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":166,"value":"lineValues = {\n const indices = columns2[3].valueIndices;\n const values = dataLookup.get('real');\n\n return indices.map(idx => values[idx]);\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":288,"value":"md`And \\`columns2[1].valueIndices\\` also has a length of ${columns2[3].valueIndices.length}, which means it's a good candidate for the date. (Which counts _up_ this time!)\n\nHow did I know which one belonged the the values and which one belonged to the dates? I tried them both and this arrangement had the right output. 🙃 That's how this works!`","pinned":false,"hidden":false,"mode":"ojs"},{"id":161,"value":"lineDates = {\n const indices = columns2[1].valueIndices;\n const values = dataLookup.get('datetime');\n\n return indices.map(idx => values[idx].replace(' 00:00:00', ''));\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":175,"value":"lineData = d3\n .zip(lineCategories, lineValues, lineDates)\n .map(([label, value, date]) => ({ label, value, date }))","pinned":true,"hidden":false,"mode":"ojs"},{"id":295,"value":"md`Now we can combine these three values by index and we have our lines.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":178,"value":"Inputs.table(lineData)","pinned":false,"hidden":false,"mode":"ojs"},{"id":7,"value":"md`## Appendix`","pinned":false,"hidden":false,"mode":"ojs"},{"id":116,"value":"d3 = require('d3-array@2')","pinned":true,"hidden":false,"mode":"ojs"}],"files":"$W31","stdlib":"1","titling":"auto"}