{[['
']]}
']]}
Data Source View
A Data Source View (DSV) is an abstraction of a relational data source that becomes the basis of the cubes and dimensions you create in a multidimensional project. The purpose of a DSV is to give you control over the data structures used in your project, and to work independently of the underlying data sources (for example, the ability to rename or concatenate columns without directly modifying the original data source).Sometimes, there is a requirement to change the DSV. For example, you might want to add a calculated column in the DSV with SQL commands and functions, you might want to create a relationship between two views, or you might need to write a query and create a DSV table based on that query.All of these modifications can be done in the DSV, and in this section, we will go through some of them.
Calculated Column in DSV
- Go to the Adventure Works DW2012 DSV designer.
- In the Customer dimension, you can see that there are three name columns as First name, Middle name, and Last name, but there is no full name. We want to have the full name shown as the main customer attribute of the customer dimension in the cube. So, we will create a column in DSV for that.
- Right-click on DimCustomer and select New Named Calculation.
- Set the column name as Full Name, and write the following code in the expression area: FirstName+' '+ ISNULL ( MiddleName + ' ' , '' ) + LastName
- Click on OK and you will see the new column added to the table. Right-click on DimCustomer and select Explore Data.
Using a Named Query
database (assume that we don't have access to the underlying database in order to create
views or change the structure of tables there). Named Query is a query that runs on the
database and its result will be shown as a table in DSV.
- In the Data Source View designer, right-click on the DimProduct table and under Replace Table, choose With New Named Query.
- In the Create Named Query window, add the ProductCategory and ProductSubCategory tables, and the designer will write joined statements itself.
- After that step, choose DimProduct.ProductKey ,DimProductCategory.EnglishProductCategoryName, DimProductSubcategory.EnglishProductSubcategoryName and DimProduct.Colore from the columns list.
- 4. Explore data of the DimProduct Named Query and review the result.
Post a Comment