The Masters Blog: Leveraging Table-Valued Functions (TVFs) in MicroStrategy

If you haven’t come across Table-Valued Functions before, this may be the time to evaluate some use cases. Table-Valued Functions (hereon referred to as "TVFs" for brevity) are a programmability function supported within a number of database platforms. Similar to User-Defined Functions and Stored Procedures, TVFs allow the developer to provide more control from a database perspective. TVFs are supported in the following databases (this is not a comprehensive list): SQL Server, Oracle, MySQL, MemSQL, Snowflake, and SAP HANA.
Overall, a TVF is effectively a parameterized view. A view, in itself, can accept a “where” clause, and the database engine optimizer will determine how to best apply that “where” clause in relation to the query. A TVF will accept one or more input parameters that can be used in the “where” clause or in other methods.
The value of a TVF can be leveraged in several cases:

• Forcing a given filter or set of filters for a query
• Applying a default value when a parameter is not specified
• Managing the database query optimizer
  
• Applying the filter at a specific level where a bloom filter may not be used in the explain plan
• Including hints or specific join orders
• Managing complex logic statements that include a variable
• Report-specific security filter

Effectively, I would use a TVF in a case that I need to take things to a next level of performance tuning for a real-time, highly concurrent query.
The one downside is that Strategy only supports TVFs for SAP HANA as an Architected Logical Table. Therefore, to maintain this level of manual control in other databases, we must use Freeform SQL reports in Strategy. I generally avoid (despise, loathe, abhor) the use of Freeform SQL reports because it embeds logic for a given database, mitigating the settings for the DB Instance and platform portability. This is an exception since a given platform should support continued use of TVFs in future versions, a number of other databases support them using the same syntax, and it is easy to refactor at the database level.
Even though this can be solved, it would be great to have this function supported as part of the platform.
**If you think this would be a good enhancement as well, definitely give that idea an up vote.
Example
Here is an example using the Strategy Tutorial Warehouse database using Microsoft SQL Server. This assumes that, based upon one’s analysis, this query needs some tuning that is not readily out-of-the-box with Strategy VLDB settings, and the data structure doesn’t provide a date of birth—only the current age—so this query also would accept only one year as an input so that we can adjust it based on the time of the order.
Database

CREATE OR ALTER FUNCTION STATE_AGE_AGG_TVF (@year_id int)
RETURNS TABLE
AS
RETURN (
select max(@year_id) YEAR_ID, cty.CUST_STATE_ID, st.CUST_STATE_NAME, avg(c.age_years - (year(current_timestamp) - @year_id)) AVG_AGE
from lu_customer c
INNER MERGE join (select customer_id from order_detail o
		where year(order_date) = @year_id ) o
on c.CUSTOMER_ID = o.CUSTOMER_ID
INNER JOIN lu_cust_city cty
	on c.CUST_CITY_ID = cty.CUST_CITY_ID
INNER MERGE join LU_CUST_STATE st
	on cty.CUST_STATE_ID = st.CUST_STATE_ID
GROUP BY cty.CUST_STATE_ID, st.CUST_STATE_NAME
);

select * from STATE_AGE_AGG_TVF(2016);

• Create the prompt objects for the prompted values.
  
  

• I will mention again: as much as I hate using Freeform SQL, there are some use cases. We will create a Freeform SQL report that calls the TVF.
  
  

• Insert the prompts in the report.
  
  

Result

Ryan Forsythe
AllScient
www.AllScient.com
Ryan has delivered Business Intelligence and Analytics consulting to over 50 companies and organizations. He is a Strategy Certified Master Analyst (MCMA) and Strategy Community Master. He graduated from Boston College with a degree in Business and concentrations in Finance and Computer Science.