Dimension filters in Link Table scenario

[#1]

Hi all,

I have a situation where I have 3 fact tables joined using a LinkTable.

For ex. 1st Fact for Sales, 2nd Fact for Group Membership

Sales Fact

AccountID       DateID         SalesAmount

123                 20151201      644.8

352                 20151211      709.25

etc..

Group Membership Fact

AccountID       DateID       GroupID      (For Example VIP Level 1, VIP Level 2, etc.. etc..)

123                 20151201      2

123                 20151202      2

123                 20151203      2

123                 20151204      2

123                 20151205      5

352                 20151211      4

352                 20151212      2

352                 20151213      2

352                 20151214      5

352                 20151215      5

etc...

Campaigns Fact

AccountID       DateID       CampaignID       Participated (1 or 0)  (For Example Email Newsletter 1, Email Promotion 2 etc.. etc.. )

123                 20151201      650                    0

123                 20151201      680                    1

123                 20151201      345                    1

352                 20151211      345                    0

352                 20151212      680                    1

etc...

Is it possible to get total SalesAmount when the user selects respective GroupID? Since when I select GroupID as filter (or other filters), it discards the information coming from Sales Fact and vice versa?

What is the best way (or the options) to tackle this kind of issues?

Thanks in advance.

Jon

[petter]

How does your linktable look like? You mention three facts - how does the third fact table look like?

[#3]

Hi Petter,

Thanks. I updated the question adding the detail for the other fact.

I appreciate your help!

[jonathandienst]

Technically, only the first table is a fact table. The other two look like slowly changing dimensions.

One way to handle slowly changing dimensions is to use the extended form of interval matching. You will need to transform the dimension table to have "to" dates as well as "from" dates, and then you will interval match the transaction date, using the AccountID as an extended parameter to the interval match. The load would look something like this:

//Existing load of sales fact table

Fact:

LOAD

  AccountID,

  Date#(DateID) As DateID,

  SalesAmount

FROM ....

// Transform group membership dimension (previously loaded as we need an Order by here)

GroupMemberShipDim:

LOAD

  AccountID,

  Date#(DateID) As FromDate,

  If(AccountID = Previous(AccountID), Previous(Date#(DateID)), Date#(29991231)) As ToDate,

  GroupID

Resident GroupMemberShip

ORDER BY AccountID, Date#(DateID) DESC;

// Apply the SCD

LEFT JOIN (Fact)

IntervalMatch(DateID, AccountID)

LOAD FromDate, ToDate, AccountID

Resident GroupMemberShipDim;

//Optional cleanup (brings GroupID into Fact table directly)

LEFT JOIN (Fact)

LOAD *

Resident GroupMemberShipDim;

DROP Fields FromDate, ToDate FROM Fact;

DROP Table GroupMemberShipDim;

//End optional cleanup

Repeat the process for the other dimension.

[#5]

Thanks for your reply.

Yes the fact tables can be rearranged to be in the form of StartDate - EndDate. Even though they are Fact Tables (factless facts) I get your idea to add the GroupID to the main Fact tables as an additional column.

I have 2 Questions please:

1. Will this scenario solve the question 'Give me the total SalesAmount for those customers members in VIP Level 2 and participated in campaign Email Newsletter 1'.

2. If I select a particular Group from slicer, can I get the total sales amount for those who took Campaign B?

3. Does this solution (using Interval) solve the case where for ex, a customer I targeted in more than 1 campaign?

Thanks a lot!