MSTR Passthough Functions; Applysimple, ApplyComparison, Applyagg, ApplyOlap

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

Order of tables appear in the SQL generated by the MicroStrategy SQL Generation Engine

Order of tables appear in the SQL generated by the MicroStrategy SQL Generation Engine By default, the MicroStrategy SQL Generation Engine orders the tables in the following way( FMRL ): Fact Tables Metric Qualification (MQ) Tables Relationship Tables Lookup Tables

HyperIntelligence Training Videos

HyperIntelligence Training Videos Design and build hyper cards Optimizing Datasets for HyperIntelligence Using the HyperIntelligence for Office Outlook Add-In Building HyperIntelligence Cards Using HyperIntelligence for Mobile on Android Deploying HyperIntelligence for Outlook Insights On-The-Go: HyperIntelligence for Mobile Building HyperIntelligence Profile Cards Designing Custom HyperIntelligence Cards Using the Calendar with HyperIntelligence for Mobile

Customers Who Live in the Same City as Call Centers

Customers Who Live in the Same City as Call Centers Your new utility company has call centers located throughout the country, and your recent surveys indicate that customers who live in the same city as a call center are particularly satisfied with service due to extremely rapid repairs during power outages. To begin your new advertising campaign, you want to generate a list of Call Centers that coincide with Customer Cities. The following steps create an attribute-to-attribute qualification filter that generates the list of desired cities. To Create an Attribute-To-Attribute Qualification that Compares the Call Center and Customer City Attributes In MicroStrategy Web, log in to a project. Open any folder page (for example, click Shared Folders on the home page). Click the Create Filter icon . From the Object browser on the left, select the Customer City attribute from the Customers hierarchy and drag it to the filter pane on the right. Change Qualify...

Administration standards to optimize network data transfer speeds

Optimization standards to help developers work with the System Administrator to optimize network data transfer speeds: Place all server components in the same segment to minimize latency between the Intelligence Server, Web server, data warehouse server, and metadata server Expand bandwidth and minimize latency between servers and clients to ensure that bottlenecks do not occur when data is requested over your network Use HTTP compression between the Web servers and Web clients to achieve maximum throughput between these components Configure a Web proxy server to handle caching and reduce the load on the Web server

Derived metric based on attribute values

Derived metric based on attribute values Here is how could create and display data correctly on using below simple steps. Create a report with Category, Subcategory and Revenue. Create New Metric in a report or VI. Case((Category@ID = 1), Revenue, 0) Booksand Name it as Revenue for where 2 is Category ID for "Books" Report will display result as below. Result for new metric is blank. Now to fix this create a new Derived metric on Category attribute first with formula as Max(Category) {~ } and calling Books Now Edit the "Revenue for Books metric and Replace Category@ID with this new Books metric formula would looks like this Case((Books = 1), Revenue, 0). Report result would now display as expected as shown below

MicroStrategy URL API Parameters

MicroStrategy URL Structure The following table summarizes the root URL structure used for every request to MicroStrategy Web. Environment Main Application URL Administration URL J2EE http://webserver/MicroStrategy/servlet/mstrWeb http://webserver/MicroStrategy/servlet/mstrWebAdmin .NET http://webserver/MicroStrategy/asp/Main.aspx http://webserver/MicroStrategy/asp/Admin.aspx Every request sent to MicroStrategy Web calls a central controller. Parameters are appended to Main.aspx or mstrWeb (in a .NET and J2EE environment, respectively) to indicate to the controller how the request should be internally forwarded and handled. The following examples show a URL for accessing a MicroStrategy folder when the user does not have an existing session. The URL contains not only the parameters needed to connect to MicroStrategy Web, but also the parameters needed to log on and create a session. J2EE environment: <a href="http:...

Case functions Microstrategy

Ca se functions Microstrategy Case functions return specified data in a SQL query based on the evaluation of user-defined conditions. In general, a user specifies a list of conditions and corresponding return values. Case This function evaluates multiple expressions until a condition is determined to be true, then returns a corresponding value. If all conditions are false, a default value is returned. Case can be used for categorizing data based on multiple conditions. This is a single-value function. Syntax Case ( Condition1 , ReturnValue1 , Condition2 , ReturnValue2 ,..., DefaultValue ) Example Case(([Total Revenue] < 300000), 0, ([Total Revenue] < 600000), 1, 2) sum(Case (Day@DESC in (“Sat”,”Sun”), Sales, 0) {~+} Sum(Case(Category@DESC In("Books","Electronics"),Revenue,0)){~+} CaseV (case vector) CaseV evaluates a single metric and returns different values according to the results. It can be used to perfo...

Personalizing file locations, email and file subscriptions using macros in Microstrategy

Personalizing file locations MSTr allows to dynamically specify the File Location and Backup File Location in a file device using macros. For example, if you specify the File Location as C:\Reports\{&RecipientName}\ , all subscriptions using that file device are delivered to subfolders of C:\Reports\ . Subscribed reports or documents for each recipient are delivered to a subfolder with that recipient’s name, such as C:\Reports\Jane Smith\ or C:\Reports\Hiro Protagonist\ . The table below lists the macros that can be used in the File Location and Backup File Location fields in a file device: Description Macro Date on which the subscription is sent {&Date} Time at which the subscription is sent {&Time} Name of the recipient {&RecipientName} User ID (32-character GUID) of the recipient {&RecipientID} Distribution Services add...

Microstrategy Custom number formatting symbols

Custom number formatting symbols If none of the built-in number formats meet your needs, you can create your own custom format in the Number tab of the Format Cells dialog box. Select Custom as the Category and create the format using the number format symbols listed in the table below. Each custom format can have up to four optional sections, one each for: Positive numbers Negative numbers Zeros Text Each section is optional. Separate the sections by semicolons, as shown in the example below: #,###;(#,###);0;"Error: Entry must be numeric" For more examples, see Custom number formatting examples . To jump to a section of the formatting symbol table, click one of the following: Numeric symbols Character/text symbols Date and time symbols Text color symbols Currency symbols Conditional symbols Numeric symbols For details on how numeric symbols apply to the Big Decimal data type, refer to the Project Design Guide . ...

Create an alert-based subscription in MicroStrategy Distribution Services

Create an alert-based subscription in MicroStrategy Distribution Services on Web Subscription to a report or Report Services document which will be executed when a certain conditional threshold is met based on another executing report. For example, a scheduled report executes which shows the Revenue by day for the past week. If the Revenue on any one day falls below a certain value, a subscription to another report or Report Services document can be triggered and delivered to a recipient. An alert based subscription can only be created directly on a report; however, another report or Report Services document can be delivered when the alert based subscription is triggered. Note: you need a grid report to create an alert and you cannot create if you want to create on a document with text boxes. The following example will walk through the basic steps on how to setup a subscription based on an alert like this: Follow the brief steps bel...

Microstrategy Analytics

Search This Blog