Powerful Business Intelligence (BI) capabilities in SQL Server, Azure and Power BI can transform your complex data into business insights to share across your organisation. Our experts can help you plan, build and deliver your BI solutions.
HOW OUR WE CAN HELP YOU
Delivering fact-based data for intelligent business decisions, Ballard Chalmers’ Business Intelligence expertise combined with Microsoft’s Business Intelligence technology stack enables you to gain the competitive edge from data whilst reducing overheads.
BI provides the insights into data that allows decision-makers to discover trends and work faster and more efficiently.
Our BI engineers can transform data into a Data Warehouse that will provide easy and fast access to that data, allowing users to easily create the dashboards, charts, reports and Key Performance Indicators (KPIs) that they need.
In a traditional on-premises environment: SQL Server together with SQL Server Integration Services (SSIS), Analysis Services (SSAS) and Reporting Services (SSRS) provide the tools to create a Data Warehouse using Extract, Transform and Load (ETL) processes and then to analyse and report on that data.
In the cloud or hybrid environment: SQL Azure, SQL Data Warehouse, Azure Data Factory, Azure Analysis Services and Power BI or Power BI Embedded provide the tools for processing structured data.
OUR BI DEVELOPMENT PROCESS
Extract, Transform & Load
Stage one of any BI development is extracting and transforming the source data from one or more source systems, using an Extract, Transform & Load (ETL) process.
Our ETL tool of choice is Azure Data Factory (ADF). This is a modern, cost-effective and easy to use cloud service that takes data from just about any data source, including CSV files from sFTP, SQL Server and Oracle databases.
ADF is a cloud service, but it comes with an Integration Runtime that allows it to access data from on-premises data sources as well. It also has the capability to run legacy SSIS (SQL Server Integration Services) packages as well so organisations are not forced to re-engineer legacy SSIS based ETL code.
We aim to make the ETL process incremental wherever possible so that only new or changed records are loaded into the Data Warehouse. This makes the ETL fast and it also makes it possible for it to be executed it more often. To do this we use features such as SQL Server Change Tracking or Change Data Capture to identify the changes and then just process those.
Data Warehouse Design
A Data Warehouse is a database with a schema that is designed to be optimal for servicing reporting queries. The selected schema is usually defined as a snowflake schema, which is a logical arrangement of tables resembles a snowflake shape. The snowflake schema is represented by a central fact table, that contains the data being analysed, which is connected to multiple dimension tables, that contain the criteria that the data is being analysed by.
There are two main methods for designing a data warehouse:
Starts by identifying the key business processes and the main business questions that the data warehouse needs to answer
Starts with the main business entities such as Customer, Product, Brand and produces a detailed logical model for each one.
Generally, we tend to use Kimball approach.
Dashboards, KPIs and Reporting
Our tool of choice for creating Dashboards, KPIs and Reporting is Power BI, which is Microsoft’s flagship reporting tool.
Power BI comes in 3 main forms:
- Power BI: Cloud service that a power user can use to self-serve, which means develop their own dashboards and reports, against a data warehouse, and then share them with others
- Power BI Embedded: Allows Power BI reports created by a developed to be embedded as part of an application. In this case, only the application needs to have a Power BI License and not each user
- Power BI Reporting Server: This is a server that you can install on-premises and run Power BI reports
For backwards compatibility, Power BI (Cloud Service and Reporting Server) can also run SSRS (SQL Server Reporting Services) reports, which are now called Paginated reports.
Why Ballard Chalmers
Ballard Chalmers’ Microsoft consultancy specialists will work with you and your team to design, deploy, debug or adopt Microsoft technologies both efficiently and cost-effectively. A vital requirement for minimizing risk in complex initiatives.
Business Intelligence Case Studies
SMT, together with Ballard Chalmers have broken new ground with Mia. It is truly an innovative business intelligence and process management application that transforms data into smart business process by fully automating an optimal balance between achieving best receivables performance against best operational efficiency.
CEO – Square Marble Technology
The standard produced from this partnership was vibrant and informative for the team. The Databix Vision and outcomes have grown substantially since the data cubes were handed over. The implementation of the cubes and the descriptions around measures enabled the developers to create many layers of business intelligence from a Sales and Stock perspective.
Application Development Manager – Weetabix