TM1 Forum

Hi all,

Here's the requirement:
We require 2 sets of Bills of Materials (BOM), 1 is for standard (we call it Std BOM) and 1 is for actual (Act BOM).
Std BOM is the BOM that was first rollout during mass production. Act BOM is the actual effective BOM used for the current month.
BOM is basically a list of raw materials, sub-assemblies, components, parts and the quantity required of each item. In short, a recipe written in hierarchy.

Over a period of 12 months, we are required to keep track of the production cost of each product, and compare the cost against Std BOM to see if there was a cost up or cost down in total costs.
Keep in mind that the unit price as well as no. of quantity used of each component may change over time.

Questions:
1. Can i built the BOM hierarchy in TM1 or Is it even possible to do so? The number of components used in each product may be greater than 400~500 different items.
2. 1 of the requirement is to able to perform rolling forecast (not sure if its the correct term to use). For example, for the month September, i am able to do a price amendment of the same item but on December (due to price up on peak periods), sort of like forecasting the costs of December's production costs.
3. The raw materials/components each has different types of categories for example:
By Item Category: electronic parts, plastic parts,
By Vendor: each individual vendor that the parts are sourced from
By Product: product A, product B etc
Just to name a few. 1 of the requirement is to be able to analyze by the different categories.

I not very sure how should i start, but i expect the BOM cube to be extremely large because of the no. of products/components we have.

Questions:
1. Can i built the BOM hierarchy in TM1 or Is it even possible to do so? The number of components used in each product may be greater than 400~500 different items.
2. 1 of the requirement is to able to perform rolling forecast (not sure if its the correct term to use). For example, for the month September, i am able to do a price amendment of the same item but on December (due to price up on peak periods), sort of like forecasting the costs of December's production costs.
3. The raw materials/components each has different types of categories for example:
By Item Category: electronic parts, plastic parts,
By Vendor: each individual vendor that the parts are sourced from
By Product: product A, product B etc
Just to name a few. 1 of the requirement is to be able to analyze by the different categories.

I not very sure how should i start, but i expect the BOM cube to be extremely large because of the no. of products/components we have.

I did something very similar to this recently in generating a Demand Planning Model which took into account the BOM for various product packs calculating the current months cover and wholesale and retail value of stock & materials on hand.

1. You certainly can though you will need to be careful about changes to the product/pack (if that ever happens). You would need a product parent/consolidation per Product/Pack per Standard & Actual and additional nodes for changes to the product (Otherwise changes will affect historic data) - you would also have to maintain how they were grouped into a 'All Products' node. This sounds like it should be built automatically using a TI Process otherwise it sounds like it would be a bit of a nightmarish manual process due to the hierarchies mentioned in point 3 & 400-500 Materials.

An alternative method (which I would probably recommend in this case) is to separate Product and Materials into two separate dimensions and then use zero suppression to show just the materials for the selected product. This would induce a pretty hefty reporting requirement (for suppression) so I would recommend MDX reports for this. TM1 Perspectives (for Excel) Report Methods

2. You can do a rolling price for this, the rule logic/category is also documented on our blog but I don't want to shamelessly link/plug too often. You can do this a few ways:

• You can have an override which will set the new price for a month, the new price will instantly roll into subsequent months.
• You can have a price adjustment which will simply add to the current price producing a final price which will instantly roll into subsequent months.
• You can push the price into every month manually, possibly not what your after...

3. You should be able to do this using hierarchies.

All in all if you do a good job of structuring it the cube shouldn't end up too big... its all in the build.
Hope this helps. Feel free to contact me if you want any offline assistance.

Thanks Kyro for sharing your experience.
Thanks also for link on the different reporting format that can be built with TM1 and Excel.

About the BOM hierarchy built into cube, i have a major concern because of the BOM design we have.
Our BOM design is shown as below:
-Product
|--Sub Assembly
|--Components
|--Components
|--Sub Assembly
|--Sub Assembly
|--Components

Usually, up to a possibly 4 levels (Product -> Sub Assembly -> Sub Assembly -> Components).
User wish to see the entire BOM structure (if possible) on monthly production/purchase reports.
All sub assemblies/components can appear multiple times in each different product

Initially, i had a few ideas:
Method 1
A "Product" dimension to keep all product and a "BOM Relation" dimension to keep the entire structure of the lowest component, concatenated with a blank space in between each sub assembly/component.
E.g.:
Product SubAssy_A SubAssy_B Component_A
Product SubAssy_A SubAssy_B Component_B
Product SubAssy_A SubAssy_B Component_C
Product SubAssy_A SubAssy_C Component_A
Product SubAssy_D SubAssy_E Component_A

This design would achieve what i am trying to do but the dimension size is very huge (around 78k elements) as each relation is uniquely defined.

Method 2
This method is similar to the way you suggested, by seperating product and materials into two seperate dimensions and using zero suppression.
The only problem is for each materials, i won't be able to know what is its prior sub assembly.

I saw the sample on rolling value rules on your blog, its interesting, i think i may have found out how to apply it in my scenario.

Yeah thats a tough gig, I see your point about the unique elements and the logic behind that.
I think both methods will result in a huge number of materials elements simply due to the fact that they need to be unique to the product.

Method 1 should work for you due to the uniqueness of the materials elements. Make sure you have another dimension with just the materials once, not per product and use this in a different cube, to store price, cost and other materials information that doesn't need to be repeated per product.

Method 2 probably isn't the best option with regards to the groupings, but could still potentially work.