SSAS: About the design of parent-child dimensions

Source: Internet
Author: User
Tags contains

In addition to the time dimensions that appear in previous articles, there is a special dimension-parent-child dimension in addition to the design of the snowflake dimension. A parent-child dimension is special in that it contains a reference structure based on a recursive relationship (recursive relationship), and in my article I mentioned how to design and make a SSRS report based on a parent-child hierarchy, but that report is based on the Data warehouse. Now we want to design a SSRS based on the parent-child structure, on which we can also design a report based on the SSAS database.

The following is still our test table and data, and the test table contains an Employee dimension table and a Sales fact table.

Use biwork_ssis go if object_id (' FactResellerSales ', ' U ') is not NULL DROP TABLE factresellersales go if OBJECT _id (' DimEmployee ', ' U ') is not NULL DROP TABLE dimemployee go SELECT employeekey, Parentemployeekey, Em Ployeenationalidalternatekey, case when ISNULL (MiddleName, ') = ' THEN FirstName + ' + LastName ELS E FirstName + ' + middlename + ' +lastname end as FullName, Title to DimEmployee from AdventureWorksDW201 2.dbo. DimEmployee SELECT ProductKey, Orderdatekey, EmployeeKey, Salesorderlinenumber, Salesorde Rnumber, UnitPrice, Productstandardcost, salesamount into FactResellerSales from AdventureWorksDW2012 . dbo.
    
FactResellerSales go ALTER TABLE dimemployee ADD CONSTRAINT pk_employeekey PRIMARY KEY CLUSTERED (employeekey) go ALTER TABLE dimemployee ADD CONSTRAINT fk_parentemployeekey FOREIGN KEY (parentemployeekey) REFERENCES DimEmployee (Emp
Loyeekey) Go    
ALTER TABLE factresellersales ADD CONSTRAINT pk_reseller_orderlinenumber_ordernumber PRIMARY KEY CLUSTERED (saleso Rderlinenumber,salesordernumber go ALTER TABLE factresellersales ADD CONSTRAINT fk_employeekey FOREIGN KEY (employeek EY) REFERENCES DimEmployee (employeekey) go select * to DimEmployee SELECT * from FactResellerSales

The Parentemployeekey in the employee table points to its own primary key EmployeeKey, while the primary key in FactResellerSales is made up of Salesorderlinenumber and SalesOrderNumber A composite primary key consisting of, and employeekey points to the employeekey of the DimEmployee.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.