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