CODEWITHSUNDEEP
iccube
UlrichWuenstel
UlrichWuenstel

Reputation: 470

IcCube - OLAP Time Dimension with range only linked on start date

In IcCube there is a Time Wizard with an option to index by range. I then can link this dimension to a fact table with a start field and an end field.

But unfortunately I have a fact table with only start dates and I would like to link them in that way, that the entry is valid, until I have another entry with the same keys. Would that be possible, or would I have to add the end date to every entry?

Upvotes: 2

Views: 103

Answers (2)

UlrichWuenstel
UlrichWuenstel

Reputation: 470

Thank you for the schema. Unfortunately I couldn't open the schema in IcCube. It said, that the schema is faulty. But having a look at the code helped a lot. I changed the javascript a little, because the was a problem with the dates and I added the possibility of a slowly changing dimension:

In Memory:

Dim1, DimTime, Amount
1,1 Feb 2014,2.4
1,3 Feb 2014,1.4
1,11 Feb 2014,1.4
2,4 Feb 2014,2.4
2,8 Feb 2014,1.4
3,3 Feb 2014,3.4
3,7 Feb 2014,6.4

Init Code:

var SCD = "Dim1";
var formerRow = null;
var currentRow = null;
var rowNumber = 0;
var jDateType = Java.type( "org.joda.time.LocalDate" );
var jDateNow = new jDateType();

Row Processing Row:

currentRow = copy();
set( currentRow, "TO", jDateNow );
if ( formerRow === null) {
  // first Line, copy the current row
  formerRow = copy(); 
  currentRow = null;
} else if (get(currentRow,SCD) != get(formerRow,SCD)) {
  set( formerRow, "TO", jDateNow );   
  fire(formerRow, rowNumber++);
  formerRow = currentRow;
  currentRow = null;
} else {
  // second  Line   
  set( formerRow, "TO", get(currentRow,"DimTime").plusDays(-1) );
  fire(formerRow, rowNumber++);
  formerRow = currentRow;
  currentRow = null;
}

Completion Code:

fire( formerRow, rowNumber++ );

Upvotes: 0

ic3
ic3

Reputation: 7680

If I understood correctly, you've a fact table with the following structrue

 Dim1, DimTime, Amount
 1 , 1 Feb 2014, 2.4
 2 , 8 Feb 2014, 1.4     
 3 , 3 Feb 2014, 3.4

You would like to bind the first line from the [1 Feb,3 Feb), the third line from [3 Feb, 8 Feb) and so on.

If this is the case, there is no easy way. Ideally you calculate the to) part in your data source or you could use icCube's ETL layer to solve it there (Javascript with cache). Note the last is not a good approach for very large tables.

If you can order by the Time column the algorithms would be a lot easier without the need to cache the whole table.

You can find an schema example in this link using a Javascript view as described here.

hope it helps

Upvotes: 2

Related Questions