17413 107 Ave NW #100, Edmonton, AB

Case Study: Custom ETL & Data Warehousing for Business Intelligence Needs

Custom ETL

Custom ETL & MSPBots – Integration and Capabilities

Introduction: At Framewerx, we believe in providing our clients with unparalleled transparency when it comes to their IT managed services. Inspired by this commitment, we embarked on a transformative journey to create custom client dashboards accessible through our website. In this case study, we explore the strategic use of MSPBots, a powerful business intelligence tool. Also, the development of a customized ETL process to fill integration gaps, offering clients a holistic view of the value derived from our services.

Utilizing MSPBots for Business Intelligence: To handle the intricacies of business intelligence logic and 3rd party integrations, we leveraged MSPBots—a versatile tool that allowed us to pull and consolidate data from various sources. Key metrics, such as ticket portal data, average ticket response and resolution times, SLO incident percentages, customer satisfaction data, hardware lifecycle data, and backup information, were seamlessly integrated. Thus providing valuable insights into the performance and impact of our IT managed services.

Addressing Integration Gaps: While MSPBots provided robust integrations, there were gaps that needed attention. Notably, certain tools for email filtering, cybersecurity, and other critical aspects were not seamlessly integrated. To bridge these gaps, we took a proactive approach by developing our own Extract, Transform, Load (ETL) process using SQL Server Integration Services (SSIS) and a custom SQL Agent job.

 

ETL Process

The Process: Our ETL process involved the creation of a sophisticated SSIS procedure designed to feed data into our local database. This data was then seamlessly integrated with MSPBots through a SQL Agent job. This strategic integration ensured that essential data from previously unlinked tools became part of the comprehensive analytics provided by MSPBots.

Custom Widgets for Client Dashboards: With the data integration gaps successfully addressed, we were able to create custom widgets for our client dashboards. These widgets offered a more holistic view of the value clients were receiving from our IT managed services. Clients could now access real-time insights into the performance metrics that mattered most to them, fostering a deeper understanding of the impact of our services on their operations.

Conclusion: The creation of custom client dashboards represents a significant milestone in our commitment to transparency and client satisfaction at Framewerx. By strategically utilizing MSPBots and addressing integration gaps through our custom ETL process, we’ve empowered clients with a comprehensive and real-time view of the value they derive from our IT managed services. As we continue to innovate, our focus remains on providing clients with the transparency and insights needed to make informed decisions for their IT infrastructure.

 

 

Read more about our other projects and applications here!

Like what you see? Follow us on Twitter, or subscribe to our newsletter.

Client Testimonials
Steve Brittain
Steve Brittain
Municipal Manager, Select Engineering Consultants
Read More
Dan and his team have been able to meet all Select Engineering challenges with minimal disruption to the business; from fast access to corporate resources thanks to our DaaS solution, to collaborating with internal and external parties utilizing our manager VoIP phone system or accessing SaaS solutions using the fully integrated security solutions.
 Daniel Elliot
Daniel Elliot
Operations Lead, Omni-McCann Geoscience
Read More
Framewerx rescued my company from obsolete digital file management and brought us into the 21st century with cloud services in an efficient manner. Kalyn and his team at Framewerx were able to provide solutions for merging two corporate entities with vastly different digital file management and email servers in a seamless manner while providing top quality support during and after the transition to all employees. They provided training so that everyone understood new operating protocols and software changes. Framewerx has been very responsive and has always been keen to work with us to understand our business, IT needs, and financial restrictions. We continue to work with Framewerx for all of our IT, phone system, and data storage needs.
 Karim Mouait
Karim Mouait
Vice President, Cornerstone Insurance Brokers Ltd
Read More
We used Framewerx to transition our traditional office to a virtual one. This included VOIP, call recording, video conferencing, VPN, remote working from home accommodations etc. Dan and his team did a fantastic job. They are extremely responsive and client service oriented.
 Kimberly Maber
Kimberly MaberManaging Partner, Brunsdon Lawrek & Associates
Read More
I have worked with Kalyn and his team for many years. He has readily handled all of our IT needs, from designing our IT strategy to our day to day computing requirements and support. Kalyn takes the time to listen to and understand our needs, delivering cost-effective and innovative IT solutions for our small business. I highly recommend working with Kalyn at Framewerx.
Services we provide
Latest Blog Posts

A UPS (Uninterruptable Power Source) is like your devices guardian angel, keeping your electronics safe from power surges and power outages alike. …

In today’s ever-evolving digital landscape, the need for robust cybersecurity measures has never been more critical. That’s why we’re thrilled to announce …

Skilled professionals (most commonly IT security experts) conduct penetration testing, often referred to as pen testing, as a simulated cyberattack. They employ …

Inventory Management

Inventory Count Tool

Enhance inventory auditing and future reconciliation efforts with a versatile mobile and web-based application.

Fabrication & Progression Management

Shopwerx

Efficiently oversee and optimize the fabrication process of steel components, meticulously tracking and managing the progression of fabrication tasks from staging, prep assembly, welding, QAQC, all the way to shipping.