从ESSBASE取数的三种方式

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Tomoras/article/details/86592806

方式1：计算脚本方式

通过计算脚本计算出essbase取数范围，以文本方式输出到数据库中
计算脚本举例：

//ESS_LOCALE SimplifiedChinese_China.MS936@Binary

SET DATAEXPORTOPTIONS

        {

                DataExportLevel "ALL";

                 DataExportDynamicCalc OFF;

                DataExportDimHeader ON;

                DATAEXPORTOVERWRITEFILE ON;

               /* DataExportRelationalFile ON;*/

        };

FIX (

/*期间*/ "Local",

/*实际*/ "CU00",

/*版本*/ "SU00",

/*费用类缺省1*/  "TR00",

/*费用类缺省2*/  "RT00",

                 "OJ00",

                 "AT00",

                 "DP00",

                 "Budget-ADJ",

                 "V0301",

                 "YearTotal",&ZF_CurrM,

                 &ZF_CurrY,

                 @Descendant("Entity_Group"),

                 @Descendant("AC0204"),@Descendant("AC12"),@Descendant("AC07"),@Descendant("AC08"),@Descendant("AC11")

)

 

DATAEXPORT "File" "," "/home/ODI/calcs/PLN_TO_TEMP_DATA_EXP_CHG_ADJ_Q1.txt";

具体做法
新建程序包，在工具箱中选择OdiOutFile，

打开接口，映射选临时区域

流选择

其余内容根据需求设定

方式2：报表脚本方式

以报表的方式得到essbase的取数范围，然后直接推数到数据库
报表脚本举例：维度不能少

//ESS_LOCALE SimplifiedChinese_China.MS936@Binary

<Sym

{SUPALL}{TABDELIMIT} {NAMESON}{ROWREPEAT}{NOINDENTGEN}{SUPMISSINGROWS}

{MISSINGTEXT "0"}

 

<Row("Currency","Counterparty","Source","Transport","Route","Object","Activity","Other","Scenario","Version","Period","Years","Entity","Account")

{ OUTALTNAMES  }

"Local"

"CU00"

"SU00"

"TR00"

"RT00"

"OJ00"

"AT00"

"DP00"

"Budget-ADJ"

"V0302"

"YearTotal"

&ZF_CurrY

<Descendant "Entity_Group"

<Descendant "AC0204"

!

具体做法

新建接口，映射选择临时区域

报表脚本流设置

方式3：MDX查询方式

通过查询essbase多维库中数据范围，推数到数据库 MDX查询语句举例：

SELECT

NON EMPTY {[Route_NoRoute]}

ON COLUMNS,

NON EMPTY {Descendants([Entity_Group])}

ON AXIS(1),

NON EMPTY {[NoCounterparty]}

ON AXIS(2),

NON EMPTY {[BusinessObject_NoBusinessObject]}

ON AXIS(3),

NON EMPTY {[Channel_No Channel]}

ON AXIS(4),

NON EMPTY {[NoBusinessActivity]}

ON AXIS(5),

NON EMPTY {[Transport_NoTransport]}

ON AXIS(6),

NON EMPTY {[HSP_InputValue]}

ON AXIS(7),

NON EMPTY {[Adjust]}

ON AXIS(8),

NON EMPTY {[Daily Adj]}

ON AXIS(9),

NON EMPTY {[Local]}

ON AXIS(10),

NON EMPTY {[&adjYear]}

ON AXIS(11),

NON EMPTY {[&ZFCurMonth],[YearTotal]}

ON AXIS(12),

NON EMPTY {Descendants([3120001000]),Descendants([5501000001]),Descendants([5501000002]),Descendants([5503000000]),Descendants([5601000000]),Descendants([5405099999])}

ON AXIS(13)

 

FROM [CNAFPRD.Plan1]

具体做法
新建接口，映射选择临时区域

报表脚本流设置

————————————————
版权声明：本文为CSDN博主「Tomoras」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/Tomoras/article/details/86592806