Let's talk about Dimensions and Measures
Dimensions and measures are the two central building blocks in multidimensional reporting. In Pyramid Analytics, they are mainly dealt with in the “Model” and “Discover” modules. However, we have noticed that Pyramid treats the dimensions and measures differently beyond their “normal differences”, which leads to difficulties, especially with large data models, or simply makes the models confusing.
I would like to understand whether there are reasons for these differences that I have perhaps not yet seen, or whether it makes sense to suggest possible improvements here.
Differences
- Dimensions are mapped in Pyramid's Dimension Tree as folders that can contain multiple hierarchies or attributes. The folder name and therefore the name of the dimension always corresponds to the name of the respective table in the data model. In contrast, the names of the folders in which the measures are contained can be given any name - regardless of the corresponding fact table in the data model.
Extensions to the data model, i.e. “Custom Columns” for the dimensions and “Business Logic” (formulas) for the measures are displayed differently. The former are always visible below the corresponding dimension folder, the latter must first be activated/shown and are then sorted in the Tree View according to their CMS folder structure (not their measure folder structure).
When searching for measures/business logic, the Tree View is no longer available - there is then only the List View, which displays the CMS folder as additional information behind the search result.
...
Background information
[We use Direct Queries on our EXASOL data mart for the centrally provided data models.]
The differences listed as examples naturally have an impact on working with the data models. For example, we cannot place hierarchies and attributes in one folder for technical reasons if they originate from different tables! Or when modeling, we would have to take into account how the user should see the objects later in Discover. Sometimes we create a separate view in the model for such objects from different tables so that the objects appear in one folder.
Another problem: The names of the measure groups correspond to the (technical) names of the tables in the data model and not to the names of the measure folders, i.e. the users find a name in the list of measure groups that is unknown to them and cannot assign it! The same applies to the assignment of a custom column to a dimension: Here, the entirety of the (technical) table names is available to the users and not the names of the dimension and measure folders!
After all: We live and breathe self-service! Our users build lots of their own objects, Custom Columns and Business Logic. It is therefore clear that a large number of objects exist multiple times - some with identical names, some with different names or even with identical names but with adapted calculation rules.
In order to maintain an overview here, it would be helpful if we had more options in the organization of the models - e.g. a functional grouping of hierarchies and attributes instead of a technical one, or the option of sorting objects in a way other than alphabetically. We have a whole series of feature requests in mind, but wanted to put the topic up for general discussion first: Have we overlooked something or not yet understood it? And how do others do it?
I look forward to a lively discussion!
Micha
2 replies
-
IMO, Pyramid's interface for traversing the semantic model is easily one of the best in the market, if not the best.
Regardless, there are a variety of claims in the posting that need to be challenged:
- Measures are NOT the same thing as attributes and hierarchies. It's not a technical issue, its a LOGICAL one.
- Power BI blends measures and attributes together under a dimension in its tree. And what an incredible pain in the a** it is to find and use the measures in PBI! Their design is incredibly myopic, because it ignores the fact that real measure calculations usually run across multiple attributes. (If you search the internet, you'll find all kinds of hacks in PBI to create fake tables to house the measures in one location.) Likely they chose this path because of the numerous limitations in the calculation tiers in PBI. Pyramid, like Tableau and a variety of other tools, keep them separate. Changing this would be an incredible regression in Pyramid's design IMO. And it would totally work against self-service.
- There is a way to change from the tree mode of the dimensions and attributes to a flat UI mode in Pyramid. This is specifically suited for smaller models for non-technical users, if people find the tree approach too overwhelming.
- Attributes and hierarchies within a dimension can already be grouped into custom display folders. The same for measures. PBI has the exact same thing. (Grouping dimensions into a super set of folders could make sense - but it can also make it harder to find things.)
- Another organization mechanism is Measure Groups, which groups related dimensions and measures. They are very relevant in large, complex semantic models, where there is a huge variety of unrelated dimensions and measures. Measure groups are generated by the fact tables housing exposed base measures and are therefore named after them. They have nothing to do with measure display folders. Pyramid exposes this grouping mechanism to allow users to find related items in the model on any data source including SSAS. (PBI does not fully expose or utilize "Measure groups" unless they are built into cubes on the server versions of SSAS by a visual studio developer! )
- It's possible to expose hierarchies under any dimension, and they can use different columns from different tables/dimensions within the hierarchy.
- It sounds like there is a misunderstanding of the difference between 3 calculation concepts. Custom columns effectively generate new attributes - that's why they appear in the dimension tree. Their operation in querying (with joins etc.) is functionally influenced by the parent dimension. Formulas, on the other hand, can generate measures which are NOT attributes - that's why they appear in the extended measure tree. They are NOT influenced by a parent dimension. Last calculations can generate custom 'members' - which are formulaic elements. They come from within the attribute - they are NOT attributes, and it's why they appear in the extended element tree. (Power BI supports custom columns and measures only - neither of which are easy to create or find for the vast majority of users.)
- Complaints about the search mechanism are misplaced. Most search engines return results as a flattened list without the folder structure. Try the search in the ubiquitous Windows Explorer folder tool to see what I mean.
-
THANK YOU, , for taking so much time and writing a very detailed answer! I really appreciate it very much. Nevertheless, I was a bit startled at first because I was pretty sure I hadn't made any “clams” or “complaints”. And by the way: I really only know PBI very marginally. But all this is perhaps also due to my poor English - and ultimately I wanted a lively discussion. So, here it is!
First of all, maybe we can clear up a misunderstanding: I know the difference between dimensions and measures - and I also like the way PA presents them!
Let's take my first point: when I create a dimension in the model, the Discover user finds it in a location that is definitely in or below (= display folder) the corresponding table of the data model. If, for technical reasons, I keep information on a central entity in several tables, the Discover user will also find it in different folders. This means that the technically motivated table view is carried over into the presentation for the user, which should actually be exclusively semantically motivated!
We would actually like to see more flexible modeling here - for example, the way BusinessObjects had implemented it in its universes. But before we make any demands or open a feature request, I wanted to understand why it was implemented this way!
In contrast to the dimensions, during model creation I can freely choose where the Discover user will later see the measure! That's what I meant by the “differences”: When modeling two similar (not the same!) concepts, I can completely freely determine the display folders for the measures, but for the dimensions only below the folder specified by the table name.
Or to my points 2 and 3: If I search for dimensions or custom columns, I get the search result in the view that I have also selected for the normal display of the dimensions and custom columns - i.e. as a list or as a tree. That's perfect! If I search for measures, the same applies. Great! Only when I search for Business Logic, the Tree View is suddenly deactivated. Why is that? That's what I meant by the "differences".
One last point: Measure Groups. Honestly, I only discovered this concept when I started this post. And I find it really helpful, especially - as you write - for larger models like we often have!
But if a Discover user wants to select or exclude a measure group, he is confronted with a list that shows the technical names of the corresponding (fact) tables, which he is generally not likely to know. I understand (by now) that a selection via the measure folders would not make sense, because the dimensions to be shown/hidden are linked to these tables and not to the measure folders. But a selection via a confusing list (see screenshots from a real project) would not either. So here the pleasant advantage of being able to sort measures completely freely into any folder becomes a disadvantage.
Let's see what others have to say! And thank you once again!
Micha