We focus on getting the best result in the shortest amount of time. It's because we are so experienced in different industries and technologies.

We use various methodologies, depending on customer and project needs, such as:

Jobs to Be Done

Agile Data Warehouse Best Practices

Rapid & incremental delivery

Firstly, we need to identify where we should focus on to begin with. Sometimes it’s streamlining the data ingestion and transformation, and other times it’s data quality or the way the data is presented.

There's no one size fits all answer here; it depends on the complexity of the data, the volumes, and the metrics that you and your team need.

Then, once we have worked that out, we build a prototype data model, either directly in Power BI or in a database.

We aim to have you a solution to test (which initially won't be pretty) within the first 5 working days at most, often by the end of day two, but it all depends.

There's no point building stuff that you and your team won't use.

So, we start off with a Jobs To Be Done approach.

Power BI makes it easy to dive right in and start building - other consultants may love burning hours on data pipelines and pretty visuals, but we just want to get you the best result.

Proof of this is that our average customer stays with us for 11.9 years - and that's an average, as we are constantly getting new customers, so it's hard to keep that number higher :)

Industry: Healthcare

Challenge: Too many data sources - too much manual data entry

Our customer provides healthcare services to schools. Although they are government funded, they have a budget to spend on promotion - they were spending money on Google & Meta ads, as well as other direct campaign strategies.

Our proposed solution:

In this case, the business wasn't large enough or complex enough to need a data warehouse. So we decided to look at the data sources individually.

The seven data sources were:

Xero, Google Analytics, Facebook Ads, Facebook pages, Zoho CRM, PHP based Custom Learning Solution and a custom booking system.

Our team reviewed the data sources and weighed up a costs and ease of using Stitch data, Airbyte, C-DATA, custom connectors or FiveTran. We chose FiveTran, as it provided 5 of the data connectors out of the box. FiveTran initially fed into a simple single PostgreSQL database deployed by our devops team.

The transformations were quite simple, so they were performed directly within Power BI

Industry: Financial Services

Challenge: Volumes of data from a number of sources.

Our customer has a number of financial services consultants who key data into various systems. It was challenging for the management team to get a complete view on the performance of each team member.

Our proposed solution:

Use a series of Python scripts to extract the data from Access, Xero, Excel and the online industry specific CRM. We chose not to use an off the shelf tool, as it would have provided very minimal time savings.

We used our own developed Xero connector, Excel & Access are out of the box for Power BI and the custom CRM was always going to be a custom developed connector. The solution has a very basic data warehouse, which technically is really an operational data store (but, don't let that worry you).

End users have daily Power BI dashboards updated.

Industry: Mechanical Services

Challenge: Too many data discrepancies within current Power BI

Our customer had been developing their own Power BI solutions for a number of months. It had grown over a period of time, based on development which different teams had performed.

Our proposed solution:

Build a traditional data warehouse in MS SQL server, with SSIS data import packages.

Being in a quite technical mechanical & software services industry, many engineers were capable of navigating their way around Power BI and indeed, building their own dashboards. However, they hit a few snags in relation to Data sources and management, so they called us in.

Data sources- The ERP, IFS, is a well known service management solution, but finding the correct data can be challenging, as there's many Slowly Changing Dimensions.

Data accuracy- By not having a data warehouse, many teams did their own calculations within their own Power BI model. This meant that project margin in one dashboard didn't match project margin in another. Additionally, by not having a data warehouse, there was no ability to perform the required data cleansing and transformations.

Our solution was to build a traditional data warehouse in MS SQL server, with SSIS data import packages.

Sometimes it all starts with data, but not always!

We start by looking and auditing your existing data and data sources, but that doesn't mean that's where we start working. There's often a lot of challenges in getting accurate data in the correct format to make Power BI dashboard development easier. If you cut corners during the data prep process, you are going to find the development harder. This compounds when you need to add more data, or there's some data complexity like Slowly Changing Dimensions.

Common issues we see:

Poorly formatted data. The data isn't formatted for easy dashboard design, so that means lots of DAX coding and data 'wrangling' just to make useful graphs and charts.

Missing data. This can be a real issue, especially for management reporting. It's often hard to see when there's some data missing - sometimes it's obvious. A best practice approach is to build some audit checks in the data load process. [Dave On Data]

Slow loading times. This can occur when there's no mechanism setup to allow for just new records, or changes to be loaded. Loading too much data can also cause the dashboards to become sluggish, leading to poor user satisfaction.

API Connectivity. Not all systems are available directly to Power BI. A choice between build or buy a data integration has to be made.

Data transformation. Loading data from different systems which need to either be joined, matched or concatenated may lead to errors.

Date & Time Issues. Inconsistent date formats within existing systems is not uncommon. For example, in one system a transaction date is stored in the local timezone, but in another it's stored in UTC.

There's an art to over-preparing the data vs just doing enough to get started with development.

If you over-prepare, you risk fixing issues in data that won't be used in reports. You can spend a lot of time ensuring that the data is correct, only to find that for the users, the data is the right data that they need.

It's important to develop a prototype (even if it looks sub-standard) as soon as possible to allow users to view and use the output of the data. We like to start with tables and a few column charts - no more at this stage. It's an iterative process, that shouldn't be too strict. Usage and feedback is critical at this stage. We will often ask users to export the data into Excel and reformat it the way they would like to see it, or add / remove columns.

Dashboard design isn't always the last thing to do, some would argue that it's the place to start.

It can be the place to start, but this can depend on what kind of dashboard is being developed, who the users are and what's the expected usage. Using a Jobs To Be Done approach helps us design a workable solution.

Dashboards fall into three broad categories:

Analytical - historic and detailed; these are used to answer questions regarding what's happened in the past and why.

Operational - in the now; these are to show what's happening now and what needs to be acted up. EG: Aircraft landing at an airport, or patient admissions to an emergency ward.

Scorecards - future focused; these are showing progress against a goal.

If we are building analytical dashboard, we often won't put too much emphasis on the design. It won't be ugly, we will use corporate colours and fonts, but we won't do a full UX design workshop.

Operational dashboards are different; the design is often the starting point. These dashboards need to convey a message instantly, so the colours and layout are critical.

Scorecards are a bit different, as users want a high level, let's see what's going on, but will always want to drill down. With scorecards, we like to let our users play with actual data in meetings and gather feedback. The reason for this is that we may over design aspects that aren't really required.