FluentPro Help Center

What do you need help with?

    Sorry, we didn't find any relevant articles for you.

    Send us your queries using the form below and we will get back to you with a solution.


    Extending Existing Database schema

    EPM Pulse On-Premises Edition app generates and fills in a brand-new database schema during each synchronization process.  It means that any consuming data has to be able to use the dynamic schema name each time the latest data is needed.  In some scenarios it is not easy to achieve this with standard tools. This article explains the way how this issue can be addressed.

    The “DataSyncConfig” table of EPM Pulse On-Premises Edition configuration database contains a column entitled “CompleteScript”.  This column may store the SQL script to be run each time the data sync process is completed. The name of the schema that was created can be referenced from the script using {SCHEMA_NAME} anchor.

    In the [datamart_config].[dbo].[DataSyncConfig] table you can find the CompleteScript column for the entry. 

    Each time the data schema is refreshed, the CompleteScript will run and perform needed changes. You can refer to the name of the newly created schema by {SCHEMA_NAME} anchor.

    Please see below several scenarios of the script usage.

    Please note: if the script is not valid for some reason, the data sync will fail, and the corresponding error message will be logged to the application log.

    Script Samples

    Creating custom view in the reporting schema

    CREATE VIEW [{SCHEMA_NAME}].[New Custom View] AS {VIEW_QUERY_HERE}

    {SCHEMA_NAME} – name anchor pointing to the newly created schema containing the latest On-Premises Edition data;
    [New Custom View] – the name of the database View to be created;

    {VIEW_QUERY_HERE} – T-SQL query code for the view.

    Setting default schema for a specific user to the reporting schema

    ALTER USER [SpecificUserName] WITH DEFAULT_SCHEMA = [{SCHEMA_NAME}];

    {SCHEMA_NAME} – name anchor pointing to the newly created schema containing the latest On-Premises Edition data;
    [SpecificUserName] – name of the specific user which is to obtain a new default schema.

    Recreating MSP_EPMProject_UserView data view in the dbo schema

    IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[MSP_EPMProject_UserView]'))
    BEGIN
    EXEC dbo.sp_executesql @statement = N'DROP VIEW [dbo].[MSP_EPMProject_UserView]'
    END
    EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[MSP_EPMProject_UserView] AS SELECT * from [{SCHEMA_NAME}].[MSP_EPMProject_UserView]'

    {SCHEMA_NAME} – name anchor pointing to the newly created schema containing the latest On-Premises Edition data.

    Recreating a full reflection of the database schema in the schema with static name (i.e. "reporting" schema)

    Please notice, a corresponding schema with a static name should be created in your SQL Database first. You may do it by running a simple SQL script, such as CREATE SCHEMA reporting;

    The script sample for CompleteScript can be found below: 

    DECLARE @static_schema_name sysname
    SET @static_schema_name = 'reporting'
    DECLARE objects_cursor CURSOR for
     SELECT o.Name
     FROM sys.objects o join sys.schemas s ON o.schema_id = s.schema_id
     WHERE s.name='{SCHEMA_NAME}' AND (o.type_desc='USER_TABLE' OR o.type_desc='VIEW')
    DECLARE @obj_name sysname
    OPEN objects_cursor
    FETCH NEXT FROM objects_cursor INTO @obj_name
    WHILE @@FETCH_STATUS = 0
    BEGIN
     DECLARE @view_name sysname
     SET @view_name = '['+@static_schema_name+'].' + '['+@obj_name+']'
     IF OBJECT_ID(@view_name, 'V') is not null
     EXEC ('DROP VIEW ' + @view_name)
     EXEC ('CREATE VIEW ' + @view_name + ' AS SELECT * FROM [{SCHEMA_NAME}].[' + @obj_name+']')
     FETCH NEXT FROM objects_cursor INTO @obj_name
    END
    CLOSE objects_cursor
    DEALLOCATE objects_cursor

    {SCHEMA_NAME} – name anchor pointing to the newly created schema containing the latest On-Premises Edition data.

    Was this article helpful?

    Table of contents

      Still can't find what you're looking for?

      Contact Support
      Back To Top