TM1 Forum

Hi,
I need help with an MDX query requirement.

I have a CubeA with the following dimensions.

- Region
- Product
- CubeA_measure

and in CubeA_measure dimension has a numeric element of IsActive, which can be 0 or 1.

I have another cube called CubeB with the following dimensions:

- Period
- Region
- Product
- Customer
- Account
- CubeB_measure

In CubeB_measure dimension, I have a numeric element of Amount.

I need to create a report out of CubeB to display total amount based on a filter in CubeA for its measure IsActive.
For example, display sum of Amount from CubeB for each period where Region-Product combination is Active as per CubeA data.

I do not want to use rules and feeders to bring data from CubeA to CubeB or vise versa as I have lets say 20 million populated cells in CubeA and 1billion populated cells in CubeB.

How do I write an MDX query to give me this type of a filtered result from CubeB based on filtered values as per CubeA?

All responses are appreciated. Thanks in advance.

Regards,

Hello,

You could try it another way than using a MDX.

You could for example in your export script get a view with these criterion :
- Total Period
- Region
- Product
- Total Customer
- Total Account
- Amount

Excluding 0, keeping rules and consolidated results, for each result you multiply it with the result of your cubeA IsActive :
Amount = Amount * CellGetN(CubeA, Region, Product, 'IsActive');

Else you can, for the same view, check with a CellGetN if for the combination Region / Product "IsActive" is equal to 0 then itemskip it.

These are the two ways I can think of to do it.

Thanks for the comment. I know it is easy when exporting data with a process. But I want to create a report, rather than an extract. I prefer having an MDX that I can parametrize and use in a websheet report.

Indeed, I have a lot of measure elements in CubeA for which I want to filter cubeB data depending on ad-hoc reporting requirements. Hence I do not want to create seperate C level measures in cubeB (with C level rules). Moreover, some of the measures in CubeA are string measures rather than numeric measures. Hence I cannot apply the logic of having C level measures with C-level rules in CubeB for those.

Kind regards,

Okamasu wrote: ↑Fri Sep 28, 2018 12:14 pm Hello,

You could try it another way than using a MDX.

You could for example in your export script get a view with these criterion :
- Total Period
- Region
- Product
- Total Customer
- Total Account
- Amount

Excluding 0, keeping rules and consolidated results, for each result you multiply it with the result of your cubeA IsActive :
Amount = Amount * CellGetN(CubeA, Region, Product, 'IsActive');

Else you can, for the same view, check with a CellGetN if for the combination Region / Product "IsActive" is equal to 0 then itemskip it.

These are the two ways I can think of to do it.

I was thinking along the same lines as Okamasu,

create a new cube using TI and populate, so Cube A is the source view with skip zero and you cellget the budget amount for each period from Cube B, then report off the new cube

or

No new cubes and on the websheet make the active form look at Cube A and suppress zero so only the 1's show in the rows and create extra period columns to DBRW to Cube B

I personally can't see how MDX would help with your current set up

cheers, Mark

Maybe you can check this post : http://www.tm1forum.com/viewtopic.php?t=13434

I didn't know until I saw this post that it was possible to do an MDX using values of a cube as variables so I can't really help more than that sorry.

Okamasu

Here's what I would do:

1) Create a zero-suppressed active form report on Cube A. This would give you the rows where the IsActive value is 1. Add a VIEW statement for Cube B. For the rest of the report use DBRW formulas that point to Cube B and the new VIEW statement. The caveat is this is really only viable when the choice for Period, Customer and Account are title dimensions, or are fixed in columns.

Thanks for the comment. Yes, this could be done. But remember that my cubeA has 20million populated cells for IsActive measure. In my real case, I will have thousands of elements in each dimension. Hence performing a zero suppression on both dimension in Excel will not work. In my ideal report on CubeB, I do not have CubeA dimensions in rows or columns. I will have other dimensions of Cube B in rows and columns. But even if I have those cubeA dimensions nested in Rows, I have to filter my context based on other dimensions of CubeB to reduce the size of the report rows to focus my analysis.

tomok wrote: ↑Fri Sep 28, 2018 3:53 pm Here's what I would do:

1) Create a zero-suppressed active form report on Cube A. This would give you the rows where the IsActive value is 1. Add a VIEW statement for Cube B. For the rest of the report use DBRW formulas that point to Cube B and the new VIEW statement. The caveat is this is really only viable when the choice for Period, Customer and Account are title dimensions, or are fixed in columns.

Okamasu wrote: ↑Fri Sep 28, 2018 3:06 pm Maybe you can check this post : http://www.tm1forum.com/viewtopic.php?t=13434

I didn't know until I saw this post that it was possible to do an MDX using values of a cube as variables so I can't really help more than that sorry.

Okamasu

I am aware of those filter functions of MDX. In a subset of Region dimension, I can filter my Region list based on data in CubeA. In a subset of Product dimension, I can filter my Product list based on data in CubeA. Then I can use these subsets in CubeB report. If I use one of these subsets in a view of CubeB, it works ok. So far so good. But when I want to use both subsets in rows or both in columns, then it does not work. TM1 cube viewer gives an error message. It does not filter list in one dimension based on the filtered list in the other dimension, when the filtering is based on CubeA data.

Hi

Personally I would do this via Rules.

In SQL you can write SELECT * FROM TABLEA WHERE (A,B) in (SELECT A, B FROM TABLEB WHERE ACTIVE=TRUE.

A little digging around the internet turned up the following

SELECT
Hierarchize(crossjoin([Geography].[Geo].[Europe].children,[Product].[Prod].[icCube].children)) ON 0
FROM
( SELECT {([Geography].[Geo].[Switzerland],[Product].[Prod].[Personal]),([Geography].[Geo].[Spain],[Product].[Prod].[Partnership])} ON 0 FROM [Sales] )

I suggest that you do some more digging. TM1 doesn't necessarily have a full implementation of MDX, but it might be worth trying.

Regards

Paul Simon

In case you are still interested I found that you can use the MDX function LookupCube (https://docs.microsoft.com/en-us/sql/md ... rver-ver15)

An example MDX query can be found here (https://www.codemag.com/Article/0801051 ... s-with-MDX)