Leveraging Table-Valued Functions (TVF’s) in MicroStrategy
February 19, 2020
If you haven’t come across Table-Valued Functions before, this may be the time to evaluate some use cases. Table-Valued Functions or TVF’s is a programmability function supported within a number of database platforms. Similar to User-Defined Functions (UDF) and Stored Procedures (SP), TVF’s allow the developer to provide more control from a database perspective. TVF’s 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 MicroStrategy only supports this for SAP HANA as an Architected Logical Table. (http://www2.microstrategy.com/producthelp/Current/SAP/en-us/Content/SAP/Calculation_View_Input_Parameters.htm). So we must use Freeform SQL reports in MicroStrategy for all other databases. 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 TVF’s 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.
**Please vote to help make this part of the platform:
Here is an example using the MicroStrategy Tutorial Warehouse database using Microsoft SQL Server. This assumes that based upon ones analysis that this query needs some tuning that is not readily out-of-the-box with MicroStrategy 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.
CREATE OR ALTER FUNCTION STATE_AGE_AGG_TVF (@year_id int)
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 JOINlu_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
The query statement:
select * from STATE_AGE_AGG_TVF(2016);
1. Create the prompt objects for the prompted values.
2. 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.
Ryan has delivered Business Intelligence and Analytics consulting to over 50 companies and organizations. He is a MicroStrategy Certified Master Analyst (MCMA) and MicroStrategy Community Master. He graduated from Boston College with a degree in Business and concentrations in Finance and Computer Science.