AS – Create a DS and DSV

Create an Analysis Services Project:

 

Create a Data Source:
-Data Source contains information that Analysis Services uses to connect to Source data base.
-Data Source Wizard can be used to create a Data Source.
-Creating Data Source contains following steps:
i) Data Provider Name  (Ex: Native OLE DB)
ii) Server Name            (Ex: BISERVER\MS2012SQL)
iii) Database Name      (Ex: AdventureWorksDW2012)
iv) Authentication Credentials

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Create a Data Source View:
-Data Source View is a logical data model that exists between physical data source and Analysis Services Dimensions and Cube.
-Data Source View Wizard can be used to select the Tables and Views (Meta Data) from the source database that contains data to build the Dimensions and Cube.
-Using “New Named Columns”, new column can be appended, parse data from column, or perform calculations using SQL Expression.

-Using “New Named Query”, New view/table can be added by grouping, filtering or join data from multiple tables using SQL SELECT statements.
Creating Data Source View involves following steps:

 

Right click on the (Data Source View) Diagram Pane and from the list menu can perform from the following :

Select the “New Named Query” to creates a View from the query as follows:

Using “Create Named Query “ wizard, View can be created. New relationship (Primarykey/FoerignKey) can also be created from this view to other related table.

Right click on the (Data Source View) Dimension\Fact table and can be select from list of menu as the following:

Select the “New Named Calculation”, to create a new column from SQL Expression. Using “Create Named Calculation” wizard, create column “DoubleUnitPrice”, and it shows as follows:

After Creating Data Source, Data Source view and Diagram Organizer the Solution Explorer looks as follows: