Creating IBM Cognos Controller Databases using Microsoft SQL Server

(1)

Guideline

Creating IBM Cognos Controller

Databases using Microsoft SQL Server

Product(s): IBM Cognos Controller 8.1 or higher

Area of Interest: Financial Management

(2)

IBM Proprietary Information

Copyright

Copyright © 2008 Cognos ULC (formerly Cognos Incorporated). Cognos ULC is an IBM Company. While every attempt has been made to ensure that the information in this document is accurate and complete, some typographical errors or technical inaccuracies may exist. Cognos does not accept

responsibility for any kind of loss resulting from the use of information contained in this document. This document shows the publication date. The information contained in this document is subject to change without notice.

Any improvements or changes to the information contained in this document will be documented in subsequent editions. This document contains

proprietary information of Cognos. All rights are reserved. No part of this document may be copied, photocopied, reproduced, stored in a retrieval system, transmitted in any form or by any means, or translated into another language without the prior written consent of Cognos. Cognos and the Cognos logo are trademarks of Cognos ULC (formerly Cognos Incorporated) in the United States and/or other countries. IBM and the IBM logo are

trademarks of International Business Machines Corporation in the United States, or other countries, or both. All other names are trademarks or registered trademarks of their respective companies. Information about Cognos products can be found at www.cognos.com

This document is maintained by the Best Practices, Product and Technology team. You can send comments, suggestions, and additions to

[email protected].

(3)

Contents

1 INTRODUCTION ... 4

1.1 PURPOSE... 4

1.2 APPLICABILITY... 4

1.3 EXCLUSIONS AND EXCEPTIONS... 4

2 SETTING UP MICROSOFT SQL SERVER DATABASE... 4

2.1 STEPS... 4

(4)

1 Introduction

1.1 Purpose

This document will provide information on setting up a Microsoft SQL Server Database to work IBM Cognos Controller.

1.2 Applicability

IBM Cognos Controller 8.1 or higher.

1.3 Exclusions and Exceptions

There are no known exceptions and exclusions at the time this document was created.

2 Setting up Microsoft SQL Server Database

This is a step by step guide to creating IBM Cognos Controller databases on Microsoft SQL Server 2000 (MSSQL).

2.1 Steps

1. Log in to a machine with Enterprise Manager and a SA connection to the MSSQL database server. Start Enterprise Manager and connect to the MSSQL server.

2. Either click the new database button (circled on the toolbar) or right-click the database windows and choose “New Database…”.

(5)

3. Name the database. If the default database location is to be used click OK and continue from step 5.

4. The location of the database can easily be changed by changing the location parameter in both the “Data Files” tab (shown below) and the “Transaction Log” tab. NOTE: The directory used must exist. To use a new directory create it using Explorer.

Click OK to create the database.

(6)

5. Open the Security/Logins page. If you want to use an existing login it has to be of the type

“Standard”. Double-click the desired account and continue from step 7. To create a new login click the new login button on the toolbar (circled).

(7)

6. Give the new login a name (do not use spaces in the names). Select “SQL Server Authentication” and give the login a password.

(8)

7. Go to the “Database Access” tab. Check the database you created (e.g. the

GreatOutdoorCompany as shown) and check “db_owner” for the database in the lower half of the window. Click OK.

NOTE: If the login was new you will be prompted to verify your password.

(9)

8. On the application server (or the PC with a local installation) start the IBM Cognos Controller Configuration utility (found on the Cognos Controller menu in Start/programs).

Choose the database tab (shown) and check “Use SelectDB"

9. Press the save button ( ) and if the question below appears click yes.

(10)

10. Click the “New” button to create a new database connection.

(11)

11. Fill in the screen shown similarly to the one below.

NOTE: The connection name should not contain spaces. If the fields for “Database Server”,

“User ID” and “Password” have been filled in correctly it should be possible to select the database from the dropdown menu.

Click Save ( ) to accept the configuration.

12. The new database should now be listed. Select it and click the run button.

(12)

13. Click OK when the informational window below is shown.

14. Select the appropriate language settings (typically those shown) and click “Create Db”.

NOTE: If the “Data” field is empty press the browse button and locate the UDL file with the connection name in the data directory (the default location is C:\Program

Files\Cognos\Cognos Controller\Data\ ).

(13)

15. Confirm the database creation by clicking “yes”.

16. When the database has been created the window should look like it does below. Now click

“Run Steps” to upgrade the database to the latest version.

(14)

17. When all processes are completed click close to exit this window.

18. Now click the check button ( ) to verify the database is set up correctly. The window below should appear.

NOTE: The database version can vary depending on the IBM CognosController version currently installed.

19. The database is now ready to use.

NOTE: If the client is on a separate machine the “SelectDB” function needs to be selected on each machine that should be able to use more than one database (see steps 8-9 to see how to enable selectdb).