ElastiCube for Advanced Business Scenarios

This section provides examples of more advanced scenarios, and the recommended methods for implementing the required business logic.

The examples are categorized into the following data manipulation methods: Integrating, Reformatting and Enhancing.

Integrating Data

Integrate and merge data from different sources into a single ElastiCube structure by identifying common keys between the different tables. Proper planning is important for merging the data. It’s important to avoid creating unnecessary relationships, but at the same time, make sure you don’t have any many-to-many relationships.Examples include:

  • Integrating Data: Create a common date field from multiple date sets (from multiple data sources), and still keep the ability to use each original date field individually.
  • Integrating Data: Bring in an additional data source to help analyze data from transaction systems. For example, Financial GL data will include all transactions, but may not have all the income statement or balance sheet reporting definitions.
  • Integrating Data: Look up a value from one table and bring it into another table. For example, knowing how much a marketing campaign costs versus the sales opportunity amount is an important KPI to measure.

Reformatting Data

Reformat field data to free space, and make fields more readable and usable. For example, convert a date field to numeric, or reduce the precision of real numbers. You can reformat fields within the ElastiCube using a custom SQL expression.

  • Reformatting Data: Create a date table that is represented by a numeric representation instead of a date field to improve the query performance, as well as provide more flexibility, including the ability to filter a date range.

Enhancing Data

Enhancing Data by adding attributes/records that did not exist in the original data source.

  • Enhancing Data: Derived facts are additional facts that we calculate while importing or delivering the data.
  • Enhancing Data: Align a fiscal calendar with a Gregorian calendar.
  • Enhancing Data: Use a source table to convert dates and times from different time zones into a uniform data set.
  • Enhancing Data: Convert one currency into another using custom fields and a currency exchange rate table.
  • Enhancing Data: Compare data such as sales between a current period and a past period.
  • Enhancing Data: Check open sales orders where the order has been placed, but has not yet been delivered.
  • Enhancing Data: Transactional data does not usually change, however the data that describes the associated dimensions may change. See how to manage dimensions that may be updated with new values within the data warehouse at different points in time.

.r.