olap - 基于 OLAP 级别的度量向下钻取问题
问题描述
每个人!
按照“Pentaho Analizer Cookbook”PDF,我尝试建立一个基于级别的度量。
我的多维数据集具有单维层次结构:具有项目的客户具有具有成本线级别 0 的阶段的建筑物具有成本线级别 1 的阶段。
所以,让我们有事实和维度的样本:
DROP TABLE IF EXISTS TMP_DEBUG_OLAP_13_FACTS;
CREATE TABLE TMP_DEBUG_OLAP_13_FACTS AS
SELECT 1 AS client_id,1 as project_id,1 as building_id,'1_1_1_1' as phase_id,'1_1_1_1_1' as costs_line_level_0_id,'1_1_1_1_1_1' as costs_line_level_1_id,10 as amount
UNION ALL SELECT 1 AS client_id,1 as project_id,1 as building_id,'1_1_1_1' as phase_id,'1_1_1_1_2' as costs_line_level_0_id,'1_1_1_1_2_1' as costs_line_level_1_id,20 as amount
UNION ALL SELECT 1 AS client_id,1 as project_id,1 as building_id,'1_1_1_2' as phase_id,'1_1_1_2_1' as costs_line_level_0_id,'1_1_1_2_1_1' as costs_line_level_1_id,30 as amount
UNION ALL SELECT 1 AS client_id,1 as project_id,2 as building_id,'1_1_2_1' AS phase_id,'1_1_2_1_1' as costs_line_level_0_id,'1_1_2_1_1_1' as costs_line_level_1_id,40 as amount
UNION ALL SELECT 1 AS client_id,2 as project_id,3 as building_id,'1_2_3_1' AS phase_id,'1_2_3_1_1' as costs_line_level_0_id,'1_2_3_1_1_1' as costs_line_level_1_id,50 as amount
UNION ALL SELECT 1 AS client_id,2 as project_id,4 as building_id,'1_2_4_1' AS phase_id,'1_2_4_1_1' as costs_line_level_0_id,'1_2_4_1_1_1' as costs_line_level_1_id,60 as amount
UNION ALL SELECT 2 AS client_id,3 as project_id,5 as building_id,'2_3_5_1' AS phase_id,'2_3_5_1_1' as costs_line_level_0_id,'2_3_5_1_1_-1' as costs_line_level_1_id,70 as amount
;
DROP TABLE IF EXISTS TMP_DEBUG_OLAP_13_DIMENSIONS;
CREATE TABLE TMP_DEBUG_OLAP_13_DIMENSIONS AS
SELECT 1 AS client_id,'Client 1 name' AS client_name,1 as project_id,'Project 1' as project_name,1 as building_id,'Building 1' as building_name,'1_1_1_1' as phase_id,'Phase 1' as phase_name,'1_1_1_1_1' as costs_line_level_0_id,'1 blah' AS costs_line_level_0_name,'1_1_1_1_1_1' AS costs_line_level_1_id,'1.1 blah' as costs_line_level_1_name
UNION ALL SELECT 1 AS client_id,'Client 1 name' AS client_name,1 as project_id,'Project 1' as project_name,1 as building_id,'Building 1' as building_name,'1_1_1_1' as phase_id,'Phase 1' as phase_name,'1_1_1_1_2' as costs_line_level_0_id,'2 blah' AS costs_line_level_0_name,'1_1_1_1_2_1' AS costs_line_level_1_id,'2.1 blah' as costs_line_level_1_name
UNION ALL SELECT 1 AS client_id,'Client 1 name' AS client_name,1 as project_id,'Project 1' as project_name,1 as building_id,'Building 1' as building_name,'1_1_1_2' as phase_id,'Phase 2' as phase_name,'1_1_1_2_1' as costs_line_level_0_id,'1 blah' AS costs_line_level_0_name,'1_1_1_2_1_1' AS costs_line_level_1_id,'1.1 blah' as costs_line_level_1_name
UNION ALL SELECT 1 AS client_id,'Client 1 name' AS client_name,1 as project_id,'Project 1' as project_name,2 as building_id,'Building 2' as building_name,'1_1_2_1' as phase_id,'Phase 1' as phase_name,'1_1_2_1_1' as costs_line_level_0_id,'1 blah' AS costs_line_level_0_name,'1_1_2_1_1_1' AS costs_line_level_1_id,'1.1 blah' as costs_line_level_1_name
UNION ALL SELECT 1 AS client_id,'Client 1 name' AS client_name,2 as project_id,'Project 2' as project_name,3 as building_id,'Building 3' as building_name,'1_2_3_1' as phase_id,'Phase 1' as phase_name,'1_2_3_1_1' as costs_line_level_0_id,'1 blah' AS costs_line_level_0_name,'1_2_3_1_1_1' AS costs_line_level_1_id,'1.1 blah' as costs_line_level_1_name
UNION ALL SELECT 1 AS client_id,'Client 1 name' AS client_name,2 as project_id,'Project 2' as project_name,4 as building_id,'Building 4' as building_name,'1_2_4_1' as phase_id,'Phase 1' as phase_name,'1_2_4_1_1' as costs_line_level_0_id,'1 blah' AS costs_line_level_0_name,'1_2_4_1_1_1' AS costs_line_level_1_id,'1.1 blah' as costs_line_level_1_name
UNION ALL SELECT 2 AS client_id,'Client 2 name' AS client_name,3 as project_id,'Project 3' as project_name,5 as building_id,'Building 5' as building_name,'2_3_5_1' as phase_id,'Phase 1' as phase_name,'2_3_5_1_1' as costs_line_level_0_id,'1 blah' AS costs_line_level_0_name,'2_3_5_1_1_-1' AS costs_line_level_1_id,'1.1 blah' as costs_line_level_1_name
--non-crosing dimentions
UNION ALL SELECT 2 AS client_id,'Client 2 name' AS client_name,3 as project_id,'Project 3' as project_name,5 as building_id,'Building 5' as building_name,'2_3_5_1' as phase_id,'Phase 1' as phase_name,'2_3_5_1_2' as costs_line_level_0_id,'2 blah' AS costs_line_level_0_name,'2_3_5_1_2_1' AS costs_line_level_1_id,'2.1 blah' as costs_line_level_1_name
UNION ALL SELECT 2 AS client_id,'Client 2 name' AS client_name,3 as project_id,'Project 3' as project_name,5 as building_id,'Building 5' as building_name,'2_3_5_1' as phase_id,'Phase 1' as phase_name,'2_3_5_1_3' as costs_line_level_0_id,'3 blah' AS costs_line_level_0_name,'2_3_5_1_3_1' AS costs_line_level_1_id,'3.1 blah' as costs_line_level_1_name
UNION ALL SELECT 2 AS client_id,'Client 2 name' AS client_name,3 as project_id,'Project 3' as project_name,5 as building_id,'Building 5' as building_name,'2_3_5_1' as phase_id,'Phase 1' as phase_name,'2_3_5_1_4' as costs_line_level_0_id,'4 blah' AS costs_line_level_0_name,NULL AS costs_line_level_1_id,'4.1 blah' as costs_line_level_1_name
;
我计算的度量是这样的
([Measures].[My measure] , Ancestor([Dimensions].CurrentMember , [Dimensions].[Building]) )
整个立方体的xml(蒙德里安)如下:
<Schema name="level_based_measure_unit_test">
<Dimension type="StandardDimension" visible="true" name="Dimensions">
<Hierarchy name="Dimensions hierarchy" visible="true" hasAll="true" primaryKey="costs_line_level_1_id">
<Table name="tmp_debug_olap_13_dimensions" schema="public" alias="">
</Table>
<Level name="Clients" visible="true" column="client_id" nameColumn="client_name" uniqueMembers="false">
</Level>
<Level name="Project" visible="true" column="project_id" nameColumn="project_name" uniqueMembers="false">
</Level>
<Level name="Building" visible="true" column="building_id" nameColumn="building_name" uniqueMembers="false">
</Level>
<Level name="Phase" visible="true" column="phase_id" nameColumn="phase_name" uniqueMembers="false">
</Level>
<Level name="Cost lines level 0" visible="true" column="costs_line_level_0_id" nameColumn="costs_line_level_0_name" uniqueMembers="false">
</Level>
<Level name="Cost lines level 1" visible="true" column="costs_line_level_1_id" nameColumn="costs_line_level_1_name" uniqueMembers="false">
</Level>
</Hierarchy>
</Dimension>
<Cube name="My cube" visible="true" cache="true" enabled="true">
<Table name="tmp_debug_olap_13_facts" schema="public" alias="">
</Table>
<DimensionUsage source="Dimensions" name="Dimensions" visible="true" foreignKey="costs_line_level_1_id">
</DimensionUsage>
<Measure name="My measure" column="amount" aggregator="sum" visible="true">
</Measure>
<CalculatedMember name="My leveled measure" formatString="" formula="([Measures].[My measure] , Ancestor([Dimensions].CurrentMember , [Dimensions].[Building]) )" dimension="Measures" visible="true">
</CalculatedMember>
</Cube>
</Schema>
我对该公式有 1 个问题:
它在建筑物级别显示一个值,但向下钻取,它保持其值。
我的意思的图片:
有谁知道我应该如何更改我的计算成员,因此它显示度量 [我的度量],但仅在建筑级别及以下?
太感谢了!
解决方案
正如 Pentaho Analizer Cookbook 指定的那样,该公式用于将 a 固定Measure
到某个级别。
它是:如果您向下钻取,您将获得向上钻取的值。
如果您需要(如果我理解正确的话)只是不为上层显示任何内容,那么使用下一个公式:
IIF([Dimensions].CurrentMember.Level.Ordinal < 3 , NULL , [Measures].[My measure] )
推荐阅读
- javascript - window.open 与变量一起使用时转到 null url (JavaScript)
- php - 如何解决 Heroku/Laravel 中的错误 500?
- shell - unix shell - ls -d $PWD
- python - 如何在 Django 中提供用户上传的 pdf 文件?
- ios - 在 Objective-C 或 Swift 中使用 CA 层和掩码将标签移动到视图顶部
- google-cloud-pubsub - 是否可以禁用订阅者对确认消息的需求?
- reactjs - 如何在 React JSX 中渲染同一个 div 中的多个元素
- pandas - 日期为字符串时的 Pandas KeyError 日期
- python - 'HdyViewSwitcherBar' 是无效的对象类型?
- javascript - 如何使用 Knex 创建 TINYINT 列?