How yo choose the right intermediate measure group in many-many relationship when you have multiple options

0
votes

I have a fact table called "FactActivity" and a few dimension tables like users, clients, actions, date and tenants. I create measure groups corresponding to each of them as follows

  1. FactActivity => Sum of ActivityCount colums
  2. DimUser => Count of rows
  3. DimTenant => Count of rows
  4. DimDate => Count of rows and distinct count of weekofyear column

Each user can do multiple actions using multiple clients. A tenant is logical grouping of users. So a tenant contain multiple users but a user can't belong to more than 1 tenant. All the dimension tables and fact tables are connected to DimDate via regular relationship.

The cube structure is as follows.

enter image description here

Now I want to defined the dimension relationships to each of the measure group. Some of them are Many-Many relationsip (to enable distinct count calculation). The designer is showing me multiple options to choose from for many of the intersections. I'm confused as to which one to select as intermediate measure group. Should I always pick the measure group whose total # rows is the least ex: DimDate? Or what is the right logic to determine the intermediate measure group.

This is what I got. IS this right? If no, what is wrong?

enter image description here

For more information to hep choose the right answer.

  • FactActivity = 1 billion rows
  • DimUser = 35 million rows
  • DimTenant = 1 million rows
  • DimDate = 1000 rows
1
ssasmdxolap-cube
Metrics has a sum measure. Dates, users, tenants each have count of rows measure – user330612
Could you possibly change your first image so that headers are completely readable and not cut off, and the arrows do not intersect? It is a bit hard to decipher details. Furthermore, on StackOverflow, you should edit your original question instead of adding comments. – FrankPl
gotcha. my bad. edited the pics and also add the measures info in the Q itself. – user330612

1 Answers

1
votes

The correct way to choose the intermediate measure group depends on how you want to evaluate your measures with respect to the dimension related:

Let's start with Activity measure group to Tenant dimension: The question is: How should Analysis Services determine the activity count (or any other measure in the Activity measure group) of a tenant? The only reasonable way to determine this would be to go from the activity fact table through the user table to the tenant table. And actually, the last relationship is not a many-to-many relationship, but a many-to one relationship. I. e. you could optimize away the tenant dimension by integrating it into the user dimension. However, using a many-to-many relationship will work as well, just be a little less efficient. You might also consider using a reference relationship from user to tenant instead of a many-to-many relationship. And there may be other considerations why you may have chosen to have them two separate dimensions, thus I do not discuss this any further.

Now let us continue with the next one: Tenant measure group to User dimension: The way you have configured it (using the date measure group) means that for each date that a tenant and a user have in common, the tenant count of a user adds one to the count. This is probably not what you want. I would assume you want to relate tenant measures to user dimension by the user measure group. However, I am not sure what the purpose of the DateKey in the user and tenant dimension tables is at all. Thus, your relationship may be correct.

Let's continue with the relationships from the Date measure group to the Tenant and User dimensions. I would assume there should be no relationship at all, as the week of the year and the date count do not depend on tenants or users. Please note that it is absolutely ok to have no relationship between some measure groups and some dimensions. If you look at the Microsoft sample cube "Adventure Works", it has more gray cells (i. e. measure group and dimension being unrelated) in the Dimension Usage than white ones (i. e. there is some kind of relationship between measure group and dimension, of whichever type). In the default setting of IgnoreUnrelatedDimensions = true of a measure group, this means that the measure value will be the same for all members of the dimension. This should be the case for date count and week of year. However, again, as I do not know the purpose of the DateKey in the user and tenant dimension tables, I am not sure if this assumption is correct for your data.

And after these examples, I would hope you can continue with the rest of relationships yourself.