Dynamically changing looker connection inside model file

[james_logan]

I have been trying to dynamically change the looker connection using user attributes. Here the database connections are SIEM databases namely siem_1 and siem_2. I created a user attribute named db_connection and under the "user values" tab I have assigned myself the "siem_1" db_connection value and another member of the team "siem_2". Then I went to the model file to change the connection and this is where I got stuck. Here connection variable takes only a string value in the form of a connection name. So if I use:
1) connection: {{_user_attribute["db_connection"]}} -> it says that it should be string
2) connection: "{{_user_attribute['db_connection']}}" -> it throws a model error as it is not one of the model connections i.e., siem_1 and siem_2

Some other solutions that I thought to solve this problem include creating a manifest file and adding the constants there but it doesn't seem to work either.

But in this Google document: https://cloud.google.com/looker/docs/admin-panel-users-user-attributes  they have mentioned that we should be able to use user attributes to dynamically change the database connection inside a model. I have attached a screenshot for the same:

 Kindly help me out with this problem and provide some solutions. I have been stuck on this for quite some time now. I also request Google to have example code snippets along with explanations in their documentation as it would make understanding use cases a lot easier.

[cjg4data]

Yes, user attributes can be used to dynamically change the connection based on the user. However, it cannot be used in the "connection" parameter as you've attempted. Using liquid can only be used in LookML in the places noted in this link. To accomplish what you're looking to do, you will need to add the user attribute in the database connection (i.e. Admin menu --> Connections).

[james_logan]

Hi thanks for your input! I tried what you suggested today but I'm still a bit confused about the entire process of it. So, I went to admin connections as you mentioned:

 As I mentioned earlier I created a user attribute named db_connection and under the "user values" tab I have assigned myself the "siem_1" db_connection value and another member of the team "siem_2". This user attribute ( "Db Connection") which you can see is one of the options available when I clicked the button available on the right side of "Dataset" field. In the brackets I am able to see the string or db name ("siem_1") that I assigned to myself. But shouldn't I directly choose the db name here? or the user attribute will work just fine? or should I choose some field other than "Dataset" to assign the user attribute to? Just wanted to know if I am going in the right direction. Also if you could explain the process about how it would work in real time or provide a documentation for the same, it would help a lot. Thank you!

[rfredman]

That wont work if you want to have some users use Oath and some users use a service account. Is there a solution for this? 

[cjg4data]

What you're doing is correct. You should be able to test this by running a report as yourself from an Explore built on the model that uses this connection. Then you can sudo as the other (i.e. the one with the "siem_2" database user attribute value) and running the same report. you should be able to look at the SQL in the queries and verify the FROM clause is sourcing from the different databases.

[IanT]

Handle this by extending the model file and using a different connection in there. Permission the different users to see the specific model file (and therefore connection) that they need.

[rfredman]

Is it really possible to extend the modelfile? 

[IanT]

Create a model file, remove connection, change the extension of the file so it doesn’t include .model (we call ours .base) and then just include the base file in a real model file with the connection you want to use. Do this multiple times for the combo of people access and connections. Same base lookml for everyone, different connections per model.

[rfredman]

Not sure if i follow, do you mean that we should include the base into multiple different models? 

OriginalModel.base -> Connection1.Model 
OriginalModel.base -> Connection2.Model 

If so, that would mean that they cant use the same reports, right? 

[IanT]

correct, content is tied to a model file...you can use lookml dashboards tho.

[rfredman]

Ok, thanks. Another thing to bring up with our looker rep.. 

[cjg4data]

Correct. You can not extend model files - Documentation

You can use extend Explores inside of model files (and/or use Refinements), but you can not extend the model file itself (e.g. to use different connections). User attributes, as described above, would be the way to do this.

[rfredman]

 User attributes cant change authentication method. So it wont solve our problem. 
(We want some users to use a service account, some to use oath)