Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DMatus
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
v-qiuyu-msft
Community Support
Community Support

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.

View solution in original post

2 REPLIES 2
v-qiuyu-msft
Community Support
Community Support

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.
ashishrj
Power Participant
Power Participant

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors