CODEWITHSUNDEEP
ssasparent-childmdxolapdimensions
mclark1129
mclark1129

Reputation: 7592

Parent Child Relationships Between Measure Groups in SSAS

I am having a tough time figuring out how I am supposed to implement parent-child relationships between my measure groups in SSAS. Essentially I am just trying recreate a standard SQL join so that I can lookup measures in my Parent group while using keys in my Child group.

For example, let's say I have the following dimensions:

And I have the following measure groups

I have not created any specific dimensions for any of my fact tables.

In the end I just want to run a query like this to list out measures in my Child table joined to measures in my Parent table:

SELECT
    {
        [ChildMeasure]
        , [ParentMeasure]        
    } ON COLUMNS
    , [Child].Children ON ROWS
FROM
    [MyCube]
WHERE
    [Time].[100]

When this query runs, the Child rows are correctly listed, alongside the appropriate measure values for Time ID 100. Unfortunately, ParentMeasure is all the same, and appears to be an aggregate for this value over all Parents at Time ID 100. I would expect this column to show the value from each child's associated parent at Time ID 100.

What am I doing wrong here? Do I need to create FactDimensions for each FactTable, and somehow relate those? Should I crate an association between Parent and Child in my Datasource view? Would that make it a Snowflake schema, which I think I am supposed to avoid?

As a side note, my ChildFact table actually contains ParentID as one of the measures, because it is on the relational table in the datasource (probably due to some previous denormalization effort by the DB developer). Should I remove any measures that are actually FKs in my fact table, or is that somehow required for what I am trying to do?

Upvotes: 1

Views: 1526

Answers (2)

Mike Honey
Mike Honey

Reputation: 15017

I don't think you should attempt to create a single Fact table due to the obvious granularity issue.

I think you should add a Dimension Relationship in your Cube definition, between your Child Fact and Parent Dimension, using the existing FK column.

Probably the only valid use of ParentID as a Measure would be to get a Distinct Count.

Upvotes: 2

Yogesh
Yogesh

Reputation: 3482

There is no need to create two facts here.

  1. Create a single Fact table (ParentID, ChildID, TimeID)
  2. Create 3 dimension DimParent, DimChild, DimTime
  3. FK of Fact table will refer to respective dimension table
  4. Also, check the Attribute relationship how Facts are sliced with dimension

Upvotes: 1

Related Questions