Data Integrity Issues

From DataSelf Knowledge Base
Revision as of 16:18, 4 June 2014 by Dataself (talk | contribs) (Created page with "{{#breadcrumb: }} Data integrity issues can cause several types of data import issues such as: * The whole auto refresh hangs * The auto refresh quits before completing * The...")
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

Data integrity issues can cause several types of data import issues such as:

  • The whole auto refresh hangs
  • The auto refresh quits before completing
  • The auto refresh completes but have incomplete data (ex.: one table was not completely refreshed).

Here are some of the most popular data integrity issues:

Invalid Date Fields

DataSelf uses MS SQL Server as its database engine. MS SQL Server only accepts date values as follows:

  • Between 1/1/1753 to 12/31/9999.
  • Consistent with calendar periods (examples of invalid dates: Feb 29 for non-leap years, or April 31, or a month outside 1 to 12 range).
  • In most cases, NULLs are accepted.

It's not uncommon for certain source databases to have date values that don't fit the rules above. That typically happens when the entry of date fields are not properly validated.

When that happens, we recommend running a report (such as Crystal or Excel) from the table having issues directly from the source database. Pull all date fields into the report.

  • To find date values outside of the accepted range: Run the report sorting for one of the date fields at a time. Sort ascending and descending and see if you find invalid date values.
  • To find dates not consistent with calendar periods: This can get a bit trickier. You may use Excel formulas to extract year, month and days from a date field and then analyze them apart (ex.: sort by month and see if any outside of 1 to 12 range).

If you find issues, be sure to clean them up. If this kind of issue continues to occur on an ongoing basis, we recommend setting up procedures in your source system to validate dates or removing the offending date fields from DataSelf BI.