I. Exordium
When working with Apptio, I'm sure many of us have problems like cost, performance…
Here I would like to share some of the experiences and case studies that I have applied to improve that. I've even implemented use cases that have helped reduce direct costs.
I have a lot more use cases to share, so if it helps, let me know in the comments, give me a like and I'll continue to describe them in detail in the next post.
Also, if my writing is flawed, please help me with the points I need to improve on.
Hope it helps you.
II. Content
1. Use simple functions instead of complex ones
1.1. Imagine you have a field that needs to define a value based on specified conditions. At first the condition seems few and fine, so you use if() or lookup() to get the values you want. But things will change when your condition is updated, and the number of conditions increases.
Examples:
- Original
- Formula:
End User Devices_Services Key = If(Service Offering="Fixed Workspace","EUD to Desktop",If(Service Offering="Mobile Workspace","EUD to Laptop",If(Service Offering="Collaborative Workspace","EUD to Tablet","No EUD Key"))).
- The original result:
- You can see that the formula is very complex and contains nested if() functions. This makes it difficult for us to understand the meaning of the formula, reducing the calculation performance. And in the future if you have more conditions to calculate the value of "End User Devices_Services Key", you will get bogged down in more nested if() and lookup() functions.
- Explain flow:
- Solution: Instead of using nested if statements that are difficult to maintain in the future, you can use the TableMatch() function.
The TableMatch function replaces long, complex IF statements. It uses a rules table to capture the information contained in the IF statements.
- First you create a table containing rules for the map values then upload this as below
- You can see that such a table form is very easy to understand, easy to add conditions. Even if 1 “End User Devices_Services Key” corresponds to many Service Offering, you just need to add that value after ",". (Note: Editable Table can be used to update the rules if you think it changes often). More details about the TableMacth() function you can refer to: TableMatch()
-Final Result:
2. Avoid Nested If() formula
2.1. If you're sure the conditions in your nested if() statements don't change and aren't so much that a table match is needed, I think you should still break up that if() statement.
-Examples:
Target Color =If(Budget>Actuals,"Green",if(Budget=Actuals,"Yellow",if(Budget<Actuals,"Red","")))
- Solutions:
Green Con = If(Budget > Actuals,"Green", “Other”)
Other Con = If(Green Con = “Other” AND Budget = Actuals, “Yellow”, “Red”)
Target Color = If(Green Con = “Green”, Green Con, Other Con)
- You can see it is not difficult to implement but it gives a great improvement in processing performance. (One more small thing: You should try to use AND instead of OR in condition definition).
3. Streamline the order of steps in transform pipelines step.
3.1. In the Apptio you can completely change the order of steps on the transform pipelines step. Pay attention to their sort order to get the best performance.
Example:
You have a table with the order of steps as shown below:
- You can see the filter step placed near the bottom of the transform pipelines. This means that system must do that upfront processing for all records. This is unnecessary and adversely affects performance.
Solution:
- If the filter step is not related to formulas and other steps, it should be sorted before those steps. So that the next steps will have to calculate fewer records.
- If the filter step involves several formulas, do those formulas first, then the filter step, then we add formulas that are not related to the filter step at the end.
- The logical arrangement of steps will avoid redundant calculations for the system and improve performance dramatically.
4.Control input Data.
4.1. Input data is not always clean and ready for use. We can use reports to identify unsatisfactory data, making it easier to plan how to deal with them.
Example: In your vendors' data, there is a lot of faulty and unsatisfactory data, for example: no Vendor Name, Vendor ID is not unique,... This affects your processing in the next steps. Accordingly, you need to find the error records and fix them as quickly as possible.
Solution: Use custom reports, create tables, formula columns to detect invalid records, use models, formulas and filters. From there we will easily manage and detect invalid records in input data.
- You can optionally add formula columns to check data with different conditions.
- Then use those columns as filters to filter out invalid records.
- With a data table containing invalid data, you can easily come up with a solution to deal with them in the fastest way.
- This report can also be used throughout the project, whenever there is a data error it will show you immediately.
4.2. Input data is not always clean and ready for use. You need some data cleaning steps before inputting them into the system.
Example: you need to input a file containing data. You just need to take out a certain ID or code, but some columns in that file contain redundant characters and sentences. So before inputting data into Apptio system, you had to clean them in excel, word, etc. (This happened to our project, and it cost a lot of money).
Solution: Apptio has ReplaceRegex() function, if we can use them skillfully, we can eliminate the tedious and costly repetitive data cleaning work.
- Example Formula: (Please allow me to hide some information.):
+) Target=ReplaceRegex({テキスト},"^.*?[A][0-9]{0,4}?(\d{4}).*","$1")
+) Final =If(ReplaceRegex(Target,"^[0-9]{1,4}$","Yes")="Yes", "A"&Target, "")
- In fact, I had to use more formulas than in this example, and it was quite difficult to implement. But if you have similar cases, why not think about solving them automatically. That helps to reduce the cost and time of tedious and repetitive tasks.
III. Result.
3.1. Lessons learned
1. Develop and communicate internal Best Practices for your TBMO
- Manage data from the first steps to achieve effective use
- Work closely with Apptio Technical Account Manager, Team members, and the data.
- Use the advantages of Apptio TBM to achieve the purpose that business is aiming for
- Technology is used to serve business
- Always thinking to optimize system performance and reduce all kinds of costs
- Always think of the simplest ways to solve problems
3.2. Achieved results.
|
Decreased the calculation time of stage build by 13 hours.
|
|
Remove data cleaning step. Off ~1200$ per month!
|
I have attached the use case overview files in the community article, hope it is useful to you.
Community post url: how-do-i-reduce-costs-and-save-time-by-using-apptio
#ApptioforAll