Forum Discussion

DMatus's avatar
DMatus
Icon for Advocate II rankAdvocate II
10 years ago
Solved

Problem recovering large amounts of tables from SQL Server

My SQL Server DB has about a hundred tables (About 40 something fact tables, the rest being dimension tables), I realize it would be impossible to bring so many tables with their respective rows into a single .pbix file, so I removed all the rows from all the tables (after a backup oc), however even doing this I'm having trouble obtaining all the tables from the DB, after a while it just stops working, no crashes or hangups or timeouts.

 

I guess what I'm asking is, is there a way to obtain an empty schema of my DB into a pbix file? The idea being that my end-user can just delete the (empty) tables he won't need for his report, hit refresh and obtain the data.

Need Help
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    10 years ago

    Hi DMatus,

     

    In your scenario, as there are large amount of data in data source, I would suggest you connect to the SQL Server database use DirectQuery mode instead of Import mode.

     

    Also it seems that client users will need data from different tables in this SQL Server database, right? If that is a case, I would suggest you create a query parameter, which contains a list of values about table names, then you can modify the connection string in Advanced Editor based on this parameter.

     

    Reference:

    Deep Dive into Query Parameters and Power BI Templates
    Power BI Desktop Query Parameters, Part 1

     

     

    If you have any question, please feel free to ask.

     

    Best Regards,
    Qiuyun Yu

     

2 Replies

  • ashishrj's avatar
    ashishrj
    Icon for Power Participant rankPower Participant
    10 years ago

    DMatus You can create a script file for your existing database and then restore this script to a new database and then try connecting pbix to this new database which contains just the schema and not data. Hope this should work !

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Icon for Community Support rankCommunity Support
    10 years ago

    Hi DMatus,

     

    In your scenario, as there are large amount of data in data source, I would suggest you connect to the SQL Server database use DirectQuery mode instead of Import mode.

     

    Also it seems that client users will need data from different tables in this SQL Server database, right? If that is a case, I would suggest you create a query parameter, which contains a list of values about table names, then you can modify the connection string in Advanced Editor based on this parameter.

     

    Reference:

    Deep Dive into Query Parameters and Power BI Templates
    Power BI Desktop Query Parameters, Part 1

     

     

    If you have any question, please feel free to ask.

     

    Best Regards,
    Qiuyun Yu