Dynamics 365 F&O/AX Dynamics 365 CE / CRM Dynamics GP Business Central / NAV UG Dynamics SL Power Platform Fabric UG MS Copilot UG

[Colleen Brassell]

Hello, looking for some updated discussion around this topic.  We are currently running NAV2013R2 and have @5 years of transactional data in our database.  We would like to remove a good chunk of that and just leave the transactional db with 2 years plus current year, that is all the functional areas need.  However, Finance has to be able to easily access up to 7 years of data from the transactional system for tax/audit purposes.  

Can you tell me what you are doing to reduce the size of your transactional db while keeping the archived data easily accessible when needed?  We are hope ing to institute an annual policy as opposed to waiting until upgrade time.   

Thanks for your input. 

——————————
Colleen Brassell
IT Projects Coordinator
Westinghouse Lighting
Philadelphia PA
——————————

[Ian Ray]

“Date Compression” is a way to remove the excess data. This will summarize data by time period, speeding up transactions.

For archiving, the only method I’ve tried is to copy the data to a separate database and create UNION queries between the current and past data. That is:

SELECT * FROM (current data) UNION ALL SELECT * FROM (archived data)

You may also want a specific cutoff date, e.g. using WHERE [Posting Date] >= ’20xx-01-01′ for current data and WHERE [Posting Date] <= ’20xx-12-31′ for archived data.

If you happen to use Jet with the universal connector, a query like this can be made in t-sql as a view that is accessed by staff in Excel. Power Query could also be used, but I am most familiar with the process in Jet.

I am not sure if there is a good method for this if you want to access all of the transactions in NAV directly. I suppose a separate database could be made and two (or more) reports run. Someone else may have a better idea for this scenario.

——————————
Ian Ray
Cypress Grove
Arcata CA
——————————
——————————————-

[Lewis Rosenberg]

If you are running Change Log or (Lanham) EDI, there are probably lots of records that you no longer need that you can purge.

Lots of deletion routines here:  
  /Departments/Administration/IT Administration/Data Deletion

Lanham EDI:  
   /Departments/Electronic Data Interchange/Processing/Administration – Purge E.D.I. Receive Documents.
  /Departments/Electronic Data Interchange/Processing/Administration – Purge E.D.I. Send Documents.

——————————
Lewis Rosenberg
IT Manager
Mars Fishcare
Chalfont PA

NAVUG Board of Advisors, Programming
NAVUG Programming Commitee
NAVUG Membership Committee
——————————
——————————————-

[Krupesh Daiya]

Hi 

Directly out of the box solution is not available in NAV. 

However using Jet Enterprise you can flush data into Jet database warehouse for historical and reporting purposes. You will to create another database and load opening balance for current data. 

——————————
Krupesh Daiya
Mumbai, India
——————————
——————————————-

[Andrew Good]

What level of detail does finance require for tax/audit purposes? Are the G/L entries sufficient or do they require posted documents as well? Understanding these requirements will help guide you through the process.

What is your compelling reason to archive data?

——————————
Andrew Good
President
Liberty Grove Software
Oakbrook Terrace IL
——————————
——————————————-

[Victor Diercksen]

In this day of cheap hard drive space and improved database indexing, I’m a little curious as to why this is a concern for you? 

If you really want or need to remove the archived data, my best suggestion is to re-implement.  You can pull over 7 years of G/L entries very easily into a new version but start from scratch with a new version and a clean database. 

Good luck in your project,
Victor

——————————
Victor Diercksen
Director of Business Technology
PermaPlate Company, Inc.
Salt Lake City UT
——————————
——————————————-

[Kris Ruyeras]

I’m with Victor on this. One of the reasons why we chose the upgrade is due to the fact storage is much cheaper and with MS continue to improve indexing. 
Otherwise Re-implementation would help as well.

——————————
Kristoffer Ruyeras
Director of Business Technology
Bellegrove Medical Supply
Seattle, WA
——————————
——————————————-

[Alex Chow]

Are you just looking to reduce your database size? Or reduce the transactional data?

Having your history for 5 years is practically nothing. It’s not uncommon for companies to have transactional data starting from 1998 or whenever they first implemented NAV.

——————————
Alex Chow
Dynamics NAV MVP
AP Commerce
Los Angeles CA
Blog: http://www.dynamicsnavconsultant.com
——————————
——————————————-