Database Reference
In-Depth Information
3.7 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.
