数据源 AIH

管理员必须在 Analytical Workspace > Virtual dataset and Datasource > Custom Query > Other DB 文件夹中配置 CPM。

该配置使用 AIH 数据源的标准配置，有关更多详细信息，建议阅读 CCH Tagetik 分析工作区模块的手册。下表包含一些注释，可简化在 SCP Modernization 中使用数据源的过程。具体而言，此选项卡配置查询（每个 DBMS 平台一个）以定义数据源。单元格字段的语法定义参数化查询。您还可以在不保存的情况下预览查询。您可以使用当前连接，或者在拥有必要权限的情况下选择特定的数据库。预览会显示查询所请求数据的临时表（最多 30 条）。

Custom Query 屏幕上 Other DB 选项卡的示例

来自 Athena 数据库的查询表 assumption_vdb 和 assumption_vdb_data 的配置详细信息如下：

配置字段	描述
Connection	与 SCP 数据的辅助数据库连接。
Query	使用 Athena SQL 编写，以从 SCP 检索数据。参见附录 A。SCP 数据查询示例.sql。注意：以下划线（'_'、数字和小写字母）字符开头的字段应使用别名重命名。如果名称在 CCH Tagetik 数据库中被锁定（即 I、DATE、TYPE 等），请重命名字段。系统架构不允许您直接从数据源保存 select * from table 查询，而只能保存调用了特定字段的查询，例如 select field_a, field_b from table。创建数据源的操作方式如下： 1. 使用 Select * from table 作为 "Preview" 查看构成表的字段，并使用 Athena 语言构建 Query Script 2. 使用 Select field_a, field_b from table。用于随后能够 "Save" 查询并像往常一样在数据集中使用它。
Fields	此选项卡按 select 中的相同顺序列出 SQL 查询的 select 语句列表中定义的所有字段。对于每个字段，您可以：修改描述（多语言向导支持 25 种语言 — 默认情况下，它等于字段的标签）将其链接到 Tagetik 维度（仅文本字段 — 每个维度只能链接到一个字段）定义其汇总（对于数字字段）您还可以将其链接到工作区中的字典，就像标准数据集的字段一样。可用维度的示例包括： - Scenario - Period - Analytical dimension - Entity - Currency Account、Category 和 Custom Dimensions 不受支持。
分区策略	两个表 assumptions_vdb_data 和 assumption_vdb 已分区，以优化数据库内查询的性能。请参阅附加的逻辑： - assumptions_vdb_data，PARTITIONED BY scenario， - assumption_vdb，PARTITIONED BY plan assumptions_vdb_data 表中有 "partion" 字段，根据 id 填充，该字段按 "-" 字符的存在进行拆分，仅取第一部分

SCP 数据查询示例.sql¶

注意：该查询从辅助数据库连接读取，该连接应设置到数据源 AIH 中。此外，SCP 实例应设置到 FROM SCPinstance.table 中（即 FROM acme.assumptions_vdb）

assumptions_vdb 的查询

SELECT

distinct

split_part (channel, '|', 2) as channel_code,

substr(cast(split_part (channel , '|',1) as varchar),1 ,22) as channel_description,

split_part (ownercode_base , '|',1) as ownercode_base_code,

substr(cast(split_part (ownername_base , '|',1) as varchar), 1,22) as ownername_base_description ,

_id as desc_code,

coalesce( split_part( split_part (part , '%',18), '7c',2), split_part (part, '|', 4) ) as Part_Source,

coalesce( split_part( split_part (part , '%',17), '7c',2), split_part (part, '|', 3) ) as Part_Type,

split_part (family, '|', 2) as family_code,

substr(cast(split_part (family , '|',1)as varchar), 1,22) as family_description,

split_part (subfam, '|', 2) as subfam_code,

substr(cast(split_part (subfam , '|',1)as varchar), 1,22) as subfam_description,

split_part (color, '|', 2)||'_C' as color_code,

substr(cast(split_part (color , '|',1)as varchar), 1,22) as color_description,

coalesce(split_part(size, '|',2), split_part(size, 'c', 2)) as size_code,

substr(cast(split_part (size, '|',1)as varchar), 1,22) as size_description,

split_part (style, '|', 2) as style_code,

substr(cast(split_part (style , '|',1)as varchar), 1,22) as style_description,

coalesce( split_part (salesacct , '|',2), split_part (salesacct , '7c',2) ) as salesacct_code,

substr(cast(split_part(split_part (salesacct, '|', 1), '%', 1) as varchar), 1,22) as salesacct_description,

CAST ( SPLIT_PART(location,'|',1) AS varchar) as Location_type_code,

substr(cast(SPLIT_PART(location,' |',2) as varchar), 1,22) as Location_type_description,

CAST ( SPLIT_PART(location,'|',3) AS varchar) as Location_region,

CAST ( SPLIT_PART(location,'|',4) AS varchar) as Location_country,

REPLACE(REPLACE(REPLACE(CAST ( UPPER(SPLIT_PART(location,' |',5) ) AS varchar) ,' ',") , ","), ", '_') as Location_city,

segment,

SPLIT_PART(SPLIT_PART(segment,'|',1), '%', 1) as segment_code,

substr(cast( coalesce( SPLIT_PART (split_part (segment , '%',1), '|', 2), split_part(split_part (segment , '7c',2), '%', 1) ) as varchar), 1,22) as segment_description,

CASE WHEN UPPER(coalesce( SPLIT_PART (split_part (segment , '%',1), '|', 4) , split_part(split_part (segment , '7c',4) , '%', 1) ) ) = 'MIXED'

THEN CONCAT(coalesce( SPLIT_PART (split_part (segment , '%',1), '|', 3) , split_part(split_part (segment , '7c',3) , '%', 1) ) , '_', UPPER(coalesce( SPLIT_PART (split_part (segment , '%',1), '|', 4) , split_part(split_part (segment , '7c',4) , '%', 1) ) ))

ELSE UPPER(coalesce( SPLIT_PART (split_part (segment , '%',1), '|', 4) , split_part(split_part (segment , '7c',4) , '%', 1) ) )

END as segment_country,

coalesce( SPLIT_PART (split_part (segment , '%',1), '|', 3) , split_part(split_part (segment , '7c',3) , '%', 1) ) as segment_region

FROM tagetik_systemdemo.assumptions_vdb where plan = 'Sales'

用于合并 assumptions_vdb、assumptions_vdb_data 的全局查询，针对从 SCP 继承的 CPM 维度元素

SELECT

CONCAT(date_format(AA.date, '%Y'),'IBP_DM_CONS') AS SCENARIO,

date_format(AA.date, '%m') AS PERIOD, AA.partition as part_code, /*--dim */

SPLIT_PART(C.channel,'|',2) as channel, /*--dim */

SPLIT_PART(C.ownercode_base,'|',1) as ownercode_base, /*--dim */

SPLIT_PART(C.location,'|',1) as Location_code, /*--dim */

split_part (split_part (AA._id, '%',1) , '-', 2) as segment_code, /*--dim */

coalesce(cast(AA.actual_v as real), 0) as actual_v ,

coalesce(cast(AA.forecast_v as real), 0) as forecast_v ,

coalesce(cast(AA.stat_v as real), 0) as stat_v,

coalesce(cast(AA.baseline_adjustments as real), 0) as baseline_adjustments

FROM tagetik_systemdemo.assumptions_vdb_data AA

LEFT OUTER JOIN tagetik_systemdemo. assumptions_vdb C

ON C._ID = AA._ID

where C.partition = 'Sales'

and AA.partition = ${@$ANL_SKU_P.code}