4 Replies Latest reply: Jan 18, 2013 7:43 AM by Wtrdev RSS

    Copying maps from a Test app to a Prod app directly (no excel download)

    Wtrdev
      All, We maintain approx 400 locations with many units using independent maps. Is there a way to directly copy maps from a Test environment to production without having to download all the dim maps into excel then upload them...

      Our Test and Dev environment are migrated to maintain consistency. So the partition keys match up. Thanks
        • 1. Re: Copying maps from a Test app to a Prod app directly (no excel download)
          SH
          The tDataMap table in the FDM database maintains all the current mapping tables for each location. If as you have said all your location partition keys are the same across the environments you could migrate the mappings at the DB level by performing an export from the the tDataMap table on the Test environment and then an import into the tDataMap table on Prod after truncating it. There are also a number of API calls that allow the bulk export and import of mappings within the FDM object model so you could try that route also.
          • 2. Re: Copying maps from a Test app to a Prod app directly (no excel download)
            Wtrdev
            Thank you for the quick response, I suspected it was possible to copy from one application to another, but would anyone happen to have a clean step by step to copy maps? Ty
            • 3. Re: Copying maps from a Test app to a Prod app directly (no excel download)
              beyerch2
              This example does a little more than you ask for, but it should show you what you are looking for...

              A couple comments on the below script :

              - It seems my pasting added extra rows between lines. I don't think it will cause you issues if you run this in query analyzer; however, if it does, I apologize in advance.
              - I am clearing out data in the Target app, you may or may not want to do that depending on your specific need.
              - I clear Data and DataMap tables by iterating through them using some SQL Server specific magic. If you are not using SQL Server, this script may not help you much if you need to do the same. (I expect you will since you want to copy maps)
              - If you want to copy the historical maps / data (DataSeg DataMapSeg) you can re-use the same logic I used to do the clearing, though be careful about Identity fields (see next comment)
              - You have to be very aware of the Unique identity fields as they are used to relate data between tables. Migrating just the maps, but not the location is a "dangerous" idea since if the location tables are not the same in the two applications, the mappings and such may no longer be associated to the proper location. This is one of the main reasons I cleared all of the tables to start with in the Target DB. The other thing I did to be sure everything worked properly was to re-import some of the tables by disabling the identity value generation to ensure that row id's matched between the databases. (i.e. EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL') After moving the data over, be sure to turn it back on though!

              If you have questions, let me know. If this helps, feel free to assign points. It seems I haven't received any points in the past few months of answering questions..... sniff sniff cry

              Thanks

              Charles

              -- NOTES
              
              -- ##TARGETDB## - Replace this with the name of the Database that you want to SYNC
              
              -- ##SOURCEDB## - Replace this with the name of the Database that is the data source
              
              -- disable referential integrity
              
              EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
              
              --Clear User Security
              
              TRUNCATE TABLE ##TARGETDB##.dbo.tSecUserPartition
              
              TRUNCATE TABLE ##TARGETDB##.dbo.tStructPartitionLinks
              
              delete from ##TARGETDB##.dbo.tStructPartitionHierarchy
              
              TRUNCATE TABLE ##TARGETDB##.dbo.tDataArchive
              
              TRUNCATE TABLE ##TARGETDB##.dbo.tDataCheck
              
              --Attempt to clear out the tDataMapSeg tables
              
              EXEC sp_MSForEachTable '
              
              DECLARE @TableName VarChar(100)
              
              Set @TableName = PARSENAME(''?'',1)
              
              IF left(@TableName,8) = ''tDataMap''
              
              TRUNCATE TABLE ##TARGETDB##.?
              
              '
              
              GO
              
              --Attempt to clear ou the tDataSeg tables
              
              EXEC sp_MSForEachTable '
              
              DECLARE @TableName VarChar(100)
              
              Set @TableName = PARSENAME(''?'',1)
              
              IF left(@TableName,8) = ''tDataSeg''
              
              TRUNCATE TABLE ##TARGETDB##.?
              
              '
              
              GO
              
              TRUNCATE TABLE ##TARGETDB##.dbo.tLogActivity
              
              TRUNCATE TABLE ##TARGETDB##.dbo.tLogProcess
              
              TRUNCATE TABLE ##TARGETDB##.dbo.tDataArchive
              
              delete from ##TARGETDB##.dbo.tSecUser
              
              delete from ##TARGETDB##.dbo.tPOVPartition
              
              TRUNCATE TABLE ##TARGETDB##.dbo.tBhvValEntItem
              
              delete from ##TARGETDB##.dbo.tBhvValEntGroup
              
              TRUNCATE TABLE ##TARGETDB##.dbo.tBhvImpItemFile
              
              delete from ##TARGETDB##.dbo.tBhvImpGroup
              
              -- RECOPY Data from Prod DB to Budget DB
              
              insert into ##TARGETDB##.dbo.tBhvImpGroup
              
              select * from ##SOURCEDB##.dbo.tBhvImpGroup
              
              insert into ##TARGETDB##.dbo.tBhvValEntGroup
              
              select * from ##SOURCEDB##.dbo.tBhvValEntGroup
              
              insert into ##TARGETDB##.dbo.tBhvValEntItem
              
              select * from ##SOURCEDB##.dbo.tBhvValEntItem
              
              insert into ##TARGETDB##.dbo.tBhvImpItemFile
              
              select * from ##SOURCEDB##.dbo.tBhvImpItemFile
              
              insert into ##TARGETDB##.dbo.tPOVPartition
              
              select * from ##SOURCEDB##.dbo.tPOVPartition
              
              insert into ##TARGETDB##.dbo.tStructPartitionHierarchy
              
              select * from ##SOURCEDB##.dbo.tStructPartitionHierarchy
              
              insert into ##TARGETDB##.dbo.tStructPartitionLinks
              
              select * from ##SOURCEDB##.dbo.tStructPartitionLinks
              
              insert into ##TARGETDB##.dbo.tSecUser
              
              select * from ##SOURCEDB##.dbo.tSecUser
              
              insert into ##TARGETDB##.dbo.tSecUserPartition
              
              select * from ##SOURCEDB##.dbo.tSecUserPartition
              
              --Attempt to import data
              
              SET IDENTITY_INSERT ##TARGETDB##.dbo.tDataMap ON
              
              insert into ##TARGETDB##.dbo.tDataMap (PartitionKey, DimName, SrcKey, SrcDesc, TargKey, WhereClauseType, WhereClauseValue, ChangeSign, Sequence, DataKey, VBScript)
              
              select PartitionKey, DimName, SrcKey, SrcDesc, TargKey, WhereClauseType, WhereClauseValue, ChangeSign, Sequence, DataKey, VBScript from ##SOURCEDB##.dbo.tDataMap
              
              SET IDENTITY_INSERT ##TARGETDB##.dbo.tDataMap OFF
              
              
              -- enable referential integrity again
              
              EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
              
              GO
              
               
              • 4. Re: Copying maps from a Test app to a Prod app directly (no excel download)
                Wtrdev
                Thank you all for your help. I was able to get into the maps via RDC with SQL Develope. Very nice GUI that allowed for filtering on the specific partition key and deletion of the specific problem records. Next step, working on bulk copies from Prod to Test to Dev. Looking promising.