A team from Microsoft and Maria Furniture Factory jointly developed a cloud-based prototype of a reporting system. This case study explains in detail the problems Maria Furniture was facing with its current infrastructure and how our combined efforts improved its architecture over the course of a hackfest.

Project team:

  • Kirill Khromov – Deputy IT Director, Maria Furniture Factory
  • Alexey Mudretsov – Lead Developer, Maria Furniture Factory
  • Jason Poon – Senior SDE, Microsoft
  • Alexey Bokov – Senior SDE, Microsoft
  • Alex Belotserkovskiy – Technical Evangelist, Microsoft Russia

Customer profile

Maria Furniture Factory designs, manufactures, and sells custom kitchens. Maria offers almost 50 kitchen design styles ranging from classic to modern at different price levels. Maria uses modern equipment and new technologies to produce the highest quality kitchen cabinets and components.

Problem statement

Maria’s reporting system is used by operators to receive, review, and send business reports. Users can request a variety of reports, with each supporting different parameters such as date, currency, and so on. Once generated, the report is delivered to the end user by email. If an end user has requested multiple reports, they are consolidated and sent in one email. These reports are generated on a schedule (two times per day) and can be generated ad-hoc.

Figure 1. Architecture before the hackfest

The reports are generated through a series of manual steps:

  1. Manual execution of Visual Basic scripts (VBS) and SQL jobs to generate the report.
  2. The generated reports are manually reviewed by an operator. If the reports are deemed to be incorrect, the operator informs the developers whereupon they will fix the underlying data issue.
  3. The operators send the report to the appropriate audience.

Problem statement

The workflow for generating business reports is not automated, and operators and developers are inundated with a series of manual operations.

Goal of the technical engagement: Automate the workflow as much as possible. Make the solution agile, stable, and scalable using the PaaS services.

Phase 1 - The project brainstorm

Figure 1 shows the typical workflow of the report lifecycle. Here are the details:

  1. User creates the report delivery requests. Delivery requests consist of different parameters such as date and format. Request is placed in the SharePoint archive; SQL job and VBS are created for every request.
  2. Twice a day, all of the created SQL jobs and VBS are executed.
  3. VBS requests the reports needed from the SQL Server Reporting Services (SSRS).
  4. Reports are sent to the operator for the reviewing step.
  5. Operator checks if everything is OK with the reports.
    • If OK, reports are saved into storage and are emailed to the recipients list.
    • If NOT OK, operator breaks the workflow, forwards the report and delivery request to the developer for fixing, and restarts the process from the beginning.

During the brainstorming session’s first phase, we identified such issues as:

  • Maintenance. A lot of nearly identical entities (jobs and scripts) are being created, stored, and executed. That leads to the high cost of maintenance and changes as well as possible issues with scalability.
  • Lack of automation. There is no interface that allows the operator to automate such actions as contacting the developers, generating reports, and so on. To form the “bug report” manually is neither a scalable approach nor a safe one.

At the end of Phase 1, we identified those as areas in which we should try to improve the workflow.

Phase 2 - Architecture brainstorm

Phase 2 began with brainstorming about the draft of the new architecture—services that can be used, changes in the workflow, the cost of the changes and maintenance.

The next three figures show the architecture development through Phase 2.

Figures 2 and 3. Drawing the architecture drafts

We decided not to do anything with the SSRS itself and concentrate on the development efforts instead.

Regarding the architecture, nobody had any doubt that the new architecture should be scalable, fast, reliable and simple. The architecture should be loosely coupled and should not have any complex communication workflow between any of the components. You can call it a microservices approach—there should be as few as possible functional simple blocks.

An obvious candidate for these blocks was the Azure Functions service. Azure Functions is a solution for easily running small pieces of code (“functions”) in the cloud. You can write the code you need for the problem at hand, without worrying about a whole application of the infrastructure to run it. It supports C#, F#, Node.js, Python, and PHP. Serverless computing was exactly what we looked for.

Unfortunately, we had only so much time to spend not only on researching other options (Azure WebJobs and the Logic Apps feature of Azure App Service), but on hacking the prototype as well. We ended up focused on the Azure Functions-based prototype.

The final architecture consisted of:

  • Virtual machine with the SSRS installed and some test reports (not in Azure).
  • Three Azure Functions:
    1. The report extraction and processing.
    2. Merging/emailing the reports.
    3. Had to be called when operator notices something is wrong to stop sending email to end user (and the workflow itself).
  • Azure Storage blobs. Storage for reports; every report has the metadata dictionary that contains the ExecutionId (the field that is used for merging reports from the single delivery later).
  • Azure Storage tables. Storage for reports information; every row contains the link to the report blob with the Shared Access Signature.
  • Service Bus queue. Once the reports from Delivery are extracted and put into the Azure Storage blob, Function 1 sends the ExecutionId to the queue. Function 2 listens to the queue, gets the message with the ExecutionID, looks for that ID in the Azure table, downloads all of the blobs with it, merges it into the email message, and forms the delivery.

Figure 4. Azure services applied to the architecture

We divided into two teams:

  • Team 1 (Azure Function – SQL Server Reporting Services, Azure Storage, Service Bus queue)
  • Team 2 (Azure Function – email, processing), infrastructure (email and so on), DevOps

We chose Gitter (team collaboration), Visual Studio 2015 (development), and GitHub (source control).

Phase 3 - Development

During the hackfest, we created a workflow of functions. The first function is triggered either through the timer or through the bot (via Slack using Microsoft Bot Framework). The function makes a web service call to SSRS, generating the relevant reports, saving them to Azure Blob storage, and placing their SAS URLs into Azure Service Bus whereupon a second function would be triggered.

The second function emails the operators the report and gives them a time window in which they can inspect the reports and respond. If the report is found to be malformed, the operator can click the link in the email which in turn is another Azure function that would mark the reports with an error status. If the time window has elapsed without any action from the operator, the message will appear on the Service Bus for the last function to process. The last function will send the reports to the distribution list.

Figure 5. The whole workflow using Azure services

Figure 6. Development team

Development team

Learnings

What is obvious but often forgotten:

  • A hackfest is similar to a hackathon, but it is not the same. It is much more intensive and focused. Therefore:
    • Do not spend a lot of time on something that isn’t working. See if any other solutions/ways exist. Example: You need to send the email. The first thought would be to use public/your own SMTP server. That is a correct assumption that it is a working solution, but it often comes with such issues as certificates. Use email services such as SendGrid. It is a recommended, reliable, and simple approach, and you do not need to set up the infrastructure or dig into security issues.
    • Exception: If you hack something like the protocol adapter or something small where you can focus only on one component.
  • Do not brainstorm too long. The longer you brainstorm, the more services you throw into consideration and the less time you have to develop, fail fast, and start again. The goal of the hackfest/hackathon is not the production system, it is to prove the hypothesis.
  • If you see that you can set up the DevOps/whatever workflow, do it, but do not make it the bottleneck. Let a teammate set it up while others are doing the coding. Often, someone pauses their work just to “make it right from the beginning” while a teammate sets up the DevOps. Do not wait.

Azure-related learnings

  • If you are going to email something from the cloud, consider options twice. The best options would be to use SendGrid or the external email server, but even considering the internal Azure email deployment can be cumbersome. That traffic can be marked as bad by Azure services and your workflows will be blocked.
  • An Azure function can do a lot more than we originally surmised. We had initially planned to create a function for each different report that needed to be generated. We were able to write one function that would generate them all and perform the necessary consolidation.
  • It is difficult to test Azure functions. Because we wished to collaborate on the same set of code, we set up deployment of Azure functions from GitHub. However, we’d often end up with a string of tiny commits that would attempt to fix compile and runtime issues based on trial-and-error.
  • General lack of documentation around usage of csx (scripts), supported bindings in the csx function declaration, whether to create a new function within an existing function app or to create a function per function app.

Note: A few days after our hackfest came the announcement of Visual Studio Tools for Azure Functions. Awesome tool.

Some code

We include a few pieces of code here so that you can better understand why we believed that functions are both efficient and simple. I am doing that with regard to the workflow steps.

The report extraction:

HttpWebRequest request = (HttpWebRequest)HttpWebRequest.Create( "http://../reportserver/?/TestReport/ReportTest&rs:Format=PDF&type_per="+typeper);         
request.Method = "GET";
request.UseDefaultCredentials = false;
request.PreAuthenticate = true;
request.Credentials = new NetworkCredential("login", "pwd", "domain" );


The report processing:

HttpWebResponse response = (HttpWebResponse)request.GetResponse(); 
    log.Info( response.ContentType.ToString());
    Stream stream = response.GetResponseStream();
    string fileName = response.Headers["Content-Disposition"].Replace("attachment; filename=", String.Empty).Replace("\"", String.Empty);
    CloudAppendBlob blob = container.GetAppendBlobReference(fileName);

    blob.UploadFromStream(stream, null);


Here, we have the Azure Storage C# SDK code for putting the report into the blob. That is definitely possible with functions. But what if someone does a lot of work for us and we could just use something ready out-of-the-box…

Report metadata - saving into Azure Table:

log.Info($"Adding entity");
            tableBinding.Add(
             new Report() { 
                    PartitionKey = partitionKey, 
                    RowKey = fileName, 
                    url = GetBlobSasUri(container, fileName),
                    OperatorMail = "@" }
                );


Here we see the functions’ simple magic. Every function can have a set of so-called bindings, which is basically the connector to some service + alias for that (for referencing in the code).

Instead of writing the boilerplate code, we use what the platform exposes. It is both convenient and stable—stable because if we do not care about the library version, for example (sometimes we do), then we can be sure that the functionality will work on the most up-to-date version.

Of course, not everything is exposed through that binding thing, so if we need to use advanced functionality (like generating Shared Access Signature), we can do it ourselves. By the way, the SAS generation looks like it can be the dedicated function. You can find the code here.

Every binding has its own configuration:

{
      "name": "outputSbQueue",
      "type": "serviceBus",
      "queueName": "maria-queue",
      "connection": "maria-sb-connection",
      "direction": "out"
  }


Direction “out” means that the functionality exposed can be used for sending something to that service, like putting the file into the blob, or a message into the queue. “In” means the opposite.

What is interesting here is the connection. It is a placeholder for the connection string that is stored in the AppSettings. Because Azure Functions is part of Web Apps, we can leverage that functionality, even sharing the same AppSetting among functions.

By using the Service Bus queue binding, we are able to enqueue the message with one string of code:

    outputSbQueue = partitionKey;


Results of the technical engagement

At the starting point, the Maria reporting system was implemented as a workflow that can hardly be described as automated. Each delivery request has its own set of VBS and SQL jobs. Every set is executed two times per day. Once the report is generated (script was executed successfully), it is reviewed by the operator. If there are any issues, the operator marks the delivery as wrong and sends the request to the developers. Once it is fixed, the delivery is created again.

Each mistake, each issue with the report or delivery, should be directly communicated by the operators to the developers. Automation of the reporting workflow would allow that to be more flexible, reliable, and fast.

The goal of the hackfest was achieved—we did automate the workflow by removing all of the scripts and jobs and migrating to the cloud solution. Its architecture was tightly coupled—in the current draft of the new architecture, it is not. Parts of the system are not interconnected, can be scaled separately and, in case of error, the system will remain functional.

We had an extra goal we planned to achieve if time allowed—to implement the operator interface for reviewing the deliveries. We did not have time to fulfill that task, but we proved that workflow is working with the new setup, and there is the basic interface for that system. Future plans are to develop that interface and load-test the system, after which the pilot can be done with real users.

The Maria team expressed a positive opinion about how the cloud platform works. The technical engagement allowed them to understand how the system can work with a new approach, and there were nice learnings for both the Microsoft and Maria teams with regard to Azure Functions capabilities, serverless computing, and the SSRS development model, among many others.

“It was a very interesting experience. During the event, we exchanged experiences (we learned and we taught) and implemented the prototype of the solution. The format of the event—the opportunity to work together, communicate with the experts—is very useful and makes our lives much easier as we can go faster, instead of spending a month to dig in to the documentation.”

— Kirill Khromov, Deputy IT Director of software development, Maria Furniture Factory

The future plans of Maria for using the Azure platform include completing work on the new reporting system pilot, testing it, and researching and assessing the integration into their current system.