Search
  • Ryan Forsythe

Leveraging Table-Valued Functions (TVF’s) in MicroStrategy

By Ryan Forsythe

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:

https://community.microstrategy.com/s/idea/087440000008rVPAAY/detail


Example

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.


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 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);


MicroStrategy

1. Create the prompt objects for the prompted values.



MicroStrategy Value Prompt



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.




Result




Ryan Forsythe

AllScient

www.AllScient.com

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.

10 views

AllScient

1005 Jarvis Court

McLean, VA 22101

 

(888) 909-8180 toll free

FOLLOW US:

  • Twitter App Icon
  • LinkedIn App Icon

© 2020 by AllScient