Best Practices with very large SQL Server Views

[MaxItaly]

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

[Greg_Deckler]

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. 

[MaxItaly]

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  😞   .

[ImkeF · 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.

[MaxItaly]

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.