The next step is to attach the two databases to work with.
1.
In SSMS (SQL Server Management Studio) double click the Databases nodes
to expand it.
2.
Right click Databases node and choose attach
3.
In the attach box, click the add button and browse to the default
directory of SQL Server and choose the first database and click ok
4.
Click the add button again and choose the second database
5.
Click the Ok button to finalize the attachment. SQL Server takes some
time before it finishes the process.
Once the databases are attached you should have a screen
like in Figure 1. Note the two databases: AdventureWorks as well as
AdventureWorksDW.
Figure 1: The SQL Server Object Explorer with databases
attached
Now are ready to create our business intelligence project.
Close the SSMS and open the SQL Server Business Intelligence Studio from the
SQL Server group in the start menu.
6.
In the IDE (Integrated Development Environment), click the File Menu à New à
Project
7.
Make sure that the category selected is Business Intelligence Projects
in the left pane
8.
Make sure that Analysis Services Project is selected on the right hand
9.
In the name box enter the following: AdventureWorks
10. In
the name box of the solution name: SlnAdventureWorks
11. Click
ok when finished to create the project. Figure 2 shows the creation screen.
Figure 2: Project Creation Screen of the Analysis
Project called AdventureWorks
As you can see on the right in the solution explorer window,
several different folders were created.
Thru the series we are going to visit most of them and they
will be explained as we go along.
In order to build your project, you should start by creating
your data sources.
12. Right
Click Data Sources in the Solution explorer and choose New Data Source
13. Click
Next on the Wizard informational screen
14. From
Figure 3 choose New
15. After
clicking New, you are presented with connection manager screen
16. Leave
the provider as is since we are going to connect to an SQL Server database
17. In
the server name type the word LocalHost as shown in the picture
18. In
the Select or Enter Database name open the drop down box and select Adventure
works
19. If
you cannot see the database in the list, make sure that your SQL Server is
started and the name is localhost
20. After
you finish click Ok to proceed
Figure 3: Data Source Connection definition
Figure 4: The Connection manager screen
21. Click
next in the data source wizard
22. In
the impersonation information screen choose the inherit option and click next
23. In
the last screen you leave the name the data source as is
Figure 5: The Impersonation information screen
After you have finished you can now see a new branch under
the data sources called AdventureWork.ds
Now we should proceed with the creation of the data source
views...
24. Right
click the data source view and choose new data source view
25. Click
Next on the Select a Data Source screen. Please note that Adventure Works Data
source is already selected for you since it is the only data source we have
created.
26. Figure
6 shows the tables you have to select from the list of tables on the left. A
note here is that all tables belong to Human Resources department.
27. After
you finish selecting the tables click Next to proceed.
28. On
the Name Screen of the Wizard, Leave the name of the data source view as
Adventure Works.
29. Click
Finish to complete the wizard.
You can now note that you have data source view presented to
you including the tables that you have selected.
Figure 6: List of Tables to select for the data
source view
Figure 7 shows the diagram created for the new data source
view.
As you can see from the picture you can see that this is a
very small data model you have. Since you are going to deal with huge number of
tables when dealing with even a simple an OLTP database, the IDE of the visual
studio has provided you with two of navigation.
The first method is to create a new diagram (right click in
the diagram organizer and choose New Diagram and you rename it as you like) and
add simply the table you need for this view by dragging tables from the Tables
section to the diagram.
The second method is simply a navigation 4 way arrow that
only appears when your data model is beyond the screen size at the right bottom
of the scroll bar to allow for fast diagram navigation. I encourage you to test
this option by moving your tables around and out of IDE scope.
As a last the relations created here are coming directly
from the database design.
Figure 7: Diagram for Data Source View of Adventure
Works