Multiple data sources and ambiguous table relationships

[emcov]

Hi ! I appreciate any and all advice or suggestions for best practice here. 

I have a model that retrieves three tables from an SQL server:

RESOURCES[resourceid] > 1:* > EVENTS[eventid, userid, resourceid] < *:1 < USERS[userid] 
there is also a data table connected to the events table

The dataset is inclusive of all possible users, all possible resources (websites), and an events fact table (where an event is defined by when a user uses a resource and includes info about the event like pagecount, duration, windowtitles, etc). 

Not included in this dataset is information about AUTHORIZATIONS for users, resources, and events., i.e., 

- some events are "unauthorized" events 

- some resources require authorizations (but not all)

- for those resources that require authorization, there is a user list i'm trying to match against

- a userlist can be used to describe authorized users for more than one resource (rare)

- many resources have no userlist

I am trying to add that in using data sourced from two other tables stored separately. 

The AUTHORIZATION source tables look like this without manipulation:

SUBSCRIPTIONRESOURCES[UserListID, SusbcriptionResourceName, # of seats]

SUBSCRIPTIONUSERS[username, SubscriptionResourceName, UserListID]

In my mind, I need to add in data to every table as follows:

USERS - somehow needs to know which resources these users are authorized for - if there is not a userlistid for the resource, do I list it out? 

RESOURCES - needs to have a column indicating the userlistID if there is one  - do i need to add in a userlistid that likns to a list of all users? 

EVENTS - needs to know whether the event is authorized or unauthorized based on userlistid or the resource+user data combined? 

Or do i add a few more dimension tables specific to these authorization tables?

I did a bunch of merging/joining and duplicating of tables and queries to make this all work and landed here:

report-builders can use SubscriptionResourceName to filter to AUTHORIZED events AND show users who are AUTHORIZED but have no events 

OR BY

ResourceName to ignore anything having to do with authorizedUsers and whether events were authorized or not. 

BUT it is very clunky and doesn't feel right. specifically because slicing by RESOURCENAME does not narrow the SubscriptionResourceName options, and slicing by SubscriptionResourceName does not allow me to retrieve information from regular user table for those users who are not authorizedusers. 

Can anyone offer advice or insight on the best way to make this model work without ambiguous relationships or duplicating tables over and over? 

I was cleaning it up and got to this but its still not right

How would you handle this setup? Happy to just totally blow it up and start over. 

THANK YOU! 

[emcov]

Thank you so much! This is so helpful. 

I built this all with some tweaks, but the concatenated lists in Resources[UserWithAccess] and User[AccessibleResources] are not helpful in that they are not connected tables by default and I can't find a nice way to connect them.

I'm sorry for floundering, but all of the youtube videos use calculate and countrows but they're always working with numbers in their measures (sales, salaries, hours, etc), whereas i'm trying to visualize data that isn't numerical. 

I specifically need to be able to create a table in the report that shows a list of SubscriptionUsers, filtered by Resource Name, that ALSO shows users who HAVE ACCESS to the resource but don't have any EVENTS. Right now, I'm not able to show those users that have access but don't have any EVENTS (FACT table). 

Am I missing something obvious? Do I just need to add all of the user info into the Subscription Users table? 

THANKS AGAIN ! So much. 

[v-yajiewan-msft]

Hi @emcov, 

Try as following:

• Create calculated column in Users table(Resources do not require authorizations and are accessible to all users):

AccessibleResources = 
CONCATENATEX(
    FILTER(
        ADDCOLUMNS(
            Resources,
            "IsAuthorized", 
            IF(
                Resources[RequiresAuthorization] = TRUE(),
                NOT(ISBLANK(
                    LOOKUPVALUE(
                        'SubscriptionUsers'[UserListID],
                        'SubscriptionUsers'[UserName], Users[UserName],
                        'SubscriptionUsers'[SubscriptionResourceName], Resources[ResourceName]
                    )
                )),
                TRUE()
            )
        ),
        [IsAuthorized] = TRUE()
    ),
    Resources[ResourceName],
    ", "
)

• Create columns in Resources table:

UserListID = 
LOOKUPVALUE(
    'SubscriptionResources'[UserListID], 
    'SubscriptionResources'[SubscriptionResourceName], Resources[ResourceName]
)
​

RequiresAuthorization = 
IF(
    ISBLANK(Resources[UserListID]),
    FALSE(),  
    TRUE()    
)

• Create column in Event table:

IsAuthorizedEvent = 
IF(
    NOT(ISBLANK(LOOKUPVALUE(
        SUBSCRIPTIONUSERS[UserListID], 
        SUBSCRIPTIONUSERS[UserName], RELATED(USERS[UserName]), 
        SUBSCRIPTIONUSERS[SubscriptionResourceName], RELATED(RESOURCES[ResourceName])))),
    "Authorized",
    "Unauthorized"
)​

Relationship:

Best regards,

Joyce

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.