This tutorial provides key guidelines and best practices to optimize Catalyst's performance with large datasets. It also includes tips for improving overall software usability.
This articles contains the following topics:
Performance and Usability Best Practices
These guidelines, disclaimers, and best practices aim to uphold the speed and efficiency of your Catalyst instance when dealing with extensive datasets. Additionally, they encompass recommendations for enhancing the general usability of the software, ensuring a comprehensive approach to maximizing performance and user experience.
Archive your unused scenarios
- Inactive or unused scenarios should be archived regularly, as they can impact the overall speed of your instance and increase the time it takes to process your data.
As an estimate, maintaining over 10 unarchived scenarios may increase the time it takes for hierarchy changes to populate, data rebuild times, and cube rebuild times to 30-45+ minutes
Excel file optimization
- Recognizing Stale, Corrupt, or Degraded Files. Identify conditions such as extensive data rows, numerous external connections, and prolonged existence without updates.
- Crashes Unrelated to EBM or Catalyst. Excel can crash for reasons unrelated to EBM or Catalyst issues.
Excel file best practices
- Routine Assessments
- Regularly refresh data connections for up-to-date information.
- Check and update OLAP formulas to reflect changes in data structure or source.
- Periodically review and validate external links and connections to prevent disruptions.
- Keep the file size below 50mb for optimal performance.
- Eliminate unnecessary worksheets to streamline the file.
- Audit and review cell formulas for errors or inconsistencies.
- Assess and optimize PivotTables and PivotCharts.
- Evaluate the overall workbook structure and rearrange as needed.
- Ensure compatibility with the Excel version and features being used.
- Always save and open files to Trusted Locations
- Limit Excel slicer use
- Avoid duplicate connections
- Avoid unnecessary, private, or unpermitted external data connections, queries, or links
- Address issues like hidden tabs or excessive file size (50mb or greater)
- File Health and Performance
- Regular updates are crucial. Learn how to refresh your file.
- Utilize Cube Updater for old EBM code.
- Maintain vigilance against hidden or locked elements.
Optimizing Excel files is vital for avoiding common issues like file breakages, permissions issues, or size limitations. For details about identifying files like these and fixing corrupt, stale, or degraded Excel files, check out comprehensive tutorial here.
Spread data across multiple hierarchies
- Understand when it's time to create a new hierarchy. Rather than continuing to add sub levels to your hierarchies to account for additional data, maybe the data could be grouped under its own category or hierarchy.
As an estimate, hierarchies with more than 50,000 records may increase the time it takes for hierarchy changes to populate, data rebuild times, and cube rebuild times to 30-45+ minutes.
Limit transaction attributes when/where able
- A common example of a Transaction Attribute is Invoice Number. But it's really any bit of data you usually store related to a single transaction (e.g. think of the column headers in your transaction records, such as location, customer name, invoice number, invoice date, ship to date, warehouse, etc). The greater the number of Transaction Attributes the bigger the lift in Catalyst, meaning it's important to only bring in TA's that are useful. Some options to pare down your TA's are:
- Remove any that you're not interested in.
- Create a new TA hierarchy instead.
- Concatenate popular TA's into the ID, rather than storing the data as a column in a table. Eg. CustomerName+GeographicalLocation.
As an estimate, instances with more than 5 transaction attributes per account may increase the time it takes for hierarchy changes to populate, data rebuild times, and cube rebuild times to 30-45+ minutes.
Use single currency attributes if possible
- Using multiple currencies essentially doubles your data set upon each new currency introduced to the equation, which can definitely impact performance. If you have a business that trades in different currencies then there's not a lot you can do to limit these in Catalyst.
As an estimate, instances with more than 5 currencies may increase the time it takes for hierarchy changes to populate, data rebuild times, and cube rebuild times to 30-45+ minutes.
Work with us to reduce total actuals/planning records
- Eventually you may not need to house all of your data in Catalyst and only the most relevant or recent data. If so, we can help decrease that volume, but in some cases you won't have that option. As a general disclaimer, once your data set reaches a certain point the system will simply take longer to load and sift through it all.
As an estimate, instances with more than 1 million records in actuals and/or planning modules may increase the time it takes for hierarchy changes to populate, data rebuild times, and cube rebuild times to 30-45+ minutes.
Create new year scenarios prior to booking or transacting to those years
- To ensure smooth financial transactions and bookings in Catalyst, it is essential to create new year scenarios before conducting any transactions for those years. In financial systems and ERPs, the capability to bill or transact in future years exists, but it is crucial to establish these years as actuals scenarios in Catalyst beforehand. As a best practice, consider creating upcoming year scenarios as the current year approaches its end. For instance, if you're finalizing orders to be fulfilled in the upcoming year, creating the corresponding scenario in Catalyst beforehand prevents potential system errors or disruptions that may occur if records are ingested without the necessary scenario setup.
To learn how to create a new Calendar year and Actuals scenario, check out our quick guide here.
Create new companies in Catalyst prior to booking or transacting to those companies
- To streamline company transactions in Catalyst, it's imperative to create new companies in advance before initiating any bookings or transactions with them. While your source ERP may allow bookings for new companies, it is essential to establish these entities as active companies within Catalyst beforehand. Adhering to this best practice ensures a seamless integration of data. For instance, if transactions for these companies are recorded in your source ERP without corresponding setup in Catalyst, the system may encounter errors or disruptions, emphasizing the importance of proactively creating new company profiles to maintain system integrity and functionality.
To learn how to create a new company in Catalyst, check out our quick guide here.