Concept of Dynamic Cohorts in SQL Server

View previous topic View next topic Go down

Concept of Dynamic Cohorts in SQL Server

Post  ksnead on Wed May 02, 2012 5:40 pm

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!


Last edited by ksnead on Thu May 03, 2012 4:46 pm; edited 1 time in total

ksnead

Posts : 6
Join date : 2011-04-26

View user profile

Back to top Go down

Re: Concept of Dynamic Cohorts in SQL Server

Post  Mike Honey on Wed May 02, 2012 9:27 pm

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
avatar
Mike Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

View user profile http://www.mangasolutions.com

Back to top Go down

Re: Concept of Dynamic Cohorts in SQL Server

Post  ksnead on Thu May 03, 2012 4:54 pm

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?

ksnead

Posts : 6
Join date : 2011-04-26

View user profile

Back to top Go down

Re: Concept of Dynamic Cohorts in SQL Server

Post  Mike Honey on Thu May 03, 2012 7:30 pm

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
avatar
Mike Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

View user profile http://www.mangasolutions.com

Back to top Go down

Re: Concept of Dynamic Cohorts in SQL Server

Post  hang on Thu May 03, 2012 9:50 pm

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.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Concept of Dynamic Cohorts in SQL Server

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum