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

[Anonymous]

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,

[Arul]

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

[Anonymous]

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]

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...

[selimovd]

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:

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:

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

@selimovd 

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

[Anonymous]

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? 

[selimovd · 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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Greg_Deckler]

@Anonymous Create a View?

[Karthik_Ram]

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]

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

[Karthik_Ram]

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]

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?