FluentPro Help Center

What do you need help with?

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

Delete

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 a static name (i.e. "reporting" schema)

Delete

Please note: A corresponding schema with a static name should be created in your SQL Database manually 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

    Request an article Export to PDF
    Back To Top