Mark White's BI Blog

In a  previous post about earned premium , I outlined how you could calculate a monetary value based on a period of time over which it was earned using DAX. Serendipitously, the next day a colleague forwarded Alberto Ferrari's paper on understanding DAX query plans , and after giving it a thorough read I fired up the query profiler and set out to optimize our calculated measure for earned premium. Alberto's paper details a performant solution to the classic events in progress problem, of which earned premium  is a close cousin. My excitement at lazily shoplifting Alberto's work came to a grinding halt when I discovered that his 40ms Jedi solution only worked if data was queried at a specific granularity. This wasn't going to cut it... we need an earned premium measure that works at any level of aggregation. Back to the drawing board. It turns out that much of Alberto's advice is (as always) really valuable. While I strongly recommend reading Alberto's pa...

In the world of short term insurance, "Earned Premium" is a common BI metric. In its simplest form, an amount of money is earned as time elapses through a period of cover. On any given day, you will have earned some, all or none of the premium paid. Using DAX calculations, solving earned premium turns out to be both easy and efficient, and in this post I'll show you how to do it. Before you start, download and open this Excel 2013 file . Make sure that you have the PowerPivot add-in enabled in Excel. If you open the PowerPivot cube, you'll find two tables, the first being a regular "Date" table used to represent the hierarchy of days, months, years etc. The second "Premium" table contains the data that's of interest, and to keep things simple, I've only included 4 columns: Product Line   - describes a type of insurance product Amount           - the amount of premium paid by a policy holder for a given period of cover. Start Date        ...

Recently during a Data Warehouse project, I had the need to move many tables to a new file group. I didn't like any of the solutions that I found on Google, so decided to create on of my own. The result? MoveTablesToFilegroup Click here for a nifty stored proc allows you to easily move tables, indexes, heaps and even LOB data to different filegroups without breaking a sweat. To get going, copy-paste the code below into Management Studio, and then run it to create the needed stored procedure. Hopefully the arguments are self explanatory, but here are some examples: 1. Move all tables, indexes and heaps, from all schemas into the filegroup named SECONDARY: EXEC dbo.sp_MoveTablesToFileGroup @SchemaFilter = '%', -- chooses schemas using the LIKE operator @TableFilter  = '%', -- chooses tables using the LIKE operator @DataFileGroup = 'SECONDARY', -- The name of the filegroup to move index and in-row data to. @ClusteredIndexes = 1, --...