Difference between the calculations
Currently, I am diving deep into more of the calculations, and this brings me to a confusion that I would like to understand. I would like to know the differences between these calculations and how Pyramid perceives them.
I have a grid which looks like the below screenshot, and this grid shows the Sales amount for each of the cities of the Philippines country.
As you can see, I have put 5 extra Sales calculations in the dropzone, and I need to understand how each of these calculations is translated in Pyramid, and how they are different from the simple Sales measure. The definitions of these calculations are as follows:
- Sales Calc - 1: sum(sum([measures].[Orders Sales], [Orders].[Country/Region].CurrentMember))
- Sales Calc - 2: sum([measures].[Orders Sales], [Orders].[Country/Region].CurrentMember)
- Sales Calc - 3: ([measures].[Orders Sales], [Orders].[Country/Region].CurrentMember)
- Sales Calc - 4: ([Orders].[Country/Region].CurrentMember, [measures].[Orders Sales])
- Sales Calc - 5: SUM([Orders].[Country/Region].CurrentMember, [measures].[Orders Sales])
I would also like to know since I am calculating these Sales for each row, does it make sense for me to use the CurrentMember function? In what exact scenario, this CurrentMember is suitable if I am not trying the filter my grid for any country/city?
8 replies
-
Hi ,
I think that the key is to understand the building blocks and the functions.
The building blocks are:
1. attribute and hierarchies - [table].[column] and [table].^[hierarchy]
2. members - elements of an attribute or hierarchy, for example [product].[category].[bikes]
3. measures - aggregated columns. for example [measures].[order sales] is probably sum(order.sales) from within the DB.
4. sets (lists) - an enumeration of members or measures, for example {[product].[category].[bikes], [product].[category].[clothing]}
5. tuples - a combination of elements and a measure, which will be the measure's value for the give elements. for example ([date].[year].[2025], [measures].[sales]) would be the sum of sales in 2025.
6. functions (prefix and suffix) - sum(4,5) or [product].[category].currentmember.
You have used in your examples attributes, measures, tuples and 2 functions (sum and currentmember).
The currentmember functions returns the member of the attribute which is in the cell of the grid that you are looking at. So within the view of this grid, it would simply always be [Orders].[Country/Region].[Philippines].The sum function has several variations:
1. sum(list, numeric value) - which will calculate the value of each member for the list and then sum them
2. sum(column) or sum(measure) - which will execute a SQL sum function on the given column: sum(sales)
3. sum(numeric value1, numeric value2....numeric value N) - which will sum all the values passed as arguments.
You did not use a list, or a single column, so the function that matches your expression is the last one, for all the sums you wrote above.Calc3 and Calc4 are the most simple. They are the value of the sales measure, for the current region. Most calculations are also injected with the rest of the grid context implicitly, so you can think of the expression as being (sales, country.currentmember, city.currentmember) or just (sales). And also, the order of elements in tuples doesn't matter.
Calc2 and Calc5 are the sum of: the sales measure and the current country (sum(X,Y)). Same as [measures].[orders sales] + [orders].[country/region].currentmember. When a measure value is not specified for a member (such as this case, where you did not say what the value of the country added to the sales should be) then we use the default measure.
So the calculation is [measures].[orders sales] + ([orders].[country/region].currentmember, [measures].defaultmember)
You can check in the model what the default measure is.
Calc1 is sum(calc2) - which is the sum of 1 number, meaning it's simply that number.
I hope this answers your question clearly, and perhaps adds some extra insight into how the expressions are built and evaluated.
Imbar-
It surely did, and now I am more curious to know if there is any thorough study material for this. Also, I saw somewhere the different parenthesis in use. I believe the "{}" used for set. Can I get some examples of different use-cases that can provide me insight on when to use what, I mean set, list or tuple? An example would be really helpful.
Thanks again.
- Shuvankar
-
this is a good start.
-
I have another ask. I saw these two calls yield two diff results:
- sum([Orders].[Country/Region].CurrentMember, [measures].[Orders Sales]) --> Sales Calc - 1
- sum({[Orders].[Country/Region].CurrentMember}, [measures].[Orders Sales]) --> Sales Calc - 6
I understood that the 1st calc uses the default measure (which is Quantity) for the CurrentMember scope, producing a different result.
- For the 1st Row, the Sales Calc -1 gets translated as= SUM(Default Measure + Sales) = (10 + 873.41) = 883.41
I wanna know what happens in the 2nd case. How do the tuple and curly braces define the scope? I saw somewhere that AllMembers is paired with sometimes in "()" braces and sometimes with "{}". I thought both of these parenthesis could be used interchangeably. Can you help me out with the difference?
Thanks,
Shuvanar
-
( ) are used for functions and for tuples.
Sum(4,5,6) -> add up 4, 5 and 6
([product].[category].[bikes],[measures].[sales]) -> give the sales value filtered by bikes
{ } is how you define a set of members (see sets example in the comment above), so Calc6 matches the 1st sum signature in the example above.
This means that the sales is calculated of each member in the set (and there is only 1 member, the current member - the one from the grid) and the summed.
So calc6 is basically less efficient way of simply calculating the basic sales measure.
The AllMembers is a function which given an attribute's name will return a set of the items of the attribute.
For example AllMembers([product].category]) or [product].[category].AllMembers (both valid).
You can write { [product].[category].AllMembers } which is a set containing the output set of the allMembers function - but it's redundant, since it's basically the same. -
Do you recommend that I learn the MDX of SSAS? Would that help?
Thanks,
Shuvankar
-
PQL and MDX do share many similarities, so MDX is a good reference for most things - mainly at the semantic layer which Ian mentioned in his comment.
However, MDX does not have a Granular layer at all, nor a context layer in the same way that Pyramid considers it.
You can see NPANS's link and this one, into the Pyramid help site, for more explanations on the different calculation layers, the object types and their syntax, and the list functions with their detailed behaviors and examples.
-
-
Hi
I thin it is also important to understand the three different levels of calculation in Pyramid. there are
- Granular Calculations
- Semantic Calculations
- Context Calculations
Granular calculations operate on the underlying table of data at the row level. that can be specified either as virtual columns defined in the Model, or dynamically at run time using the "Custom Columns" feature of Formulate. This effectively allows a user to dynamically extend the Semantic Model at run time by adding new Dimensions (Attributes) or Measures.
Semantic calculations operate on the Semantic Model. I.e. a unique list of members in each Dimension or the aggregated Measure based on the aggregation defined in the model. Members of a dimension effective define the "View" of the measure when used in calculations.
Context calculations work on the output of a query. they do not access the model directly, rather operate on the results of a query and are restricted to that data only.
It could be argued that a "fourth" type of calculation is that used in present especially is Dynamic Text, which allows access to the query results based on a cell reference approach to the output grid of data, but this is completely separate from the query engine.
All of your examples are Semantic calculations.
Hope that helps.
Ian