一个简单而优雅的TM1 MDX库
mdxp的Python项目详细描述
MDXpy公司
一个简单但优雅的TM1 MDX库
安装
pip install mdxpy
使用
使用Member
、MdxTuple
、MdxHierarchySet
、MdxBuilder
类以编程方式创建MDX查询。在
在代码中使用MDXpy而不是对原始MDX查询进行黑客攻击的好处
- 写得更快
- 需要较少的MDX知识
- 永远消除语法错误(例如,在查询中忘记
}
,]
,)
) - 使代码更健壮、更易于重构
- 对象名中
]
的转义处理
成员
Member
用于MdxTuple
和{Member.of(*args: str)
方法创建一个Member
。在
MDX组件
使用静态的of(*args: Member)
方法创建MdxTuple
。元组的MDX表达式是用to_mdx
方法生成的。在
>>>mdx_tuple=MdxTuple.of(Member.of("Product","Product1"),Member.of("Region","US"))>>>print(mdx_tuple.to_mdx())([PRODUCT].[PRODUCT].[PRODUCT1],[REGION].[REGION].[US])>>>mdx_tuple=MdxTuple.of(Member.of("Product","ByType","Product1"),Member.of("Region","ByGeography","North America"))>>>print(mdx_tuple.to_mdx())([PRODUCT].[BYTYPE].[PRODUCT1],[REGION].[BYGEOGRAPHY].[NorthAmerica])
您可以将Member
添加到MdxTuple
>>>mdx_tuple=MdxTuple.of(Member.of("Product","ByType","Product1"))>>>mdx_tuple.add_member(Member.of("Region","ByGeography","North America"))>>>print(mdx_tuple.to_mdx())([PRODUCT].[BYTYPE].[PRODUCT1],[REGION].[BYGEOGRAPHY].[NORTHAMERICA])
MdxHierarchySet
MdxHierarchySet
是用MdxHierarchySet
类上的任何静态方法创建的。集合的MDX
表达式是用to_mdx
方法生成的。在
>>>mdx_set=MdxHierarchySet.tm1_subset_all("Product")>>>print(mdx_set.to_mdx()){TM1SUBSETALL([Product].[Product])}>>>mdx_set=MdxHierarchySet.tm1_subset_to_set("Region","By Geography","Default")>>>print(mdx_set.to_mdx()){TM1SUBSETTOSET([REGION].[BYGEOGRAPHY],'Default')}>>>mdx_set=MdxHierarchySet.all_leaves("Region")>>>print(mdx_set.to_mdx()){TM1FILTERBYLEVEL({TM1SUBSETALL([REGION].[REGION])},0)}>>>mdx_set=MdxHierarchySet.members([Member.of("Region","US"),Member.of("Product","Product1")])>>>print(mdx_set.to_mdx()){[REGION].[REGION].[US],[PRODUCT].[PRODUCT].[PRODUCT1]}
MdxHierarchySet
上的函数可以以函数样式连接到任意长度:
>>>mdx_set=MdxHierarchySet.tm1_subset_all("Region").filter_by_level(0).filter_by_pattern("I*").tm1_sort()>>>print(mdx_set.to_mdx()){TM1SORT({TM1FILTERBYPATTERN({TM1FILTERBYLEVEL({TM1SUBSETALL([REGION].[REGION])},0)},'I*')},ASC)}
MdxBuilder
用于构建MDX查询。MdxHierarchySet
或MdxTuple
放置在轴上。可以在每个轴上打开或关闭零点抑制。实际的MDX
表达式是用to_mdx
方法生成的。在
>>>query=MdxBuilder.from_cube("Cube").add_hierarchy_set_to_column_axis(MdxHierarchySet.all_leaves("Product"))>>>print(query.to_mdx())SELECT{TM1FILTERBYLEVEL({TM1SUBSETALL([PRODUCT].[PRODUCT])},0)}ON0FROM[CUBE]>>>query=MdxBuilder.from_cube("Cube").add_hierarchy_set_to_column_axis(MdxHierarchySet.member(Member.of("Product","Product1")))>>>print(query.to_mdx())SELECT{[PRODUCT].[PRODUCT].[PRODUCT1]}ON0FROM[CUBE]>>>query=MdxBuilder.from_cube("Cube").add_member_tuple_to_axis(0,Member.of("Product","Product1"),Member.of("Region","EMEA"))>>>print(query.to_mdx())SELECT{([PRODUCT].[PRODUCT].[PRODUCT1],[REGION].[REGION].[EMEA])}ON0FROM[CUBE]>>>query=MdxBuilder.from_cube("Cube").columns_non_empty().add_hierarchy_set_to_column_axis(MdxHierarchySet.member(Member.of("Product","Product1")))>>>print(query.to_mdx())SELECTNONEMPTY{[PRODUCT].[PRODUCT].[PRODUCT1]}ON0FROM[CUBE]
MDX查询可以有任意数量的轴。必须定义轴0(=列)。在
>>>mdx=MdxBuilder.from_cube("Cube") \
.add_hierarchy_set_to_axis(0,MdxHierarchySet.member(Member.of("Region","US"))) \
.add_hierarchy_set_to_axis(1,MdxHierarchySet.all_leaves("Product")) \
.add_hierarchy_set_to_axis(2,MdxHierarchySet.member(Member.of("Version","Actual"))) \
.add_hierarchy_set_to_axis(3,MdxHierarchySet.tm1_subset_to_set("Time","Time","2020-Q1")) \
.to_mdx()>>>print(mdx)SELECT{[REGION].[REGION].[US]}ON0,{TM1FILTERBYLEVEL({TM1SUBSETALL([PRODUCT].[PRODUCT])},0)}ON1,{[VERSION].[VERSION].[ACTUAL]}ON2,{TM1SUBSETTOSET([TIME].[TIME],'2020-Q1')}ON3FROM[CUBE]
CalculatedMember
类用于定义查询范围的计算成员。它们通过MdxBuilder
函数与MdxBuilder
一起使用。在
>>>mdx=MdxBuilder.from_cube(cube="Record Rating").with_member(CalculatedMember.avg(dimension="Period",hierarchy="Period",element="AVG 2016",cube="Record Rating",mdx_set=MdxHierarchySet.children(member=Member.of("Period","2016")),mdx_tuple=MdxTuple.of(Member.of("Chart","Total Charts"),Member.of("Record Rating Measure","Rating")))) \
.add_hierarchy_set_to_row_axis(MdxHierarchySet.children(Member.of("Record","Total Records")).top_count(cube="Record Rating",mdx_tuple=MdxTuple.of(Member.of("Period","AVG 2016")),top=5)) \
.add_member_tuple_to_columns(Member.of("Period","AVG 2016")) \
.where(Member.of("Chart","Total Charts"),Member.of("Record Rating Measure","Rating")) \
.to_mdx()>>>print(mdx)WITHMEMBER[PERIOD].[PERIOD].[AVG2016]ASAVG({[PERIOD].[PERIOD].[2016].CHILDREN},[RecordRating].([CHART].[CHART].[TOTALCHARTS],[RECORDRATINGMEASURE].[RECORDRATINGMEASURE].[RATING]))SELECT{([PERIOD].[PERIOD].[AVG2016])}ON0,{TOPCOUNT({[RECORD].[RECORD].[TOTALRECORDS].CHILDREN},5,[RECORDRATING].([PERIOD].[PERIOD].[AVG2016]))}ON1FROM[RECORDRATING]WHERE([CHART].[CHART].[TOTALCHARTS],[RECORDRATINGMEASURE].[RECORDRATINGMEASURE].[RATING])
要查看所有示例,请签出test.py
文件
支持的MDX函数
- TM1子集合
- 成员
- TM1变电站
- 默认成员
- 家长
- 第一个孩子
- 最后一个孩子
- 儿童
- 祖先
- 祖先
- 向下钻取
- 过滤器
- TM1过滤器模式
- TM1过滤器等级
- TM1端口
- 头部
- 尾巴
- 子集
- 顶部计数
- 底数
- 工会
- 相交
- 除了
- 命令
测试
test.py
中的所有测试
贡献
欢迎投稿。如果你发现了一个bug或者觉得你可以贡献一些东西,请分叉存储库,更新代码,然后创建一个pull请求,这样我们就可以合并这些更改了。在
标签:
- 项目
推荐PyPI第三方库
MdxBuilder
用于构建MDX查询。MdxHierarchySet
或MdxTuple
放置在轴上。可以在每个轴上打开或关闭零点抑制。实际的MDX
表达式是用to_mdx
方法生成的。在
>>>query=MdxBuilder.from_cube("Cube").add_hierarchy_set_to_column_axis(MdxHierarchySet.all_leaves("Product"))>>>print(query.to_mdx())SELECT{TM1FILTERBYLEVEL({TM1SUBSETALL([PRODUCT].[PRODUCT])},0)}ON0FROM[CUBE]>>>query=MdxBuilder.from_cube("Cube").add_hierarchy_set_to_column_axis(MdxHierarchySet.member(Member.of("Product","Product1")))>>>print(query.to_mdx())SELECT{[PRODUCT].[PRODUCT].[PRODUCT1]}ON0FROM[CUBE]>>>query=MdxBuilder.from_cube("Cube").add_member_tuple_to_axis(0,Member.of("Product","Product1"),Member.of("Region","EMEA"))>>>print(query.to_mdx())SELECT{([PRODUCT].[PRODUCT].[PRODUCT1],[REGION].[REGION].[EMEA])}ON0FROM[CUBE]>>>query=MdxBuilder.from_cube("Cube").columns_non_empty().add_hierarchy_set_to_column_axis(MdxHierarchySet.member(Member.of("Product","Product1")))>>>print(query.to_mdx())SELECTNONEMPTY{[PRODUCT].[PRODUCT].[PRODUCT1]}ON0FROM[CUBE]
MDX查询可以有任意数量的轴。必须定义轴0(=列)。在
>>>mdx=MdxBuilder.from_cube("Cube") \ .add_hierarchy_set_to_axis(0,MdxHierarchySet.member(Member.of("Region","US"))) \ .add_hierarchy_set_to_axis(1,MdxHierarchySet.all_leaves("Product")) \ .add_hierarchy_set_to_axis(2,MdxHierarchySet.member(Member.of("Version","Actual"))) \ .add_hierarchy_set_to_axis(3,MdxHierarchySet.tm1_subset_to_set("Time","Time","2020-Q1")) \ .to_mdx()>>>print(mdx)SELECT{[REGION].[REGION].[US]}ON0,{TM1FILTERBYLEVEL({TM1SUBSETALL([PRODUCT].[PRODUCT])},0)}ON1,{[VERSION].[VERSION].[ACTUAL]}ON2,{TM1SUBSETTOSET([TIME].[TIME],'2020-Q1')}ON3FROM[CUBE]
CalculatedMember
类用于定义查询范围的计算成员。它们通过MdxBuilder
函数与MdxBuilder
一起使用。在
>>>mdx=MdxBuilder.from_cube(cube="Record Rating").with_member(CalculatedMember.avg(dimension="Period",hierarchy="Period",element="AVG 2016",cube="Record Rating",mdx_set=MdxHierarchySet.children(member=Member.of("Period","2016")),mdx_tuple=MdxTuple.of(Member.of("Chart","Total Charts"),Member.of("Record Rating Measure","Rating")))) \ .add_hierarchy_set_to_row_axis(MdxHierarchySet.children(Member.of("Record","Total Records")).top_count(cube="Record Rating",mdx_tuple=MdxTuple.of(Member.of("Period","AVG 2016")),top=5)) \ .add_member_tuple_to_columns(Member.of("Period","AVG 2016")) \ .where(Member.of("Chart","Total Charts"),Member.of("Record Rating Measure","Rating")) \ .to_mdx()>>>print(mdx)WITHMEMBER[PERIOD].[PERIOD].[AVG2016]ASAVG({[PERIOD].[PERIOD].[2016].CHILDREN},[RecordRating].([CHART].[CHART].[TOTALCHARTS],[RECORDRATINGMEASURE].[RECORDRATINGMEASURE].[RATING]))SELECT{([PERIOD].[PERIOD].[AVG2016])}ON0,{TOPCOUNT({[RECORD].[RECORD].[TOTALRECORDS].CHILDREN},5,[RECORDRATING].([PERIOD].[PERIOD].[AVG2016]))}ON1FROM[RECORDRATING]WHERE([CHART].[CHART].[TOTALCHARTS],[RECORDRATINGMEASURE].[RECORDRATINGMEASURE].[RATING])
要查看所有示例,请签出test.py
文件
支持的MDX函数
- TM1子集合
- 成员
- TM1变电站
- 默认成员
- 家长
- 第一个孩子
- 最后一个孩子
- 儿童
- 祖先
- 祖先
- 向下钻取
- 过滤器
- TM1过滤器模式
- TM1过滤器等级
- TM1端口
- 头部
- 尾巴
- 子集
- 顶部计数
- 底数
- 工会
- 相交
- 除了
- 命令
测试
test.py
中的所有测试
贡献
欢迎投稿。如果你发现了一个bug或者觉得你可以贡献一些东西,请分叉存储库,更新代码,然后创建一个pull请求,这样我们就可以合并这些更改了。在
- 项目
标签: