Transform Pipeline Best Practices

Document created by AWestlake@apptio.com Employee on Jul 19, 2016Last modified by dkelly@apptio.com on Oct 8, 2018
Version 34Show Document
  • View in full screen mode

NEW

No new issues this week.

General

  • Pay attention to sequence of steps. You can now move steps around in the transform pipeline which means you need to pay attention to the sequence. As an example, if you create a formula that is used in a date partition, the Formula step must be sequenced before the Date Partition step.

 

 

Formulas

  • Object identifiers. Don't build synthetic columns to support the object identifier. You can specify multiple columns now in the object identifier. If there is an existing Out-of-the-Box identifier, use that.
  • You can have two Formula steps in the transform pipeline. This occurs in cases where you have a Formula step where the formulas are needed for a subsequent step (such as a Date Partition) but then have more formulas based on the results of the step in between (such as the new Amount column created as a result of the Date Partition).
  • Two Formula steps may be combined if the intermediate step is removed. For example, you may have a Formula step, then a Date Partition step, and then a Formula step. If you remove the Date Partition step, the two Formula steps are merged together.
  • Use the Evaluation Order tab to sequence the order of evaluation of your formulas.
  • If you need to break formulas into two separate steps, you have to have a blank step between them.  See screenshot below
  • Duplicate Rows Using SplitEx and Delimiter. Instead of supplying a delimiter to activate "Duplicate Rows using Split", use =SPLITEX(Column,Delimiter) to create a new column with replicated rows.

 

Multiple Formula in Pipeline:

 

Join

  • Use the Join step for:
    • Allocation weighting
    • Manual joins in allocations
    • Reporting
    • Troubleshooting
  • Mismatch data identification. If the customer is looking for mismatches in data (ex. something exists in one table but not another), create a join between the two tables of interest. You can then create a report that highlights the mistakes (i.e. null value in one table but not the other). The join provides more flexibility (as opposed to a LOOKUP) because you can pull in columns from either joined table in the report.
  • Add join steps out of the table you are allocating values from. This provides you a sustainable troubleshooting step for your allocations.
  • Add join steps to help with custom reporting. This enables you to report from joined tables in your custom reports.
  • You cannot use the Join step for data structure manipulation (such as appending to a master data set).
  • The join step is automatically added based on inference links for upgraded customers. You may not need all of the join steps long term.

 

Transforms

  • Use data and joins in allocations. In V11, you had to create lots of transforms to pivot, group, filter, allocate, and so forth. In V12, you should limit the number of transforms you build. For example, if you're thinking of building a transform for an allocation, you don't need to. You can use the data and joins in the allocation instead.

 

Filtering Your View

  • If you apply a filter while working in the transform pipeline, that filter will remain applied until you close the document.

 

Append

  • You can control where you place the Append step in the pipeline but you cannot append into a Formula column, even if the Formulas step is after the Append step.

 

Unpivot

  • None defined yet

 

Date Partition

  • Date partition automatically pulls in column names with Months, Periods and/or Years in the header.

 

Group

  • None defined yet

 

Filter

  • Filtering values not equal to zero can fail if a group step has been added that groups non-zero numbers which net to zero value in the resulting grouped row (see BIIT-44042 for details).  In such cases apply a formula step after the grouping and before the filter step which applies the Round function or similar to force Apptio to look at the result vs. the underlying data when filtering.  Be sure to round to an appropriate number of decimal places given how the data is being used.

 

Hide and Rename

  • None defined yet

 

Model

  • None defined yet

 

Pivot

  • Use pivot to convert data captured in rows into columns headers. Be advised that any data within column headers that are not included as part of the pivot (Row, Column or Value) will no longer be available.

 

Remove Duplicates

  • None defined yet

Flatten Hierarchy

  • None defined yet
11 people found this helpful

Attachments

    Outcomes