SSAS: Dimension design based on snowflake model

Product and product subcategories based on the dimensions of the snowflake model, as an example. There is a foreign key relationship between the DimProduct table and the DimProductSubcategory table, and there is a foreign key relationship between the DimProductSubcategory table and the Dimproductcategory table.

Dimension tables and data for testing-

Use biwork_ssis go if object_id (' dimproduct ') are not NULL DROP TABLE dimproduct go if object_id (' Dimproductsub  Category ' is not a null drop table dimproductsubcategory go to IF object_id (' dimproductcategory ') is a NOT NULL drop table Dimproductcategory go SELECT productkey, Productalternatekey, Productsubcategorykey, ENGLISHPR
       Oductname, StandardCost, Color, Safetystocklevel, ListPrice, Class, Size, StartDate, EndDate, [Status], Productalternatekey + ' (' + CONVERT (Char (), StartDate, 120) + ') ' As ProductID into DimProduct from AdventureWorksDW2012.dbo.DimProduct SELECT Productsubcategorykey, productsub Categoryalternatekey, Englishproductsubcategoryname, Productcategorykey into DimProductSubcategory from ADV EntureWorksDW2012.dbo.DimProductSubcategory SELECT Productcategorykey, Productcategoryalternatekey, Eng
LishproductcategorynameInto Dimproductcategory from AdventureWorksDW2012.dbo.DimProductCategory ALTER TABLE dimproductcategory ADD Constrai NT pk_categorykey PRIMARY KEY CLUSTERED (productcategorykey) go ALTER TABLE dimproductsubcategory ADD CONSTRAINT Ubcategorykey PRIMARY KEY CLUSTERED (productsubcategorykey) go ALTER TABLE dimproduct ADD CONSTRAINT pk_product PRIMA RY key CLUSTERED (ProductKey) go ALTER TABLE dimproduct ADD CONSTRAINT fk_subcategorykey FOREIGN Key (Productsubcategor Ykey) REFERENCES dimproductsubcategory (productsubcategorykey) ALTER TABLE dimproductsubcategory ADD CONSTRAINT Fk_c Ategorykey FOREIGN KEY (productcategorykey) REFERENCES dimproductcategory (Productcategorykey) SELECT * from Dimprodu CT SELECT * from dimproductsubcategory SELECT * dimproductcategory