Configure AdventureWorks for Business Intelligence solutions

Summary: Configure the AdventureWorks sample data for use with Excel, Excel Services, and PerformancePoint Services business intelligence scenarios.

The AdventureWorks sample data set provides a sample database, data warehouse, and OLAP cube. The subsequent articles in this section make use of this sample data to demonstrate Business Intelligence capabilities in Excel 2013, Excel Services, and PerformancePoint Services. This article describes how to install and configure the AdventureWorks sample data set and configure a Business Intelligence Center on your SharePoint Server 2013 farm.

Because this file was downloaded from the Internet, it may be blocked by Windows. Right-click the file, and then click Properties. Click the Unblock button if it is present, and then click OK. (If the Unblock button is not present, then the file is not blocked.)

Because this file was downloaded from the Internet, it may be blocked by Windows. Right-click the file, and then click Properties. Click the Unblock button if it is present, and then click OK. (If the Unblock button is not present, then the file is not blocked.)

The following requirements must be met before you can deploy the sample OLAP cube. Procedures are provided to accomplish each of these tasks if they have not already been completed in your environment.

The AdventureWorksDW2012 database must be deployed on the SQL Server database engine as covered in the section above.

The SQL Server Data Tools must be installed as part of your SQL Server and Analysis Services deployment.

Note:

SQL Server Data Tools was known as Business Intelligence Developer Studio (BIDS) in previous versions of SQL Server.

The account running the Analysis Services service must have a login on the SQL Server database engine.

If you have not deployed the AdventureWorksDW2012 database, do so now using the procedures under Business Connectivity Service before proceeding with the procedures in this section.

If you have not deployed the SQL Server Data Tools, use the following procedure to deploy them.

Note:

You can determine if the SQL Server Data Tools are installed by clicking Start, All Programs, and then Microsoft SQL Server 2012 on the computer running SQL Server. If the SQL Server Data Tools are installed, it will appear in the menu under Microsoft SQL Server 2012.

To install the SQL Server Data Tools

On the SQL Server 2012 DVD, run setup.exe.

In the SQL Server Installation Center, on the left pane, click Installation.

In the right pane, click New SQL Server stand-alone installation or add features to an existing installation.

On the Setup Support Rules page, click OK.

On the Product Updates page click Next.

On the Setup Support Rules page, click Next.

On the Installation Type page, select the Add features to an existing instance of SQL Server 2012 option, and select the instance where you want to install the SQL Server Data Tools.

In the right pane, find the instance of Analysis Services that you will be using, and note the account listed in the Log On As column. This is the account for which you must add a logon in SQL Server.

If you do not already have a SQL Server login for the account running Analysis Services, use the following procedure to create one.

To add a login for the Analysis Services service account

Open SQL Server Management Studio.

Connect to the database engine.

Expand Security.

Right-click Logins and click New Login.

In the Login name text box, type the name of the account running the Analysis Services service.

Click OK.

Note:

This login does not require any Server Roles other than the default role of Public. No User Mapping is necessary.

Once you have configured the login for the Analysis Services service account, the next step is to download and deploy the AdventureWorks OLAP cube. Use the following procedure to download and deploy the cube.

Because this file was downloaded from the Internet, it may be blocked by Windows. Right-click the file, and then click Properties. Click the Unblock button if it is present, and then click OK. (If the Unblock button is not present, then the file is not blocked.)

Unzip the file to a location on the computer running Analysis Services.

In the Enterprise folder, double-click AdventureWorksDW2012Multidimensional-EE.sln.

Once the databases and the cube have been deployed, you must grant your users access to them. The following access is required:

Users who will be creating reports or dashboards in the subsequent articles in this section must have db_datareader access to the AdventureWorks databases and Read access to the AdventureWorks cube.

If you are using the unattended service account with Excel Services or PerformancePoint Services, that account must have db_datareader access to the AdventureWorks databases and Read access to the AdventureWorks cube.

If you are using Secure Store to refresh data in Excel Services or PerformancePoint Services, the target application credentials must have db_datareader access to the AdventureWorks databases and Read access to the AdventureWorks cube.

We recommend that you use an Active Directory group containing the users to whom you want to grant access.

Use the following procedure to grant access to the AdventureWorks databases. If you choose to grant access to each user individually instead of using an Active Directory group, you must create a separate login for each user.

To grant access to the AdventureWorks databases

In SQL Server Management Studio, connect to the database engine.

Expand Security.

Right-click Logins, and then click New Login.

Click Search.

If you are using an Active Directory group, click Object Types, select the Groups check box, and then click OK.

On the Select User or Group dialog box, type the name of the Active Directory group or user to whom you want to grant database access, and then click OK.

Under Select a page, click User Mapping.

Select the Map check box for AdventureWorks2012, and then select the db_datareader database role membership check box.

Select the Map check box for AdventureWorksDW2012, and then select the db_datareader database role membership check box.

Click OK.

Use the following procedure to grant access to the AdventureWorks OLAP cube.

To grant access to the AdventureWorks OLAP cube

In SQL Server Management Studio, connect to Analysis Services.

Expand Databases, and then expand AdventureWorksDW2012Multidimensional-EE.

Note:

If the AdventureWorksDW2012Multidimensional-EE database is not present, then right-click Databases and click Refresh.

Right-click Roles and then click New Role.

In the Role name text box, type a name for the role.

In the Select a page pane, click Membership.

Click Add.

Type the name of the users or Active Directory group to whom you want to grant cube access.

Note:

If you will be using Secure Store or an unattended service account to access the cube, include those credentials here.

On the Select Users or Groups dialog box, click OK.

In the Select a page pane, click Cubes.

In the right pane, in the Access column, click select Read from the dropdown list for Adventure Works and Mined Customers.

In the right pane, in the Local Cube/Drillthrough Access column, click select Drillthrough from the dropdown list for Adventure Works and Mined Customers.

The subsequent articles in this section rely on a Business Intelligence Center site being present. If you have an existing Business Intelligence Center, you can use it. However, we recommend creating a new Business Intelligence Center that is not part of your production environment.

Use the following procedure to create a Business Intelligence Center.

To create a Business Intelligence Center

On the SharePoint Central Administration website, under Application Management, click Create site collections.

On the Create Site Collection page:

Type a title in the Title text box.

Type the URL that you want to use in the URL text box.

Under Select a template, choose the Enterprise tab, and then select Business Intelligence Center.

In the Primary Site Collection Administrator section, type a name for the primary site collection administrator in the User name text box.

Optionally, type a name for the secondary site collection administrator.