Hey, we know this is a lot of content. If you read through it a bit and decide you want to take it with you, all you have to do is leave a bit of information, and it's yours!
(BI) is a technology-driven process for analysing data and presenting actionable information to help executives, managers and other business end users make informed business decisions.
Business intelligence can be used by companies to support a wide range of business decisions ranging from operational to strategic. Basic operating decisions include product positioning or pricing. Strategic business decisions involve priorities, goals and directions at the broadest level. In all cases, BI is most effective when it combines data derived from the market in which a company operates (external data) with data from company sources internal to the business such as financial and operations data (internal data). When combined, external and internal data can provide a complete picture which, in effect, creates an “intelligence” that cannot be derived from any singular set of data.
BI technologies provide historical, current and predictive views of business operations. Common functions of business intelligence technologies include:
- online analytical processing
- data mining
- process mining
- complex event processing
- business performance management
- predictive analytics
- and prescriptive analytics.
BI technologies can handle large amounts of structured and sometimes unstructured data to help identify, develop and otherwise create new strategic business opportunities. They aim to allow for the easy interpretation of these big data. Identifying new opportunities and implementing an effective strategy based on insights can provide businesses with a competitive market advantage and long-term stability.
A typical BI platform will provide a select subset of all common functionalities of a BI but not all. Each platform focuses on a set number of areas to position the platform to stand out from other competitors and appeal to a specific market segments, therefore it is important to look at the main business objectives as the main criterion for choosing a BI platform or solution.
So what is Business Intelligence really?
Before we go any further and explain the different components that make a BI solution we should look at the purpose of a BI solution. BI means something different for different people, meaning the outcome we think a BI solution can provide us with is different from one business to another. So before you deem that your business or organisation requires a BI we need to set the record straight on what is possible with a BI solution and where does it fit in the business.
BI is crucial to any business size, big or small. There is a difference between what we think we can get from a BI solution vs. what a BI solution can actually offer.
For that I believe the best way to illustrate how a BI solution fits in your business or organisation is to put the discussion in context. So I'll be discussing the BI solution outcome from the point of view of 3 different businesses, and hopefully you'll be able to pick where your business sits and which path is more suitable for you
Let's get a closer look at our hero and know a little bit about their businesses.
1. The Professional Service Provider
The first business we are going to look at is Jim. Jim business is in the professional services (e.g. Coaching, Consulting, Accounting , Graphic Design, or Website Design), Jims business target is predominantly B2C with an occasional B2B client. Jim works predominantly by himself however he does employee several contractors on a regular basis and collaborates with other smaller sized business on certain project. Jim's outreach to bring in new customers is mostly outbound marketing however he is starting to be more visible online and investing heavily in inbound marketing.
2. The Marketing Agency
The second business is for Susan. Susan runs a small digital agency with an even split between B2B and B2C clients, Susan serves her client in the social space from social management, promotions and pay per click advertising. Susan employees a handful of full time employees and relies heavily on inbound marketing to reach out for new customers.
3. The Established Business
The third business owner is Mick. Mick runs a B2B business working with brands to promote their business and help optimise their user acquisitions. Mick's team is diverse and includes sales, operations, accounting, production and IT staff. Furthermore Mick's business operates in locations in Australia, New Zealand and Singapore, all of Mick's sales and marketingstrategy is strictly outbound.
Reading about our 3 heroes and their businesses it might seem the only difference is in scale, that is true to some extent. However when it comes to Business Intelligence their needs are entirely different and the choice of BI platform is different. Below I'm going to break each business into the several components that a typical Business Intelligence projects would consider.
So the crucial question to ask before anything, why do you think you need a Business Intelligence solution? For our heroes each have a different outcome:
Jim: Optimise the inbound marketing funnel and increase conversion
Susan: Manage all client metrics and stay on top of the goals
Mick: Get a 360 view of all business operations and team activities to generate insight and optimise the business further
A BI platform provides various visual to display your reports. Tabular reports, columns, bars, pie charts, heat maps, tree maps, and geo maps are example of such reports. The collection of those reports we call a Dashbaord. A dashboard is a single view that will give you all the reports associated with a given area in your business. It allows you to get an instant insight on how that area of the business is performing.
However useful those reports and dashbaords are, the real value of reports and dashbaord should go beyond displaying the data in beautiful charts and provide controls that will help teams to cut down on their reporting times considerably. Automating the reporting will help shift the focus from the manual and repetitive tasks of generating those reports to act on those reports instead. So a reporting functionality should enable for:
- Daily, weekly, monthly, or any other period, automatic reporting
- Report generation based on conditions (e.g. Campaigns not performing)
- Distribute automatic reports to different departments (e.g. end of campaign billing) or even dispatch to a client account (e.g. daily campaign reports)
- Department/user level access to various report levels, this allows users to focus only on reports that are specific to their area of the business.
All data analytics is considered Descriptive, since the data happened in the past and the analysis is trying to describe the data at hand and find insights. Descriptive analytics is crucial in that regards because it:
- links the market to the business through information
- provides the information needed for actionable decisions
- it’s the principle for systematically collecting and interpreting data that can aid decision makers.
A BI Platform should allow for two main functionalities to facilitate data analytics
- Unrestricted data source connectivity, you need to have the ability to connect any data source to the BI platform
- Provide tools that allows the business analysts or data scientist to dissect the data anyway required, either directly in the cloud or by interfacing to a more specialised software such as R or Tableau.
Providing this level of analytics opportunity to the business will allow the business to devise business decision that are:
A BI platform should have allow for any types of those questions to be asked on the data loaded into the platform, because it is that synergy between data and decisions that you have to make that makes for a good analytics.
Performance campaigns are run across multiple channels and platforms, each platform will inevitably have it’s unique way of trafficking, naming and reporting on a campaign such as:
- Campaign name pre-fix, post-fix or a different column name altogether
- Line item vs Flight vs Ad set
- Creative vs ad name
- Time zone collisions
Grouping those different dimensions and metrics from multiple platform is more often than not challenging and in most cases it is not possible by certain BI platforms. The result of that is having a campaign performance report that will need to be downloaded and joined manually off-line using Excel or Google Sheets.
A BI Platform should allow for rapid unification for such complex data in order to provide a broad and complete business visibility.
As BI platforms provide many reporting visuals and an n number of dashboards that can be populated with those silo reports, managing those reports becomes crucial, and more important the ability to connect one report to another is a must have, that’s because having 40+ discreet reports sitting side by side on one or several dashboards doesn’t provide a related story.
Most mid-tier BI platforms will offer what is called a Data Wall. As the name implies, it’s as many dashboards you are allowed to create that will slide in every n seconds. The reason for this is because reports from a single dashboard cannot be connected to a report in another dashboard, which prevents the end user to filter the data and drill in on the cause of the data looking like this.
A BI platform should allow for a drill-in or a drill-out from a report which encourages and embodies a value based design of a business intelligence dashboard.
A value based design assembles data from the bottom up, so a user looking at the end result can drill-in on the drivers for this result, drill-in further to action points, to users responsible, decisions that needs to made and the info required to drive that top result.
A BI Platform consists of many components/elements in order to provide the required reports needed for a business. Some BI platforms will attempt to provide a simplification for one or two of those processes in order to expedite the on-boarding process. Other 3rd party platform specialises in providing a solution for a specific component in the BI platform building process.
As illustrated in the diagram below a BI reporting solution has to have the components illustrated. Those components are:
Services: Those are all the data sources (external data and internal data) that you wish to pull data from. For Impelus those are LeadByte, HasOffers, FB Ads, Google AdWords, Affiliate network, Google Spreadsheet.
Extract, Transform, Load Layer (ETL): Is the process responsible for pulling the data from those services and placing that data into a data warehouse. The ETL layer usually sits on it’s own servers.
Data Warehouse: All data that have gone through the ETL are hosted on one, two or more databases which is called a data warehouse
Data Preparation: Data will need to undergo a data preparation stage in order to prepare the data for modeling. BI Platform: Is where the modeling (relationship building) is done and reports are generated
Each component of the BI platform requires resources with specific skills set to manage it and operate it. Some of those components are done once and will not require constant maintenance or modification, while other components do require constant update to maintain the BI solution.
Because of the varied resources and their required skill sets which most businesses don’t have in house, most 3rd party platforms solutions for either the ETL component or the Data Preparation component that allows for non technical resources to operate and maintain that component without resorting to hiring to those extra resources. Those 3rd party platforms are a viable solutions to take into consideration if the volume of data and continuous involvement of an extra resource justifies the investment in such solutions.
The ETL component is usually worked on once. Once all services are connected, chances that the connection will change or the requirements of data that needs to be pulled in is minimal. The ETL stage is usually performed by a single senior programmer or by a team of programmers, in either case the programmer will need to be comfortable across multi disciplines (Back-end, API, Reporting, SQL). The next section we’ll discuss he requirements of those roles in more detail.
The ETL component consists of:
- A web app
- Multiple web services
- A cloud server (single or load-balanced to scale based on volume)
- A data warehouse
Once the complete ETL layer is built, the ETL component should run uninterrupted, but will require periodic checks or ad-hoc work for monitoring and maintenance, typical cases that will require those periodic checks or ad-hoc work are:
- Web services failure (data sometimes fail to pull from a source -ecause a peak in the network, or a request that took longer than expected)
- API change (some channels update or break their current API in a periodic manner)
- Cloud server failure (server shutdown, increased traffic, migration...etc)
There are 3rd party platforms that offer a cloud based solution that solves some parts of this ETL stage.
Xplenty is a company that provides a platform that covers the following area of the ETL layer
- Writing the transformation logic (using visual interface, hence remove the need for a programmer and instead allows a senior business analyst to perform those transformations)
- Monitoring the web services (if a web service fails you get notified and restart it)
- Cloud server scalability and up-time monitoring
The design of the transformational logic can be done by a senior business analyst, as most companies will have a data analyst resource but not necessarily a full-stack programmer or programmers in house, for such situations, and if the work on the ETL component is continuous, it would make sense to use a tools such as Xplenty. The caveat ere in the Extraction layer. Xplenty doesn’t have connectors to all services Impelus uses, they do have the ability to build those connectors for their client if requested. However if time to develop those connectors is issue then the business will need to hire a programmer(s) to build this extraction layer, which results in extra costs.
The other thing that Xplenty doesn’t provide is the data warehouse which will need to be setup and deployed by a senior programmer or a DevOp.
Xplenty pricing starts from $999 for a few job per day, and $2,499 for continuous updates per day. Pricing increases with data volume and the server instances required.
Workato is company that provides access to API level to most platforms in the market in a form of triggers and actions. This connectivity is currently named Integrated Platform as a Service (IPaaS). Benefits of using Workato is having a connection to a service that will only be activated when a certain trigger is identified. The API maintenance and connectivity is also a plus since the API maintenance is regularly checked and maintained by Workato.
The biggest draw back of Workato or similar IPaaS tools is the level of API connectivity available to a certain data source. As not all API end points are available for the user to pull data from.
Workato pricing starts at $599 monthly on an annual commitment for basic apps and $2,499 monthly with an annual commitment for business plus apps.
Data Preparation Component
BI is notoriously difficult to implement, often taking six months or a year or more. That complexity of implementation often sits with the Data Preparation stage and the actual BI modeling stage. The Data preparation stage continues to be time-consuming process because of the volume of work needed in combining, cleaning, and shaping different data sets into a clean and structured data that can be modeled in the BI platform.
Once completed the data preparation stage will not require changing after the implementation project, This of course depends on the business needs.
The data preparation stage is usually performed by a senior business analyst. A programmer is not suitable for this task because her/his understanding of the business is on a technical level instead of the business and operational level.
There are many 3rd party solutions for a business analyst to prepare the data, some of those solutions and discussed below:
Pexata is a Data Preparation services on the cloud with some AI components. The data preparation on the cloud allows a business analyst or several business analyst to comb through all the data in the data warehouse or even directly from a connected source and prepare this data for consumption by a BI platform, catalogue it, version it and secure aspect of it. (Note, a service includes hundreds of functions, and most services will not provide access to all those functions. Let’s take HasOffers as an example, when a programmer is performing an ETL on HasOffers most often she/he will pick a certain amount of API end points and filter out the rest, that’s because of the complexity to maintain or replicate the HasOffer data structure on the ETL layer)
Pexata pricing starts at $20,000 yearly for 10 Million raws of data to process.
Tableau is a BI desktop and cloud platform, data analytics tool and a preparation tool. Data preparation and modeling (modeling is the process of assisting various data sets and setting hierarchies and grains for analysis) is performed on a desktop application with the resulting dashboards or reports pushed to the cloud for the team to consume. Each aspect of those tools is sold separately and priced differently. The link between the data warehouse and Tableau is done on a desktop computer and will require an active connection to update and fetch new data. Tableau data preparation and off-line analytics are extremely intensive and is considered the choice of many data analysts in various industries. Having a tableau license along side a BI platform is a common practice.
Tableau recently released a data preparation version which is primely focused on data preparation to suite different use case scenarios. The Tableau Prep is provided along side the Desktop version at a $70 monthly per user (i.e. Business analyst)
Continuous collaboration between Business Analysts and Developers. If a business have dedicated resources for the BI part of the businesses, then most of the data preparation can be performed on the ETL layer, however that requires constant collaboration between the business analysts and the developers to clean the data and prepare it for consumption.
To design, implement and maintain a BI platform, several resources are required either on an ongoing basis or contracting. This section will go over some of those roles, their technical requirements and some costs involved.
1.1 BACK-END DEVELOPER
A back-end developer is a programmer who is capable of building the logic and architecture of a web platforms and web services. A full-stack developer adds the ability to build the front end side of the web service, some senior full-stack developer can also develop mobile apps.
Stage in the pipeline: ETL stage and Data Warehouse
Seniority required: Senior
Expected salary annually: $110,000 - $150,000
- Strong web development architecture (Objective Oriented or Functional)
- Strong design patterns (preferably MVC)
- Strong Microservices background
- Strong API development knowledge
- Good understanding of MySQL, PostgreSQL or even SQL.
1.2 SOFTWARE ARCHITECT
A software architect is a software expert who makes high-level design choices and dictates technical standards, including software coding standards, tools, and platforms.
Stage in the pipeline: ETL stage and Data Warehouse design
Seniority required: Senior
Expected salary annually: $150,000++
- Server design experiences
- Strong application deployment experience
- Strong experience in multiple web development frameworks
- Background in DevOps
DevOps (a clipped compound of “development” and “operations”) is a software engineering culture and practice that aims at unifying software development (Dev) and software operation (Ops). The main characteristic of the DevOps movement is to strongly advocate automation and monitoring at all steps of software construction, from integration, testing, releasing to deployment and infrastructure management. DevOps aims at shorter development cycles, increased deployment frequency, and more dependable releases, in close alignment with business objectives.
DevOps increases an organization’s ability to deliver applications and services at high velocity: evolving and improving products at a faster pace than organizations using traditional software development and infrastructure management processes. This speed enables organizations to better serve their customers and compete more effectively in the market.
Stage in the pipeline: ETL Architecture, Data warehousing and BI Servers
Seniority required: Senior
Expected salary annually: $200,000++
- DevOps Release & Delivery experience
- Deep knowledge of Continuous Integration / Continuous Delivery
- Deep knowledge of DevOps best practices
- Deep understanding of Entire Application Development Life-cycle
- Strong server management and deployment background.
2.1 REPORTING DEVELOPER
An integral member of our delivery team, the BI Report Developer is responsible for the design, development, implementation and support of mission-critical enterprise Business Intelligence (BI) reports.
Stage in the pipeline: Data Preparation and BI Reports
Seniority required: Senior
Expected salary annually: $200,000++
- Ability to build, implement and support BI Reports.
- Strong data integrity issues like manipulation, validation and cleansing
- Strong data analysis to understand business requirements, design, deploy and test the reports
- Good experience in BI implementation methodologies
- Strong analytical and problem solving skills
- Good knowledge of relational data bases and mapping
2.2 DATA ANALYST
Data analysts translate numbers into plain English Every business collects data, whether it’s sales figures, market research, logistics, or transportation costs. A data analyst’s job is to take that data and use it to help companies make better business decisions.
Stage in the pipeline: Data Analytics and BI Reports
Seniority required: Mid to Senior
Expected salary annually: $150,000++
- Sound data management experience
- Sound communication skills
- Ability to generate insights and present solutions/recommendations to a variety of stakeholders with varying technical capabilities
- Sound SQL and Visualization (Tableau) experience
2.3 REPORTING DEVELOPER
A Data Scientist is someone who makes value out of data. Such a person pro actively fetches information from various sources and analyzes it for better understanding about how the business performs, and to build AI tools that automate certain processes within the company.
Data Scientist duties typically include creating various Machine Learning-based tools or processes within the company, such as recommendation engines or automated lead scoring systems. People within this role should also be able to perform statistical analysis.
Stage in the pipeline: Data Analytics and Predictive Analytics
Seniority required: Mid to Senior
Expected salary annually: $150,000++
- Strong stakeholder engagement, requirement elicitation and solution presentation abilities.
- Commercial Data Science experience building a range of statistical models relating to customer marketing strategy (Acquisition, retention, growth, cross sell, churn), customer segmentation, next best action, campaign analytics.
- Strong technical skills using SQL/NoSQL or Python to extract, manipulate and merge large volumes of data from disparate source systems (e.g. AWS, Azure, SQL Teradata, Oracle SQL, SQL Server, MYSQL, SAS Base, Python, R)
- Exposure to BIG DATA platforms with technologies that include; Hadoop, Apache Spark, Scala, PIG, Hive, etc.