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
MaxItaly
Helper III
Helper III

Best Practices with very large SQL Server Views

Hello everyone,

I'd like to ask your opinion about how to handle very large SQL Server Views.

We use SQL Server, and we have some views that operate on a large fact table (15+ million rows).

We'd like to create some reports with Power BI based on them.

My first thought was to create a table where I import the results of the view, and then use that table as data source (I wanted to limit possible problems).

The import of the data has been quite slow, but it worked (~250 MB .pbix file), and I also managed to publish this data set.

I thought to keep working this way, but then I realized that the table I created on SQL Server was something like 15+GB.

I can't afford to create tables this big, and, furthermore, I discovered that Power BI does not manage to automatically refresh the data because of the 10+ GB of raw data.

How would you act in such a situation?
If I tried to import directly from the view, but I encountered some error (like timeout). Anyway I would not be able to refresh the data, I guess.

If I direct query from the view, it will take way too much time to render a report, making Power BI useless (SQL Server takes 40+ minutes to elaborate the view).

Do I have to direct query from the table created from the view?

Do I have other possibilities, like trying to reduce the raw data by creating "normalized keys"?

I never encountered this problem, so I'd like to hear your best practices about it...

Many thanks...!

1 ACCEPTED SOLUTION

Normalizing is an option worth trying. Not only because it reduces the total amount of data, but also because the tabular engine likes it more: Simply spoken: Tabular has no problem with long (narrow) table, but will tend to slow down with (even short) wide tables.

 

So everything that will reduce the number of columns (and their cardinality) will help you here.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

Well, you could post the SQL here and see if it can be optimized. Otherwise, the general recommendation is that you create a cube and hook Power BI to that. 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Yes, that's a possibility...but I'd like to avoid that, since the data is quite dirty and I wanted to move the calculation work to Power BI.
If I created a cube on my data and import the cube to Power BI, would the cube weight much less than the import of the data?

That may partially solve my problems: I could create the cubes from the tables created from the views. In this way, I may import the cubes to Power BI, but I'd have a lot of space occupied by such tables in SQL Server Database  😞   .

Normalizing is an option worth trying. Not only because it reduces the total amount of data, but also because the tabular engine likes it more: Simply spoken: Tabular has no problem with long (narrow) table, but will tend to slow down with (even short) wide tables.

 

So everything that will reduce the number of columns (and their cardinality) will help you here.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

I guess I'll try normalizing the data...

20+ columns for a fact table are way too much, and I guess it's easier than creating a cube and use it by direct query.

Thanks.

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.