multi level ratio

system · May 24, 2011, 11:25pm

Hey guys, Really could use some help here.

Lets say i have the following columns in my fact table

year_wk_number (ie 201101, 201102, 201103)
Market (Toyota, Nissan, Lexus)
Product (Celica, Camery, Altima, 300rx)
AMT (amount of cars sold)

There can be multiple products in a market. In the above example the products Celica and Camery belong to the Toyota Market, Altima belongs to Nissan and 300rx goes with Lexus. what i want to do is create a measure that calculates the market share of a specific product.

The formula for this would be

Market_Share = sum(AMT) product level/ sum(AMT) Market Level
Has anyone been able to do this?

mithu81 (BOB member since 2010-03-04)

system · May 25, 2011, 5:25am

Create two universe objects using database analytical functions along the following lines and use them in your report.

SUM(AMT) over (partition by Market order by Market)

SUM(AMT) over (partition by Product order by Product)

Calculate the ratio at the report level.

Does that help ?

BO_Chief (BOB member since 2004-06-06)

system · May 25, 2011, 1:25pm

thanks BO_CHIEF,

Do these objects have to be database delegated?

mithu81 (BOB member since 2010-03-04)

system · May 25, 2011, 1:37pm

ok so I created an object for market_total,

here is the code i used

sum(CDW_BI_SL.ADS_AGG_RX_PRSCR_NORM_M.trx_dol) over (partition by  CDW_BI_SL.ADS_AGG_RX_PRSCR_NORM_M.mkt_grp_sk order by CDW_BI_SL.ADS_AGG_RX_PRSCR_NORM_M.mkt_grp_sk)

it Parses fine in designer but when I try to run a report I get the following error:

any suggestions on how to fix this?

mithu81 (BOB member since 2010-03-04)

system · May 25, 2011, 2:02pm

ok so here is the sql generated for that query is

SELECT DISTINCT
  CDW_BI_SL.ADS_DIM_MKT_GRP.MKT_GRP_DESC,
  sum(CDW_BI_SL.ADS_AGG_RX_PRSCR_NORM_M.trx_dol) over (partition by  CDW_BI_SL.ADS_DIM_MKT_GRP.MKT_GRP_DESC order by CDW_BI_SL.ADS_DIM_MKT_GRP.MKT_GRP_DESC)
FROM
  CDW_BI_SL.ADS_AGG_RX_PRSCR_NORM_M,
  CDW_BI_SL.ADS_DIM_MKT_GRP
WHERE
  ( CDW_BI_SL.ADS_DIM_MKT_GRP.MKT_GRP_SK=CDW_BI_SL.ADS_AGG_RX_PRSCR_NORM_M.mkt_grp_sk
  )
GROUP BY
  1

I noticed that if i remove the group by it works fine. How do we do this in designer where only if this measure is included do not include a group by. Not sure if that is the way to do it. Any suggestions?

mithu81 (BOB member since 2010-03-04)

system · May 26, 2011, 2:59am

Yes, that’s correct. When you create objects using database analytical functions in Designer it should not generate GROUP BY clause. You have to check the databasexx.prm file settings on your machine and it should have something along the following lines.

Default location for .PRM file will be here, [drive]:\BusinessObjects31\Business Objects\BusinessObjects Enterprise 12.0\win32_x86\dataAccess\connectionServer\teradata

The TERADATAEN.PRM or TERADATA.PRM file must contain these 2 parameters in the [RDBMS] (general) section:

OLAP_CLAUSE=QUALIFY
RISQL_FUNCTIONS= list of analytical functions used… from the database


[RDBMS]
(GENERAL)

OVER_CLAUSE=QUALIFY
RISQL_FUNCTIONS= RANK, CSUM, MAVG, 
MDIFF,MLINREG,MSUM,QUANTILE

It should also contain the following definition for the RANK function.


<Function Group="False" ID="Rank_over" InMacro="False" Type="Numeric">
            <Arguments>
                <Argument Type="All"></Argument>
                <Argument Type="All"></Argument>
            </Arguments>
            <SQL>RANK() OVER (PARTITION BY ($1) ORDER BY ($2))</SQL>
</Function>

If you see above Function Group is set to False this will not generate the GROUP BY clause. After it generates the correct SQL, you have to make the same changes on the Server or copy your .PRM file to the BOE Server.

Hope that helps.

BO_Chief (BOB member since 2004-06-06)

system · May 26, 2011, 1:46pm

Thanks again BO_CHIEF,

I will try to make this change today. I have a couple more questions for you. I see that in the RISQL_FUNCTIONS line you have CSUM. Should i be using CSUM in my select statement for my object

I am refereing to this line:

sum(CDW_BI_SL.ADS_AGG_RX_PRSCR_NORM_M.trx_dol) over (partition by  CDW_BI_SL.ADS_DIM_MKT_GRP.MKT_GRP_DESC order by CDW_BI_SL.ADS_DIM_MKT_GRP.MKT_GRP_DESC)

should this be CSUM instead of sum? here is an example with CSUM

CSUM(CDW_BI_SL.ADS_AGG_RX_PRSCR_NORM_M.trx_dol) over (partition by  CDW_BI_SL.ADS_DIM_MKT_GRP.MKT_GRP_DESC order by CDW_BI_SL.ADS_DIM_MKT_GRP.MKT_GRP_DESC)

and finally i was wondering what happens when i include multiple measures into this query. Will it move the Market_Total object into its own query and if not how does removing the “group by” affect the rest of the measures?

Thanks again, I truly appriciate your help.

mithu81 (BOB member since 2010-03-04)

system · May 26, 2011, 10:36pm

No, Don’t use it .

What version of database are you using for Teradata .?
Check this link, Ordered Analytical functions in PDF:
http://www.teradataforum.com/teradata_pdf/b035-1101-122a_5.pdf

BO_Chief (BOB member since 2004-06-06)

system · June 7, 2011, 8:20pm

Thanks BO_CHIEF,

I really am greatefull for your help.

here is a what my teradata.prm file has. I can not find the OVER_CLAUSE statement.

Please let me know what i am missing.

Thanks again,

<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE DBParameters SYSTEM "../dbparameters.dtd"><DBParameters>
    <Configuration>
        <Parameter Name="DB_TYPE">Teradata</Parameter>
        <Parameter Name="SORT_BY_NO">YES</Parameter>
        <Parameter Name="ORDER_BY">YES</Parameter>
        <Parameter Name="ORDER_BY_STRINGS">YES</Parameter>
        <Parameter Name="HAVING">YES</Parameter>
        <Parameter Name="GROUP_BY">YES</Parameter>
        <Parameter Name="GROUPBYCOL">YES</Parameter>
        <Parameter Name="EXT_JOIN">YES</Parameter>
        <Parameter Name="OUTERJOINS_GENERATION">INGRES</Parameter>
        <Parameter Name="FULL_EXT_JOIN">YES</Parameter>
        <Parameter Name="LEFT_EXT_JOIN">YES</Parameter>
        <Parameter Name="RIGHT_EXT_JOIN">YES</Parameter>
        <Parameter Name="EXT_JOIN_INVERT">YES</Parameter>
        <Parameter Name="JOIN_BY_SQL">YES</Parameter>
        <Parameter Name="IDENTIFIER_DELIMITER">&amp;quot;</Parameter>
        <Parameter Name="CONCAT">||</Parameter>
        <Parameter Name="UNION">UNION</Parameter>
        <Parameter Name="UNION_ALL">YES</Parameter>
        <Parameter Name="UNION_IN_SUBQUERY"></Parameter>
        <Parameter Name="INTERSECT">INTERSECT</Parameter>
        <Parameter Name="INTERSECT_ALL">YES</Parameter>
        <Parameter Name="INTERSECT_IN_SUBQUERY"></Parameter>
        <Parameter Name="MINUS">MINUS</Parameter>
        <Parameter Name="MINUS_ALL">YES</Parameter>
        <Parameter Name="MINUS_IN_SUBQUERY"></Parameter>
        <Parameter Name="SUBQUERY_IN_FROM">YES</Parameter>
        <Parameter Name="SUBQUERY_IN_WHERE">YES</Parameter>
        <Parameter Name="SUBQUERY_IN_IN">YES</Parameter>
        <Parameter Name="NULL_IN_SELECT_SUPPORTED">NO</Parameter>
        <Parameter Name="OWNER">Y</Parameter>
        <Parameter Name="QUALIFIER">N</Parameter>
        <Parameter Name="COMMA">||' '||</Parameter>
        <Parameter Name="KEY_INFO_SUPPORTED">Y</Parameter>
        <Parameter Name="COMBINE_WITHOUT_PARENTHESIS">Y</Parameter>
        <Parameter Name="OLAP_CLAUSE">QUALIFY</Parameter>
        <Parameter Name="BACK_QUOTE_SUPPORTED">Y</Parameter>
        <Parameter Name="IF_NULL">COALESCE</Parameter>
        <Parameter Name="RANK_SUPPORTED">YES</Parameter>
        <Parameter Name="PERCENT_RANK_SUPPORTED">YES</Parameter>
        <Parameter Name="CONSTANT_SAMPLING_SUPPORTED">YES</Parameter>
        <Parameter Name="SEED_SAMPLING_SUPPORTED">NO</Parameter>
        <Parameter Name="RISQL_FUNCTIONS">CSUM,MAVG,MDIFF,MLINREG,MSUM,RANK,QUANTILE,PERCENT_RANK</Parameter>
        <Parameter Name="USER_INPUT_DATE_FORMAT">{\d 'yyyy-mm-dd'}</Parameter>
        <Parameter Language="ja" Name="USER_INPUT_DATE_FORMAT">{!d 'yyyy-mm-dd'}</Parameter>
        <Parameter Name="USER_INPUT_NUMERIC_SEPARATOR">.</Parameter>
    </Configuration>
    <DateOperations>
        <DateOperation Name="YEAR">YEAR($D)</DateOperation>
        <DateOperation Name="QUARTER">{ FN QUARTER($D) }</DateOperation>
        <DateOperation Name="MONTH">{ FN MONTH($D) }</DateOperation>
    </DateOperations>
    <Operators>
        <Operator Arity="1" ID="ADD" Type="Numeric">+</Operator>
        <Operator Arity="1" ID="SUBSTRACT" Type="Numeric">-</Operator>
        <Operator Arity="1" ID="MULTIPLY" Type="Numeric">*</Operator>
        <Operator Arity="1" ID="DIVIDE" Type="Numeric">/</Operator>
        <Operator Arity="1" ID="CONCAT" Type="Char">||</Operator>
        <Operator Arity="0" ID="NOT" Type="Logical">NOT</Operator>
        <Operator Arity="1" ID="OR" Type="Logical">OR</Operator>
        <Operator Arity="1" ID="AND" Type="Logical">AND</Operator>
        <Operator Arity="0" ID="NOT_NULL" Type="Logical">IS NOT NULL</Operator>
        <Operator Arity="0" ID="NULL" Type="Logical">IS NULL</Operator>
        <Operator Arity="1" ID="SUP" Type="Logical">&amp;gt;=</Operator>
        <Operator Arity="1" ID="INF" Type="Logical">&amp;lt;=</Operator>
        <Operator Arity="1" ID="EQUAL" Type="Logical">=</Operator>
        <Operator Arity="1" ID="DIFF" Type="Logical">^=</Operator>
        <Operator Arity="1" ID="STRICT_SUP" Type="Logical">&amp;gt;</Operator>
        <Operator Arity="1" ID="STRICT_INF" Type="Logical">&amp;lt;</Operator>
        <Operator Arity="1" ID="IN_LIST" Type="Logical">IN</Operator>
        <Operator Arity="1" ID="NOT_IN_LIST" Type="Logical">NOT IN</Operator>
        <Operator Arity="1" ID="MATCH" Type="Logical">LIKE</Operator>
        <Operator Arity="1" ID="NOT_MATCH" Type="Logical">NOT LIKE</Operator>
        <Operator Arity="2" ID="BETWEEN" Type="Logical">BETWEEN  AND</Operator>
        <Operator Arity="2" ID="NOT_BETWEEN" Type="Logical">NOT BETWEEN  AND</Operator>
    </Operators>
    <Functions>
        <Function Distinct="True" Group="True" ID="Minimum" InMacro="True" Type="Numeric">
            <Arguments>
                <Argument Type="All"></Argument>
            </Arguments>
            <SQL>min($1)</SQL>
        </Function>
        <Function Distinct="True" Group="True" ID="Maximum" InMacro="True" Type="Numeric">
            <Arguments>
                <Argument Type="All"></Argument>
            </Arguments>
            <SQL>max($1)</SQL>
        </Function>
        <Function Distinct="True" Group="True" ID="Average" InMacro="True" Type="Numeric">
            <Arguments>
                <Argument Type="Numeric"></Argument>
            </Arguments>
            <SQL>avg($1)</SQL>
        </Function>
        <Function Distinct="True" Group="True" ID="Sum" InMacro="True" Type="Numeric">
            <Arguments>
                <Argument Type="Numeric"></Argument>
            </Arguments>
            <SQL>sum($1)</SQL>
        </Function>
        <Function Distinct="True" Group="True" ID="Count" InMacro="True" Type="Numeric">
            <Arguments>
                <Argument Type="All"></Argument>
            </Arguments>
            <SQL>count($1)</SQL>
        </Function>
        <Function Group="False" ID="Concat" InMacro="True" Type="String">
            <Arguments>
                <Argument Type="String"></Argument>
                <Argument Type="String"></Argument>
            </Arguments>
            <SQL>{fn concat($1,$2)}</SQL>
        </Function>
        <Function Group="False" ID="Left" InMacro="True" Type="String">
            <Arguments>
                <Argument Type="String"></Argument>
                <Argument Type="Numeric"></Argument>
            </Arguments>
            <SQL>{fn left($1,$2)}</SQL>
        </Function>
        <Function Group="False" ID="Length" InMacro="True" Type="Numeric">
            <Arguments>
                <Argument Type="String"></Argument>
            </Arguments>
            <SQL>{fn length($1)}</SQL>
        </Function>
        <Function Group="False" ID="Locate" InMacro="True" Type="Numeric">
            <Arguments>
                <Argument Type="String"></Argument>
                <Argument Type="String"></Argument>
            </Arguments>
            <SQL>{fn locate($1,$2)}</SQL>
        </Function>
        <Function Group="False" ID="Rtrim" InMacro="True" Type="String">
            <Arguments>
                <Argument Type="String"></Argument>
            </Arguments>
            <SQL>{fn rtrim($1)}</SQL>
        </Function>
        <Function Group="False" ID="Substring" InMacro="True" Type="String">
            <Arguments>
                <Argument Type="String"></Argument>
                <Argument Type="Numeric"></Argument>
                <Argument Type="Numeric"></Argument>
            </Arguments>
            <SQL>{fn substring($1,$2,$3)}</SQL>
        </Function>
        <Function Group="False" ID="Uppercase" InMacro="True" Type="String">
            <Arguments>
                <Argument Type="String"></Argument>
            </Arguments>
            <SQL>{fn ucase($1)}</SQL>
        </Function>
        <Function Group="False" ID="Absolute" InMacro="True" Type="Numeric">
            <Arguments>
                <Argument Type="Numeric"></Argument>
            </Arguments>
            <SQL>{fn abs($1)}</SQL>
        </Function>
        <Function Group="False" ID="Exp" InMacro="True" Type="Numeric">
            <Arguments>
                <Argument Type="Numeric"></Argument>
            </Arguments>
            <SQL>{fn exp($1)}</SQL>
        </Function>
        <Function Group="False" ID="Log" InMacro="True" Type="Numeric">
            <Arguments>
                <Argument Type="Numeric"></Argument>
            </Arguments>
            <SQL>{fn log($1)}</SQL>
        </Function>
        <Function Group="False" ID="Mod" InMacro="True" Type="Numeric">
            <Arguments>
                <Argument Type="Numeric"></Argument>
                <Argument Type="Numeric"></Argument>
            </Arguments>
            <SQL>{fn mod($1,$2)}</SQL>
        </Function>
        <Function Group="False" ID="Pi" InMacro="False" Type="Numeric">
            <SQL>{fn pi()}</SQL>
        </Function>
        <Function Group="False" ID="Sqrt" InMacro="True" Type="Numeric">
            <Arguments>
                <Argument Type="Numeric"></Argument>
            </Arguments>
            <SQL>{fn sqrt($1)}</SQL>
        </Function>
        <Function Group="False" ID="Character_prompt" InMacro="True" Type="String">
            <Arguments>
                <Argument Type="String"></Argument>
            </Arguments>
            <SQL>@prompt($1,'A',,,)</SQL>
        </Function>
        <Function Group="False" ID="Numeric_prompt" InMacro="True" Type="Numeric">
            <Arguments>
                <Argument Type="String"></Argument>
            </Arguments>
            <SQL>@prompt($1,'N',,,)</SQL>
        </Function>
        <Function Group="False" ID="Date_prompt" InMacro="True" Type="DateTime">
            <Arguments>
                <Argument Type="String"></Argument>
            </Arguments>
            <SQL>@prompt($1,'D',,,)</SQL>
        </Function>
        <Function Group="False" ID="RightRemove" InMacro="True" Type="String">
            <Arguments>
                <Argument Type="String"></Argument>
            </Arguments>
            <SQL>TRIM (TRAILING FROM $1)</SQL>
        </Function>
        <Function Group="False" ID="LeftRemove" InMacro="True" Type="String">
            <Arguments>
                <Argument Type="String"></Argument>
            </Arguments>
            <SQL>TRIM (LEADING FROM $1)</SQL>
        </Function>
        <Function Group="False" ID="RemoveBoth" InMacro="True" Type="String">
            <Arguments>
                <Argument Type="String"></Argument>
            </Arguments>
            <SQL>TRIM (BOTH FROM $1)</SQL>
        </Function>
        <Function Group="False" ID="Case" InMacro="True" Type="String">
            <Arguments>
                <Argument Type="String"></Argument>
                <Argument Type="String"></Argument>
                <Argument Type="String"></Argument>
                <Argument Type="String"></Argument>
            </Arguments>
            <SQL>CASE $1 WHEN $2 THEN $3 ELSE $4 END</SQL>
        </Function>
        <Function Group="False" ID="NullIf" InMacro="True" Type="String">
            <Arguments>
                <Argument Type="String"></Argument>
                <Argument Type="String"></Argument>
            </Arguments>
            <SQL>NULLIF($1, $2)</SQL>
        </Function>
        <Function Group="False" ID="Coalesce" InMacro="True" Type="String">
            <Arguments>
                <Argument Type="String"></Argument>
            </Arguments>
            <SQL>COALESCE($1)</SQL>
        </Function>
        <Function Group="False" ID="YearExtract" InMacro="True" Type="Numeric">
            <Arguments>
                <Argument Type="DateTime"></Argument>
            </Arguments>
            <SQL>EXTRACT(YEAR FROM $1)</SQL>
        </Function>
        <Function Group="False" ID="MonthExtract" InMacro="True" Type="Numeric">
            <Arguments>
                <Argument Type="DateTime"></Argument>
            </Arguments>
            <SQL>EXTRACT(MONTH FROM $1)</SQL>
        </Function>
        <Function Group="False" ID="DayExtract" InMacro="True" Type="Numeric">
            <Arguments>
                <Argument Type="DateTime"></Argument>
            </Arguments>
            <SQL>EXTRACT(DAY FROM $1)</SQL>
        </Function>
        <Function Group="False" ID="HourExtract" InMacro="True" Type="Numeric">
            <Arguments>
                <Argument Type="Numeric"></Argument>
            </Arguments>
            <SQL>EXTRACT(HOUR FROM $1)</SQL>
        </Function>
        <Function Group="False" ID="MinuteExtract" InMacro="True" Type="Numeric">
            <Arguments>
                <Argument Type="Numeric"></Argument>
            </Arguments>
            <SQL>EXTRACT(MINUTE FROM $1)</SQL>
        </Function>
        <Function Group="False" ID="SecondExtract" InMacro="True" Type="Numeric">
            <Arguments>
                <Argument Type="Numeric"></Argument>
            </Arguments>
            <SQL>EXTRACT(SECOND FROM $1)</SQL>
        </Function>
        <Function Group="False" ID="Add_months" InMacro="True" Type="DateTime">
            <Arguments>
                <Argument Type="DateTime"></Argument>
                <Argument Type="Numeric"></Argument>
            </Arguments>
            <SQL>ADD_MONTHS($1, $2)</SQL>
        </Function>
        <Function Group="False" ID="CurrentDate" InMacro="True" Type="DateTime">
            <SQL>DATE</SQL>
        </Function>
        <Function Group="False" ID="Cumulative_sum" InMacro="False" Type="Numeric">
            <Arguments>
                <Argument Type="Numeric"></Argument>
                <Argument Type="All"></Argument>
            </Arguments>
            <SQL>CSUM($1,$2)</SQL>
        </Function>
        <Function Group="False" ID="Moving_average" InMacro="False" Type="Numeric">
            <Arguments>
                <Argument Type="Numeric"></Argument>
                <Argument Type="Numeric"></Argument>
                <Argument Type="All"></Argument>
            </Arguments>
            <SQL>MAVG($1,$2,$3)</SQL>
        </Function>
        <Function Group="False" ID="Rank" InMacro="False" Type="Numeric">
            <Arguments>
                <Argument Type="All"></Argument>
            </Arguments>
            <SQL>RANK($1)</SQL>
        </Function>
        <Function Group="False" ID="Moving_sum" InMacro="False" Type="Numeric">
            <Arguments>
                <Argument Type="Numeric"></Argument>
                <Argument Type="Numeric"></Argument>
                <Argument Type="All"></Argument>
            </Arguments>
            <SQL>MSUM($1,$2,$3)</SQL>
        </Function>
        <Function Group="False" ID="Moving_difference" InMacro="False" Type="Numeric">
            <Arguments>
                <Argument Type="Numeric"></Argument>
                <Argument Type="Numeric"></Argument>
                <Argument Type="All"></Argument>
            </Arguments>
            <SQL>MDIFF($1,$2,$3)</SQL>
        </Function>
        <Function Group="False" ID="Quantile" InMacro="False" Type="Numeric">
            <Arguments>
                <Argument Type="Numeric"></Argument>
                <Argument Type="All"></Argument>
            </Arguments>
            <SQL>QUANTILE($1,$2)</SQL>
        </Function>
        <Function Group="False" ID="Moving_linear_regression" InMacro="False" Type="Numeric">
            <Arguments>
                <Argument Type="Numeric"></Argument>
                <Argument Type="Numeric"></Argument>
                <Argument Type="All"></Argument>
            </Arguments>
            <SQL>MLINREG($1,$2,$3)</SQL>
        </Function>
        <Function Group="False" ID="NUMBER_TO_CHAR" InMacro="True" Type="String">
            <Arguments>
                <Argument Type="Numeric"></Argument>
            </Arguments>
            <SQL>CAST($1 AS CHAR(30))</SQL>
        </Function>
        <Function Group="False" ID="DATE_TO_CHAR" InMacro="True" Type="String">
            <Arguments>
                <Argument Type="DateTime"></Argument>
            </Arguments>
            <SQL>CAST($1 AS CHAR(30))</SQL>
        </Function>
        <Function Group="False" ID="Rank_over" InMacro="False" Type="Numeric">
            <Arguments>
                <Argument Type="All"></Argument>
                <Argument Type="All"></Argument>
            </Arguments>
            <SQL>RANK() OVER (PARTITION BY ($1) ORDER BY ($2))</SQL>
        </Function>
        <Function Group="False" ID="Percent_rank_over" InMacro="False" Type="Numeric">
            <Arguments>
                <Argument Type="All"></Argument>
                <Argument Type="All"></Argument>
            </Arguments>
            <SQL>PERCENT_RANK() OVER (PARTITION BY ($1) ORDER BY ($2))</SQL>
        </Function>
        <Function Group="False" ID="ComplexCase" InMacro="True" Type="All">
            <Arguments>
                <Argument Type="Any"></Argument>
            </Arguments>
            <SQL>CASE $1 END</SQL>
        </Function>
    </Functions>
</DBParameters>

mithu81 (BOB member since 2010-03-04)

system · August 26, 2014, 7:52am

I was getting this error for few reports, which is pointing to the same Universe. But Other reports are working fine, because the universe is different.

The Underlying DB and ODBC connection looks good.

I just imported the Universe and Exported once again. By surprise, all started working again !!!

yourajai (BOB member since 2011-03-09)