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 @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')
Posted on Utopian.io - Rewarding Open Source Contributors
doesnt it force nolock by views?
Yes as far as I am aware it does. This is for those wishing to now get their heads around a little sql.
Interesting my friend... I love to try...
Thanks Paula. Now I have no excuses not to have PowerBI added to my skill set!
haha I knew I would catch you in my powerbi web
Personally, I like to make sure the SQL works the way I want first and use SQL Developer for that. After that, I shall then put in the query in Power BI.
SQL developer is a foreign language and totally new tool when you don't know sql and just come from an excel background. But when I get to developing more complex queries this will be an option
yes, it is quite fun when you get the hang of it and give your better control.
there's another 2 tool I heard of which I have not tried is Lincpad and Valentina Studio.
only because I'm quite comfortable with SQL developer.
although I did go to the Valentina Studio to read bit about and was bit impress.
maybe something for you to consider https://www.valentina-db.com/en/valentina-studio-overview :-)
I use Valentina Studio for my steemsql SQL queries. It works pretty well once you get the hang of it. And it's free.
Yay... free is good. So, is SQL developer but I see the Valentino Studio has a more funky look than the boring SQL developer. :)
One more thing, I see you use a condition of
author in ('somebody')
That's useful if you want to go with more than one author. In Oracle SQL, when you want only one, the performance is better if you do.
author = 'somebody'
I suspect MS SQL should be the same.
Thank you!
you're welcome :-)
doesnt it force
Don't know for sure but if you have the answer, please do update.
wow, really going to follow you, great work, I will continue to follow you.
Thank you for the contribution. It has been approved.
You can contact us on Discord.
[utopian-moderator]
Hey @paulag I am @utopian-io. I have just upvoted you at 17% Power!
Achievements
Community-Driven Witness!
I am the first and only Steem Community-Driven Witness. Participate on Discord. Lets GROW TOGETHER!
Up-vote this comment to grow my power and help Open Source contributions like this one. Want to chat? Join me on Discord https://discord.gg/Pc8HG9x
Thanks @paulag ! This article help me a lot! :-)