Solving NOLOCK SQL Power BI issues with Sample Code for STEEMSQL

Power BI is an awesome tool for connecting to a wide variety of data sources and creating meaningful reports. It is used extensively by myself and other members of the Business Intelligence Steemit Community (#bisteemit) to connect to SteemSQL and carry out analysis on the data.

The SteemSQL database is held and managed by Hive account@arcange and you can find more details on both his steemit blog and on the website http://www.steemsql.com/

The log in details to connect to the database is :
Server: sql.steemsql.com
Database: DBSteem
User: steemit
Password: steemit

It is a live database and is continuously updating with data from the blockchain. When connecting Power BI to a live database, to ensure that you do not cause a lock in the database you must use a NOLOCK query.

Creating a NOLOCK query in Power BI is not automatic, and the steps taken by connecting with the query editor will cause problems with a live database.

To ensure that your query is NOLOCK, you can use the advanced options in the connection wizard in Power BI.

In this tutorial we will look at connecting to the SteemSQL database using NOLOCK queries.

You will learn

• How to connect and pull an entire table using NOLOCK
• How to apply a simple filter to the table
• Overcoming reserved words on columns
• Converting date time format to a date
• How to update existing power bi queries to NOLOCK

Queries used in the tutorial

SELECT *
FROM TxTransfers (NOLOCK)

SELECT *
FROM TxTransfers (NOLOCK)
WHERE [FROM] in ('paulag', 'steemitbc')

SELECT *
FROM comments (NOLOCK)
WHERE author in ('steemitbc')
AND created >= CONVERT(datetime,'10/01/2017')
AND created< CONVERT(datetime,'11/01/2017')