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

    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? 

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      5 years ago

      Anonymous Create a View?

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

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

    • selimovd's avatar
      selimovd
      Icon for Most Valuable Professional rankMost Valuable Professional
      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
       
  • 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.