Granularity – Why you need it and why you may not.
Apptio is an amazing system and at its heart is a highly optimised system that has a high performance transactional engine.
When you build a model you are essentially moving money or values via metrics between the modelled tables via allocation lines. The amount of rows you have in the data set that backs the Modelled table is directly proportional to the amount of work that Apptio has to do to calculate the model.
The more granularity in an object, the more rows Apptio has to calculate. Now in small models this does not cause a noticeable issue since Apptio is exceptionally quick at calculating, but when your model consists of many tables and the relationships are less than optimal this can add up and increase your calculation times as Apptio has to build out bigger and bigger tables that will, by their nature take longer to calculate.
But I need All that granularity
When you are building out reports there are times when you need lots of details on the reports to give the users of that report the information they need to make the decisions that they want. Balancing this requirement vs performance of the system can sometimes be an art form, but the information in this article is designed to help you work out a way to give your customers what they need, still keeping the information in the reports, but allow Apptio to calculate in the most efficient manner.
Drills
A drill is where a report is taking the data at one object and showing details from another object that shows the contributory costs. For example a report that shows you Applications and the various proportions of IT Resource Tower costs that makes up each application.
In this article we will use the example of End User Device, typically business unit owners want to see lots of detail in End user devices. Given the number of devices in large companies can really add up. This is an area where it is very difficult to manage a large and distributed fleet of devices and management will want to see detail about who has what device.
The temptation is to load all of your end user devices into the End User Device object at the granularity of each device. Now while this will give you the ability to granularly apply costs, see lots of detail, easily produce reports showing all this detail, it does come at a cost. While a model is small this will not affect the calculation times, but as the model scales then this can cause an impact.
But I really need all that detail for my reports
The key to getting this is to break down what you need and address the granularity you need.
1st you will need to know what costs you are going to apply.
2nd where are these costs going
3rd what information do I need on my reports.
Taking End User Devices as an example.
What Costs we are going to Apply
The issue with end user devices is that there can be a number of costs that vary and you will need to ascertain if these variance in costs is something that makes a difference to decisions. For example you may purchase many different models of laptops but the cost difference between the different models is not financially significant enough as it’s something that is amortised across several years. Alternately we may have expensive software that we want to include in the total End User Device cost and we want to show this back to the department in question.
These are questions only you can answer for your specific requirement, but we will consider here that we have devices that have a cost weighting dependant on the device “T-Shirt” size and if we have the software installed.
Where are these costs going?
Typically end user devices will send their costs to Business Services. It is not uncommon for there to be limited services for these devices and depending on the granularity there you may only have 3 or 4 services, for example; Mobile Device, Laptop, Desktop, Desktop – Trading platform.
Why does this matter? When Apptio allocates costs and you build a report from an object to another object, once the costs have gone through another object there is a removal of information if you do not include it in the allocation. In this example we could increase granularity at the Business Services Object to include additional variations of End User Devices or even in what department these devices are used and this would keep the information within the drill, but if you don’t have this information then your drill will show all of the End User devices since they all contribute to the cost.
What information do I need on the reports?
This is the tricky bit. We want to be able to show what department has what device and have the name of the owner in a report, but we want to reduce the granularity. How do we have the granularity and remove it at the same time?
The key to this is understanding how the costs move through the model and if we need the granularity to do that.
Below we have a custom cost model built out. Note this is substantially cut down from a standard model for simplicity.
We are bringing in costs at IT Resource Towers and allocating them just to End User Devices. We are letting the Software allocations fall out as we are just concentrating on the Devices themselves. It may be that your business wants to have all of the costs distributed to each device, however that is beyond the scope of this document.
You can see from the above Model Diagram that there are no allocation lines between End User Device Master Data and End User Device Detail.
We have taken the data in from the End User device as a level of granularity that identifies each device individually.
Obviously this isn’t real data so the End User Device column is the equivalent of the Device’s Unique ID.
Because the next object up from End User device is Business Services and we only have 5 Services for this example we effectively only need those services in End User Device Master Data. The reason for this is that the detail we need from IT Resource Towers is the same or less granularity than services as shown below.
As you can see there we have Metadata and this relates to the type of device we are going to allocate these costs to. Just to repeat, the metadata here is going to be at the same level of granularity as the services, eg Desktop Trader will be going to the Desktop Trading Platform service.
So we need to group down the data by the appropriate columns. In this instance we can use the Service column that is in the End User Device Data.
And you end up with the following data.
This is going to feed our End User Devices Master Data.
Now you are moving money from the IT Resource Towers to the appropriate type of device as identified in the IT Resource Towers. Notice how some of the departments are showing the 3 dots indicating a {Various} as we have grouped down the data. If we were to report on this then we would not be able to get data about the departments out of the system and this is where having a detail object comes in.
We need a detail object to report on.
Take the original End User Device table we uploaded and create a transform called End User device Detail. We will need a column in this to work out the total costs of all the device types contained in the Metadata column. We will need this to work out the proportion of the costs we are going to extract from the End User Device Master Modelled data table.
We will create a a formula step and in there create a numeric column called Sum of Device Type with a formula of SumIf(Metadata,Metadata,Cost Reference)
Now we will end up with a table that looks like the following
We will create a model step at the individual End User Device level by using the End User Device as the Object ID
We should end up with something that looks like this
Notice how the value of the Cost metric is 0. This is fine and what we want as we do not want to allocate costs from IT Resource Towers or End User Devices Master Data to this object.
Building the report
The trick with this report is to take the cost reference column and work out a relationship between the cost of the individual device and the allocations. Since we already have the sum of the Cost Reference we created in the End User Device Detail table we can use this to work out a multiplication factor to apply to the rows in the End User Device Master Data table. Weighting is taking the sum of all the same type of allocation and divide the row’s cost reference column by that sum.
To simplify the mathematics
Cost Device ID Device Cost Total of Device Costs Multiplication factor
100 Device 1 4 10 0.4
Device 2 6 10 0.6
If we take the Multiplication factor and multiply this by the 100 cost we will get a distribution of 40 and 60. This of course adds up to the total 100
In Apptio we need to pick up the values of the Cost Metric from the End User Device Master Data that matches the allocation from IT Resource Towers and then multiply them by the rows Cost Reference divided by sum of all the Cost reference for each allocation.
In the report Table below we have included the following columns
End User device
Metadata
Department
Cost Reference
For reporting clarity I have brought in the following in the Value section of the Ad Hoc Component Configuration. You would hide these columns or create them in the transform.
Value of Costs = Sum of Device Type
Weighting Value = Cost Reference / Sum of Device Type
Cost from End User Device =LookupObjectUnitValue(End User Device Master Data,Cost,End User Device Detail.Metadata,End User Device Master Data.Metadata)
Individual Cost of Device =Weighting Value*Cost From End User Device
Breaking Down the LookupObjectUnitValue() function
As we are using the same data some of the columns in the function have the same name and this is a little confusing, however I will explain below.
=LookupObjectUnitValue(End User Device Master Data,Cost,End User Device Detail.Metadata,End User Device Master Data.Metadata)
End User Device Master Data, is the data set that we are looking into to get the costs. This is part of our main model.
Cost, This is the metric that we want to capture
End User Device Detail.Metadata, This is the column who’s value we are going to use when we look in the table we are going to get the costs from
End User Device Master Data.Metadata, This is the column in the Object that we will be matching the column in our detail report to and using that match to grab the Cost Metric value.
Since this is a more advanced concept I will assume you can create AdHoc table reports already and are familiar with adding additional columns via Inserting Formula Columns
The above Sample report has 2 tables in 2 Groups. The lower table is a drill report from Business Units to the Services Objects so we can see what Services the Business Unit is consuming and we can validate the costs that we are seeing in that portion of the model.
The Top table is the detail table which is not part of the Model and is created from a stand alone object.
Here we have filtered the Detailed End User device List by the Department Slicer set to Trading and we can see the results below.
You can see that the Cost Reference column is giving us a different distribution of the cost from the main model for the individual items of Mobile 5, Mobile 6 and Mobile 7 & 8. So we are demonstrating the different costs to someone interested in the individual amounts for their department at a device level and we have the overall service cost available to us in the bottom table.
You can see by using this method we have reduced the granularity in the main model down to what we need to move the costs to the appropriate service, utilised the detail to give us allocations from the Services via Service Allocations Direct and then in to Business Units and been able to produce a report that shows the detail down to individual items in the list.
#CostingStandard(CT-Foundation)