Optim - Test Data Management and Archiving

Expand all | Collapse all

Using Views in Access Definition

  • 1.  Using Views in Access Definition

    Posted Fri February 14, 2020 04:04 PM
    While working on resolving the issue I recently posted about Optim corrupting NVARCHAR(MAX) data fields, I decided that we could possibly work around this issue in the short term by creating a View against the table with the NVARCHAR data and CAST that column as VARCHAR(MAX) since Optim handles VARCHAR fine. The plan was to use the View in the Access Definition. 

    My initial test worked when running just against the View in question....but once I add the View into the Access Definition with a dozen other tables I run into an issue of not being able to traverse the View. I know there is no way to create defined RI Constraints against a View...but it appears you can't create a logical Optim relationship against a view either. Optim only allows tables, not Views, in a relationship.  

    Which makes me question why views are allowed in an Access Definition at all (beyond being the start table) since they are bypassed without a relationship... 

    Anyone else out there run into this issue with Views and were you able to be creative and make it work?

    Appreciate any insight on this one. 


    ------------------------------
    Keith Tidball
    Progressive Insurance
    ------------------------------


  • 2.  RE: Using Views in Access Definition

    Posted Tue February 18, 2020 12:30 PM
    Thanks for sharing Keith and yes, We've run into issues creating views of tables for archiving purposes. Unfortunately, in some cases, we had to create a new table, migrate data to the new table and "gently transform" the data types to play nice with Optim. While this isn't a good best practice, it does, on the other hand, allow us to archive these problem tables. I would not do this if we were migrating data for TDM purposes, but since it's for archiving purposes and the application layer is no longer interfacing with the data in question, the risk is low and we've met our obligation to retain for regulatory or compliance reasons.

    ------------------------------
    Danny Lankford
    3M - IT Manager
    ------------------------------



  • 3.  RE: Using Views in Access Definition

    Posted Fri February 21, 2020 12:49 PM
    We actually were able to get the View to work properly in the Access Definition. There is a Product Options setting to allow this (why it is not a default, I have no idea).

    1. Go into 'Product Options'
    2. Open 'Database' Tab. 
    3. Check off the 'Allow Optim PK/Rels on Db Views' 
    4. Restart Optim

    Once we did this, we were able to add and actually traverse the Table View to select just the related rows we care about.  This view is a temporary workaround to allow us to test an archive and restore process on a table that contains NVARCHAR data which Optim is corrupting, so hopefully we get an iFix for that soon. But until then, this may help others whom need to leverage Views in their Access Definitions. 



    ------------------------------
    Keith Tidball
    Progressive Insurance
    ------------------------------