SQL Server 2005 Analysis Services with the Project Server 2007 : Part 3

Configure SQL Server 2005 Analysis Services
After installing require component and configuring SSP account , we will see how to configure SQL Server 2005 Analysis Services to enable access to the repository. You can create the repository in either of two ways:
· Create the repository by using a SQL Server 2000 Microsoft Jet database
· Create the repository in a SQL Server 2005 database
we will take the second approach in this article ......

• Create the Repository database in SQL Server 2005
1. Connect to Database Engine in SQL Server Management Studio
2. Create New Database 'Analysis Services Repository'.
3. In the Object Explorer list, expand the Security folder. Right-click Logins and then click New Login.
4. In the Select Users or Groups page, Add this group 'SQLServer2005MSOLAPUser$$MSSQLSERVER'.
5. In the Select a page list, click User Mappings. In the Users mapped to this login list, select Analysis Services Repository.
6. In the Database role membership for: list for the repository database, select db_owner. Click OK.
7. In Microsoft SQL Server Management Studio, expand the Databases folder and right-click Analysis Services Repository. Click New Query.
8. In the Query Editor screen, enter the following text:
   CREATE TABLE [dbo].[OlapObjects] (
   [ID] [varchar] (36) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
   [ParentID] [varchar] (36) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
   [ObjectName] [nvarchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
   [ClassType] [int] NOT NULL ,
   [ObjectDefinition] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
   [LastUpdated] [datetime] NULL ,
   [Changed] [bit] NULL ,
   [Version] [int] NULL
   ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
   GO
   
   CREATE TABLE [dbo].[Server] (
   [ObjectDefinition] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
   ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
9. Execute the script. The script will create the database scheme required for the repository.
   
• Configure Analysis Services to use a SQL Server repository database
  
1. Connect to Analysis Services in SQL Server Management Studio.
2. Right click the Analysis Services name, and then choose Properties.
3. On the Analysis Services Properties page, in the Select a page section select General. Select Show Advanced (All) Properties.
4. Select DSO\RepositoryConnectionString from the Name list.
   a. Select the corresponding value for the string in the Value column, and then click the box that appears to the right of the value to display the Connection Manager page.
   b. On the Connection Manager page, in the Provider list, select Native OLE DB\SQL Native Client.
   c. In the Server Name list, select the server on which the repository database is located.
   d. In the Logon to the server field, enter the account information to log onto the server.
   e. In the Connect to database section, select a database name.
   f. Click OK.
5. Select DSO\RemoteRepositoryConnectionString from the Name list.
   a. Repeat all the steps from step 4
6. On the Analysis Server Properties page, click OK.
   
• Grant permissions to the SQL Server Analysis Service account to access the Project Server Reporting database
1. Connect to database engine in SQL Server Management Studio
2. In Management Studio, expand the Security folder, right-click Logins, and then click New Login.
3. On the General page, enter the Windows Authenticated account for the user running the SQL Server Analysis Services service.
4. In the Select a page list, click User Mapping.
5. In the Database list, select Project Server_Reporting. Select the corresponding Map check box.
6. In the Database role membership for: ProjectServer_Reporting section, select db_datareader.
7. Click OK.
• Enable the "Access data sources across domains" security setting in Internet Explorer
1. In Internet Explorer, click Tools, and then click Internet Options.
2. Click the Security tab, click the zone that you use to connect to the Office Project Server 2007, and then click Custom Level.
3. Under Access data sources across domains, select Enable.

--------------------------
<< Part 2    
Part 4 >>

---------