Cube Storage: Planning Partitions (Business Intelligence Development Studio Perspective) - Page 2
November 18, 2009
Preparation: Create a Sample Basic UDM within Which to Perform the Steps of Our Practice Session
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 2008 database, which can be installed by anyone installing 2008 Analysis Services.
1. Click the Start button.
2. Select Microsoft SQL Server 2008 within the Program group of the menu.
3. Click SQL Server Management Studio, as shown in Illustration 1.
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.
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 2008 database.
11. Select Back Up... from the context menu that appears, as shown in Illustration 3.
The Backup Database Adventure Works DW 2008 dialog appears.
12. Replace the default name that appears in the Backup file box with the following:
13. Uncheck the Apply compression setting in the Options section.
14. Uncheck the Encrypt backup file setting that immediately follows.
The Backup Database Adventure Works DW 2008 dialog appears, as depicted in Illustration 4.
15. Click OK to begin the backup.
The Backup Database Adventure Works DW 2008 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 PC. Once completed, the dialog closes, returning us to the Management Studio.
We will next restore the same backup under the new name we have given it, 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 2008 or elsewhere.
16. Within the Object Explorer, right-click the Databases folder underneath the Analysis Server name.
17. Select Restore... from the context menu that appears, as shown in Illustration 5.
The Restore Database dialog appears.
18. Click the Browse button to the right of the box (first from the top, in the Restore Source section) labeled Backup file.
The Locate Database Files dialog appears.
19. Navigate to the following backup file (where we located it in our backup steps above):
20. Click the file within the Select the file window, to place the file name into the File name box, at the bottom of the dialog, as depicted in Illustration 6.
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 Backup file box.
22. Type the following into the Restore database box immediately above the Storage location box in the Restore Target section:
The Restore Database dialog, with our input, appears as shown in Illustration 7.
23. 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 completed, the dialog closes, returning us to the Management Studio.
24. Within the Object Explorer, right-click the Databases folder underneath the Analysis Server name, once again.
25. Select Refresh... from the context menu that appears, as depicted in Illustration 8.
We see the new ANSYS086_AS database appear in the Object Browser, as shown in Illustration 9.
26. Exit the SQL Server Management Studio when desired.
Having created the new ANSYS086_AS database, we can now proceed with the creation of a new Analysis Server project within Business Intelligence Development Studio, where we can get some hands-on exposure to partition resource assignment and settings options.