Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 hav...
Need Help
  • selimovd's avatar
    selimovd
    5 years ago

    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
     
selimovd's avatar
selimovd
Icon for Most Valuable Professional rankMost Valuable Professional
5 years ago

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
 
Anonymous's avatar
Anonymous
Not applicable
5 years ago

selimovd 

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