Designing a Single - Server Multidimensional Database

Designing a Single-Server, Multidimensional Database

To implement a multidimensional database, first you install Essbase, and then you design and create an application and databases. You analyze data sources and define requirements very carefully and then decide whether a single-server approach or a partitioned, distributed approach best serves your needs. For criteria that you can review to decide whether to partition an application,

The information in this post is designed for block storage databases. Some of the information is not relevant to aggregate storage databases.

Process for Designing a Database

As illustrated in the below figure, designing an application is a cyclic process that moves from a planning stage to a verification stage.

The database design process includes the following basic steps:

1. Analyze business needs and design a plan.

The application and database that you create must satisfy the information needs of your users and your organization. Therefore, you identify source data, define user information access needs, review security considerations, and design a database model.

2. Draft a database outline.

The outline determines the structure of the database—what information is stored and how different pieces of information relate to one another.

3. Check system requirements.

How you meet system requirements and define system parameters affects the efficiency and performance of the database.

4. Load test data into the database.

After an outline and a security plan are in place, you load the database with test data to enable the later steps of the process.

5. Define calculations.

You test outline consolidations and write and test formulas and calculation scripts for specialized calculations.

6. Define reports.

Users access data through print and online reports and spreadsheets or on the Web. If you plan to provide predefined reports to users, you design report layouts and run reports.

7. Verify with users.

To ensure that the database satisfies your user goals, solicit and carefully consider user opinions.

8. Repeat the process.

To fine-tune the design, repeat steps 1 through 7.

Case Study: The Beverage Company

This chapter bases the database planning process on the needs of a fictitious company called The Beverage Company (TBC) and uses TBC as an example to demonstrate how to build an Essbase database. TBC is a variation of the Sample Basic application that is included with the Essbase installation.

TBC manufactures, markets, and distributes soft drink products internationally. Analysts at TBC prepare budget forecasts and compare performance to budget forecasts on a monthly basis. The financial measures that analysts track are profit and loss and inventory.

TBC uses spreadsheet packages to prepare budget data and perform variance reporting. Because TBC plans and tracks a variety of products over several markets, the process of deriving and analyzing data is tedious. Last month, analysts spent most of their time entering and rekeying data and preparing reports.

TBC has determined that Essbase is the best tool for creating a centralized repository for financial data. The data repository will reside on a server that is accessible to analysts throughout the organization. Users will have access to the server and will be able to load data from various sources and retrieve data as needed. TBC has a variety of users, so TBC expects that different users will have different security levels for accessing data.

Analyzing and Planning

The design and operation of an Essbase multidimensional database play a key role in achieving a well-tuned system that enables you to analyze business information efficiently. Given the size and performance volatility of multidimensional databases, developing an optimized database is critical. A detailed plan that outlines data sources, user needs, and prospective database elements can save you development and implementation time.

The planning and analysis phase involves three tasks:

● “Analyzing Source Data”

● “Identifying User Requirements”

● “Planning for Security in a Multiple User Environment”

● “Creating Database Models”

When designing a multidimensional application, consider these factors:

● How information flows within the company—who uses what data for what purposes ● The types of reporting the company does—what types of data must be included in the outline to serve user reporting needs

Note:

Defining only one database per application enables enhanced memory usage and ease of database administration. Applications that use the optional Essbase Currency Conversion module are an exception to this recommendation. Currency conversion applications generally consist of a main database and a separate currency database.

Analyzing Source Data

First, evaluate the source data to be included in the database. Think about where the data resides and how often you plan to update the database with the data. This up-front research saves time when you create the database outline and load data into the Essbase database.

Determine the scope of the database. If an organization has thousands of product families containing hundreds of thousands of products, you may want to store data values only for product families. Interview members from each user department to find out what data they process, how they process data today, and how they want to process data in the future. Carefully define reporting and analysis needs.

● How do users want to view and analyze data?

● How much detail should the database contain?

● Does the data support the desired analysis and reporting goals?

● If not, what additional data do you need and where can you find the needed data?

Determine the location of the current data.

● Where does each department currently store data?

● Is data in a form that Essbase can use?

● Do departments store data in a DB2 database on an IBM mainframe, in a relational database

on a UNIX-based server, or in a PC-based database or spreadsheet?

● Who updates the database and how frequently?

● Do the individuals who need to update data have access to the data?

Make sure that the data is ready to load into Essbase.

● Does data come from a single source or from multiple sources?

● Is data in a format that Essbase can use? For a list of valid data sources that you can load

into Essbase,

● Is all data that you want to use readily available?

Identifying User Requirements

Discuss information needs with users. Review the information they use and the reports they must generate for review by others. Determine the following requirements.

● What types of analysis do users require?

● What summary and detail levels of information do users need?

● Do some users require access to information that other users should not see?

Planning for Security in a Multiple User Environment

You must consider user information needs when you plan how to set up security permissions. End your analysis with a list of users and permissions.

Use this checklist to plan for security:

● Who are the users and what permissions should they have?

● Who should have load data permissions?

● Which users can be grouped, and as a group, given similar permissions?

Creating Database Models

Next, create a model of the database on paper. To build the model, identify the perspectives and views that are important to your business. These views translate into the dimensions of the database model.

Most businesses choose to analyze the following areas:

● Time periods

● Accounting measures

● Scenarios

● Products

● Distribution channels

● Geographical regions

● Business units

Use the following topics to help you gather information and make decisions:

● “Identifying Analysis Objectives”

● “Determining Dimensions and Members”

● “Analyzing Database Design”

Identifying Analysis Objectives

After you identify the major areas of information in a business, the next step in designing an Essbase database is deciding how the database enables data analysis:

● If analyzing by time, which time periods are needed? Does the analysis need to include only the current year or multiple years? Does the analysis need to include quarterly and monthly data? Does the analysis need to include data by season?

● If analyzing by geographical region, how do you define the regions? Do you define regions by sales territories? Do you define regions by geographical boundaries such as states and cities?

● If analyzing by product line, do you need to review data for each specific product? Can you summarize data into product classes?

Regardless of the business area, you need to determine the perspective and detail needed in the analysis. Each business area that you analyze provides a different view of the data.

Determining Dimensions and Members

You can represent each business view as a separate standard dimension in the database. If you need to analyze a business area by classification or attribute, such as by the size or color of products, you can use attribute dimensions to represent the classification views.

The dimensions that you choose determine what types of analysis you can perform on the data.

With Essbase, you can use as many dimensions as you need for analysis. A typical Essbase database contains at least seven standard dimensions (non-attribute dimensions) and many more attribute dimensions.

When you know approximately what dimensions and members you need, review the following topics and develop a tentative database design:

● “Relationships Among Dimensions”

● “Example Dimension-Member Structure”

● “Checklist for Determining Dimensions and Members”

After you determine the dimensions of the database model, choose the elements or items within the perspective of each dimension. These elements become the members of their respective dimensions. For example, a perspective of time may include the time periods that you want to analyze, such as quarters, and within quarters, months. Each quarter and month becomes a member of the dimension that you create for time. Quarters and months represent a two-level hierarchy of members and their children. Months within a quarter consolidate to a total for each quarter.

Relationships Among Dimensions

Next, consider the relationships among the business areas. The structure of an Essbase database makes it easy for users to analyze information from many different perspectives. A financial analyst, for example, may ask the following questions:

● What are sales for a particular month? How does this figure compare to sales in the same month over the last five years?

● By what percentage is profit margin increasing?

● How close are actual values to budgeted values?

In other words, the analyst may want to examine information from three different perspectives —time, account, and scenario. The sample database shown in the below figure represents these three perspectives as three dimensions, with one dimension represented along each of the three axes:

● A time dimension, which consists of the individual months Jan, Feb, and Mar and the total for Qtr1, is displayed along the X-axis.

● An accounts dimension, which consists of accounting figures such as Sales, COGS, Margin, and Margin%, is displayed along the Y-axis.

● Another dimension which provides a different point of view, such as Budget for budget values and Actual for actual values, is displayed along the Z-axis.

The cells within the cube, where the members intersect, contain the data relevant to all three intersecting members; for example, the actual sales in January.

Checklist for Determining Dimensions and Members

Use the following checklist when determining the dimensions and members of your model database:

● What are the candidates for dimensions?

● Do any of the dimensions classify or describe other dimensions? These dimensions are candidates for attribute dimensions.

● Do users want to qualify their view of a dimension? The categories by which they qualify a dimension are candidates for attribute dimensions.

● What are the candidates for members?

● How many levels does the data require?

● How does the data consolidate?

Analyzing Database Design

While the initial dimension design is still on paper, you should review the design according to a set of guidelines. The guidelines help you to fine-tune the database and leverage the multidimensional technology. The guidelines are processes or questions that help you achieve an efficient design and meet consolidation and calculation goals.

The number of members needed to describe a potential data point should determine the number of dimensions. If you are not sure that you should delete a dimension, keep it and apply more analysis rules until you feel confident about deleting or keeping it.

Use the information in the following topics to analyze and improve your database design:

● “Dense and Sparse Dimensions”

● “Standard and Attribute Dimensions”

● “Dimension Combinations”

● “Repetition in Outlines”

● “Interdimensional Irrelevance”

● “Reasons to Split Databases ”

● “Checklist to Analyze the Database Design”

Dense and Sparse Dimensions

Which dimensions are sparse and which dense affects performance. Refer to the previous posts regarding the Sparse and Dense Dimensions

Standard and Attribute Dimensions

For simplicity, the examples in this topic show alternative arrangements for what was initially designed as two dimensions. You can apply the same logic to all combinations of dimensions. Consider the design for a company that sells products to multiple customers over multiple markets; the markets are unique to each customer:

Cust A Cust B Cust C

New York 100 N/A N/A

Illinois N/A 150 N/A

California N/A N/A 30

Cust A is only in New York, Cust B is only in Illinois, and Cust C is only in California. The company can define the data in one standard dimension:

Market

New York

Cust A

Illinois

Cust B

California

Cust C

However, if you look at a larger sampling of data, you may see that there can be many customers in each market. Cust A and Cust E are in New York; Cust B, Cust M, and Cust P are in Illinois; Cust C and Cust F are in California. In this situation, the company typically defines the large dimension, Customer, as a standard dimension and the smaller dimension, Market, as an attribute dimension. The company associates the members of the Market dimension as attributes of the members of the Customer dimension. The members of the Market dimension describe locations of the customers.

Customer (Standard dimension)

Cust A (Attribute:New York)

Cust B (Attribute:Illinois)

Cust C (Attribute:California)

Cust E (Attribute:New York)

Cust F (Attribute:California)

Cust M (Attribute:Illinois)

Cust P (Attribute:Illinois)

Market (Attribute dimension)

New York

Illinois

California

Consider another situation. Again, the company sells products to multiple customers over multiple markets but, the company can ship to a customer that has locations in different markets:

Cust A Cust B Cust C

New York 100 75 N/A

Illinois N/A 150 N/A

California 150 N/A 30

Cust A is in New York and California. Cust B is in New York and Illinois. Cust C is only in California. Using an attribute dimension does not work in this situation; a customer member cannot have more than one attribute member. Therefore, the company designs the data in two standard dimensions:

Customer

Cust A

Cust B

Cust C

Market

New York

Illinois

California

Dimension Combinations

Break each combination of two dimensions into a two-dimensional matrix. For example, proposed dimensions at TBC include the following combinations:

● Year across Measures

● Year across Product

● Year across Market

● Year across Scenario

● Measures across Product

● Measures across Market

● Measures across Scenario

● Market across Product

● Market across Scenario

● Scenario across Product

● Ounces across Pkg Type

As attribute dimensions associated with the Product dimension, Ounces and Pkg Type can be considered with the Product dimension.

To help visualize each dimension, you can draw a matrix and include a few of the first generation members. The below figure shows a simplified set of matrixes for three dimensions.

For each combination of dimensions, ask three questions:

● Does it add analytic value?

● Does it add utility for reporting?

● Does it avoid an excess of unused combinations?

For each combination, the answers to the questions help determine if the combination is valid for the database. Ideally, the answers to all questions should be yes. If all answers are not yes, you should consider rearranging the data into dimensions that are more meaningful. As you work through this process, discuss information needs with users.