Creating Hierarchy with parent-child

• philipp.landmann

  Valued Member

  Points: 62

  More actions

  June 26, 2017 at 2:00 am

  #148052

  Dear Experts,

  I´m trying to create a hierarchy in SQL Server Analysis Services.
  It´s getting complicated when I try to combine one table with a parent-child-hierarchy with a second table.

  Does any one know how this works?
  This is my simple example.
  In table DIM_PRODUCT_GROUP I have a parent-child-hierarchie and I want to combine it with the second table DIM_BUSINESS_UNIT in one hierarchy.

  CREATE TABLE dbo.DIM_PRODUCT_GROUP (
      ID int NOT NULL PRIMARY KEY,
      PARENT_ID int,
      DESCRIPTION nvarchar(255) NOT NULL,
      BUSINESS_UNIT_ID int NOT NULL
  )

  ALTER TABLE dbo.DIM_PRODUCT_GROUP ADD CONSTRAINT FK_PGR_PARENT KEY(PARENT_ID) REFERENCES dbo.DIM_PRODUCT_GROUP (ID);
  ALTER TABLE dbo.DIM_PRODUCT_GROUP ADD CONSTRAINT FK_BUSINESS_UNIT KEY(BUSINESS_UNIT_ID) REFERENCES dbo.DIM_BUSINESS_UNIT (ID);

  CREATE TABLE dbo.DIM_BUSINESS_UNIT (
      ID int NOT NULL PRIMARY KEY,
      DESCRIPTION int NOT NULL
  );
  
  Thanks for your help!
  Philipp

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  June 26, 2017 at 5:55 am

  #1948137

  What if you create a view and just use the view as your source?
  
CREATE VIEW dbo.vDIM_PRODUCT_GROUP
AS
  SELECT ID,
   PARENT_ID,
   DESCRIPTION,
   BUSINESS_UNIT_ID
   DESCRIPTION AS BUSINESS_UNIT_DESCRIPTION
  FROM dbo.DIM_PRODUCT_GROUP PG
  JOIN dbo.DIM_BUSINESS_UNIT BU ON PG.BUSINESS_UNIT_ID = BU.ID


  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2