Weighted Average Metrics 2

[MSIsam]

I am having dificulty building a weighted average metric. Here are the details:
The report will display product (prod_key) and month (Mo_key).

Table A
ATTRIBUTES
Week_Key
Prod_key
Loc_key
FACTS
Service_Lvl
Return_Sls

Table B
ATTRIBUTES
Week_Key
Prod_key
Loc_key
FACTS
Reg_Sls

Metric Formula
The sales and returns come from different tables. Here is the definition:

A.Service_Lvl*(A.Return_Sls+B.RegSls) For Each A.Loc Key
Divided By
(A.Return_Sls+B.RegSls) Total Sum

I know that the numerator needs to have the loc_key in the SELECT clause but since it is not in the report, I can't figure out how to get it there. I tried dimensionality on loc_key with and without removing the reort level dimensionality but to no avail.

I would create a compound fact for A.Service_Lvl*(A.Return_Sls+b.RegSls) so that it would calculate at the lowest level, but since the Returns and Sales come from different tables, I cannot.

I would be most grateful for any help!

 

[Z3]

I think the support can help you here, or the knowledge base - I am sure there will be some TN out there. Have you tried check the "smart total" or something like that? But default, the mstr SQL does not like to join two fact tables. A view could help in this case. Also, if you have OLAP services and you don't have a lot of loc_keys, simply leave it on the report but not displaying it. You can also try hard coding the SQL, if you somehow can get A to join B on the right keys.

 

[MSIsam]

Thanks Z3,
I will look at smart totals but the SQL generated does not even have the loc_key in it. Also, we do not have OLAP services. You mentioned hard coding the SQL. Does MSI allow custom SQL?

 

[nlim]

1 questions would help clarify the problem. Is the service level specific to each location, week and prod?

I think the answer to your problem lies in nested aggregation. ie. create one metric M1 at report and lockey level. then create another metric defined as sum(m1) but at the report level. this will give you your numerator.

 

[MSIsam]

That's It!
Thanks Nlim. I completely forgot about the ability to nest aggregations.

 

[MSIsam]

False Alarm.
I still cannot get this metric to work.
I built nested aggregation metrics for the 3 facts, then combined them in a compound metric as A*(B+C).

The first 2 passes of SQL are correct and return the data at the lowest level (week, loc and prod), but when the data is combined the SQL for the numerator is:
sum(A.Service_Lvl)*(sum(A.Return_Sls)+sum(B.RegSls))

The correct SQL should be
sum(A.Service_Lvl*(A.Return_Sls+B.RegSls) )

Basically the column calcs need to be performed before the sum. I think that this is because of the nested aggregations.

Another thing that I tried is to create the nested aggregation using the 3 facts, but this came back with the "fact does not exist at the level..." error. I think that it is because the facts are coming from different tables.

We are really getting desperate for a solution. Even though a view to combine the fact on one logical table will work, it is not an option due to the size of the tables.

 

[entaroadun]

gapinc,

Are you sure that a view wouldn't work? What is your RDBMS? No commercial RDBMS would be foolish enough to materialize the entire view for this query. It should perform OK. It is definitely the cleanest solution.

If not, then you can create a fact for Reg_Sls using this expression:

ApplySimple("(select Reg_sls from B where B.Week_key=#0 and B.Prod_key=#1 and B.Loc_key=#2)",[Week_key],[Prod_key],[Loc_key])

Then pin this expression to table A. This uses an inline subquery to bring the Reg_sls fact column into logical table A, so MSI will think it's part of the same table as Return_sls. Notice that logically, this is the EXACT SAME as a view that pre-joins the tables, except that the view may perform better, since some RDBMSs come up with crappy plans for inline subqueries.

If this metric is only for a limited set of production reports, and not for any adhoc reporting needs, then we can write an ApplyAgg metric to hack the SQL. It ain't pretty. Let us know if the view and inline subquery don't work...

 

[MSIsam]

Thanks entaroadun,
I was able to solve this issue by creating individual metrics with dimensionality at the lowest level (Prod, Week, Location) and then, instead of using the nested agg approach, I created a new metric as

Sum(ApplySimple("#0*(#1+#2)", Service_Lvl, Return_Sls, RegSls)) {~}

FYI on why we should not do this in a view. Our backend is Teradata and with V2R4, the optimizer for the SQL will spool the tables to create the view BEFORE report filtering is applied. Table B is over 100GB (1+ billion rows). Teradata V2R5 does a much better job of this, but we are not there yet.

Thanks everyone for their help with this issue. I have learned a great deal about MSI functionality from this.