This data could be invaluable for businesses. From planning big sales and advertising campaigns to staffing departments and retrieving information for accounting purposes, BI (Business Intelligence) processes can help you make decisions that drive profit and growth within your organization.
While many companies see the potential of their data, they lack the tools to analyze it properly. How can you implement the right BI tools in your company’s processes?
In this article, two of Coherent Solutions’ experienced data engineers, Maksim Spektorenko and Julia Grishkevich, will discuss getting started with the Microsoft BI stack — one of the most popular BI software stacks according to Gartner BI Magic Quadrant 2019.
The four pillars of Microsoft BI
“This set of tools is usually an appropriate choice for BI projects because it can be used for an extensive range of tasks. It also provides flexibility in case a client has more specific needs. It’s a very stable stack, and it keeps improving with every new version.” – Julia Grishkevich, Coherent Solutions Data Engineer
Finding the right BI tools is complicated enough, so having tools that are stable and flexible is critical. As Julia pointed out, Microsoft BI meets these requirements. What does Microsoft BI look like?
Microsoft BI is a complete software stack built on four pillars: SQL Server, SSAS, SSIS, and SSRS. When you buy a license, you are provided with all the necessary tools for migrating, processing, analyzing, and visualizing data. Let’s look at the four pillars individually.
Microsoft SQL Server. Microsoft SQL Server is Microsoft’s core data management platform. It has all the necessary components for keeping, processing, and securing data, allowing you to build data warehouses and databases for different needs.
SSAS. SSAS is a Microsoft technology for implementing OLAP (Online Analytical Processing) solutions. You can use SSAS for creating enterprise-level semantic data models for client software and business reports. It supports two primary types of semantic models: Tabular (ROLAP) and Multi-Dimensional (MOLAP). It’s a great choice when you need to extract heavy and hard-to-process data in a highly proficient way.
SSRS. SSRS is a reporting tool that helps present data in a user-friendly way.
SSIS. SSIS is handy when you need to unite data from separate sources (files, databases, and so on). It’s also great for deployments and releases, as well as for organizing and scheduling technical operations using SQL. This tool can reduce your costs by eliminating the need for additional licenses. For example, you may need to integrate third-party data into your data warehouse. Another advantage of using SSIS, in comparison to using an external tool, is that it is integrated with the server. Additionally, you get the opportunity to use TFS (Team Foundation Server) for version control, as well as for deployments and releases.
This stack of tools is flexible and can adapt to your needs. To better understand its processes, we’ll use the example of a clothing chain. One of the most common purposes for which retailers might need a BI team is planning for big sales. When a nationwide retailer plans something of this scale, they need tons of data on customer shopping preferences. Here are the BI steps you would use to achieve that goal:
Step 1: Gathering Data. To gather data from a retail store, you need to access some databases and write a few SQL requests. You can do this by yourself or with the help of a development team. It’s important to remember that it must be a database rather than just files and should have enough data history for your report.
Step 2: Creating a Data Warehouse. What if you have hundreds of stores? Or need to analyze data taking into account the seasons or even weather changes? Or you would like to control the situation on the market and have the ability to react quickly on the demand changes in all stores? These are cases when you can use a data warehouse. Building a data warehouse allows to integrate data in one centralized place, structure and aggregate it in a way that makes your data available and representative for making decisions in a variety of spheres. The method that is usually used for loading and transforming data is ETL (Extract, Transform, Load), which can be done with the help of Microsoft tool SSIS.
Step 3: Reporting. After you have gathered and stored your data, you’ll want to present it in an easily digestible view. Reporting, at its core, is about visualizing your data. It consists of several components: sources, data warehouses, and reporting tools.
Two of the leading Microsoft reporting tools are Power BI and SSRS. Which one should you use? Our data engineers shared their thoughts:
Power BI. “Navigation of Power BI is very friendly, as simplicity is one of its key features. Its interface is familiar, resembling Excel. You can use this tool to create beautiful reports with dashboards, maps, diagrams, and so on. Power BI is also cloud-based and allows users to access reports from any device through the web browser. You can also use Power BI Report Services on projects with strict security requirements or on those that aren’t ready to be moved to the cloud. The on-premises version of Power BI allows you to build and distribute reports behind your company’s firewall.” – Julia Grishkevich, a Coherent Solutions Data Engineer
SSRS. “SSRS is a primitive and old-school tool for reporting. Users can create reports with drag-and-drop graphic icons and then download them in excel, pdf, HTML archive and more. It’s slower and less capable than its modern counterpart Power BI. That’s why most dashboards are created with Power BI.” – Maksim Spektorenko, Coherent Solutions Data Engineer
Key aspects to focus on during your project
To ensure you choose the right tools and use them correctly, you’ll want to follow three steps. This will help you get reports faster and ensure that they have actionable insights for your team.
Define Your Goals: Even though the process is flexible, it’s essential to have clear goals at the start. Only then can you have clarity with your implementation roadmap. Data engineers are a great resource when discussing your roadmap.
Define the Sources for Your Data: You should also consider your data sources. For example, when planning your next sale, you might need to retrieve information from cash registers. However, that would require a new program to help analyze and process that data.
Define Your Requirements: Another key aspect that needs to be defined is the amount of data you’re working with. For example, you may have large amounts of data that need to be processed quickly, possibly in under one or two seconds. Will your SQL server be able to process it fast enough? Usually, developers try to respond to the client’s situation and tailor the process to their needs. However, if your dataset is too large, you might want to consider using Hadoop or other suitable big data tools.
Another important question is: How current does your data need to be? In most BI solutions, you don’t need real-time reporting, and data can load into a data warehouse once a day. If you need real-time reporting, however, where data is processed every second, that will increase operational costs. When understanding your goals, sources, and requirements, you’ll have the building blocks to choose the right BI tools.
How Coherent Solutions has helped other companies leverage the power of BI tools
Coherent Solutions has a long history of successful BI projects using the Microsoft BI Stack. Our keystone was Bluestem Brands — a major eCommerce enterprise that owns 13 brands. Bluestem relies heavily on data since it is critical to their mission to help customers find the products that suit their needs and payment methods.
Bluestem turned to Coherent when it needed to improve its system for communicating data between departments. As a result, it was able to get faster data updates, open up extensive data analysis possibilities, and add other features that simplified life for both users and developers.
Our team focuses on data integration servises (including integrations between their data warehouses and Marketing Cloud Salesforce), building forecasts, and other BI initiatives. While we enjoy using Microsoft’s BI stack, it’s just one page in our portfolio. Instead of obsessing over using our preferred toolset, we concentrate on solving real business problems using the tools that work best for our client’s needs.
Do you have large amounts of data, but don’t know what to do with them? Talk to our team about how you can get the most from your data with the right tech stack.