Skip to content
October 6, 2013 / Erik Svensen

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.

Advertisements

One Comment

Leave a Comment
  1. milhouse77bs / Nov 1 2013 6:10 pm

    in DB2, you add “FETCH FIRST 1000 ROWS ONLY” at the end of the SQL SELECT.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: