Saturday, May 3, 2014

6. Hierarchy using dimproduct, category and subcategory

Let's create the cube using table dimproduct and its category. This is the also example of snow-flake schema.
1. create datasource view using below tables
As we can see category related data reside in tables upto 3-hierarchy level, so we can say that it can be a example of snowflake schema.
2. Now launch the Dimension Wizard from solution-explorer .
 step to follow in dimension wizard from figure-2.
 - 2.2 chose main table dimproduct
 - 2.3 related table automatically get check and shown
 - 2.4 check the attribute if unchecked "Product Subcategory key" , "Product Category key","Color"
 - 2.5 Finish the wizard.

3. Now you can see dimension detail page where "Attributes","Hierarchy","Data Source View" pane are visible.
 figure -3
 Step to follow:
3.1 drag and drop the "English Product Name" attribute "Data Source View" pane  to "Attributes" pane.
3.2 Now we need to create hierarchy so that we can drill down the data.
   -  drag and drop attribute from "Attributes" pane to "Hierarchy" pane.
   - Order should be High to low hierarchy "Product Category key", "Product Subcategory key",
"English Product Name"
   - Move to "Attribute relationship" tab and recreate the relationship
   - pick "Product Subcategory key" attribute and drop onto "Product Category key"
   - pick "English Product Name" attribute and drop onto "Product Subcategory key"
   - pick "Product key" attribute and drop onto  "English Product Name".

3.3 change the "name column" properties of attribute
        1.Product Category Key > properties > namecolumn >to> EnglishProductCategoryName
        1.Product Subcategory Key > properties > namecolumn >to>

4. Now process the dimension and Move to "Browse" tab. where we can drill down /up the dimension only.
    Have a look on below image.

5. Launch the cube wizard.
  figure -5

- Add fact "factInternetSales"
- Choose the columns, which are need to aggreagated. .
- Finish the cube wizard.

6. Process the project and browse the cube.

No comments:

Post a Comment

web stats