SSAS - Named query in DSV

Named query in DSV is important in helping the developer to add only required field from the multiple tables. It is very similar to the views in database. There may be scenario where few fields required out of n number of fields from multiple tables for e.g. Only three fields are important for product sales and profitability analysis such as product name, product sub category and product category but these are spread across three tables called Product , productSubCcategory and productCategory along with so many unwanted fields (Refer the database AdventureWorksDW2008). Here we can make use of named query instead of referring these tables directly in DSV which improves the performance as well.

I would like to give steps required to create named query in DSV in this post.

Step 1: Create new DSV with FactInternetSales as main table and pull all the related tables to it and name it as InternetSales.

Fig 1

Step 2: Right click on DimProduct dimension and select the option NamedQuery from Replace table option

Fig 2

Step 3: Provide the below query in the query window of the NamedQuery

select a.ProductKey,a.EnglishProductName,
b.ProductSubcategoryKey,EnglishProductSubcategoryName ,
c.ProductCategoryKey,EnglishProductCategoryName
from dimProduct a
join DimProductSubcategory b on a.ProductSubcategoryKey = b.ProductSubcategoryKey
inner join DimProductCategory c on b.ProductCategoryKey = c.ProductCategoryKey



Fig 3

Now you can see that product table is replaced with fields from this query and by default it considered productkey as primary key which is having unique values among the fields selected.

Fig 4

We can see importance of hierarchy and attribute relationship in my next blog.