How to easily limit the number of rows in your Excel Power Pivot model

I am currently working with an Excel Power Pivot model with about 20+ million rows in the model, and as there are a lot of Excel reports in the model and some of the calculated fields are pretty complex it takes some time when I move calculated fields, format them or change them.

The model retrieves data from a SQL database so the table queries are either Stored Procedures or handwritten SQL statements.

So in order to quickly reduce the number of rows in my biggest fact table I have added

SET ROWCOUNT 2000

Before my in this case Stored Procedure

And it validates

 

And after saving and thereby and update – I only have 2000 rows in my fact table

And then when I need the full dataset I use

/* statement */ to turn it into a comment

 

By the way use the /**/ to add comments to your queries so you create short description on what your query is intended to do.

One thought on “How to easily limit the number of rows in your Excel Power Pivot model

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s