Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted May 8, 2006

Mastering Enterprise BI: Introduction to Key Performance Indicators - Page 2

By William Pearson


Create a Clone Analysis Services Database in SQL Server Management Studio

We will begin our preparation within SQL Server Management Studio, where we will create a clone of the sample Adventure Works DW database, which can be installed by anyone implementing MSSQL Server 2005 Analysis Services.

1.  Click the Start button.

2.  Select Microsoft SQL Server 2005 within the Program group of the menu.

3.  Click SQL Server Management Studio, as shown in Illustration 1.

Illustration 1: Opening SQL Server Management Studio

The Connect to Server dialog appears.

4.  Select Analysis Services in the Server type selector.

5.  Type / select the server name / instance, if appropriate) into the Server name selector.

6.  Supply authentication information, as required in your own environment.

The Connect to Server dialog appears, with the appropriate input for our local environments, similar to that depicted in Illustration 2.

Illustration 2: The Connect to Server Dialog, with Representative Settings

7.  Click the Connect button to connect with the specified Analysis Services server.

The SQL Server Management Studio opens.

8.  Within the Object Explorer (the leftmost pane of the Studio, by default), expand the server in which we are working, if necessary, by clicking the "+" sign to its immediate left.

9.  Expand the Databases folder that appears underneath the expanded server.

10.  Right-click the Adventure Works DW database.

11.  Select Back Up... from the context menu that appears, as shown in Illustration 3.

Illustration 3: Right-click the Adventure Works DW Database – Select Back Up ...

The Backup Database – Adventure Works DW dialog appears.

12.  Replace the default name that appears in the Backup file box with the following:

ANSYS047 Adventure Works DW.abf

13.  Uncheck the Apply compression setting in the Options section.

14.  Uncheck the Encrypt backup file setting that immediately follows.

The relevant portion of the Backup Database – Adventure Works DW dialog appears, as depicted in Illustration 4.

Illustration 4: The Backup Database – Adventure Works DW Dialog Appears

15.  Click OK to begin the backup.

The Backup Database – Adventure Works DW dialog grays, as the Executing symbol in the Progress pane (lower left corner of the dialog) becomes active. The process may run several minutes depending upon the resources available on the local system. Once completed, the dialog closes, returning us to the Management Studio.

We will now restore the same backup under a different name, to create a copy of the existing sample database – a copy wherein we can make modifications without impairing the existing sample, which we may wish to use to complete tutorials included with MSSQL Server 2005 or elsewhere.

16.  Within the Object Explorer, right-click the Databases folder underneath the Adventure Works DW database.

17.  Select Restore... from the context menu that appears, as shown in Illustration 5.

Illustration 5: Right-click the Databases Folder – Select Restore ...

The Restore Database dialog appears.

18.  Click the Browse button to the right of the box (second from the top) labeled From backup file.

The Locate Database Files dialog appears.

19.  Navigate to the following backup file (where we located it in our backup steps above):

ANSYS047 Adventure Works DW.abf

20.  Click the file within the Select the file window, to place the file name into the File name box, as depicted in Illustration 6.

Illustration 6: Locate Database Files Dialog with Our Input ...

21.  Click OK to accept the file path / name, and to close the Locate Database Files dialog.

We return to the Restore Database dialog, where we see the file we have selected appear in the From backup file box.

22.  Type the following into the Restore database box immediately above the From backup file box:

ANSYS047 Adventure Works DW

23.  Leave all other settings at default.

The affected portion of the Restore Database dialog, with our input, appears as shown in Illustration 7.

Illustration 7: The Completed Restore Database Dialog

24.  Click OK to initiate the restoration.

The Restore Database dialog grays, as the Executing symbol in the Progress pane, once again, becomes active. The process runs, and, once it completes, the dialog closes, returning us to the Management Studio. Here we see the new ANSYS047 Adventure Works DW database appear in the Object Browser, as depicted in Illustration 8.

Illustration 8: The New Database Appears ...

NOTE: If the new database does not appear immediately, right-click the Databases folder and select Refresh from the context menu that appears, as shown in Illustration 9.

Illustration 9: Refreshing as Required ...

Having created the ANSYS047 Adventure Works DW database, we can now proceed with the practice portion of our session, and get some hands-on experience with KPIs in Analysis Services 2005. To do so, we will move to the Business Intelligence Development Studio, closing SQL Server Management Studio for the present.

25.  Select File -> Exit from the main menu in the SQL Server Management Studio.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM