Centralizing law firm data for actionable reporting
This full-service law firm has hundreds of lawyers operating in more than a dozen offices across the U.S. They are focused on client service and leveraging technical, industry and legal knowledge. Their resources have led small to large companies, at every stage in their development, and their hands-on experience helps them to serve a diverse client base.
Overview
THE CHALLENGE
Our client was already a trailblazer in the cloud environment for law firms, but they were looking for the tools to be trailblazers in the cloud in general.
THE SOLUTION
Ollion evaluated and revamped this law firm client's data warehouse structures and ETL processes for a more modern, relevant future data architecture.
THE OUTCOME
We enabled the client to internally manage data and generate reports. They were trained how to use and continue to develop the new data platform so they can make data-driven decisions in near real-time, responding to any changes in the environment. This enables them to stay competitive and gain some distance from competitors that aren’t currently engaging in similar practices.
The challenge
This law firm engaged Ollion to assist in the analysis and modeling of their existing enterprise data platform, Snowflake, related to the core business functions of the firm. In addition, the client requested Ollion’s assistance in evaluating their current data warehouse structures and ETL processes for a more modern, useful and relevant future data architecture. Ollion partnered with the client to identify and develop a modern future state architecture data platform and data model that leverages industry best practices and supports the long-term vision for their data and analytics.
Some challenges of client faced included:
- Our client needed to generate diversity reports for their own clients on a per-request basis. Reports would take weeks to manually generate between aggregating data from disparate sources, cleaning and presenting that data.
- Metrics were not consistent across the organization, and formulas could differ by team.
- This law firm wanted to ingest data from a firm management software that they previously had not utilized.
Our team conducted a one-week assessment to confirm the level of effort and scope, as well as the reusability of the client data pipelines, data models and cloud infrastructure in support of their more useful and relevant future data architecture. This step helps Ollion provide the client with an accurate man-hour assessment to complete their project and avoid any change orders toward the conclusion of the project. It was determined that Ollion would assist in building a foundational Snowflake data platform including a data ingestion process, enterprise data model and data visualization solution.
Within the previous year, the client had contracted a consulting firm to address the same problems but were unsatisfied with the results. They were looking for a collaborative partner who would walk them through a solution that would fit their needs, from development to delivery.
The solution
The Ollion data discovery team concluded that a revamped ELT workflow would be the best solution to address the client’s pain points. Using ELT, we could leverage Snowflake’s cost-effective storage costs by landing all the raw client data straight into the data warehouse, without doing any transformations first. This keeps development costs down because we don’t need to create proprietary scripts to extract and transform data before moving it to the data warehouse. When utilizing the ELT model, dbt makes for a fantastic orchestration tool that natively supports Snowflake. In addition to easier pipeline development, orchestration is a less complex task with a tool like dbt as all transformations are tracked in one software.
We also created a custom script in Fivetran to ingest an employee management software’s data. With that, our client could track the efficiency of employees, hours billed, hours collected and revenue. Previously, our client needed to gather this data by hand – or possibly not at all. Fivetran allows you to schedule the ingestion process, so we were able to keep the financial data up-to-date almost hourly.
Ollion created a layered warehouse for the client, utilizing a raw database for landing data directly from the source into the warehouse. Using dbt, we transformed that data into a cleaner version of the raw data in an intermediate layer. We scheduled "jobs" in dbt to carry out transformation tasks, taking advantage of cloud automation to reduce the overhead in executing hourly tasks. Then, using dbt again, we created a production layer to further clean the data and enhance the data with the client’s business logic. This solved the inconsistent metric problem because we stored business logic in the transformation workflow, allowing all data consumers to see the same information. This also made it easier on the data team to keep track of, and update, metrics across the business.
Leveraging Power BI and Snowflake integration
Finally, a dashboarding workflow was created for the data team. Power BI provided the platform to distribute dashboards on an enterprise level, supported CI/CD development and natively integrated with Snowflake software.
Our client’s data team was small and needed a solution that would be easy to manage. They put significant priority on iterative development and restricted permissions when generating new reports, at least while the software was being adopted. We created a development environment where new dashboards could be generated and formatted, and this was restricted to the data team. Dashboards are then promoted to a test environment where the underlying production data could be tested in visuals and charts, where some end users outside of the data team could verify data accuracy. After receiving approval, dashboards could be moved to the production environment where they are distributed across the enterprise or to clients.
The outcome
Our client had diversity data that needed to be delivered to their clients on a regular basis. That process would take weeks to complete between gathering data, cleaning and verifying it, and then visualizing it. The HR team would commit significant resources to this, plus many hours of work, and may need to do it all over again. While learning about this pain point, the Ollion team received different answers about how the process worked.
Because we were able to create a comprehensive data model that included reporting tables with the grain the client needed, these reports could be updated daily and our client could answer a request for diversity data by sharing the report with their client within the day.
The data team was then able to build trust across the enterprise as the source of truth for financial, diversity and performance data. Data is centralized in their Snowflake Data Cloud, metrics are stored in the transformation software, and they are the owners of internal and client facing reports. This aggregation of data across a 1000+ employee firm can be managed by a small internal team of passionate data analysts. Plus, thanks to our training experience, we were able to set them up to manage and further develop their environment on their own. This helped the firm overall regain trust in data consulting and they were free to benefit from data-driven decision-making. Upper management satisfaction prompted the data team to start hiring to meet demand for internal reports.
