SQL Server Analysis Services - Creating Data Source Views
 
Published: 06 Oct 2008
Abstract
Now it is time to go practical in our business intelligence project as we have provided enough concepts. The first step in creating BI projects is to provide data sources to build your model out of. In this article, Nidal examines the different aspects of creating data source views using SQL Server 2008. After providing a short introduction, he outlines the procedure involved in a series of steps with the help of relevant screenshots.
by Nidal Arabi
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 20977/ 35

Introduction

There are several ways to implement data sources and you can even build your databases from text files that are coming to your system. It is important to note here that you can use the SQL Server Integration Services (SSIS) provided to simplify your life by loading data from different databases and other data sources into one single database instance of SQL Server. It is also important to note that the text interface provides a great deal of flexibility if you are dealing with heterogeneous systems to get data out. Please remember that SSIS is outside the scope of this series. In our case, we are going to deal simply with an SQL Server database as our source.

Requirements

Before we can proceed, it is imperative to have Microsoft SQL Server 2005 or 2008 installed completely. Also note that we should install one of these editions:

·         SQL Server Standard Edition or Enterprise Editionà    you will need Windows Server as OS

·         SQL Server Developer Edition    à       You can use Windows XP as operating system.

If you do not want to buy one, you can simply get a trial edition. Please do not use the express edition because it does not work.

After installing the server, you need to download the two most famous databases for SQL server: Adventure Works and Adventure Works BI.

You can go to CodePlex (Microsoft Open Source Library) and download the SQL Server databases.

Please note that you have to respect two things to follow up the exercises:

·         You have to download the case insensitive database version.

·         You have to respect your SQL Server release you have installed.

·         Download the OLTP as well as the Data Warehouse database.

You can download the SQL Server 2005 version samples here:

http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004

You can download the SQL Server 2008 version sample here:

http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=16040

After you download the databases please proceed with their installation by clicking the downloaded files.

Now we have to attach the databases installed. Proceed in starting the SQL Server Management studio from the start menu. Or, you can simply click Start à Run and type SQLWB if you are using version 2005 or SSMS if you are using SQL Server 2008.

In my article I will be using SQL Server 2008.

Steps

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

Summary

In this long article, we have created downloaded our sample databases, attached them, created an SSAS project, created a data source as well as a new data source views. In the next article will explore more the option inside the data source views and report some nice features, stay tuned and happy BI.



User Comments

Title: Mr   
Name: Christopher Seewald
Date: 2008-12-03 5:19:14 AM
Comment:
Hi

I need to be able to access the datasource and datasource views outside of SSAS, via a .NET webservice, so that i can generate an XML file as a datasource for reporting (Silverlight, Infragistics etc)

Thanks

Chris
Title: Thanks   
Name: Pawan Kumar
Date: 2008-10-17 9:05:44 AM
Comment:
I really appreciate this site becuz i think it is most updated site according to newer technolozy.
Title: greate article   
Name: siddharth
Date: 2008-10-13 8:20:40 AM
Comment:
but how to fire dynamic inline query through .NET page on qube.






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2024 ASPAlliance.com  |  Page Processed at 2024-03-19 4:14:52 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search