Recently, I was presented with a unique client requirement. They needed to prevent write back functionality when acting as users who have write back capabilities. They also wanted to be able to turn it on or off depending on the situation, since there are some scenarios where using Write Back in conjunction with Act As is needed. This created a few challenges because of how Act As and Write Back are configured in OBI.
First, let’s look at the Act As and Write Back configuration in my OBIEE 12.2.1.3 test environment.
I have set up two users in my proxy table to use for testing.
Both of these users are available to weblogic for Act As functionality. For testing purposes, the user Matt has full author access within Answers as well as Write Back capabilities and the user Bill has Read-Only access.
Note: For more information on how to set up Act As in OBIEE 12c, please refer to the Oracle document.
Now, let’s take a quick look in Answers to check the functionality and see how the out-of-the-box session variables behave when using Act As (this will be important later in the blog post).
I’m going to log in using weblogic and create a quick analysis, calling the following session variables:
Now, I’m going to show the same variables when Acting As the user Matt.
Notice how the out-of-the-box variables are populated while using Act As the same as if they would be logged in as that user.
For Write Back testing, I have set up a simple template that will allow users with Write Back capabilities to update the Product Name column.
Note: For more information on how to set up Act As in OBIEE 12c, please refer to the Oracle document.
Now, let’s work through preventing Write Back while using Act As. First, I need to set up a session variable that returns a value when Act As is being used. To do this, open the RPD and navigate to Manage > Variables.
I’m going to create a new Init Block using the following SQL to populate the target variable ACT_AS with the value ‘True’ when the RUNAS session variable is equal to the USER session variable:
SELECT CASE WHEN
UPPER('VALUEOF(NQ_SESSION.RUNAS)') = UPPER(':USER') THEN 'True' ELSE
'False' END
FROM
TST_BIPLATFORM.OBIEE_PROXY
I am going to use the ACT_AS variable in my custom message template used for Write Back. Make sure to set the Init Block connection pool to the same connection pool as the other variables you are using for Act As. Also, make sure that the target variable’s Default Initializer is set to the same value as the ELSE value in the CASE statement (in this case, ‘False’). This ensures that, if the connection ever fails, the default initializer will populate the variable and users will not lose Write Back functionality. It also ensures that this will be tied with Act As functionality being available for use.
Next, I’m going to alter the SQL in the template used to update Product Name in the customMessages folder to use the ACT_AS Variable. I am going to set the value to ‘False,’ because I only want Product Name to be updated when the Act As functionality is not being used.
Now, it’s time for the test. I’m going to log in as the User Matt and create a quick analysis to show the value of the ACT_AS Session Variable.
I’m going to use Write Back as the user Matt and change a value for one of the products in the Product Name column.
Next, I’m going to log in as weblogic and Act As the user Matt to see if I can change the value back to its original state.
When I click Apply, it notifies me that the update was successful but, when the analysis reloads, the value remains unchanged.
I can further verify this by looking at the value in the database itself.
Because the variable value is set to ‘True’ when using Act As, the constraints defined in the template are not satisfied and therefore will not allow Product Name to be altered.
While this is a very specific use case, preventing or allowing Write Back under certain conditions allows for additional layer of flexibility for organizations.