Powerful Data Warehouse and 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.
SOME OF OUR Data Warehouse and Business Intelligence ENGAGEMENTS
HOW 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 insights into data that allow decision-makers to discover trends and work faster and more efficiently.
Our Data Warehouse experts 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.
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
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
Data Warehouse and Business Intelligence Case Studies
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 also makes it possible to be executed 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, generally, we tend to use Kimball approach:
Kimball: Starts by identifying the key business processes and the main business questions that the data warehouse needs to answer.
Inmon: Starts with the main business entities such as Customer, Product, Brand and produces a detailed logical model for each one.
DASHBOARDS, KPIS AND REPORTING
Our tool of choice for creating Dashboards, KPIs and Reporting is Microsoft’s flagship reporting tool, Power BI.
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 developer 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 use to 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.
FIND OUT MORE
Our data warehouse and business intelligence services are generated by the powerful Microsoft technologies, SQL BI and Azure Modern Data Warehouse.
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.