Chapter 3. Building the Essbase Outline
We know that any true technology geek gets impatient, at the very least, when made to suffer through too much reading and page turning. We too feel the same way, but it was absolutely necessary to give you everything we did in the previous two chapters. Likewise, it is also necessary to give you every detail in this chapter.
You are aware of the fact that the Esscar Motor Company is hunting for a new system to provide their production and sales planning department with the ability to quickly create sales and production forecasts, profit forecasts, scenario over scenario what-if analysis, and more.
Armed with what you have learned in the previous chapters and using the Oracle Essbase Esscar application and database you created in Chapter 2, we will now guide you through building an honest-to-goodness real and usable database outline.
We will show you how to build the dimensions necessary to describe the data using a step-by-step approach. We will add members...
Before we get too deeply involved with the Essbase outline, we just wanted to be clear that the information and examples presented in this chapter assume that you have created your database using the BSO.
We devote most of the future chapter to explaining the BSO and the ASO options, both their similarities and their differences.
So remember, the BSO is the flavor of this chapter.
The Essbase outline—the foundation
Think of the Essbase database outline as the air traffic controller of the database or a planning and logistics manager or even a traffic cop. For those of you who are relational database fans, you can think of an Essbase database outline as a logical database model. It can be thought of as a tool that gives you a visual reference to how the data is stored in the database and how the different elements relate to each other. Think of the Essbase database outline as all of these things and more.
As the very foundation, the Essbase database outline is the framework or base platform, upon which the entire database is built. If you remember, when you created your first Essbase database in the previous chapter, a shell or empty Essbase database outline was automatically created.
This newly created shell outline has no dimensions or members, does not contain any data, and is just a place holder or starting point. Let us now learn about the various components and...
We have previously discussed that the Essbase database outline is comprised of components called dimensions and members. To quickly review, remember that the outline dimensions are best described as the categories of your data that is, model year, calendar periods, and so on. For you die-hard relational database types, dimensions loosely translate into the columns you use in your tables. Database outline members are the children of the dimensions. The database members and dimensions enjoy a hierarchical parent-child relationship with the dimensions at the highest level.
Dimensions are best used to describe the data in the data warehouse system. Dimensions are the top-most members in the hierarchy. A dimension can represent the summarized or consolidated data for all its children members. In the following screenshot TOTAL VEHICLE is the parent dimension.
Members are children of the parent dimensions. A parent dimension can have an infinite number of child members....
In Essbase, there are two types of dimensions, the first and most common dimension type is the Standard dimension. The other less common dimension type is called an Attribute dimension. Both are explained here.
Standard dimensions are derived from the main business data components (or data categories we described earlier) and are then defined in a database cube. Some of the Standard dimensions in our example database are: TOTAL VEHICLES, MEASURES, CALENDER PERIODS, TOTAL MODEL YEARS, CUSTOMERS, MARKET, and SCENARIO. These dimensions can be tagged as Accounts, Time, and Country types. Some dimensions are not associated with any type of special dimension types and are considered general dimensions.
Briefly, let's explore the different ways you can tag a Standard dimension and their uses.
The Account dimension type
When you tag a dimension as an Account type, you are telling Essbase that this dimension will be used for your financial measures or metrics....
That's it! Your brain is just chocked full of Essbase database outline knowledge. Let's build an Essbase database outline using the EAS.
Within EAS, perform the following tasks:
Double click, to expand in the EAS, on ESSBASE Severs then Applications then ESSCAR (Application) then ESSCAR (database) and finally Outline. The ESSCAR database outline is opened in the Essbase Outline Editor as shown in screenshot below. Remember, as we said before, this is basically a placeholder outline created for you by Essbase when you created the new database.
Your job is to now build this outline into a fully functioning Essbase database outline that will support a real database.
The steps to add dimensions and members to an Essbase database outline are:
1. Begin by right-clicking on Outline, and then click on Add Child.
2. Enter CALENDAR PERIODS to name this new member and create your first Essbase database dimension.
Remember, any child member that is added to the main outline is...
Member properties are characteristics, set by you, that determine the behavior of the data stored in your database. For example, it is quite natural to want to know the value of sales for a quarter. If you set the consolidation property of each month member to (+) and calculate the database, the calendar quarter value will be the sum of the three child members under it which are the individual months. There is no need to load data at the calendar quarter's level since you can calculate it from the individual month's level.
There are also storage properties that Essbase uses to determine how the data is stored in the database. The following is a detailed explanation of member properties and their uses.
When you define a new child member of a dimension, you need to tag that member with a consolidation operator. A consolidation operator will tell Essbase how this member will be calculated along with its sibling member(s) upto the parent member. Get familiar...
Build your first data rules file
Before we start building a data rules file, let us talk about the purpose of the Essbase data rules file.
The Essbase data rules file is one of the smartest features available to you as an Essbase database programmer! Whether you are using data with a rules file to dynamically add members to your outline (dimension build setting), or loading data into the database (data load setting), the Essbase data rules file can prove to be an invaluable tool.
Data rule files can be used to maintain your Essbase database outline or load data into your Essbase database. In this section, we will talk about using the data rules file to update the Essbase dimensions. In the next chapter, we will talk about how to use the data rules file to load data into your database or cube.
You can run your data load or dimension build data rules file with accompanying data manually from the EAS tool or you can run it by executing an Essbase Command or Essbase MaxL script.
What are we talking...
Update your outline using a rules file
Now that you have completed your first rule file, you need to update the outline using this rules file.
There are several ways you can update an outline using a data rules file and a data file:
Updating the outline from the EAS Outline Editor
Executing MaxL commands
Making your own API calls
Running Essbase command scripts
We will discuss the first and second points now, and the third and fourth points will be discussed with examples provided, in the next chapter.
Update your outline using the EAS Outline Editor
One of the easiest ways the outline can be updated is manually by using the EAS Outline Editor.
The steps to update the outline are:
1. In the EAS, open the Application | Database for which the outline needs to be updated.
2. Double-click on the Outline, the outline editor is opened for editing.
3. Or, you can click on the Outline in the Menu option and then click on the Update outline. The following screen will then be displayed:
As you can see from the...
Attribute dimensions are dimensions associated with standard dimensions. Attribute dimensions are useful in describing a standard dimension member's attributes. For example, in our Esscar database, we have the Vehicles dimension. If we wanted to differentiate between a two or four door car of the same model, we could use an attribute dimension to accomplish this. We could also use an attribute dimension to track color.
Note
Attribute dimensions are always dynamically calculated, which means they do not store the data. This is always a benefit as they do not affect the size or performance of the database.
Attribute dimensions must always be associated with a standard dimension.
Earlier, we briefly saw the differences between standard dimensions and attribute dimensions. Sometimes, your data has attributes that make it desirable to perform further analysis on it. In the case of the Esscar Motor Company, it may be desirable to track a certain vehicle model by its color....
User Defined Attributes (UDA)
An Essbase UDA is a descriptive word or tag about an outline member. Similar to an alias, the main difference is that an alias may only be attached to one member. A UDA can be attached to many members.
What the UDA offers is a way to simplify and make the ongoing operations of your database more efficient.
For example, because forecasting analytics is one of the most common uses for an OLAP system, the users deal with time periods that fall into three distinct categories. In any Essbase database outline, you will have historical time periods, actual time periods, and forecast time periods. Typically, the data you have previously loaded for historical time periods, and the data you just loaded for actual time periods, does not need to be fully calculated since it is real data and does not need to be derived or changed. On the other hand, your forecast data still needs to be calculated whenever you make a change to a component piece of data. If you tagged your...
Once again, Essbase gives us a feature so cool that it makes us wonder how we lived without it! The Dynamic Time Series function is one of those features.
We can't think of anyone who wouldn't use this functionality. Likewise, we can't think of anyone who wouldn't need this functionality.
What the Dynamic Time Series does for you is automatically calculate period-to-date data based on your Time dimension. Using our example database for the Esscar Motor Company, we see that it is probably a good idea to know our year-to-date vehicle production, by both calendar year and model year. The Dynamic Time Series function does this for you. You can easily calculate period-to-date data for any period as defined in your Time dimension.
In other words, Essbase can automatically calculate a cumulative number based on how you have the Time dimension set up. If you want to track how many vehicles you have built from the beginning of the year through the current month, the Dynamic Time...
Shared members are another great tool offered by Essbase to help you increase your data and reporting versatility in a simple and extremely efficient manner.
In a nutshell, a shared member is another occurrence of an already existing member in the outline. The shared member only stores a pointer to the existing outline member, thereby giving serious space and processing efficiencies in the database.
The benefits do not end there either. What you can do is have the shared member /original member be a child to many different parents. For example, if you have a product that is available in several markets, you can organize your outline with the child members as needed for each market. The shared member can appear in several places simultaneously and be selectively calculated with relative ease.
In the preceding screenshot, notice how we have the individual vehicles located in three different groups in the database outline. The vehicles under the Total Vehicles parent are the original...
Well, that wraps up the chapter on the Essbase outline. We know you've been hit with a lot of information upto this point. There is a lot to know about the Essbase database outline.
You have learned how to add dimension level members to the database outline and you've learned how to make decisions on how to best determine what categories of your data best fit as Essbase database outline dimensions.
You have learned the myriad available properties that a database outline member can have. For instance, how the child members consolidate their values to the parent member, or do the individual members store data or have it dynamically calculated.
You have also learned that there are several ways to actually update an Essbase database outline. You can perform any necessary task manually by using the EAS, but you can also perform most routine tasks automatically by either using a dimension build load process or by executing a MaxL command script.
You are now at a point where you should have...
