Date bridges

[hobanwashburne]

I'm not sure if I have the hang of the Date Bridge process.

I have a fairly large data set (108 fields x 4,000,000 records and growing). Included in that table there are 4 date fields: Order Entry Date, Requested Ship Date, Actual Ship Date, and Invoice Date. There is also a unique record ID field.

After I load the History table I go through 4 additional load steps (one for each date type) similar to the following:

DateBridge:

Load

     'InvoiceDate' as DateType,

     RecordID,

     InvoiceDate as CalDate

Resident History:

They are followed by the load of a master calendar (which includes the field "CalDate").

Everything in the dashboard functions as expected using this method.

My question is about efficiency though, for each of the Date Bridge load statements the entire data set doubles. The resulting data set is now 16,000,000 records where each previously unique record now has four duplicates that only differ by one field (DateType).

Is that correct?

[JonnyPoole · Accepted solution]

Hi Scott - i think you are referring to the datebridge here and the answer is 'yes' to your question

http://community.qlik.com/blogs/qlikviewdesignblog/2014/02/17/canonical-date

Think of each date type as you might say a measure value.  For example,  Sales$ and Expense$ and Margin $ are all clearly different, but the chance is high that some or many of the actual values are repeated among all 3. Its not so much the value but the context, which is everything. That is why you need the DateBridge to split the date types apar .

Quick tip:  if your dates have timestamp component and you don't need to report times of the day,  its worth stripping it out so your dates don't preserve additional uniqueness. It will keep your data model more performant and smaller.

[MK_QSL]

Number of Rows for your DateBridge Table could be

(Number of Rows of Fact Table) * (Number of Date Fields used in DateBridge Table)