From 4a73d6cdd1c809b6a09cbd44959588b6d5a703d2 Mon Sep 17 00:00:00 2001 From: CoprDistGit Date: Tue, 11 Apr 2023 11:07:57 +0000 Subject: automatic import of python-mdxpy --- .gitignore | 1 + python-mdxpy.spec | 777 ++++++++++++++++++++++++++++++++++++++++++++++++++++++ sources | 1 + 3 files changed, 779 insertions(+) create mode 100644 python-mdxpy.spec create mode 100644 sources diff --git a/.gitignore b/.gitignore index e69de29..cdd2171 100644 --- a/.gitignore +++ b/.gitignore @@ -0,0 +1 @@ +/mdxpy-1.2.tar.gz diff --git a/python-mdxpy.spec b/python-mdxpy.spec new file mode 100644 index 0000000..e57d25f --- /dev/null +++ b/python-mdxpy.spec @@ -0,0 +1,777 @@ +%global _empty_manifest_terminate_build 0 +Name: python-mdxpy +Version: 1.2 +Release: 1 +Summary: A simple, yet elegant MDX library for TM1 +License: MIT-LICENSE +URL: https://github.com/cubewise-code/mdxpy +Source0: https://mirrors.nju.edu.cn/pypi/web/packages/46/72/e16ebcbfcf4af46cf03879f44e8e2b48220e024095ef3826b61dd63e05db/mdxpy-1.2.tar.gz +BuildArch: noarch + + +%description +![Logo](./images/logo.png) + + +## MDXpy + +A simple, yet elegant MDX library for TM1 + +## Install + + pip install mdxpy + +## Usage + +Create MDX queries programmatically with the `Member`, `MdxTuple`, `MdxHierarchySet`, `MdxBuilder` classes. + +Benefits of using MDXpy over hacking raw MDX queries in your code +- Faster to write +- Requires less MDX knowledge +- Eliminates syntax errors (e.g. forget `}`, `]`, `)` in a query) forever +- Makes code more robust and easier to refactor +- Escaping of `]` in object names is taken care of + +### Member + +`Member` is used in `MdxTuple` and `MdxHierarchySet`. +create a `Member` with the static `Member.of(*args: str)` method. + +``` python +>>> member = Member.of("Product", "Product1") +>>> print(member.unique_name) +[PRODUCT].[PRODUCT].[PRODUCT1] + +>>> member = Member.of("Region", "ByGeography", "UK") +>>> print(member.unique_name) +[REGION].[BYGEOGRAPHY].[UK] +``` + +### MdxTuple + +Create a `MdxTuple` with the static `of(*args: Member)` method. The MDX expression of the tuple is generated with the `to_mdx` method. + +``` python +>>> 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].[North America]) + +``` + +you can add a `Member` to a `MdxTuple` + +``` python +>>> 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` is created with any of the static methods on the `MdxHierarchySet` class. The `MDX` expression of the set is generated with the `to_mdx` method. + +``` python +>>> 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]} +``` + +Functions on `MdxHierarchySet` can be concatenated to arbitrary length in a functional style: + +``` python +>>> 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 + +The `MdxBuilder` is used to build MDX queries. `MdxHierarchySet` or `MdxTuple` are placed on the axes. Zero suppression can be switched on or off per axis. The actual `MDX` expression is generated with the `to_mdx` method. + +``` python +>>> 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)} ON 0 +FROM [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]} ON 0 +FROM [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])} ON 0 +FROM [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()) +SELECT +NON EMPTY {[PRODUCT].[PRODUCT].[PRODUCT1]} ON 0 +FROM [CUBE] +``` + +MDX queries can have any number of axes. Axis 0 _(=columns)_ must be defined. + +``` python +>>> 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]} ON 0, +{TM1FILTERBYLEVEL({TM1SUBSETALL([PRODUCT].[PRODUCT])},0)} ON 1, +{[VERSION].[VERSION].[ACTUAL]} ON 2, +{TM1SUBSETTOSET([TIME].[TIME],'2020-Q1')} ON 3 +FROM [CUBE] +``` + +The `CalculatedMember` class is used to define query-scoped calculated members. They are used with the `MdxBuilder` through the `with_member` function. + +``` python +>>> 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) +WITH +MEMBER [PERIOD].[PERIOD].[AVG2016] AS AVG({[PERIOD].[PERIOD].[2016].CHILDREN},[Record Rating].([CHART].[CHART].[TOTALCHARTS],[RECORDRATINGMEASURE].[RECORDRATINGMEASURE].[RATING])) +SELECT +{([PERIOD].[PERIOD].[AVG2016])} ON 0, +{TOPCOUNT({[RECORD].[RECORD].[TOTALRECORDS].CHILDREN},5,[RECORDRATING].([PERIOD].[PERIOD].[AVG2016]))} ON 1 +FROM [RECORDRATING] +WHERE ([CHART].[CHART].[TOTALCHARTS],[RECORDRATINGMEASURE].[RECORDRATINGMEASURE].[RATING]) +``` + +The `DimensionProperty` class is used to query attributes in conjunction with data. +It is used with the `MdxBuilder` through the `add_properties_to_row_axis`, `add_hierarchy_set_to_column_axis` functions. + +``` python +from mdxpy import DimensionProperty, MdxHierarchySet, MdxBuilder, Member + +query = MdxBuilder.from_cube("Sales") + +query = query.rows_non_empty() +query = query.add_hierarchy_set_to_row_axis(MdxHierarchySet.all_leaves("Product")) +query = query.add_properties_to_row_axis(DimensionProperty.of("Product", "Description")) + +query = query.columns_non_empty() +query = query.add_hierarchy_set_to_column_axis(MdxHierarchySet.member(Member.of("Sales Measure", "Revenue"))) + +query = query.where(Member.of("Year", "2022"), Member.of("Region", "Switzerland")) + +print(query.to_mdx()) + +>>> print(mdx) +SELECT +NON EMPTY {[salesmeasure].[salesmeasure].[revenue]} DIMENSION PROPERTIES MEMBER_NAME ON 0, +NON EMPTY {TM1FILTERBYLEVEL({TM1SUBSETALL([product].[product])},0)} DIMENSION PROPERTIES [product].[product].[description] ON 1 +FROM [sales] +WHERE ([year].[year].[2022],[region].[region].[switzerland]) +``` + +To see all samples checkout the `test.py` file + +## Supported MDX Functions + +- TM1SUBSETALL +- MEMBERS +- TM1SUBSETTOSET +- DEFAULTMEMBER +- PARENT +- FIRSTCHILD +- LASTCHILD +- CHILDREN +- ANCESTORS +- ANCESTOR +- DRILLDOWNLEVEL +- FILTER +- TM1FILTERBYPATTERN +- TM1FILTERBYLEVEL +- TM1SORT +- HEAD +- TAIL +- SUBSET +- TOPCOUNT +- BOTTOMCOUNT +- UNION +- INTERSECT +- EXCEPT +- ORDER + +## Tests + +All tests in `test.py` + +## Contribution + +Contribution is welcome. If you find a bug or feel like you can contribute please fork the repository, update the code and then create a pull request so we can merge in the changes. + + + +%package -n python3-mdxpy +Summary: A simple, yet elegant MDX library for TM1 +Provides: python-mdxpy +BuildRequires: python3-devel +BuildRequires: python3-setuptools +BuildRequires: python3-pip +%description -n python3-mdxpy +![Logo](./images/logo.png) + + +## MDXpy + +A simple, yet elegant MDX library for TM1 + +## Install + + pip install mdxpy + +## Usage + +Create MDX queries programmatically with the `Member`, `MdxTuple`, `MdxHierarchySet`, `MdxBuilder` classes. + +Benefits of using MDXpy over hacking raw MDX queries in your code +- Faster to write +- Requires less MDX knowledge +- Eliminates syntax errors (e.g. forget `}`, `]`, `)` in a query) forever +- Makes code more robust and easier to refactor +- Escaping of `]` in object names is taken care of + +### Member + +`Member` is used in `MdxTuple` and `MdxHierarchySet`. +create a `Member` with the static `Member.of(*args: str)` method. + +``` python +>>> member = Member.of("Product", "Product1") +>>> print(member.unique_name) +[PRODUCT].[PRODUCT].[PRODUCT1] + +>>> member = Member.of("Region", "ByGeography", "UK") +>>> print(member.unique_name) +[REGION].[BYGEOGRAPHY].[UK] +``` + +### MdxTuple + +Create a `MdxTuple` with the static `of(*args: Member)` method. The MDX expression of the tuple is generated with the `to_mdx` method. + +``` python +>>> 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].[North America]) + +``` + +you can add a `Member` to a `MdxTuple` + +``` python +>>> 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` is created with any of the static methods on the `MdxHierarchySet` class. The `MDX` expression of the set is generated with the `to_mdx` method. + +``` python +>>> 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]} +``` + +Functions on `MdxHierarchySet` can be concatenated to arbitrary length in a functional style: + +``` python +>>> 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 + +The `MdxBuilder` is used to build MDX queries. `MdxHierarchySet` or `MdxTuple` are placed on the axes. Zero suppression can be switched on or off per axis. The actual `MDX` expression is generated with the `to_mdx` method. + +``` python +>>> 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)} ON 0 +FROM [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]} ON 0 +FROM [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])} ON 0 +FROM [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()) +SELECT +NON EMPTY {[PRODUCT].[PRODUCT].[PRODUCT1]} ON 0 +FROM [CUBE] +``` + +MDX queries can have any number of axes. Axis 0 _(=columns)_ must be defined. + +``` python +>>> 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]} ON 0, +{TM1FILTERBYLEVEL({TM1SUBSETALL([PRODUCT].[PRODUCT])},0)} ON 1, +{[VERSION].[VERSION].[ACTUAL]} ON 2, +{TM1SUBSETTOSET([TIME].[TIME],'2020-Q1')} ON 3 +FROM [CUBE] +``` + +The `CalculatedMember` class is used to define query-scoped calculated members. They are used with the `MdxBuilder` through the `with_member` function. + +``` python +>>> 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) +WITH +MEMBER [PERIOD].[PERIOD].[AVG2016] AS AVG({[PERIOD].[PERIOD].[2016].CHILDREN},[Record Rating].([CHART].[CHART].[TOTALCHARTS],[RECORDRATINGMEASURE].[RECORDRATINGMEASURE].[RATING])) +SELECT +{([PERIOD].[PERIOD].[AVG2016])} ON 0, +{TOPCOUNT({[RECORD].[RECORD].[TOTALRECORDS].CHILDREN},5,[RECORDRATING].([PERIOD].[PERIOD].[AVG2016]))} ON 1 +FROM [RECORDRATING] +WHERE ([CHART].[CHART].[TOTALCHARTS],[RECORDRATINGMEASURE].[RECORDRATINGMEASURE].[RATING]) +``` + +The `DimensionProperty` class is used to query attributes in conjunction with data. +It is used with the `MdxBuilder` through the `add_properties_to_row_axis`, `add_hierarchy_set_to_column_axis` functions. + +``` python +from mdxpy import DimensionProperty, MdxHierarchySet, MdxBuilder, Member + +query = MdxBuilder.from_cube("Sales") + +query = query.rows_non_empty() +query = query.add_hierarchy_set_to_row_axis(MdxHierarchySet.all_leaves("Product")) +query = query.add_properties_to_row_axis(DimensionProperty.of("Product", "Description")) + +query = query.columns_non_empty() +query = query.add_hierarchy_set_to_column_axis(MdxHierarchySet.member(Member.of("Sales Measure", "Revenue"))) + +query = query.where(Member.of("Year", "2022"), Member.of("Region", "Switzerland")) + +print(query.to_mdx()) + +>>> print(mdx) +SELECT +NON EMPTY {[salesmeasure].[salesmeasure].[revenue]} DIMENSION PROPERTIES MEMBER_NAME ON 0, +NON EMPTY {TM1FILTERBYLEVEL({TM1SUBSETALL([product].[product])},0)} DIMENSION PROPERTIES [product].[product].[description] ON 1 +FROM [sales] +WHERE ([year].[year].[2022],[region].[region].[switzerland]) +``` + +To see all samples checkout the `test.py` file + +## Supported MDX Functions + +- TM1SUBSETALL +- MEMBERS +- TM1SUBSETTOSET +- DEFAULTMEMBER +- PARENT +- FIRSTCHILD +- LASTCHILD +- CHILDREN +- ANCESTORS +- ANCESTOR +- DRILLDOWNLEVEL +- FILTER +- TM1FILTERBYPATTERN +- TM1FILTERBYLEVEL +- TM1SORT +- HEAD +- TAIL +- SUBSET +- TOPCOUNT +- BOTTOMCOUNT +- UNION +- INTERSECT +- EXCEPT +- ORDER + +## Tests + +All tests in `test.py` + +## Contribution + +Contribution is welcome. If you find a bug or feel like you can contribute please fork the repository, update the code and then create a pull request so we can merge in the changes. + + + +%package help +Summary: Development documents and examples for mdxpy +Provides: python3-mdxpy-doc +%description help +![Logo](./images/logo.png) + + +## MDXpy + +A simple, yet elegant MDX library for TM1 + +## Install + + pip install mdxpy + +## Usage + +Create MDX queries programmatically with the `Member`, `MdxTuple`, `MdxHierarchySet`, `MdxBuilder` classes. + +Benefits of using MDXpy over hacking raw MDX queries in your code +- Faster to write +- Requires less MDX knowledge +- Eliminates syntax errors (e.g. forget `}`, `]`, `)` in a query) forever +- Makes code more robust and easier to refactor +- Escaping of `]` in object names is taken care of + +### Member + +`Member` is used in `MdxTuple` and `MdxHierarchySet`. +create a `Member` with the static `Member.of(*args: str)` method. + +``` python +>>> member = Member.of("Product", "Product1") +>>> print(member.unique_name) +[PRODUCT].[PRODUCT].[PRODUCT1] + +>>> member = Member.of("Region", "ByGeography", "UK") +>>> print(member.unique_name) +[REGION].[BYGEOGRAPHY].[UK] +``` + +### MdxTuple + +Create a `MdxTuple` with the static `of(*args: Member)` method. The MDX expression of the tuple is generated with the `to_mdx` method. + +``` python +>>> 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].[North America]) + +``` + +you can add a `Member` to a `MdxTuple` + +``` python +>>> 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` is created with any of the static methods on the `MdxHierarchySet` class. The `MDX` expression of the set is generated with the `to_mdx` method. + +``` python +>>> 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]} +``` + +Functions on `MdxHierarchySet` can be concatenated to arbitrary length in a functional style: + +``` python +>>> 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 + +The `MdxBuilder` is used to build MDX queries. `MdxHierarchySet` or `MdxTuple` are placed on the axes. Zero suppression can be switched on or off per axis. The actual `MDX` expression is generated with the `to_mdx` method. + +``` python +>>> 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)} ON 0 +FROM [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]} ON 0 +FROM [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])} ON 0 +FROM [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()) +SELECT +NON EMPTY {[PRODUCT].[PRODUCT].[PRODUCT1]} ON 0 +FROM [CUBE] +``` + +MDX queries can have any number of axes. Axis 0 _(=columns)_ must be defined. + +``` python +>>> 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]} ON 0, +{TM1FILTERBYLEVEL({TM1SUBSETALL([PRODUCT].[PRODUCT])},0)} ON 1, +{[VERSION].[VERSION].[ACTUAL]} ON 2, +{TM1SUBSETTOSET([TIME].[TIME],'2020-Q1')} ON 3 +FROM [CUBE] +``` + +The `CalculatedMember` class is used to define query-scoped calculated members. They are used with the `MdxBuilder` through the `with_member` function. + +``` python +>>> 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) +WITH +MEMBER [PERIOD].[PERIOD].[AVG2016] AS AVG({[PERIOD].[PERIOD].[2016].CHILDREN},[Record Rating].([CHART].[CHART].[TOTALCHARTS],[RECORDRATINGMEASURE].[RECORDRATINGMEASURE].[RATING])) +SELECT +{([PERIOD].[PERIOD].[AVG2016])} ON 0, +{TOPCOUNT({[RECORD].[RECORD].[TOTALRECORDS].CHILDREN},5,[RECORDRATING].([PERIOD].[PERIOD].[AVG2016]))} ON 1 +FROM [RECORDRATING] +WHERE ([CHART].[CHART].[TOTALCHARTS],[RECORDRATINGMEASURE].[RECORDRATINGMEASURE].[RATING]) +``` + +The `DimensionProperty` class is used to query attributes in conjunction with data. +It is used with the `MdxBuilder` through the `add_properties_to_row_axis`, `add_hierarchy_set_to_column_axis` functions. + +``` python +from mdxpy import DimensionProperty, MdxHierarchySet, MdxBuilder, Member + +query = MdxBuilder.from_cube("Sales") + +query = query.rows_non_empty() +query = query.add_hierarchy_set_to_row_axis(MdxHierarchySet.all_leaves("Product")) +query = query.add_properties_to_row_axis(DimensionProperty.of("Product", "Description")) + +query = query.columns_non_empty() +query = query.add_hierarchy_set_to_column_axis(MdxHierarchySet.member(Member.of("Sales Measure", "Revenue"))) + +query = query.where(Member.of("Year", "2022"), Member.of("Region", "Switzerland")) + +print(query.to_mdx()) + +>>> print(mdx) +SELECT +NON EMPTY {[salesmeasure].[salesmeasure].[revenue]} DIMENSION PROPERTIES MEMBER_NAME ON 0, +NON EMPTY {TM1FILTERBYLEVEL({TM1SUBSETALL([product].[product])},0)} DIMENSION PROPERTIES [product].[product].[description] ON 1 +FROM [sales] +WHERE ([year].[year].[2022],[region].[region].[switzerland]) +``` + +To see all samples checkout the `test.py` file + +## Supported MDX Functions + +- TM1SUBSETALL +- MEMBERS +- TM1SUBSETTOSET +- DEFAULTMEMBER +- PARENT +- FIRSTCHILD +- LASTCHILD +- CHILDREN +- ANCESTORS +- ANCESTOR +- DRILLDOWNLEVEL +- FILTER +- TM1FILTERBYPATTERN +- TM1FILTERBYLEVEL +- TM1SORT +- HEAD +- TAIL +- SUBSET +- TOPCOUNT +- BOTTOMCOUNT +- UNION +- INTERSECT +- EXCEPT +- ORDER + +## Tests + +All tests in `test.py` + +## Contribution + +Contribution is welcome. If you find a bug or feel like you can contribute please fork the repository, update the code and then create a pull request so we can merge in the changes. + + + +%prep +%autosetup -n mdxpy-1.2 + +%build +%py3_build + +%install +%py3_install +install -d -m755 %{buildroot}/%{_pkgdocdir} +if [ -d doc ]; then cp -arf doc %{buildroot}/%{_pkgdocdir}; fi +if [ -d docs ]; then cp -arf docs %{buildroot}/%{_pkgdocdir}; fi +if [ -d example ]; then cp -arf example %{buildroot}/%{_pkgdocdir}; fi +if [ -d examples ]; then cp -arf examples %{buildroot}/%{_pkgdocdir}; fi +pushd %{buildroot} +if [ -d usr/lib ]; then + find usr/lib -type f -printf "/%h/%f\n" >> filelist.lst +fi +if [ -d usr/lib64 ]; then + find usr/lib64 -type f -printf "/%h/%f\n" >> filelist.lst +fi +if [ -d usr/bin ]; then + find usr/bin -type f -printf "/%h/%f\n" >> filelist.lst +fi +if [ -d usr/sbin ]; then + find usr/sbin -type f -printf "/%h/%f\n" >> filelist.lst +fi +touch doclist.lst +if [ -d usr/share/man ]; then + find usr/share/man -type f -printf "/%h/%f.gz\n" >> doclist.lst +fi +popd +mv %{buildroot}/filelist.lst . +mv %{buildroot}/doclist.lst . + +%files -n python3-mdxpy -f filelist.lst +%dir %{python3_sitelib}/* + +%files help -f doclist.lst +%{_docdir}/* + +%changelog +* Tue Apr 11 2023 Python_Bot - 1.2-1 +- Package Spec generated diff --git a/sources b/sources new file mode 100644 index 0000000..7332a71 --- /dev/null +++ b/sources @@ -0,0 +1 @@ +c66d0331f3e2274a81c5d40fd9351f4b mdxpy-1.2.tar.gz -- cgit v1.2.3