Friday, April 25, 2014

SSAS CUBES HIERARCHY examples

Getting Start with SSAS sqlserver analysis services
examples of start schema
1. Create hierarchy on the basis of regular dimension on self referencing table
    Complete with DIMEMPLOYEE table
   
2. Create cube with above dimension hierarchy
    using table "DIMEMPLOYEE" , "DIMTIME" , "FactSalesQuota"

3. Create hierarchy on the basis of user-define dimension on self referencing table
    Complete with "DIMEMPLOYEE" table    
   
4. Create cube with user-define dimension hierarchy   
    using table "DIMEMPLOYEE" , "DIMTIME" , "FactSalesQuota"
   
5. Hierarchy using dimsalesterritory table
    using table dimsalesterritory / dimcurrency / factresellersales

example of snowflake schema   
6. Hierarchy using dimproduct, category and subcategory
    using table dimproduct, dimeproductcategory, dimproductsubcategory
   
7. Hierarchy using NAMED QUERY in product table.

Note:-
In HIERARCHIES pane of dimension:
    The of of hierarchy column should in high to low level like below example
    Lets a product manufacturer company, manufacturer the product and
    1. level 1 - have two type of products (a) bikes and (b) accessories
    2. level 2 - Bike can be of different types (mountain bike , dirt bike, regular bike) and
    3. level 3 is bike names

    so correct order will be products > product type > product name (product > category > subcategory )
   
In ATTRIBUTE RELATIONSHIP tab the hierarchy should be set lower to higher (just do reverse of above).

No comments:

Post a Comment

web stats