Ingredients:
- Vendor List – single records, all vendors
- Vendor to Multiple Apps List – only vendors going to multiple apps
Assumptions:
This recipe assumes you have a General Ledger loaded and piped to Cost Source and that the ledger contains Vendor information. Also, that you have an initial setup of your IT Resource Tower List piped to IT Resource Towers Master Data.
Steps:
- Create and load a Vendor List1
- Vendor List should include columns: Vendor ID, Vendor Name, IT Resource Tower Name, IT Resource Sub-Tower Name, and Resource Tower Metadata 1* (put your Application ID’s in Metadata 1)
- Call this Vendor List Raw and load the file
- Then create a new table in Apptio, call it Vendor List Transform and use Existing Table option as source. Select the Vendor List Raw.
- Even if you aren’t immediately transforming this list, you want to leave space for the future
- In newly created Vendor List Transform add the Map Columns step, and map to Vendor Master Data
- You may need to add Custom Column for Resource Tower Metadata 1
- Create and load Vendor to Multiple Apps List2
- Like your Vendor List, this should include the following columns (plus a Weight column): Vendor ID, Vendor Name, IT Resource Tower Name, IT Resource Sub-Tower Name, and Resource Tower Metadata 1, Weight**
- Name this dataset Vendors to Multiple Apps and set aside for later
- Add additional Transform dataset in Apptio
- Create new table and call it Vendors to ITRT Transform
- Use existing table, and select your Vendor List Raw table from earlier
- Add Pipeline Steps
- Formula, include:
- Is Multiple =Lookup(Vendor ID, Vendors to Multiple Apps, Vendor ID, Vendor ID)
- Weight =1
- Filter
- Append
- Append your Vendors to Multiple Apps dataset
- Columns should auto populate
- Append this Vendors to ITRT Transform dataset into your IT Resource Towers
- How/where you append this will depend on your setup. In best practice, you should have a Towers transform somewhere before the Master Data so append this into that Towers transform
- Make sure you add the Weight column! It’s the secret sauce here.
- Build the Allocation lines
- Cost Source to Vendors
- Data Relationship – Vendor ID=Vendor ID
- Vendors to Towers
- Weight by: Weight
- Data Relationships - Vendor ID=Vendor ID, Resource Tower Metadata 1=Resource Tower Metadata 1
- Towers to Applications
- Data Relationships – Resource Tower Metadata 1=Application ID
- This is a dish that you can enjoy immediate or put in the freezer for the future. It can serve as the hearty foundation for additional tasty treats like Services and Products. There are many variations - What's your favorite? Tell me in the comments.
Footnotes:
*There are a few different options for adding granularity to Towers such Sub-Tower Element, Metadata 1, and Metadata 2. In this case going with Metadata 1 since the Application ID doesn’t really describe ‘elements’ of the Sub-Tower.
**Towers’ Master Data also has a lot of options for values. Quantity however is somewhat related to Unit of Measure so didn’t seem appropriate for weighting values. There is also something like OID Metadata but I like to reserve this for emergencies and complicated key()s. So, I’m appending a new weight column.
1.
2.