MDX query using more than one cube.
- mce
- Community Contributor
- Posts: 352
- Joined: Tue Jul 20, 2010 5:01 pm
- OLAP Product: Cognos TM1
- Version: Planning Analytics Local 2.0.x
- Excel Version: 2013 2016
- Location: Istanbul, Turkey
MDX query using more than one cube.
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,
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,
-
- Posts: 12
- Joined: Thu Sep 13, 2018 12:38 pm
- OLAP Product: IBM Cognos TM1
- Version: 10.2
- Excel Version: 2010
Re: MDX query using more than one cube.
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.
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.
- mce
- Community Contributor
- Posts: 352
- Joined: Tue Jul 20, 2010 5:01 pm
- OLAP Product: Cognos TM1
- Version: Planning Analytics Local 2.0.x
- Excel Version: 2013 2016
- Location: Istanbul, Turkey
Re: MDX query using more than one cube.
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,
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.
-
- Community Contributor
- Posts: 292
- Joined: Tue Sep 06, 2016 7:55 am
- OLAP Product: TM1
- Version: 10.1.1
- Excel Version: Excel 2010
Re: MDX query using more than one cube.
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
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
-
- Posts: 12
- Joined: Thu Sep 13, 2018 12:38 pm
- OLAP Product: IBM Cognos TM1
- Version: 10.2
- Excel Version: 2010
Re: MDX query using more than one cube.
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 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
-
- MVP
- Posts: 2831
- Joined: Tue Feb 16, 2010 2:39 pm
- OLAP Product: TM1, Palo
- Version: Beginning of time thru 10.2
- Excel Version: 2003-2007-2010-2013
- Location: Atlanta, GA
- Contact:
Re: MDX query using more than one cube.
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.
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.
- mce
- Community Contributor
- Posts: 352
- Joined: Tue Jul 20, 2010 5:01 pm
- OLAP Product: Cognos TM1
- Version: Planning Analytics Local 2.0.x
- Excel Version: 2013 2016
- Location: Istanbul, Turkey
Re: MDX query using more than one cube.
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.
- mce
- Community Contributor
- Posts: 352
- Joined: Tue Jul 20, 2010 5:01 pm
- OLAP Product: Cognos TM1
- Version: Planning Analytics Local 2.0.x
- Excel Version: 2013 2016
- Location: Istanbul, Turkey
Re: MDX query using more than one cube.
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.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
- paulsimon
- MVP
- Posts: 808
- Joined: Sat Sep 03, 2011 11:10 pm
- OLAP Product: TM1
- Version: PA 2.0.5
- Excel Version: 2016
- Contact:
Re: MDX query using more than one cube.
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
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
-
- Posts: 1
- Joined: Sun Feb 05, 2017 3:43 pm
- OLAP Product: TM1
- Version: 10.2.2
- Excel Version: 2013
Re: MDX query using more than one cube.
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)
An example MDX query can be found here (https://www.codemag.com/Article/0801051 ... s-with-MDX)