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!
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Tonnage calculations
- Thread starter petperson
- Start date
entaroadun
Programmer
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)
)",
[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.
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)
)",
[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.
- Thread starter
- #3
- Thread starter
- #4
entaroadun
Programmer
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(
"(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)",[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.
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(
"(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)",[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.
- Thread starter
- #6
- Status