TM1 Forum

I work for an animal pharma company and we have divisions as Cattle, Equine, Pet and Swine etc.
I have a product that is assigned to the Equine division but it was sold by the Pet division.
How do I get Net Sales to reflect the selling division of Pet and not the assigned division of Equine?

Would the selling division be an alias to the division dimension or would it be a different Net Sales Measure in the cube?

Not sure where to start. Would someone just get me started in the right direction?

Much appreciated, ged

A lot depends on the SKU structure and whether or not go down to the SKU level in your Product dimension. If you do, and there are separate SKUs for that same product (so that one SKU is the one sold by Equine and the other is sold by Pet) then the problem can be solved by having two trees in the Product dimension, one for Manufacturing and one for Selling. However, If that is not the case then you would best be served by having two Product dimensions, one for Manufacturing and one for Selling. You would post the sales in the SKU or division in the Manufacturing dimension in the division or SKU it was manufactured in and in the Selling dimension in the SKU or division it was sold in. In most cases they would be the same and in some cases they would be different. This would let you report either based on where it was manufactured or where it was sold. Just my two cents based on the very limited information you have given us.

tomok wrote:A lot depends on the SKU structure and whether or not go down to the SKU level in your Product dimension. If you do, and there are separate SKUs for that same product (so that one SKU is the one sold by Equine and the other is sold by Pet) then the problem can be solved by having two trees in the Product dimension, one for Manufacturing and one for Selling. However, If that is not the case then you would best be served by having two Product dimensions...

That sounds a bit painful in terms of maintenance and potentially the sparsity of data in the cube (not to mention whether such alternate hierarchies are available in the first place.) I don't think this is necessary, I would just have a single product dimension with the rollups to the brands and ultimately divisions that they "should" roll up to but I would add an additional dimension to the cube "Selling Division" with N elements of just the divisions themselves (Cattle, Equine, Pet and Swine etc.). Then you can easily do reporting by sales division (just select "total products" on the product dimension and the division on the sales division dimension) or by divisional product hierarchy and you can also easily do uncomplicated leakage analysis by comparing product structure "division" sales vs. what each division actually sells.

Hi Ged

I may be way off here ... I assume you are wanting an analysis cube for Net Sales data, so the measure is "Net Sales" the product dim will be the product hierarchy and the difficulty is that the product hierarchy including division
won't show the exceptions of products sold which are unassigned (sold by another division).

Could you not just add an attribute of Sales_Division (on the N level SKU elements within the Product dimension) and mapp the data on load.

Possibly have 2 measures MD_NetSales SD_NetSales if you wanted to show Sales by manufactured Division.
or
Include Divisions in the Product hierarchy and then the Division dimension is only used to see the individual Sales Division and you can see the assigned Division in the product dimension.

Given the above its a fairly simple cube, 1 Division dimension and a Product dimension.

Thanks you everyone for your postings and helpful information.
To clarify, I have one SKU for the product but in the Sales_Fct table I have a key to link the sales to the credit division and a key to the assigned division.

From your comments I asked the client if they needed to see both the credit division and the assigned division and I was that they only need the credit division so that eliminates the need for two Division dimensions or two Net Sales measures. I just need to locate where in the code where the assigned division is being loaded and hopefully change it to the Credit division.

Thanks everyone for your suggestions they are helpful especially when your lost to start with.
ged