Loading Dates as Data

<Do not delete this text because it is a placeholder for the generated list of "main" topics when run in a browser>

Purpose

This tutorial shows you how to load dates as data to an Essbase ASO cube.

Time to Complete

Approximately 20 minutes

Overview

After enabling an Essbase outline to accept date measures, you can add them to the measures dimension and use them for analysis.

Scenario

The Hyptek corporation, manufacturer of hard disk drives, uses Essbase for sales budgeting and forecasting (BSO databases), as well as current year and historical sales analysis (ASO databases). Current year "units sold" data is loaded into an Aggregate storage (ASO) cube every month and used for standard and ad hoc reporting. Monthly data files are archived at the end of each year.

An interesting challenge has just come across your desk. Hyptek management would like you to create a cumulative-units-to-date report for all of the Roadranger products sold in North American retail channels under New Line Introduction promotions. The report should be designed to show units sold for each product in the Roadranger line since the product introduction date. The oldest product was introduced in 2003.

Because data spans such a large period of time, no single cube contains all of the data required, nor does any online relational database. You decide to build a one-off ASO cube to aggregate data from the data load files used for monthly reporting, which are available from machine-readable archives. You will load these into the ASO cube using an aggregated load.

Since the granularity of time for this project (spanning years) is so different from that of normal reporting (spanning a single month), you will create a date measure in the Essbase database to store the introduction date for each of the Roadranger products.

The final report should look something like the following figure:

Software and Hardware Requirements

The following is a list of software requirements:

Note: The HyptekAS database used in this OBE was developed for classroom use and is not available as a sample application. However, screenshots are provided for the outline sections used in this OBE.

Prerequisites

None

Creating an Application

To minimize design time, you make a copy of the HyptekAS application (already cleared of data), naming the copy HyptekRR.

.

In the Navigation panel, right-click the HyptekAS application and select Copy.

.

For the application name, enter HyptekRR.

.

Click OK.

The HyptekRR application is displayed in the Navigation panel.

.

Expand the HyptekRR application and database, then right-click Outline and select Edit.

 

.

The outline is displayed.

Keep Outline Editor open for the next topic.

Enabling Date Measures

Both text and date measures are typed measures, measures that can be associated with text and date values respectively. Both are enabled in the outline properties, accessed through Outline Editor.

.

Select the Properties tab.

.

In the Properties tab, in the Typed Measures enabled drop-down list, select true.

A confirmation message is displayed, indicating that once you enable an outline to support typed measures, it cannot be reverted to an outline that does not support typed measures.

.

Click OK.

 

.

In the Date format drop-down list, select mm-dd-yy.

All date measures in the outline will use this date format.

.

Click Verify.

A confirmation message is displayed.

.

Click OK, then click Save. Keep Outline Editor open for the next topic.

Adding a Date Measure to the Outline

In ASO cubes, date measures must be level 0 members of the measures dimension.

.

Select the Outline tab.

.

Expand the Measures dimension, then right-click Measures and select Add child.

A text box is displayed.

.

In the text box, enter Intro Date, and then press Enter.

Another text box is displayed.

.

Press Enter again to close the text editor.

Date measure Intro Date is displayed in the outline.

.

Right-click Intro Date and select Edit member properties.

Member properties for Intro Date are displayed.

.

In the Type drop-down list, select Date.

.

Click OK. Since date measures should not be aggregated, Intro Date is assigned the non-aggregation operator (^) by default.

Completing the Outline Changes and Reviewing the Input File

Due to the long time horizon for this analysis, outline members usually associated with monthly reporting are inappropriate. For example, the Fiscal Year dimension normally spans three years--insufficient for this project. Some new level 0 members need to be added to the outline. (The remaining dimensions already contain members to support the cumulative units report.)

.

Add the following level 0 members to the HyptekRR outline:

    For Period: Period Unspecified
    For Fiscal Year: FY Unspecified
    For Customer: Retail Unspecified
    For Sales Person: Retail Rep Unspecified (not shown in figure)

.

The tab-delimited input file, IntrDate.txt, contains all of the introduction dates for the Roadranger products, as shown in the following figure:

Creating a Rules File for Loading Dates

A data load rule (aka rules file) can be used to load dates as data.

.

Expand database HyptekRR, right-click Rules Files and select Create rules file.

Data Prep Editor is displayed.

.

Select File, then Open data file, then navigate to IntrDate.txt and open it.

Data is displayed in Data Prep Editor.

.

Widen the column for Field1.

.

Select View, then Data load fields.

Data load fields are displayed.

.

Select Options, then Data source properties.

.

In the Delimiter tab, select the field delimiter for the data file, in this example Tab.

.

Select the Header tab.

.

Select the number of header lines to be skipped during the data load, in this example, 1, and then click OK.

.

Click the column header for Field1.

.

Select Field, then Properties.

The Field Properties dialog box is displayed.

.

In the Data Load Properties tab, expand the dimension list to reveal Product, then double-click Product.

Product is displayed in the Field name.

.

Click OK.

The dimension name (Product) is displayed in the field header.

.

Click the column header for Field2.

.

Repeat steps 10 and 11 for Intro Date.

.

Click OK.

The member name (Intro Date) is displayed in the field header.

.

Select Options, then Validate.

.

Click OK, then select File, and then Save.

.

Save the rules file on Essbase Server as IntrDate.rul.

Loading the Dates

You can use the data file and the rules file, along with specific data load settings, to load dates as data.

.

Right-click database HyptekRR, then select Load data.

.

The Data Load dialog box is displayed.

.

In the Data load values drop-down list, select Overwrite existing values.

.

Select Aggregate Use Last.

.

Select Find Data File, navigate to data file IntrDate.txt, then select it and click OK.

.

Select Find Rules File, navigate to rules file IntrDate.rul, then select it and click OK.

.

Confirm data load values.

.

Click OK.

A success message is displayed in the Status column.

.

Click Close.

Viewing Results

You can use Smart View to view dates loaded as data.

.

Open a new Excel workbook.

.

Select Smart View, then Open, and then Smart View Panel.

.

In the Smart View Panel, select Shared Connections.

.

Provide authentication, then navigate to the HyptekRR database, right-click and select Connect.

.

Right-click database HyptekRR and select Ad hoc analysis.

.

Optional: Using the Move function in the POV (Point of View) drop-down list, position the POV box inside the worksheet.

The default ad hoc analysis worksheet is displayed.

.

Adjust the POV to match the input specifications, then produce a report showing Roadranger products by Intro Date.

.

After loading Units data (steps not shown), add a column for Units and click Refresh.

Intro Date and Units for each Roadranger product are displayed in the worksheet.

Summary

In this tutorial, you learned how to enable an Essbase outline to accept dates as data and to load dates using a data load rule and an input file.

Resources

Hardware and Software Engineered to Work Together About Oracle | Oracle and Sun | Oracle RSS Feeds | Subscribe | Careers | Contact Us | Site Maps | Legal Notices | Terms of Use | Your Privacy Rights