cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DMatus Frequent Visitor
Frequent Visitor

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-qiuyu-msft
Moderator

Re: Problem recovering large amounts of tables from SQL Server

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

 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
ashishrj Senior Member
Senior Member

Re: Problem recovering large amounts of tables from SQL Server

@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 !

Moderator v-qiuyu-msft
Moderator

Re: Problem recovering large amounts of tables from SQL Server

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

 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.