Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

How to start using SQL (SMSS) instead of importing whole tables for Power BI report

Hi all,

 

At the I import data in my power BI report using "get data" --> "SQL Server Analysis Services database".

However, now I import the whole table, resulting in slow reports.

Recently I have been learning how to write SQL using SMSS, but I have no idea how to use this for my Power BI reports in practice.

I want to learn how to use this in pratice so to reduce loading time of my Power BI report I want to import only a few columns into Power BI using the SQL lines I wrote. 

 

But I have no idea how to do this?

Is the only method using the gear icon next to source in Power BI and paste the SQL code? 

Or can I save the new (smaller) table in SMSS somwhere so I can access this in Power BI?

Or what is the best most proper way to do this?

 

Is someone able to show this using some screenshots? 

 

Kind regards,

 

 

1 ACCEPTED SOLUTION

Hey @Anonymous ,

 

sure, you can also save the SQL query in SQL Server. This is then called a view.

In Power BI you can just select that view and it behaves like a table. In SQL Server this view is a SELECT to the real table.

 

To create the view you have to create it in SQL Server. You can do that like this:

 

CREATE VIEW view_name AS
SELECT *
FROM table_name
WHERE condition;

 

 

And then you can just select the view in Power BI as table. You can also in SQL Server then query the view like this:

 

SELECT * FROM
view_name
WHERE condition

 

 

Here is a short introduction to views in SQL:

SQL: Views - YouTube

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

View solution in original post

12 REPLIES 12
Arul
Super User
Super User

Hi @Anonymous ,

@Greg_Deckler is right,

 

Instead of bringing all the columns to Power BI, you can create view in SQL to keep the needed columns for the reporting and then bring that view into Power BI.

 

Thanks,

-Arul





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


LinkedIn


Anonymous
Not applicable

Hi @Arul 

Thanks, That helpps. Question on SQL; what is the point in writing a SQL query without "create view" ? Because if you do not use "create view" you can't use it for things like Power BI? Why have most SQL I see online not "create view" in their statemetn? 

CNENFRNL
Community Champion
Community Champion

Value.NativeQuery does the trick, I think.

https://blog.crossjoin.co.uk/2021/02/21/query-folding-on-sql-queries-in-power-query-using-value-nati...


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

selimovd
Super User
Super User

Hey @Anonymous ,

 

you can write a Query in SSMS and then just paste the result in Power BI.

For that chose SQL Server as data source and then use the optional SQL statement to paste your query:

selimovd_0-1626360015813.png

 

 

In general you can just load the basic table and then do your transformations like removing columns. Usually that is then translated into a SQL query. That topic is called query folding and you can in Power Query even see the transformed query that is actually sent to the data source. Check the following article:

Query folding | Microsoft Docs

 

As you mentioned to import from SSAS. It's also possible to import data and to write the query in DAX or MDX directly:

selimovd_1-1626360290091.png

 

However from my experience that is often pretty slow, so I would always prefer a relational database like SQL Server.

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 
Anonymous
Not applicable

@selimovd 

Im just asking because it seems weird to me that copying the whole statement is the best way to do this.

Anonymous
Not applicable

Hi @selimovd,

Thank you for your answer. I want to do it using SQL. Then the only way to do this is to paste the whole SQL statement i wrote in that text box you show? Is that the best way to do this?

Isn't there a way to create the table in SQL using SMSS and then simply import that (I have no idea if thats possible)? To avoid having to paste SQL statements in Power bi? 

Hey @Anonymous ,

 

sure, you can also save the SQL query in SQL Server. This is then called a view.

In Power BI you can just select that view and it behaves like a table. In SQL Server this view is a SELECT to the real table.

 

To create the view you have to create it in SQL Server. You can do that like this:

 

CREATE VIEW view_name AS
SELECT *
FROM table_name
WHERE condition;

 

 

And then you can just select the view in Power BI as table. You can also in SQL Server then query the view like this:

 

SELECT * FROM
view_name
WHERE condition

 

 

Here is a short introduction to views in SQL:

SQL: Views - YouTube

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

@Anonymous Create a View?


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

I was going to say the same. View. But view and also pasting same query when making connection give same results. But if its materialized view then yes.

Anonymous
Not applicable

Sorry, not experienced what do you mean by creating a view? 

View is like a Vitrual table. So you write the same SQL statements but instead of creating table you will create a view instead. This way you do not create physical tables. This is some what similar to pasting the sql statement in query box when you make connection in power bi.

Something like this....
Create View 
as
SQL Statement

Anonymous
Not applicable

Thanks, very clear explanation. Question on SQL; what is the point in writing a SQL query without "create view" ? Because if you do not use "create view" you can't use it for things like Power BI? Why have most SQL I see online not "create view" in their statemetn? 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors