I have to build a cube based on date range records, and not sure about the best way to proceed.

Imagine say a cube of Cars and warranty periods. Each car has a start date, and an end of warranty periods. Then there may be extended warranty periods.. so imagine

CAR REG    TYPE      WARRANTY START    WARRANTY END
CAR A      PURCHASE    01/01/2016        31/01/2016
CAR A      EXTENDED    01/01/2017        30/06/2017
CAR A      EXTENDED    01/08/2017        30/01/2018  -- note, gap here
CAR B      PURCHASE    01/01/2016        31/01/2016
CAR B      EXTENDED    01/01/2017        30/06/2017
CAR B      EXTENDED    01/08/2017        30/01/2018  -- note, gap here

So multiple items, with multiple date ranges. There is a main table (CARS) with car details (colour, model, etc).

Now I want to build a cube, which is reportable at month level, cars under warranty/warranty type, etc.

So plan 1 was to build a view which explodes the above out by a join to a date table, report by month, and feed this into a cube. But, the number of cars multiplied by the months covered leads to multi hundreds of milions of rows - which means sometimes the server runs out of TempDB space, and when it does run, the cube takes hours to build.

Is there a better way - such as a view for the car details, and then another view on the warranty table (how do I get SSAS to deal with months in a date range) - will the join in SSAS be more efficient than a join in a view in SQL?

Thanks all.

You can connect start and end columns to time dimension. And on the report you can use ":" operator to build date tange report. More details you will find here: http://www.purplefrogsystems.com/blog/2013/04/mdx-between-start-date-and-end-date/