Browse Author

beyerch

Technology expert, voider of warranties, mad scientist.

Synchronizing FDM (9.3.1) Applications w/ T-SQL

At my current location we have 1 main and 2 supporting FDM (9.3.1) applications. The main app is used to load production G&L data, and the supporting apps are used for loading our Budget and Forecast data and all are separated to ensure that a mistake in a multi-load template is minimized.
The problem we have with this setup is that we have to do 3x the maintenance work when it comes to users and locations.

While the users and locations are the exact same in each application, there are some differences; however: a.) Data Maps in Budget / Forecast are simply * to * as we use multi-load templates that have hte actual HFM account/entity names in them whereas production maps account for each company’s GL b.) Categories are different as we limit each FDM app to only the exact Category (Scenarios) required. c.) Periods are different as we limit each FDM app to only the needed periods required.

The solution for me was to implement a SQL direct copy routine for the information I need to move. This helps as I can : a.) automate this task b.) copy only what I want c.) Perform the task relatively quickly.

While the Workbench offers the ability to import / export components, I ran into trouble with it not correctly importing all of the data and it also restrcted my flexibility. Copying the entire database was not viable either as there were pieces I did not want to update….
The script below illustrates how to perform a copy via SQL. *bold*Please note that if you want to use this (or base a script off of this) that there’s absolutely no warranty and its use at your own risk.bold I’m fairly comfortable that this works fine (as I’ve tested this in our Dev), but you should test it on your own as well.

Also note, that this script does not copy ALL tables as I didn’t need them all for my purpose.

The script performs the following :

– Clears data from Target Database – Data Archives – Data Maps – Data
– Logs – Import Groups/Items – Validation Groups/Entities – Users / Partition Security – Partition Hierarchies / Links – Partitions (locations)
– Copies data from Source Database: – Import Groups / Items – Validation Groups / Items – Partitions (locations) – Partition Hierarchy / Links – Users / Partition Security
– Updates Parent Location setting on all Data Load locations (This is since I want all of my data load locations in the Budget / Forecast apps to use a * to * map. I added a new location in the Prod Database which is defined with the * to * mappings and when I copy everything over, I want the locations to use this map instead of their production G&L map.

-=-=-==-=-=-===-=-=-=-=-=-=-=-===-=-=-=-=-=-=-=-=-=-

[[code]]czo0MDk1OlwiLS0gRkRNIERhdGFiYXNlIDxzcGFuIHN0eWxlPVwiY29sb3I6IG5hdnk7XCI+XCdBdXRvIENvcHlcJzwvc3Bhbj4NCi0tIEF1e1smKiZdfXRob3IgOiBDaGFybGVzIEJleWVyDQotLSBEYXRlIDogNy8xLzIwMTANCi0tIERlc2NyaXB0aW9uIDogVGhpcyBzY3JpcHQgaXMgdXN7WyYqJl19ZWQgdG8gYXV0b21hdGljYWxseSAvIG1hbnVhbGx5IHN5bmMgdXAgZGF0YWJhc2UgYmV0d2VlbiBhIFNPVVJDRSBGRE0gYXBwbGljYXtbJiomXX10aW9uIGFuZCBhIA0KLS0gICAgICAgICAgICAgICAgREVTVElOQVRJT04gRkRNIGFwcGxpY2F0aW9uLg0KLS0gTk9URVMNCi0tICMje1smKiZdfVRBUkdFVERCIyMgLSBSZXBsYWNlIDxzcGFuIHN0eWxlPVwiY29sb3I6IG5hdnk7XCI+PGI+dGhpczwvYj48L3NwYW4+IHdpdGggdGhlIHtbJiomXX1uYW1lIG9mIHRoZSBEYXRhYmFzZSB0aGF0IHlvdSB3YW50IHRvIFNZTkMgDQotLSAjI1NPVVJDRURCIyMgLSBSZXBsYWNlIDxzcGFue1smKiZdfSBzdHlsZT1cImNvbG9yOiBuYXZ5O1wiPjxiPnRoaXM8L2I+PC9zcGFuPiB3aXRoIHRoZSBuYW1lIG9mIHRoZSBEYXRhYmFzZSB0aGF0IHtbJiomXX1pcyB0aGUgZGF0YSBzb3VyY2UNCg0KLS0gZGlzYWJsZSByZWZlcmVudGlhbCBpbnRlZ3JpdHkNCkVYRUMgc3BfTVNGb3JFYWNoVGFie1smKiZdfWxlIDxzcGFuIHN0eWxlPVwiY29sb3I6IG5hdnk7XCI+XCdBTFRFUiBUQUJMRSA/IE5PQ0hFQ0sgQ09OU1RSQUlOVCBBTExcJzwvc3Bhbj4Ne1smKiZdfQoNCi0tQ2xlYXIgVXNlciBTZWN1cml0eQ0KVFJVTkNBVEUgVEFCTEUgIyNUQVJHRVREQiMjLmRiby50U2VjVXNlclBhcnRpdGlvbg17WyYqJl19ClRSVU5DQVRFIFRBQkxFICMjVEFSR0VUREIjIy5kYm8udFN0cnVjdFBhcnRpdGlvbkxpbmtzDQpkZWxldGUgZnJvbSAjI1RBUkdFVHtbJiomXX1EQiMjLmRiby50U3RydWN0UGFydGl0aW9uSGllcmFyY2h5DQpUUlVOQ0FURSBUQUJMRSAjI1RBUkdFVERCIyMuZGJvLnREYXRhQXJje1smKiZdfWhpdmUNClRSVU5DQVRFIFRBQkxFICMjVEFSR0VUREIjIy5kYm8udERhdGFDaGVjaw0KDQotLUF0dGVtcHQgdG8gY2xlYXIgb3V0IHR7WyYqJl19aGUgdERhdGFNYXBTZWcgdGFibGVzDQpFWEVDIHNwX01TRm9yRWFjaFRhYmxlIDxzcGFuIHN0eWxlPVwiY29sb3I6IG5hdnk7XCI+XCc8L3tbJiomXX1zcGFuPg0KICBERUNMQVJFIEBUYWJsZU5hbWUgVmFyQ2hhcigxMDApDQogIFNldCBAVGFibGVOYW1lID0gUEFSU0VOQU1FKDxzcGFue1smKiZdfSBzdHlsZT1cImNvbG9yOiBuYXZ5O1wiPlwnXCc8L3NwYW4+PzxzcGFuIHN0eWxlPVwiY29sb3I6IG5hdnk7XCI+XCdcJzwvc3Bhbj4sMSkNCiAgSXtbJiomXX1GICBsZWZ0KEBUYWJsZU5hbWUsOCkgPSA8c3BhbiBzdHlsZT1cImNvbG9yOiBuYXZ5O1wiPlwnXCc8L3NwYW4+dERhdGFNYXA8c3BhbiBzdHtbJiomXX15bGU9XCJjb2xvcjogbmF2eTtcIj5cJ1wnPC9zcGFuPg0KICAgICAgVFJVTkNBVEUgVEFCTEUgIyNUQVJHRVREQiMjLj8gDQo8c3BhbiBzdHtbJiomXX15bGU9XCJjb2xvcjogbmF2eTtcIj5cJyAgPC9zcGFuPg0KR08NCg0KLS1BdHRlbXB0IHRvIGNsZWFyIG91IHRoZSB0RGF0YVNlZyB0YWJse1smKiZdfWVzDQpFWEVDIHNwX01TRm9yRWFjaFRhYmxlIDxzcGFuIHN0eWxlPVwiY29sb3I6IG5hdnk7XCI+XCc8L3NwYW4+DQogIERFQ0xBUkUgQFR7WyYqJl19YWJsZU5hbWUgVmFyQ2hhcigxMDApDQogIFNldCBAVGFibGVOYW1lID0gUEFSU0VOQU1FKDxzcGFuIHN0eWxlPVwiY29sb3I6IG5hdnl7WyYqJl19O1wiPlwnXCc8L3NwYW4+PzxzcGFuIHN0eWxlPVwiY29sb3I6IG5hdnk7XCI+XCdcJzwvc3Bhbj4sMSkNCiAgSUYgIGxlZnQoQFRhYmxlTmFtZSx7WyYqJl19OCkgPSA8c3BhbiBzdHlsZT1cImNvbG9yOiBuYXZ5O1wiPlwnXCc8L3NwYW4+dERhdGFTZWc8c3BhbiBzdHlsZT1cImNvbG9yOiBuYXZ5O1wiPntbJiomXX1cJ1wnPC9zcGFuPg0KICAgICAgVFJVTkNBVEUgVEFCTEUgIyNUQVJHRVREQiMjLj8gDQo8c3BhbiBzdHlsZT1cImNvbG9yOiBuYXZ5O1wiPntbJiomXX1cJyAgPC9zcGFuPg0KR08NCg0KVFJVTkNBVEUgVEFCTEUgIyNUQVJHRVREQiMjLmRiby50TG9nQWN0aXZpdHkNClRSVU5DQVRFIFRBQntbJiomXX1MRSAjI1RBUkdFVERCIyMuZGJvLnRMb2dQcm9jZXNzDQpUUlVOQ0FURSBUQUJMRSAjI1RBUkdFVERCIyMuZGJvLnREYXRhQXJjaGl2e1smKiZdfWUNCmRlbGV0ZSBmcm9tICMjVEFSR0VUREIjIy5kYm8udFNlY1VzZXINCg0KZGVsZXRlIGZyb20gIyNUQVJHRVREQiMjLmRiby50UE97WyYqJl19VlBhcnRpdGlvbg0KDQpUUlVOQ0FURSBUQUJMRSAjI1RBUkdFVERCIyMuZGJvLnRCaHZWYWxFbnRJdGVtDQpkZWxldGUgZnJvbSAjI3tbJiomXX1UQVJHRVREQiMjLmRiby50Qmh2VmFsRW50R3JvdXANCg0KVFJVTkNBVEUgVEFCTEUgIyNUQVJHRVREQiMjLmRiby50Qmh2SW1wSXRle1smKiZdfW1GaWxlDQpkZWxldGUgZnJvbSAjI1RBUkdFVERCIyMuZGJvLnRCaHZJbXBHcm91cA0KDQotLSBSRUNPUFkgRGF0YSBmcm9tIFByb2R7WyYqJl19IERCIHRvIEJ1ZGdldCBEQg0KaW5zZXJ0IGludG8gIyNUQVJHRVREQiMjLmRiby50Qmh2SW1wR3JvdXANCiAgIHNlbGVjdCAqIGZyb3tbJiomXX1tICMjU09VUkNFREIjIy5kYm8udEJodkltcEdyb3VwDQoNCmluc2VydCBpbnRvICMjVEFSR0VUREIjIy5kYm8udEJodlZhbEVudEdye1smKiZdfW91cA0KICAgc2VsZWN0ICogZnJvbSAjI1NPVVJDRURCIyMuZGJvLnRCaHZWYWxFbnRHcm91cA0KDQppbnNlcnQgaW50byAjI1RBUkd7WyYqJl19RVREQiMjLmRiby50Qmh2VmFsRW50SXRlbQ0KICAgc2VsZWN0ICogZnJvbSAjI1NPVVJDRURCIyMuZGJvLnRCaHZWYWxFbnRJdGVtDXtbJiomXX0KDQppbnNlcnQgaW50byAjI1RBUkdFVERCIyMuZGJvLnRCaHZJbXBJdGVtRmlsZQ0KICAgc2VsZWN0ICogZnJvbSAjI1NPVVJDRURCe1smKiZdfSMjLmRiby50Qmh2SW1wSXRlbUZpbGUNCg0KaW5zZXJ0IGludG8gIyNUQVJHRVREQiMjLmRiby50UE9WUGFydGl0aW9uDQogICBzZWx7WyYqJl19ZWN0ICogZnJvbSAjI1NPVVJDRURCIyMuZGJvLnRQT1ZQYXJ0aXRpb24NCg0KaW5zZXJ0IGludG8gIyMJVEFSR0VUREIjIy5kYm8udHtbJiomXX1TdHJ1Y3RQYXJ0aXRpb25IaWVyYXJjaHkNCiAgIHNlbGVjdCAqIGZyb20gIyNTT1VSQ0VEQiMjLmRiby50U3RydWN0UGFydGl0aW9ue1smKiZdfUhpZXJhcmNoeQ0KDQppbnNlcnQgaW50byAjI1RBUkdFVERCIyMuZGJvLnRTdHJ1Y3RQYXJ0aXRpb25MaW5rcw0KICAgc2VsZWN0ICp7WyYqJl19IGZyb20gIyNTT1VSQ0VEQiMjLmRiby50U3RydWN0UGFydGl0aW9uTGlua3MNCg0KaW5zZXJ0IGludG8gIyNUQVJHRVREQiMjLmRib3tbJiomXX0udFNlY1VzZXINCiAgIHNlbGVjdCAqIGZyb20gIyNTT1VSQ0VEQiMjLmRiby50U2VjVXNlcg0KDQppbnNlcnQgaW50byAjI1RBUkdFe1smKiZdfVREQiMjLmRiby50U2VjVXNlclBhcnRpdGlvbg0KICAgc2VsZWN0ICogZnJvbSAjI1NPVVJDRURCIyMuZGJvLnRTZWNVc2VyUGFydGl7WyYqJl19dGlvbg0KDQotLUF0dGVtcHQgdG8gPHNwYW4gc3R5bGU9XCJjb2xvcjogbmF2eTtcIj48Yj5pbXBvcnQ8L2I+PC9zcGFuPiBkYXRhDQpTe1smKiZdfUVUIElERU5USVRZX0lOU0VSVCAjI1RBUkdFVERCIyMuZGJvLnREYXRhTWFwIE9ODQppbnNlcnQgaW50byAjI1RBUkdFVERCIyMuZGJ7WyYqJl19by50RGF0YU1hcCAoUGFydGl0aW9uS2V5LCBEaW1OYW1lLCBTcmNLZXksIFNyY0Rlc2MsIFRhcmdLZXksIFdoZXJlQ2xhdXNlVHlwZXtbJiomXX0sIFdoZXJlQ2xhdXNlVmFsdWUsIA0KICAgICAgQ2hhbmdlU2lnbiwgU2VxdWVuY2UsIERhdGFLZXksIFZCU2NyaXB0KQ0KICAgc2Vse1smKiZdfWVjdCBQYXJ0aXRpb25LZXksIERpbU5hbWUsIFNyY0tleSwgU3JjRGVzYywgVGFyZ0tleSwgV2hlcmVDbGF1c2VUeXBlLCBXaGVyZUN7WyYqJl19bGF1c2VWYWx1ZSwgQ2hhbmdlU2lnbiwgU2VxdWVuY2UsIERhdGFLZXksIA0KICAgICAgIFZCU2NyaXB0IGZyb20gIyNTT1VSQ0VEQntbJiomXX0jIy5kYm8udERhdGFNYXANClNFVCBJREVOVElUWV9JTlNFUlQgIyNUQVJHRVREQiMjLmRiby50RGF0YU1hcCBPRkYNCg0KLS0gVXBke1smKiZdfWF0ZSBwYXJlbnQgbG9jYXRpb25zIC4uLg0KdXBkYXRlICMjVEFSR0VUREIjIy5kYm8udFBPVlBhcnRpdGlvbg0KICBzZXQgUGFydFB7WyYqJl19YXJlbnQgPSA8c3BhbiBzdHlsZT1cImNvbG9yOiBuYXZ5O1wiPlwnQnVkZ2V0VGVtcGxhdGVMb2NcJzwvc3Bhbj4NCiB3aGVyZSANCiAgIFB7WyYqJl19YXJ0TmFtZSAgPHNwYW4gc3R5bGU9XCJjb2xvcjogbmF2eTtcIj5cJ0J1ZGdldFRlbXBsYXRlTG9jXCc8L3NwYW4+IGFuZCBQYXJ0Q29udHJ7WyYqJl19b2xzVHlwZSA9IDENCg0KLS0gZW5hYmxlIHJlZmVyZW50aWFsIGludGVncml0eSBhZ2Fpbg0KRVhFQyBzcF9NU0ZvckVhY2hUYWJsZXtbJiomXX0gPHNwYW4gc3R5bGU9XCJjb2xvcjogbmF2eTtcIj5cJ0FMVEVSIFRBQkxFID8gQ0hFQ0sgQ09OU1RSQUlOVCBBTExcJzwvc3Bhbj4NCkdPXCJ7WyYqJl19O3tbJiomXX0=[[/code]]