Navigating Complexity: Empowering Data-driven Decisions in Portfolio Management
We’ve embarked on yet another project recently, one that poses our biggest challenge to date.
We have a long-standing client for whom we developed a web-based portfolio management system over 20 years ago. Since then, we’ve continuously improved and expanded its functionality by incorporating new modules, ETL processes to import data from external sources, and various types of reports and exports. Recently, the client presented us with an intriguing request.
The client emphasized the need for extensive flexibility across their portfolio management teams, including S&A, CFOs, and Fund Admins. They required seamless addition/removal of fields and enhanced data uploading capabilities. Additionally, they expressed interest in self-service business intelligence tools for end-users, such as PowerBI, MS Excel, and Tableau. Their objective was to empower users to manipulate existing data dynamically to address a wide array of inquiries pertinent to their areas of focus, aiding management and strategists in making well-informed decisions.
To accommodate these evolving needs, the client decided to democratize their data by establishing a Snowflake Enterprise Data Warehouse. One segment of this warehouse contains raw data synced daily from the legacy platform. We devised an ETL process to populate a newly defined database within the same warehouse, structured with facts and dimensions tables. The ultimate goal is to leverage the data from all client’s systems and bring it into the data warehouse allowing users to seamlessly combine and analyze information from various sources.
Within Azure Analytics Services, we defined an OLAP cube. This cube interfaces with data sources to process raw data, performing aggregations and calculations for associated measures. In the project’s initial phase, drawing from our legacy platform expertise, we aimed to define a comprehensive set of DAX expressions within the cube. These expressions would enable end-users to create reports using Excel independently.
A significant challenge arose from the legacy system’s heavy reliance on complex SQL Server stored procedures for reporting. We had to reimagine these processes within the cube’s star schema using DAX expressions. Similarly, the Finance Formulas engine built in the legacy system, which allowed users to define various financial formulas, posed a challenge. We had to replicate this functionality without stored procedures, opting instead for cube-based solutions.
As a proof of concept, we started with a simple task: creating an Excel report from the cube displaying Sales and EBITDA per portfolio company. This report offered filtering options by fund, with subtotals for each fund and the selected period.
Throughout the process, we iteratively improved the EDW structure and Cube definition. We gradually tackled more complex reports, incorporating additional filters, currency conversions, and IRR calculations. The journey was fraught with challenges, demanding continuous learning and problem-solving.
The outstanding performance and scalability of the AAS cube impressed everybody involved in this project. This new infrastructure delivers the results to the end user almost instantly while producing similar output in the legacy system using traditional relational database queries was few times slower.
We intend to share our experiences through a series of blog posts, detailing the significant challenges encountered and our solutions. Stay tuned for more insights.
By the conclusion of Phase I, we had developed a fully functional Cube with defined DAX expressions. The client successfully utilized it to recreate most reports from the legacy system in Excel, while also introducing new variations and details as needed.
Looking ahead to the next phase, our plans include further expanding the EDW structure, adding new DAX expressions to the Cube, migrating existing ETL processes from the legacy system, and integrating new ones from external data sources.