Forum Discussion
New to BI. Writing SQL queries to parse data. I am obviously missing something.
Solution SageWhen I select from a SQL source in PBI Desktop I always make my queries in the Query Editor like this. I prefer using the advanced editor to write the M# code myself, and use parameters to have my values for Server and Database. I do this for 2 reasons - I can easily create a new query by dublicating one of my other queries and then just replace the T-SQL code and I just have to change 2 values if my data source changes since I just change the parameters and then don't have to change all my queries.
let
Source = Sql.Database(Server, Database, [Query="
--SQL Query Start
SELECT
a.DWID_Customer AS 'CustomerKey'
, a.CustomerNo AS 'Customer No'
, a.Name AS 'Customer Name'
, a.CustomerLabel AS 'Customer'
, a.CustomerPostingGroup AS 'Customer Posting Group'
, a.Country AS 'Customer Country'
, a.City + ', ' + a.Country AS 'Customer City'
, a.SalespersonCode AS 'Salesperson Code'
, a.SalespersonName AS 'Salesperson Name'
, a.SalespersonName + ' - ' + a.SalespersonCode AS 'Salesperson'
FROM D_Customer a
--SQL Query End
"])
in
Source
Helper II
sdjensen wrote:When I select from a SQL source in PBI Desktop I always make my queries in the Query Editor like this. I prefer using the advanced editor to write the M# code myself, and use parameters to have my values for Server and Database. I do this for 2 reasons - I can easily create a new query by dublicating one of my other queries and then just replace the T-SQL code and I just have to change 2 values if my data source changes since I just change the parameters and then don't have to change all my queries.
let Source = Sql.Database(Server, Database, [Query=" --SQL Query Start SELECT a.DWID_Customer AS 'CustomerKey' , a.CustomerNo AS 'Customer No' , a.Name AS 'Customer Name' , a.CustomerLabel AS 'Customer' , a.CustomerPostingGroup AS 'Customer Posting Group' , a.Country AS 'Customer Country' , a.City + ', ' + a.Country AS 'Customer City' , a.SalespersonCode AS 'Salesperson Code' , a.SalespersonName AS 'Salesperson Name' , a.SalespersonName + ' - ' + a.SalespersonCode AS 'Salesperson' FROM D_Customer a --SQL Query End "]) in Source
Could someone please spell this out easier for me? I am not a SQL guy but really need to go this route with my complex BI model. I have many tables loading (some with millions of rows that I would like to try and filter before they load into query editor first) and many editor steps that I feel could be reduced by SQL coding doing a lot of the joins and filters I need done.
My model times out and fails scheduled refresh during business hours a lot due to everything taxing on the SQL server.
Is there a good beginniners book to SQL I could pick up to maybe learn enough to join and filter as I need too?
alwwebAdvocate II
If the SQL refreshes are timing out, you are probably correct that you are pulling too much data at once, but, doing all of the joins in the SQL may also slow it down more.
I am a SQL person first and a Power BI person second, so I look at things a little differently from most people on this forum.
1) I like bringing in all of the tables individually and using the relationships in Power BI rather than "flattening" my data source by doing all of the joins when pulling in the data. This gives me more power for creating my formulas, etc inside of Power BI.
2) I would prefer to bring in one table at a time with a query for each tables (some denormalization where it makes sense by doing the joins) if I need to remove a lot of columns or some of the rows. This way, if the SQL database is optimized properly, it will take much less time to refresh. Rather than pulling in all of the data and then removing a bunch, we only pull what we need.
On your comment from page 3 that I responded to earlier. I realized you missed where the other user was putting in the M code. It is after the import happens, by clicking Advanced Editor on the Home tab of the Power Query Editor.
For books, to understand the concept of normalization that I mentioned earleir in this comment, I recommend any of the edtitions of Jan Harrington's Relational Database Design Clearly Explained. I like the 1st edition best if you can find it in a used book store.
For writing T-SQL (MIcrosoft's flavor of the SQL language - stands for Transact-SQL) there are a huge number of books to choose from. If you are going to be doing a lot of query writing and don't have an IT team to create VIEWS that already have what you need in them, I recommend taking a 3 day course. If you like learning on your own, one of the most popular books are the ones by Itzik Ben-Gan. Either T-SQL Querying or SQL Fundamentals.
I hope some of this helps.
- ElizabethTachji
Helper III
Hi,
Where does the data reside?
Elizabeth Tachjian
Analytics Realtime
