Tailoring Data Solutions for Seamless Integration: A Case Study with Costpoint, Smartsheet, and more
In an increasingly data-driven world, businesses often find themselves swimming in a sea of data without the means to effectively harness it. One such company reached out to us with this exact challenge. Our custom solution for them demonstrates the essence of what we do: creating tailored, interoperable, and agile systems that provide actionable insights. This blog post breaks down how we used a diverse range of tools—from Deltek Costpoint's Business Intelligence (BI) to Microsoft PowerAutomate and Python—to streamline operations for our client.
The Problem: Fragmented Systems
The client already had an array of tools at their disposal: Deltek Costpoint for enterprise resource planning, Smartsheet for daily operations, and Microsoft Office tools like Excel. The challenge lay in having these systems communicate with each other efficiently, a common issue many companies face.
The Solution: A Multi-Faceted Approach
Step 1: Data Extraction with Deltek Costpoint's BI
To kick things off, we leveraged Deltek Costpoint's BI to extract targeted data queries relevant to the client. These queries were then scheduled to be sent automatically via email to an Azure cloud Windows 11 virtual machine.
Step 2: Parsing Attachments with Microsoft PowerAutomate
We utilized Microsoft PowerAutomate to parse these email attachments and save them to OneDrive. From there, the data was prepared for ingestion into a MySQL-based "Data Warehouse."
Step 3: Data Transformation and Integration
After the data was imported into the Data Warehouse, we manipulated and related the data to better align with the client's needs. This transformed data was then fed into the client's existing tools.
Step 4: Smartsheet and Excel Integration
We collaborated with the client's in-house Smartsheet expert to develop queries ingestible by Smartsheet’s Data Shuttle. For a smaller subset of users, the data was displayed through Excel, using PowerQueries to populate dashboards that featured key performance indicators (KPIs) and other vital metrics.
Step 5: On-Demand Refreshes via Python
We built a Python program that enables "on-demand" data refreshes. By logging into Costpoint and running the batch on demand, the company could update their data as needed.
Step 6: Future-Proofing
The system was designed with scalability in mind. Transitioning to other tools like PowerBI is supported, enabling the client to adapt as their data needs evolve.
Why It Worked: Empowerment through Data
By integrating these tools, we didn’t just solve the client’s immediate problem; we empowered them to ask new questions and see their data in a fresh light. This iterative approach is central to our philosophy: we build solutions that grow with our clients, rather than forcing them into a one-size-fits-all model.
Conclusion: Iterating for Success
This case study encapsulates our core principles: custom solutions, seamless integration, and the power of simplification. We are committed to helping our clients continually improve and ensure that their data evolves with them. If you're grappling with similar challenges and are seeking tailored solutions, we invite you to contact us to discuss how we can assist you in your journey toward more efficient and insightful data management.
Turn10 Innovations will continue to be the partner for businesses seeking to leverage their data in effective and transformative ways. Because after all, it's not just about collecting data—it's about making it work for you.