Calculated Element in OLAP Cube

#1

Is it possible to create a calculated element in a hierarchy from an OLAP cube datasource? If so, could someone point in the right direction? Thanks!

#2

Calculated elements are a feature that can only be used with Dundas cubes, as they are meant to be a method of consolidating/modifying data pulled from a source. OLAP cubes are just a raw data source, so you can’t manipulate data directly at the source level from within Dundas.

Formulas and calculated elements generally use the same expressions and are built off the same programming language (DundasScript), so your best bet for something similar to calculated elements would be to add formula measures at the metric set level.

https://www.dundas.com/support/learning/documentation/analyze-data/formulas/adding-formulas

#3

Thank you for your response Christian. I am familiar with formulas however I am unfamiliar with a way to use them to address the task I have been assigned to complete.

It may be a bit strange to describe and to understand, but I will do my best. I have a dimension which splits a measure into several columns. I would like to form a calculation based on two of these columns and display them in a separate column all on their own.

  1. Can formulas be used to display what I am trying to do? Any calculation I can come up with adds itself to all columns. I need a separate column.
  2. Can formulas reference a distinct member of a hierarchy? For Example, $[Location].[Location].[LOCATION NAME]$
  3. Can formulas reference a distinct cell? For Example, ($[Location].[Location].[LOCATION NAME]$,$[Measures].[Measure Name]$)

This will be a frequent occurrence across many dashboards in my product which uses OLAP datasources, however, I am not sure that it is possible. I have not seen any examples but it would be very helpful if I had one.
Thank you for your answers.

#4

Apologies for the delay! Anyways, to answer your questions.

  1. This wouldn’t really be recommended as formulas are meant to be repeatable on a row by row basis. You wouldn’t be able to just iterate over a column dimension on a per data point basis without having to create several invisible columns to track this. What I would recommend doing instead is creating a pivot table at the cube level so each column dimension can be converted into a measure column, which would make the formula at the end much easier.

    https://www.dundas.com/Support/learning/documentation/data-metrics/transforms/other/pivot

  2. Yes, this is possible. A hierarchy has four possible fields that are exposed to a formula bar - MemberTime, MemberNumber, UniqueName, or Caption.

    image

    So you would do something like this - $HierarchyName$.Caption. From there, you can use an advanced formula and an if statement when you need to reference a specific member.

  3. Given that metric set formulas are meant to be repeating over dimensions, this would not be recommended. But you could do so by checking every possible dimension for a specific member and then checking the measure in question. But again, this wouldn’t really be a recommended course of action, as you’d limit yourself to one cell per formula if you needed to do this multiple times (as somewhat alluded to in part 1).

1 Like
#5

Thank you Christian,

In an advanced formula, as you mentioned in #2 above, I could only reference a distinct member but not two distinct members and say - divide them. I could probably come up with a script that would make sense logically, but it would not work because it would be limited to only one dimension member, since there can only be one dimension member per hierarchy per row/column. Correct?

In reference to number 3, I am unsure of what you mean by checking every possible dimension for a member? Basically what I am wondering is, could I do a percent of a total or similar?

#6

For the first part, you are mostly correct. The purpose of a formula measure is to apply a single formula on a repeating row by row basis.

Referencing a value for a singl emember would look something like this: advanced formula:

if ($HierarchyName$.Caption == "string") {
    return $Measure$;
}
return null;

Of course, this will result in your measure being a null everywhere except for the one hierarchy member you specified, where you have the actual value. so if you wanted to do something like this in your case where you want to divide a specific measure cell by a specific measure cell, you would create three formulas. Two of them would be exactly like the above, just modified to reference a different measure/hierarchy member. Then formula 3 would just be something like

$formula1$ / $formula2$

If you would like to do a percent of a total, you can use this formula:
https://www.dundas.com/Support/learning/documentation/design-view/formulas/standard/percent-of-total

#7

Christian,

Thank you for the percent of total calculation. I am terribly sorry for all the back and forth but I am still not sure that this is possible even with the formula you mentioned. Like I said in my previous post, formulas can only rest inside of the distinct hierarchy member. Is the dimension which you are formulating on a slicer? If not, then I do not see how this can work. Please see the screenshot below to better understand my dilemma. The formulas that you see labeled plan only and actual only are built similar to what you listed above.

Any formula that I try and build off of either of these two tables never results in the way that you describe. It can only divide by zero because of the way that Dundas formulas are designed. It simply places a new formula in both areas and is null or zero or infinity depending on the many variations of calculations I have attempted to perform.

Dundas_Formula

#8

Ah, apologies. I totally forgot my own little disclaimer that formulas are meant to take things ona row by row basis.

Here is the formula you should be using for the division (the third one):

SUM($formula1$) / SUM($formula2$)

Dimensions that are in slicers cannot be used in formulas (well, at least should not be) as they aren’t grouping or splitting data up. It’s just filtering what you already see.

1 Like
#9

Christian!! Excellent, that worked -

When it stays on a single row. As soon as I start add another dimension to this row (it’s necessary use), the sum calculation grabs every plan / actual from every row. Is there a way to keep the formula distinct to it’s parent row?

Thank you!

#10

In that case, keep in mind that each data point is each unqiue instance of a hierarchy1 -> hierarchy2 pair. In other words, think of it as a multi level hierarchy. We only specify data points for child members. Every parent’s All member is considered an aggregated total. As such, your best bet would be to set up a custom total rule formula.

https://www.dundas.com/support/learning/documentation/analyze-data/using-a-custom-total-calculation

You’ll want to edit the measure from your data analysis panel and then set up a custom total rule to have the numbers you’d like show up.

Of course, if you add more hierarchies in a situation like this where this is a use case the formula measure isn’t best optimized for to begin with, you will need to take more things into consideration. As such, you may want to consider just having separate measures for Actual/Planned at the data cube level to simplify the process immensely. In cases like this, this generally involves doing an pivot on the source data.

https://www.dundas.com/Support/learning/documentation/data-metrics/transforms/other/pivot

1 Like
#11

I figured as much! Thank you Christian!!

Ryan