Hey Dude! Where's my filter?

By -
We all have to agree, MicroStrategy has a lot of features and offers a lot of control over them, but sometimes I feel I'm in the cockpit of a 747, with hundreds of knobs and switches and the worst part is: you don't know what you're going to break.


Several years back, after couple of months deep into MicroStrategy, happened many times, in a report, to add filters and yet, the SQL generated to be incomplete. Incomplete as in not all filters in the report are part of the WHERE condition. After hustling for a while, I finally discovered the missing piece of the puzzle. There's a hidden set of options, at the metric condition level, controlling the interaction of those filters with the report filters.


By default, MicroStrategy, will remove the report filter(s) based on the attribute being part of the same hierarchy, as the attribute used in the metric condition filter.

That's why you can also be blindsided, if you don't correctly set the attribute relationships (System Hierarchy), so that they are part of the same hierarchy. But, this can also be a good thing, because you are in control of deciding if you relate the attributes or you disable the option at the metric level. This really depends on the project's constraints and specificities

This is one example of the great flexibility the tool offers. Sometimes you feel overwhelmed, occasionally frustrated, by this kind of options present all over the place, but if I learned something about MicroStrategy, is that if you give it enough time, you'll get what you want out of it.

Comments

Post a Comment

Popular posts from this blog

The count distinct adventure - Part I

By -
Image
I've been working with several reporting and data analysis tools for a while now. Started with SSAS cubes, then Business Objects came along, later, MicroStrategy (MSTR) joined the team and then Tableau. They all have their own ways of managing count distinct (CD), but they yet have more in common. Manage simply means yielding the good results every time a user interacts with that CD measure. Us, the BI tech guys, we have a goal to facilitate easy data analysis to the users, especially that most of them work in non-IT domains and have different skill levels. Throughout time, CDs have proved to be a difficult thing to manage and if it wasn't the ease of implementation, it was the performance impact we soon discovered. It's been known that the difficulty lies in the nature of this non-additive measure. Basically, we cannot sum it as a standard revenue measure, but you always have to re-calculate it when the level of the aggregation changes. Have to admit that I like a goo...
Read more

The count distinct adventure - Part II

By -
During the first part of the topic we introduced the challenge of count distinct and 2 solutions to address them, along with their advantages and disadvantages. The list of solutions continues in this final section and these, presented below, are also the solutions that we use across our projects where CDs are required. Solution 3 - Count Distinct derived metrics This functionality is probably the most known by the users and mostly used, as it offers flexibility and the necessary control without the intervention of the IT teams. Creating =derived metrics in reports, Vis and documents is a walk In the park, using the context menus. With the deployment of V10, things got even more flexible adding support for conditional derived metrics (IF and CASE) by using attributes directly. As you probably remember, or still use today, up until V10, building conditional derived metrics was a hustle, as only metrics could be used with IF and CASE statements. You would first create a MAX(<...
Read more

The surrogate key strikes back

By -
If you ever participated in building a data warehouse or worked around data warehouses, you've probably came across the surrogate key (SK) notion. If you’re not familiar, then take a look at this article on the Kimball's group website. Surrogate keys are very common in data warehouses and they bring a lot of functionality, from design to performance enhancements. Now, one of the surrogate key's drawbacks is the difficulty to keep it in synch between all environments. As each environment can be linked to a different version of the source (e.g. UAT, live environments etc.), surrogate keys can get off for the same natural key and this can also have an effect in MicroStrategy. When building attributes you have to define its forms. The ID form should contain a unique key describing the attribute in order to provide correct joining, aggregation and attribute member list when applying filters. Indeed the surrogate key doesn’t have any business meaning, so this form w...
Read more