none
Modelling of snapshot tables for Tabular 2017 with M2M relationships RRS feed

  • Question

  • Hello everybody,

    I'm looking for a solution of a data modelling problem in SSAS Tabular 2017.

    My tabular project is loaded from a SQL Server Database. The tables in the database are modelled as snapshots.

    From a business point of view we're documenting possible cases that can lead to some damage for the business (power blackout, hardware failure, theft etc.) and what counteractions were taken to prevent these cases / decrease their damage. The users enter (and delete) such events and counteractions once per month. So for each month we have a snapshot of possible damage cases and counteractions.

    In the SQL database I have following tables:

    1. Date (DateId int). One record for each month.

    2. DamageCase (DateId int, DamageCaseId int, other columns). PK is (DateId, DamageCaseId).

    3. Counteraction (DateId int, CounteractionId int, other columns). PK is (DateId, CounteractionId).

    4. MapDamageCaseToCounteraction(DateId int, DamageCaseId int, CounteractionId int). It's a many-to-many table, so one damage case can have multiple counteractions. And one such action can counter several damage cases. It's also a snapshot for each month.

    In my tabular project I've defined following relationships:

    1. OneDirection DamageCase->Date over DateId

    2. BothDirections(!) DamageCase<->MapDamageCaseToCounteraction over (DateId, DamageCaseId). I had to make a single technical column from these two, because the relationships can only use one column, but it's not important here.

    3. OneDirection MapDamageCaseToCounteraction -> Counteraction over (DateId, CounteractionId).

    With this model I can build following pivots using Excel as frontend:

    1. DamageCases for a given Date

    2. Historical development of a damage case (such as its estimated probability)

    3. Overview of DamageCases with corresponding Counteractions

    4. Overview of Counteractions which are registered for one or more DamageEvents.

    What I cannot do is to make an overview of all Counteractions which are valid for a given date. Independent of the filter on the Date I always see all possible Counteractions, also the historical ones.

    The only connection between Counteraction and Date tables goes over MapDamageCaseToCounteraction. I cannot add a relationship between Counteraction and Date because of "There are ambiguous paths between MapDamageCaseToCounteraction and Date".

    I have found a hack/workaround: Make a Relationship and set it to inactive. Make a "dummy" measure in the table Counteraction (such as Count()) and use the function USERELATIONSHIP to apply the inactive relationship between Counteraction and Date.

    Is there a better solution for this problem?

    Thanks in advance.

    Monday, August 12, 2019 1:58 PM

All replies

  • Hi Andrej,

    Thanks for question.

    Per your situation, here are another two possible options for you to choose:

    1. Import table "Date" into your tabular model several times.

    2. Create several SQL Server views in the database.

    Reference

    Role-playing Dimension in SSAS Tabular Models

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, August 13, 2019 2:18 AM
  • Hello Will,

    thanks for answering. My point is I do not have any role playing dimensions in the model described above. It's always the same date which I would like to use as filter, but due to M2M relationship I can't specify an addition relationship from Counteraction to Date and can't thus filter Counteractions by date.

    (Maybe there is some other way to model M2M which solves this problem, I don't know)

    The proposal with an extra Date-Table doesn't really suit my case. It's basically the same USERELATIONSHIP "Hack", but I'd need to explain my users to use Date1 Table in frontend for some reports and use Date2 Table if they want to filter Counteraction Table. Not user friendly at all.

    Tuesday, August 13, 2019 10:32 AM
  • Hi Andrej,

    Since you don't like the two suggestions, you could only use DAX expression such as USERELATIONSHIP to create desired measures. If you could maintain a balance between advantages and disadvantages of the solution that you would choose, we have no more comment about your choice. Thanks!

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, August 14, 2019 9:59 AM