<div>
You have to aggregate the profile based facts (a subquery) up to event level before joining to the event based facts.<br />
<br />
Can you share the query you used that produced the wrong results please. I can then duggest a correction.
</div>


You have to aggregate the profile based facts (a subquery) up to event level before joining to the event based facts.

Can you share the query you used that produced the wrong results please. I can then duggest a correction.

<div>
<div class="content wysiwyg-content">
I have a primary fact table of Events with a bridge to another fact table of Interest that are tied by an Event Id. Each event can have multiple Interest. I'm able to get the correct Event &amp;&nbsp;Interest counts.<br />
<br />
The same Events fact table has another bridge to another fact table of Profiles, but this is linked by a Profile Code within the Event, instead of the Event Id. I'm able to get the correct Event counts with the correct distribution but the problem is that the Interest counts are always the same, the total sum of Interests.<br />
<br />
The one thing I haven't tried is to include every Event Id in the Profile fact for every possible combination. I would imagine this could get huge and have a negative impact on performance.<br />
<br />
I've attached the design and examples of correct results and of the &nbsp;wrong Interest measure.<br />
<a href="https://filedb.experts-exchange.com/incoming/2018/02_w06/1270891/CorrectCount.PNG" target="_blank" title="Correct Example (5 KB)"><img alt="Correct Example" class="file-block lazyload" style="max-width: 328px;" data-src="https://filedb.experts-exchange.com/incoming/2018/02_w06/1270891/CorrectCount.PNG" /></a><a href="https://filedb.experts-exchange.com/incoming/2018/02_w06/1270889/ThreeFactEvents.PNG" target="_blank" title="Multiple Fact Tables (11 KB)"><img alt="Multiple Fact Tables" class="file-block lazyload" style="max-width: 460px;" data-src="https://filedb.experts-exchange.com/incoming/2018/02_w06/1270889/ThreeFactEvents.PNG" /></a><a href="https://filedb.experts-exchange.com/incoming/2018/02_w06/1270892/WrongCount.PNG" target="_blank" title="Wrong Example (4 KB)"><img alt="Wrong Example" class="file-block lazyload" style="max-width: 220px;" data-src="https://filedb.experts-exchange.com/incoming/2018/02_w06/1270892/WrongCount.PNG" /></a>
</div>
</div>


CorrectCount.PNG

WrongCount.PNG

ThreeFactEvents.PNG

I have a primary fact table of Events with a bridge to another fact table of Interest that are tied by an Event Id. Each event can have multiple Interest. I'm able to get the correct Event & Interest counts.

The same Events fact table has another bridge to another fact table of Profiles, but this is linked by a Profile Code within the Event, instead of the Event Id. I'm able to get the correct Event counts with the correct distribution but the problem is that the Interest counts are always the same, the total sum of Interests.

The one thing I haven't tried is to include every Event Id in the Profile fact for every possible combination. I would imagine this could get huge and have a negative impact on performance.

I've attached the design and examples of correct results and of the  wrong Interest measure.




Multiple fact tables with different granularity

SQL Server Analysis Services (SSAS) is an online analytical processing (OLAP) and data mining tool in Microsoft SQL Server used as a tool by organizations to analyze and make sense of information possibly spread out across multiple databases, or in disparate tables or files. Analysis Services includes a group of OLAP and data mining capabilities and comes in two flavors - Multidimensional and Tabular.

SSAS

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.