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.
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
We can see importance of hierarchy and attribute relationship in my next blog.
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.
Comments
Post a Comment