Database Passthrough Functions
MicroStrategy’s unquestionable strength is it’s SQL Engine. The ability to define an object layer over your data that empowers end users with no technical knowledge of the data structure to build any report is truly remarkable. Of course, it’s still just a set of options, and while it accomplishes probably 90% of use caes that you’d ever need, there’s still some room left for cases that it just doesn’t support (or support efficiently). MicroStrategy adds functionality over time through releases, but fortunately we don’t have to wait. Thankfully there are functions that allow us to slightly modify the behavior of objects by directly providing a snippet of SQL but without compromising the overall usefullness of the engine like we would if we turned to a full Freeform SQL Report. You’ve probably used one or two, but did you know there are five?
ApplySimple
This is the most common function, as you can use it in an Attribute definition. The only real limitation is that you can only reference columns from the same physical table (though you can get around that limitation by using Logical Views). Some things you can do with ApplySimples include Embedding HTML in Attributes, performing dynamic grouping similar to a Custom Group but with better performance, and building a fancy filter.
Filtering
You can drag an attribute into a filter like normal, choose an Attribute Form (like it’s ID), but instead of choosing the methods “Exactly” or “Elements in List”, you can choose “Custom”. This will allow you to type the ApplySimple function and write whatever SQL snippet you want. I generally use this trick if I need to set an ID equal to the results of a subselect, usually the result of a Max or a Join. MicroStrategy can generally generate this type of SQL, but I find using this method usually comes out with more efficient SQL, especially compared to using a Set Qualification Filter.
ApplyComparison
This type of passthrough can be used to create flexible filters. While this feature is similar to using an ApplySimple in a filter, the difference is that you can customize both sides of the condition, whereas with the ApplySimple you can only customize one side to be equal to a single Attribute Form.
ApplyAgg
This useful function is used in Metrics, and counts as aggregation. If you need to do some fancy aggregation or use a database function that MicroStrategy doesn’t natively support, this is your tool. Keep in mind that whatever you put inside an ApplyAgg will NOT be grouped by, so make sure that the SQL output will be valid.
Adaptive Metrics
A great example of the power of ApplyAgg is through the creation of Adaptive Metrics. These are metrics that use different aggregation functions at different levels. For example, you may need to do a Count() in the detail table and a Sum() in the aggregate table. One way to accomplish this is to use ApplySimple’s in the Fact creation to embed the aggregation function into the fact definition, and then use an ApplyAgg in the Metric. Since an ApplyAgg satisfies MicroStrategy’s need for an aggregation function, this results in the Fact controlling it’s own aggregation. Follow this tech note for the details to build your own. A neat trick!
ApplyOLAP
ApplyOLAP is used to define compound metrics. Despite the name, it only support Database Specific OLAP functions like Rank, Tile and RunningSum (assuming your DB supports those). The resulting metric is an OLAP metric. Personally, I’ve never used this one before and can’t even think of a use case for when you would.
ApplyLogic
This is a logical function that allows you to pass a condition down to the database level to conditionally return a value. Traditionally, we use the IF and CASE functions built into MicroStrategy for this, but if there is some special kind of comparison function you have (most likely a custom function you’ve built on your DB), then this would be an easy way to call it.
There aren’t any tech notes or documentation in the Product Manuals for ApplyOLAP or ApplyLogic, so if you have any tips or experience with them, feel free to share.
Comments
Post a Comment