aDDitional thoughts

The reality is that Essbase Studio is a simple tool for modeling and deploying Essbase cubes. While there are a variety of techniques you can use to increase Studio’s function-ality, in general, what you see is what you get. The real trick is knowing where to look, and this chapter has given you the map. The following sections cover some of the best of the rest.

3.7.1 Stealing SQL

A common request from people working with Essbase Studio is the ability to leverage custom SQL as opposed to the SQL the Studio generates. Studio’s programmatically generated SQL is not optimized because it must be universally accepted for a given data source. This means it cannot take into account indexing strategies on the fact table or other optimizations that may have been implemented in SQL Server 2008, for example.

In respect to data loading, this is easy to do. you can select the option in the upper left corner of Figure 3.40 to use custom SQL on the Essbase properties dialog for a given cube schema.

What you cannot do from the Studio uI is leverage custom SQL for dimensions. In fact, while there is a trick for stealing the SQL Studio generates for dimension builds, you can-not force Studio to leverage this SQL for deployment. If you are using Studio to prototype against a relational source and then want to leverage that same SQL in a SQL Interface-Based Load rule, you can copy the SQL out from the Studio-generated Load rules.

3.7.1.1 See Studio’s Dimension Build SQL Studio

1. use the Cube Deployment Wizard to deploy the Essbase cubes using the Create and save rule file only option.

2. once the deployment is completed, browse to the Essbase database directory using the Windows/Linux/unix file system.

3. open the Load rule in a text editor (Figure 3.41).

Figure 3.40 Data Load SQL dialog box.

4. The clear text in the Load rule is the SQL generated to build the dimension.

This SQL can be copied out and leveraged as the basis for the SQL in the SQL Interface-Based Load rules.

Note: making any changes to the SQL in the Load rule in the text editor is not a supported action. yes, this is a hack, but a cool and useful one.

3.7.2 To Stream or Not to Stream

one of the often-overlooked capabilities of Studio is data and metadata streaming.

Studio performs its modeling tasks using JDBC (Java database connectivity) drivers.

however, when you deploy a cube using the Cube Deployment Wizard, that build is handed off to the Essbase SQL Interface and oDBC (open database connectivity) con-nections. That means that the Essbase server must have the proper drivers installed and oDBC connections setup prior to deployment (Figure 3.42).

If you are running in a stand-alone environment where you have control, ensuring the oDBC connections are in place is easy. however, there is a very good chance that the Studio and Essbase servers are on separate physical boxes. If that is the case, you may not have access to either, then create an oDBC connection on the Essbase box or set up the required oDBC connections.

Studio provides the option to force (or stream) the entire deployment process to run from the Studio server and the JDBC connections. Streaming is controlled in the Studio server.properties file. to enable streaming add the following line to the Studio server.

properties file:

server.essbase.streamingCubeBuilding=true

Note: In general, streaming provides slower build and load times as compared to the Essbase SQL Interface. Also, in newer versions of Studio, you also have the option, as shown in Figure 3.43, of having Essbase dynamically generate the oDBC connections at time of deployment. This uses the built-in drivers on the Essbase server and the con-nection information in Studio.

Note: When using the oracle database as a source, you have the option of leveraging an oCI connection to optimize processing.

Figure 3.41 Produc.rul opened in a text editor.

3.7.3 Considering XOLAP

While Studio provides a breadth of capabilities for modeling and deploying Essbase cubes, the majority of those features can be handled by other build methods. The SQL interface and the Data Prep Editor can handle SQL and text file modeling. Essbase Integration Services can create and deploy drill-through reports (and you also can cre-ate drill-through reports via maxL in recent incarnations of Essbase). The one capability where you must use Essbase Studio is in creating and deploying an xoLAP model.

xoLAP is simply leveraging Essbase for metadata navigation and the oLAP ad hoc experience while generating dynamic SQL back to the data source to bring back the numbers; in short, it is the Essbase version of roLAP.

xoLAP models are ASo cubes modeled into Studio and deployed with a specific set of options and restrictions (more on these in a moment). The value in xoLAP models is in three main areas:

1. Latency: There zero latency between changes in the data source and the values shown in Essbase. If the source data systems change, you see those results as soon as you refresh your report.

2. navigation: By leveraging Essbase, you still have the oLAP navigation capa-bilities (zoom-in, zoom-out, keep only, etc.), and you have access to the data through all Essbase reporting front ends and APIs.

Figure 3.43 Dynamic ODBC connection string.

Modeling through JDBC

Data Source

SQL Interface ODBC

Essbase Essbase

Studio

During Deployment, Studio hands the process off to Essbase

Figure 3.42 Essbase, Studio, and Data Source connections.

3. Calculation: Calculations (member formulae) in xoLAP models are handled by the Essbase calculation engine. As such, complex calculations that are often more difficult relationally can be handled in the oLAP engine.

The main downside (aside from the design restrictions) is performance. With an xoLAP, model performance is no longer governed by Essbase, but rather the underly-ing data sources. If the source system is slow to respond, so is Essbase. Further, you can-not generate custom SQL when working with xoLAP, you have to use the generic SQL generated by Studio. In short, xoLAP models are very often significantly slower than full oLAP models.

Although performance concerns must be considered, from a prototyping perspec-tive, xoLAP models provide an excellent vehicle for data exploration in the source sys-tems. Because xoLAP models deployments are metadata only, they are extremely fast (comparatively) to deploy. you can then use an xoLAP model to walk through the data-set with the client or key stakeholders and validate assumptions about the source data.

to build an xoLAP model from Studio, you need to select the following Essbase properties for cube schema:

•  Aggregate Storage model

•  Duplicate member name Support

•  xoLAP model

Note: When you select xoLAP model, as in Figure 3.44, the other two options are automatically selected and “grayed out” because they are not optional for xoLAP.

In addition to the Essbase property requirements, there are hierarchy/dimension design restrictions when working with xoLAP:

•  Derived text measures cannot be used. no hierarchies allowed in attribute dimensions.

•  recursive tables for hierarchy definition are not allowed.

•  hierarchical measures are not allowed.

•  Level 0 user-defined members are not allowed.

•  Filters cannot be used on the hierarchies used in the model.

3.7.4 Managing Studio Processes

given that Studio is a front end for Essbase, it is easy to forget that under the hood, Studio is a server. It is a stand-alone engine that performs modeling tasks directly on data sources and communicates with and deploys models to the Essbase server. As such, the Studio server has a series of settings you can manage and monitor for troubleshoot-ing purposes. These setttroubleshoot-ings center on how Studio handles parallel processes and how the server handles thread usage.

Figure 3.44 Selecting the XOLAP model.

While much of this chapter talks about ideas on prototyping with Studio, this section is more relevant for those situations when you are using Studio in an ongoing produc-tion scenario. The commands in table 3.2 may never need be changed from their default settings, but it is useful to understand how to monitor and modify how Studio uses threads and other system resources.

All of these commands can be executed directly in the Studio server. For example, you can display the server threads and set concurrent tasks by issuing the following commands in the Studio server window:

pconf sres 250

realistically, however, the Studio server is likely running as a service. As such, you can place these commands in the Studio Server.Properties file. For instance, to set the number of server resources (sres), you include this line in the Server.Properties file:

server.resourceCount=250

Note: Any commands issued directly in the Studio server temporarily override con-flicting commands in the Server.Properties file.

3.7.5 Retrofitting Existing Models

Another common question when working with Essbase Studio is the ability (or inability) to leverage an existing Essbase cube. If you want to leverage Studio for an ongoing pro-duction deployment, what do you do with your existing Essbase cubes? Can you convert the current models quickly into Studio? Can you simply add drill-through reports to an existing Essbase cube?

The realistic answer is: It depends. Studio is a long-term replacement for EIS. If you have an existing deployment leveraging EIS, then you can use the EIS catalog migration wizard in Figure 3.45 to migrate the mini-schema, hierarchies and other artifacts to the Studio catalog environment. While this does not create a perfect rep-lica of the EIS deployment, it does provide a solid foundation for migrating an existing deployment.

If you are leveraging Load rules for the current deployment, you cannot import these rules into Studio. The hierarchies they represent, including any transformations, need

Table 3.2 Studio Console Commands

dumps Displays information on a current server threads

pconf Displays state of the Studio server (threads, resources, etc.)

squeue Displays and lets you set number of tasks waiting to be executed by the Studio server sthd Sets the number of threads being used by the

Studio server

sres Sets the number of concurrent tasks being executed by the Studio server (20 is the default)

to be rebuilt as Studio hierarchies and deployed to Essbase using the Cube Deployment Wizard.

often, what people want to do with Studio when they talk of retrofitting exist-ing models is the population of existexist-ing cubes (includexist-ing hyperion Plannexist-ing cubes) with Studio drill-through reports. to reliably populate a drill-through report defini-tion into an Essbase cube, Studio must deploy the outline. An additional complexity with Studio drill-through report definitions is that to execute and display reports you need to use Smart view for office and the Essbase Smart view Provider (Analytic Provider Services). As such, properly deploying the cube via the Studio (even if just deploying the application and database definition with Load rule) through the cube deployment wizard is the best way to ensure your drill through definitions work as designed.

Instead of concentrating on retrofitting an existing Essbase or Planning model to contain drill-through definitions, you should consider building a secondary report-ing model (likely in ASo) to leverage for both larger user reportreport-ing and drill-through.

many Planning deployments, for example, contain an ASo component for either quicker reporting or to combine the Planning data with other required data for com-plete reporting. you can use Studio to build and deploy this reporting model using the same dimensionality as the Planning or custom Essbase cubes, and embed the desired drill-through reports.

In addition to directly connecting to the source relational or flat-file sources, when working to mimic an existing Planning model, there are a number of options to keep the dimensionality synchronized between the source Planning model and the Studio generated cube:

•  use shared EPmA dimensions (Figure 3.46): Studio can connect to EmPA as a data source (Dimension Server) and use “shared” dimensions to build a cube.

•  Data relationship management (Drm) exports: Studio can use flat file exports from Drm to build the reporting cube.

•  Financial Quality Data management (FDm): Studio can use either flat file exports from FDm or can connect directly to the FDm relational table to source hierarchies.

Figure 3.45 EIS catalog migration.