Tonnage calculations

[petperson]

I need to create metrics that provide tonnage calculations in pounds. The calculation will be unit sales qty * content. Content is defined as a numeric (ie 20) and contents_units is defined as pounds, kg, ounces, etc. I need to uses both 'content' and 'contents_units' in this calculation in order be able to calculate the equivalent pounds for an item with a content unit other than pounds. I'm confused as to how to do this. Any suggestions to get me started?? thanks!

 

[entaroadun]

There are several ways to do this. I think this is the most straightforward:

I assume that unit sales qty, content, and content_units are already defined as facts and sit on the same fact table.

Create a metric:
[tt]
ApplyAgg("sum(#0 *
(CASE
WHEN #2 = 'Pounds' THEN #1
WHEN #2 = 'kg' THEN #1 * <kg to pound factor>
ELSE #1 * <default factor>
END)
)&quot;,
[unit sales qty],
[content],
[content_units])
[/tt]

Insert WHEN clauses into the CASE statement for each conversion. The ELSE clause is just in case the content_units fact is NULL or doesn't match any of the defined cases.

Set the metric at ReportLevel (standard/standard).

Let us know if you need this performance tuned. We can figure out a more efficient solution.

 

[petperson]

Well, actually the problem is that sales_qty comes from a sales fact table, and the contents and content units come from a SKU dimension table. Could I duplicate this case statement using a compound metric?

 

[petperson]

Contents is a numeric field which can be a fact, but contents units is a char field (refers to 'Pound', 'KG', etc). Thanks...

 

[entaroadun]

In that case, is this the structure of your logical tables?

Sales Fact Table
1. Sales Quantity (Fact)
2. SKU (Attribute)

SKU Dimension Table
1. SKU (Attribute)
2. Contents (Attribute - 1-M Parent of SKU)
3. Units (Attribute - 1-M Parent of SKU)

There are several possible implementations, but I prefer this:

Create a fact named [Contents in Pounds] with fact expression:
[tt]
ApplySimple(
&quot;(select [contents] *
(CASE
WHEN [contents_units] = 'kg'
THEN <kg to pound factor>
WHEN [contents_units] = 'tons'
THEN <ton to pound factor>
...
ELSE <default factor>
END)
from [SKU Table]
where [SKU Field] = #0)&quot;,[SKU Field])
[/tt]

Bind this fact expression to the Sales Fact table.

This fact pulls the contents in pounds for each SKU into the Sales Fact table. This way, you can create a metric that does a straightforward:

Sum([Sales Quantity] * [Contents in Pounds])


BTW, just because a field isn't numeric doesn't mean that it can't be a fact. The core of MSTR Architect is just a SQL generation engine. The goal is getting MSTR to generate the SQL you want, regardless of any data warehousing definitions or rules.

 

[petperson]

thank you entaroadun! that worked perfectly. I appreciate the help as I was struggling with this.