Creating a slowly changing dimension (SCD) is an essential part of Data Warehousing. There are many tools for the job but I prefer to do the work using T-SQL against the database rather than using something like SSIS. This will generally involve staging the data from source and then merging the source copy against target. … Continue reading Create a Slowly Changing Dimension Type 2 with T-SQL
In Power BI you can add conditional formatting to your tables by using hex colour code values from another column.I prefer this method of managing your conditional formatting for two reasons. Its quicker to set up rather than using the colour scale form, and the column can be re-used.In this example I've created a measure … Continue reading Conditional Formatting Based on Field Value in Power BI
The correct way to calculate age in DAX is by using the YEARFRAC() function instead of DATEDIFF() because we can get a more accurate age by using fractions. Create a calculated column named Age You also might want to wrap this calc around an IF statement to account for when some birth dates are blank. … Continue reading Calculate Age in DAX from Birthday
In this post I'll show you how to quickly create a date table in Power BI. To do this you'll need the below DAX and your Power BI report. In your Power BI report go to Modelling in the ribbon and select New Table. In the DAX editor paste the below date code and hit enter. … Continue reading Create a Quick Date Table in Power BI
Post created using SQL Server 2016 SP2 Lets say you have some users who need access to data in a database but you want to obfuscate sensitive data in certain columns. You can do this through masking the data by user roles. Masked data To do this is relatively simple. You need to add additional definitions … Continue reading Dynamic Data Masking with MS SQL Server
Before beginning my SQL development I like to work with the latest version of the database from production.If your database is doing OLTP workloads then I would advise getting a copy from backups. However in this scenario I'm getting a copy of a data mart which supports a SSAS Tabular Model run on a schedule … Continue reading Copy Production SQL Database to Development
Sometimes you can have tables in your tabular data model with too many columns. You can organise them using display folders like in the example below. Example display folder from SSAS World Wide Importers Data Model via Power BI How to You can create a display folder by editing the properties of columns while working … Continue reading Create Display Folders in Analysis Services Tabular Models
You might not be aware that you can create your own keyboard shortcuts in SSMS. Which is handy when you have some SQL but want to perform a quick action without typing it out, for example inspecting tables like below. Example So in the below example I've highlighted my table and hit Ctrl + 3 … Continue reading Custom Keyboard Shortcuts in SQL Server Management Studio (SSMS)
This script gives you basic information on a SQL Instance such as version, edition, server collation, and instance level switch info.
Sometimes you need to investigate emails being sent from SQL Server. This query the last 100 items.
