Call today 0845 643 6463

Business Intelligence

  • Using ConcatenateX in PowerBI to return multiple values.

    June 9th, 2020 in Blog, Business Intelligence, Power BI

    In this blog post we’ll take a quick look at using ConcatenateX function to view a concatenated string of dates where the max daily sales occurred for a given month. I came across this function whilst going through the excellent “Mastering DAX 2nd Edition Video Course” by the guys from SQLBI.com. So credit to Marco and Alberto for sharing this. So how does it work? If we had a list of dates ranging from 01/01/2020 to 31/12/2020 and we…

    » Read more

  • Creating calendar tables with DAX using Power BI

    April 6th, 2020 in Blog, Business Intelligence, Dashboarding and Reporting, Power BI

    By Nick Edwards In this blog post we’ll take a quick look at creating a self-generating calendar table using DAX. Dates are important if we want to perform time intelligence reporting on our data i.e. yearly sales, monthly sales, weekly sales, year to date sales or previous year sales. We’ll be using the calendar function to create our date table, but there are other methods to do this such as CALENDARAUTO or GENERATESERIES. Here is the…

    » Read more

  • Sampling data in Data Lake U-SQL for Power BI

    January 26th, 2017 in Big Data, Blog, Business Intelligence, Cloud, Data Lake, Power BI

    Being able to hook Power BI directly into Azure Data Lake Storage (ADLS) is a very powerful tool (and it will be even more so when you can link to ADLS files that are in a different Azure account!! – not yet available as at January 2017). However there is a problem, Data Lake is designed to scale to petabytes of data whereas Power BI has a 10GB limit. Yes this is compressed, so we’d expect around 100GB of raw data, however to load…

    » Read more

  • First thoughts on DataZen

    May 20th, 2015 in Blog, Business Intelligence, Dashboarding and Reporting

    Following the recent announcement of Microsoft acquiring DataZen, I’ve been having a play around to see what it can and can’t do. Here’s a very brief summary so far: The Good: Very quick and easy design interface Fantastic way of modifying dashboard layout for tablets and mobile devices It seems to ‘just work’ very well You don’t have to worry about the pixel perfect layout of what’s where, it takes care…

    » Read more

  • BIML – What is it?

    February 17th, 2015 in .Net, Blog, Business Intelligence, Integration Services

    I’ve noticed a growing trend over the last year – the ever growing presence of BIML (Business Intelligence Markup Language). So what is it? What does it do? And do you need to learn it? What is BIML? Simply, it’s a way of defining the functionality of an SSIS (Integration Services) package. If you’ve ever opened an SSIS .dtsx file in notepad you’ll see a daunting mess of GUIDs that you really don’t want to…

    » Read more

  • MSc Dissertation – Performance of Loading SCDs in SSIS

    July 20th, 2012 in Blog, Business Intelligence, Integration Services, SQL Server

    Well after 3.5 years, I’ve finally completed my MSc Business Intelligence – hoorah! And to reward the time, effort and increased grey hair, they saw fit to give me a merit as well. During the last year I’ve been writing a thesis investigating the performance characteristics of loading data into data warehouse dimensions. Specifically loading Type 2 SCDs using SSIS. For those who have followed the previous posts and my…

    » Read more

  • SQLBits X Video available

    June 19th, 2012 in Blog, Business Intelligence, Community, Integration Services, SQL Server

    SQLBits X Video Now available The video of my talk at SQLBits X is now available on the SQLBits website here. The talk was focused on presenting the results of my MSc Business Intelligence dissertation, comparing the performance of different methods of using SSIS to load data warehouse dimensions, specifically type 2 SCDs. The talk also covers a comparison of the performance between traditional hard disks and solid state storage systems such as…

    » Read more

  • Automating T-SQL Merge to load Dimensions (SCD)

    April 6th, 2012 in Blog, Business Intelligence, Integration Services, SQL Server

    This is the 3rd post in the Frog-Blog series on the awesomeness of T-SQL Merge. Post 1: Introduction to T-SQL merge basics Post 2: Using T-SQL merge to load data warehouse dimensions In this post we’ll be looking at how we can automate the creation of the merge statement to reduce development time and improve reliability and flexibility of the ETL process. I discussed this in the 2nd half of a talk I gave at the UK technical launch of SQL…

    » Read more

  • Using T-SQL Merge to load Data Warehouse dimensions

    January 16th, 2012 in Blog, Business Intelligence, SQL Server

    In my last blog post I showed the basic concepts of using the T-SQL Merge statement, available in SQL Server 2008 onwards. In this post we’ll take it a step further and show how we can use it for loading data warehouse dimensions, and managing the SCD (slowly changing dimension) process. Before we start, let’s have a quick catch up on what an SCD is… What is a Slowly Changing Dimension (SCD)? If you want a full explanation of…

    » Read more

  • PowerPivot Vs QlikView 101 – SQLBits Video

    November 10th, 2011 in Analysis Services, Blog, Business Intelligence, PowerPivot, QlikView

    PowerPivot Vs QlikView 101 – SQLBits Video The video is now available from my PowerPivot and QlikView talk at SQLBits 9 in Liverpool in September 2011. You can download or watch the video here. In this 1 hour session I create interactive dashboards from scratch in both PowerPivot and QlikView, showing how to set up the data model, overcome common pitfalls and build the dashboards. I create almost identical dashboards in both systems and…

    » Read more