Business Intelligence

  • Tabular Cube Processing Report

    I have created a Power BI report which provides detail on the state of processing in a Tabular Cube.The report uses the cube’s dynamic management views to provide information about the cube’s partitions. To use the tabular cube processing report, you need to insert the following information: Server Database (Cube Name) Once entered and assuming the connection is fine you need to accept some native queries. These statements are select…

    » Read more
  • How to block the creation of a classic workspace in powerbi.com when a new MS Team is created!

    There is now a new tenant setting for Power BI admins to prevent users from creating classic workspaces in Power BI! This includes from places like MS Teams! Hurrah! If you’re a Power BI Admin you can now enable ‘Block classic workspace creation’. To do this go to the powerbi.com portal and click the ‘settings’ icon in the top right hand corner of the screen. Click on ‘Admin portal’ and then locate and…

    » Read more
  • My experience in obtaining an MCSE

    Background I have recently completed my MCSE in Data Management and Analytics, and I wanted to share my experience of working towards and passing the exams that have led to me getting this MCSE. In this post I will cover some of the challenges I faced and provide some tips and advice, hopefully others following the same certification path, or other paths, will find this useful. I am a business intelligence developer at Purple Frog, I have…

    » Read more
  • ADF: Where have my connections gone?

    If you’ve been doing any development work in ADF this week you might have noticed that “Connections” has moved. But where has it gone? When you click onto “Connections” now you’ll receive the following message: Clicking this button takes you to the new Management Hub, the new fourth icon which goes alongside the existing three. Within this area you now have access to your Linked Services, Integration Runtimes…

    » Read more
  • Using ConcatenateX in PowerBI to return multiple values.

    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

    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

    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

    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?

    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

    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