.pbix excessively large for 5k row dataset

[cpljdford866]

I've encountered an interesting challenge in a .pbix file where my SQL Server data connection seems to be pulling in far more data and/or metadata that isn't actually needed. 

I'm using the Advanced Options within the connection interface to inject raw SQL with limiting criteria and only necessary columns but despite this query only retrieving ~5k rows and 27 columns the .pbix file is almost 500MB in size and takes a considerable amount of time to publish to the PBI Service. 

I've taken the usual suggested steps on eliminating unnessessary columns and limiting query results as much as possible but this challenge persists. The only distinguishing characteristic of my dataset that I believe could be having an impact here is that many the columns are Date values, which the PBI SQL server connection seems to pull in some date hierarchy metadata on.

I would welcome any helpful suggestions or advice on how I can reduce the size of this dataset to allow for faster publishing and data refreshes!

[dcrow5378 · Accepted solution]

Have you tried turning off Time Intelligence, Auto date/time?
File > Options > Current File > Data Load > Time Intelligence > Uncheck Auto date/time.

[cpljdford866]

That did the trick! Thank you very much!

Strange that such a simple feature can cause the .pbix file size to increase by orders of magnitude. My file size is now ~500kb vs 500MB.

[dcrow5378]

Nice!
Yeah, should probably come unchecked out of the box.