Thanks to binaryworld.net , I was finally able to get a sql way to calculate XIRR. After 2 long hours of search I found this site and the logic as well as the code works perfectly well! XIRR is a function in excel that calculates Internal Rate of Return based on payments/income over a period of time. Without further ado, here is the code (a slightly modified version from BinaryWorld.net. Happy XIRRing! -- First, CREATE XIRR Table to store values CREATE TABLE XIRRTempData( amt float, dt datetime, guid varchar(128) ) go create function dbo.XIRR( @d datetime, @GUID varchar(128) ) returns decimal(18,10) as begin /* USAGE: select @IRR = dbo.xirr(null, guid) select @IRR IRR, @IRR * 100 'IRR %' Note: Leave the first parameter (date) null if you wish to see the XIRR calculated as of the maximum date in the dataset provided else provide a specific date to see the XIRR calculated as the given date. Created By: Ankeet Shah Created On: 7/16/2008 */ IF @d is null SELECT @d = max(d) from Inc
I recently had a need to trigger Intelligent Cube refresh based on a certain event. That certain event was ETL completion. My ETL writes begin and end times to a log. I could easily have a service run that would periodically monitor the log and refresh the Intelligent Cube. That seems to be too much of an overhead when I can simply call the Command Manager to execute the cube refresh from the ETL. Image Source: https://www.rubiks.com/solve-it/3x3 You will need to create a MicroStrategy Event, MicroStrategy Schedule, schedule Intelligent Cube Refresh, create Script file, create Batch file and that pretty much sums it all! Create an Event Select New, Event and name it – Events are an empty object - as in they do not do anything, it is just an empty shell. Call this event a proper name. In my case, it is aclled Batch Cube Refresh - Short Form. Create a Schedule that uses the new Event Select New > Schedule Name the schedule, in my case 'Batch