Calling Stored Procedures from Microstrategy

Question

We have a stored procedure that takes in a set of parameters (from dimension tables) and then outputs a set of rows from which a report has to be created.

Till now this is done in a .NET app using an ORM, but is it possible to integrate it with MicroStrategy?. How do I pass the selected params (from a report prompt) to a Stored Procedure on the database and then map the results back to the report?

Answer 1

In the past I did a Free Form SQL (FFSQL) report in MicroStrategy on top of a PL/SQL function which returned a 'table' (Of course that was an Oracle database, more info about returning a table with a function here).

So what you are looking for is something doable.

• You can use the prompts in FFSQL report as parameters for your function
• Then you have to map the columns of the returned table as attributes and metrics returned by the FFSQL report

This is something I did in a very old MicroStrategy implementation to provide writeback functionality: the users were allowed to update some values in some tables and the function returned a Success or Error message.

As you can imagine this was not a standard Data Warehouse solution, it was more an hoc solution for an operational database.

My suggestion is to avoid similar solution if not necessary and prepare in advance the tables/rows that you need. Of course in your case you have already the procedure ready so you have just to figure out how to combine it with MicroStrategy.

Some useful readings:

TN37783: Instructions to use stored procedures within Freeform SQL reports in MicroStrategy 9.x against different databases

Using Prompts in Freeform SQL Reports