Concept of Dynamic Cohorts in SQL Server

We are developing a BI GUI on the microsoft platform (SSAS, SSRS, SQL Server, MDX cubes, Sharepoint, etc). Our current capabilities allow users to drill down on a particular attribute, for example, a population of 300 who has Purple Hair > age range > males or females > State > city, when we get to city we see that of the original 300 people 53 of them live in New Jersey. My business owners would now like to take this population of 53 and filter another/multiple fact(s) based on this popluation. I understand this concept is called "cohorts" or "clusters" and potentially require the buidling of a supercube, both of which are new concepts to me. I was hoping someone from this forum could poin me to something that can help me understand how to build out such a solution.

Any help is welcomed!!

Thanks!

I'm thinking you can meet this requirement using the "Classical many-to-many relationship" scenario outlined by the SQLBI guys in their outstanding "Many-to-Many Revolution" whitepaper:
http://www.sqlbi.com/articles/many2many/

For their example "Bridge Account Customer", substitute a bridge table/view that relates your two facts. You'll then be able to filter by common or even unrelated dimensions and see the resulting filtered data from the two facts in one query/pivot table.

Good luck!
Mike

A bridge is a good concept, but the bridge would be for a known set of attributes, rather than dynamic. Correct me if I am wrong?

Hi ksnead,

Typically a bridge links a detailed key from one fact to a detailed key in another fact. In that whitepaper example, they are bridging Account Key to Customer Key.

Once the bridge is defined, then any attribute of any dimension related to facts which have Account Key will filter all facts which have Customer Key.

So if you meant "dynamic" in the sense that it includes future additional attributes in the dimensions without needing design changes in the bridge, then yes it is "dynamic".

Mike

Leverage dimension conformance. You must have some sort of transaction fact (headcount) that gives you that magic number 53 by constraining the relevant dimension values in the SSAS cube. If other fact tables also share those dimensions, the constraints on the dimensions will automatically apply to the fact tables in the same cube.

The conformed fact tables may not be grained on individual level, but the dimension constraints will guarantee only 53 individuals are contributing to the facts, as others will be excluded by the set attributes in the dimensions. If any of the relevant dimension, say age range, is missing from one of those fact tables, you need try to relate it to the fact so that the full set of constraints can be applied, otherwise the facts can only be conformed at more general levels.