It's Friday afternoon, and you are already tasting a metaphorical daiquiri. Suddenly, the phone rings: it's your boss! Are you getting a pay rise? Did he finally agree to send you to Splash on expenses...?
"Hey, we have a bit of an issue. Judy says she noticed several OneStream accounts belonging to employees who have actually passed away. There are probably more, we better find them before auditors get on our case. If you could sort this out today, that would be great."
"No problem, boss!". As you put down the phone, you sigh heavily. There is, in fact, a "yes problem": the HR system with anagraphical data is in a separate database, so you can't just whip up an SQL Data Adapter with a JOIN to find these ghostly accounts.
How can we deal with this complexity? When there's something strange in your SQL needs, who're you gonna call? Dashboard DataSets. DDS rules allow you to mix & match datasets coming from any source, with a minimum of scaffolding. Sounds perfect for our job, so let's write one!
DDS get called by OneStream in two situations: to return a dataset (duh!) or to list all datasets the rule can produce. Satisfying the latter demand is a matter of best practice, and it will help some corners of the interface that users or administrators can see when choosing datasets.
So the first thing we do in a DDS is figuring out why we were called, by checking args.FunctionType :
Select Case args.FunctionType
Case Is = DashboardDataSetFunctionType.GetDataSetNames
' return the list of datasets we can produce
'...
Case Is = DashboardDataSetFunctionType.GetDataSet
' return an actual dataset
'...
Building and returning the list of datasets is trivial: create a list of strings and return it. You can use a handy list literal for it:
Case Is = DashboardDataSetFunctionType.GetDataSetNames
' return the list of datasets we can produce
Return New List(Of String)({ _
"GhostDetails", _
"SomeOtherDataset"}) ' we won't actually implement this second one
Done!
Satisfying the other circumstance (i.e. producing a dataset) is where we do the real work. To start with, we have to figure out which dataset was requested; so we check the aptly-named args.DataSetName property:
Case Is = DashboardDataSetFunctionType.GetDataSet
If args.DataSetName.XFEqualsIgnoreCase("GhostDetails") Then
' ...
Else If args.DataSetName.XFEqualsIgnoreCase("SomeOtherDataset") then
' ...
End if
What happens now, is entirely up to us. We could be calling a website to give us FX rates of the day, or we could read a file saved in a certain location. DDS are incredibly powerful!
In this case, we want to do three things:
- retrieve users from OneStream
- retrieve detailed data from the external HR system
- mesh the two sets of records.
The first bit is straightforward, once you know the basics:
If args.DataSetName.XFEqualsIgnoreCase("GhostDetails") Then
Dim usersDt As DataTable
' get a connection to the framework db
Using dbConn As DbConnInfoFW = BRApi.Database.CreateFrameworkDbConnInfo(si)
' create a list of conditions ("WHERE" clauses) to filter the table by;
' in this case, we only want enabled users
Dim dbWheres As New List(Of DbWhere)({ _
New DbWhere("isEnabled", DbOperator.IsEqualTo, True)})
' create a list of columns to sort by
' This is basically optional, you can pass Nothing instead
Dim dbOrderBys As New List(Of DbOrderBy)({ _
New DbOrderBy("Name", True)})
' perform the query
usersDt = BRApi.Database.GetDataTable(dbConn, _
"SELECT * FROM SecUser", _
dbWheres, dbOrderBys, False)
End Using
Employing the Using block ensures that the connection will automatically be discarded once we're done with it (or an exception is thrown), without wasting resources. Inside, we perform our query using GetDataTable, which returns an ADO DataTable object containing our records.
Note how I'm using DbWhere clauses to filter the table, rather than hand-writing the entire SQL query. This helps with performance optimizations behind the scenes, and it's the recommended way to query going forward. Top tip: you need at least one DbWhere object in the list, or the query could fail; if you need all records, you can create a dummy object like this: New DbWhere("1", DbOperator.IsEqualTo, "1")
Retrieving data from the external HR system is even easier. If the external database was added to the Application Server Configuration file, we can simply reference it by the name specified there. In our case, we know "Big HR System" contains a "Users" table with a "isDead" column (how lucky!).
' filter to keep only dead folks
Dim dbWheres As New List(Of DbWhere)({ _
New DbWhere("IsDead", DbOperator.IsEqualTo, True)})
' perform the query
Dim ghostsDt As DataTable = BRApi.Database.GetCustomDataTable(si, _
"Big HR System", "Users", _
dbWheres, Nothing)
(If the system had not been preconfigured like this, we would have had to use BRApi.Database.CreateCustomExternalDbConnInfo in a Using clause, like we did for the first query.)
Now we have two DataTable objects, how do we mesh them? Another 3 steps:
- creating a DataSet (Note: this is not compulsory, a DDS can be satisfied by just returning a DataTable. We are using a full DataSet only because we want to mesh two of them.)
- adding tables to it
- building a relation between tables
In this case, both tables have an email field we can use to join records, so we pass those fields to the .Relations.Add() method.
' create the dataset
Dim dsn as New DataSet("DetailedGhosts")
' add tables
dsn.Tables.add(usersDt)
dsn.Tables.add(ghostsDt)
' create the relation
dsn.Relations.Add("emailRelation", _
dsn.Tables(1).Columns("email"), _
dsn.Tables(0).Columns("Email"), _
False)
' done! Let's return the DataSet
Return dsn
(A little gotcha: some APIs will return DataTables that have already been wrapped in a DataSet behind the scene, so adding it to a new one will result in an error. If that's the case, you can copy the object with yourDataTableObject.Copy() , which creates a free object you can use; or you can try to remove the table from the dataset, which is more memory-efficient but might not always be possible.)
We can then create a Data Adapter that will execute our rule:
You can test the adapter right there and then, with the handy Test button above it:
That's it! We could now attach this adapter to a GridView or Report component, and send the resulting output to Ray, Egon, or Winston, so they can set up the relevant traps (because let's be honest, Peter ain't gonna do it).
Our weekend days (and New York) are safe once more.
4 Comments
