Create a Planning Application Repository with SQL Server

---

Disclaimer

Click the images in the article below to toggle between thumbnail and full-size. Enlarge all | Reduce all

---

Introduction

This article shows how to use scripts that can be run from the command line in SQL Server Management Studio to create a relational repository in a SQL Server database for an Oracle Hyperion Planning application.

When you create a new Planning application, you must first create a Data Source for the application. When you create a Data Source, if you are using SQL Server as your RDBMS you must specify the name of the database that the Data Source will use. This database is known as the repository for the application.

This article has two DDL scripts that you can use to create such a database. The first script creates a Login and the second creates a Database owned by that Login.

Create the Login

Here is a SQL script you can run from a query window in SQL Server Management Studio. Connect to the query window using a sysadmin account such as sa. The script will create a Login named ‘Vision_user’ with a password of ‘Vision_pwd’. I don't suggest or recommend using this Login name and password on your system. They are only shown for instructional purposes. Modify the Login name and password as needed for your system. After creating the Login, the script then gives the Login a ‘sysadmin’ role so it can be made the owner of the repository.

CREATE LOGIN [Vision_user] WITH PASSWORD='Vision_pwd', 
    DEFAULT_DATABASE=[master], 
    DEFAULT_LANGUAGE=[us_english], 
    CHECK_EXPIRATION=OFF, 
    CHECK_POLICY=OFF
GO

EXEC sys.sp_addsrvrolemember @loginame = 'Vision_user', @rolename = 'sysadmin'
GO

ALTER LOGIN [Vision_user] ENABLE
GO
                

Create the Database

Then run the following SQL. The database has been sized to be adequate for the Planning sample application called Vision that ships with Planning 11.1.2.3.500 and later. For other Planning applications you may need to provide more space.

CREATE DATABASE [Vision] ON  PRIMARY 
( NAME = 'Vision', 
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Vision.mdf' , 
SIZE = 200MB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
 LOG ON 
( NAME = 'Vision_log', 
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Vision_log.ldf' , 
SIZE = 20MB , MAXSIZE = 200MB , FILEGROWTH = 10%)
GO

ALTER DATABASE [Vision] SET ALLOW_SNAPSHOT_ISOLATION ON 
GO

ALTER DATABASE [Vision] SET READ_COMMITTED_SNAPSHOT ON 
GO
                

Final Remarks

Using the scripts above lets you create the repository from the command line of a query window, which is generally more efficient than going through the UI, especially if you need to create multiple repositories.

-Tom Cockerline, February 2017