A look at Table Views in OneStream – Update values in an SQL View 3

Table Views is an excellent addition to the analytic capabilities of the OneStream 5.2 version.

It allows a user to analyze values from a relational table to the OS Spreadsheet app. Yes, it is not available in the EXCEL Add-in, which I think it should totally be there. Hopefully a patch or another release)

Now, did I say you can update the values too! Yes, you heard that right.

I did take this feature for a spin. There was (I guess it still is) an issue with most SQL editors that you cannot update the values from a view.

If you think about it, that is correct too. IT IS A VIEW!

How does the editor know which tables to update?

What tables are involved in the view creation?

Prior versions of OneStream also had this issue; you can use a grid view to show values from a view. However, you cannot edit the values.

SQL Editor component was there; however, you cannot use a view in this component.

How does Table View solve this dilemma? Well, it won’t solve it for you. However, it can give you the armor and the weapons so that you can solve it 🙂

Table View offers excellent flexibility in displaying the content and how you allow the user to write back to the table.

The Challenge

I’m going to go with an example of updating Employee information. I picked this example, as I started playing with the 5.2 version and was doing this against an Oracle database (Sample HR Schema).

I was struggling with the UPDATE statements against Oracle from Vb.Net and immediately dropped the plan. I might go back to that approach, and you might see the solution as another blog post (If I succeed ;)).

I did like the HR schema for some odd reason and decided to migrate those into the SQL server. I did use SSMA for Oracle and migrated all the tables to the SQL server. I did update the Employees table to include two extra columns

• SSN
• Middle Initial

The customer wants to update the employee information like salary, job title. While he is at it, he would also like to update the minimum and Maximum Salary of the respective employee’s position.

Now the challenge is Employee information like salary, hire date, name et al. are stored in the EMPLOYEES table.

Job-related items like minimum and maximum salaries are stored in the JOBS table.

Yes, I did create a view to merging the information from both tables. However, how can the customer edit the information from a single screen?

Solution

The solution is to use a Spreadsheet rule and surface the information from the view on the Spreadsheet app. When the user updates an Employee specific information, an UPDATE statement gets issued against the EMPLOYEES table. When he makes a change to job-related information, an UPDATE goes against the JOB table.

I told you, you are deciding to update the underlying tables :). You are still viewing the data from an SQL view; once the UPDATE is successful, the new information automatically gets populated on the screen.

How it works

OneStream 5.2 introduced a new type of Business rules called “Spreadsheet”. You need to create a spreadsheet type rule to create a table view.

There are three function types supported in this rule.

1. GetCustomSubstVarsInUse
2. GetTableView
3. SaveTableView

Showing data

That is the order of operation in the rule as well. Any substitution variables that are used in the dashboard/spreadsheet needs to be resolved first.

Once that is done, you can get the table views. You have an option to support multiple table views in the same rule. If you do, then the user must type in the table view name when he adds the rule.

In my example, I have a table view name, and that must be used when you are retrieving the table view.

By default the name of the rule gets populated as the name, if the rule is supporting multiple table views, you must type in the name of the table view in the name field. I’m hoping for a release where OS allows you to show all the names in the rule as a drop-down list.

For the time being, you type in the name of the table view you need from the rule.

Once the correct information is filled in, you’ll see the data getting populated in the spreadsheet.

I’m using a parameter in my example, which allows the user to search for employees based on the job title.

You can use all OS parameters types here.

I’m using a bound list. This parameter populates the distinct job titles from the JOBS table and shows it as a drop-down for the user.

You can use this in a dashboard; in my case, since the parameter is not resolved (it is not coming in from a dashboard, or it is not a literal value), I’ll be prompted by spreadsheet rule to select a job title.

The GetCustomSubstVarsInUse function type achieves this part.

All I’m doing there is add the parameter that we saw earlier to a list.

Here is how it looks when you add the table view/refresh the sheet.

The spreadsheet can be added to a dashboard, and the drop-down can become a combo box, radio button, or can even come from another dashboard.

It’s time to fetch data, and this operation is performed using the GetTableView function type. TableView is similar to DataTable, just that it has extra functions.

• It can tell you whether a column is dirty so that you can update only that row.
• It can tell whether to add value as a header.

As you can see, I’m not showing the SSNs in this spreadsheet. Now, if you look at the same query in SQL server, you’ll be able to see those numbers.

Now here is the beauty of the spreadsheet rule. You can check whether a column needs special treatment and remove parts of data from it. You can also perform this check based on the user/group.

All I’m saying is if it is SSN, then show only the last 4 characters, make all other numbers X.

Saving data

Show and tell is over, it’s time to save the data. Now, as you saw, the data we have on the sheet comes from a view, which comes from two different tables.

When the user updates a row, you now need to find out which column was updated, find out which table needs an update and update the corresponding row.

Easy peasy.

I’m only allowing the user to change 3 fields.

• Salary
• Minimum salary of the job title
• Maximum salary of the job title

The salary is from the EMPLOYEES table, and the other two are from the JOBS table.

All I’m doing in that code is to loop through each cell. Once in the loop, find out whether salary, min_salary, or max_salary was updated. If it is updated, then create the UPDATE SQL statement for those and run it at the end.

I was hoping for an enhancement that allows you to hide columns/rows from the code because sometimes you, as a user, don’t want to see some columns. However, to save the information you might need them. In the example above, let’s say the user doesn’t want to see JOB_ID and Employee_ID.

You need those to update the rows as they are the primary keys for the JOBS and EMPLOYEES tables. Here is where an option to hide the column comes in handy.

For now, you can ask the user to hide those manually.

That information gets saved for the session, so “No, it won’t come back when you perform a refresh or pick another job title.”

Here is Table View in action.