Managed service plans
We run your environment, with 24/7 monitoring, maintenance, and proactive improvement.
Reporting in under a day, from three
The warehouse was already moving to Azure Synapse. What was missing was everything around it: getting the vendor files, loading them without taking reporting down, and adding a new feed later without writing new code to do it.
Reporting turnaround
1 day
Down from three business days, and under one in practice, on a warehouse loaded automatically rather than by hand.
- Before
- 3 business days
- After
- Under 1 day
- Platform
- Azure Synapse
In short
What happened
A large international pharmaceutical company was accumulating data from multiple sources and granting access to different departments at different times. It moved its data warehouse from on premises to Azure Synapse and asked Fortified Data to automate the processing of data into it. Fortified Data used dynamic SQL and metadata to build a template the client can use for new tables, divided the load into staged schema layers so business users stay up during it, and built the file handling around it: retrieving files from the vendor's FTP site, placing them for load, and archiving them for long-term storage. Reporting time fell from three business days to under one.
The build-it-or-buy-it problem, and why it is really about flexibility
Organizations that set out to build data warehousing solutions usually discover two things: the hardware has to be scaled for peak usage, and a considerable amount of resource has to be dedicated to managing the system. It is the classic build-it-or-buy-it dilemma, and it takes time and attention away from the data and the applications that were the point. Buying offers more than ease of management: a platform that scales up and down, and can be turned off entirely when reporting is not needed, without altering the warehouse underneath it.
This company was reconsidering its reporting and data analysis systems. It was accumulating large volumes of data from multiple sources and had to grant access to different departments at different times for different business needs, which is a flexibility problem before it is a performance one.
It had worked with Fortified Data before, so it came to the same team to determine and meet the requirements for deployment and for long-term use rather than for the migration alone.
What the solution had to be
Six requirements, established before anything was built
The first step was establishing the client's key requirements. None of them is a stage: the solution had to satisfy all six at once.
Technology
Completely cloud-based in Azure. Not a hybrid arrangement with a component left on premises to be dealt with later.
Extensibility
Handle the current syndicated feed files, extend to new feeds without anyone writing new code, and allow either a single file or multiple files for a given table.
Uptime
The load process had to minimize downtime for business users, and ideally be transparent to them. A warehouse that is unavailable while it loads is a warehouse with a nightly outage.
Reporting performance
The data model had to be easy to leverage in Power BI, which is where the business actually reads it.
Query flexibility
Business users wanted good performance through tools like Power BI and access to the raw data from the source files. The solution had to serve both, rather than picking one and calling the other an edge case.
File management
Beyond loading the data: connecting to the vendor's FTP site, retrieving the files, placing them in the right location for load, and archiving them appropriately for long-term storage.
What changed
- Reporting from three business days to under oneA vast improvement in data availability, and the only figure the published case study states.
- A template for new tables, not a ticketDynamic SQL and metadata produced a template the client uses itself for new tables, which is what the extensibility requirement was actually asking for.
- A self-cleaning load that recovers itselfDesigned for supportability: if the client hits connectivity issues, recovery is straightforward rather than a call.
- A staged load that keeps users upThe load was divided into stages and schema layers, and a final layer of views presents a user-friendly dashboard for on-demand reporting.
- Raw vendor data, ingestible with minimal transformationEnhanced filtration and an MDI process to increase data ingestion, so both the reporting users and the people who want the source files are served by one warehouse.
Three ways to work with us
On-Call DBA
A guaranteed 15-minute emergency response plus monthly on-demand hours that bank when unused.
Database consulting and projects
Project-based engagements for migrations, performance tuning, data modernization, business intelligence, and high availability and disaster recovery.
Is your reporting waiting on a load?
Reporting latency is usually blamed on the warehouse and usually caused by everything around it: how the files arrive, what happens while they load, and who has to write code when a new feed turns up. Those are answerable separately from the platform decision.
Let us show you what's possible.