Skip to main content

Posts

Using SQL To Calculate XIRR (Internal Rate of Return)

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 IncomeTable

declare @irrPrev fl…
Recent posts

MicroStrategy: Event Triggered Intelligent Cube Refresh

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.




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 Cube Refresh'
Click Next and select Event Triggered
Click Next and assign appropriat…

SQL SERVER – Puzzle – Write a Shortest Code to Produce Zero

Originally Posted by: Pinal Dave @ SQLAuthority
https://blog.sqlauthority.com/2017/08/24/sql-server-puzzle-write-shortest-code-produce-zero/




Pinal Dave is someone whose blogs I've followed for years, probably almost a decade. He has posted a puzzle on his website. If you know the answer, head over to the site, link above and respond!

Puzzle: Write a Shortest Code to Produce Zero
Conditions
1. Do not use numbers and arithmetic calculation
2. Do not use the function 'LEN()'
3. Do not use 0 in the code
4. Length of the entire code should be less than 19 characters

Good Luck!

Scripting ALL ForeignKeys At Once!

I have a pretty extensive ETL in place involving a ton of tables. To improve performance and keep the transaction log clean, I recently decided to TRUNCATE a bunch of tables instead of DELETE. Unfortunately, Now, I was unable to truncate data as these tables are a part of foreign key based referential integrity in MS SQL.

No love lost. I started scripting one FK at a time and saving them in two separate files, DROP_FK.sql and RECREATE_FK.sql. After doing about five of them at 11pm, I realized that this was going to be a painful task. A light bulb went off - why not use the system tables; they surely have all of this information stored somewhere!

After a bit of research, I came up with the below query. There are many other queries (probably more robust than mine) out there on the interwebs! Please do your research.

Without further ado, here is my query.

Database: any
Tables: sys.tables
      sys.schemas
      sys.foreign_keys
      sys.columns
      sys.foreign_key_columns


DROP CONSTRAINTS

Only 2 Jobs Running at a time

I noticed while executing a document with four underlying reports that only 2 reports were executing at a time. There was no reason that the server cannot handle more than 2 jobs at a time.

Think of this scenario - there are 50 users in your organization who need to run report. If only 2 jobs execute at a time on iserver, it while be a while before all users get their data!

Here's how to change that:

Right click on projectGo to Project ConfigurationGo to Database Instances > Select your DB providerSelect Database Instance and click Modify buttonGo to Job Prioritization tabOn the bottom change the number of simultaneous jobs that can be executed
Consult your system administrator for any 'side effects'. I work with 9.3 version.

Setting First Day of Week in Prompt Calendar display

Here is a pretty simple request from my client - "Our work week starts on Monday and ends on Sunday. Can you make sure that the calendar in date prompt also starts from Monday?" Me - "Sure why not, MSTR is pretty flexible". I was so wrong! There is no [easy] way to do this in MicroStrategy.

One solution that 'kind of' worked for me:
1. In preferences, change the locale for date and numbers to European. This will change the display to show Monday first. Issue: Date format changes to DD/MM/YYYY and here in the US we need MM/DD/YYYY.

2. To address the 'side effect', based on TN 12819 (https://resource.microstrategy.com/Support/MainSearch.aspx?tnkey=12819&formatted=1) I made appropriate changes to format_config.xml.
This now changed the date format to US format when selected from Calendar Date prompt. But default values still showed up in European format. Furthermore, within the report the dates showed up as YYYY-MMM-DD and using Advanced Format di…

Quick Tip - Display Project Status on project's home page

Have you wanted to do this?


Very easy to so  with version 9.x. Accessible from Project Configuration > Project Definition > Communications.



Quick Tip - Showing a metric as hundreds or thousands

Ever come across a situation where you need more real estate on the chart but those large dollar amounts on your Y axis running in to hundreds of millions just take up all of the white space? Or simply make your grids too wide?

Solution is very simple! Go to the metric number formatting, select Fixed number format and change Abbreviation value to desired format - thousands, millions or billions.

Another option is to select Custom number format and enter this formula:#,##0,.00"K";(#,##0,.00)"K"
Once this formula is working fine, modify it to suit your needs.