In some cases we need to be able to analyse non-transactional data for discrete periods along a time dimension. An example of such a case is a collection of invoices, which have start and end dates for a period, but are not otherwise connected to a time axis. We may have such invoices with these properties:
One of the invoices may be:
Invoice Id: 34821432
Start Date: 2008-10-15
End Date: 2009-03-14
and another one:
Invoice Id: 34934221
Start Date: 2008-12-01
End Date: 2009-05-30
If the company we are building this for is daily deducting a fee for its services (e.g. funds management, software maintenance, etc.), we may have to be able to spread the amount in smaller periods, like months or days and then aggregate the smaller amounts along a time dimension.
To do this we have to first store the data in a relational table and then write some SQL to do the trick for us.
First, we should create a table valued function which returns all the dates at a specified granularity, such as days, from the Start to the End dates and the count of all the periods in between (in our case is is a count of days):
CREATE FUNCTION udf_Create_Daily_Date_Spread
, @End_Date datetime
RETURNS @Daily_Spread TABLE (
, Count_Of_Days int
DECLARE @Count int
SET @Count = 0
IF @Start_Date >= @End_Date
WHILE @Start_Date <= @End_Date
INSERT INTO @Daily_Spread(Date_Id)
SET @Start_Date = DATEADD(d, 1,@Start_Date)
SET @Count = @Count + 1
SET Count_Of_Days = @Count
After having created these functions, we can use the CROSS APPLY statement to create the even spread:
FROM Invoice_Source inv
CROSS APPLY udf_Create_Daily_Date_Spread(inv.Start_Date, inv.End_Date) cdds
After running the sample data through this code, we will get an even spread for both invoices and we will be able to attach a time dimension to them.
Even though the data size may explode after such a manipulation, Analysis Services provides an excellent way of handling even the largest sets of data. If storage is a problem, we can always choose to break down our data in less periods – instead of days, weeks or months.