Oracle have published some pre-requisites for SQL Server databases to be used by EPM. You will typically see these mentioned:
ALTER DATABASE DB_XXX SET SINGLE_USER WITH ROLLBACK IMMEDIATE ;
ALTER DATABASE DB_XXX SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE DB_XXX SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE DB_XXX SET MULTI_USER ;
However that really doesn't tell the whole story! I had to go through an Oracle Support Request to find this out but there are actually much more than that.
The script below will run the full pre-requisites on all databases prefixed with "EPM_". I like to use prefixes on the databases as it keeps things neat, and it also means we can perform operations on more than 1 database at a time without worrying about screwing up anyone else's databases! We also assume the EPMA database ends with "EPMA" and the FDMEE database ends with "FDMEE". The SQL user is assumed to be called "hyperion".
DECLARE @command varchar(1000)
SELECT @command = 'IF ''?'' LIKE ''EPM_%''
BEGIN
USE ?
SELECT DB_NAME() As DatabaseName
ALTER DATABASE ? SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE ? SET READ_COMMITTED_SNAPSHOT OFF;
ALTER DATABASE ? SET ALLOW_SNAPSHOT_ISOLATION OFF;
ALTER DATABASE ? SET MULTI_USER WITH NO_WAIT;
ALTER DATABASE ? SET ANSI_NULL_DEFAULT OFF;
ALTER DATABASE ? SET ANSI_NULLS OFF;
ALTER DATABASE ? SET ANSI_PADDING OFF;
ALTER DATABASE ? SET ANSI_WARNINGS OFF;
ALTER DATABASE ? SET ARITHABORT OFF;
ALTER DATABASE ? SET CONCAT_NULL_YIELDS_NULL OFF;
ALTER DATABASE ? SET QUOTED_IDENTIFIER OFF;
ALTER DATABASE ? SET RECURSIVE_TRIGGERS OFF;
ALTER DATABASE ? COLLATE SQL_Latin1_General_CP1_CI_AS;
END'
EXEC sp_MSforeachdb @command
DECLARE @command varchar(1000)
SELECT @command = 'IF ''?'' LIKE ''%EPMA''
BEGIN
USE ?
SELECT DB_NAME() As DatabaseName
ALTER DATABASE ? SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE ? SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE ? SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE ? SET MULTI_USER WITH NO_WAIT ;
END'
EXEC sp_MSforeachdb @command
DECLARE @command varchar(1000)
SELECT @command = 'IF ''?'' LIKE ''%FDMEE''
BEGIN
USE ?
SELECT DB_NAME() As DatabaseName
ALTER DATABASE ? SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE ? SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE ? SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE ? SET MULTI_USER WITH NO_WAIT ;
END'
EXEC sp_MSforeachdb @command
ALTER LOGIN hyperion WITH DEFAULT_LANGUAGE = us_english
It is also worth mentioning that EPM requires the languages on all users and databases to be "us_english". Again, had to go through an SR to find that one out as well!
Based on feedback in the comments and some tuning guides for FDMEE and HFM the default is now for READ_COMMITTED_SNAPSHOT to be off for all databases except EPMA and FDMEE.
This does contradict the recommendations here, though, which say to turn on READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION for all databases except HFM:
https://docs.oracle.com/cd/E57185_01/EPMIS/ch03s04s02s01.html
Edit: the collation previously mentioned was case sensitive. This is against Oracle recommendations and has been amended.
