Post reply

Creating Hierarchy with parent-child

  • 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

  • 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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply