Power Pivot: The PivotTable report will not fit on sheet.

During the refresh of data in an Excel Power Pivot next error can occur:

The PivotTable report will not fit on the sheet. Do you want to show as much as possible?

In the past I created an Excel Power Pivot file with multiple sheets. On every sheet one or more pivots or pivot charts are defined. Big question for me: On which sheet does this error occur? Current error message is to general. It does not tell on which sheet the error occurs. After a while I found the root cause of this problem. In the past I made a Pivot chart on some data. The datasheet of this Power Pivot Chart was hidden. Later on I deleted the sheet with the Pivot Chart. The datasheet is not automatically deleted. In this situation the datasheet was still availabel as hidden sheet. I unhide the sheet. When I looked to the data, it contains a big amount of columns.  (Over 256 columns). This sheet with more than 256 column is the root cause of this error. Because the initial Pivot chart sheet was already deleled, I deleted the datasheet. After deleting the datasheet, I was able to refresh all data in my Power Pivot sheet.

Solution: check all datasheets in your Power Pivot sheet for pivots with more than 256 columns.

The alternative for Zipping your SQL backups. Save download and restore time.

Sometimes you need a SQL database backup to analyze. To minimize the download time people compress the SQL database backup with tools like WINZIP, WINRAR, 7ZIP, ARJ etc...... This is nice but there is a more efficient way. First of all, I will explain the download and restore process of a WINZip backup.
You need to uncompress the database backup file before you can start the restore process it self. Example: you receive a SQL database backup of 50 Gb which is compressed to 5 GB. To restore this database, you need much more diskspace:

• 5 Gb for the zip file
• 50 Gb for the backup file
• 50 Gb for the restored database. (assume their is no empty space in the database)

In total 105 Gb of diskspace is needed.


The more efficient way. Use the Backup compression feature. Backup time is much faster because less disk IO is needed to write the backup file. A compressed backup file can be restored without a seperate uncompress proces. This will save a lot of disk space. In the previous example 50 Gb because you do not need to uncompress the WINZIP file. You will receive a 5Gb database which you can directly restore to the 50 GB database file(s).

In the option tab of the Backup database window you will find at the buttom the Set backup compression option. By default it is set to Use the default server setting. You can change this to Compress backup.

On server level you can change the default compression setting to compressed. Retrieve the server properties of the SQL server. Select the Database Settings property. Check the Compress backup checkbox.

As of now every SQL backup will be compressed.

By default, compression significantly increases CPU usage, and the additional CPU consumed by the compression process might adversely impact concurrent operations. Backup compression was introduced in SQL Server 2008 Enterprise. Beginning in SQL Server 2008 R2, backup compression is supported by SQL Server 2008 R2 Standard and all higher editions. Every edition of SQL Server 2008 and later can restore a compressed backup.

More information can be found here.

SQl 2008 R2 Setup fails: ExecuteStandardTimingsWorkflow

I tried to install SQL 2008 R2 Enterprise Edition X64 on my laptop which has a brand new image of Windows 7. All Windows updates are installed. I started the setup of SQl 2008 R2 from the root of my installation DVD. I run the setup with the option 'Run as Administrator'. During the Setup Support Files, the setup progress hangs on: ExecuteStandardTimingsWorkflow.

After a while next error while occur: Error writing to file: X86_Microsoft.VC80.CRT_1fc8b3b9a1e18e3b_8.0.50727.4027_xww_e69378d0.cat Verify that you have access to that directory.

Big question: Which directory?

Solution: Your installation DVD is corrupt. Take another installation DVD to complete succesfully the setup of SQL Server 2008 R2.

Script to generate a time dimension table to use in Power Pivot.

In SQL Server 2012 Power Pivot a new feature is introduced. You can Mark as Date Table. This will enable you to leverage date filtering in Excel. For instance, you can see the revenue totals grouped by different date groupings. For instance per week number, per month, week day number etc. To use this feature you need to have a Time Dimension table.

• Create a time dimension table.  (See later in this blog how to do)
• Mark this table as Date table.
• Link from your Revenue table the column invoicedate to the key of the Time Dimension table.

Now you are ready to use this feature.

To create a Time dimension table you can a script (GlobeBI_DimTime.SQL) which can be downloaded from here. At the end of the script you can specify the start date and end date of the Time dimension entries. 


INSERT INTO Dimtime
SELECT * FROM dbo.F_TABLE_DATE ('20000101','20201231')

In the script Date entries are created from 1 Januari 2000 up to 31 December 2020.