cubewise-code
feat: convert MDX query to TM1py Native View
Introduction
this proposal is to add a MDX compiler to analyze Cube MDX expression and build TM1py Native View Object.
mdx = """
SELECT
{[DIM A].[HIE A].[ELE A]}
* {[DIM B].[ELE B]}
ON ROWS,
NONEMPTY
{[DIM C].[HIE C].MEMBERS}
ON COLUMNS
FROM [CUBE NAME]
WHERE (
[DIM D].[ELE D]
)
"""
native_view = MdxBuilder.to_tm1py_native_view(mdx)
tm1.views.create_or_update(cueb_name, view_name, native_view)
Motivation
Comparing to MDX View, Native View has a significant performance advantage on retrieving large data from Cube. But currently we don't have an direct approach to create a Native View with MDX.
I think we may have a good enough compiler to convert simple MDX to Native View to low computing cost of query.
Dependencies
this feature is requiring a new technology, which may increase a cost of maintenance.
Lark is a parser library to create a Concrete Syntax Tree with simple grammar setup.
It would be much easier to implement if we didn't start with raw MDX as a string but with an instance of the MdxBuilder class as input.
Is that acceptable for your use case?
Do you mean convert the mdx string to Mdxbuilder class first, then output the NativeView object regarding the content of MDXBuilder?
something like
builder = MDXbuilder.from_string_mdx(mdx)
nativeview = builder.to_tm1py_native_view()
I mean the MDX string must originate somewhere. I'm guessing it's created with Python originally, e.g., based on arguments or a parameter cube. If that is the case, Python could create a MdxBuilder object instead of an MDX string.
The other issue now I am facing is the performance. seems the parser algorithm can't handle long mdx string.
if the mdx length is over 10000, the parsing performance is noticeable bad. around 20-30k is unusable.
If we solve MdxBuilder to NativeView would that actually solve your problem?
I mean the MDX string must originate somewhere. I'm guessing it's created with Python originally, e.g., based on arguments or a parameter cube. If that is the case, Python could create a MdxBuilder object instead of an MDX string.
I see where you from. for specific project, building mdxbuilder based on arguments or parameter cube works.
but I have an experience about the system only provides a plaintext mdx that I can do nothing to optimise it. And we did tones of workaround for it.
like, reconstruct the pipeline using bedrock to build the view, then using python to get it
but I have an experience about the system only provides a plaintext mdx that I can do nothing to optimise it. And we did tones of workaround for it.
Is it a simple MDX at least (e.g. comma separated members on axes)?
if it's simple enough maybe it can be understood via regex?
but I have an experience about the system only provides a plaintext mdx that I can do nothing to optimise it. And we did tones of workaround for it.
Is it a simple MDX at least (e.g. comma separated members on axes)?
if it's simple enough maybe it can be understood via regex?
yes, I agree. if the mdxset is {[Dim A].[Ele A]}, regex handle it perfectly.
it backed to the question of scale. do I want more?
if I want to understand the mdx function context. like, TM1FilterByLevel and TM1SubsetAll.
or even the nested mdxset...
probably regex is not a good decision for long-term maintenance
Are native views available for v12, I don’t recall. If not, I don’t think this feature would have a long lifespan
From: Joe Chow HK @.> Reply-To: cubewise-code/mdxpy @.> Date: Tuesday, May 27, 2025 at 11:24 PM To: cubewise-code/mdxpy @.> Cc: Subscribed @.> Subject: Re: [cubewise-code/mdxpy] feat: convert MDX query to TM1py Native View (Issue #57)
[https://avatars.githubusercontent.com/u/164451309?s=20&v=4]Cubewise-JoeCHK left a comment (cubewise-code/mdxpy#57)https://github.com/cubewise-code/mdxpy/issues/57#issuecomment-2914774087
but I have an experience about the system only provides a plaintext mdx that I can do nothing to optimise it. And we did tones of workaround for it.
Is it a simple MDX at least (e.g. comma separated members on axes)?
if it's simple enough maybe it can be understood via regex?
yes, I agree. if the mdxset is {[Dim A].[Ele A]}, regex handle it perfectly.
it backed to the question of scale. do I want more?
if I want to understand the mdx function context. like, TM1FilterByLevel and TM1SubsetAll.
probably regex is not a good decision for long-term maintenance
— Reply to this email directly, view it on GitHubhttps://github.com/cubewise-code/mdxpy/issues/57#issuecomment-2914774087, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AEK7GZTT6RT4Y72SZQCXEC33AUT3RAVCNFSM6AAAAAB5ZDRLPCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDSMJUG43TIMBYG4. You are receiving this because you are subscribed to this thread.Message ID: @.***>
@rclapp I understand. I would make it in a new project for trial first. I still have a strong belief about the demands for the feature converting plaintext to MDXpy classes.