The final phase in moving from data source to Essbase cube involves modeling and deploying the cube. When I talk about modeling in this phase, I am referring to the process of selecting hierarchies for a given cube and specifying the desired deployment properties. While on the surface, this phase of working with Studio appears generally straightforward, there are a number of tricks you can utilize to more effectively deploy your Essbase database.
3.6.1 Creating a Cube Schema without Metrics
The first step in the deployment phase is selecting the desired hierarchies for the Essbase cube. When selecting the hierarchies you must specify the standard dimensions as well as the measures dimension or column.
Figure 3.26 Recursive Measures table.
Figure 3.27 shows a single column as the measures. This is done because, in the spe-cific model, the measures dimension was built from a recursive table as a standard hier-archy type leveraging the parent/child relationship. The fact table used for this build contains a single data column (“Amount”).
What do you do, however, if you are simply creating a prototype with Studio and either do not have a data file or want to load the data from a process external to Studio?
In either case, you can trick Studio into believing you have a measures column. Instead of adding a true data column to the Cube Schema wizard, you can add any column with a numeric data type to this portion of the uI to simply move forward. For example, in Figure 3.28, ProDuCtID from the Product table is added to the measures area of the Cube Schema Wizard. once in place, the uI lets you move forward to the next step as in Figure 3.29.
If you plan to employ this trick during the deployment phase, make sure you think ahead when adding a data source to Studio and confirm that it has a numeric column in one of tables or text files.
Note: This trick only works if you use a standard hierarchy as your Accounts/measures dimension. This hierarchy is added to the standard dimension area like products, time, or any other hierarchy. If you have a “measures” type hierarchy and you want to add it to your Essbase cube, this definition must be added to the measures hierarchy area on the cube schema wizard (Figure 3.30).
Figure 3.27 Specifying dimensions.
3.6.1.1 Loading Data External to Studio After the metadata is in place, your next deci-sion is how to load the data. From a data load perspective, a cube built using Studio via the Cube Deployment Wizard or directly from a numeric data source is like any other Essbase database. It is only natural to question why you would load data external to Studio after leveraging Studio to build the dimensionality. The answer is: Control.
When loading data with Studio you have the option of writing custom data-load SQL. This is good, assuming you are leveraging a relational source, but not always ideal.
Figure 3.29 Specifying dimensions with a faked Measures table.
Figure 3.28 Faked Measures table.
For instance, if you are building an ASo model, you may want to take advantage of multiple load buffers to expedite load process.
From a prototype perspective, it is often easier and quicker to go directly to the data source or use a secondary flat file for the data load. From a production deployment perspective, you should benchmark the load time results from Studio compared to other methods to determine which method yields the most efficient and manageable results.
3.6.2 Leveraging Open Properties to Validate Dimensionality
Everyone believes his or her data is clean. to be blunt, it never really is. This statement is not intended to insult an organization or an individual. People honestly believe that their data is in a good state and ready to build; there are those rare instances when that is the case. The problem, often, is not data—it is people. Data is clean and rules are fol-lowed until humans get involved and break the rules. over time the tightest of systems experience a level of dirty data and metadata. Further, the definition of what represents clean data varies from one person to another.
For instance, a client might find it perfectly acceptable that there are duplicate mem-bers in the markets dimension when you intend to follow Essbase best practice and create a unique member model. Inevitably the understanding and discovery of data inconsis-tencies comes out during the load and build process. These can often be very difficult to scrub. however, you can leverage open settings and incremental build options in Studio to help find and resolve these inconsistencies.
Figure 3.30 Specifying dimensions with “Measures” hierarchy type.
The recommended practice for Essbase is to (when possible) create unique member names in the cubes. It is also a recommended practice, when creating ASo models, to have as few dynamic dimensions as possible. on your initial deployment of dimension-ality from Studio, you can violate both of these rules to validate the metadata being sent to Essbase, as shown in Figure 3.31.
When Studio validates dimension settings and the schema prior to deployment, it literally validates only those things. Studio does not validate the metadata being sent to Essbase. If you select unique member names and then try to deploy to Essbase with duplicate metadata, the deployment fails at the Essbase side, and you need to then start trouble-shooting to understand why. If you instead allow duplicate members, you can build all of the dimensionality and validate dimension by dimension in EAS.
Figure 3.32 shows turning off duplicates on a dimension-by-dimension basis after the duplicate member name enabled cube is deployed.
As long as the outline validates, you know that you do not have a duplicate issue (for instance) in a given dimension. If you do run into duplicate issues in a dimension, then
Figure 3.32 EAS duplicate Member setting.
Figure 3.31 Duplicate Members enabled.
you can turn off duplicates on a level-by-level basis in that dimension and continue to validate the outline until you focus in on the specific members that are causing the issue.
Along the same line, when you are dealing with ASo models, you can set the dimension types to all “Dynamic” for initial deployment purposes, as in Figure 3.33.
Even after you have scrubbed duplicate members out of the dataset, you might end up in a situation where multiple shared members appear in a dimension hierarchy. Like unexpected duplicates, this causes the cube deployment to fail on the Essbase side.
making all dimensions dynamic on the initial deployment, Essbase allows all consoli-dation operators and multiple shared members to deploy in a dimension or hierarchy. In a similar process to duplicate, you can then go into EAS and toggle each dimension from
“dynamic” to “stored” or to “multiple hierarchies enabled” and validate the outline. This lets you see the specific hierarchies and members where you have issues.
3.6.3 Incremental Building to Validate Dimensionality
one final consideration in deploying the cube from Studio is leveraging incremental build options. This makes sense, of course, when adding to an existing cube. It also is quite useful when first deploying a cube.
The deployment process from Studio can take minutes or hours (usually somewhere in between). At any point during the deployment, if a dimension fails the entire deploy-ment is aborted (even if multiple other dimensions have, in theory, built correctly).
If you are dealing with large dimensions, such as a “Work order tasks” dimension with a million members, a failure can cost you hours of downtime. Instead, you can leverage the incremental build capabilities to deploy the model one dimension at a time and key in on a dimension or set of dimensions that cause a failure.
to leverage incremental building on the initial deployment:
1. Create an empty application and database in using Essbase Administration Services.
2. In Studio, initiate the Cube Deployment Wizard.
3. Fill in the Application and Database name with the application and database