Author: alexmcqueen

Field Parameters in PBI

Microsoft recently released a brand new feature for public preview called Field Parameters. Field parameters allows users to dynamically change what is being shown within a visualisation. This feature allows a user to easily switch between the analysis of multiple metrics or multiple categorical breakdowns using a very small number of visualisations. Before this feature […]

Dimensional Modelling in Power Query

I have recently been working on a solution centred around reseller sales. We wanted to be able to analyse sales across several different entities such as product, vendor, delivery type and date. The source data comes from a single line of business system which can produce two unique files; a file with a list of […]

Query Folding in Power BI

Query Folding is the ability to generate a single query statement from your Power Query transformations. It allows transformations being performed within Power Query to be pushed back to the database. Many data sources support the concept of query folding including relational databases like SQL Server. There are a number of transformations in Power Query […]

Date / Time Tables in Data Modelling

Date and time tables are commonly found in data models. This includes data warehouse models and models created in BI visualisation tools such as Power BI (PBI). A typical business requirement is to analyse data over a period of time. These tables provide the foundation to do this. In this article I will show how […]

Table Expressions

Table expressions are queries that represent a relational table in SQL Server. They can be interacted with in the same way you would do a normal table. Table expressions are not persistent objects in the database. When they get used within SQL statements the underlying objects making up the table expression can be inlined within […]

Statistics IO

Statistics IO is a command we can use to display statistical information from all executed SQL Server T-SQL statements. We can turn it on for our session using the command SET STATISTICS IO ON and turn it back off with the command SET STATISTICS IO OFF. Statistics IO can be used to help performance tune […]

Dodgy data in a declarative language

Working within the health sector in the UK we have lots of very old systems; some built 20 years + ago. Many have questionable database forms and data validation built into their front end application layers. This can lead to interesting data transformations needed when creating data warehouse data marts. One of these systems stores […]

SQL Window Functions

Window functions allow you to perform calculations against a subset of rows. The result of this calculation is shown against each row. This subset of rows in which you perform the calculation can be thought of as a window. This is achieved by using the SQL OVER clause. Within the OVER clause we can specify […]