MDX Calculation Help

Jose Salinas

Hi everyone,

I have two cubes with the exact same dimensions. Cube one is the out of the box cube that retrieves general ledger total data and cube two is used for user inputs. From reporting standpoint, we can create reports in Application Studio that pull and stitch together data from both cubes by calling on same dimensions and then just calling on appropriate cube.

Now the issue... In cube one, we have several MDX calculations that we have created to simplify the calling of KPIs on our reports. I would like to call on these same MDX calculations for data on cube two. Is there an easy way to have Application Studio, recognize the MDX calculations in cube two, even though they have only been defined in cube one? I was thinking that these calculations lived in the dimension structures, since this is where they live in Application Studio. Based on this assumption, I thought that the MDX calculations would get recognized in any cube but that doesn't appear to be the case. It seems that the MDX calculations are cube specific.

By chance, is there a way to create MDX calculations that are "global" and recognized in any cube that is available for reporting or will I now need to look at another process to maintain KPIs since I want them to be recognized in the two cubes that I am using for reporting?

In the past, I have looked at cube rules but didnt use them because we have a nightly reload, which clears all of our cube rules. Based on my understanding, the rules live only in the cube itself. Due to this, the reload doesn't have a place that it can go back to retrieve those rules once they are cleared so they are lost. This being the case, I haven't used cube rules so I was hoping that there was another option within our reach. Thanks in advance!

Best regards,

Jose

martin-reischl

Hi José,

there is no way to make MDX calculations span over several cubes. In fact, MDX never accesses a dimension without the context of a cube. You will just have to implement the same calculations in the other cube.

The same is true for hypercube rules. But in most cases, rules will give you more flexibility and better performance than MDX calculations, so I would recommend you look into rules once more. Olap Modeling gives you an elegant way of keeping your rules in relational tables of your staging database, potentially copying them between cubes using a script. You can then publish your cube (with the rules) from Olap Modeling, again using scripts (or App Engine).

Best Regards

Martin