cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ElizabethTachji Regular Visitor
Regular Visitor

Re: New to BI. Writing SQL queries to parse data. I am obviously missing something.

Hi,

 

I would like to add here, in Power BI Desktop, query Editor, you have the option to disable the load query so the query is not refreshed.  When you disable the load, you will not slow down the performance of the servers and will improve the performance of Power BI Desktop report.

 

Cheers

Elizabeth Tachjian

Analytics Realtime

elizabeth@analyticsrealtime.com.au

Elizabeth Tachjian
calerof Member
Member

Re: New to BI. Writing SQL queries to parse data. I am obviously missing something.

Hi @anithat  and supporting team , @ElizabethTachji ,

 

I performed your procedure, only modifying the query to the table I need, as follows:

Custom query 1.png

 

But I'm still getting this error:

Custom query 2.png

 

Could you please help?

 

Best regards,

 

Fernando

 

 

calerof Member
Member

Re: New to BI. Writing SQL queries to parse data. I am obviously missing something.

The problem stated here persists for me, but I managed to solve my issue of not being able to upload on table that didn't appear as follows:

  1. The database has more than 10,000 tables
  2. In SSMS all the tables are visible
  3. I deducted after a very long week of insomnia that the problem was the limit of number of tables read with Get Data, SQL Server
  4. So I tried the procedure explained above, with no results
  5. Then I tried it with the blank query option, and voila

I think it's still not resolved using Advanced Options, so I'll leave it open for your comments.

 

Best regards,

 

Fernando

 

alwweb Regular Visitor
Regular Visitor

Re: New to BI. Writing SQL queries to parse data. I am obviously missing something.

@calerof .  At this point in the Process your query needs to be SQL.  It is in M which is used a little later in the process. 

 

Try the following and replace yourtablename wiht the name of the table that you want to retrieve. Once you have that working, you can go from there to get exactly the data that you want.

 

SELECT TOP 100  *

FROM yourtablemame;

 

 

alwweb Regular Visitor
Regular Visitor

Re: New to BI. Writing SQL queries to parse data. I am obviously missing something.

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.

Highlighted
BB9 Frequent Visitor
Frequent Visitor

Re: New to BI. Writing SQL queries to parse data. I am obviously missing something.

This is exacly what we have been doing with one additional step which may not be applicable for every one. 

 

When we want to refresh/schedule the data and if we are using the first approach to import all the tables, the performance of the SQL server will be impacted.

1. I have written a query to pull all the filtered data from multiple tables with huge number of rows.

2. Schedule a job to retreive all the information and enter into a different table in a different database.

3. Schedule a refresh with in PowerBI to get the updated data.

 

Reasons to do this: The only SQL server we have is being used by multiple SQL developers to run reports every day. When we try to extract the data with either of these approaches, it impacts the performance of the SQL server. By doing this, the data will be retreived overnight and copied into a different database. We use this database to connect through PowerBI and providing datasets to the Finance team. It has been working so far. I am not saying this solution will work for everyone, but in order to avoid the perf, issues, we thought this might be the best way.

 

I would be happy to hear if anyone has any other ideas to make it more efficient. Thanks for reading!

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 232 members 2,321 guests
Please welcome our newest community members: