For the occasions that you need to check the backup history of your SQL Instance this script is helpful. /****************** * Title: View Latest Backup History * Notes: Provides detail on the last backups and when they took place. *******************/ SELECT bs.[database_name] AS DatabaseName ,bs.backup_start_date AS DateStart ,bs.backup_finish_date AS DateFinished ,DATEDIFF(MI, bs.backup_start_date, bs.backup_finish_date) AS DurationMin … Continue reading View SQL Server Backup History
Author: jlmcdermott
Create a Slowly Changing Dimension Type 2 with T-SQL
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
Conditional Formatting Based on Field Value in Power BI
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
Calculate Age in DAX from Birthday
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
Create a Quick Date Table in Power BI
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
Dynamic Data Masking with MS SQL Server
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
Copy Production SQL Database to Development
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
Create Display Folders in Analysis Services Tabular Models
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
Custom Keyboard Shortcuts in SQL Server Management Studio (SSMS)
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)
View SQL Server Properties
This script gives you basic information on a SQL Instance such as version, edition, server collation, and instance level switch info.
