Creating Time Series Measures
The ability to compare business performance with previous time periods is fundamental to understanding a business. Time comparisons allow businesses to analyze data that spans multiple time periods, providing a context for the data. Yet, as Ralph Kimball states, SQL was not designed to make comparisons over time straightforward:
”The most difficult area of data warehousing is the translation of simple business analyses into SQL. SQL was not designed with business reports in mind. SQL was really an interim language designed to allow relational table semantics to be expressed in a convenient and accessible form, and to enable researchers and early developers to proceed with building the first relational systems in the mid-1970s. How else can you explain the fact that there is no direct way in SQL to compare this year to last year?” – Ralph Kimball
The solution is to model time series data in the Oracle BI repository. This allows users to make one request for the desired result. Oracle BI Server runs multiple queries in parallel to get the results. The queries that run in the background to support the time measure are transparent to the user.
Oracle BI Server provides Ago and ToDate functions for time series comparisons. Both of these functions operate on measures. The Ago function calculates the aggregated value as of some time period shifted from the current time. For example, the Ago function can produce sales for every month of the current quarter, along with the corresponding quarter ago sales.
The ToDate function is used to aggregate a measure attribute from the beginning of a specified time period to the currently displaying time. For example, the ToDate function can calculate Month to Date sales for a given year.
You use the Expression Builder to apply the functions.
To create time series measures, perform the following steps:
 | Identify a Dimension as a Time Dimension | |
| Create a Month Ago Measure | |
| Create a Change Month Ago Measure | |
| Create a ToDate Measure | |
| Test the Time Series Measures | |
To identify a dimension as a time dimension, perform the following steps:
| 1. | In the Business Model and Mapping layer, double-click the TimesDim dimension hierarchy to open the Dimension dialog box. |
| 2. | In the Dimension dialog box, check Time Dimension. |
| 3. | Click OK to close the Dimension dialog box. |
| 4. | Expand TimesDim to the Times Detail level and double-click the Times Detail level to open the Logical Level dialog box. |
| 5. | Click the Keys tab. |
| 6. | Check the Chronological Key checkbox for Times Detail_Key. |
| 7. | Click OK to close the Logical Level dialog box. |
To create a Month Ago measure, perform the following steps:
| 1. | Right-click Sales Facts and select New Object > Logical Column to open the Logical Column dialog box. |
| 2. | Name the logical column Month Ago Dollars. |
| 3. | Check Use existing logical columns as the source. |
| 4. | Click the Expression Builder button (three dots) to open the Expression Builder. |
| 5. | Select Functions > Time Series Functions > Ago. |
| 6. | Click Insert to add the Ago function to the Expression Builder. |
| 7. | Click the first < |
| 8. | Select Logical Tables > Sales Facts and then double-click Amount Sold to add it to the expression. |
| 9. | Click the second < |
| 10. | Select Time Dimensions > TimesDim and then double-click Month to add it to the expression. |
| 11. | Click < |
| 12. | Click OK to close the Expression Builder. The formula is displayed in the Logical Column dialog box. |
| 13. | Click OK to close the Logical Column dialog box. |
To create a Change Month Ago measure, perform the following steps:
| 1. | Right-click Sales Facts and select New Object > Logical Column to open the Logical Column dialog box. |
| 2. | Name the logical column Change Month Ago Dollars. |
| 3. | Check Use existing logical columns as the source. |
| 4. | Click the Expression Builder button to open the Expression Builder. |
| 5. | Select Logical Tables > Sales Facts and then double-click Amount Sold to add it to the expression. |
| 6. | Insert a minus sign in the expression. |
| 7. | Select Logical Tables > Sales Facts and then double-click Month Ago Dollars to add it to the expression. |
| 8. | Click OK to close the Expression Builder. The formula is displayed in the Logical Column dialog box. |
| 9. | Click OK to close the Logical Column dialog box. |
To create a ToDate measure, perform the following steps:
| 1. | Right-click Sales Facts and select New Object > Logical Column. |
| 2. | Name the new logical column Month To Date Dollars. |
| 3. | Select Use existing logical columns as the source. |
| 4. | Open the Expression Builder. |
| 5. | Select Functions > Time Series Functions and double-click ToDate to insert the expression. |
| 6. | Click the first < |
| 7. | Select Logical Tables > Sales Facts and then double-click Amount Sold. |
| 8. | Click the second < |
| 9. | Select Time Dimensions > TimesDim and the double-click Year to add it to the expression. |
| 10. | Click OK to close the Expression Builder. The formula is displayed in the Logical Column dialog box. |
| 11. | Click OK to close the Logical Column dialog box. |
To test the time series measures, perform the following steps:
| 1. | In the Business Model and Mapping layer, expand the Times logical table. |
| 2. | Double-click Calendar Month Name to open the logical column dialog box. |
| 3. | Click the Set button. |
| 4. | Select Calendar Month Desc and click OK. |
| 5. | The sort order column displays in the Logical Column dialog box. The Calendar Month Name column is now automatically sorted by Calendar Month Desc whenever it is used in an Answers query. |
| 6. | Click OK to close the logical column dialog box. |
| 7. | Drag the three time series measures from the Business Model and Mapping layer to the Sales Facts presentation table. |
| 8. | Check in changes. |
| 9. | Check global consistency. Fix any errors before proceeding. |
| 10. | Save the repository. |
| 11. | Return to Answers and reload server metadata. |
| 12. | Create the following query and filter in Answers: Calendar.Calendar Month Name Sales Facts.Amount Sold Sales Facts.Month Ago Dollars Sales Facts.Change Month Ago Dollars Sales Facts.Month To Date Dollars Calendar Year is equal to / is in 2001   |
| 13. | Click Results. |
No comments:
Post a Comment