Skip to main content
    Industry Insights—

    Flattening project financials with the Project Financial Snapshot bundle

    Improving performance in complex project reporting scenarios

    Brian Wenzl
    Brian Wenzl
    The Glue Guy

    A project in NetSuite uses a surprising number of records. Time entries roll up to it, vendor bills and expense reports hang off it, and invoices and revenue recognition schedules reference it, while in the background there’s a budget or estimate representing the original plan.

    Answering questions like these takes real work:

    • What have we spent on this project?
    • What have we billed?
    • How does that line up with the budget?

    Cost lives in time entries, vendor bills, and expense reports. Revenue lives in invoices and revenue recognition. Budget lives in estimates. NetSuite’s powerful reporting capabilities certainly permit you to bring the pieces together, but it’s easy to find yourself trying to combine three or four datasets into a single pivot, managing Cartesian explosions because when you added just one extra little element to your dataset, 8,612 rows turned into 1,210,944 rows.

    The Project Financial Snapshot bundle takes a different route. Instead of building that picture fresh on every report, it builds it once on a schedule and stores the result in a single flat record, so that reading a project's whole financial summary becomes one efficient query against one record rather than a six-way join across your busiest tables. It trades freshness for speed and simplicity.

    What the bundle does

    Project Financial Snapshot is a SuiteBundle. Though it was originally developed by a partner, the agencies we’ve been working with recently all have the bundle installed from day one.

    The mechanics are straightforward.

    • A Map/Reduce script runs on a schedule or on demand, reads from as many as four saved searches, groups everything it finds by project, and writes a custom snapshot record.
    • Each snapshot record is one project + one “event,” plus many supporting fields.
      • An event could be a vendor bill line, a time entry, an invoice line, or an estimate.
    • All the snapshot records in each run are tied together by a second Snapshot Run custom record, which is tagged as current or not.

    What makes it so fast for reporting: the snapshot is only ever as current as the last run. Schedule it nightly and your numbers are at most a day old.

    It is worth noting that the bundle never creates an invoice or posts a transaction. It reads your data and writes records of its own, and those are the only records it ever touches.

    Inside the bundle

    Four kinds of objects come with the bundle, and the first is the one that matters.

    Snapshot record

    The snapshot record (customrecord_sss_proj_snapshot) holds one row per project/transaction line combination per run, carrying the dimension links you would expect: project, client, parent client, item, subsidiary, and a date for the project's last activity.

    The snapshot feature was clearly designed to prize flexibility over usability. The field names on the snapshot record are vague on purpose, so the record can hold whatever you decide to put in it.

    Run record

    The run record (customrecord_sss_run) is both the audit trail and the key you’ll use to group data for reporting. Every execution creates one, logging when it started and finished, whether it succeeded, which cutoff dates it used, and any error it hit. The “Is Current” checkbox marks the most recent successful run for a given script deployment, allowing you to filter out historical records without losing snapshot history. Old snapshots stay in the database while dropping out of your reports. There is also a Mass Update script in the bundle that can automatically prune the Financial Snapshot record based on conditions you configure.

    Cutoff date record

    Two smaller records control how the script filters its searches. The script uses a cutoff date record (customrecord_sss_cutoff) to drop anything that falls after it, applied as "on or before" rather than as a period.

    Data source record

    A data source record (customrecord_sss_data_source) tells the script which field carries the subsidiary, and which carries the date for a given search, since a time search reaches the subsidiary through the employee while a transaction search reaches it directly. Leave one of those fields blank and the script skips that filter for that search, which is sometimes what you want. The last piece is the scripts themselves: the Map/Reduce that does the building, and a mass update script for clearing out old snapshot and run records so the count doesn’t just keep increasing forever.

    How it works

    One cool design choice the Financial Snapshot feature made gives you tremendous latitude to decide what counts as what type of cost for your projects: the column labels on the saved searches are the field IDs on the snapshot record. When the script reads a search result, it takes each column's label and writes the value into the field with that exact ID, so a column labeled custrecord_sss_ss_cost1 lands in the cost1 field, while a column labeled incorrectly sends its value…nowhere.

    Every search also has to return a column called custrecord_sss_ss_key, the grouping key, which is how the script decides which rows belong to the same project. When two searches write to the same field for the same project, the last one wins, with a single exception: for the last-activity date, the script keeps the latest date it sees across all of them.

    Map/Reduce is the script type NetSuite built for processing big volumes of data. The framework (1) breaks the work into independent pieces and creates as many jobs as it needs, (2) runs the map and reduce stages in parallel across separate processing queues, and (3) handles governance on its own, yielding and rescheduling the remainder of a job before it hits a usage or time limit without anyone writing yield logic by hand. The deployment here runs at a concurrency of two and yields after sixty minutes, both of which are the platform defaults, and since writing to a custom record is one of the cheaper things you can do under NetSuite's governance model, far cheaper than touching a transaction, churning out thousands of snapshot rows costs little.

    The bundle sits dormant in most of the accounts we work with. Until you set it up and schedule it, you won’t have any snapshot data. During setup, edit the MapReduce script to point error notifications to your own team, rather than to the original legacy bundle developers.

    Can't I do this in datasets and workbooks?

    Totally.

    SuiteAnalytics Workbook only reached general availability in NetSuite's 2019.2 release. Datasets did not become separate objects from workbooks until 2020.1. And linked datasets, the feature that lets you combine separate datasets on a shared key, came in releases after that. The Financial Snapshot feature dates to a time when these features weren’t ready for prime time, especially for Projects.

    The platform has caught up a good deal since then. A workbook dataset now supports multilevel joins across several record types, custom records included, and you can link separate datasets on a common key, so the modern version of what this bundle does is four datasets, one each for time, cost, billing, and estimates, linked on the project, with no custom record to maintain at all.

    The reason to still be using Project Financial Snapshot today is performance. For agencies with hundreds of active projects (or more), opening a pivot that needs to run and join four datasets can be a profound exercise in patience. In contrast, the snapshot smooshes everything into a flat, custom record. When you combine this with the fact that custom records – blissfully free of the ledger – return data very fast in the application, you get high-performing, only-a-little-bit-and-usually-not-materially-stale analytics.

    At a certain size and scale, snapshot feature or no, you’ll want to consider NetSuite Analytics Warehouse, Oracle's separate data warehouse product.

    Who should care

    For agencies and other services organizations with many concurrent projects to manage, the Project Financial Snapshot bundle should be on your radar. If you’re trying to build a workbook pivot that combines three or more datasets, and every time you make a change you have time to make coffee before your results come back, then consider using the snapshot.

    If working with datasets and workbooks is satisfactory… do that. But the snapshot bundle is a clever solution that has helped our clients and may help you, too.

    Sources

    Related Articles

    an old-timey adding machine
    Industry Insights

    Full-Cycle Accounts Payable in NetSuite with Bill Capture

    Adding Digital Signatures to ACH File Transmission via SFTP
    Industry Insights

    Adding Digital Signatures to ACH File Transmission via SFTP