Friday, May 2, 2014

3. Create hierarchy on the basis of user-define dimension on self referencing table

Before proceeding for the user-define dimension we have to drop the Foreign-Key constraint from table "DIMEMPLOYEE".

Use below command to drop the constraint.
USE [AdventureWorksDW]

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_DimEmployee_DimEmployee]') AND parent_object_id = OBJECT_ID(N'[dbo].[DimEmployee]'))
ALTER TABLE [dbo].[DimEmployee] DROP CONSTRAINT [FK_DimEmployee_DimEmployee]

1. Create datasource. Create new one or use pre-existing datasource connection
2. Create a Data source View with the help of wizard. To launch it right click on "Data source View" create new.
    Add below tables in data source view
    "DIMEMPLOYEE" , "DIMTIME" , "FactSalesQuota"
( figure-1)

3. In below image you can see there is no self reference arrow, so we have to tell it to that which column will act as "parentkey"

4. Now create a Dimension
    Right click "Dimensions" > "New Dimension" from "solution explorer"
    While dimension create wizard on page "Select Dimension Attributes" ,
    here you can see "Parent Employee Key" attribute is unchecked here.
    - To make a hierarchical relationship you need to check "Parent Employee Key" attribute here or
    - you can add it after dimension creation wizard completion as you can see in subimage-2.2 below.
( figure-2)
5. Now you can see there is no automatic relation create between dimension because there is no FOREIGN KEY is present.

6. Now set "SET ATTRIBUTE USAGE" to "parent". Look at below image.

( figure-3)
7. Now process the dimension.
    after successful process now you can check the hierarchical data in browser tab.


No comments:

Post a Comment

web stats