How to Create a Dimension

vidalaprasad

DW Fresher

Reputation: +0/-0
Offline

Offline

Posts: 3

How to Create a Dimension

« on: March 31, 2010, 09:32:56 am »

I actually want to create a dimension. The below is the small example for this.

CREATE DIMENSION products_dim
LEVEL product IS (products.prod_id)
LEVEL subcategory IS (products.prod_subcategory)
LEVEL category IS (products.prod_category)
HIERARCHY prod_rollup (
product CHILD OF
subcategory CHILD OF
category
)
ATTRIBUTE product DETERMINES
(products.prod_name, products.prod_desc,
prod_weight_class, prod_unit_of_measure,
prod_pack_size,prod_status, prod_list_price, prod_min_price)
ATTRIBUTE subcategory DETERMINES
(prod_subcategory, prod_subcat_desc)
ATTRIBUTE category DETERMINES
(prod_category, prod_cat_desc);

In this what does HIERARCHY and ATTRIBUTE do ?

Thanks for your valuable time.


	Logged

Whoever

Administrator

Reputation: +34/-0
Offline

Offline

Posts: 124

Re: How to Create a Dimension

« Reply #1 on: March 31, 2010, 08:09:54 pm »

Quote from: vidalaprasad on March 31, 2010, 09:32:56 am

In this what does HIERARCHY and ATTRIBUTE do ?

"Hierarchy" defines the relationship between the various levels defined above.
In your example, Each Category can have multiple SubCategories, and each SubCategory can have multiple Products.

This also defines how the data aggregates to higher levels.

If your Fact table has a SalesAmount -
The SalesAmount for a given Category = Sum of SalesAmount for all child SubCategories.
Similarly,
The SalesAmount for a given SubCategoryCategory = Sum of SalesAmount for all child Products.

Attributes are the various characteristics or properties of the members at the levels defined.

Eg., Name, Min_Price etc. are properties of Product.