Business Intelligence isn’t a panacea, yet it is very unwise to skimp on analytics during the dark times. Economic downturn is the right moment for smart decisions. So, let’s see how you can make smart decisions without developing a hole in your budget using available legacy systems to your best.

In this article, I’ll go through a real case of our corporate insurance client. The important thing is that both BI and ETL are industry-agnostic. So, this article will be well worth its reading time for everyone, regardless of their business area, if you have some basic understanding of how complex reporting is done.

Today’s market of BI solutions has a lot to offer. User-friendly interfaces, stunning functionality, lightning fast performance are all there for you.

Problem #1 is how much it will cost you. For huge corporations, though, it might not be a problem at all. As our client does use Power BI for different projects, the cost is not an issue in this case. So let's move on to see other bottlenecks you may face when using Power BI for corporate business reporting.

The second issue is the functionality which may not suit your tasks at all. Say, this client of ours has millions of rows in its databases in the US and UK. The data volume it generates is outrageous for some projects -- data loads may take up to 15 hours for monthly reports -- and Power BI may not be an option for that.

Source: learn.microsoft.com

Last but not least, day-to-day reporting tasks rarely require stunning designs. Seriously, if those aren’t for consulting purposes, your reports may be simple spreadsheets. Why integrate costly solution? Instead of buying a John from marketing a fancy toy to play during the lunch break, let’s save some dollars for John’s bonus instead and find an affordable option to meet specific business needs.

Back to the case. We’ve got two types of tasks here: generating fancy-looking and ugly (but very important and functional) reports. Let’s see how we met those business needs of our client using legacy solutions from the Microsoft ecosystem.

Enterprise reporting – let’s give this project a name – is a large team of BI engineers in charge of the client’s reporting. The client is an insurance major with 10K employees in the US and UK. Every day talents from various business units send us requests for specific reports. Say, Martha wants to see how her policies sold in Colorado last month. The requests differ in complexity and specifics, so to build it we collect additional data – how often the reports should be built, in which format, is it difficult to refer to a server in order to generate exactly such a table for Martha, etc.

Data is a complex thing. What Martha wants sometimes is in conflict with what the BI tool can provide. Depending on the specifics of the requests, we choose the tool to prepare the data. On this project we have two options to work with – either it’s SSIS or SSRS. Any report is based on the SQL script. We refer to the server and decide which one will be a more efficient shell for this very script. If the script is too heavy and we’ll have to run it frequently, we go for SSIS. If it’s a lightweight one and some fancy visualizations are required (filtering, interactions, dynamic charts and graphs, etc.), SSRS is our tool of choice.

A very brief intro into SSIS and SSRS

In case you have never used SSRS before and have problems following my story (it’s okay, I’ve done my share of SSRS studies before, I won’t pretend I was born with this knowledge), here’s a brief intro.

For a general user, not a developer, SSRS is a reporting portal. Say, you open a web page with multiple folders in it. Those folders contain icons, each of which refers to a specific report. Click on the icon and the report opens up.

The reports are prepared and uploaded by the engineering team. The report users – the client’s employees – can look through them, filter or make some visualizations using the portal functionality. At the end of the day, a user either gets a table in the required format or a visualization which they can do themselves in SSRS. Visuals are done in pretty much the same manner as they are in Power BI, as SSRS is actually Power BI’s predecessor.

Speaking of SSIS and the reports it does, it helps automate processing of huge bulky pieces of data that SSRS simply cannot digest. Data processing isn’t the key feature of SSIS. Everyone concerned knows very well that SSIS is an ETL tool used mainly for data transformations. 

Have a look at the workflow that we’ve established for this project, and you’ll immediately recognize a small ETL process in it.

1. Extract – download data from the database
2. Transform – transform the data (either in a SQL script or using the SSIS/SSRS functionality)
3. Load – save the results as a file in a required format, upload it to the folder/portal/send via email

That’s why choosing SSIS for reporting was a no-brainer for the engineering team.

So, whenever a big reporting task is on the horizon, we follow the decision tree below.

Let’s put it a bit different:

If you still aren’t sure you can explain it to your grandma, let’s have one more try.

• SSIS is a cool thing for the scripts that are too large for SSRS. 
• At the same time, SSIS makes only ugly reports with very basic table design. It has no report customization features. Which is okay for 95% of day-to-day analytics.
• SSRS does pretty much the same job, but for the smaller data chunks. Reports will look fancier, if you push some buttons knowingly in the SSRS portal.

For a more tech-savvy grandma use this:

• SSIS is an enormous algorithm which allows processing a large chunk of data from a server. 
• You pull your data from the server, do the settings and automate the data processing in SSIS in the way you want it to be done. Say, the script runs through SSIS and SSIS generates a report for you according to the preset schedule in the format you choose (excel, txt, etc.). 
• Voilà! You have your huge data set in the format of a very functional table covering all your basic business needs.
• Nope, there’s no chance of building a bubble chart in corporate colors using corporate fonts with SSIS.
• Yes, you can process a tremendous amount of important business data that would take a thousand years to transform and cleanse, was it not for SSIS.

Our client is a global player with an incredible leadership team. They have perfected their decision-making processes through test and trial for decades. Given the dire economic situation, I think it was an efficient solution they’ve come to when choosing the tool that meets their business needs across multiple departments and offices without making a hole in their budget.

For now, SSIS fuels their business with the important data in an optimal format. Would Power BI do the same job better? Perhaps. If the reports weren’t so bulky. Or if the data wouldn’t be so complex. Also, if there were more adequately trained talents willing to learn to work with Power BI. There are a lot of IF’s and BUT’s. Before you go through all of them your final decision may not look so stunning. But once we’ve done proper research, we came to an optimal solution that works fine for the client for the time being. 

As a summary to my article, I’d love to break three BI-related myths.

First, BI isn’t a silver bullet. I mean, it’s cool and stuff, but oftentimes businesses have no clue how to use it properly, or how to properly build their BI units, which all boils down to adequate talent training.

Second, the most commercially successful BI solutions might not suit your needs. Look for your best match, do proper research and you’ll find THE platform for you. It may not look stunning, or it may lack certain features, but it should cover your business needs. After all, there are only two types of software - first is the one that everyone hates and the other is the one that no one ever uses. 

Third and the last one, BI costs a lot, but it shouldn’t necessarily cost you a fortune. If you have access to a good workhorse that stood the test of time and a pair of brainy engineers, you’re fine.

We’ve been doing this project for over a year now. The other day, I asked the client how we were doing. He said, we’re doing great. I blushed. The feedback, even such a short one, is crucial both for me and my teammates. We’re working really hard to be a natural extension of the client’s team. We make the project decisions together, train and celebrate our success together. 

Just recently the client handed over the expertise for one of the BI platforms they use to us.  Like, you’re the boss, do what you deem best for the company. So, I would say a trusted vendor is what you need, along with a reliable BI solution. This is enough for you to see your company, with the whole variety of its branches and offices, as if in the palm of your hand. For those who're interested in how we managed to streamline the processes and become the trusted partner for this corporate client, here's one more article that sheds some light on how we approach the task and deliver the expected result.