How to connect together multiple fact tables

[lourson]

Hi,
I have multiple fact tables, and dimension tables. My question is : what is the best way to connect the fact tables together.
At first I just connected each fact table to my dimensions. For basic calculation, that is working fine. But then I need to create conditions covering multiple fact tables and I'm starting to create a schema like hell, which doesn't seem a long term reliable idea.
Click here to see a version of my schema with the minimum details (I will be adding more facts tables):

Most of my information is related to user accounts, and my DimAccount get me all my users ID.
here the kind of metrics I'd like to have, which requires me to look at data in multiple fact tables at once:

• Number of users who have 1 Thread but no Answers and no Actions.
• Most active users (number of threads or answers) on a given period

How do you suggest I do that ? I've used the DimAccounts table to create calculated columns which get the values from each fact tables, is this a correct way to do that ? It feels bad...
Thanks a lot !

 

[marco.russo]

Creating calculated columns would generate static data, whereas you might want to analyze a specific period of data in a dynamic way.
For example, the following calculation would solve the "Number of users who have 1 Thread but no Answers and no Actions" even if it wouldn't work with your schema because I don't see a relationship between DimAccounts and ActionData.

UsersOneThread :=
COUNTROWS (
    FILTER (
        DimAccounts,
        CALCULATE ( COUNTROWS ( Threads ) ) = 1
        && CALCULATE ( COUNTROWS ( Answers ) ) = 0
        && CALCULATE ( COUNTROWS ( ActionData ) ) = 0
    )
)

The following one (Most active users) can be resolved in different ways, it depends if you want a PivotTable or a DAX query. What is your goal?

 

[lourson]

Thank you very much for your insight. I haven't thought of such an elegant way to handle this problem! It seems to work fine (once I added the missing relationship indeed). I'm cleaning right now my old fashion way

Regarding the other data, I'm constructing a dashboard with slicers and many graphs and I'd like to include a table with the TOP users list. I'm not sure which way would be best to do so?

 

[marco.russo]

There are two ways: using a PivotTable sorting and filtering data by using the "Top 10" filter in rows, or writing a DAX query as described here up to "Edit DAX" step: Linkback Tables in PowerPivot for Excel 2013 | SQLBI

 

[lourson]

Hi

Related question with the same dashboard, in order to extend the "Number of users who have 1 Thread but no Answers and no Actions" KPI : If I wanted to filter on users who have subscribed at least XX weeks before the current week, how could I do that? (XX could be 1 for now).

I imagine I should connect my DimDate to DimAccount, but is this correct to relate 2 Dimension tables? And even if, how can I do such a date filter?
Thanks again

 

[marco.russo]

Probably something like that:

UsersOneThreadSomeWeeksAgo :=
COUNTROWS (
    FILTER (
        DimAccounts,
        CALCULATE ( 
            COUNTROWS ( Threads ),
            FILTER ( 
                ALL ( 'Date'[WeekNumber] ),
                'Date'[WeekNumber] <= @CurrentWeekNumber - 2
            )
        ) = 1
        && CALCULATE ( COUNTROWS ( Answers ) ) = 0
        && CALCULATE ( COUNTROWS ( ActionData ) ) = 0
    )
)

 

[lourson]

Thanks again Marco ! I'm not familiar with references such as @CurrentWeekNumber and this one seems not to be recognized. Any idea?

 

[marco.russo]

The @CurrentWeekNumber is just a placeholder - it could be a parameter if this was a query, but if you want to create a measure in such a way, how do you identify which is the "current week" ? if it is the one selected in the PivotTable, you might replace it with MAX( 'Date'[WeekNumber] )