Report requirement is to show the total premiums collected from policies that are effective as of a given date.
We would usually use the Policy Start date and Policy end date fields and write a query that gets the count of all polices and hence the premiums collected where the selected date (or let’s say today) falls between the Policy Start date and Policy End date. Thereby we get the premiums from all policies that are effective as of a certain date and show it in the report.This would be easy to implement in most BI tools on the market.
However this has been difficult to implement in Sisense. The workaround that we have come up with is to use tables that stores every date between the Policy Start date and Policy End date for every policy loaded from the source. Typically the Policy Start and End date spans a year and hence a policy ends up having 365 records against it in this table.
Hence when 2 policies come in from source, there are 700 records created in this table at a minimum. This is then provided in the dashboard as a mandatory filter so that user can select the date and see the data as of that date. With 100 policies created a month , we can expect 0.5 million records for a year in this table.
These tables are created as custom expressions in Sisense and hence by nature they are memory bottlenecks, even though the logic used to compute this are usually pretty simple.
These tables of late have become pain points owing to the exponential increase in their data volumes and difficulty in modelling the data just to cater to this requirement.
It would be useful to have filters / prompts that show up at dashboard load time so that the user can provide the input date and Sisense fetches the transactions that would fall between the start date and end date.If this is difficult then have a generic date filter that the user can select on the dashboard and Sisense fetches the transactions that would fall between the start date and end date.
Please sign in to leave a comment.