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 […]
Posts
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 […]
It’s slow, it’s fast, it’s Parameter Sniffing
A classic problem in relational databases. I see this come up again and again. SQL report writers in my department will be stumped when the query they setup for a reporting services report using parameters will run really fast in testing and then be very slow when a specific set of parameters are selected. SQL […]
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 […]
Introduction to Transactions in SQL Server
Relational databases are governed by the A.C.I.D principle; these are a set of properties of database transactions to guarantee the integrity of the data. The name ACID is an acronym for the four components that make it up: Atomic. All grouped statements perform as a single unit of work. Either all of them succeed or […]