Can you join two SSAS tables at the time of query based on end user filter selection?

Question

I'm working with a "slowly changing fact table" that keeps track of changes to a reservation by stamping the effective start and effective end dates on when a change is effective for. The latest change has an effective end date of 12/31/9999 23:59:59.999 to keep it effective forever until if/when a new change comes in. I have a second table called an "As-Of" date table that has every single date since 8/1/2008 to current with a timestamp of 23:59:59.999 that I can use to join back to the slowly changing fact where "as-of" dates are between the effective start and end dates of in the fact. The purpose of this is to be able to go back to any specific date in time and see what the reservation looked like on that date. As you can imagine, each new as-of date has exponentially more data.

I've been tasked with creating an SSAS tabular model that has every "as-of" date available in a drop down for end users to select and be able to see data for that particular date. I'm concerned about storage and performance issues of having all as-of dates joined back to the fact table to provide end users the freedom to select any as-of date they want at any given time.

If I create a view that has the fact and as-of date table joined together, is it possible to pass the as-of dates they select in the drop down in SSAS back to a dynamic where clause in the view so that I am only joining the fact and as-of date tables dynamically for the as-of dates they actually need to see? Is it possible to create some type of "live" connection that only joins the fact and as-of date tables on the fly so I don't have to blow out the underlying data for each as-of date?

It seems as though the two tables will have to be joined in a SQL view before I bring it into SSAS since it doesn't seem possible to join two tables on more than one column in SSAS.

Can someone please tell me if this is something that is even technically possible? Or if you have any other ideas on the best way to represent this data in SSAS?

msta42a msta42a · Accepted Answer · 2020-11-23T19:30:14

For some table, you can use Direct Query (live query) instead of Import.

DirectQuery Documentation

You can use a virtual relationship in your measure (where you can specifying more than one column). VirtualRelationship

example:

CALCULATE (
    <target_measure>,
    TREATAS (
        SUMMARIZE (
            <lookup_table>
            <lookup_granularity_column_1>
            <lookup_granularity_column_2>
        ),
        <target_granularity_column_1>,
        <target_granularity_column_2> 
    )
)

Can you join two SSAS tables at the time of query based on end user filter selection?

1 Answers