Saving 12 hours on calculation times sounds like a wonderful achievement. Are you sure it's all about fixing some formulas here and there?
I agree that having cyclic references would kill your system, and potentially affecting calc times as well.
Also, Tablematch is powerful, but potentially very tricky. The famous
Peter Parker Principle applies here:
with great power comes great responsibility. Also, it's usually a good idea having it on a separate Formula step.
In my TBMC20 hackathon entry (
link), I call it a
heavy hitter function. As I wrote there, I wish we had better TBMA analysis & troubleshooting tools.
------------------------------
Regards, Guillermo
------------------------------
Original Message:
Sent: 10-23-2022 21:18
From: Tran Minh Phuong
Subject: Share some experience about apptio one in the working process that I have met
I. Exordium
When working with Apptio, I'm sure many of us have problems like error, 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. Hope it helps you.
II. Content
1. Attention when use Upper and Lower function(References from other sources)
-Examples:
-
-
- ColA = Upper(A) - when the column 'A' is already uppercase
- ColA = Upper(A) - when you are comparing A to another version of A that is the same case
- ColA = Lower(A) -in the above cases
-Why is it slow?
-
-
- Because you need to grab the actual STRING representation of the data
- And then…. We have to look at EVERY character of that string (or worse)
- And then we enlarge our dictionary
-What are the alternatives?
-When you need to do this, you have to do it
-
-
-
- A few people do this when unneeded… just to be safe.
- On small data that's ok, but not on large data
2. Cylcle Detect Error:
-Examples:
-
-
- Cycle detected error when trying to lookup() between two tables :
- Table A -> Table B then I Lookup() Table B -> Table A
- When in project I have a lot of tables and it can confuses me,
- I didn't realize this so it took a long time to solve
Solution:
-
-
-
- I lookup to another table.
- Table A -> Table B and Table B -> Table C
- In case there is no other table to lookup, create a new table and copy the same data into it
- Table A -> Table B ,create table C is copy of table A then lookup Table B -> Table C
- If you know another better solution please guide me.
3.Right formula instead of search
-
- TBMO's goal was to improve formulas: more readable, faster performance.
- I performed Right function - that will faster performance in some case
+)Original
-
-
- PattyCheck = if(search("Patty", Ingredient)>0,"Patty","")
- This formula will look at every character.
+)Improved
-
- PattyCheck ==if(right(Ingredient,5)=" Patty "," Patty ","")
- This formula will look at every character. it can just grab the last five characters and do the comparison. For a large data set, this can make a significant difference
4 .Inconsequential transform pipeline steps order
- We can change steps order for specified purpose.
- 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.
- Pipeline steps order:
We can filter data before use formula. For a large data set, this can make a significant difference.
In this example fomula no need to process 788 blank record, so we should filter blank before using formulas
5 .Waste of time cleaning data before handle
-
- Input data provided has unsatisfactory data, so we need to clean the data and it takes time.
- TBMO's goal was to improve input data: Use Apptio to directly edit input data, make it more correct and ready to use.
- I used the Value() function to validate data it is value, not string. If not validate resulte may be incorrect
Example:
-Original
- Amount = Collapse Value * 1000
In the right picture all Amount is blank=Collapse Value * 1000
-
-Improved
- Amount =Value(Collapse Value) * 1000
- Use Value formula Amount will correct
6. Attention when using find function
Example:
-
- My project have one error under example:
- Fomulas: Check text "Risk" in "Risk Profile" Collumn If contain return Yes else return No
-
- But why Risk Profile = Blanks also return Yes?
The reason is because:
Find function will return blank but not 0 when find Risk Profile is blank. And CheckRisk collumn I use: CheckRisk =IF(
Find("Risk",Risk Profile)=0,"No","Yes") That's why it returns Yes
Solution:
- Change Fomulas to: CheckRisk = IF(Find("Risk",Risk Profile)>0,"Yes","No")
7. Attention when using table match function
- When you work with table match you should carefully understand how it works
-
- Table match will check all columns with the same name
- In a project, a table can have hundreds of columns, Unfortunately if a column same name but it not in the rule you want to match,then no results will be returned.
-Example:
- Product table: I just want base on Product collum to get PriceChange column value in Match table, Unfortunately Match table also exist Adress Under example is 3 collumn(But in fact can have hundreds of columns and you may not be aware of the existence of the Adress column )
- Match table: This table also have Adress collumn
-Result:
- You can see CheeseBurg have PriceChange is 1.99$ , Frie have 0.99 $... but you can not get PriceChange value(under image).
- The reason is because Product(CheeseBurger) + Adress(North) in Product table not match Product(*CheeseBurg*) + Adress(Calvin District Los Angeles State) in Match table
Solution:
- You can change name column "Adress" to "Location" in Product table because I just want base on Product collum to get PriceChange column value in Match table to Product table.In this image under I have value of PriceChange collum after change Adress to Location name column on Product table.
III. Result.
3.1 Lessons learned
-
- 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. 4. Use the advantages of Apptio TBM to achieve the purpose that business is aiming for 5. 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 12 hours.
#ApptioforAll