Forum Discussion

EVEAdmin's avatar
EVEAdmin
Post Patron
7 years ago
Solved

Import tables or write SQL query? The debate

Please share your opinions on this subject. I successfully connected to my remote MSSQL Server and can import those tables necessary to generate reports.  When I import tables, I can hide/delete co...
Need Help
  • Mark_Timson's avatar
    Mark_Timson
    7 years ago

    Using direct query can help if your table is too large to import. From what I can tell trying to import using params in power bi is pretty difficult. I tend to create a sql view with ctes to act as params and use another cte to bring it altogether in view to only return and smaller data subset and then import in with power bi. 

     

    The import is fairly quick for tables under 30 million rows.

  • EVEAdmin's avatar
    EVEAdmin
    Post Patron
    7 years ago

     

     

    Anonymous wrote:

    HI EVEAdmin ,

    Actually, power bi sql connector also support use t-sql query to choose specific fields. You can find it in advanced option, sql statement:

    Quickstart: Visualize data using the Azure Data Explorer connector for Power BI

    Regards,

    Xiaoxin Sheng

     

    Anonymous many thanks, appreciated.
    I am aware of that option but I must say I never used it to filter records when I import a table. 

    Say I am importing 4 tables, shall I just enter 4 select statements, separated by ; to get all records as needed?
    Alternately, I can just write the complete T-SQL query, including JOIN etc, and import the exact records and fields. Then user Power BI only to generate reports and skip the modeling process.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi EVEAdmin ,

      Actually, you need to write four queries for these query tables.(it will change navigation steps and direct to use query to choose which tables and fields imported to power bi.)

      let
    tquery="SELECT * FROM TABlE1",//sample
    Source = Sql.Database(Server, Database, [Query=tquery]),
    Othersteps=xxxxxx
in
    Othersteps

      Regards,

      XIaoxin Sheng

      • EVEAdmin's avatar
        EVEAdmin
        Post Patron
        7 years ago
        Anonymous wrote:

        Hi EVEAdmin ,

        Actually, you need to write four queries for these query tables.(it will change navigation steps and direct to use query to choose which tables and fields imported to power bi.)

        let
    tquery="SELECT * FROM TABlE1",//sample
    Source = Sql.Database(Server, Database, [Query=tquery]),
    Othersteps=xxxxxx
in
    Othersteps

        Regards,

        XIaoxin Sheng

        Got it, thank you. One query for each set of data I wish to import to Power BI.