4 Replies Latest reply on Feb 24, 2011 9:16 PM by beyerch2

    can FDM user maintenance be automated through backend?

    838610
      We have a FDM environment with 2 FDM apps for 3000 entities and about 700 users. You might have figure out that maintaining security across large number users since each one has to be done manually across both apps. Now we are about to start using FDM for our planning application and the security scenario gives us a nightmare.

      Has anyone automated FDM security maintenance? Is it through APIs or SQL backend?

      Any help would be really appreciated.
        • 1. Re: can FDM user maintenance be automated through backend?
          TonyScalese
          It depends. If you are using Shared Services, then you have a few layers to consider.

          I always strongly caution backend manipulation of the application as it can have greater impacts than you may realize - including rendering the app unsupported by Oracle. If abssolutely necessary, I would consider using SQL.
          • 2. Re: can FDM user maintenance be automated through backend?
            838610
            Hey Tony,

            Thank you for quick response. I should clarify that I am not a FDM resource but trying to help another team member as the issue is overwhelming her for now. As for backend stuff, you are right about potentially being out of warranty but we had to do few things for planning too and I am hoping there is something useful here. As for automation, we have automated Essbase/Planning security where users maintains it in a file and systems picks it up to update security.

            I went through FDQM documentation to read about security, API and other stuff that could help us but did not find any information that would help us resolve our issue.

            We on version 9.3.x and I believe SS for FDM comes into play for v11. Right? Do you know if this can be done through VB in FDM. If yes, where should I look? Can this be done through SQL backend? If yes, where should we start? What tables, or how we capture what SQL we need to write to assign security.

            Another thing, the security interface for FDM is not seeming stellar for now. We have to click next tens of times before we get to intended user/entity where we need to go. Hope this is getting better in new versions very soon.
            • 3. Re: can FDM user maintenance be automated through backend?
              TonyScalese
              Honestly, it sounds like you need far more help than the forums are going to be able to offer. You need someone with deep knowledge of the application to help you define and build a process. I would suggest you consider reaching out to any of the EPM partners that are well versed in FDM.
              1 person found this helpful
              • 4. Re: can FDM user maintenance be automated through backend?
                beyerch2
                I have multiple FDM applications as well as a test environment. I usually sync all of the production App to the other pieces and override certain sections for instance :

                A.) Production - This is production Actual Reporting App
                B.) Budget - This is a Budget FDM App. I want to keep all of the users, locations, etc. However, as we use multi-load, I don't care about the individual maps. Instead I map everything to * as the multi-load templates use our HFM accounts.

                I have a SQL script I wrote which copies the necessary details from A to B and then I do an update to set every location of the proper type to have a parent map for a special location I called BudgetLocation.

                I also have FDM app C.) Tax

                Tax is an EXACT copy of actual. The reason I keep it separate from the normal production app; however, is they load to production HFM server but to different scenarios. End users (no offense) are not aware enough to realize what Scenario (category) they are in so things get messy if we are not paying attention.

                I made a more generic SQL Server Stored Procedure that copies a GOOD NUMBER (BUT NOT ALL) of the FDM tables. This will copy users, groups, locations, mappings, etc. It does not copy Categories, or other System tables (periods, currencies)

                Additionally, keep in mind if you have any custom scripts, you will need to sync the /Data/Scripts folder....

                If you use this, great; however, USE THIS AT YOUR OWN RISK. I AM NOT RESPONSIBLE IF YOUR SERVER BECOMES SELF AWARE AND ATTEMPTS TO TAKE OVER THE KNOWN UNIVERSE. Seriously though, use this at your own risk........ so test it. It works for me. (NOTE this works for SQL 2005 and newer only, not 2000)

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

                -- 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

                -- Update parent locations ...
                -- This is used for my budget copy to get every location working for multi-load * mappings
                --update ##TARGETDB##.dbo.tPOVPartition
                --
                --set PartParent = 'BudgetTemplateLoc'
                --
                --where
                --
                --PartName <> 'BudgetTemplateLoc' and PartControlsType = 1
                --

                -- enable referential integrity again

                EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

                GO
                1 person found this helpful