7. Hierarchy using NAMED QUERY in product table

Named Query in SSAS
A named query is a SQL expression represented as a table. In a named query, you can specify an SQL expression to select rows and columns returned from one or more tables in one or more data sources.
    -named queries can be used to split up a complex dimension table into smaller and simple dimension
    -named query can also be used to join multiple database tables from one or more data sources into a single data source view table.
   
    For example, suppose you want to create a hierarchy based on the product categories and subcategories. If make a look at post (6. Hierarchy using dimproduct, category and subcategory) , we can see that product categories and subcategories related data reside upto 3-level of dimension table.
   
1. create datasource.
2. create data source view using below tables. (see figure 1.2)
    fact - factinternetsales
    dim - dimproduct

 figure - 1

3. In Solution Explorer, expand the Data Source Views folder, then double-click the data source view.
4. In the Tables or Diagram pane, right-click an open area (of DimProduct) and then click replace table > with New Named Query. (see image -1.3)

   
5.    In the Create Named Query dialog box, do the following:

 figure -2

from image 2 do as follow:

    -In the Name text box, type a query name.
    -Optionally, in the Description text box, type a description for the query.
    -In the Data Source list box, select the data source against which the named query will execute.
    -Type the below query in the bottom pane, or use the graphical query building tools to create a query.
                SELECT
                  p.ProductKey,
                  p.EnglishProductName,
                  CASE
                    WHEN p.SpanishProductName = '' THEN  p.EnglishProductName
                    ELSE p.SpanishProductName
                  END AS SpanishProductName,
                  CASE
                    WHEN p.FrenchProductName = '' THEN p.EnglishProductName
                    ELSE p.FrenchProductName
                  END AS FrenchProductName,
                  p.ListPrice,
                  p.StandardCost,
                  s.EnglishProductSubcategoryName,
                  c.EnglishProductCategoryName
                FROM
                  DimProduct p
                  INNER JOIN DimProductSubcategory s
                     ON p.ProductSubcategoryKey = s.ProductSubcategoryKey
                  INNER JOIN DimProductCategory c
                     ON s.ProductCategoryKey = c.ProductCategoryKey
                WHERE
                  p.ListPrice IS NOT NULL;

5. Launch CUBE wizard
    - choose the factinternetsales table from wizard and finish the wizard as per below image.

 figure - 3

6. Now go to "solution explorer" double click on dimension "dimproduct.dim", now you can see the design page.

 figure - 4

    - drag and drop selected attribute from "data source view" pane to "attributes" pane. (see subimage 4.1)
    - open attribute relationship tab , delete all relation (see subimage -2)
    - to create hierarchy, drag and drop attribute from "attributes" pane to "hierarchy" pane (see subimage -4.3)
        order should be kept high to low.

7. Now move to attribute relationships tab. and drag and drop attribute as low to high order.
    - pick "English Product Subcategory Name" and drop onto "English Product Category Name"
    - pick "English Product Name" and drop onto "English Product Subcategory Name"
    - pick "product key " and drop onto "English Product Name"

   
8. Now process the dimension and drill down/up the dimension in "browser tab".
9. Now you can process the cube and play with it.