Exporting data in Catalyst
Exporting data can be useful and can usually be managed in Catalyst under the Excel options > Export options in the various management pages within the user interface, such as Manage Actuals, Planning, or Hierarchy Management.
But what if the dataset is too big to export here?
There's a feature we can enable for you, which will allow you to use the Query Builder to export 50 million+ rows via CSV file. Just submit a ticket and reference this article URL and we'll enable that function ("EnableProfitabilityActualDataQueryModel") for you.
How to use the Query Builder tool
- Navigate to Analysis > Query Builder.
- On the next page, you can either click Apply Filter right away to see all current queries that exist or fine tune your search to look for just one.
- The next page will show you a list of queries you have filtered to, which you can edit, copy, export, subscribe to, or delete via the 3-dot hamburger menu at the right. Otherwise, you can create a new query.
- When you create a new query, toggle to the Query Builder tab to get started.
- On this page you have all of your query options, which allow you to filter to any number of data combinations. We think of it like SQL for dummies :). The end format you'll produce will mimic a spreadsheet, with columns and rows.
- Start by selecting your data source, Financial or Profitability.
- Then select from the attributes list on the left and tick any box you want pulled over into columns. Note that this is often overutilized; you really just need a few subcategories. Remember that this is pulling every field that exists, so oftentimes there are redundancies. Example: Scenario ID, Scenario Short Description, Scenario Name. You only need to tick one of those, not all three. Click Add to Columns when you've finished your selection.
- Once added, your attributes will show up in the Columns section, where you can then sort, delete, simplify, or aggregate them by clicking the option toggle to the left of each item. Note that usually you don't need to do anything here besides delete. You can also add new columns by clicking Add Column at the top right.
- After you've chosen your columns, now select your Conditions. This is where you can really drill into specifics. Click Add Condition and then hover on an Attribute to drill into the condition you want included. Click it to add it to the Conditions module, then click into the row options to choose how you want that condition represented. You're essentially using the row options to search or select the criteria for that line item. (Example: I want to see data from Scenario 2021 for invoices from a certain Company and Customer that contain an Item number of 900).
- When all conditions have been added, click Execute to see your results. Note that if there's an error or if no results show when you expect there should be, you will need to fine tune or change your conditions. Conditions can be finicky, so try something more broad or inclusive, or simply a different search criteria (e.g. choose from Is "In List", rather than (Contains").
- Export your data to Excel or (more likely) CSV. If the data produced is less than 100K records/rows then Excel should work. Anything greater up to 1 million will require a CSV output. If more than 1 million records need to be exported, you'll need EBM Support to enable the Profitability Actual Data Query Model option, which will then allow you to export 50 million+ rows via CSV. If you run into issues, the only other option is to narrow your criteria and search to a smaller dataset and export each dataset one at a time.
