Essbase® Analytic Services Database Administrator's Guide | | Update Contents | Previous | Next | Print | ? | |
Information Map | |
To implement a multidimensional database, first you install Essbase Analytic Services, 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, see Deciding Whether to Partition a Database.
Using a case study, this chapter provides an overview of the database planning process and discusses working rules that you can follow to design a single-server, multidimensional database solution for your organization. For detailed information about building applications and databases, see Creating Applications and Databases.
Note: The information in this chapter is designed for block storage databases. Some of the information is not relevant to aggregate storage databases. For detailed information on the differences between aggregate and block storage, see Comparison of Aggregate and Block Storage.
This chapter includes the following topics:
As illustrated in Figure 36, designing an application is a cyclic process that moves from a planning stage to a verification stage.
Figure 36: The Database Design Cycle
The database design process includes the following basic steps:
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. See Analyzing and Planning.
The outline determines the structure of the database-what information is stored and how different pieces of information relate to one another. See Drafting Outlines.
How you meet system requirements and define system parameters affects the efficiency and performance of the database. See Checking System Requirements.
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. See Loading Test Data.
You test outline consolidations and write and test formulas and calculation scripts for specialized calculations. See Defining Calculations.
Users access data through print and online reports and spreadsheets or on the World Wide Web. If you plan to provide predefined reports to users, you design report layouts and run reports. See Defining Reports.
You want to ensure that the database satisfies your user goals. You must solicit and carefully consider the opinions of users. See Verifying the Design.
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 Analytic Services database. The examples follow a variation of the Sample Basic application that is included with the Analytic Services 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 Analytic Services 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.
The design and operation of an Analytic Services multidimensional database plays 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:
When designing a multidimensional application, consider these factors:
Note: The best practices recommendation is to define only one database per application. There are several reasons for this recommendation, including enhanced memory usage and ease of database administration. Applications that use the optional Analytic Services currency conversion module are an exception to this recommendation. Currency conversion applications generally consist of a main database and a separate currency database (see Designing and Building Currency Conversion Applications).
First, you need to evaluate the source data that you want to include 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 Analytic Services 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.
Determine the location of the current data.
Make sure that the data is ready to load into Analytic Services.
Be sure to discuss information needs with users. Review the information they use and the reports they must generate for review by others. Determine the following requirements.
The time to think about the type of security permissions you plan to issue for an Analytic Services database is when you consider user information needs. End your analysis with a list of users and permissions.
Use this checklist to plan for security:
See Managing Security for Users and Applications for information about assigning user permissions.
You are now ready to 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:
Use the following topics to help you gather information and make decisions:
After you identify the major areas of information in a business, the next step in designing an Analytic Services database is deciding how the database enables data analysis:
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.
You can represent each of the business views 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 Analytic Services, you can use as many dimensions as you need for analysis. A typical Analytic Services database contains at least seven standard dimensions (non-attribute dimensions) and many more attribute dimensions.
When you have an idea of what dimensions and members you need, review the following topics and develop a tentative database design:
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.
Next, consider the relationships among the business areas. The structure of an Analytic Services database makes it easy for users to analyze information from many different perspectives. A financial analyst, for example, may ask the following questions:
In other words, the analyst may want to examine information from three different perspectives-time, account, and scenario. The sample database shown in Figure 37 represents these three perspectives as three dimensions, with one dimension represented along each of the three axes:
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.
Table 2 shows a summary of the TBC business areas that the planner determined would be dimensions. The dimensions represent the major business areas to be analyzed. The planner created three columns, with the dimensions in the left column and members in the two right columns. The members in column 3 are subcategories of the members in column 2. In some cases, members in column 3 are divided into another level of subcategories; for example, the Margin of the Measures dimension is divided into Sales and COGS.
In addition the planner added two attribute dimensions to enable product analysis based on size and packaging:
Dimensions |
Members |
Child Members |
---|---|---|
Use the following checklist when determining the dimensions and members of your model database:
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.
Keep in mind that the number of members needed to describe a potential data point should determine the number of dimensions. As you analyze the design, 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, as needed, to improve your database design:
You need to decide which dimensions are sparse and which dense. These decisions affect performance. For a basic introduction, see Sparse and Dense Dimensions. For a comprehensive discussion of storage and performance, see Designing an Outline to Optimize Performance.
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. This time, 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
Break each combination of two dimensions into a two-dimensional matrix. For example, proposed dimensions at TBC (as listed in Table 2) include the following combinations:
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. Figure 38 shows a simplified set of matrixes for three dimensions.
Figure 38: Analyzing Dimensional Relationships
For each combination of dimensions, ask three questions:
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, be sure to discuss information needs with users.
The repetition of elements in an outline often indicates a need to split dimensions. Here is an example of repetition and a solution:
Repetition | No Repetition |
---|---|
Accounts |
Accounts |
Separating Budget and Actual and placing them into another dimension simplifies the outline and provides a simpler view of the budget and actual figures of the other dimensions in the database.
The left column of this table uses shared members in the Diet dimension to analyze diet beverages. You can avoid the repetition of the left column and simplify the design of the outline by creating a Diet attribute dimension, as shown in the second example.
Attribute dimensions also provide additional analytic capabilities. For a review of the advantages of using attribute dimensions, see Designing Attribute Dimensions.
Interdimensional irrelevance occurs when many members of a dimension are irrelevant across other dimensions. Analytic Services defines irrelevant data as data that Analytic Services stores only at the summary (dimension) level. In such a situation, you may be able to remove a dimension from the database and add its members to another dimension or split the model into separate databases.
For example, TBC considered analyzing salaries as a member of the Measures dimension. But salary information often proves to be irrelevant in the context of a corporate database. Most salaries are confidential and apply to specific individuals. The individual and the salary typically represent one cell, with no reason to intersect with any other dimension.
TBC considered separating employees into a separate dimension. Table 4 shows an example of how TBC analyzed the proposed Employee dimension for interdimensional irrelevance. Members of the proposed Employee dimension are compared with members of the Measures dimension. Only the Salary measure is relevant to individual employees.
|
Joe Smith |
Mary Jones |
Mike Garcia |
All Employees |
---|---|---|---|---|
As discussed in the previous topic, Interdimensional Irrelevance, TBC agreed that, in context with other dimensions, individual employees were irrelevant. They also agreed that adding an Employee dimension substantially increased database storage needs. Consequently, they decided to create a separate Human Resources (HR) database. The new HR database contains a group of related dimensions and includes salaries, benefits, insurance, and 401(k) plans.
There are many reasons for splitting a database; for example, suppose that a company maintains an organizational database that contains several international subsidiaries located in several time zones. Each subsidiary relies on time-sensitive financial calculations. You may want to split the database for groups of subsidiaries in the same time zone to ensure that financial calculations are timely. You can also use a partitioned application to separate information by subsidiary.
Use the following checklist to analyze the database design:
At this point, you can create the application and database and build the first draft of the outline in Analytic Services. The draft defines all dimensions, members, and consolidations. Use the outline to design consolidation requirements and identify where you need formulas and calculation scripts.
Note: Before you create a database and build its outline, you must create an Analytic Services application to contain it.
The TBC planners issued the following draft for a database outline. In this plan, the bold words are the dimensions-Year, Measures, Product, Market, Scenario, Pkg Type, and Ounces. Observe how TBC anticipated consolidations, calculations and formulas, and reporting requirements. The planners also used product codes rather than product names to describe products.
The following topics present a review of the basics of dimension and member properties and a discussion of how outline design affects performance:
An outline is comprised of dimensions and members. Dimensions and members have specific properties that provide access to built-in functionality. The properties of dimensions and members define the roles of the dimensions and members in the design of the multidimensional structure. These properties include the following:
For a complete list of dimension and member properties, see Setting Dimension and Member Properties.
A dimension type is a property that Analytic Services provides that adds special functionality to a dimension. The most commonly used dimension types are time, accounts, and attribute. This topic uses dimensions of the TBC database to illustrate dimension types.
Figure 39: TBC Dimensions and Related Properties
Database:Design Year (Type: time) Measures (Type: accounts) Product Market Scenario Pkg Type (Type: attribute) Ounces (Type: attribute)
Table 5 defines each Analytic Services dimension type.
Dimension Types |
Description |
---|---|
Defines the time periods for which you report and update data. You can tag only one dimension as time. The time dimension enables several accounts dimension functions, such as first and last time balances. |
|
Contains items that you want to measure, such as profit and inventory, and makes Analytic Services built-in accounting functionality available. Only one dimension can be defined as accounts. For discussion of two forms of account dimension calculation, see Accounts Dimension Calculations. |
|
Contains members that can be used to classify members of another, associated dimension. For example, the Pkg Type attribute dimension contains a member for each type of packaging, such as bottle or can, that applies to members of the Product dimension. |
|
Contains data about where business activities take place. In a country dimension, you can specify the type of currency used in each member. For example, Canada has three markets-Vancouver, Toronto, and Montreal. They use the same currency type, Canadian dollars. |
|
Separates local currency members from the base currency defined in the application. This dimension type is used only in the main database and is only for currency conversion applications. The base currency for analysis may be US dollars, and the local currency members may contain values that are based on the currency type of their region. |
With Analytic Services, you can specify data storage properties for members; data storage properties define where and when consolidations are stored. For example, by default, members are tagged as store data. Analytic Services sums the values of store data members and stores the result at the parent level.
You can change the default logic for each member by changing the data storage property tag for the member. For example, you can change a store data member to label only member. Members with the label only tag, for example, do not have data associated with them.
Table 6 describes Analytic Services data storage properties.
When you design an outline, you must position attribute dimensions at the end of the outline. You should position dense dimensions before sparse dimensions.
The position of dimensions in an outline and the storage properties of dimensions can affect two areas of performance-how quickly calculations are run and how long it takes users to retrieve information.
Use the following topics to understand performance optimization basics:
To optimize query performance, use the following guidelines when you design an outline:
The outline shown in Figure 40 is designed for optimum query performance:
Figure 40: Designing an Outline for Optimized Query Times
To optimize calculation performance, order the sparse dimensions in the outline by their number of members, starting with the dimension that contains the fewest members.
For information about factors that affect calculation performance, see Designing for Calculation Performance.
The outline shown in Figure 41 is designed for optimum calculation performance:
Figure 41: Designing an Outline for Optimized Calculation Times
Even though they contain the same dimensions, the example outlines of Figure 40 and Figure 41 are different. To determine the best outline sequence for a situation, you must prioritize the data retrieval requirements of the users against the time needed to run calculations on the database. How often do you expect to update and recalculate the database? What is the nature of user queries? What is the expected volume of user queries?
A possible workaround is initially to position the dimensions in the outline to optimize calculation. After you run the calculations, you can manually resequence the dimensions to optimize retrieval. When you save the outline after you reposition its dimensions, choose to restructure the database by index only. Before you run calculations again, remember to resequence the dimensions in the outline to optimize calculation.
After you determine the approximate number of dimensions and members in your Analytic Services database, you are ready to determine the system requirements for the database.
Before you can test calculations, consolidations, and reports, you need data in the database. During the design process, loading mocked-up data or a subset of real data provides flexibility and shortens the time required to test and analyze results.
Detailed instructions for loading data are in the following chapters:
After you run your preliminary test, if you are satisfied with your database design, test the loading of the complete set of real data with which you will populate the final database, using the test rules files if possible. This final test may reveal problems with the source data that you did not anticipate during earlier phases of the database design process.
Calculations are essential to derive certain types of data. Data that is derived from a calculation is called calculated data; basic noncalculated data is called input data.
The following topics use the Product and Measures dimensions of the TBC application to illustrate several types of common calculations that are found in many Analytic Services databases:
For details on Analytic Services calculations, see the following chapters:
When you define members of standard dimensions, Analytic Services automatically tags the members with the addition (+) consolidator, meaning that during consolidation members are added. As appropriate, you can change a member consolidation property to one of the following operators: -, *, /, %, and ~ (no consolidation).
Consolidation is the most frequently used calculation in Analytic Services. This topic uses the Product dimension to illustrate consolidations.
The TBC application has several consolidation paths:
The following topics discuss consolidation in greater detail:
Consolidation operators define how Analytic Services rolls up data for each member in a branch to the parent. For example, using the default operator (+), Analytic Services adds 100-10, 100-20, and 100-30 and stores the result in their parent, 100, as shown in Figure 42.
Figure 42: TBC Product Dimension
The Product dimension contains mostly (+), operators, which indicate that each group of members is added and rolled up to the parent. Diet has a tilde (~), which indicates that Analytic Services does not include the Diet member in the consolidation to the parent, Product. The Diet member consists entirely of members that are shared or duplicated. The TBC product management group wants to be able to isolate Diet drinks in reports, so TBC created a separate Diet member that does not impact overall consolidation.
The following topics discuss consolidation in more detail:
Analytic Services calculates the data of a branch in top-down order. For example, if you have, in order, two members tagged with an addition symbol (+) and a third member tagged with a multiplication symbol (*). Analytic Services adds the first two and multiplies the sum by the third.
Be aware that Analytic Services always begins with the top member when it consolidates, so the order and the labels of the members is very important. For an example of how Analytic Services applies operators, see Calculating Members with Different Operators.
Table 7 shows the Analytic Services consolidation operators.
Shared members also affect consolidation paths. The shared member concept enables two members with the same name to share the same data. The shared member stores a pointer to data contained in the other member, so Analytic Services stores the data only once. Shared members must be in the same dimension. Data can be shared by two or more members.
Use the following checklist to help define consolidation:
The Measures dimension is the most complex dimension in the TBC outline because it uses both time and accounts data. It also contains formulas and special tags to help Analytic Services calculate the outline. This topic discusses the formulas and tags that TBC included in the Measures dimension (the dimension tagged as accounts).
Take a moment to look closely at the Measures dimension tags defined by TBC (in Figure 43). Many of the properties of the Measures dimension are discussed in previous topics of this chapter: positive (+), negative (-), and tilde (~) consolidation operators as well as accounts and label only tags:
Figure 43: TBC Measures Dimension
This topic discusses two forms of calculations for a dimension tagged as accounts:
Note the two tags in the Measures dimension of Table 9-TB first and TB last. These tags, called time balance tags or properties, provide instructions to Analytic Services about how to calculate the data in a dimension tagged as accounts. To use the tags, you must have a dimension tagged as accounts and a dimension tagged as time. The first, last, average, and expense tags are available exclusively for use with accounts dimension members.
In the TBC Measures dimension, Opening Inventory data represents the inventory that TBC carries at the beginning of each month. The quarterly value for Opening Inventory is equal to the Opening value for the quarter. Opening Inventory requires the time balance tag, TB first.
Ending Inventory data represents the inventory that TBC carries at the end of each month. The quarterly value for Ending Inventory is equal to the ending value for the quarter. Ending Inventory requires the time balance tag, TB last. Table 8 shows the time balance tags for the accounts dimension.
Table 9 shows how consolidation in the time dimension is affected by time balance properties in the accounts dimension; details are shown only for first quarter:
Accounts -> Time |
Jan |
Feb |
Mar |
Qtr1 |
Year |
---|---|---|---|---|---|
Normally, the calculation of a parent in the time dimension is based on the consolidation and formulas of children of the parent. However, if a member in an accounts branch is marked as TB First, then any parent in the time dimension matches the member marked as TB First.
For examples of the use of time balance tags, see Setting Time Balance Properties.
One of the TBC Analytic Services requirements is the ability to perform variance reporting on actual versus budget data. The variance reporting calculation requires that any item that represents an expense to the company must have an expense reporting tag. Inventory members, Total Expense members, and the COGS member each receive an expense reporting tag for variance reporting.
Analytic Services provides two variance reporting properties-expense and non-expense. The default is non-expense. Variance reporting properties define how Analytic Services calculates the difference between actual and budget data in members with the @VAR or @VARPER function in their member formulas.
When you tag a member as expense, the @VAR function calculates Budget - Actual. For example, if the budgeted amount is $100 and the actual amount is $110, the variance is -10.
Without the expense reporting tag, the @VAR function calculates Actual - Budget. For example, if the budgeted amount is $100 and the actual amount is $110, the variance is 10.
You can define formulas to calculate relationships between members in the database outline. You can either apply the formulas to members in the outline, or you can place the formulas in a calculation script. This topic explains how TBC optimized the performance of its database by using formulas.
Functions are predefined routines that perform specialized calculations and return sets of members or sets of data values. Formulas are composed of operators and functions, as well as dimension names, member names, and numeric constants.
Analytic Services supports the following operators:
The Analytic Services functions include over 100 predefined routines to extend the calculation capabilities of Analytic Services. Analytic Services supports the following functions:
The Measures dimension uses the following formulas:
Analytic Services uses consolidation operators to calculate the Margin, Total Expenses, and Profit members. The Margin% formula uses a % operator, which means "express Margin as a percentage of Sales." The Profit% formula uses the same % operator. The Profit per Ounce formula uses a division operator (/) and a function (@ATTRIBUTEVAL) to calculate profitability by ounce for products sized in ounces.
Note: In the Profit per Ounce formula, the @NAME function is also used to process the string "Ounces" for the @ATTRIBUTEVAL function.
For a complete list of operators, functions, and syntax, see the Technical Reference. For a comprehensive discussion of how to use formulas, see Developing Formulas.
When you design the overall database calculation, you may want to define a member as a Dynamic Calc member. When you tag a member as Dynamic Calc, Analytic Services calculates the combinations of that member when you retrieve the data, instead of pre-calculating the member combinations during the regular database calculation. Dynamic calculations shorten regular database calculation time but may increase retrieval time for dynamically calculated data values.
As shown in Figure 44, the TBC Measures dimension contains several members that are tagged as Dynamic Calc-Profit, Margin, Total Expenses, Margin %, and Profit %.
Figure 44: TBC Measures Dimension, Dynamic Calc Tags
When an overall database calculation is performed, the Dynamic Calc members and their corresponding formulas are not calculated. Rather, the members are calculated when a user requests them, for example, from Spreadsheet Add-in. Analytic Services does not store the calculated values; it recalculates the values for any subsequent retrieval. However, you can choose to store dynamically calculated values after the first retrieval.
To decide when to calculate data values dynamically, consider your priorities in the following areas:
For a comprehensive discussion of dynamic calculation, see Dynamically Calculating Data Values.
In the TBC database, both Margin % and Profit % contain the label two-pass. This default label indicates that some member formulas need to be calculated twice to produce the desired value. The two-pass property works only on members of the dimension tagged as accounts and on members tagged as Dynamic Calc and Dynamic Calc and Store. The following examples illustrate why Profit % (based on the formula Profit%Sales) has a two-pass tag.
Analytic Services loads data into the system as follows:
Measures -> Year |
Jan |
Feb |
Mar |
Qtr1 |
---|---|---|---|---|
Analytic Services calculates Measures first. The data then looks as follows:
Measures -> Year |
Jan |
Feb |
Mar |
Qtr1 |
---|---|---|---|---|
Next, Analytic Services calculates the Year dimension. The data rolls up across the dimension.
Measures -> Year |
Jan |
Feb |
Mar |
Qtr1 |
---|---|---|---|---|
The result in Profit % -> Qtr1 of 30% is not correct. However, because TBC tagged Profit% as two-pass calculation, Analytic Services recalculates profit percent at each occurrence of the member Profit %. The data is then correct and is displayed as follows:
Measures -> Year |
Jan |
Feb |
Mar |
Qtr1 |
---|---|---|---|---|
Use the following checklist when you define a calculation:
Note: The triggers feature provided by Analytic Services enables efficient monitoring of data changes in a database. For more information, see Understanding Triggers Definitions. Triggers is licensed separately from Analytic Services.
To be sure the design meets user information requirements, you need to view data as users view it. Users typically view data through spreadsheets, printed reports, or reports published on the Web. There are many tools available through Hyperion and Hyperion partners for producing the reporting systems that users use.
Analytic Services provides several tools that can help you during the design process to display and format data quickly and to test whether the database design meets user needs. You can use Administration Services Console Report Script Editor to write report scripts quickly. Those familiar with spreadsheets can use the Spreadsheet Add-in or Spreadsheet Services (Spreadsheet Services requires Deployment Services).
During the design phase, check for the following things:
If you provide predesigned reports for users, now is the time to use the appropriate tool to create those reports against the test data. The reports that you design should provide information that meets your original objectives. The reports should be easy to use. They should provide the right combinations of data and the right amount of data. Reports with too many columns and rows are very hard to use. It may be better to create a number of different reports instead of one or two all-inclusive reports.
After you analyze the data and create a preliminary design, you need to check all aspects of the design with the users. You should have already checked to see if the database satisfies the users' analysis and reporting needs. Make sure that you check with the users to ensure that the database satisfies all of their goals.
Do the calculations give them the information they need? Are they able to generate reports quickly? Are they satisfied with consolidation times? In short, ask users if the database works for them.
Near the end of the design cycle, you need to test with real data. Does the outline build correctly? Does all data load? If the database fails in any area, repeat the steps of the design cycle to identify the cause of the problem.
Analytic Services provides several sources of information to help you isolate problems. Sources include application and Analytic Server logs, exception logs, and database information accessible from Administration Services. Look at documentation topics relevant to your problem; for example, topics about security, calculations, reports, or general error messages. You can also use the index of this guide to find help for solving problems. Look up such terms as troubleshooting, logs, optimizing, performance, recovery, resources, errors, and warnings.
Most likely, you will need to repeat one or more steps of the design process to arrive at the ideal database solution.
![]() |