SSAS: About the design of parent-child dimensions

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.