IDEA Limitations

5 posts / 0 new
Last post
Brian Element's picture
Brian Element
Joined: 07/11/2012 - 19:57
IDEA Limitations

I found this on the IDEA forum and thought it would be of interest:

The limitations in IDEA are  :

•Maximum number of records in a database is 2,147,483,647.
•Maximum record length in a database is 64,512 bytes.

•You can append or concatenate up to 32,768 databases, in the order selected, into a single database.
•A Numeric field cannot hold more than 17 digits, including decimal places. Any digits after the 17th digit will change to a zero. IDEA rounds values to the 16th digit.
•A Character field cannot contain more than 1024 characters. Except in the Search task, where the Text to find field accepts a maximum of 1,000 characters.
•A field name can only contain letters, numbers, and underscores. The first character must be a letter. The maximum length of a field name is 40 characters.
•Pivot Table maximum size is approximately 50,000 rows x 2,000 columns. However, when no row or columns fields are defined for a table, the page limit for data items is 50,000.
•Pivot Table exports to Microsoft Excel are maximized at 256 column items.
•Gap Detection limitations:
•Numeric and Date Gap Detection are limited to a maximum of 10,000 gaps. If the total number of gaps detected is more than 10,000, IDEA will display the first 10,000 gaps only.
•Character Gap Detection is limited to 256 unique key values. The results output only displays the first 256 key values.
•Expanding a gap entry in the Gap Detection results output will display a maximum of 100 items. If the number of missing items in the gap is greater than 100, the first 100 items are displayed followed by a message indicating the number of items not displayed.
•For Date Gap Detection, if you want to specify a criteria and a range, it is recommended to first set the criteria using the Criteria property in the Properties window and then use the Gap Detection task to specify the range.
•Maximum Results output is 4,000 rows by 4,000 columns.
•Database exports to Microsoft Excel are maximized at 65,535 records for Excel 97-2003 and 1,048,576 records for Excel 2007 or at 255 fields for all versions of Excel.
•Extractions are limited to 50 during a single run of the task.
•Stratification limitations:
•In the Stratification task, you may use up to 1,000 stratification bands.
•A Stratification result is limited to 80 groups. If there are more that 80 unique values in the field selected to group by, only the first 80 will be displayed in the result.
•The Chart Data task can display a maximum of 500 records in a graphical format.
•The preview in Microsoft Excel Import is limited to 5,000 rows and 250 columns.
•Equations can be a maximum length of 10,000 characters and wrap within the frame of the Equation Editor area.
•IDEA Ribbon limitations:
•Supports a maximum of 256 commands.
•You can only insert a maximum of nine custom tabs before the File tab or between other pre-defined tabs. There is no limit to the number of custom tabs you can add after the last pre-defined tab (Macros tab).
•You can only insert a maximum of nine custom groups before the first pre-defined group in a tab or between other pre-defined groups within the tab. There is no limit to the number of custom groups you can add after the last pre-defined group in a tab.
•The Library can contain a maximum of 50 Library groups (a combination of default and custom groups) and each Library group can contain a maximum of 200 files.
•Any pre-IDEA VersionNine databases exported as Tab Separated Variables files (*.tsv) that contain Time or Numeric fields will not be imported correctly into IDEA Version Nine.
•Editable and Virtual Time fields are not supported in pre-IDEA Version 7 databases.
•Copying or moving databases or files between projects using Windows Explorer is not recommended and could result in database corruption. Databases and files should either be imported, copied or moved using the File Explorer in IDEA, or copied from the Library.
•It is recommended to always wait for a task to complete entirely before attempting to perform another task on the same database. Failure to do so could result in corrupting the database.
•Caution should be used when canceling an operation that replaces existing files. When executing tasks, if you select Yes to replace existing files and then try to cancel the operation, the target files could have already been deleted and cannot be recovered.

Baricha Saddam Ali
Joined: 04/13/2017 - 03:47

Hello Brian
You just mentioned, you can append or concatenate up to 32,768 databases, in the order selected, into a single database. What script can i use to append more than one file, I have 400 different data bases and would like to append it to one huge database.

Brian Element's picture
Brian Element
Joined: 07/11/2012 - 19:57

Hi Baricha, you can find the script here that will hopefully help you out:

Good luck.


Joined: 06/06/2017 - 10:54

After helping 2 audits using IDEA I wanted to make a report about the gains and limitations for IDEA. I was looking for something more specific and not technical.
I wanted to show major gains VS Excel and also limitations.
I have noticed that doing something simple as creating a new date field that adds 60 years and 5 months and 2 days is extremely difficult to be accurate as I need to take into account Leap Years and different maximum days per month as on excel it is really easy
Do you wanna share other limitations that you have found or similar situations where it isn't a limitation but it is actually harder than excel?

Brian Element's picture
Brian Element
Joined: 07/11/2012 - 19:57

Hi myrahz,

Thanks for the comment.  Another alternative to using the equation editor is to create a custom function.  I have attached one that should do what you want and the syntax is #AddToDate(Date Field, Year increment, month increment, day increment), so you can develop other items.  Also in the next version of IDEA (10.3) we will be able to develop custom functions in python which will open up many new and interesting functions that will be able to be created.

Lots of time the speed gains is in the automation of the script so the next time it takes a few minutes and minimal human intervention.

Appreciate the comments.