Database Reference
In-Depth Information
essOtl.save(EEssRestructureOption.KEEP_ALL_DATA)
essOtl.close(); essCube.clearActive(); essSvr.disconnect(); ess-
Home.signOff();
The sort method we use here is a very powerful collection method. The collection can
contain anything (here our collection is an array of arrays). The sort method executes a
closure against each element of the collection, and whatever value the closure returns is
used to determine the sort order. We sort the same array twice using different closures.
We also use the reverse collection method to coax our array into the order we need. The
way we use movemember places the member as the first child beneath the parent mem-
ber, so we move the ones we want to end up higher in the outline after the ones we want
to end up lower in the outline (see recipe 9.5.8 for a similar technique).
9.5.6 Querying Data and Balancing against a Data Repository
At the end of data load script, it is nice to know that all the data is loaded and rolled up
properly. one way we can do this is by running and parsing report Scripts. With the
JAPI, we can just query the data directly. We can script user operations like zoom and
pivot, then pull data out of the resulting grid. We can use groovy's SQL (structured
query language) package to easily query data in a source repository for comparison.
Again, pretending that Sample.Basic has a relational data source, we will make sure that
source and Essbase agree on the sales dollars at the product family level.
import com.essbase.api.session.IEssbase
import groovy.sql.Sql
sqlVals = [:] //empty map
sql = Sql.newInstance("jdbc:sqlserver://sqlServerName;databaseName=
dbName", 'sqlUser', 'sqlPass', "com.
microsoft.sqlserver.jdbc.SQLServerDriver")
sql.eachRow("SELECT Prod_Fam, SUM(Sales) FROM TBC_Data GROUP BY
Prod_Fam") { record ->
sqlVals[record[0]] = record[1] // add map entries (key=Prod_Fam,
value=Sales)
}
essHome = IEssbase.Home.create(IEssbase.JAPI_VERSION)
essSvr = essHome.signOn('user', 'pass', false, null, 'embedded',
'ess_server')
essCube = essSvr.getApplication('Sample').getCube('Basic')
cv = essCube.openCubeView('default')
grid = cv.getGridView()
grid.setSize(2, 5) // 2 rows, 5 columns
grid.setValue(0, 1, "Actual"); grid.setValue(0, 2, "Sales"); grid.
setValue(0, 3, "Market"); grid.setValue(0, 4, "Year"); //col hdrs
grid.setValue(1, 0, "Product") //row hdr
opZoom = cv.createIEssOpZoomIn()
opZoom.addCell(1, 0) // add "Year" cell to zoom operation
cv.performOperation(opZoom)
essVals = [:] // empty map
(1..<grid.countRows).each{
essVals[grid.getStringValue(it,0)] = grid.getDoubleValue(it,1)
//add map entries
}
